mysql slow query 설정

영구적적용

my.cnf 위치확인

[darksharavim]mysql --verbose --help | grep 'my.cnf'
/etc/my.cnf ~/.my.cnf

my.cnf설정

[mysqld]
# slow query on = 1, slow query off = 0
slow_query_log = 1

# n초이상 걸렷을 경우 
query_long_time = 5

# file이 아닌 table도 가능하며
# table로 할 경우 logfile경로 옵션 주석처리
log_output = file

# defualt name = host_name-slow.log
slow_query_log_file = /var/log/mysql/slowquery.log

mysql service restart

 

한시적 적용

session설정(서비스 재기동 없이)

SET GLOBAL log_output = 'file';
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 5;

1 또는 2 방법으로 한 뒤 설정확인

MariaDB [(none)]> show global variables like '%slow_query_log%';
+---------------------+--------------+
| Variable_name       | Value        |
+---------------------+--------------+
| slow_query_log      | ON           |
| slow_query_log_file | elk-slow.log |
+---------------------+--------------+
2 rows in set (0.001 sec)

MariaDB [(none)]> show global variables like '%long_query_time%';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 5.000000 |
+-----------------+----------+
1 row in set (0.001 sec)


MariaDB [(none)]> show global variables like '%log_output%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |
+---------------+-------+
1 row in set (0.001 sec)

 

시스템 변수 비교

MariaDB [(none)]> SELECT VARIABLE_NAME, SESSION_VALUE, GLOBAL_VALUE FROM
    ->  INFORMATION_SCHEMA.SYSTEM_VARIABLES WHERE 
    ->   VARIABLE_NAME IN ('slow_query_log', 'log_output', 'long_query_time');
+-----------------+---------------+--------------+
| VARIABLE_NAME   | SESSION_VALUE | GLOBAL_VALUE |
+-----------------+---------------+--------------+
| LONG_QUERY_TIME | 2.000000      | 2.000000     |
| SLOW_QUERY_LOG  | OFF           | OFF          |
| LOG_OUTPUT      | NULL          | TABLE        |
+-----------------+---------------+--------------+
3 rows in set (0.003 sec)

 

슬로우쿼리 테이블 저장되게끔 변경 후 조회

MariaDB [(none)]> set global log_output = 'TABLE';
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> show global variables like '%log_output%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | TABLE |
+---------------+-------+
1 row in set (0.001 sec)



# slow쿼리 정상동작 여부확인
MariaDB [(none)]> select sleep(10);
+-----------+
| sleep(10) |
+-----------+
|         0 |
+-----------+
1 row in set (10.001 sec)



# 테이블 확인
MariaDB [(none)]> select * from mysql.slow_log\G;
*************************** 1. row ***************************
    start_time: 2022-11-18 10:37:50.869474
     user_host: root[root] @ localhost []
    query_time: 00:00:10.000274
     lock_time: 00:00:00.000000
     rows_sent: 1
 rows_examined: 0
            db: 
last_insert_id: 0
     insert_id: 0
     server_id: 1
      sql_text: select sleep(10)
     thread_id: 141370
 rows_affected: 0
1 row in set (0.001 sec)


# 또는
MariaDB [(none)]> SELECT start_time, user_host, query_time, lock_time, rows_sent, rows_examined, db, CONVERT(sql_text USING utf8 ) sql_text FROM mysql.slow_log\G;
*************************** 1. row ***************************
   start_time: 2022-11-18 10:37:50.869474
    user_host: root[root] @ localhost []
   query_time: 00:00:10.000274
    lock_time: 00:00:00.000000
    rows_sent: 1
rows_examined: 0
           db: 
     sql_text: select sleep(10)
1 row in set (0.001 sec)

 

각 컬럼정보

start_time	쿼리가 시작된 시간
user_host	쿼리를 실행한 사용자
query_time	쿼리가 실행되는 데 걸린 시간(초)
lock_time	테이블 잠금에 대한 대기 시간(초)
rows_sent	실제 클라이언트에 전달된 레코드의 수
rows_examined	쿼리가 처리되기 위해 실제 접근한 레코드의 수
db	쿼리가 수행된 데이터베이스
sql_text	실제 수행된 쿼리

https://dev.mysql.com/doc/refman/5.7/en/slow-query-log.html#slow-query-log-contents

MySQL :: MySQL 5.7 Reference Manual :: 5.4.5 The Slow Query Log

SET - MariaDB Knowledge Base

SET