느린 쿼리 분석: slow log, pg_stat_statements, Performance Schema

느린 쿼리는 발견하기 전까지 숨어있다

데이터베이스 성능 문제의 70% 이상은 특정 쿼리 패턴에서 시작된다. 그러나 쿼리가 느리다는 사실이 드러나는 건 대부분 장애가 터진 뒤다. 느린 쿼리를 일상적으로 모니터링하고 분석하는 체계가 없으면, 트래픽이 증가하거나 데이터가 쌓일 때마다 예상치 못한 곳에서 지연이 터져 나온다.

세 가지 도구가 이 문제를 다루는 핵심 인터페이스다.

1. MySQL Slow Query Log: 설정한 임계값을 초과한 쿼리를 파일로 기록한다. 가장 오래되고 범용적이다.
2. PostgreSQL pg_stat_statements: 정규화된 쿼리 단위로 실행 통계를 집계한다. SQL로 조회한다.
3. MySQL Performance Schema: 메모리 내 계측 엔진으로 실시간 쿼리 요약을 제공한다.

세 도구는 목적이 다르고, 함께 쓸 때 더 강력하다.

MySQL Slow Query Log

설정

Slow query log는 long_query_time을 초과하는 쿼리를 파일이나 테이블에 기록한다. 가장 먼저 활성화해야 하는 진단 도구다.

-- 현재 설정 확인
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- 동적으로 활성화 (재시작 불필요, 세션 수준)
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;          -- 1초 초과하면 기록
SET GLOBAL log_queries_not_using_indexes = 'ON';  -- 인덱스 미사용 쿼리도 기록
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

my.cnf에 영구 설정하려면:

[mysqld]
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = ON
log_slow_admin_statements = ON   # ALTER, OPTIMIZE 같은 관리 쿼리도 포함

Slow query log 항목 구조

실제 로그 항목 하나의 구조다.

# Time: 2026-06-27T03:14:22.000000Z
# User@Host: app[app] @ [10.0.0.5]  Id: 1234
# Query_time: 4.321678  Lock_time: 0.000123  Rows_sent: 1  Rows_examined: 890432
SET timestamp=1750989262;
SELECT * FROM orders WHERE status = 'PENDING' ORDER BY created_at DESC LIMIT 10;

핵심 필드:

Rows_examined가 Rows_sent보다 100배 이상이면 인덱스를 타지 못하거나 cardinality 추정 오류를 의심한다.

분석 도구: mysqldumpslow와 pt-query-digest

# mysqldumpslow: 쿼리 패턴별로 그룹화, 평균 시간 기준 정렬
mysqldumpslow -s at -t 20 /var/log/mysql/slow.log

# pt-query-digest (Percona Toolkit): 더 상세한 집계
pt-query-digest /var/log/mysql/slow.log \
  --limit 20 \
  --report-format query_report

pt-query-digest는 같은 패턴의 쿼리를 정규화해 묶고, 총 실행 횟수·총 시간·최대·최소·평균을 출력한다. 로그에 수백만 항목이 있어도 가장 위험한 패턴 20개를 빠르게 찾아준다.

PostgreSQL pg_stat_statements

설치와 설정

pg_stat_statements는 PostgreSQL의 공식 확장이다. 모든 쿼리를 정규화된 형태로 집계하며 SQL로 조회할 수 있다.

-- postgresql.conf에 추가 (서버 재시작 필요)
-- shared_preload_libraries = 'pg_stat_statements'
-- pg_stat_statements.max = 10000
-- pg_stat_statements.track = all

-- 확장 활성화 (DB별)
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- 정상 동작 확인
SELECT count(*) FROM pg_stat_statements;

핵심 쿼리 패턴

-- 1. 총 실행 시간 기준 Top 20 느린 쿼리
SELECT
    left(query, 120)             AS query_sample,
    calls,
    round(mean_exec_time::numeric, 2) AS avg_ms,
    round(total_exec_time::numeric, 2) AS total_ms,
    round(stddev_exec_time::numeric, 2) AS stddev_ms,
    rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

-- 2. 평균 실행 시간이 오래 걸리는 쿼리 (호출 빈도 낮아도 느린 것)
SELECT
    left(query, 120)             AS query_sample,
    calls,
    round(mean_exec_time::numeric, 2) AS avg_ms,
    round(max_exec_time::numeric, 2)  AS max_ms
FROM pg_stat_statements
WHERE calls > 10
ORDER BY mean_exec_time DESC
LIMIT 20;

-- 3. 스캔 효율 문제 쿼리 (rows returned vs rows examined 비율)
SELECT
    left(query, 120) AS query_sample,
    calls,
    rows / nullif(calls, 0)                AS avg_rows_returned,
    round(mean_exec_time::numeric, 2)      AS avg_ms
FROM pg_stat_statements
WHERE calls > 100
  AND rows / nullif(calls, 0) > 10000   -- 평균 1만 행 이상 반환
ORDER BY mean_exec_time DESC;

-- 4. 통계 초기화 (배포 후 깨끗한 기준선 확보)
SELECT pg_stat_statements_reset();

주요 컬럼 설명

shared_blks_read가 높고 shared_blks_hit가 낮은 쿼리는 캐시 미스가 많아 I/O 병목이 있다.

