MySQL 슬로우 쿼리 로그 분석 실전 가이드 설정부터 최적화까지

MySQL 슬로우 쿼리 로그 분석 실전 가이드 설정부터 최적화까지

📑 목차

• 1슬로우 쿼리 로그가 뭔지 왜 그렇게 중요한가
• 2slow_query_log 활성화 설정 방법
• 3로그 파일 직접 읽기와 mysqldumpslow 기본 활용
• 4pt-query-digest로 더 정밀하게 분석하기
• 5EXPLAIN 실행계획 해석하는 법
• 6실전 최적화 사례 인덱스 추가와 쿼리 개선
• 7장기 모니터링을 위한 운영 설정 팁

 

사이드 프로젝트 운영해보신 분들, 어느 날 갑자기 "왜 이렇게 느려졌지?" 하는 순간 찾아오죠. 저도 퇴근 후 틈틈이 만들던 사이드 프로젝트에서 딱 그 상황을 겪었어요. 사용자가 조금씩 늘어나면서 응답 시간이 3초, 5초, 심지어 10초까지 튀기 시작하는 거예요. MySQL 슬로우 쿼리 로그라는 걸 제대로 알기 전까지는 진짜 막막했습니다.

처음엔 서버 스펙 문제인 줄 알고 인스턴스를 업그레이드했는데, 당연히 비용만 늘고 별로 나아진 게 없었어요. 솔직히 그때 상당히 답답했죠. 나중에야 문제가 DB 쿼리에 있다는 걸 깨달았는데, 슬로우 쿼리 로그 하나로 원인이 다 보이더라고요.

이 글에서는 MySQL 슬로우 쿼리 로그 설정부터 mysqldumpslow, pt-query-digest 분석, EXPLAIN 실행계획 해석, 인덱스 최적화까지 제가 직접 써보면서 정리한 방법을 공유할게요.

1. 슬로우 쿼리 로그가 뭔지 왜 그렇게 중요한가

MySQL 슬로우 쿼리 로그(Slow Query Log)는 설정한 시간 이상 걸린 쿼리를 자동으로 파일에 기록하는 기능이에요. 예를 들어 long_query_time = 1로 설정하면, 실행에 1초 이상 걸린 쿼리가 전부 로그에 남는 방식이죠.

근데 이게 왜 중요하냐면, DB 성능 문제는 대부분 "특정 쿼리 몇 개"가 원인이에요. 전체 DB가 느린 게 아니라, 풀 테이블 스캔을 하는 쿼리 하나가 서버 자원을 다 잡아먹는 경우가 태반이에요. 슬로우 쿼리 로그 없이 이걸 찾으려면 진짜 삽질이 따로 없습니다. 저도 처음엔 그냥 코드 전체를 의심하면서 한참 헤맸어요.

슬로우 쿼리 로그로 할 수 있는 것들을 정리해보면 이렇습니다.

• 실행 시간이 긴 쿼리 자동 수집
  → 특정 임계값을 초과하는 쿼리를 놓치지 않고 전부 기록
• 쿼리 실행 횟수와 총 실행 시간 집계
  → 1회에 느린 쿼리보다 100번 실행되는 0.5초 쿼리가 더 치명적일 수 있음
• 인덱스를 사용하지 않는 쿼리 감지
  → log_queries_not_using_indexes 옵션으로 인덱스 누락 쿼리 별도 수집 가능
• 배포 후 실시간 이상 감지
  → 새 기능 배포 직후 슬로우 쿼리 발생 여부를 즉시 확인 가능

2. slow_query_log 활성화 설정 방법

설정 방법은 크게 두 가지예요. my.cnf 파일에 영구 설정하는 방법과, MySQL 콘솔에서 즉시 적용하는 방법이에요. 운영 환경이라면 영구 설정이 기본이고, 빠르게 테스트해볼 때는 즉시 적용 방식이 편합니다.

영구 설정은 my.cnf (또는 my.ini) 파일의 [mysqld] 섹션에 아래 내용을 추가하면 돼요. 설정 후 MySQL을 재시작해야 반영됩니다.

<<>>

즉시 적용은 MySQL 콘솔에 접속해서 SET GLOBAL 명령어를 사용하면 돼요. 재시작 없이 바로 반영되지만, MySQL을 재시작하면 초기화되니까 임시 상황에서만 쓰는 게 맞아요.

<<>>

설정이 제대로 됐는지 확인할 때는 아래 쿼리를 사용하면 됩니다.

<<>>

주요 설정 파라미터를 아래 표에 정리했어요. 처음 설정할 때 참고하면 도움이 될 거예요.

파라미터	기본값	권장값	설명
slow_query_log	OFF	1 (ON)	슬로우 쿼리 로그 활성화 여부
long_query_time	10초	1~2초	이 시간 초과 시 로그에 기록
log_queries_not_using_indexes	OFF	1 (ON)	인덱스 미사용 쿼리 추가 기록
slow_query_log_file	호스트명-slow.log	직접 경로 지정	로그 파일 저장 경로
min_examined_row_limit	0	100 이상	스캔 행 수 최소값(노이즈 필터용)

