Identifying Slow Queries in MySQL: Slow Query Log 101
Tags: mysql, database, webdev, programming
Published: 2022-11-12 07:00:00

<!-- wp:paragraph -->
<p>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.</p>
<!-- /wp:paragraph -->

<!-- wp:heading -->
<h2>Understanding the Slow Query Log</h2>
<!-- /wp:heading -->

<!-- wp:paragraph -->
<p>In MySQL, the slow query log consists of SQL statements that take more time to execute than defined in the <code>long_query_time</code> 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 <code>Slow_queries</code> 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 <strong>after it has been executed and all locks have been released</strong>, so the log order might be different than the query execution order.</p>
<!-- /wp:paragraph -->

<!-- wp:heading -->
<h2>Enabling the Slow Query Log</h2>
<!-- /wp:heading -->

<!-- wp:paragraph -->
<p>In order to enable the slow query log in MySQL, follow these steps:</p>
<!-- /wp:paragraph -->

<!-- wp:list {"ordered":true} -->
<ol><li>Log in to your server using SSH;</li><li>Log in to MySQL, provide the root password by typing <code>mysql -u root -p</code>;</li><li>Enable the slow query log by typing <code>SET GLOBAL slow_query_log = 'ON';</code></li></ol>
<!-- /wp:list -->

<!-- wp:paragraph -->
<p>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 <strong>x</strong> is the time in seconds:</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p><code>SET GLOBAL long_query_time = <b>x</b>;</code></p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>If no name for the slow query log file is specified, the default name is <code>host_name-slow.log</code>. The file is located in the <code>/var/lib/mysql/</code> 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 <code>SET GLOBAL slow_query_log_file = '/path/filename';</code></p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>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 <strong>x</strong> is the amount of time in seconds:</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p><code>SELECT SLEEP(<b>x</b>);</code></p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>The slow query log should now contain information about the query.</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>In order to disable the slow query log, run <code>SET GLOBAL slow_query_log = 'OFF';</code></p>
<!-- /wp:paragraph -->

<!-- wp:heading -->
<h2>Diving Deeper Into the MySQL Slow Query Log</h2>
<!-- /wp:heading -->

<!-- wp:paragraph -->
<p>The slow query log can be optimized further by enabling certain variables or using certain options:</p>
<!-- /wp:paragraph -->

<!-- wp:list -->
<ul><li>In order to enable slow administrative statements to be logged inside the query log, enable the <code>log_slow_admin_statements</code> variable. Administrative statements include the <code>ALTER TABLE</code>, <code>ANALYZE TABLE</code>, <code>CHECK TABLE</code>, <code>CREATE INDEX</code>, <code>DROP INDEX</code>, <code>OPTIMIZE TABLE</code> and <code>REPAIR TABLE</code> statements;</li><li>To log queries not using indexes, enable the <code>log_queries_not_using_indexes</code> variable. 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 <code>log_throttle_queries_not_using_indexes</code> variable to 1 (the default value is 0);</li><li>The <code>log_throttle_queries_not_using_indexes</code> variable limits the number of queries per minute that can be written to the slow query log. The default value of 0 means no limit;</li><li>Queries that examine fewer rows that the number set in the <code>min_examined_row_limit</code> variable will not be logged in the slow query log;</li><li>By default, a replication slave does not write replicated queries to the slow query log, though this can be changed by enabling the <code>log_slow_slave_statements</code> system variable. This variable only has an effect if statement-based replication is used;</li><li>The server does not log queries handled by the query cache.</li></ul>
<!-- /wp:list -->

<!-- wp:paragraph -->
<p>It is also worth noting that the server writes less information to the slow query log if you use the <code>--log-short-format</code> option.</p>
<!-- /wp:paragraph -->

<!-- wp:heading -->
<h2>The Contents of the Slow Query Log File</h2>
<!-- /wp:heading -->

<!-- wp:paragraph -->
<p>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 <code>NONE</code>, no queries will be written to the log even if the slow query log will be enabled.</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>The <code>/var/log/mysql/mysql-slow.log</code> file can only be accessed by the <code>root</code> user using <code>sudo</code> privileges - you will not be able to read the file when using another user.</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>Entries in the slow query log are preceded by the <code>#</code> character and they contain the following:</p>
<!-- /wp:paragraph -->

