Summary of MySQL slow log related knowledge

1. Introduction to Slow Log

The full name of slow log is slow query log, which is mainly used to record SQL statements that take longer than a specified time to execute in MySQL. Through the slow query log, you can find out which statements have low execution efficiency so as to optimize them.

By default, MySQL does not have a slow log enabled. You can enable the slow log by modifying the slow_query_log parameter. The parameters related to slow logs are introduced as follows:

• slow_query_log: Whether to enable the slow query log. The default value is 0. It can be set to 0 or 1. 1 means enabled.
• slow_query_log_file: Specifies the slow query log location and name. The default value is host_name-slow.log. An absolute path can be specified.
• long_query_time: Slow query execution time threshold. If the time exceeds this threshold, the query will be recorded. The default value is 10, in seconds.
• log_output: The output destination of the slow query log. The default value is file, which means the log is output to a file.
• log_timestamps: mainly controls the display time zone in the error log, slow log, and genera log log files. The UTC time zone is used by default. It is recommended to change it 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: SQL statements with a query scan number less than this parameter will not be recorded in the slow query log. The default value is 0.
• log_slow_admin_statements: Whether slow administrative statements are written to the slow log. Administrative statements include alter table, create index, etc. The default value is off, which means that they are not written.

In general, we only need to enable slow logging and configure the threshold time, and the remaining 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 practice

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

# Slow query log related configuration, you can modify vim /etc/my.cnf according to the actual situation
 [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 the slow log records. Let's execute a slower query SQL and see how it is reflected in the slow log.

# The SQL execution time exceeds the threshold# 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 '%Test%';

If the slow query log is enabled and FILE is selected as the output destination, each statement written to the log begins with a # character. For each group of slow SQL statements, the first line records the time when the SQL statement is executed (if the log_timestamps parameter is UTC, the time will be displayed in the UTC time zone), the second line records the user and IP address that executed the statement, and the connection ID. The 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 examined at the server level (not counting any processing inside the storage engine).

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

In actual environments, it is not recommended to enable the log_queries_not_using_indexes parameter, as enabling this parameter may cause the slow log to grow rapidly. For the screening and analysis of slow logs, we can use tools such as mysqldumpslow and pt-query-digest for analysis. For slow log files, they need to be archived regularly. For example, you can temporarily close the slow log, rename the old file, and then open the slow log again. This will write it to the new log file, effectively reducing the log size.

The above is the detailed content of the summary of MySQL slow log related knowledge. For more information about MySQL slow log, please pay attention to other related articles on 123WORDPRESS.COM!