MySQL slow query log

MySQL slow query log

MySQL supports slow query logs. You can configure query records that have exceeded the specified time for SQL query to log. This is a very effective tool in optimizing SQL queries and can help us quickly locate the problematic SQL statements.

Common variables for slow query logs

Long_query_time: the minimum value is 0. The default value is 10 ms. Only the SQL statements that exceed the query time are recorded. The minimum granularity is milliseconds.

Min_examined_row_limit: number of rows scanned during query.

Slow_query_log_file: name of the slow query log file

Log-output: Slow query log storage path

Log_queries_not_using_indexes: the query records that do not use indexes go to slow query logs.

Log_slow_slave_statements: records the slow query logs in the cluster to the slow query logs of the master node host.

Describes the usage of slow query logs.

The minimum time granularity for logging to a file can be millisecond. However, when logging to a table in the database, it can only be recorded in integer seconds. Therefore, most slow query logs are recorded in files.

Mysql does not regard the initial lock time as the execution time. mysqld writes logs into the file only after the SQL Execution is completed and all the locks are released. Therefore, the sequence recorded in the log may be different from the actual execution time of the application.

By default, slow query logs are not enabled, so many people never know about this. If the slow query log is enabled, you must specify -- slow_query_log [= {0 | 1}] when mysql is started. If no parameter exists or the parameter is 1, the slow query log is enabled. If the parameter is 0, slow query logs are disabled. If the log file name is specified, use -- slow_query_log_file = file_name. If you specify the 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 the host_name-slow.log. If no directory is specified for the log, the log will be stored in the data directory.

One thing you need is that if the directory cannot be found or the directory does not exist for the slow query log file, even if the slow query log is enabled, it will not be recorded in the slow query log file. Therefore, remember that the specified slow query log directory is an existing Directory.

You can use the -- log-short-format option if you want to slow down the query of logs with less notes.

If you want to record management behaviors to slow query logs, you can use the log_slow_admin_statements variable. It records the behaviors of modifying data tables, analyzing data tables, checking data tables, creating indexes, deleting indexes, and repairing data tables.

You can use the log_queries_not_using_indexes variable to record a query without an index in the slow query log. Of course, this will lead to a particularly fast increase in system logs. We can use the log_throttle_queries_not_using_indexes variable to limit the frequency of record queries. The default value of this variable is 0, that is, unlimited records. We 'd better set an integer such as 60 to record the number of times no index queries are used per minute.

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

This article permanently updates the link address: