Preface:

Slow logs are often used in daily database operation and maintenance. You can view slow logs to obtain SQL with low efficiency, and then optimize SQL. In this article, we will learn about slow logging.

1. Slow log introduction

Slow Query logs are used to record SQL statements that take longer than the specified time to execute in MySQL. By slowly querying logs, you can find out which statements are inefficiently executed for optimization.

Slow logging is not enabled by default and can be enabled by changing the slow_query_log parameter. Parameters related to slow logs are described as follows:

  • Slow_query_log: specifies whether to enable slow query logs. The default value is 0. The value can be 0 or 1.
  • Slow_query_log_file: specifies the location and name of slow query logs. The default value is host_name-slow.log, and the absolute path can be specified.
  • Long_query_time: specifies the time threshold for slow query execution. When this time is exceeded, the system records the value. The default value is 10, in seconds.
  • Log_output: indicates the output target of slow query logs. The default value is file, that is, output to a file.
  • Log_timestamps: controls the time zones displayed in error log, slow log, and genera log files. The default time zone is UTC.
  • Log_queries_not_using_indexes: Indicates whether to record all query statements that do not use indexes. The default value is off.
  • Min_examined_row_limit: SQL examinations whose number of scanned rows is smaller than this parameter are not recorded in the slow query log. The default value is 0.
  • Log_slow_admin_statements: specifies whether slow management statements, such as ALTER table and CREATE INDEX, are written to slow logs. If the default value is OFF, slow management statements are not written.

Generally, you only need to enable slow logging, set the lower threshold time, and keep the default Settings for other parameters. The threshold time can be flexibly adjusted, for example, 1s or 3s.

2. Slow log practice

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

# Slow query log related configuration, you can modify vim according to actual situation/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
Copy the code

Now let’s see, what does a slow log record? Let’s execute a slow query SQL to see how slow logs are represented.

# whichSQLExecution time exceeds threshold #Time: 2021- 05- 13T17:38:03.687811+08:00
# User@Host: root[root] @  [192.16885.. 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 '% % test';
Copy the code

If slow query logging is enabled and FILE is selected as the output target, each statement written to the log begins with the # character. For each set of slow SQL statements, the first row records the time when the SQL statement was executed (if the log_timestamps parameter is UTC, the UTC time zone is displayed). The second row records the user who executed the statement, the IP address, and the link ID. The third row records the following fields:

  • Query_time: duration Statement execution time, in seconds.
  • Lock_time: duration Specifies the lock duration, in seconds.
  • Rows_sent: N Number of rows sent to the Client.
  • Rows_examined: N Number of lines examined by the server layer (without counting any processing inside the storage engine).

The following two lines show the timestamp and the specific slow SQL at the time the statement is executed.

In actual situations, it is not recommended to enable log_QUERies_NOT_using_INDEXES. If this parameter is enabled, slow logs may grow rapidly. For filtering and analyzing slow logs, we can use tools such as mysqlDumpslow and pt-query-digest to analyze them. You need to archive slow log files periodically. For example, you can temporarily disable slow log files, rename old files, and enable slow log files again. In this way, slow log files are written into new log files, effectively reducing the log volume.