GoDaddy Help

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.

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.
  4. vi /etc/my.cnf
  5. 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
  6. Save the file and exit the editor.
  7. Create the log file and set the appropriate permissions
    touch /var/log/slow-queries.log && chown mysql:mysql /var/log/slow-queries.log
  8. 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
  9. 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

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

To learn more about the hosting services GoDaddy offers, see our Hosting Services Menu.

Share this article