디스크 I/O, 임시 파일, WAL을 유발하는 쿼리 찾기(pg_stat_statements)

명령어

SELECT queryid,
       calls,
       shared_blks_hit,
       shared_blks_read,
       round(100.0 * shared_blks_hit / NULLIF(shared_blks_hit + shared_blks_read, 0), 1) AS hit_pct,
       pg_size_pretty(temp_blks_written * current_setting('block_size')::bigint) AS temp_written,
       pg_size_pretty(wal_bytes) AS wal,
       left(regexp_replace(query, '\s+', ' ', 'g'), 120) AS query
FROM pg_stat_statements
ORDER BY shared_blks_read + temp_blks_read + temp_blks_written DESC
LIMIT 20;

하는 일

shared buffers 밖에서 가장 많은 블록을 읽고, 임시 파일에 가장 많이 쓰며(work_mem에 들어가지 않는 정렬/해시), 가장 많은 WAL을 생성하는 쿼리를 cache hit ratio와 함께 보여 준다.

사용 시점

디스크 읽기 지연이 증가하거나, 임시 파일 알림이 발생하거나(log_temp_files), WAL 양이 갑자기 늘어날 때 원인 쿼리를 찾기 위해.

주의

shared_blks_read는 OS page cache에서 처리된 읽기도 포함한다. 실제 디스크 시간에는 track_io_timing = on이 필요하다(먼저 pg_test_timing으로 오버헤드를 측정). temp_written이 높으면 해결책은 대개 롤/세션별 work_mem 상향 또는 쿼리/인덱스 수정이다. work_mem을 전역으로 올리면 메모리 위험이 커진다.