MySQL Slow Query Log 운영: 설정

들어가며: 슬로우 쿼리를 “나중에 보겠다”고 미루면 생기는 일

MySQL 운영에서 성능 문제는 대부분 소수의 느린 쿼리가 원인이다. 그런데 Slow Query Log를 켜지 않았거나, 켜놓고도 분석하지 않는 팀이 많다. 장애가 터진 뒤 SHOW PROCESSLIST로 급하게 범인을 찾는 것은 이미 늦은 대응이다.

이 글에서는 MySQL 8.0/8.4 공식 문서(The Slow Query Log, Server System Variables)를 근거로, Slow Query Log의 설정·분석·자동화까지 운영 관점에서 정리한다.

1. Slow Query Log 활성화: 핵심 변수 4가지

1-1. 필수 설정

1-2. 런타임 즉시 적용 (재시작 불필요)

-- 런타임에서 바로 켜기
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = 'ON';

-- 확인
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

MySQL 공식 문서에 따르면, 이 변수들은 GLOBAL과 SESSION 모두 동적으로 변경 가능하다. 단, 런타임 변경은 MySQL 재시작 시 초기화되므로 my.cnf에도 반영해야 한다.

1-3. my.cnf 영구 설정

[mysqld]
slow_query_log = 1
long_query_time = 1
log_queries_not_using_indexes = 1
slow_query_log_file = /var/log/mysql/slow.log
log_throttle_queries_not_using_indexes = 60

log_throttle_queries_not_using_indexes는 분당 기록할 인덱스 미사용 쿼리 수를 제한한다. 인덱스 없는 쿼리가 대량 발생할 때 로그 폭발을 방지한다(MySQL 공식 문서 Server System Variables).

2. 슬로우 쿼리 로그 포맷 읽기

2-1. 로그 엔트리 구조

# Time: 2026-02-19T08:15:32.456789Z
# User@Host: app_user[app_user] @ 10.0.0.100 [10.0.0.100] Id: 12345
# Query_time: 3.456789  Lock_time: 0.000123  Rows_sent: 1  Rows_examined: 1500000
SET timestamp=1771488932;
SELECT * FROM orders WHERE created_at > '2026-01-01' AND status = 'PENDING';

2-2. 핵심 필드 해석

핵심 지표: Rows_examined / Rows_sent 비율이 높을수록 비효율적인 쿼리다. 이상적으로는 1:1에 가까워야 한다.

3. mysqldumpslow: 빌트인 분석 도구

MySQL에 기본 포함된 mysqldumpslow는 슬로우 쿼리 로그를 요약 분석한다.

# 가장 느린 쿼리 Top 10
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# 가장 많이 실행된 느린 쿼리 Top 10
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log

# 정렬 옵션
# -s t : 총 실행 시간 순
# -s c : 실행 횟수 순
# -s l : 락 대기 시간 순
# -s r : 반환 행 수 순
# -s at : 평균 실행 시간 순

3-1. 출력 예시 해석

Count: 1523  Time=2.34s (3564s)  Lock=0.00s (0s)  Rows=1.0 (1523)
  SELECT * FROM orders WHERE status = 'S' AND created_at > 'S'

mysqldumpslow는 리터럴 값을 추상화하여 같은 패턴의 쿼리를 그룹화한다. 이를 통해 “어떤 쿼리 패턴이 전체 부하의 몇 %를 차지하는가”를 파악할 수 있다.

4. pt-query-digest: 고급 분석 (Percona Toolkit)

mysqldumpslow보다 상세한 분석이 필요하면 Percona Toolkit의 pt-query-digest를 사용한다.

# 설치 (Debian/Ubuntu)
apt-get install percona-toolkit

# 분석
pt-query-digest /var/log/mysql/slow.log

# 특정 시간 범위만 분석
pt-query-digest --since '2026-02-19 00:00:00' --until '2026-02-19 12:00:00' \
  /var/log/mysql/slow.log

4-1. pt-query-digest 출력 구조

출력은 세 부분으로 구성된다:

1. Overall summary: 전체 쿼리 수, 총 실행 시간, 시간당 QPS
2. Profile: 쿼리 패턴별 순위 (총 실행 시간 비율)
3. Query detail: 각 패턴의 실행 시간 분포(min/max/median/95th percentile), EXPLAIN 결과

# Profile 예시
# Rank  Query ID                      Response time   Calls  R/Call
# ==== ============================== =============== ====== ======
#    1  0xABCD1234567890AB             3564.00  45.2%   1523  2.34
#    2  0xEFGH5678901234CD             1890.00  24.0%    890  2.12
#    3  0xIJKL9012345678EF              980.00  12.4%   4500  0.22

Response time 비율이 높은 순서대로 최적화하는 것이 가장 효과적이다. 1위 쿼리만 최적화해도 전체 부하의 45%를 줄일 수 있다.

5. 슬로우 쿼리 → EXPLAIN → 인덱스 설계 워크플로

5-1. 단계별 흐름