PostgreSQL 14부터는 pg_stat_statements_info 뷰에서 마지막 초기화 시각(stats_reset)을 확인할 수 있다.

MySQL Performance Schema

Slow Query Log과 Performance Schema의 차이

Performance Schema로 느린 쿼리 찾기

MySQL의 sys 스키마는 Performance Schema 위에 사람이 읽기 좋은 뷰를 제공한다.

-- sys 스키마 활용 (MySQL 5.7.7+)
-- 총 실행 시간 기준 Top 쿼리
SELECT
    digest_text                             AS query,
    exec_count                              AS calls,
    round(avg_latency / 1000000, 2)         AS avg_ms,
    round(total_latency / 1000000000, 2)    AS total_sec,
    rows_examined_avg,
    rows_sent_avg
FROM sys.x$statement_analysis
ORDER BY total_latency DESC
LIMIT 20;

-- Performance Schema 직접 조회
SELECT
    LEFT(digest_text, 100)                  AS query,
    count_star                              AS calls,
    round(avg_timer_wait / 1000000000, 3)   AS avg_sec,
    sum_rows_examined,
    sum_rows_sent,
    sum_created_tmp_disk_tables             AS disk_tmp_tables,
    sum_sort_merge_passes                   AS sort_merges
FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC
LIMIT 20;

sum_created_tmp_disk_tables가 크면 정렬이나 그룹핑에서 디스크 임시 테이블이 생성된다는 신호다. tmp_table_size와 max_heap_table_size를 늘리거나, 쿼리를 재작성해 인덱스 정렬을 활용해야 한다.

현재 실행 중인 느린 쿼리 즉시 확인

-- 현재 실행 중인 쿼리 중 1초 이상 걸리는 것
SELECT
    id,
    user,
    host,
    db,
    command,
    time,
    state,
    left(info, 200) AS query
FROM information_schema.processlist
WHERE command != 'Sleep'
  AND time > 1
ORDER BY time DESC;

-- PostgreSQL: 활성 쿼리 모니터링
SELECT
    pid,
    now() - pg_stat_activity.query_start AS duration,
    query,
    state,
    wait_event_type,
    wait_event
FROM pg_stat_activity
WHERE state != 'idle'
  AND (now() - pg_stat_activity.query_start) > interval '1 second'
ORDER BY duration DESC;

느린 쿼리 분석 실전 워크플로우

체크리스트

느린 쿼리 분석 시 확인 순서:

1. slow query log 또는 pg_stat_statements에서 총 시간 기준 Top 10 쿼리를 뽑는다.
2. Rows_examined / Rows_sent 비율이 100 이상인 쿼리에 집중한다.
3. Lock_time이 Query_time의 30% 이상이면 잠금 경합을 의심한다.
4. EXPLAIN ANALYZE로 실제 rows와 estimated rows를 비교한다.
5. 인덱스를 타지 못한 노드(Sequential Scan / Full Table Scan)의 원인을 확인한다.
6. 통계가 오래됐으면 ANALYZE / ANALYZE TABLE로 갱신한다.
7. 개선 후 기준선을 초기화하고 동일 부하에서 avg_ms를 재측정한다.

운영 주의사항

long_query_time을 너무 낮게 설정하지 마라. 0.1초로 설정하면 로그가 폭발적으로 쌓여 디스크가 가득 찬다. 처음에는 1초로 시작하고, 주요 쿼리가 모두 잡히면 0.5초로 낮춘다.

pg_stat_statements.max는 넉넉하게. 기본값 5000이면 고다양성 쿼리 워크로드에서 일부 쿼리가 NULL digest 행으로 묶인다. 10000으로 설정하는 것을 권장한다.

배포 직후 slow log를 확인하라. 새 쿼리 패턴이 등장하는 시점이 배포 직후다. 배포 전 통계를 초기화하고 배포 후 20분 내 Top 10을 확인한다.

References

• PostgreSQL Documentation 18, "F.32. pg_stat_statements" — https://www.postgresql.org/docs/current/pgstatstatements.html
• MySQL 8.4 Reference Manual, "The Slow Query Log" — https://dev.mysql.com/doc/refman/8.4/en/slow-query-log.html
• MySQL 8.4 Reference Manual, "Performance Schema Statement Digests and Sampling" — https://dev.mysql.com/doc/refman/8.4/en/performance-schema-statement-digests.html
• Percona Blog, "PERFORMANCE_SCHEMA vs Slow Query Log" — https://www.percona.com/blog/performance_schema-vs-slow-query-log/
• Percona Blog, "MySQL Query Digest with Performance Schema" — https://www.percona.com/blog/mysql-query-digest-with-performance-schema/
• pganalyze, "Enabling pg_stat_statements" — https://pganalyze.com/docs/install/01_enabling_pg_stat_statements
• virtual-dba.com, "Mastering pg_stat_statements in PostgreSQL" — https://virtual-dba.com/blog/mastering-pgstatstatements-in-postgresql/
• DEV Community – Philip McClarence, "PostgreSQL Slow Query Log: Finding & Fixing Your Slowest Queries" — https://dev.to/philip_mcclarence_2ef9475/postgresql-slow-query-log-finding-fixing-your-slowest-queries-2n4d
• Severalnines, "How to Identify MySQL Performance Issues with Slow Queries" — https://severalnines.com/blog/how-identify-mysql-performance-issues-slow-queries/