How to enable MySQL slow query log and analyze it on Linux

MySQL slow query log is helpful when you needed to debug slow down or failure of your MySQL server. Slow query log can house queries which took more than n number of seconds.

MySQL Slow query log could be enabled by editing MySQL configuration file named my.cnf and adding three lines of configuration code.

In this tutorial I will show you how to do it in simple steps.

1. Connect to server via SSH.
2. Enable the MySQL slow query log in the MySQL configuration file my.cnf:2.1. Open the my.cnf file in a text editor. In this example, we use the vi editor:
   • on CentOS/RHEL-based distributions

     vi /etc/my.cnf

   • on Debian/Ubuntu-based distributions

     vi /etc/mysql/my.cnf

   2.2. Add the records below under the [mysqld] section:

   Note: In MySQL 5.7 and MariaDB 10.X, the variable log-slow-queries is deprecated. Use slow_query_log_file instead.

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

   • where long_query_time – time taken by an SQL query to be executed in seconds. If a query takes longer than the value specified, this query will be recorded in the slow query log file.

   2.3. Save the changes and close the file.

3. Create the slow query logfile /var/log/mysql/slow.log and adjust ownership on it:

   touch /var/log/mysql/slow.log && chown mysql:mysql /var/log/mysql/slow.log

4. Restart MySQL. The command depends on operating system and installed MySQL version:

   service mysql restart

   OR

   service mysqld restart

   OR

   service mariadb restart

5. Start monitoring the slow query logfile. Use the command mysqldumpslow to analyze it and print summary of the slow query logfile.
   For example, to print all slow queries that have already been recorded, run the command:

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

   The full list of mysqldumpslow options is available here.

Share on Facebook

Tweet

Follow us

Save