SQL 튜닝 과정(StartUP Tuning For PostgreSQL)까지 공부가 필요하신 분만 수행하시면 됩니다.
•
새로 만든 컨넥션으로 DBeaver에 접속한 후에 아래 SQL로 확장팩 관련된 정보를 확인합니다.
SELECT * FROM pg_available_extensions; -- 설치 가능한 확장팩
SELECT * FROM pg_extension; -- 설치된 확장팩
SQL
복사
1. PG_HINT_PLAN 설치하기
•
PG는 힌트를 사용하기 위해서는 확장팩인 pg_hint_plan을 설치해야 합니다.
•
조금 복잡할 수 있습니다. 개인적으로 이렇게 복잡해서.. 나중에 관리가 될까 싶습니다.
•
아래 경로에서 pg_hint_plan rpm 파일을 다운로드 합니다.
◦
여기서는 pg_hint_plan16-1.6.0-1.el8.x86_64.rpm 파일을 다운로드했습니다.
•
다운로드 받은 파일을, C 드라이드에 mypg_upload 이란 폴더에 카피해놓습니다.
◦
다운로드 받은 파일을 PG 컨테이너에 올려야 하는데, 파일 단위가 아닌 폴더 단위로만 올릴 수 있습니다. 그러므로 폴더 하나에 다운로드한 rpm 파일 하나만 놓도록 합니다.
•
rpm 파일을 컨테이너에 업로드합니다.
◦
Docker Desktop을 사용해 rpm 파일을 업로드합니다.
1.
containers를 선택합니다.
2.
mypg 컨테이너를 선택합니다.
3.
Files를 선택합니다.
4.
tmp 디렉토리에 마우스 우클릭 후 import를 선택합니다.
a.
앞에서 만든 mypg_upload 폴더를 선택하면 해당 폴더 채로 컨테이너에 업로드가 됩니다.
b.
업로드된 폴더를 확인할 수 있습니다.
•
rpm 파일을 설치합니다.
◦
debian linux에서 rpm 을 직접 설치하는데 이슈가 있습니다.
◦
alien을 사용해 rpm을 deb 형태 파일로 변환후 설치해야 합니다.
1.
Docker Desktop에서 컨테이너에 진입해 Exec 메뉴를 클릭합니다.
a.
여기서 Linux Shell 명령어를 실행할 수 있습니다.
2.
아래 명령어들을 차례대로 하나씩 입력합니다.
apt update
apt install alien
alien -d /tmp/mypg_upload/pg_hint_plan16-1.6.0-1.el8.x86_64.rpm
dpkg -i pg-hint-plan16_1.6.0-2_amd64.deb
Shell
복사
•
apt install alien 과정에서 y/n을 물으면 y를 선택합니다.
•
alien 이후에 rpm 파일의 이름이 변경됩니다. _(언더바)가 -(대쉬)로 변경되니 주의하세요.
3.
계속해서 EXEC창에서 다음 명령어로 pg_hint_plan이 컨테이너에 잘 설치되어 있는지 확인합니다.
ls /usr/pgsql-16/lib/
Shell
복사
•
pg_hint_plan.so란 파일이 보여지면 정상 설치된 것입니다.
4.
설치된 파일을 정상적인 경로로 카피해서 옮깁니다.
•
제 환경에서는 설치된 확장팩 모듈들이 다음 경로에 존재합니다.
◦
/usr/pgsql-16/share/extension/
•
위 경로의 파일들을 아래 경로로 옮겨야 정상적으로 확장팩이 작동합니다.
◦
/usr/share/postgresql/16/extension/
◦
아마도, 확장팩 모듈이 설치되는 경로와 실제 컨테이너상의 확장팩 모듈의 경로가 상이한 것 같습니다.
◦
EXEC 창에서 다음 명령어로 설치된 확장팩 모듈들을 카피합니다.
1.
pg_hint_plan.so는 옮기지 않고 현재 경로에 그대로 둡니다.
2.
/usr/pgsql-16/share/extension 에 설치된 파일만 다른 곳으로 옮깁니다.
cp -a /usr/pgsql-16/share/extension/* /usr/share/postgresql/16/extension/
Shell
복사
•
pg_hint_plan을 PG의 conf 파일에 등록합니다.
◦
설치된 pg_hint_plan 모듈을 PG가 가동될 때 불러올 수 있도록 설정해야 합니다.
1.
컨테이너의 Files 메뉴를 선택
a.
var/lib/postgresql/data 폴더까지 내려갑니다.
2.
postgresql.conf 를 마우스 우클릭 후 Edit File을 선택합니다.
3.
아래 부분에 파일의 내용이 열립니다. shared_preload_libraries 부분의 주석을 제거하고 다음과 같이 수정합니다.
a.
기본 설치된 경로가 문제인지, 경로 없이 pg_hint_plan을 바로 설정하면 제대로 작동하지 않습니다.
shared_preload_libraries = 'pg_stat_statements, /usr/pgsql-16/lib/pg_hint_plan' # (change requires restart)
Shell
복사
4.
오른쪽 위에 저장 버튼을 누릅니다.
5.
컨테이너를 종료했다. 다시 시작합니다.
•
추가로, postgresql.conf (var/lib/postgresql/data) 에서 병렬 처리를 제한합니다.
◦
방금 위에서 수정한 postgresql.conf 파일을 다시 엽니다.(열려 있다면 바로 수정하면 됩니다.)
1.
max_parallel_workers_per_gather 부분을 찾아서 주석을 제거하고 값을 0으로 설정합니다.
2.
저장합니다.
3.
종료 후 다시 시작합니다.
max_parallel_workers_per_gather = 0 # limited by max_parallel_workers
Python
복사
2. PG_HINT_PLAN 작동 확인하기
•
DBeaver를 사용해 앞에서 생성한 startdbpg에 재접속합니다.
•
아래 SQL로 설치된 pg_hint_plan을 확인합니다.
SELECT * FROM pg_available_extensions WHERE name like '%pg_hint%';
SQL
복사
•
DBeaver에서 다음 명령어로 확장팩을 사용할 수 있도록 합니다.
◦
startdbpg에 접속해서 실행해야, startdbpg안에서 확장팩을 사용할 수 있습니다.
-- startdbpg에 확장팩 두 개를 설치합니다.
-- pg_hint_plan은 추가 설치한 내용.
-- pg_stat_statements는 기본 제공하는 확장팩(shared_preload에 추가해야 사용할 수 있음)
CREATE EXTENSION pg_hint_plan; -- 힌트 관련 확장팩
CREATE EXTENSION pg_stat_statements; -- SQL 성능 수집 확장팩
SQL
복사
•
다음 SQL을 차례대로 입력해 힌트가 작동하는지 확인합니다.
◦
힌트를 사용하지 않았을때는 Bitmap Index Scan를, 힌트를 사용한 경우에는 Index Scan이 작동합니다. 힌트로 인해 실행계획이 변경되므로 힌트가 작동함을 알 수 있습니다.
create table test(a varchar(10), c varchar(10));
create index test_x1 on test(a);
insert into test(a,c)values('abc','abc');
insert into test(a,c)values('aaa','a12');
insert into test(a,c)values('ccc','cba');
EXPLAIN
SELECT t1.*
FROM test t1
WHERE t1.a = 'abc';
-- 위 SQL의 실행계획
Bitmap Heap Scan on test t1 (cost=4.18..12.64 rows=4 width=76)
Recheck Cond: ((a)::text = 'abc'::text)
-> Bitmap Index Scan on test_x1 (cost=0.00..4.18 rows=4 width=0)
Index Cond: ((a)::text = 'abc'::text)
EXPLAIN
SELECT /*+ IndexScan(t1 test_x1) */
t1.*
FROM test t1
WHERE t1.a = 'abc';
-- 힌트를 사용한 SQL의 실행계획(IndexScan으로 변경됨)
Index Scan using test_x1 on test t1 (cost=0.15..20.22 rows=4 width=76)
Index Cond: ((a)::text = 'abc'::text)
-- 이후 데이터베이스 생서을 위해 test 테이블을 DROP 합니다.
DROP TABLE IF EXISTS test;
SQL
복사