[PostgreSQL] shared_buffers·work_mem·effective_cache_size 지금 값부터 확인한다

설치·접속: PostgreSQL 설치와 접속

부제: 튜닝 글을 읽기 전에, 내 서버가 실제로 어떤 메모리 값으로 돌고 있는지 먼저 눈으로 확인할 때

SHOW shared_buffers;
SHOW work_mem;
SHOW effective_cache_size;

SELECT name, setting, unit, context
FROM pg_settings
WHERE name IN ('shared_buffers','work_mem','effective_cache_size');

 shared_buffers  -> 128MB
 work_mem        -> 4MB
 effective_cache_size -> 4GB

         name         | setting | unit |  context
----------------------+---------+------+------------
 effective_cache_size | 524288  | 8kB  | user
 shared_buffers       | 16384   | 8kB  | postmaster
 work_mem             | 4096    | kB   | user

세 값의 성격이 다르다. shared_buffers는 PostgreSQL이 잡아두는 공유 캐시 크기로, context가 postmaster — 즉 재시작해야만 바뀐다. work_mem은 정렬·해시 한 번당 쓰는 작업 메모리라 세션에서 바로 바꿀 수 있고(context=user), 연결·연산마다 곱해지므로 크게 잡으면 위험하다. effective_cache_size는 메모리를 실제로 할당하지 않는다 — "OS 캐시까지 합쳐 이 정도는 캐시돼 있을 것"이라고 플래너에게 알려주는 힌트라, 인덱스를 쓸지 말지 판단에만 영향을 준다. 남의 튜닝 수치를 복붙하기 전에 이 셋의 현재값과 성격을 아는 게 먼저다.

이렇게도 쓴다

이 쿼리만 무거우니 세션에서 work_mem을 잠깐 올린다. 전역은 안 건드린다. (조합: SET 세션)

SET work_mem = '64MB';
SHOW work_mem;   -- 64MB (이 연결에서만)

 

값을 함수로 읽어 계산·비교에 쓴다. (조합: current_setting)

SELECT current_setting('work_mem');

 

바꾸려면 재시작이 필요한 파라미터인지 미리 가려낸다. (조합: context 컬럼)

SELECT name, context FROM pg_settings WHERE context='postmaster' LIMIT 5;

 

기본값에서 벗어나 누가 손댄 파라미터만 훑는다. (조합: source 필터)

SELECT name, setting, source FROM pg_settings
WHERE source NOT IN ('default','override');

 

바이트 단위 raw 값 대신 사람이 읽는 크기로 본다. (조합: pg_size_bytes 역산)

SELECT setting::int * 8192 AS bytes FROM pg_settings WHERE name='shared_buffers';

 

세션에서 만진 값을 기본값으로 되돌린다. (조합: RESET)

RESET work_mem;