[PostgreSQL] 스왑(swap)을 유발하는 무거운 쿼리 찾는 방법

관리자

PostgreSQL은 개별 쿼리 단위로 OS 스왑 메모리 사용량을 직접 추적하거나 기록하지 않습니다. PostgreSQL은 운영체제(OS)의 메모리 관리 영역인 스왑(Swap)을 직접 인지하지 못하며, 전체 프로세스 관점에서 메모리를 할당받아 사용하기 때문입니다.

하지만 임시 파일(Temporary Files)을 많이 생성하거나 과도한 메모리를 사용하는 쿼리를 역추적하여 스왑 유발 원인을 찾을 수 있습니다

스왑을 유발하는 무거운 쿼리 찾는 방법

pg_stat_statements 활용
- 확장 모듈인 pg_stat_statements를 사용해 정렬(Sort)이나 해시(Hash) 작업에서 디스크 임시 파일을 많이 생성하는 쿼리를 찾습니다.
- temp_blks_read와 temp_blks_written 값이 큰 쿼리가 메모리(work_mem)를 초과해 디스크 I/O를 유발하고, 결국 OS 스왑으로 이어질 가능성이 높습니다. [1]
log_temp_files 설정
- postgresql.conf에서 log_temp_files = 0 또는 특정 용량(예: 10MB)으로 설정합니다.
- work_mem 한도를 넘어 임시 파일을 만드는 쿼리의 텍스트와 사용량이 로그에 기록되므로 원인 쿼리를 특정할 수 있습니다. [1]
OS 명령어와 프로세스 매핑
- top, htop, 또는 smem 같은 OS 명령어로 현재 스왑을 가장 많이 쓰는 프로세스(PID)를 찾습니다.
- SELECT pid, query, state, age(clock_timestamp(), query_start) FROM pg_stat_activity WHERE pid = <PID>; 쿼리를 실행해 해당 프로세스에서 실행 중인 쿼리를 직접 확인합니다.

임시 파일(디스크)을 가장 많이 생성하고 읽은 쿼리 상위 10개를 찾는 SQL 쿼리문

pg_stat_statements 뷰를 조회

SELECT 
    userid::regrole AS user_name,
    dbid::regclass AS database_name,
    -- 임시 블록 읽기/쓰기 합계 (블록 크기는 보통 8KB)
    (temp_blks_read + temp_blks_written) * 8 / 1024 AS total_temp_mb,
    temp_blks_read * 8 / 1024 AS temp_read_mb,
    temp_blks_written * 8 / 1024 AS temp_write_mb,
    calls AS execution_count,
    -- 실행 1회당 평균 임시 파일 사용량
    ((temp_blks_read + temp_blks_written) * 8 / 1024) / NULLIF(calls, 0) AS avg_temp_mb_per_call,
    total_exec_time / 1000 AS total_exec_time_seconds,
    query
FROM 
    pg_stat_statements
WHERE 
    temp_blks_read > 0 OR temp_blks_written > 0
ORDER BY 
    total_temp_mb DESC
LIMIT 10;

쿼리 해석 팁

total_temp_mb: 이 값이 클수록 work_mem이 부족해 디스크(임시 파일) 영역을 많이 썼다는 뜻입니다. OS 스왑을 간접적으로 유발하는 가장 유력한 후보들입니다.
avg_temp_mb_per_call: 1번 실행될 때마다 평균적으로 얼마만큼의 임시 파일을 만들었는지 보여줍니다. 특정 쿼리가 소수만 실행되었어도 이 값이 매우 크다면 해당 쿼리의 정렬(Sort)이나 조인(Hash Join) 로직을 우선적으로 튜닝해야 합니다.

[연관 자료]