Mysql 슬로우 쿼리 로그 설정은 어떻게 하나요?

💡 요약 정리

MySQL my.cnf에 log-slow-queries와 long_query_time을 설정해 임계 시간 이상 걸리는 쿼리를 로그로 남길 수 있습니다.
수집된 슬로우 쿼리 로그는 자주 등장하는 패턴 추출, Rows_examined/Rows_sent 비율 점검, EXPLAIN 실행계획 확인, 인덱스 추가 또는 키셋 페이지네이션 전환 순으로 분석하면 효과적입니다.

1. 설치 환경 정보

운영체제: CentOS 5.x (64bit)
데이터베이스: mysql 5.1.59 설치 위치: /home/APM/mysql

2. 슬로우 쿼리란?

일정 시간을 초과하는 쿼리에 대해 로그를 남김으로써 로그 분석을 통해 쿼리 성능을 개선해 나갈 수 있습니다.

3. 슬로우 쿼리 로그 활성화 방법

/etc/my.cnf 파일에 다음 내용을 추가합니다.
아래 설정은 3초 이상 지속되는 쿼리를 슬로우 쿼리 로그에 저장합니다.

[root@cafe24 ~]# vi /etc/my.cnf
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
# The MySQL server
[mysqld]
port           = 3306
socket         = /tmp/mysql.sock
skip-locking
key_buffer_size = 384M
max_allowed_packet = 1M
table_open_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8
max_connections = 2048

#default-character-set=euckr
#character-set-client-handshake = FALSE

# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
#
#skip-networking

# slow qeury log
log-slow-queries = /home/APM/mysql/var/mysql-slow.log         <-- 추가합니다
long_query_time = 3

# Replication Master Server (default)
# binary logging is required for replication
log-bin=mysql-bin
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------

설정을 완료한 후, MySQL을 재시작해야 적용됩니다.

[root@cafe24 ~]# /etc/rc.d/init.d/mysql restart
Shutting down MySQL.                                [  OK  ]
Starting MySQL.                                     [  OK  ]
[root@cafe24 ~]#

4. 슬로우 쿼리 로그 항목 설명

* 슬로우 쿼리 로그에는 다음 항목들이 포함됩니다.

Time: 쿼리 요청 시간
User@Host: 쿼리를 요청한 사용자와 호스트 정보
Query_time: 실제 수행된 시간
Lock_time: 락이 걸린 시간
Rows_sent: 쿼리 결과로 반환된 행(Row)의 수
Rows_examined: 쿼리에서 검사한 총 행(Row)의 수
쿼리문: 로그 마지막 줄에 실제 실행된 SQL 쿼리 문이 표시됨

5. 슬로우 쿼리 로그 분석 및 진단 가이드

* 슬로우 쿼리 로그 분석 및 진단 가이드

1) 로그 파일에서 자주 등장하는 쿼리 패턴 추출

슬로우 쿼리 로그(예: /home/APM/mysql/var/mysql-slow.log)에서 동일/유사 쿼리가 반복되어 기록되는 경우, 해당 쿼리가 성능 병목의 주요 원인일 수 있습니다.
쿼리문 + 대상 테이블 단위로 묶어서 가장 자주 등장하거나 누적 수행시간이 큰 쿼리를 우선 점검 대상으로 선정합니다.
일별/시간대별 발생 추이를 함께 확인하면 특정 시간대 부하(예: 야간 배치, 광고 유입 시간대)와 연관된 쿼리도 식별할 수 있습니다.

2) Rows_examined / Rows_sent 비율로 비효율 쿼리 식별

Rows_examined(쿼리가 검사한 전체 행 수) ÷ Rows_sent(실제 반환 행 수) 비율이 클수록 풀스캔(Full Table Scan)에 가깝다는 신호입니다.
예: Rows_examined=1,000,000 / Rows_sent=10 → 10만 배 비효율 → WHERE 절에 인덱스 적용 필요.
Rows_examined가 테이블 전체 행수와 비슷하면, 해당 WHERE/ORDER BY/JOIN 조건에 적합한 인덱스가 없는 상태일 가능성이 높습니다.

3) EXPLAIN으로 실행계획 확인 및 인덱스 권고

의심 쿼리 앞에 EXPLAIN 을 붙여 실행계획을 확인합니다.
점검 포인트:
- type: ALL(풀스캔), index(전체 인덱스 스캔)로 표시되면 인덱스 최적화 여지 있음. ref/range/const/eq_ref면 비교적 양호.
- key: NULL이면 인덱스 미사용 → WHERE/JOIN 컬럼에 인덱스 추가 검토.
- rows: 실제 반환 대비 과도하게 크면 인덱스 선택도(selectivity)가 낮은 컬럼임. 복합 인덱스 컬럼 순서를 재검토합니다.
- Extra: "Using filesort", "Using temporary"가 보이면 ORDER BY/GROUP BY 컬럼에 인덱스 또는 키셋 페이지네이션(Keyset Pagination) 적용을 검토합니다.

4) 키셋 페이지네이션(Keyset Pagination) 권고

대용량 테이블에서 LIMIT offset, count 방식(OFFSET 페이지네이션)은 offset 값이 커질수록 누적 스캔 행수가 급격히 증가하여 슬로우 쿼리로 누적됩니다.
정렬 키(보통 PK 또는 단조 증가 ID/timestamp)를 기준으로 WHERE id > {마지막ID} ORDER BY id ASC LIMIT N 형태의 키셋 방식으로 전환하면 OFFSET 부하 없이 일정한 성능을 유지할 수 있습니다.
무한 스크롤/페이지네이션 API에서 슬로우 쿼리 로그에 같은 SELECT 가 반복 등장하면 이 패턴 전환을 우선 검토합니다.

5) 처리 우선순위

(a) 호출 빈도 × 평균 Query_time 이 큰 쿼리부터 → (b) Rows_examined 대비 Rows_sent 비율이 큰 쿼리 → (c) ORDER BY/GROUP BY로 Filesort/Temporary 가 발생하는 쿼리 순으로 점검합니다.
단순히 long_query_time 임계값을 낮추는 것만으로는 로그가 폭증하여 분석이 어려워지므로, 위 우선순위에 따른 분석과 인덱스/쿼리 리팩토링이 함께 진행되어야 효과적입니다.

문제가 해결되지 않았나요?

슬로우 쿼리 분석 결과 인덱스 추가, 쿼리 리팩토링, 서버 사양 조정 등이 필요하신 경우 1:1 문의게시판으로 문의해 주시면 안내해 드리겠습니다.