If you suspect that the MySQL performance of your server has degraded and want to dig deeper on finding the exact cause, enabling MySQL slow queries is a good place to start with.

The slow query log is a feature in MySQL which enables us to log queries that exceed a predefined time limit. This will greatly help us to easily find the long running queries that might be slowing down the SQL performance.

It can be enabled either by adding the required parameters into the my.cnf configuration file or directly through the mysql command prompt.

Using my.cnf:

Open the my.cnf file (usually located under /etc) with a text editor like vi or nano and add the following block of code under the mysqld section:

slow_query_log = 1
slow-query_log_file = /var/log/mysql-slow-queries.log
long_query_time = 2

For MySQL 5.6 and older, replace the slow-query_log_file variable with log -slow-queries variable.

When the slow query log feature is enabled, it will log any query that takes longer than 10 seconds to run. If you wish to change this interval then we can set the time using the parameter long_query_time = 2 (replacing 2 with the number of seconds).

The same can be achieved through the mysql command prompt by running the following commands as MySQL root:

SET GLOBAL slow_query_log = ‘ON’;

SET GLOBAL long_query_time = 2;

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

Log out of the mysql prompt, and then log back in as this will reload the session variables for the mysql program.

It is always better to test if the slow queries are being logged after enabling this feature. To do so, log into the mysql command prompt and run the following command:

SELECT SLEEP(15);

This query should be logged in the /var/log/mysql-slow-queries.log file if everything works fine.

You can also utilize the mysqldumpslow command to generate a summary of the log data, like

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

If you need expert assistance, please feel free to contact RackNerd! We can offer managed hosting solutions. Just get in touch with our sales team to discuss your requirements: sales@racknerd.com or check out our server hosting solutions below.