[MySQL] Slow Query 알아보기 ( slow_query_log, long_query_time )

- 목차

 

들어가며.

Slow Query Log 는 MySQL 서버에서 실행 시간이 일정 시간 이상 소요된 쿼리들을 기록하는 로그 파일입니다.

이번 글에서는 Slow Query 와 관련된 여러 가지 설정과 활용 방법 등에 대해서 알아보는 시간을 가지겠습니다.

 

slow_query_log.

MySQL 에서는 Slow Query 의 기록을 위한 몇가지 설정이 있습니다.

먼저 my.cnf 파일에서 slow_query_log 을 ON 또는 OFF 를 통해 활성화/비활성화시킬 수 있습니다.

그리고 slow_query_log_file 설정을 통해서 Slow Query Log File 의 파일 위치를 지정할 수 있습니다.

 

아래의 예시는 slow_query_log_file 설정을 통해서 Slow Query Log 의 파일 위치와 파일명을 설정하는 예시입니다.

 

cat <<'EOF'> /tmp/my.cnf
[mysqld]
slow_query_log=ON
slow_query_log_file=/var/lib/mysql/this_is_slow_query.log
EOF

docker run -d --rm --platform linux/amd64 \
  --name mysql \
  -e MYSQL_ALLOW_EMPTY_PASSWORD=1 \
  -v /tmp/my.cnf:/etc/mysql/conf.d/slow.cnf \
  mysql:8.0.30

 

 

slow_query_log_file 확인해보기.

아래의 예시는 Slow Query 를 실행하기 위해서 의도적으로 Lock 이 걸린 데이터를 변경하는 쿼리를 실행하였습니다.

그리고 Lock 을 Waiting 하느라 Long Query Timeout 제한에 걸리도록 의도하였습니다.

 

 

 

그리고 아래의 결과는 "update mysql.test_table set value = '2' where id = 1" 쿼리가 Slow Query Log 에 추가된 결과입니다.

 

bash-4.4# cat this_is_slow_query.log
/usr/sbin/mysqld, Version: 8.0.30 (MySQL Community Server - GPL). started with:
Tcp port: 0  Unix socket: /var/run/mysqld/mysqld.sock
Time                 Id Command    Argument
/usr/sbin/mysqld, Version: 8.0.30 (MySQL Community Server - GPL). started with:
Tcp port: 0  Unix socket: /var/run/mysqld/mysqld.sock
Time                 Id Command    Argument
/usr/sbin/mysqld, Version: 8.0.30 (MySQL Community Server - GPL). started with:
Tcp port: 3306  Unix socket: /var/run/mysqld/mysqld.sock
Time                 Id Command    Argument
# Time: 2023-02-12T10:41:14.805123Z
# User@Host: root[root] @ localhost []  Id:     9
# Query_time: 50.211429  Lock_time: 50.204228 Rows_sent: 0  Rows_examined: 0
SET timestamp=1676198424;
update mysql.test_table set value = '2' where id = 1;
bash-4.4#

 

 

long_query_time.

long_query_time 설정은 Slow Query 의 기준을 정의하는 시간 설정입니다.

long_query_time 의 기본값은 10초로 설정됩니다.

이는 my.cnf 또는 시스템 변수 설정으로 변경할 수 있습니다.

 

cat <<'EOF'> /tmp/my.cnf
[mysqld]
slow_query_log=ON
slow_query_log_file=/var/lib/mysql/this_is_slow_query.log
long_query_time=60
EOF

docker run -d --rm --platform linux/amd64 \
  --name mysql \
  -e MYSQL_ALLOW_EMPTY_PASSWORD=1 \
  -v /tmp/my.cnf:/etc/mysql/conf.d/slow.cnf \
  mysql:8.0.30

 

my.cnf 로 설정한 long_query_time 은 아래와 같이 반영이 되구요.

 

show variables like 'long_query_time';
ERROR 2013 (HY000): Lost connection to MySQL server during query
No connection. Trying to reconnect...
Connection id:    8
Current database: *** NONE ***

+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 60.000000 |
+-----------------+-----------+
1 row in set (0.05 sec)

 

아래와 같이 SET long_query_time 을 통해서 변경이 가능합니다.

다만, SET 을 활용한 설정 변경은 적용 범위가 해당 세션임을 유의하시면 좋습니다.

 

mysql> set long_query_time=66;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 66.000000 |
+-----------------+-----------+
1 row in set (0.01 sec)