Skip to main content

Analyzing MySQL Slow Query Log

The slow query log is a valuable tool for identifying queries that are taking longer than expected to execute. This document outlines how to enable and analyze the MySQL slow query log.

1. Configure MySQL to Enable the Slow Query Log​

Edit the MySQL configuration file (my.cnf) to enable and configure the slow query log. The location of this file may vary depending on your operating system and MySQL installation. Common locations include:

  • /etc/mysql/my.cnf
  • /etc/my.cnf
  • /usr/etc/my.cnf
  • /etc/mysql/mysql.conf.d/mysqld.cnf (Debian/Ubuntu)

Important: Before editing, create a backup of your my.cnf file.

sudo cp /etc/mysql/my.cnf /etc/mysql/my.cnf.bak

Steps:

  1. Open the configuration file: Use your favorite text editor with root privileges.

    sudo nano /etc/mysql/my.cnf  # Example using nano. Adjust the path if necessary.

  2. Add or modify the following lines within the [mysqld] section:

    [mysqld]
    slow_query_log = 1
    slow_query_log_file = /var/log/mysql/mysql-slow.log  # Recommended: Create a dedicated directory
    long_query_time = 2
    log_output = FILE  # Ensure output is set to file
    • slow_query_log = 1: Enables the slow query log. Set to 0 to disable.
    • slow_query_log_file = /var/log/mysql/mysql-slow.log: Specifies the path to the slow query log file. It is highly recommended to store the log file in a dedicated directory (e.g., /var/log/mysql/) to improve organization and security. Make sure this directory exists.
    • long_query_time = 2: Sets the threshold (in seconds) for considering a query "slow". Queries taking longer than this value will be logged. Adjust this value to your needs (e.g., 1, 0.5, or even 0 for very sensitive systems). Remember that a lower value will increase the size of your log file. 0 logs all queries.
    • log_output = FILE: Specifies that the slow query log should be written to a file. (Alternative is TABLE). Note the system user (usually 'mysql') should have the permission to write to this directory and log file.

  3. (Optional) Configure Logging of Queries Without Indexes

    Add the following line to also log queries that are not using indexes. This can be very helpful for identifying performance bottlenecks.

    log_queries_not_using_indexes = 1

  4. Save the changes to my.cnf and exit the editor.

2. Create the Log File and Set Permissions​

If the specified log file or directory doesn't exist, you'll need to create it and set the correct permissions.

sudo mkdir -p /var/log/mysql # Create the directory if it doesn't exist
sudo touch /var/log/mysql/mysql-slow.log
sudo chown mysql:mysql /var/log/mysql/mysql-slow.log
sudo chmod 644 /var/log/mysql/mysql-slow.log #Set appropriate permissions
  • mkdir -p: Creates the /var/log/mysql directory if it doesn't exist. The -p option creates parent directories as needed.
  • touch: Creates the empty log file.
  • chown mysql:mysql: Changes the ownership of the log file to the mysql system user and group. This ensures that the MySQL server process can write to the file. The system user and group might vary depending on your operating system.
  • chmod: sets the file permissions. Change this to fit security requirements.

3. Restart MySQL​

Restart the MySQL server to apply the configuration changes.

sudo systemctl restart mysql  # For systemd-based systems (e.g., Ubuntu 16.04+, CentOS 7+)
# OR
sudo service mysql restart     # For systems using SysVinit (older systems)

Important: Verify that MySQL has restarted successfully using sudo systemctl status mysql or sudo service mysql status. Check the MySQL error log for any startup issues.

4. Analyze the Log File​

Several tools and techniques can be used to analyze the MySQL slow query log:

4.1. mysqldumpslow (Command-Line Tool)​

mysqldumpslow is a MySQL utility that summarizes and sorts the slow query log.

mysqldumpslow -t 10 -s at /var/log/mysql/mysql-slow.log  # Top 10 queries by average query time
  • -t 10: Displays the top 10 queries.
  • -s at: Sorts by average query time. Other sorting options include:
    • c: Count
    • t: Time
    • l: Lock time
    • r: Rows sent
    • at: Average time
    • al: Average lock time
    • ar: Average rows sent
  • -a: Don't abstract all numbers to N and strings to 'S'. When used, mysqldumpslow will print the raw queries with the specific values.
  • -g: allows pattern matching of the queries inside the slow log file.

4.2. pt-query-digest (Percona Toolkit)​

pt-query-digest is a more advanced slow query log analysis tool from Percona Toolkit. It provides more detailed statistics and insights into query performance.

Installation:

sudo apt-get update  # Debian/Ubuntu
sudo apt-get install percona-toolkit

# OR

sudo yum install percona-toolkit # CentOS/RHEL

pt-query-digest /var/log/mysql/mysql-slow.log