PostgreSQL 실전 강좌 #3 성능 진단 루틴: pg_stat_statements로 느린 쿼리 찾기

인프라 PostgreSQL 데이터베이스
4 분 소요

기초 5편의 EXPLAIN은 “이 쿼리가 왜 느린가"에 답합니다. 그런데 실전의 질문은 한 단계 앞에서 옵니다. “DB가 느린데, 어느 쿼리가 문제인가.” 서버 전반이 느린 경우의 일반적인 병목 찾기는 서버가 느린 이유 시리즈에서 다뤘고, 이번 편은 그중 DB 층을 PostgreSQL의 도구로 파고드는 루틴입니다.

지금: pg_stat_activity #

“지금 이 순간 DB에서 무슨 일이 벌어지는가"의 답은 pg_stat_activity 뷰입니다. 지난 편에서 커넥션 상태를 셀 때 이미 썼습니다.

pg_stat_activity 조회 
SELECT pid, state, wait_event_type, wait_event,
       now() - query_start AS running_for,
       left(query, 60) AS query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY running_for DESC;

읽는 포인트는 세 가지입니다. 오래 도는 쿼리(running_for가 큰 행), 대기 중인 쿼리(wait_event_type이 Lock이면 잠금 대기, 실전 6편의 영역), 그리고 놓치기 쉬운 idle in transaction입니다. 트랜잭션을 열어 두고 아무것도 안 하는 상태로, 잠금을 쥔 채일 수 있고 VACUUM의 청소도 막습니다(실전 5편에서 다시 만납니다). 애플리케이션이 트랜잭션을 열고 외부 API를 기다리는 코드가 전형적인 범인입니다.

누적: pg_stat_statements #

순간의 스냅샷으로는 “가끔 느린” 문제를 못 잡습니다. 누적 통계가 필요하고, 그 표준이 pg_stat_statements 확장입니다. 같은 모양의 쿼리(파라미터만 다른)를 하나로 묶어, 호출 횟수와 시간을 계속 집계해 줍니다.

pg_stat_statements 상위 10개 
-- postgresql.conf: shared_preload_libraries = 'pg_stat_statements' 후 재시작
CREATE EXTENSION pg_stat_statements;

-- 총 시간 기준 상위 10개
SELECT calls,
       round(total_exec_time::numeric / 1000, 1) AS total_sec,
       round(mean_exec_time::numeric, 2)         AS mean_ms,
       rows,
       left(query, 60) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

재시작이 필요한 확장이라 미리 켜 두는 것이 중요합니다(장애가 난 뒤에 켜면 그날의 데이터는 없습니다). 관리형 서비스에서는 대부분 파라미터 그룹에서 켤 수 있습니다.

총합 상위와 평균 상위는 다른 범인입니다 #

정렬 기준에 따라 다른 문제가 보입니다.

  • total_exec_time 상위: DB 자원을 가장 많이 쓰는 쿼리. 평균 2ms라도 초당 수백 번 불리면 1위가 됩니다. DB 전체 부하를 낮추려면 이쪽부터 봅니다. 개선은 쿼리 튜닝만이 아니라 “덜 부르기”(캐싱, N+1 해소)인 경우도 많습니다.
  • mean_exec_time 상위: 한 번이 느린 쿼리. 사용자가 “이 화면이 느려요"라고 느끼는 것은 대개 이쪽입니다. 여기서 나온 쿼리를 EXPLAIN ANALYZE로 가져가는 것이 기초 편과의 연결 고리입니다.

rows / calls(호출당 행 수)도 훑어 둘 가치가 있습니다. 호출당 수만 행을 돌려주는 쿼리는 “DB가 느린” 것이 아니라 “너무 많이 가져오는” 문제일 수 있습니다.

로그: log_min_duration_statement #

세 번째 도구는 느린 쿼리 로그입니다.

postgresql.conf 
log_min_duration_statement = 500   # 500ms 넘는 쿼리를 로그에 남김

pg_stat_statements가 “묶어서 집계"라면, 로그는 실제 파라미터가 박힌 개별 실행을 남깁니다. “특정 사용자에서만 느리다” 같은 데이터 편중 문제는 이쪽이 잡습니다. 0으로 두면 전체 쿼리 로그라 부하가 크니, 500ms〜1s 정도의 문턱이 일반적입니다.

루틴으로 묶기 #

이제 진단은 이 순서의 루틴이 됩니다. ① 장애 중이면 pg_stat_activity로 지금을 봅니다(오래 도는 쿼리, 잠금 대기, idle in transaction). ② 평시 개선이면 pg_stat_statements를 총합과 평균 두 기준으로 뽑습니다. ③ 범인 쿼리를 EXPLAIN ANALYZE로 파고, 4편의 인덱스 체크리스트를 돌립니다. ④ 고친 뒤 pg_stat_statements 수치가 실제로 내려갔는지 확인합니다(SELECT pg_stat_statements_reset();으로 초기화하고 비교하면 깔끔합니다). 인덱스를 만들어도 해결되지 않는 다음 단계, 인덱스 전략의 심화가 다음 편입니다.

정리 #

  • 진단의 순서는 “어느 쿼리인가”(이번 편) → “왜 느린가”(EXPLAIN)입니다. 도구를 이 순서로 씁니다.
  • 지금의 문제는 pg_stat_activity입니다. 오래 도는 쿼리, Lock 대기, idle in transaction 세 가지를 봅니다.
  • 누적의 표준은 pg_stat_statements입니다. 재시작이 필요하니 장애 전에 미리 켜 둡니다.
  • 총합 상위는 DB 부하의 범인, 평균 상위는 사용자 체감의 범인입니다. 두 기준을 다 뽑습니다.
  • 로그(log_min_duration_statement)는 파라미터까지 남겨 데이터 편중 문제를 잡습니다. 다음 편은 인덱스 전략 심화입니다.
X