1. 슬로우 쿼리 로그에서 상위 쿼리 추출: mysqldumpslow -s t -t 5
2. EXPLAIN으로 실행 계획 확인: type, Extra, rows 확인
3. 인덱스 설계/수정: WHERE → ORDER BY → SELECT 순서로 복합 인덱스 컬럼 배치
4. EXPLAIN ANALYZE로 실측 검증: 변경 전후 actual time 비교
5. 배포 후 슬로우 쿼리 로그 재확인: 해당 패턴이 사라졌는지 확인

5-2. 예시: 슬로우 쿼리 최적화

-- 슬로우 쿼리 로그에서 발견된 쿼리
-- Query_time: 3.45  Rows_examined: 1500000  Rows_sent: 23
SELECT order_id, total_amount, created_at
FROM orders
WHERE status = 'PENDING'
  AND created_at > '2026-01-01'
ORDER BY created_at DESC
LIMIT 20;

-- EXPLAIN 결과: type=ALL, rows=1500000, Extra=Using where; Using filesort

-- 인덱스 추가
ALTER TABLE orders ADD INDEX idx_status_created (status, created_at);

-- EXPLAIN 결과: type=range, rows=23, Extra=Using index condition
-- Query_time: 0.002초로 개선

6. 운영 자동화: 로그 로테이션과 모니터링

6-1. 로그 로테이션

슬로우 쿼리 로그가 무한히 커지는 것을 방지해야 한다.

# /etc/logrotate.d/mysql-slow
/var/log/mysql/slow.log {
    daily
    rotate 14
    compress
    delaycompress
    missingok
    notifempty
    create 640 mysql mysql
    postrotate
        /usr/bin/mysqladmin flush-logs
    endscript
}

flush-logs는 MySQL이 로그 파일을 닫고 새 파일을 열게 한다. 이것이 없으면 MySQL은 삭제된 파일에 계속 쓴다.

6-2. Performance Schema 연계

MySQL 8.0+에서는 Performance Schema의 events_statements_summary_by_digest 테이블로 슬로우 쿼리 로그 없이도 쿼리 통계를 조회할 수 있다:

SELECT
  DIGEST_TEXT,
  COUNT_STAR AS exec_count,
  ROUND(SUM_TIMER_WAIT / 1e12, 2) AS total_sec,
  ROUND(AVG_TIMER_WAIT / 1e12, 4) AS avg_sec,
  SUM_ROWS_EXAMINED,
  SUM_ROWS_SENT
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

Performance Schema는 메모리 기반이므로 디스크 I/O 부담이 없다. 슬로우 쿼리 로그와 함께 사용하면 로그 파일(상세 분석)과 실시간 집계(대시보드)를 모두 확보할 수 있다.

6-3. sys 스키마 활용

-- 가장 비용이 큰 쿼리 Top 10 (sys 스키마)
SELECT * FROM sys.statements_with_runtimes_in_95th_percentile
LIMIT 10;

-- 인덱스를 사용하지 않는 쿼리
SELECT * FROM sys.statements_with_full_table_scans
ORDER BY no_index_used_count DESC
LIMIT 10;

-- 사용되지 않는 인덱스
SELECT * FROM sys.schema_unused_indexes;

7. 실전 체크리스트

1. Slow Query Log 활성화: slow_query_log = ON, long_query_time = 1 (또는 팀 SLA 기준)으로 설정했는가?
2. 인덱스 미사용 쿼리 기록: log_queries_not_using_indexes = ON을 켰는가? log_throttle_queries_not_using_indexes로 로그 폭발을 방지했는가?
3. 정기 분석 루틴: 주 1회 이상 mysqldumpslow 또는 pt-query-digest로 상위 쿼리를 분석하는가?
4. Rows_examined/Rows_sent 비율: 비율이 100:1 이상인 쿼리를 인덱스 최적화 대상으로 분류하는가?
5. 로그 로테이션: logrotate + flush-logs로 로그 파일 크기를 관리하는가?
6. Performance Schema 대시보드: events_statements_summary_by_digest를 Grafana 등에 연결하여 실시간 모니터링하는가?

8. 흔한 실수와 방지법

정리

MySQL Slow Query Log는 성능 문제의 사전 탐지와 지속적 개선을 위한 가장 기본적인 도구다. 핵심 워크플로는 단순하다: 로그 켜기 → 정기 분석(mysqldumpslow/pt-query-digest) → EXPLAIN → 인덱스 설계 → EXPLAIN ANALYZE 검증. 여기에 Performance Schema/sys 스키마 실시간 모니터링을 더하면, 대부분의 MySQL 성능 문제를 장애 전에 발견하고 해결할 수 있다.

참고 자료

• MySQL 8.4 공식 문서 — The Slow Query Log
• MySQL 8.4 공식 문서 — Server System Variables
• MySQL 8.4 공식 문서 — Statement Digests and Performance Schema
• MySQL 8.4 공식 문서 — sys Schema
• Percona Toolkit 공식 문서 — pt-query-digest