It’s a stressful situation when your website isn’t available. In such situations, you're typically checking the application logs for hints. If you can’t find anything in the logs, you have to check the system load, like CPU and memory usage. What process is taking up most of the CPU or memory usage and is that OK?

If MySQL is under high load, you can get more details on what’s going on in your database. This tutorial shows you ideas on how to debug high-load situations in your MySQL instance.

MySQL Series Overview

Show Full Process List

You can print the list of MySQL operations currently active in your database. MySQL executes SQL operations in threads. You can print out this process list using the following command:

SHOW FULL PROCESSLIST;  

The resulting process list may look like this. Please notice, that we’ve shortened the table to highlight the columns of interest. There may be more columns in your printed listing:

| Id | User | db | Command | Time | State | Info | |--------|---------|--------------|---------|------|--------------|-----------------------| | 109824 | dbuser | dbname | Query | 0 | starting | show full processlist | | 109827 | dbuser | dbname | Sleep | 0 | | NULL | | 109828 | db_user | futurestudio | Query | 2207 | Sending data | SELECT user.* FROM … |

The columns to look at when debugging high-load scenarios are Time and Info. You should check rows with a high time value. The Info contains the statement currently processed in the related thread. A thread is idle and not processing anything, the info value is NULL. You can map the info values to database queries from your application and check which ones are eating up the resources.

SHOW PROCESSLIST Truncates the Queries

We recommend using SHOW FULL PROCESSLIST because it prints the full SQL queries running on your database. You can also run SHOW PROCESSLIST to print out the active processes and truncated SQL queries. Typically it’s more useful to see the full query info. You may be fine with the truncated queries if you’re looking to get an overview of your database’s workload.

| Id | User | db | Command | Time | State | Info | |--------|---------|--------------|---------|------|--------------|---------------------------| | 109824 | dbuser | dbname | Query | 0 | starting | show full processlist | | 109828 | db_user | futurestudio | Query | 2207 | Sending data | SELECT user.* |

Stop SQL Processes

You may find long-running MySQL threads processing a statement. You can stop these threads using MySQL’s KILL statement. Stopping a thread causes an error in your application because the active query will fail. This is fine because you want your system to become responsive again.

You may stop an active MySQL process via its ID. The process list contains the Id column and you can stop a process using the KILL statement:

KILL <process-id>;

-- stop a thread via ID
KILL 109828;  

EXPLAIN SQL Queries

MySQL allows you to get more information about a given query and its execution plan using the EXPLAIN statement. You can put the EXPLAIN keyword in front of your actual SQL query and run it. The output is a list of information on how MySQL will process the statement. It prints the primary keys and index that will be used and also the expected number of matching rows.

Explaining queries is helpful with large queries that join multiple tables. Here’s the query execution plan for a sample query joining users with their company and the assigned subteam:

EXPLAIN  
    SELECT 
        users.*,
        companies.name as company_name,
        companies_subteams.name as subteam_name,
    FROM users
    INNER JOIN companies
        ON company.id = users.company_id

    LEFT JOIN companies_subteams
        ON companies_subteams.id = users.subteam_id

The SQL statement above prints this table:

| id | selecttype | table | type | possiblekeys | key | rows | Extra | |-----|-------------|--------------------|-------|------------------------------|-------------------------|------|-------------| | 1 | SIMPLE | users | const | PRIMARY,idxuserscompanyid | PRIMARY | 1 | | | 1 | SIMPLE | companies | const | PRIMARY | PRIMARY | 1 | | | 1 | SIMPLE | companiessubteams | ref | nameuniquepercompany | nameuniquepercompany | 4 | Using index |

The result of an EXPLAIN statement gives you an overview of used primary keys and indexes. It may hint you to a missing index in a table and point out full table scans.

Enable MySQL Slow Query Log

MySQL includes a slow query log. This log contains entries of SQL statements that take more seconds than defined in the long_query_time option. The long_query_time is an option in your my.cnf.

Also, you must enable the slow query log in your MySQL config. You can do that with the slow_query_log=1 toggle.

Here’s a sample MySQL configuration for the slow query log. Find and edit your MySQL configuration file. The default location is /etc/mysql/my.cnf. The MySQL configuration may already contain the options with different values. Go ahead and adjust them to your values of choice. If you can’t find the slow query log option, add it to that file.

slow_query_log=1  
long_query_time=1  
log_output=FILE  
slow_query_log_file=/var/lib/mysql/slow.log  

Notice: you must restart your MySQL instance on the server to apply the options. Restarting MySQL means your database has a downtime for a short time. Also, you may need to restart or reconnect your application to the MySQL database afterward.

That’s it!

---

Mentioned Resources

• MySQL docs for SHOW PROCESSLIST
• MySQL docs for the KILL statement
• MySQL docs for the EXPLAIN statement