Using pt-query-digest to Analyze MySQL Slow Query Logs

Author Zhang Wei, a DBA at iKang Beijing, shares his experience with MySQL daily issue handling and DMP product maintenance, focusing on the use of Percona Toolkit's pt-query-digest for slow‑query analysis.

Installation of Percona Toolkit

Download the latest RPM package:

wget https://www.percona.com/downloads/percona-toolkit/3.2.1/binary/redhat/7/x86_64/percona-toolkit-3.2.1-1.el7.x86_64.rpm

Install required Perl modules:

yum install perl-DBI.x86_64
yum install perl-DBD-MySQL.x86_64
yum install perl-IO-Socket-SSL.noarch
yum install perl-Digest-MD5.x86_64
yum install perl-TermReadKey.x86_64

Install the toolkit:

rpm -iv percona-toolkit-3.2.1-1.el7.x86_64.rpm
rpm -qa | grep percona

The binaries are placed in /usr/bin .

Check MySQL slow‑log configuration

mysql> show variables like '%slow%';
+---------------------------+-------------------------------------+
| Variable_name             | Value                               |
+---------------------------+-------------------------------------+
| log_slow_admin_statements | ON                                  |
| log_slow_slave_statements | ON                                  |
| slow_launch_time          | 2                                   |
| slow_query_log            | ON                                  |
| slow_query_log_file       | /opt/mysql/data/7777/mysql-slow.log |
+---------------------------+-------------------------------------+

Adjust the query‑time threshold for testing:

set global long_query_time=0.001000;

Run pt-query-digest on the slow log

pt-query-digest mysql-slow.log

The tool outputs overall statistics (total queries, unique queries, QPS, concurrency), time range, and attribute summaries such as execution time, lock time, rows sent/examined, and query size.

Interpretation of the output

The report is divided into three parts:

Overall information with resource usage and query distribution.

Profile section showing the top queries by response time, calls, and V/M score.

Detailed per‑query sections listing counts, execution/lock times, rows, query size, database, host, user, and time‑distribution histograms.

Example of a detailed query block:

# Query 1: 1.02k QPS, 10.94x concurrency, ID 0xFFFCA4D67EA0A788813031B8BBC3B329 at byte 26111916
# Exec time 90 328s 1ms 129ms 11ms 23ms 8ms 9ms
# String: COMMIT

To locate the original SQL in the slow‑log file, use the byte offset provided:

tail -c +26111916 ./mysql-slow.log | head

In production, the identified slow statements can be optimized based on the detailed metrics.

References

Percona Toolkit pt-query-digest documentation

MySQL Slow Query Log documentation