The role and opening of MySQL slow query log

Preface

The MySQL slow query log is a type of log record provided by MySQL. It is used to record statements in MySQL whose response time exceeds the threshold. Specifically, SQL statements whose running time exceeds the long_query_time value will be recorded in the slow query log. The default value of long_query_time is 10, which means that the statement runs for more than 10 seconds. By default, the MySQL database does not start the slow query log, and we need to manually set this parameter. Of course, if it is not required for tuning, it is generally not recommended to start this parameter, because turning on the slow query log will more or less bring certain performance impact. The slow query log supports writing log records to files or to database tables.

The official document introduces the slow query log as follows (partial information, please refer to the official related link for details):

The slow query log consists of SQL statements that took more than long_query_time seconds to execute and required at least min_examined_row_limit rows to be examined. The minimum and default values ​​of long_query_time are 0 and 10, respectively. The value can be specified to a resolution of microseconds. For logging to a file, times are written including the microseconds part. For logging to tables, only integer times are written; the microseconds part is ignored.

By default, administrative statements are not logged, nor are queries that do not use indexes for lookups. This behavior can be changed using log_slow_admin_statements and log_queries_not_using_indexes, as described later.

Slow query log related parameters

MySQL slow query related parameter explanation:

• slow_query_log : Whether to enable slow query log, 1 means enabled, 0 means disabled.
• log-slow-queries: The storage path of the slow query log of the old version (below 5.6) of MySQL database. You can leave this parameter unset, the system will default to a default file host_name-slow.log
• slow-query-log-file: The storage path of the slow query log of the new version (5.6 and above) of the MySQL database. You can leave this parameter unset, the system will default to a default file host_name-slow.log
• long_query_time: slow query threshold. When the query time exceeds the set threshold, a log is recorded.
• log_queries_not_using_indexes: Queries that do not use indexes are also logged to the slow query log (optional).
• log_output: log storage method. log_output='FILE' means saving logs to files. The default value is 'FILE'. log_output='TABLE' means storing the log in the database, so the log information will be written to the mysql.slow_log table. MySQL database supports two log storage methods at the same time. When configuring, just separate them with commas, such as: log_output='FILE,TABLE'. Logging to the system's dedicated log table consumes more system resources than logging to files. Therefore, if you need to enable slow query logs and obtain higher system performance, it is recommended to log to files first.

The role of slow query log

The slow query log records the SQL statements that take longer than the specified time to execute. The slow query log can be used to locate and analyze performance bottlenecks.

Check whether the slow query log function is enabled and the directory where the slow query log files are stored

SHOW VARIABLES LIKE 'slow_query%'

Enable slow query logging

• slow_query_log can set the switch status of the slow query log
• long_query_time can set the query timeout time in seconds

In the MySQL configuration file /etc/my.cnf, set

slow_query_log=ON
long_query_time=1

Enable the slow query log to record SQL statements that take more than 1 second to query. The log will take effect after restarting MySQL.

You can use the following sql to test the following

SELECT SLEEP(2);

Slow query log file

Tcp port: 0 Unix socket: (null)
Time Id Command Argument
# Time: 210125 6:30:14
# User@Host: reptile[reptile] @ [192.168.10.254] Id: 1
# Query_time: 2.000380 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
SET timestamp=1611556214;
SELECT SLEEP(2);

• SET timestamp=1611556214; Execute sql timestamp
• Query_time SQL execution time
• Rows_sent returns several records

Summarize

This is the end of this article about the role and activation of MySQL slow query log. For more relevant MySQL slow query log content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!