[PostgreSQL] 튜닝 옵션: work_mem

work_mem은 쿼리 최적화에 중요한 주요 옵션 중 하나다. 주로 정렬에 사용된다.

이건 디스크 스캔 후에, 메모리를 통해 데이터를 가공하는 특정 작업들에 대한 메모리 한도를 정의한다.
ORDER BY 처리, 인메모리 Hash JOIN, Merge Join용 정렬 등의 후처리 작업들이 work_mem의 영향을 받는다.

그래서 work_mem이 부족하다면 디스크 기반의 처리를 하기 때문에 급격히 느려질 수 있다.

work_mem은 글로벌 한도가 아니라 작업당 한도로 적용된다. 10MB로 걸려있으면 특정 작업마다 10MB의 한도 버킷이 적용되는 셈이다.

옵션 확인 및 조정

show 명령어를 통해 크기를 확인할 수 있다.

기본값은 4MB다.

그리고 대개, 재부팅 없이 수정할 수 있는 플래그에 속한다.

가령 AWS RDS의 경우에는 재부팅 없이 즉시 적용이 가능하다. DB 파라미터 그룹을 변경한다.

시스템을 직접 제어하는 중이라면 "postgresql.conf"를 열어서 "work_mem = 64MB"처럼 수정한 뒤에, reload를 하면 된다.
"SELECT pg_reload_conf();" 쿼리를 통해서도 처리할 수 있다.

성능 실험

실제로 쿼리를 돌려보면서 얼마나 성능 차이가 나는지, 어떻게 접근하면 되는지 알아보자.

3000만개짜리 대형 테이블을 하나 만든다.

CREATE TABLE wm_sort_test AS
SELECT
    g::bigint AS id,
    ((g::bigint * 48271) % 2147483647)::int AS sort_key
FROM generate_series(1, 30000000) AS g;

ANALYZE wm_sort_test;

SELECT pg_size_pretty(pg_total_relation_size('wm_sort_test'));

1GB 넘으니까 제법 큰 편이다.

그리고 쿼리를 돌려보면

5초 정도가 걸렸다.
그리고 잘 보면 정렬 방법에 "external merge Disk"라고 잡힌게 보일 것이다. 이게 work_mem에 올리기 너무 작아서 디스크로 정렬을 처리한 것이다.

이제 work mem을 올려서 날려보면

이런, 여전히 좀 부족하다.
테이블이 1GB라서 다 안올라가는 모양이디.

이번엔 넉넉히 4GB를 줘보자

이번에는 디스크가 안떴다. 인메모리 퀵소트로 정렬을 했고, 처음보다 좀 빨라진 것을 볼 수 있다.

이 경우에는 결과가 극적이지는 않지만, Join 이것저것 붙고 하다보면 효과는 점점 커진다.

주의사항

당연히 이걸 무조건 키우는게 능사가 아니고, 기본값이 4MB인 이유가 있다.

work_mem의 크기가 클수록 정렬 작업이 많아질수록 전체 메모리를 더 많이 소모하게 된다.
이에 따라 자연히 OOM 가능성이 증가하고, 캐시 날라갈 확률이 증가하고, 동시 처리량이 떨어질 수 있는 것이다.

그래서 공식적인 권장사항은 work_mem은 기본값으로 두고, 특정 쿼리마다 work_mem을 지역적으로 올리게 하는 것이다.

예를 들멘 이런 식으로 말이다.

BEGIN;
SET LOCAL work_mem = '256MB';
SELECT ...;
COMMIT;

참조
https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-WORK-MEM