동블

그래프 뷰

05. Slow Query와 운영 진단 상세

9 min read

이 문서를 통해 아래 질문들에 답할 수 있게 됩니다.

  • 운영에서 slow query는 어떻게 수집하고 해석해야 하는가?
  • pg_stat_activity, pg_stat_statements, Performance Schema는 어떤 역할인가?
  • 성능 진단 결과를 백엔드 개선 작업으로 어떻게 연결하는가?

개요

운영 성능 진단은 느린 쿼리 하나를 찾는 작업이 아니다. 어떤 SQL이 자주 느린지, 어떤 wait가 많은지, 어떤 배포 이후 지표가 바뀌었는지, 어떤 API와 연결되는지 보는 일이다.

백엔드 개발자는 DB 지표를 애플리케이션 코드 경로로 되돌려 연결할 수 있어야 한다.

원리

운영 성능 진단의 원리는 SQL 하나를 고립해서 보는 것이 아니라, API 요청이 DB 자원을 어떻게 소비했는지 시간순으로 연결하는 것이다. 같은 slow query라도 full scan, lock wait, pool wait, replica lag, 통계 부정확, 배포 후 호출량 증가 중 무엇이 원인인지에 따라 대응이 달라진다.

백엔드 개발자는 DB 도구의 결과를 코드 변경으로 번역해야 한다. pg_stat_statements의 fingerprint, APM trace, Repository 메서드, 배포 diff, 사용자 영향 범위가 하나로 이어질 때 실전 개선이 가능하다.

관측 도구

PostgreSQL에서 자주 보는 도구:

  • pg_stat_activity: 현재 세션과 실행 중인 query
  • pg_stat_statements: query fingerprint별 누적 통계
  • EXPLAIN: 실행 계획
  • slow query log: 오래 걸린 SQL 로그

MySQL에서 자주 보는 도구:

  • Performance Schema
  • slow query log
  • EXPLAIN
  • SHOW PROCESSLIST

클라우드에서는 AWS RDS Performance Insights, Cloud SQL Query Insights 같은 도구도 쓴다.

현재 상태 확인

PostgreSQL에서 현재 오래 실행 중인 쿼리를 볼 수 있다.

SELECT pid, now() - query_start AS duration, state, wait_event_type, wait_event, query
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY duration DESC;

이 쿼리는 장애 중 단서를 제공한다. 그러나 실제 운영에서는 권한과 부하를 고려해 DBA와 함께 사용한다.

누적 통계 확인

pg_stat_statements는 query fingerprint별 누적 실행 시간, 호출 수, 평균 시간을 볼 수 있다.

핵심은 총 시간과 평균 시간을 함께 보는 것이다.

query A: 평균 5초, 하루 10회
query B: 평균 50ms, 하루 500만 회

둘 다 중요할 수 있다. 장애와 비용은 평균 시간뿐 아니라 호출 빈도에서 나온다.

PostgreSQL에서는 다음처럼 총 시간을 많이 쓴 쿼리부터 볼 수 있다.

SELECT query,
       calls,
       total_exec_time,
       mean_exec_time,
       rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

이 결과를 그대로 “나쁜 쿼리 순위”로 보면 안 된다. 호출량이 많은 핵심 API인지, batch인지, 최근 배포 후 새로 늘었는지, lock wait가 포함된 시간인지 함께 봐야 한다.

애플리케이션 연결

Slow query를 찾았으면 코드 경로로 연결한다.

  • 어떤 Repository/DAO가 만드는가?
  • 어떤 endpoint에서 호출되는가?
  • 어떤 파라미터에서 느린가?
  • 배포 이후 호출량이 늘었는가?
  • count 쿼리나 N+1이 포함되는가?

SQL만 고치면 안 되고 API 계약을 바꿔야 할 수도 있다. 예를 들어 전체 count 제거, cursor pagination, 검색 기간 제한, 비동기 export가 더 나은 해결일 수 있다.

문제 SQL을 코드로 연결할 수 없으면 운영 대응이 느려진다. 다음처럼 애플리케이션 로그나 APM tag에 SQL 출처를 남기는 전략을 검토한다.

