15.2 슬로우 쿼리 진단 워크플로우
“쿼리가 갑자기 느려졌다"는 사고는 매우 흔합니다. 운영자가 30분 안에 원인을 파악하는 표준 워크플로우를 정리합니다.
5단계 워크플로우
flowchart TD
S0["사고 인지"]
S1["범위 식별<br/>(어떤 쿼리·언제·얼마나)"]
S2["plan 캡처<br/>(EXPLAIN ANALYZE)"]
S3["원인 가설<br/>(통계·인덱스·락·자원)"]
S4["변경<br/>(staging 검증 후)"]
S5["검증·문서화"]
S0 --> S1 --> S2 --> S3 --> S4 --> S5
classDef step fill:#dbeafe,stroke:#1d4ed8,color:#1e3a8a
class S0,S1,S2,S3,S4,S5 step
1단계 — 범위 식별
어떤 쿼리
SELECT round(mean_exec_time::numeric, 1) AS mean_ms,
calls,
round(total_exec_time::numeric / 1000, 1) AS total_sec,
LEFT(query, 80) AS query
FROM pg_stat_statements
WHERE last_seen > now() - interval '1 hour' -- PG 17+
ORDER BY total_exec_time DESC
LIMIT 10;total_exec_time이 전체 부하의 큰 부분을 차지하는 쿼리부터.
언제부터
pg_stat_statements의 추세를 그래프로 — Grafana 또는 외부 모니터링. 갑작스러운 spike 시점 = 사고 시점.
추세가 없으면 application 로그·alert 시간으로 추정합니다.
얼마나 느려졌나
- 평균 30ms → 800ms = 27배
- p95·p99 latency 추세 비교
- application 측정 vs DB 측정 비교 (네트워크인지 DB인지 구분)
2단계 — plan 캡처
auto_explain 로그
# 사고 시간대 PG 로그
grep -A 50 "duration:" /var/lib/pgsql/17/data/log/postgresql-*.log | lessauto_explain이 켜져 있으면(10.6) 사고 시점 실제 plan이 로그에 남습니다.
수동 EXPLAIN
운영 트래픽 받는 쿼리는 재현하기 어렵습니다. 가능한 한:
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT json)
SELECT ...;UPDATE/DELETE는 트랜잭션으로 감싸 ROLLBACK.
plan 시각화
depesz·dalibo (5.4) — 느린 노드를 빠르게 발견.
3단계 — 원인 가설
5.4·5.5에서 본 4가지 우선 신호입니다.
| 신호 | 가설 | 대응 |
|---|---|---|
actual rows >> estimated rows | 통계 미스 | ANALYZE, CREATE STATISTICS |
Seq Scan 큰 테이블 | 인덱스 누락 | CREATE INDEX |
Sort Method: external merge | work_mem 부족 | SET work_mem |
Hash Batches > 1 | 같음 | 같음 |
loops 큰 Nested Loop | 잘못된 조인 | hash join 유도 |
Buffers: shared read >> | cache miss | hot 쿼리 인덱스·shared_buffers |
wait_event = LWLock | 공유 자원 경합 | 동시 부하 점검 |
추가 진단
-- 락 대기?
SELECT pid, query, pg_blocking_pids(pid)
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;
-- 통계 신선도
SELECT relname, n_mod_since_analyze, last_analyze, last_autoanalyze
FROM pg_stat_user_tables
WHERE relname IN ('orders', 'users');
-- 인덱스 BLOAT
-- (6.7의 pgstattuple 쿼리)
-- 시스템 부하
-- top, iostat, vmstat4단계 — 변경 (staging 먼저)
13.1의 순서대로 싼 도구부터:
- ANALYZE
- EXPLAIN 다시
- 인덱스 추가
- 쿼리 재작성
- work_mem 등 파라미터
- 파티셔닝·아키텍처
운영 환경에서는 staging 또는 standby에서 먼저 검증합니다.
5단계 — 검증 + 문서화
SELECT pg_stat_statements_reset();
-- (운영 1시간 또는 적절한 기간)
-- 다시 측정변경 효과:
| 측정 | 변경 전 | 변경 후 |
|---|---|---|
| mean_exec_time | 800ms | 30ms |
| total_exec_time | 100h/day | 5h/day |
| p95 | 1500ms | 50ms |
문서:
- 사고 일자·시간
- 영향 받은 쿼리·테이블
- 가설 + 검증 과정
- 변경 사항·날짜
- 효과 측정
다음 비슷한 사고에 절차서가 됩니다.
자주 보는 시나리오
Scenario A — autovacuum이 못 따라옴
plan: rows=10 → actual rows=1000000
last_autovacuum: 1주일 전
n_dead_tup: 50% of n_live_tup대응:
ANALYZE big_table;
-- + autovacuum_*_scale_factor를 0.02로
ALTER TABLE big_table SET (autovacuum_vacuum_scale_factor = 0.02);Scenario B — 인덱스 누락
Seq Scan on orders, rows=10M, Filter: (status = 'pending')
Rows Removed by Filter: 9M대응:
CREATE INDEX CONCURRENTLY ON orders(user_id) WHERE status = 'pending';
ANALYZE orders;Scenario C — 갑작스러운 plan 변경
어제까지 Index Scan, 오늘 Seq Scan원인 후보:
- ANALYZE 후 통계 변화 (selectivity 다름)
- 데이터 분포 큰 변화
- 새 데이터 INSERT 후
대응:
pg_stats로 분포 확인pg_hint_plan으로 임시 강제 + 통계 개선
Scenario D — work_mem 부족
Sort Method: external merge Disk: 256MB대응:
SET LOCAL work_mem = '512MB';
-- 또는 분석 사용자에 ALTER ROLE ... SET work_memScenario E — 락 대기
pg_stat_activity의 다른 backend가 같은 row UPDATE 중대응:
-- 블로커 식별
SELECT pid, query FROM pg_stat_activity
WHERE pid IN (SELECT unnest(pg_blocking_pids(<slow_pid>)));
-- 필요시 종료
SELECT pg_terminate_backend(<blocker_pid>);Scenario F — 트래픽 폭증
TPS는 평소 1000, 사고 시 5000
모든 쿼리가 균등하게 느림원인: connections·CPU·디스크 한계.
대응:
- pgBouncer pool 점검
- application 캐시 추가
- read replica 분리
운영 알람 임계
| 지표 | WARN |
|---|---|
| query mean p95 | 평소 대비 3x |
pg_stat_activity.active 수 | max_connections × 80% |
| dead_tup > live_tup × 0.2 | 큰 테이블 |
| disk %util | 80% |
| connection waiting | pgBouncer cl_waiting > 0 |
정리
- 5단계: 범위 → plan → 가설 → 변경 → 검증
- auto_explain·pg_stat_statements가 제일 큰 도움
- 4가지 우선 신호: rows 격차·Seq Scan·Sort spill·loops
- 시나리오별 대응을 절차서로 누적
- 변경은 staging 검증 후 운영
다음 절(15.3)에서는 락 대기·세션 폭주 — 락 대기·세션 문제를 봅니다.