참고로 long_query_time 기본값이 10초인 게 의외로 많은 분들이 모르는 부분이에요. 기본값 그대로 두면 진짜 심각한 쿼리만 잡히거든요. 저는 개발 환경에서는 0.5초, 운영 환경에서는 1초로 설정해서 씁니다.

slow_query_log 활성화 설정 방법

3. 로그 파일 직접 읽기와 mysqldumpslow 기본 활용

로그가 쌓이기 시작하면 이걸 분석해야 하는데, 파일을 그냥 cat으로 열면 정신없어요. 날것의 로그 파일은 아래처럼 생겼어요.

<<>>

쿼리 하나하나는 이해가 되는데, 이게 수천 줄 쌓여 있으면 막상 어디서부터 봐야 할지 막막하더라고요. 그럴 때 쓰는 게 MySQL에 기본으로 포함된 mysqldumpslow 도구예요. 설치 없이 바로 쓸 수 있다는 게 장점입니다.

상황별로 자주 쓰는 명령어를 정리하면 이렇습니다.

• 실행 시간 기준 상위 10개 추출
  → mysqldumpslow -s t -t 10 /var/log/mysql/slow-query.log
• 실행 횟수 기준 상위 10개 추출
  → mysqldumpslow -s c -t 10 /var/log/mysql/slow-query.log
• 평균 실행 시간 기준 정렬
  → mysqldumpslow -s at -t 10 /var/log/mysql/slow-query.log
• 특정 키워드 포함 쿼리만 필터링
  → mysqldumpslow -s t -t 10 /var/log/mysql/slow-query.log | grep orders

처음 써봤을 때 "아 이렇게 간단하게 되는 거야?" 싶었어요. 다만 리포트가 좀 투박한 편이라서 더 정밀한 분석이 필요할 때는 pt-query-digest 쪽이 훨씬 유용합니다.

4. pt-query-digest로 더 정밀하게 분석하기

솔직히 pt-query-digest

▸ 참고 자료: Percona Toolkit

<<>>

기본 분석 명령어는 정말 간단해요.

<<>>

처음 실행했을 때 리포트 양에 살짝 당황했어요. 쿼리별로 실행 횟수, 총 시간, 평균 시간, 최대 시간, 표준편차, 95th percentile 시간까지 전부 나와요. 이게 한 번에 보이니까 어디서부터 손대야 할지가 확실히 보여요!

자주 쓰는 옵션도 알아두면 편합니다.

• 최근 1시간 로그만 분석
  → pt-query-digest --since=1h /var/log/mysql/slow-query.log
• 특정 시간대 범위 분석
  → pt-query-digest --since="2025-03-15 10:00:00" --until="2025-03-15 12:00:00" slow-query.log
• 결과를 파일로 저장
  → pt-query-digest slow-query.log > analysis_report.txt
• 상위 N개 쿼리만 출력
  → pt-query-digest --limit=20 slow-query.log

개인적으로 배포 직후 30분 로그를 pt-query-digest로 한 번 돌려보는 게 습관이 됐어요. 새로 추가한 기능에서 예상 못한 슬로우 쿼리가 나오는 경우가 꽤 있더라고요.

pt-query-digest로 더 정밀하게 분석하기

5. EXPLAIN 실행계획 해석하는 법

슬로우 쿼리를 찾았다면 이제 EXPLAIN으로 왜 느린지를 파악해야 해요. 처음엔 EXPLAIN 결과가 뭔 뜻인지 몰라서 그냥 무시하고 지나친 적이 있었는데, 그게 진짜 큰 실수였어요. EXPLAIN 한 번이면 문제 원인의 80%는 잡혀요.

<<>>

결과에서 가장 먼저 봐야 하는 컬럼들을 아래 표에 정리했어요.

컬럼명	좋은 상태	위험 신호	의미
type	ref, range, eq_ref	ALL (풀 스캔)	테이블 접근 방식
key	인덱스명 표시	NULL (인덱스 미사용)	실제 사용된 인덱스
rows	반환 행 수와 유사	수십만 이상	예상 스캔 행 수
Extra	Using index	Using filesort, Using temporary	추가 처리 방식

제가 처음 EXPLAIN을 제대로 읽었을 때 type이 ALL이고 rows가 90만인 걸 발견했을 때 정말 뒤통수를 맞은 것 같았어요. 그게 풀 테이블 스캔(Full Table Scan)이라는 거였거든요. 90만 행을 매 요청마다 전부 훑고 있었던 거예요. 놀랍도록 비효율적인 쿼리를 아무 생각 없이 쓰고 있었던 거죠!

Extra 컬럼에서 Using filesort가 보이면 MySQL이 정렬을 위해 별도 파일 연산을 수행한다는 뜻이에요. 인덱스를 잘 타면 Using index가 나오면서 훨씬 빠르게 처리됩니다. 의외로 ORDER BY 컬럼 하나 때문에 성능이 확 떨어지는 경우가 많으니 꼭 확인해보세요.

