Looking on how to enable slow query log in MySQL?

Users often enable this to analyze the performance of web applications and databases easily.

The slow query log is a record of SQL queries that takes a long time to execute.

At Bobcares, we often get requests from our customers to enable slow query log as part of our Server Management Services.

Today, let’s get into the details on how our Support Engineers enable slow query log.

Why use a slow query log?

Applications that use databases constantly fetch data using MySQL queries. Some queries get results rapidly, while some of them will be too slow.

These slow queries remarkably affect badly the performance of any web application. It causes poor database performance and eats up more server resources.

Thus, enabling MySQL server slow query log helps us to log those queries which exceed a predefined time limit.

In other words, Slow query log aid to ease the task of finding inefficient or time-consuming queries. Moreover, it works as the primary step in tweaking a MySQL server for best performance.

How to enable slow query log in MySQL

At Bobcares, where we have more than a decade of expertise in managing servers, we see many customers request to enable this.

Now, let’s see how our Support Engineers enable the slow query log by different methods.

1. Enabling via command line

Recently, one of the customers approached us for enabling the slow query log in his server. We assisted the customer via the MySQL method.

We helped the customer by following the below steps to enable the slow query log via MySQL.

1. Initially, we log in to the server via SSH.

2. We then create the following file.

/var/log/mysql/log-slow-queries.log

3. Thereafter we change the ownership of the file.

chown mysql.mysql -R /var/log/mysql

4. Then we access the MySQL server and add the following into the MySQL server.

Here, for enabling the slow query log, we typed the following command at the MySQL> prompt.

mysql> SET GLOBAL slow_query_log = 'ON';

5. We also set additional options for slow queries.

6. When a slow query log is enabled, then by default it logs the query longer than 10 seconds. Following command changes this interval by replacing ‘X’

SET GLOBAL long_query_time = X;

7. We change the default slow query log file (log path or filename) located at /var/lib/mysql/hostname-slow.log by the following command.

SET GLOBAL slow_query_log_file = '/path/filename';

8. Finally restart the MySQL service.

service mysqld restart

9. We then verify the working of the slow query log. To reload the session variables, we log out of the MySQL program and then log back in. We execute the command and replace X with value more than long_query_time setting.

SELECT SLEEP(X);

Now, the slow query log file contains information about the query.

2. Altering configuration file

Also, another method to enable slow query log is by altering the MySQL configuration file. Let’s see how we did this as part of MySQL server optimization.

1. Initially, we logged in to the server and edited the “mysqld” section of the configuration file /etc/my.cnf.

2. Next, we added the entry

slow_query_log=1

This modification involves defining the long query time too. To decide upon the appropriate values, we checked the MySQL queries on the server. Most of the requests completed in less than 5 seconds.

Therefore, to log queries which take more than five seconds to “/var/lib/mysql/mysqld-slow.log”, the final configuration was:

After altering we then saved the file.

2. We then restarted MySQL service.

service mysqld restart

[Need more help to enable slow query log in MySQL ?- We’re available 24/7.]

Conclusion

In short, to enable a slow query log in MySQL we either do it via command line or by altering the configuration file. Today, we saw how our Support Engineers help the customers to enable the slow query log in MySQL.