<!-- wp:list -->
<ul><li><code>Query_time</code> contains the query execution time in seconds;</li><li><code>Lock_time</code> contains the time to acquire locks in seconds;</li><li><code>Rows_sent</code> contains the number of rows sent to the client;</li><li><code>Rows_examined</code> contains the number of rows examined by the server.</li></ul>
<!-- /wp:list -->

<!-- wp:paragraph -->
<p>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 <code>CREATE USER</code>, <code>ALTER USER</code> and <code>GRANT</code> statements with <code>IDENTIFIED BY</code> postfixes, the <code>SET PASSWORD</code> statement, the <code>SLAVE START</code> statement with the <code>PASSWORD</code> postfix, also the <code>CREATE SERVER</code> and <code>ALTER SERVER</code> statements with the <code>OPTIONS</code> postfix.</p>
<!-- /wp:paragraph -->

<!-- wp:heading -->
<h2>Using <code>mysqldumpslow</code> to Examine The Slow Query Log</h2>
<!-- /wp:heading -->

<!-- wp:paragraph -->
<p><code>mysqldumpslow</code> is a tool to examine the slow query log and group similar queries together. A few tool usage examples are provided below:</p>
<!-- /wp:paragraph -->

<!-- wp:list -->
<ul><li>Running <code>mysqldumpslow</code> without any flags will show results of all the queries that ran in the database.<br>For example, the query <code>mysqldumpslow /var/log/mysql/mysql-slow.log</code> could provide the following results (the results are an example):</li></ul>
<!-- /wp:list -->

<!-- wp:paragraph -->
<p><code>Reading mysql slow query log from /var/log/mysql/mysql-slow.log<br>Count: 1 Time=1.17s (1s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@localhost<br>SELECT * FROM table_1</code></p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p><code>Reading mysql slow query log from /var/log/mysql/mysql-slow.log <br>Count: 15 Time=127.41s (127s) Lock=0.00s (0s) Rows=851752.0 (851752), root[root]@localhost<br>SELECT * FROM table_2</code></p>
<!-- /wp:paragraph -->

<!-- wp:list -->
<ul><li>The query <code>mysqldumpslow -r -t 10 /var/log/mysql/mysql-slow.log</code> will show you the last 10 queries in a reversed sort order.</li></ul>
<!-- /wp:list -->

<!-- wp:paragraph -->
<p>There are all sorts of queries that can be analyzed using the <code>mysqldumpslow</code> tool, just play around with it.</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p><code>mysqldumpslow</code> supports various flags:</p>
<!-- /wp:paragraph -->

<!-- wp:table -->
<figure class="wp-block-table"><table><tbody><tr><td><b><center>Option Name</center></b></td><td><b><center>Description</center></b></td></tr><tr><td>-a</td><td>Numbers will not be abstracted to N, strings will not be abstracted to S.</td></tr><tr><td>-n</td><td>Numbers with at least the specified number of digits will be abstracted.</td></tr><tr><td>-g</td><td>Only statements matching a specified pattern will be shown in the result set.</td></tr><tr><td>-h</td><td>Host name of the server can be provided in the log file name.</td></tr><tr><td>-i</td><td>Name of the server instance should be provided.</td></tr><tr><td>-l</td><td>Lock time should not be substracted from the total time.</td></tr><tr><td>-r</td><td>The sort order should be reversed.</td></tr><tr><td>-s</td><td>Define how to sort output. Output can be sorted by query time ("<code>t</code>"), average query time ("<code>at</code>"), lock time ("<code>l</code>"), average lock time ("<code>al</code>"), rows sent ("<code>r</code>"), average rows sent ("<code>ar</code>") or by count ("<code>c</code>").</td></tr><tr><td>-t</td><td>Display only the first <strong>x</strong> amount of queries.</td></tr><tr><td>--debug</td><td>Display debugging information.</td></tr><tr><td>--help</td><td>Display help message and exit.</td></tr><tr><td>--verbose</td><td>Enable verbose mode printing more information about what the program does.</td></tr></tbody></table><figcaption><center>List of available <code>mysqldumpslow</code> flags</center></figcaption></figure>
<!-- /wp:table -->

<!-- wp:heading -->
<h2>Summary</h2>
<!-- /wp:heading -->

<!-- wp:paragraph -->
<p>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.</p>
<!-- /wp:paragraph -->