메모리 설정 검토(shared_buffers, work_mem, maintenance_work_mem)

명령어

SELECT name,
       current_setting(name) AS value,
       source,
       context
FROM pg_settings
WHERE name IN ('shared_buffers', 'effective_cache_size', 'work_mem', 'hash_mem_multiplier',
               'maintenance_work_mem', 'autovacuum_work_mem', 'max_connections',
               'max_parallel_workers_per_gather', 'huge_pages', 'temp_buffers', 'wal_buffers')
ORDER BY name;

-- Rough ceilings: work_mem x max_connections and the autovacuum memory ceiling
SELECT pg_size_pretty(pg_size_bytes(current_setting('work_mem'))
                      * current_setting('max_connections')::int) AS work_mem_x_max_connections,
       pg_size_pretty(pg_size_bytes(current_setting('maintenance_work_mem'))
                      * current_setting('autovacuum_max_workers')::int) AS autovacuum_mem_ceiling;

-- Trace of operations that did not fit in work_mem: temp files per database
SELECT datname, temp_files, pg_size_pretty(temp_bytes) AS temp_size, stats_reset
FROM pg_stat_database
WHERE datname IS NOT NULL
ORDER BY temp_bytes DESC;

하는 일

메모리 관련 파라미터를 값, 출처(기본값/구성 파일), 변경 context(postmaster = 재시작)와 함께 보여 준다. work_mem과 autovacuum의 대략적인 메모리 상한, 데이터베이스별 임시 파일 양도 보여 준다.

사용 시점

새 서버를 구축할 때, RAM을 추가한 후, OOM killer 발생 후, 또는 임시 파일 사용량이 많을 때.

주의

work_mem은 연결당이 아니라 정렬/해시 노드당(해시는 hash_mem_multiplier 배) 할당되며 parallel worker마다 별도로 할당된다. 복잡한 쿼리는 work_mem의 몇 배를 사용할 수 있으므로 계산된 곱은 상한이 아니라 대략적인 경고다. 일반적인 시작점: shared_buffers는 RAM의 ~25%(재시작 필요), effective_cache_size는 ~50-75%(플래너 힌트일 뿐 아무것도 할당하지 않음), maintenance_work_mem은 512MB-2GB. autovacuum_work_mem = -1이면 각 autovacuum worker가 maintenance_work_mem을 사용할 수 있다. temp_bytes가 높으면 work_mem을 전역이 아닌 롤이나 쿼리별로 높인다. Linux에서 shared_buffers가 크면 huge_pages를 고려한다.