MySQL 슬로우 쿼리 로그로 페이지 속도를 잡아먹는 쿼리를 찾아냅니다

MySQL 슬로우 쿼리 로그 설정과 분석으로 페이지 속도 문제의 원인을 찾은 건 병원 예약 목록 페이지가 유독 느린데도 GTmetrix에서는 파일 크기나 캐시 문제가 없었을 때입니다. 슬로우 쿼리 로그를 켜보니 wp_postmeta 테이블에 인덱스가 없는 meta_key로 전체 스캔하는 쿼리가 초당 수십 번 실행되고 있었습니다.

슬로우 쿼리 로그 활성화

MySQL 설정 파일 수정:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld]
# 슬로우 쿼리 로그 활성화
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log

# 1초 이상 걸리는 쿼리만 기록
long_query_time = 1

# 인덱스 미사용 쿼리도 기록
log_queries_not_using_indexes = 1

# 관리 쿼리(SHOW, EXPLAIN)는 제외
log_slow_admin_statements = 0

sudo systemctl restart mysql

# 설정 확인
mysql -e "SHOW VARIABLES LIKE '%slow%';"

슬로우 쿼리 분석

# 로그 직접 확인
sudo tail -f /var/log/mysql/slow.log

# mysqldumpslow로 통계 분석
sudo mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
# -s t: 실행 시간 기준 정렬
# -t 10: 상위 10개

# pt-query-digest 설치 (더 상세한 분석)
sudo apt install percona-toolkit
sudo pt-query-digest /var/log/mysql/slow.log | head -100

문제 쿼리 개선

슬로우 쿼리 발견 시 원인 분석:

-- 쿼리 실행 계획 확인
EXPLAIN SELECT * FROM wp_postmeta WHERE meta_key = 'reservation_date';

-- type이 'ALL'이면 풀 테이블 스캔 (문제)
-- type이 'ref' 또는 'range'이면 인덱스 사용 (정상)

-- 인덱스 추가
ALTER TABLE wp_postmeta ADD INDEX idx_meta_key (meta_key(20));

-- 워드프레스 기본 인덱스 복구
mysql wp_database < /usr/share/wordpress/wp-admin/install.php

슬로우 쿼리 로그는 평소에 켜두고 주 1회 mysqldumpslow로 점검하는 루틴을 만들면 DB 성능 저하를 조기에 발견할 수 있습니다.