6. 실전 최적화 사례 인덱스 추가와 쿼리 개선

실제로 겪었던 사례를 공유할게요. 퇴근 후 만들던 중고거래 사이드 프로젝트에서 상품 목록 조회 쿼리가 갑자기 6초씩 걸리기 시작했어요. 슬로우 쿼리 로그에 잡힌 문제의 쿼리는 이런 형태였습니다.

<<>>

EXPLAIN 결과를 보니 type이 ALL에 rows가 82만 개. 진짜 어이없었어요. category_id, status, deleted_at 어디에도 인덱스가 없는 거예요. 개발 초반에 인덱스 생각 없이 막 개발해온 게 데이터가 쌓이면서 한꺼번에 터진 거죠. 개인적으로 초기 설계에서 인덱스를 소홀히 한 게 그때 가장 후회됐어요.

복합 인덱스를 추가해서 해결했습니다.

<<>>

인덱스 추가 후 EXPLAIN을 다시 돌려보니 type이 ref로 바뀌고 rows가 245개로 줄었어요. 쿼리 시간은 6초에서 0.03초로. 드디어 해결됐을 때 그 뿌듯함은 진짜 말로 표현이 안 될 정도였습니다!

인덱스 외에 쿼리 자체를 개선해야 하는 경우도 있어요. 흔한 안티패턴을 정리하면 이렇습니다.

• SELECT * 남발 금지
  → 필요한 컬럼만 명시하면 네트워크 전송량과 처리 비용이 줄어요
• WHERE 절에 함수 사용 금지
  → WHERE YEAR(created_at) = 2025 대신 WHERE created_at BETWEEN '2025-01-01' AND '2025-12-31' 사용
• N+1 쿼리 패턴 제거
  → 반복문 안에서 쿼리를 호출하는 패턴. JOIN이나 IN 절로 한 번에 처리할 것
• 대용량 조회에 LIMIT 없는 경우 금지
  → 관리자 화면 포함해서 LIMIT + 페이지네이션을 반드시 붙일 것

WHERE 절에 함수를 쓰면 인덱스를 아예 못 타게 되는 경우가 많아요. 이거 처음 알았을 때 꽤 신기하더라고요. YEAR(), DATE(), SUBSTR() 같은 함수를 컬럼에 씌우는 순간 인덱스가 무력화된다는 걸 기억해두세요.

7. 장기 모니터링을 위한 운영 설정 팁

개발 중 한 번 최적화했다고 끝이 아니에요. 서비스가 성장하면서 데이터가 쌓이면 예전에 괜찮던 쿼리도 느려지는 경우가 생기기도 해요. 그래서 지속적인 모니터링이 필요합니다.

운영 환경에서 제가 직접 써보면서 정착시킨 설정들을 소개할게요.

• long_query_time 단계적 조정
  → 처음엔 2초로 시작해서 큰 문제 해결 후 1초, 0.5초로 낮춰가며 점진적 개선
• 로그 로테이션 설정
  → logrotate로 일별 로테이션. 한 파일이 너무 커지면 pt-query-digest 분석 시간이 오래 걸려요
• 주간 분석 루틴 만들기
  → 매주 월요일에 지난 주 슬로우 쿼리 상위 10개 확인. 새로 나타난 쿼리가 있는지 체크
• performance_schema 활용
  → MySQL 5.7 이상에서 기본 활성화. sys 스키마와 함께 쓰면 실시간 쿼리 분석이 편해요

▸ 참고 자료: MySQL 공식 문서의 슬로우 쿼리 로그 레퍼런스

처음에는 슬로우 쿼리 분석이 복잡하게 느껴질 수 있어요. 근데 막상 로그 켜고 mysqldumpslow로 상위 쿼리 10개 뽑아서 EXPLAIN 한 번씩 돌려보면, 생각보다 금방 문제 쿼리를 찾게 되더라고요. 확실히 직접 해봐야 감이 오는 작업이에요.

여러분의 프로젝트에서 슬로우 쿼리 로그를 켜고 처음 발견한 문제 쿼리가 뭔지 궁금하네요. 저처럼 풀 테이블 스캔 폭탄이 기다리고 있을 수도 있어요! 댓글로 공유해주시면 같이 이야기 나눠봐요.

📌 콘텐츠 안내 및 필수 확인사항

• 본 콘텐츠는 AI 기술을 활용하여 작성되었으며, 정보 제공 목적으로만 제공됩니다.
• 법률, 의료, 금융 등 전문적 조언을 대체하지 않으며, 콘텐츠의 정확성, 완전성, 최신성을 보장하지 않습니다.
• 본 콘텐츠를 참고하여 내리는 모든 결정과 그에 따른 결과에 대한 책임은 전적으로 독자 본인에게 있습니다.
• 운영자는 본 콘텐츠의 이용으로 인해 발생하는 어떠한 직접적, 간접적 손해에 대해서도 법적 책임을 지지 않습니다.
• 중요한 사안은 반드시 해당 분야의 전문가와 상담하시기 바랍니다.