[PostgreSQL] 가장 디스크를 많이 괴롭히는 쿼리 top 10 뽑기
디스크를 많이 괴롭히는 쿼리을 뽑아낸 뒤, 그 쿼리들에 인덱스를 주거나 쿼리 실행 방식을 효율적으로 바꾸는 작업을 진행하시면 됩니다.
SELECT
query,
calls,
-- 1회 실행당 평균 캐시 오염/작성 블록 수
round((shared_blks_dirtied + shared_blks_written)::numeric / calls, 2) as avg_write_blocks,
-- 1회 실행당 평균 WAL 생성 크기 (바이트 단위)
round(wal_bytes::numeric / calls, 2) as avg_wal_bytes,
-- 전체 누적 쓰기 부하 기준 정렬
shared_blks_dirtied + shared_blks_written as total_writes
FROM pg_stat_statements
WHERE calls > 0
ORDER BY total_writes DESC
LIMIT 10;- PostgreSQL에서 블록(Block) 1개의 크기는 기본적으로 8KB입니다. 이 단위를 기준으로 계산하면 감을 잡기 쉽습니다.
avg_write_blocks (회당 평균 쓰기 블록)
- 좋음 (정상): 10 이하. 단순 한 건 수정/삽입은 블록을 거의 건드리지 않습니다.
- 나쁨 (위험): 100 이상. 한 번 실행할 때마다 메모리와 디스크에 가하는 부하가 큽니다.
- 매우 나쁨 (최악): 1,000 이상. 인덱스가 너무 많거나 대량의 데이터를 통째로 쓰고 있다는 뜻입니다.
avg_wal_bytes (회당 평균 WAL 로그 생성량)
- 좋음 (정상): 10KB (10,240) 이하. 일반적인 트랜잭션 수준입니다.
- 나쁨 (위험): 1MB (1,048,576) 이상. 트랜잭션 로그가 쏟아져 나와 디스크 쓰기 병목(I/O 스트레스)을 유발합니다.
[연관 자료]
[PostgreSQL]pg_stat_statements 뷰를 조회하여 디스크/메모리 버퍼를 과도하게 사용(I/O 부하 유발)하는 쿼리들을 상위 순위로 추출방법 및 실행계획 튜닝
