How to Log Slow Queries in MySQL

When working with MySQL, you often encounter an issue with slow queries affecting your database’s performance. The slow performance mainly affects your application, especially when dealing with many users in a case of a large website or application.

The slow queries affect the data retrieval and other operations related to your application or website such as page rendering. You can fix and evade the slow queries affecting your database by analyzing, optimizing, and tuning them earlier. This post helps you understand how to log the slow queries in MySQL to ensure that you optimize your database’s performance.

Understanding the MySQL Slow Queries

There are cases when you must log the queries that take longer than the expected threshold. When unaccounted for, these queries slow your database performance. However, MySQL offers a slow query feature to let the users log the queries with a longer execution time.

The slow query feature is disabled by default. When you want to use it, you must activate it and specify how you want to log the slow queries. You can verify the slow query status in your MySQL database by checking it with the following command.

Open the MySQL server and log in as follows:

mysql -u root -p

Next, execute the following command to check the slow logging:

SHOW GLOBAL VARIABLES LIKE 'slow_query_log%';

Once you execute it, you get a similar output to the one in the following. We can see that the status is currently set to OFF. Therefore, the slow queries are currently not being logged. We will see how you can enable it to ensure that we log the slow queries.

How to Log the Slow Queries in MySQL

It’s worth noting that enabling the slow queries is not always recommended. You should only enable it whenever you want to enhance the performance or investigate an issue with your database. Even when you enable the slow queries, the status is disabled after you reboot your MySQL server.

There are a couple of steps that you must take when you want to enable the slow queries in your database.

Step 1: Set the Slow_Query_Log to ON

The slow query must be enabled by setting its parameter to 1 with the following command:

SET GLOBAL slow_query_log = 1;

Step 2: Set the Slow_Query_Time

Once we enable the slow query feature, we must specify the duration in seconds for which any query with an execution time longer than the specified time should be logged. The default time for slow queries is 10. With the following command, we can set a new time such as 6 seconds.

SET GLOBAL long_query_time = 6;

Note that the long_query_time that you set depends on your preference. Suppose you want to log all queries, set it to 0.

Step 3: Change the Slow Log File Location

MySQL stores the slow query log file by default in the “/var/lib/mysql/hostname-slow.log” location. However, you should specify a new location and change the log file’s name. The following command specifies the new location and name as “/var/log/mysql/mysql-slow.log”.

SET GLOBAL slow_query_log_file = ‘/var/log/mysql/mysql-slow.log’;

Step 4: Include the “Not Using Indexes”

When setting the slow queries, it’s recommended to indicate the “not to use indexes” log queries. For that, execute the following command:

SET GLOBAL log_queries_not_using_indexes = 1;

With that, you successfully set your database to log the slow queries. You can confirm that all integrations are accommodated by checking the status of the slow query with the earlier command. In this case, we should have the status set to ON.

Conclusion

With MySQL, it’s possible to log the slow queries by changing the status of the slow query feature. By default, the status is disabled. But this post explained the steps that you must follow to enable it and set the ground to log the slow queries in MySQL.