postgreSQL 튜닝

이 글은 postgreSQL의 내부 구조를 어느 정도 안다고 가정하에 진행합니다.

 

 

1.  shared buffer 튜닝

- DISK I/O를 최소화 함으로써 I/O의 성능을 향상하기 위한 버퍼

- 많은 사용자의 동시 접근할때의 경합 최소화가 목표

- 자주 사용되는 블록은 최대한 버퍼 내에 있어야 한다.

 

 

1.1 pg_prewarm()

- 해당 테이블을 강제로 buffer로 올린다. (캐시처럼 사용하기 위해 미리 로드)

- 테이블의 크기에 따라 buffer가 더 느려질 수도 있다.

creste extension pg_prewarm;
select pg_prewarm('t1')

 

1.2 HOT (heap only tuple)

- MGA에 의한 단점으로 해당 테이블의 변경이 빈번할 경우 테이블 크기 상승과 해당 데이터들의 체인 닝이 늘어난다.

- 이를 최소화하기 위해(물론 vacuum으로 되지만, 처리가 느림) 테이블에 셋팅하는 작업

- 테이블의 변경이 많다면 해당 filefactor 값을 줄여야 한다. (많을 수록 체이닝이 많아져서 느려진다.)

- 아래의 filefactor값은 %이다. (디폴트 100%)

create table t1 (c1, c2) with (filefactor=50)

 

1.3 shared buffer 메모리 설정

 

1. Buffer (디스크) 사용량 : 너무 클 경우 파티션으로 변경 필요

2. shared_buffers  대비 사용률 : 너무 클 경우 메모리 조정 필요

3. 테이블 대비 캐시 비율 

$ CREATE EXTENSION pg_buffercache;
$ select c.relname as relname
       , pg_size_pretty(count(*) * 8192) as buffered
       , round(100.0 * count(*) / ( select setting from pg_settings where name='shared_buffers')::integer,1) as buffer_percent
       , round(100.0*count(*)*8192 / pg_table_size(c.oid),1) as percent_of_relation
from pg_class c inner join pg_buffercache b on b.relfilenode = c.relfilenode inner
join pg_database d on ( b.reldatabase =d.oid and d.datname =current_database())
group by c.oid,c.relname order by 3 desc

 

메모리 수정은 postgresql.conf 파일 수정을 통해서 이뤄지며 (aws RDS의 경우 환경설정에서 가능)

주요 파라미터는 아래와 같다.

default_statistics_target	테이블 칼럼에 대한 기본 통계 갯수 default 100
maintenance_work_mem	Vacuum, create index 작업 시 사용 메모리 메모리 1GB당 * 50MB로 선정 (ex 32GB * 50MB -> 1.6G)
checkpoint_completion_target	트랜잭션 페이지 기록 단  세크먼트는 3으로 설정
effective_cache_size	데이터 캐싱에 사용할 수 있는 메모리 양 시스템 전체 메모리의 50~70% 설정
work_mem	Sort, merge 에 사용, 시스템 전체 메모리의 /( 최대 커넥션 수 * 16)
wal_buffers	REDO 버퍼, 16mb
checkpoint_segments	트랜잭션 페이지 기록 단위, 용량은 16MB
shared_buffers	DISK IO 최소화, 전체 메모리의 25%

 

1.3.1 shared_buffers

공유 버퍼의 크기는 기본 32M이지만, 대략 서버 메모리 기준으로 1/4 ~ 1/2 정도로 할당

# - Memory -
shared_buffers = 128MB            # min 128kB

 

1.3.2 트랜잭션 로그 버퍼 ( wal_buffers )

- 데이터를 업데이트 할 때 어떤 변경을 할 것 인지를 남기는 로그

- 일반적으로 shared_buffers의 1/32 크기로 지정

#wal_buffers = -1            # min 32kB, -1 sets based on shared_buffers

 

1.3.3  체크 포인트 세그먼트 ( checkpoint_segments )

- 아직 디스크에 기록 안 된 공유 버퍼의 페이지를 기록하는 부분의 설정

