MySQL Slow Query Logs

Summary: in this tutorial, you will learn about MySQL slow query logs, configurations, and the mysqldumpslow utility to examine slow queries.

Introduction to MySQL slow query logs

When you execute queries that take longer than a specified threshold, MySQL treats them as slow queries.

Subsequently, MySQL records these slow queries in logs called slow query logs.

These slow query logs are a crucial tool to help you analyze and optimize your database performance.

MySQL slow query log configurations

1) Checking the current configuration

To check the current slow query log configuration, you follow these steps:

First, open the Command Prompt on Windows or Terminal on Unix-like systems and connect to the MySQL server:

mysql -u root -p

Second, check the slow query log variables:

SHOW VARIABLES LIKE '%slow_query%';Code language: JavaScript (javascript)

Output:

+---------------------+----------------------------------+
| Variable_name       | Value                            |
+---------------------+----------------------------------+
| slow_query_log      | OFF                              |
| slow_query_log_file | /var/lib/mysql/mysql-slow.log    |
+---------------------+----------------------------------+
2 rows in set (0.00 sec)Code language: JavaScript (javascript)

The value of slow_query_log indicates whether the slow query log is enabled. If set to ON, it means the slow query log is enabled; If set to OFF, it means the slow query log is disabled.

MySQL disables the slow query logs by default. Therefore, the value of the slow_query_log option is OFF.

The slow_query_log_file option specifies the log file where MySQL records the slow queries.

2) Setting up a slow query log file

First, make a new directory called mysql in the /var/log/ directory:

mkdir -p /var/log/mysql/Code language: JavaScript (javascript)

Second, create a new file called mysql-slow.log inside the /var/log/mysql/ directory:

touch /var/log/mysql/mysql-slow.logCode language: JavaScript (javascript)

Third, change permission for the mysql user to the directory:

chown -R mysql:mysql /var/log/mysql/Code language: JavaScript (javascript)

3) Enabling slow query logs

To enable slow query logs, modify the MySQL configuration file by adding the following lines:

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1Code language: JavaScript (javascript)

In this file:

• slow_query_log: Set to 1 to enable slow query logging.
• slow_query_log_file: Specify the path to the log file, which is /var/log/mysql/mysql-slow.log.
• long_query_time: Specify the threshold in seconds, if the query is taking longer than this, it is logged. We put one second for demonstration purposes.

After saving the MySQL configuration file, you need to restart the MySQL server.

Alternatively, you can set the corresponding variables at run time by following these steps:

First, connect to the MySQL server:

mysql -u root -p

Second, set the slow query logs-related options:

set global slow_query_log = 1;
set global slow_query_log_file = '/var/log/mysql/mysql-slow.log';
set global long_query_time = 1;Code language: PHP (php)

Third, retrieves the variables to verify the changes:

select @@slow_query_log, @@slow_query_log_file, @@long_query_time;Code language: CSS (css)

Output:

+------------------+-------------------------------+-------------------+
| @@slow_query_log | @@slow_query_log_file         | @@long_query_time |
+------------------+-------------------------------+-------------------+
|                1 | /var/log/mysql/mysql-slow.log |          1.000000 |
+------------------+-------------------------------+-------------------+
1 row in set (0.00 sec)Code language: JavaScript (javascript)

4) Executing a slow query

Execute the following query that takes 3 seconds to complete:

select sleep(3);

5) Analyzing slow queries

To analyze the slow query, you can review the slow query log file:

cat '/var/log/mysql/mysql-slow.log'Code language: JavaScript (javascript)

Here’s the extracted contents:

# User@Host: root[root] @ localhost []  Id:    11
# Query_time: 3.000500  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 1
SET timestamp=1703580567;
select sleep(3);Code language: PHP (php)

The last line shows the slow query that took more than three seconds, which surpasses the one second specified in the long_query_time.

To make it more convenient, MySQL offers the mysqldumpslow utility that parses the slow query logs and summarizes the queries.

Execute the following command in the terminal to examine the slow queries stored in the mysql-slow.log file:

mysqldumpslow /var/log/mysql/mysql-slow.logCode language: JavaScript (javascript)

Output:

Reading mysql slow query log from /var/log/mysql/mysql-slow.log
Count: 1  Time=3.00s (3s)  Lock=0.00s (0s)  Rows=1.0 (1), root[root]@localhost
  select sleep(N)Code language: JavaScript (javascript)

Summary

• MySQL slow query logs store queries that took longer than a specified threshold to execute.
• Use mysqldumpslow utility to examine the slow queries in the slow query logs.