Enable MySQL slow query logging

MySQL is one of the most popular database management systems used for database driven sites (like WordPress). It is crucial that you are able to investigate issues with MySQL on your VPS or Dedicated server.

Steps

1. Login to your server through SSH as root.
2. Backup /etc/my.cnf as /etc/my.cnf.bak.$(date +%F_%R)
         this will save a backup of the file with the date and time
3. Edit the /etc/my.cnf file.

vi /etc/my.cnf

4. Insert the following lines in the [mysqld] section:
   mysql versions < than 5.5

   # Slow Query Log
   set-variable=long_query_time=4
   log-slow-queries=/var/log/slow-queries.log

   MySQL versions 5.5 and higher

   # Slow Query Log
   slow_query_log=1
   long_query_time=4
   slow_query_log_file=/var/log/slow-queries.log

   MariaDB
   

   # Slow Query Log
   slow_query_log
   long_query_time=4
   slow_query_log_file=/var/log/slow-queries.log

5. Save the file and exit the editor.
6. Create the log file and set the appropriate permissions
   

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

7. Restart the database service with the command appropriate to your enviornment.
   CentOS 6 cPanel MySQL CentOS 6

   service mysql restart
   service mysqld restart
   service mariadb restart

   CentOS 7

   systemctl mysqld restart
   systemctl mariadb restart

8. Check the slow query log to make sure it is running.
   

   cat /var/log/slow-queries.log

   You should see something similar to the following if it was properly enabled. Let MySQL run a few hours to capture a good amount of data.

   /usr/libexec/mysqld, Version: 5.0.45-log (Source distribution). started with:
   Tcp port: 0 Unix socket: /var/lib/mysql/mysql.sock
   Time Id Command Argument

Related Steps

• Review the slow query log.
• Review the PHP error log.

GoDaddy Hosting Services

Our server experts can enable/disable MySQL slow query logging and optimize MySQL. To learn more about this service, our GoDaddy Guides are here to help.

More Info