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</code><code>yum install perl-DBD-MySQL.x86_64</code><code>yum install perl-IO-Socket-SSL.noarch</code><code>yum install perl-Digest-MD5.x86_64</code><code>yum install perl-TermReadKey.x86_64

Install the toolkit:

rpm -iv percona-toolkit-3.2.1-1.el7.x86_64.rpm</code><code>rpm -qa | grep percona

The binaries are placed in /usr/bin.

Check MySQL slow‑log configuration

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

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</code><code># Exec time 90 328s 1ms 129ms 11ms 23ms 8ms 9ms</code><code># 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