Enable MySQL Slow Query Log and Use pt-query-digest to Find Database Bottlenecks - ITFROMZERO - Share tobe shared! A guide to enabling MySQL Slow Query Log without restarting the server and using pt-query-digest to dissect and rank CPU-bottlenecking queries in production.

Table of Contents

When Your Database Suddenly Overloads: Where Do You Start?

Your website is running smoothly when server CPU suddenly spikes to 99%. API response times surge from 50ms to 8s, database connections hit the max_connections ceiling, and users are repeatedly hit with 504 Gateway Timeout errors. The issue usually doesn’t lie with the entire system. Most often, it’s just a few poorly written queries silently scanning millions of records and draining all your resources.

Open error.log? You won’t find anything unusual. The queries are syntactically valid and throw no errors—they just take too long to execute. Amid millions of daily requests, you cannot afford to guess which query is bringing your server down.

The Core Duo: Slow Query Log and pt-query-digest

To tackle the root cause based on empirical data, you need two essential tools: Slow Query Log and pt-query-digest.

What Is the Slow Query Log?

MySQL comes with a built-in Slow Query Log feature. You only need to set a time threshold using the long_query_time variable, such as 0.5 seconds. Any query executing longer than this threshold, or performing table scans without an index, is fully recorded in a dedicated log file.

Why Raw Logs Aren’t Enough

After 24 hours of operation on a mid-sized e-commerce website, the log file can easily bloat to 2GB with over 300,000 lines. Trying to parse this file with less or grep is practically impossible.

This is where pt-query-digest from the Percona Toolkit comes into play. It parses the entire log file, groups queries with identical structure (replacing specific literals with question marks ?), calculates their execution time share, and ranks the queries imposing the highest burden on your system.

Practical Setup and Analysis Steps

Step 1: Enable Slow Query Log Without Restarting MySQL

Restarting MySQL during peak hours is a major taboo in production environments. Fortunately, you can configure logging on the fly within an active MySQL session:

mysql -u root -p

Next, run the runtime configuration commands:

-- Enable Slow Query Log
SET GLOBAL slow_query_log = 'ON';

-- Log file path
SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log';

-- Log queries executing longer than 0.5 seconds (supports microseconds since MySQL 5.1+)
SET GLOBAL long_query_time = 0.5;

-- Also log queries that do not use indexes
SET GLOBAL log_queries_not_using_indexes = 'ON';

Note: long_query_time supports decimal values such as 0.2 or 0.5. The default 1.0-second threshold is often too high for high-traffic web applications.

To persist these settings across server reboots, open your MySQL configuration file (typically located at /etc/mysql/mysql.conf.d/mysqld.cnf or /etc/my.cnf) and add them under the [mysqld] block:

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 0.5
log_queries_not_using_indexes = 1

Ensure write permissions are properly configured for the mysql user to avoid silent logging failures:

sudo touch /var/log/mysql/mysql-slow.log
sudo chown mysql:mysql /var/log/mysql/mysql-slow.log
sudo chmod 640 /var/log/mysql/mysql-slow.log

Step 2: Install Percona Toolkit

Install on Ubuntu/Debian via apt:

sudo apt update
sudo apt install -y percona-toolkit

For CentOS, RHEL, or AlmaLinux:

sudo yum install -y https://repo.percona.com/yum/percona-release-latest.noarch.rpm
sudo percona-release enable-only tools release
sudo yum install -y percona-toolkit

Step 3: Analyze the Log File with pt-query-digest

Let the system run through at least one peak traffic window (2 to 4 hours) to gather a representative sample. Then, generate the report to a text file:

pt-query-digest /var/log/mysql/mysql-slow.log > /tmp/slow_report.txt
less /tmp/slow_report.txt

The report consists of 3 main sections:

Overall Metrics: Total execution time, unique query count, and average system load.
Profile: A ranked table of query groups by percentage of total execution time (Response Time %).
Query Details: In-depth metrics for each query: call count, min/max/95th percentile execution times, and examined vs. sent row ratios.

The profile ranking will look similar to this:

# Profile
# Rank Query ID           Response time  Calls R/Call   V/M   Item
# === ================== ============== ====== ======== ===== ==============
#   1 0x4F18B9A6D2C8E7B1 1420.50s 65.2%   2300   0.6176  0.02 SELECT orders
#   2 0x9A3B5C7D1E8F2A40  420.10s 19.3%    120   3.5008  0.15 SELECT users

Don’t be fooled by the Rank 2 query (taking 3.5s). The Rank 1 query (SELECT orders) takes only 0.61s per execution, but it is called 2,300 times and accounts for 65.2% of total database response time. Optimizing this query alone will relieve nearly two-thirds of the server load.

Step 4: Analyze Rows Examine vs. Rows Sent to Optimize

Scroll down to the details section for the Rank 1 query and examine the following metrics:

# Attribute          pct   total     min     max     avg     95%  stddev  median
# Exec time           65   1420s   100ms      2s   617ms      1s   200ms   500ms
# Rows examine        88   1200M     50k    600k    521k    580k     50k    500k
# Rows sent            1    2.3k       1       5       1       2       1       1

The data reveals:

Rows examine: scans an average of 521,000 rows.
Rows sent: returns exactly 1 row.

A ratio of 521,000 : 1 is a textbook signature of a Full Table Scan. MySQL has to comb through more than half a million records just to fetch a single row.

Inspect the query execution plan using EXPLAIN:

EXPLAIN SELECT * FROM orders WHERE status = 'PENDING' AND customer_id = 9821;

If the type column shows ALL, add a composite index immediately:

ALTER TABLE orders ADD INDEX idx_customer_status (customer_id, status);

After adding the index, the number of examined rows drops from 521,000 to just 1. Response time drops from over 600ms to under 2ms.

Production note: On tables with tens of millions of rows, a standard ALTER TABLE can trigger table locks or replication lag. Consider using online schema change tools such as pt-online-schema-change or gh-ost to prevent downtime.

Summary

Database optimization should never rely on guesswork. Enabling the Slow Query Log records the exact ground truth. Using pt-query-digest quantifies the impact of each query pattern. By combining these two tools, you will know precisely where every millisecond of CPU time is wasted and resolve the bottlenecks that deliver the greatest impact.