MySQL slow log full analysis

Foreword:

Slow logs are often used in daily database operation and maintenance. We can obtain inefficient SQL by viewing the slow logs, and then SQL optimization can be performed. In this article, let's learn about the slow log.

1. Introduction to Slow Log

The full name of the slow log is the slow query log (Slow Query Log), which is mainly used to record SQL statements whose execution time exceeds a specified time in MySQL. Through the slow query log, you can find out which statements have low execution efficiency for optimization.

By default, MySQL does not turn on the slow log. You can turn on the slow log by modifying the slow_query_log parameter. The parameters related to the slow log are introduced as follows:

• slow_query_log : Whether to enable the slow query log, the default is 0, can be set to 0, 1, and 1 means to enable it.
• slow_query_log_file : Specify the location and name of the slow query log, the default value is host_name-slow.log, and the absolute path can be specified.
• long_query_time : The slow query execution time threshold. If this time is exceeded, it will be recorded. The default is 10 and the unit is s.
• log_output : slow query log output destination, the default is file, that is, output to a file.
• log_timestamps : Mainly to control the display time zone in the error log, slow log, and generator log log files. The UTC time zone is used by default, and it is recommended to change to the SYSTEM system time zone.
• log_queries_not_using_indexes ：Whether to log all query statements that do not use indexes, the default is off.
• min_examined_row_limit : For SQL whose number of query scan rows is less than this parameter, it will not be recorded in the slow query log. The default is 0.
• log_slow_admin_statements : Whether slow management statements are written to the slow log, management statements include alter table, create index, etc. The default is off, which means no writes.

Under normal circumstances, we only need to enable slow logging, configure the lower threshold time, and other parameters can be configured by default. The threshold time can be adjusted flexibly, for example, it can be set to 1s or 3s.

2. Slow log combat

In the configuration file, we can set the following slow log related parameters:

# 慢查询日志相关配置，可根据实际情况修改
vim /etc/my.cnf 
[mysqld] 
slow_query_log = 1
slow_query_log_file = /data/mysql/logs/slow.log
long_query_time = 1
log_timestamps = SYSTEM
log_output = FILE

Let's take a closer look at what content will be recorded in the slow log? Let's execute a slower query SQL to see how it is reflected in the slow log.

# 该条SQL执行时间超过阈值

# Time: 2021-05-13T17:38:03.687811+08:00
# User@Host: root[root] @  [192.168.85.0]  Id: 2604943
# Query_time: 1.099889  Lock_time: 0.000144 Rows_sent: 39  Rows_examined: 45305
SET timestamp=1620898683;
select * from test_table where col_name like '%测试%';

If the slow query log is enabled and FILE is selected as the output destination, every statement written to the log starts with the # character. For each group of slow SQL, the first line records the time when the SQL was executed (if the log_timestamps parameter is UTC, the time will display UTC time zone time), the second line records the user and IP that executed the statement, and Link id, several fields in the third line are explained as follows:

• Query_time : duration statement execution time, in seconds.
• Lock_time : duration The time to acquire the lock (in seconds).
• Rows_sent : N The number of rows sent to the client.
• Rows_examined : N The number of rows checked by the server layer (not counting any processing inside the storage engine).

The following two lines are the timestamp and specific slow SQL when this statement is executed.

In the actual environment, it is not recommended to enable the log_queries_not_using_indexes parameter, which may cause the slow log to grow rapidly. For the screening and analysis of slow logs, we can use mysqldumpslow, pt-query-digest and other tools to analyze. For slow log files, archive processing needs to be performed on a regular basis. For example, you can temporarily close the slow log, then rename the old file, and then open the slow log. This will write to the new log file, effectively reducing the log volume.