Unexpectedly slow queries are one of the most common MySQL performance problems. A query that performs acceptably in development may falter under production workloads.
Large applications can run hundreds of unique database queries each time an endpoint is hit. This makes it difficult to single out queries that cause a delay in the server’s response. MySQL’s slow query log is a debugging option that can help you identify suspicious SQL statements and provide a starting point for your investigation.
Enable slow query log
The log is a built-in mechanism for recording long-running SQL queries. Queries that do not complete within the set time will be logged. Reading the contents of the log will show you the SQL that was executed and the time.
Slow query logging is disabled by default. You can activate it on your server by running the following command from the MySQL administrative 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 will now be logged
/var/log/mysql/mysql-slow.log. You can check this file periodically to identify low-performing queries.
MySQL counts a query as “slow” if it takes more than 10 seconds to complete. This limit is usually too relaxed for user-oriented web applications where near-instant responses are expected. You can change the limit by setting
SET GLOBAL long_query_time=1;
The value sets the minimum duration for slow queries. It is important to find a balance that suits your own application. Too high a threshold will exclude queries that really affect performance. Conversely, very low values can cause too many queries to be captured and create an excessively noisy log.
Using the MySQL configuration file
If you plan to use it long term, you should enable the slow query protocol in the MySQL configuration file. This will automatically resume logging after the MySQL server is restarted.
The location of the configuration file may vary by platform distribution. It is usually in
/etc/mysql/mysql.conf.d/mysqld.cnf. Add the following lines to replicate the settings that were 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 the changes:
$ sudo service mysql restart
The slow query log will now be active every time the MySQL server is started.
Customization of log content
The log usually only contains SQL queries that exclude the “slow” threshold and were sent by client applications. This excludes any slow administrative operations that may occur, such as creating indexes and optimizing tables, as well as queries that have potential be slow in the future.
You can extend the log to include this information by making the following changes to the configuration file:
log_slow_admin_statements = 1– Includes administrative SQL commands such as
OPTIMIZE TABLE. This is rarely desirable, as these operations are typically run 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_statementssite for MySQL version 8.0.26 and earlier.
log_queries_not_using_indexes = 1– When this setting is enabled, queries that expect to retrieve all records from the target table or view will be logged, even if they did not exceed the slow query threshold. This can help identify when a query is missing an index or cannot use it. Queries that have an index available will still be logged if they lack constraints that limit the number of rows retrieved.
Logging queries that do not use indexes can significantly increase verbosity. There may be situations where a full index scan is expected or necessary. These queries will continue to appear in the log even if they cannot be resolved.
You can evaluate the limitation of queries without indexes by setting
log_throttle_queries_not_using_indexes variable. This defines the maximum number of logs that will be written in 60 seconds. Value
10 means that up to 10 queries will be recorded per minute. After the tenth event, no more queries will be logged until another 60 second window opens.
Interpretation of the slow query protocol
Every query that reaches the slow query log will display a set of lines that look similar to the following:
# Time: 2022-07-12T19:00:00.000000Z # [email protected]: 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 commented lines above the query include the time the query was executed, the MySQL user the client connected as, and statistics showing the duration and number of rows sent. The above example took 3.5 seconds to complete and looked at over 320,000 rows before only 5,143 were sent to the client. This could indicate that the missing indexes are causing MySQL to check too many records.
Optionally, you can include additional information in the log by setting
log_slow_extra = 1 system variable in your configuration file. This adds the thread ID, the number of bytes received and sent, and the number of rows considered for sorting, as well as command-specific request counts that provide insight into how MySQL processed the query.
The log file should be handled with care as its contents will be sensitive. Queries are displayed in full, without masking parameter values. This means that the user data will be present if you are using the slow query protocol on the production server. Access should be limited to developers and database administrators who debug SQL statements.
Slow query logging and backups
One common frustration with a slow query log arises when you also use MySQLDump to create database backups. Long lasting
SELECT * FROM ... queries will be made to retrieve data from your tables and put it in the backup. They will be included in the slow query log like any other SQL statement. This can cause log pollution if you back up regularly.
You can work around this problem by temporarily disabling the slow query log before starting
mysqldump. After the backup is complete, you can reactivate the log. Edit the backup script to look similar to the following:
#!/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 the MySQLDump activity out of the slow query log, making it easier for you to focus on the SQL being executed by your application.
MySQL’s slow query log is one of the most effective ways to determine the cause of performance problems. Start by estimating the delay that is occurring and use that as your value
long_query_time. Decrease the value if nothing appears in the log after reproducing the problem.
The slow query log doesn’t tell you exactly how to fix the slowness. However, the ability to view the exact SQL received by the server allows you to replay poorly performing commands and then measure the effect of optimizations. Adding an index or missing a constraint can be the difference between a query that touches thousands of rows and one that works with a handful.