- 디폴트는 3세그먼트 이므로, 3*16MB, 즉, 48MB가 쌓이면 디스크에 기록한다는 의미

- DISK I/O 빈도를 줄여 성능 향상에 도움이 됨

# - Checkpoints -
#checkpoint_segments = 3
#checkpoint_timeout = 5min        # range 30s-1d
#max_wal_size = 1GB
#min_wal_size = 80MB
#checkpoint_completion_target = 0.5    # checkpoint target duration, 0.0 - 1.0
#checkpoint_flush_after = 256kB        # measured in pages, 0 disables
#checkpoint_warning = 30s        # 0 disables

 

1.3.4 default_statistics_target

- PostgreSQL 쿼리 플래너는 개별 테이블에 통계를 필요

- 만약 이 값이 적다면, 쿼리 플래너의 결과가 달라짐, 대신 이 값이 너무 높으면 PostgreSQL는 개별 테이블의 통계를 수집하는데 많은 시간을 사용

- 만약 ‘LIKE’ 쿼리를 자주 사용한다면, 이값을 증가시켜줄 필요 있음

 

1.3.5 maintenance_work_mem

- autovacuum_max_workers의 설정과 연관된 값 (값이 작다면 자주 vacuum이 발생한다)

- 이값은 전체 메모리의 5% 정도로 설정

 

1.3.6 work_mem

- sort가 많이 사용되거나 큰 sort 가 필요하다면 이 값을 증가

- 이 값은 각 사용자 세션별로 할당되며, 최대 접속자의 수를 고려해야 한다.

- ( OS cache memory / connections ) * 0.5 = 메모리 (MB)

 

 

 

 

 

2. 쿼리 튜닝

2.1 explain

- 실행계획엔 예측 모드 / 실행 모드가 있다

- 예측모드 : 실제 수행하지 않고 예상 실행 결과

- 실행모드 :  실제 수행한 후 실행 계획 결과 (수행 시간 / IO 블록을 추가 제공)

 

- 예측모드 : 쿼리 앞에 explain을 붙인다

$ explain select * from t1;

- 실행모드 : 쿼리 앞에 explain analyze를 붙인다. , IO 블록수를 확인하려면 buffers를 붙인다.

explain (analyze, buffers) select * from t1;

- rows removed by filter 의 값이 크면 인덱스 생성을 고려한다.

- buffers의 hit수가 크면 메모리의 튜닝을 고려한다.

- 칼럼의 인덱스를 생성했지만, 인덱스를 타지 않았다면 vacuum실행을 고려한다.(기존 옵티마이져 통계가 잘못되어 있는 것을 vacuum을 통해 재 설정)

 

 

2.2 PG_HINT_PLAN

설치 및 환경 설정 :  https://pghintplan.osdn.jp/pg_hint_plan.html

pg_hint_plan

사용 함수들

SeqScan (table)	Seq Scan 방식으로 유도한다.
IndexScan (table index)	Index Scan 방식으로 유도한다.
BitmapScan (table index)	Bitmap Scan 방식으로 유도한다.
NoIndexScan (table)	Index Scan과 Index Only Scan 방식을 사용하지 않도록 한다.
NoIndexOnlyScan (table)	Index Only Scan 방식을 사용하지 않도록 한다.
NoBitmapScan (table)	Bitmap Scan 방식을 사용하지 않도록 한다
NestLoop (table table)	NL 조인으로 유도한다.
HashJoin (table table)	해시 조인으로 유도한다.
MergeJoin (table table)	Merge 조인으로 유도한다.
NoNestLoop (table table)	NL 조인을 사용하지 않도록 한다.
NoHashJoin (table table)	해시 조인을 사용하지 않도록 한다
NoMergeJoin (table table)	Merge 조인을 사용하지 않도록 한다
Leading (table table [table…])	조인 순서를 제어한다. 단, 조인 방향을 제어하지는 않는다.
Leading (table table)	조인 순서와 방향을 제어한다. 즉, 왼쪽 테이블이 Driving 또는 Outer 테이블이다

 

 

 

 

 

 

참고자료

https://github.com/postgres/postgres/blob/master/src/backend/access/heap/README.HOT