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:
Next, execute the following command to check the slow logging:
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:
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.
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”.
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:
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.
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.