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.
NOTE: The steps below will not be available in a shared hosting (cPanel, Managed WordPress) environment due to the shared nature of the product.
- Login to your server through SSH as root.
- 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
- Edit the /etc/my.cnf file.
- 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.logMySQL versions 5.5 and higher
# Slow Query Log slow_query_log=1 long_query_time=4 slow_query_log_file=/var/log/slow-queries.logMariaDB
# Slow Query Log slow_query_log long_query_time=4 slow_query_log_file=/var/log/slow-queries.log
- Save the file and exit the editor.
- Create the log file and set the appropriate permissions
touch /var/log/slow-queries.log && chown mysql:mysql /var/log/slow-queries.log
- 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 restartCentOS 7
systemctl mysqld restart systemctl mariadb restart
- Check the slow query log to make sure it is running.
cat /var/log/slow-queries.logYou 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
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.