endpoint=GET /api/orders
repository=OrderRepository.findSummaries
query_fingerprint=orders_by_member_created_desc
page_size=20

민감한 bind 값은 그대로 남기지 않는다. 대신 파라미터 유형, 범위, cardinality를 알 수 있는 안전한 태그를 쓴다.

진단 순서

느린 API가 보고되면 다음 순서로 좁힌다.

  1. 전체 API 장애인지 특정 endpoint인지 확인한다.
  2. 애플리케이션 p95/p99와 에러율 변화를 본다.
  3. pool acquire time과 active/pending을 본다.
  4. DB CPU/I/O, active session, wait event를 본다.
  5. slow query fingerprint와 호출량 변화를 본다.
  6. 실행 계획과 실제 row 수를 확인한다.
  7. 최근 배포, migration, batch, 이벤트 트래픽을 겹쳐본다.

이 순서를 지키면 DB CPU가 낮은 lock 장애를 CPU 증설로 해결하려 하거나, pool 고갈의 원인을 단순 pool 크기로만 보는 실수를 줄일 수 있다.

진단 결과를 작업으로 만들기

성능 진단은 다음 작업으로 연결한다.

  • 인덱스 추가 또는 정리
  • 쿼리 재작성
  • fetch 전략 수정
  • pagination 변경
  • batch 분리
  • timeout과 pool 조정
  • 캐시 도입
  • 알림 기준 추가

각 작업에는 검증 방법이 있어야 한다. 실행 계획 전후 비교, p95 개선, rows 감소, lock wait 감소처럼 측정 가능한 기준을 둔다.

실전 팁

  • slow query는 호출 빈도와 총 시간을 함께 본다.
  • SQL fingerprint와 endpoint를 연결하는 태그나 로그가 있어야 slow query를 코드 경로와 배포 이력으로 빠르게 되돌릴 수 있다.
  • p95/p99를 평균보다 중요하게 본다.
  • 배포 전후 DB 지표 비교를 습관화한다.
  • 운영 진단 결과는 문서와 PR로 남긴다.
  • EXPLAIN ANALYZE는 실제 실행을 동반하므로 운영에서는 부하와 변경 쿼리 여부를 확인하고 신중히 사용한다.
  • SQL fingerprint와 endpoint를 연결할 수 없다면 먼저 관측 구조를 개선한다.
  • 개선안은 인덱스, 쿼리, API 계약, 캐시, 배치 분리 중 무엇인지 분류한다.

위험 신호!

  • slow query log를 켜지 않았거나 아무도 보지 않는다.
  • 쿼리 개선 후 실행 계획 전후 비교가 없다.
  • DB 지표와 애플리케이션 trace가 연결되지 않는다.
  • 평균 응답 시간만 보고 p99 장애를 놓친다.
  • 성능 개선을 인덱스 추가로만 생각한다.
  • lock wait로 느려진 쿼리를 full scan 문제로 오해한다.
  • EXPLAIN ANALYZE를 운영 write 쿼리에 무심코 실행한다.
  • 어떤 배포 뒤에 호출량과 query shape가 바뀌었는지 기록이 없다.

확인 질문

  • Slow query 분석에서 평균 시간만 보면 부족한 이유는 무엇인가?
    • 호출 빈도가 높은 짧은 쿼리가 총 부하를 더 크게 만들 수 있고, p95/p99 지연이 사용자 경험을 결정하기 때문이다.
  • DB query를 백엔드 코드와 연결해야 하는 이유는 무엇인가?
    • 어떤 endpoint와 Repository가 문제를 만드는지 알아야 쿼리, 인덱스, API 계약을 실제로 고칠 수 있기 때문이다.
  • 성능 개선 후 검증해야 할 것은 무엇인가?
    • 실행 계획, 읽은 row 수, 응답 시간 분포, DB 지표, lock wait, pool 지표가 개선됐는지 확인해야 한다.
  • Slow query를 코드 경로와 연결하기 위해 어떤 태그가 도움이 되는가?
    • endpoint, Repository/DAO 이름, query fingerprint, page size, 주요 조건 유형, 배포 버전 같은 태그가 도움이 된다.

참고 문서

관련 문서