When MySQL performance issues are encountered, one of the first things troubleshooting developers do is identify long running queries and optimize them. We will take a look at a MySQL feature that helps you identify long running queries – MySQL slow query log.
Understanding the Slow Query Log
In MySQL, the slow query log consists of SQL statements that take more time to execute than defined in the
long_query_time system variable. The system variable’s minimum and default values are 0 and 10 respectively. If a query execution takes more seconds than defined, the server increments the
Slow_queries variable – this counter increments regardless of whether the slow query log is enabled. It is worth noting that mysqld writes a statement to the slow query log only after it has been executed and all locks have been released, so the log order might be different than the query execution order.
Enabling the Slow Query Log
In order to enable the slow query log in MySQL, follow these steps:
- Log in to your server using SSH;
- Log in to MySQL, provide the root password by typing
mysql -u root -p;
- Enable the slow query log by typing
SET GLOBAL slow_query_log = 'ON';
When the slow query log is enabled, by default it logs any query that takes longer than 10 seconds to run. To change the interval, type the following command where x is the time in seconds:
SET GLOBAL long_query_time = x;
If no name for the slow query log file is specified, the default name is
host_name-slow.log. The file is located in the
/var/lib/mysql/ directory – the server creates the file there unless an absolute path to a different directory is given: in order to change the log path or the file name, run the command
SET GLOBAL slow_query_log_file = '/path/filename';
To verify that the slow query log is running correctly, reset the session variables by logging out and logging back into MySQL and type the following command where x is the amount of time in seconds:
The slow query log should now contain information about the query.
In order to disable the slow query log, run
SET GLOBAL slow_query_log = 'OFF';
Diving Deeper Into the MySQL Slow Query Log
The slow query log can be optimized further by enabling certain variables or using certain options:
- In order to enable slow administrative statements to be logged inside the query log, enable the
log_slow_admin_statementsvariable. Administrative statements include the
- To log queries not using indexes, enable the
log_queries_not_using_indexesvariable. It is worth noting that when queries not using indexes are logged, the slow query log may grow quickly – in order to put a rate limit on these queries, increase the
log_throttle_queries_not_using_indexesvariable to 1 (the default value is 0);
log_throttle_queries_not_using_indexesvariable limits the number of queries per minute that can be written to the slow query log. The default value of 0 means no limit;
- Queries that examine fewer rows that the number set in the
min_examined_row_limitvariable will not be logged in the slow query log;
- By default, a replication slave does not write replicated queries to the slow query log, though this can be changed by enabling the
log_slow_slave_statementssystem variable. This variable only has an effect if statement-based replication is used;
- The server does not log queries handled by the query cache.
It is also worth noting that the server writes less information to the slow query log if you use the
The Contents of the Slow Query Log File
When the slow query log is enabled, the server writes output to the destination specified by the log_output variable – if the destination is set to
NONE, no queries will be written to the log even if the slow query log will be enabled.
/var/log/mysql/mysql-slow.log file can only be accessed by the
root user using
sudo privileges – you will not be able to read the file when using another user.
Entries in the slow query log are preceded by the
# character and they contain the following:
Query_timecontains the query execution time in seconds;
Lock_timecontains the time to acquire locks in seconds;
Rows_sentcontains the number of rows sent to the client;
Rows_examinedcontains the number of rows examined by the server.
MySQL statement logging avoids logging passwords in statements written to the slow query log to not occur in plain text. MySQL avoids logging passwords in clear text for the
ALTER USER and
GRANT statements with
IDENTIFIED BY postfixes, the
SET PASSWORD statement, the
SLAVE START statement with the
PASSWORD postfix, also the
CREATE SERVER and
ALTER SERVER statements with the
mysqldumpslow to Examine The Slow Query Log
mysqldumpslow is a tool to examine the slow query log and group similar queries together. A few tool usage examples are provided below:
mysqldumpslowwithout any flags will show results of all the queries that ran in the database.
For example, the query
mysqldumpslow /var/log/mysql/mysql-slow.logcould provide the following results (the results are an example):
Reading mysql slow query log from /var/log/mysql/mysql-slow.log
Count: 1 Time=1.17s (1s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@localhost
SELECT * FROM table_1
Reading mysql slow query log from /var/log/mysql/mysql-slow.log
Count: 15 Time=127.41s (127s) Lock=0.00s (0s) Rows=851752.0 (851752), root[root]@localhost
SELECT * FROM table_2
- The query
mysqldumpslow -r -t 10 /var/log/mysql/mysql-slow.logwill show you the last 10 queries in a reversed sort order.
There are all sorts of queries that can be analyzed using the
mysqldumpslow tool, just play around with it.
mysqldumpslow supports various flags:
|Numbers will not be abstracted to N, strings will not be abstracted to S.
|Numbers with at least the specified number of digits will be abstracted.
|Only statements matching a specified pattern will be shown in the result set.
|Host name of the server can be provided in the log file name.
|Name of the server instance should be provided.
|Lock time should not be substracted from the total time.
|The sort order should be reversed.
|Define how to sort output. Output can be sorted by query time (“
t“), average query time (“
at“), lock time (“
l“), average lock time (“
al“), rows sent (“
r“), average rows sent (“
ar“) or by count (“
|Display only the first x amount of queries.
|Display debugging information.
|Display help message and exit.
|Enable verbose mode printing more information about what the program does.
To summarize, the slow query log feature in MySQL enables developers to log queries that exceed a specified time limit. By doing so developers can simplify the task of finding bottlenecks in MySQL query performance and eliminate inefficient or time-consuming queries.