Slow query log for MySQL

MySQL itself supports slow query logging, which can be configured to log queries that exceed a specific time in SQL queries. This is a very effective tool in optimizing SQL queries to help us quickly locate the SQL that has the problem.

　　Variables commonly used in slow query logs

Long_query_time: The minimum value is 0, the default is 10 milliseconds, and only SQL that exceeds the query time is logged, with a minimum granularity of milliseconds.

Min_examined_row_limit: The number of rows scanned when the query was executed.

Slow_query_log_file: Slow query log file name

Log-output: Slow query log storage path

Log_queries_not_using_indexes: Queries that do not use indexes are logged to the slow query log

Log_slow_slave_statements: Logs the slow query log in the cluster to the master node host slow query log.

use of slow query log

When logging to a file, the minimum granularity of time can be milliseconds. However, when logging to a table in a database, only the second of an integer is logged. Therefore, the slow query log is most logged in the file.

MySQL does not use the initial lock SQL time as the execution time, and the log is written into the file only after SQL execution is complete and all locks are released mysqld. Therefore, the order of the records in the log may differ from the time that the application actually executes.

By default, the slow query log is not enabled, so a lot of people never know about this thing. If you enable slow query logging, you need to specify--slow_query_log[={0|1}] when you start MySQL. If there are no parameters or a parameter of 1, the slow query log is enabled. If the parameter is 0, the slow query log is disabled. If you specify a log file name, use--slow_query_log_file=file_name. If you specify a log file path, use--log-output=output_file_name.

If you do not specify a file name for the slow query log, the default name is Host_name-slow.log. If you do not specify a directory for the log, this log will be stored in the data directory.

One thing you need is that if you do not find a directory for the slow query log file or if the directory does not exist, it will not be logged to the slow query log file even if the slow query log is enabled. Therefore, remember that the specified slow query log directory is a directory that already exists.

You can use the--log-short-format option if you want to slow down the query log for less content.

You can use the log_slow_admin_statements variable if you want to record administrative behavior in the slow query log as well. It records behavior such as modifying a data table, analyzing a data table, checking a data table, creating an index, deleting an index, and repairing a data table.

If you want to record a query that does not use an index in the slow query log, you can use the log_queries_not_using_indexes variable. Of course, this will cause the system log to increase particularly quickly. We can use the log_throttle_queries_not_using_indexes variable to limit the frequency of logging queries. The default value for this variable is 0, which is an unrestricted record. We'd better set an integer like 60, just to record the number of times you don't use index queries per minute.

Finally, the records queried from the database cache are not logged to the slow query log. If a table has no data, it is not recorded in the slow query log. The primary node in the cluster does not log duplicate content to the slow query log unless log_slow_slave_statements is enabled.

Slow query log for MySQL