How to Enable MySQL’s Slow Query Log

Unexpectedly slow queries are one of the most common MySQL performance issues. A query that performs reasonably well in development can stall when stressed by a production workload.

Large applications can execute hundreds of unique database queries each time an endpoint is reached. This makes it difficult to filter out the queries that are causing delays in the server’s response. The slow MySQL query log is a debugging option that can help you identify suspicious SQL statements and provide a starting point for your investigations.

Enable the slow query log

The log is a built-in mechanism for recording long-running SQL queries. Queries that are not completed within a configured time are written to the log. Reading the contents of the log will show you the SQL executed and the time taken.

Slow query logging is disabled by default. You can enable it on your server by running the following command from an administrative MySQL shell:

SET GLOBAL slow_query_log_file="/var/log/mysql/mysql-slow.log";
SET GLOBAL slow_query_log=1;

The change applies immediately. Slow queries are now logged /var/log/mysql/mysql-slow.log. You can periodically review this file to identify poorly performing queries.

MySQL counts a query as “slow” if it takes longer than 10 seconds. This limit is typically too loose for user-facing web applications where near-instantaneous responses are expected. You can change the limit by setting the long_query_time Variable:

SET GLOBAL long_query_time=1;

The value sets the minimum duration for slow queries. Finding a balance that suits your own application is important. A threshold that is too high excludes queries that actually affect performance. Conversely, very low values ​​can result in capturing too many queries, resulting in an overly noisy log.

Using MySQL’s configuration file

You should enable the slow query log in your MySQL configuration file if you intend to use it long-term. This ensures that logging will automatically resume after the MySQL server restarts.

The location of the configuration file may vary depending on the platform distribution. It’s usually at /etc/mysql/my.cnf or /etc/mysql/mysql.conf.d/mysqld.cnf. Add the following lines to replicate the settings dynamically enabled above:

slow_query_log=1
slow_query_log_file=/var/log/mysql/mysql-slow.log
long_query_time=1

Restart MySQL to apply your changes:

$ sudo service mysql restart

The slow query log is now active every time the MySQL server starts.

Customize log content

The log typically contains only SQL queries excluding the slow threshold sent by client applications. This eliminates any slow management operations that may occur, such as: B. index creation and table optimization, as well as queries with the potential be slow in the future.

You can add this information to the log by making the following changes to your configuration file:

• log_slow_admin_statements = 1 – Contains administrative SQL statements such as ALTER TABLE, CREATE INDEX, DROP INDEXand OPTIMIZE TABLE. This is rarely desirable as these operations are typically performed during maintenance and migration scripts. However, this setting can be useful if your application also performs these tasks dynamically.
• log_slow_replica_statements = 1 – This setting enables slow query logging for replicated queries on replication servers. This is disabled by default. Use log_slow_slave_statements instead for MySQL versions 8.0.26 and older.
• log_queries_not_using_indexes = 1 – If this setting is enabled, queries that are expected to retrieve all records from the target table or view are logged, even if they don’t exclude the slow query threshold. This can help identify when a query is missing an index or one that cannot be used. Queries for which an index is available are still logged if they lack constraints that limit the number of rows retrieved.

Logging queries that do not use indexes can increase verbosity significantly. There may be situations where a full index scan is expected or required. These queries will still appear in the log even though they cannot be resolved.

You can rank queries without indexes by setting that log_throttle_queries_not_using_indexes Variable. This defines the maximum number of logs written in a 60 second period. A value of 10 means that up to 10 queries per minute are recorded. After the tenth event, no further queries are logged until the next 60 second window is opened.

Interpreting the slow query log

Any query that makes it into the slow query log will display a series of lines that look something like this:

# Time: 2022-07-12T19:00:00.000000Z
# User@Host: demo[demo] @ mysql [] Id: 51
# Query_time: 3.514223  Lock_time: 0.000010  Rows_sent: 5143  Rows_examined: 322216
SELECT * FROM slow_table LEFT JOIN another_table ...

The annotated rows above the query include the time it was run, the MySQL user the client connected as, and statistics showing the duration and number of rows sent. The example above took 3.5 seconds and looked at over 320,000 rows before only 5,143 were sent to the client. This could be an indication that missing indexes are causing MySQL to examine too many records.

You can optionally include more information in the log by using the log_slow_extra = 1 System variable in your configuration file. This will add the thread ID, the number of bytes received and sent, and the number of rows considered for sorting, as well as the number of statement-specific requests that provide insight into how MySQL processed the query.

The log file must be handled with care as its content is sensitive. Queries are displayed in full, with no masking of parameter values. This means user data is present when using the slow query log on a production server. Access should be restricted to the developers and database administrators who tweak the SQL statements.

Slow query logging and backups

A common frustration with slow query logs arises when you also use MySQLDump to create database dumps. Durable SELECT * FROM ... Queries are executed to retrieve the data from your tables and inject it into your backup. They are included in the slow query log like any other SQL statement. This can pollute the log if you make regular backups.

You can fix this by temporarily disabling the slow query log before running it mysqldump. You can re-enable the log after the backup is complete. Adjust your backup script to look something like this:

#!/bin/bash

# Temporarily disable slow query logging
mysql -uUser -pPassword -e "SET GLOBAL slow_query_log=0";

# Run mysqldump
mysqldump -uUser -pPassword --single-transaction databaseName | gzip > backup.bak

# Enable the slow query log again
mysql -uUser -pPassword -e "SET GLOBAL slow_query_log=1"

This keeps MySQLDump activity out of the slow query log, making it easier to focus on the SQL being executed by your application.

summary

The slow MySQL query log is one of the most effective ways to determine the root cause of performance issues. First, estimate the lag you are experiencing and use that as your score long_query_time. Decrease the value if nothing appears in the log after you reproduce the problem.

The slow query log doesn’t tell you exactly how to fix the slowdown. However, the ability to view the exact SQL received from the server allows you to repeat poorly performing statements and then measure the impact of optimizations. Adding an index or missing constraints can be the difference between a query that touches thousands of rows and one that gets by with a handful.