PostgreSQL DB 성능 개선을 위한 설정 파일 수정

개발 서버에 PostgreSQL DB 설치 후 사용하면서 속도가 느리다는 느낌을 받았습니다. 서버 사양은 낮지 않은데 DB 성능은 좋지 않아 설정의 문제가 있지 않을까 싶어 개선 방법을 찾아보았습니다.

PostgreSQL 의 기본 설정은 최고의 성능을 내기 보다는 가능한 다양한 기기에서 잘 동작할 수 있도록 보수적으로 잡혀 있다고 합니다. 설치 후 아무런 설정을 건드리지 않았다면 속도가 느린게 당연한 것이었습니다.

PostgreSQL 은 postgresql.conf 파일을 수정한 후 DB 를 재기동하면 설정이 변경됩니다. 

※ DB 재기동 없이 Query 또는 Command 를 실행해서 설정을 적용하는 방법도 있습니다. 그러나 일반적인 설정 적용은 가능하지만 일부 재시작이 필요한 설정은 적용되지 않습니다.

- 쿼리(Query)를 활용한 Reload 

SELECT pg_reload_conf();

- 명령어(Command)를 활용한 Reload

pg_ctl reload -D [Datbasae 경로]

 

※ postgresql.conf 파일이 있는 위치를 확인하는 방법입니다.

show data_directory;

※ 성능 개선을 위해 postgresql.conf 파일 내용을 수정한 항목입니다.

1) shared_buffers
Postgresql 은 디스크에 메모리 값을 직접 변경하는 대신 공유 버퍼 캐시에 데이터를 읽도록 요청한다.
서버의 메모리 성능은 이 공유버퍼의 용량을 조정함으로써 조절할 수 있다.
기본값은 128MB, 최소 128KB 이상으로 설정해야 한다.
서버 메모리의 1/4 정도 용량을 설정한다. (공식 페이지에서는 40%를 넘지 않도록 권장)
설정값은 MB, GB 등 단위별로 설정 가능하다.

2) work_mem
sort, merge, join 등에 사용하는 메모리 설정 값. 서버 사양에 따라 설정 값을 변경해주면 됩니다.
기본값은 4MB 로 설정되어 있습니다.

계산식
ex) 32GB 메모리에 max_connections 100

(시스템 전체 메모리) / (최대 커넥션 수 * 16)
(32G * 1024) / (100 * 16) = 32,768 / 1600 = 20.48 => 약 20MB 로 설정

1 / (max_connections * 2)
(32G * 1024) / (100 * 2) = 163.84 => 약 160MB 로 설정

이 계산식이 정답이라고 할 수는 없습니다.
work_mem 을 크게 설정하면 대용량 작업을 할 때 좀더 성능이 좋아지지만 커넥션이 늘어나면 프로세스를 다 띄우지 못하고 초과되어 Out of Memory 가 발생할 수 있습니다.
각자 상황에 맞게 운영하면서 최적 값을 찾아가면 될 것 같습니다.

3) maintenance_work_mem
VACUUM, CREATE INDEX 및 ALTER TABLE ADD FOREIGN KEY 와 같이 유지 보수 작업에서 사용할 최대 메모리 크기
백업 및 복원 성능도 해당 설정의 영향을 받는다.
기본값은 64MB이며, work_mem 설정보다 크게 설정하는 것을 권장한다.
maintenance_work_mem 설정은 서버 메모리의 1/16으로 설정하는 것을 권장한다.

4) wal_buffers
데이터베이스의 변경 사항을 잠시 저장(로그)는 버퍼입니다. WAL(Write Ahead Log)이라고 명칭합니다.
백업 및 복구 관점에서 보면, WAL 버퍼와 WAL 파일은 매우 중요합니다.
데이터를 업데이트 할 때 어떤 변경을 할 것 인지를 남기는 로그이며, PostgreSQL에서는 WAL(Write Ahead Log)이라 명칭합니다.
Default : 64KB
트랜잭션 로그(WAL log) 버퍼, shared_buffers 의 1/32 권장

5) max_wal_size
WAL 조각 파일들은 더 이상 보관할 필요가 없어지면, 지워지는 것이 아니라, 앞으로 사용될 파일로 그 이름을 바뀝니다. 이 설정은 이렇게 남겨두는 WAL 최소값을 지정합니다.
Default : 80 MB
산정 방법
OS 메모리의 1/2 또는 3/4 설정

6) min_wal_size
체크포인트 작업을 자동으로 진행할 WAL 최대 크기입니다.
Default : 1GB
산정 방법
최소 1GB로 만들고 최대 수 GB를 50 - 100GB

7) random_page_cost
비순차적으로 접근하는 디스크 페이지에 대한 계획 관리자의 예상 비용을 설정합니다.
random I/O cost 추정값으로 높으면 풀스캔 선호, 상대적으로 줄이면 인덱스 스캔을 선호함
Default : 4.0

8) effective_cache_size
단일 쿼리에 사용할 수 있는 디스크 캐시의 효율적인 크기(캐시할 수 있는 메모리 양)에 대한 플래너의 가정을 설정합니다.
이것은 인덱스를 사용하는 비용 추정에 반영됩니다. 값이 클수록 인덱스 스캔이 사용될 가능성이 높습니다.
Default : 4GB
산정 방법
OS 메모리의 1/2 또는 3/4 설정

9) autovacuum 설정
- autovacuum_max_workers
한 번에 실행할 수 있는 autovacuum 프로세스(autovacuum 제외)의 최대 수를 지정한다. 
기본값은 3 

- autovacuum_vacuum_scale_factor, autovacuum_analyze_scale_factor
autovacuum_vacuum_scale_factor, autovacuum_analyze_scale_factor 값 0 으로 설정
0 으로 설정해야 autovacuum_vacuum_threshold, autovacuum_analyze_threshold 에 설정한 dead tuple 수마다 autovacuum 이 동작함

- autovacuum_vacuum_threshold, autovacuum_analyze_threshold
autovacuum_vacuum_threshold = 100000
dead tuple 이 10만개 생성될 때마다 autovacuum 이 동작함

- vacuum_cost_limit
vacuuming 프로세스를 슬립 시키는 누적 비용. 기본값은 200이다.
autovacuum_vacuum_cost_limit = -1 일 경우 vacuum_cost_limit 값 참조

※ 실제 개발 서버에 적용한 설정파일입니다. 정답이 아니니 참고용으로만 활용하시길 바랍니다.
- 개발서버 사양

postgresql.conf

0.03MB