MySQL 느린 쿼리 로그 설정

MySQL 데이터베이스의 성능 문제를 식별하고 해결하는 데 있어 느린 쿼리 로그 설정과 실시간 스레드 분석은 필수적인 도구입니다. 이 가이드는 느린 SQL을 효과적으로 찾아내고, 데이터베이스의 스레드 상태를 모니터링하며, 높은 부하 상황을 진단하는 방법을 안내합니다.

1.1 임시 활성화 (런타임 중)

데이터베이스 재시작 없이 일시적으로 성능 문제를 진단해야 할 때 사용합니다. 서버가 재시작되면 설정은 초기화됩니다.

-- 1. 느린 쿼리 로깅 기능 활성화
SET GLOBAL slow_query_log = 'ON';

-- 2. 느린 쿼리 기준 시간 설정 (단위: 초)
-- 예를 들어, 2초보다 오래 걸리는 쿼리를 기록
SET GLOBAL long_query_time = 2;

-- 3. (선택 사항) 인덱스를 사용하지 않는 쿼리도 기록
SET GLOBAL log_queries_not_using_indexes = 'ON';

참고: long_query_time 설정은 새롭게 생성되는 연결에만 적용됩니다. 현재 세션에서 이 설정을 즉시 적용하려면 SET long_query_time = 2;와 같이 현재 세션에 대해서도 설정해야 합니다.

1.2 영구 활성화 (설정 파일 편집)

장기적인 성능 모니터링을 위해 my.cnf (리눅스) 또는 my.ini (윈도우) 파일을 수정하여 설정을 영구적으로 적용합니다.

[mysqld] 섹션에 다음 내용을 추가하거나 수정합니다.

[mysqld]
# 느린 쿼리 로그 활성화
slow_query_log = 1

# 느린 쿼리 로그 파일 경로 (절대 경로 권장)
slow_query_log_file = /var/log/mysql/mysql_slow.log

# 느린 쿼리 기준 시간 (초)
long_query_time = 2

# (선택 사항) 인덱스 미사용 쿼리 기록
log_queries_not_using_indexes = 1

설정 파일을 수정한 후에는 MySQL 서비스를 재시작해야 합니다:

systemctl restart mysqld

1.3 설정 확인

다음 SQL 명령을 실행하여 느린 쿼리 로그 설정이 올바르게 적용되었는지 확인합니다.

SHOW GLOBAL VARIABLES LIKE '%slow_query%';
SHOW GLOBAL VARIABLES LIKE 'long_query_time';

2. 느린 쿼리 로그 분석

느린 쿼리 로그는 텍스트 파일로 직접 확인할 수도 있지만, MySQL이 제공하는 mysqldumpslow 유틸리티를 사용하면 더욱 효과적으로 집계 및 분석할 수 있습니다.

2.1 mysqldumpslow 주요 파라미터

• -s <정렬 기준>: 결과 정렬 방식 지정
  • c: 쿼리 실행 횟수 (Count)
  • t: 총 실행 시간 (Time)
  • l: 잠금 시간 (Lock time)
  • r: 반환된 레코드 수 (Rows)
  • at: 평균 실행 시간 (Average Time)
• -t <숫자>: 상위 N개 쿼리만 표시 (Top N)
• -g <정규 표현식>: 특정 패턴을 포함하는 쿼리 필터링 (Grep)

2.2 분석 명령 예시

터미널(Shell)에서 다음 명령들을 실행합니다.

# 1. 총 실행 시간이 가장 긴 상위 10개 쿼리 확인
mysqldumpslow -s t -t 10 /var/log/mysql/mysql_slow.log

# 2. 가장 자주 실행된 상위 10개 느린 쿼리 확인
mysqldumpslow -s c -t 10 /var/log/mysql/mysql_slow.log

# 3. 'product_info' 테이블을 포함하며 평균 실행 시간이 긴 상위 5개 쿼리 확인
mysqldumpslow -s at -t 5 -g "product_info" /var/log/mysql/mysql_slow.log

3. 스레드 및 부하 문제 진단

데이터베이스의 CPU 사용률이 급증하거나 응답이 지연되지만, 쿼리가 아직 완료되지 않아 느린 쿼리 로그에 기록되지 않은 경우 실시간으로 스레드 상태를 확인해야 합니다.

3.1 현재 실행 중인 스레드 확인

SHOW PROCESSLIST 명령을 사용하여 현재 MySQL 서버에서 실행 중인 스레드 목록을 확인합니다.

-- 현재 연결된 상위 100개 스레드 목록 확인
SHOW PROCESSLIST;

-- 모든 스레드의 상세 SQL 문장과 함께 확인
SHOW FULL PROCESSLIST;

3.2 주요 필드 설명

SHOW PROCESSLIST 결과에서 특히 주목해야 할 필드들은 다음과 같습니다.

3.3 Information Schema를 이용한 고급 필터링

동시에 많은 스레드가 실행 중일 때, information_schema.processlist 테이블을 직접 쿼리하여 특정 조건에 맞는 스레드를 필터링할 수 있습니다.

-- 30초 이상 실행 중인 'Sleep' 상태가 아닌 스레드 조회
SELECT id, user, host, db, command, time, state, info
FROM information_schema.processlist
WHERE command != 'Sleep'
AND time > 30
ORDER BY time DESC;

3.4 정체된 스레드 종료

특정 SQL 쿼리가 교착 상태에 빠지거나 자원을 과도하게 소모하여 문제를 일으키는 경우, 해당 스레드를 강제로 종료할 수 있습니다. (예: 스레드 ID가 12345인 경우)

KILL 12345;

4. 일반적인 문제 해결 가이드

데이터베이스 운영 중 발생할 수 있는 일반적인 성능 문제에 대한 빠른 진단 및 해결 방법을 요약합니다.