Mysql Slow Query Log

Slow Query Log 확인 절차

1. 현재 설정 확인

mysql> SHOW VARIABLES LIKE 'slow_query_%';
+---------------------+---------------------------------------+
| Variable_name       | Value                                 |
+---------------------+---------------------------------------+
| slow_query_log      | OFF                                   |
| slow_query_log_file | /var/lib/mysql/ip-10-0-1-175-slow.log |
+---------------------+---------------------------------------+
2 rows in set (0.00 sec)

 

2. my.cnf 설정파일 변경

# my.cnf 파일 변경
sudo vi /etc/my.cnf


# 설정 값 입력
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/lib/mysql/mysql-slow.log
long_query_time = 3

 

3. mysql 재시작

service mysqld restart

 

4. mysql 접속 후 slow 쿼리 만들기

SELECT SLEEP(10);

 

5. 로그 확인

[root@ip-10-0-1-175 mysql]# cat /var/lib/mysql/mysql-slow.log
/usr/sbin/mysqld, Version: 5.7.35-log (MySQL Community Server (GPL)). started with:
Tcp port: 0  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument
# Time: 2021-09-29T06:16:05.653863Z
# User@Host: root[root] @ localhost []  Id:     2
# Query_time: 10.000297  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1632896165;
select sleep(10);

 

mysqldumpslow

mysql에는 slow쿼리를 통계내줄수 있는 mysqldumpslow가 있다. 이를 통해 slow 쿼리의 실행 횟수등에 대한 통계를 알 수 있으며 빈번하게 수행된 쿼리 순서로도 확인 할 수 있다.

mysqldumpslow -s c /var/lib/mysql/mysql-slow.log > slow_rersult.log

주요 옵션

• 쿼리 실행 횟수
  mysqldumpslow -s c 옵션을 사용하면 쿼리가 많이 실행된 순서대로 슬로우 쿼리를 분석해서 결과를 출력한다.
• 쿼리 잠금 시간
  mysqldumpslow -s l 옵션을 사용하면 쿼리가 실행되면서 오랜시간 동안 잠금을 가지고 있었던 순서대로 슬로우 쿼리를 분석해서 결과를 출력한다. 잠금 시간은 잠금을 획득하기 위해 기다린 시간이 아니라 쿼리가 실행되면서 잠금을 획득해서 가지고 있었던 ㅅ ㅣ간을 의미한다. 테이블 수준의 잠금만 해당 하므로 InnoDB테이블의 레코드 잠금은 해당되지 않는다.
• 쿼리로 조회한 레코드(row sent)
  mysqldumpslow -s r 옵션은 쿼리가 실행된 후 클라이언트로 결과 레코드를 많이 보내 준 순서대로 정렬해서 출력한다.

 

옵션 설명

• -n : 몇개까지 출력할지 보여줌 Top 10개의 쿼리만 출력하고 싶다면 10을 입력하면 됨
• -g : 리눅스나 유닉스의 grep 패턴, 즉 특정단어가 포함된 결과만 출력한다. 원하는 쿼리나 특정 테이블 명만 알고 있을때 이용하면 편리하다.
• -r : 정렬순서를 역순으로 정리한다. 기본값은 내림차순임
• -s : 출력시 정렬방법 정의

 

효율적 활용

일반적으로 어떤 쿼리가 얼만큼 들어오는지 확인하지 위해 여러 방법이 있을수 있다. WAS에서 확인할 수도 있고 general log를 내려 모든 쿼리를 확인 할 수 있다. 하지만 쿼리의 통계를 내는 프로그램은 없어 mysqldumpslow 을 활용하면 쉽게 할 수 있다.

간단하게 특정 시간동안 long_query_time을 0으로 세팅하여 모든 쿼리를 slow_query로 남긴 후 mysqldumpslow 을 통해 분석해 낼 수 있다.

mysql> set global long_query_time=0;
shell# mysqldumpslow -s c slow-queries.log > static.sql

 

관련 문서

https://dev.mysql.com/doc/refman/5.7/en/mysqldumpslow.html

MySQL :: MySQL 5.7 Reference Manual :: 4.6.8 mysqldumpslow — Summarize Slow Query Log Files