pt-query-digest is a powerful utility within the Percona Toolkit suite designed for analyzing MySQL performance, specifically targeting slow queries. It can process various input sources, including the MySQL slow query log, binary logs, general logs, or even network traffic captured via tcpdump. The tool's core functionality involves normalizing query statements by parameterizing thier conditions, which allows it to group similar queries together. It then provides comprehensive statistics for each group, including execution time, frequency, and resource utilization, enabling data base administrators to identify and address performance bottlenecks effectively.

Installation

Before using pt-query-digest, you need to install the Percona Toolkit. Here are the steps for a typical Linux environment.

Prerequisites

Ensure Perl and necessary modules are installed. For Debian/Ubuntu systems:

apt-get install -y perl libdbi-perl libdbd-mysql-perl libio-socket-ssl-perl libnet-ssleay-perlInstallation Methods

Method 1: Package Manager (APT)

apt-get update && apt-get install -y percona-toolkitThe tool will be installed in the system's PATH, typically at /usr/bin/pt-query-digest.

Method 2: Source Code

cd /optwget https://www.percona.com/downloads/percona-toolkit/3.5.5/tarball/percona-toolkit-3.5.5.tar.gztar -xzf percona-toolkit-3.5.5.tar.gzcd percona-toolkit-3.5.5./configure --prefix=/opt/percona-toolkitmakemake installThe executables will be located in /opt/percona-toolkit/bin.

Key Features and Command-Line Options

pt-query-digest offers a rich set of options to customize its analysis. Here are some of the most important ones:

• --create-review-table, --create-history-table: Automatically create the specified tables in the database if they do not exist when using the --review or --history options.
• --filter: Apply a Perl-based filter to the input queries. Only queries matching the filter condition will be analyzed.
• --limit: Restrict the output. By default, it shows the slowest 20 queries. You can specify a percentage (e.g., 50%) to output queries until their cumulative response time reaches that percentage.
• --host, --user, --password: Database connection credentials for when saving results to a table.
• --history: Save detailed analysis results to a history table in a MySQL database. This allows tracking query performance changes over time.
• --review: Save a summarized version of the analysis to a review table. It only stores one entry per unique query fingerprint.
• --output-format: Specify the output format. Common choices are report (default), slowlog, json, or json-anon.
• --start-time, --end-time: Define a time window for analysis. The values can be a specific timestamp (e.g., '2023-10-27 14:30:00') or a relative time (e.g., '3h ago', '1d').

Interpreting the Analysis Report

The output of pt-query-digest is structured into three main sections, providing a comprehensive overview of query performance.

Section 1: Overall Statistics

This section provides a high-level summary of the entire log file being analyzed.

• Overall: Total number of queries and unique query fingerprints.
• Time range: The start and end timestamps of the queries in the log.
• Attributes (total, min, max, avg, 95%, stddev, median): Statistical breakdown of key metrics like execution time, lock time, rows sent, and rows examined. The 95th percentile is particularly useful as it shows the value below which 95% of the observations fall, helping to identify outliers.

# Overall: 150 total, 45 unique, 12.5 QPS, 1.2x concurrency# Time range: 2023-10-26 10:00:00 to 2023-10-26 11:00:00# Attribute total min max avg 95% stddev median# ============ ======= ======= ======= ======= ======= ======= =======# Exec time 45.2s 10ms 5.1s 301ms 1.2s 780ms 150ms# Lock time 2.1s 0us 800ms 14ms 100ms 120ms 5ms# Rows sent 1.2M 1 50K 8.5K 25K 12K 4.2K# Rows examine 45.8M 0 2.1M 305.3K 1.1M 510.2K 150.7KSection 2: Query Profile

This section ranks the queries by their impact, typically by total response time.

• Rank: The query's position in the ranking.
• Query ID: A hash of the normalized query statement.
• Response time: The total time spent executing this type of query.
• time: The percentage of the total analysis time this query consumed.
• calls: How many times this query was executed.
• R/Call: The average response time per call.
• V/M: Variance-to-Mean ratio, indicating the consistency of execution time.
• Item: The type of query (e.g., SELECT, UPDATE).

# Profile# Rank Query ID Response time Calls R/Call V/M Item# ==== ================== ============= ===== ====== ===== ===============# 1 0x8A3F1B7C5E2D9F0A 18.5s 40.9% 120 154ms 0.85 SELECT# 2 0x4C9A2B8D6E1F0C3D 7.2s 15.9% 85 85ms 0.30 UPDATE ordersSection 3: Detailed Query Statistics

This section provides an in-depth look at each individual query from the profile.

• Query ID: Matches the ID from the profile section.
• Attribute breakdown: Detailed statistics (pct, total, min, max, avg, 95%, stddev, median) for execution time, lock time, rows sent, etc.
• Query time distribution: A visual histogram showing the distribution of execution times.
• Explain: The actual SQL statement being analyzed.

# Query 1: 2 QPS, 0.1x concurrency, ID 0x8A3F1B7C5E2D9F0A at byte 1234# This query is included due to the --limit option.# Scores: V/M = 0.85# Time range: 2023-10-26 10:15:00 to 2023-10-26 10:45:00# Attribute pct total min max avg 95% stddev median# ============ === ======= ======= ======= ======= ======= ======= =======# Count 80 120# Exec time 40 18.5s 50ms 5.1s 154ms 1.2s 780ms 150ms# Lock time 5 1.1s 0us 800ms 9.2ms 100ms 120ms 5ms# Rows sent 30 360.5k 1 10K 3.0k 8.5k 2.5k 2.1k# Rows examine 0 12.5M 0 500k 104k 350k 120k 100k# Query size 2 5.4k 45 120 45.3 120 15 45# Databases sales# Hosts 192.168.1.100# Users app_user# Query time distribution# 1ms# 10ms ############################################################# 100ms# 1s# 10s+# EXPLAIN# SELECT * FROM products WHERE category_id = 5 ORDER BY price DESC;Practical Examples

Here are several practical use cases for pt-query-digest.

1. Analyze a Slow Query Log File

pt-query-digest /var/log/mysql/performance_log.txt > analysis_report.log2. Analyze Queries from the Last 6 Hours

pt-query-digest --start-time='6h ago' /var/log/mysql/performance_log.txt > recent_analysis.log3. Analyze Queries Within a Specific Time Range

pt-query-digest /var/log/mysql/performance_log.txt --start-time '2023-10-27 09:00:00' --end-time '2023-10-27 10:00:00' > timeboxed_analysis.log4. Filter for UPDATE Statements Only

pt-query-digest --filter '($event->{fingerprint} || "") =~ m/^UPDATE/i' /var/log/mysql/performance_log.txt > update_queries.log5. Filter by a Specific User

pt-query-digest --filter '($event->{user} || "") =~ m/^app_user/i' /var/log/mysql/performance_log.txt > app_user_queries.log6. Find Full Table Scans

pt-query-digest --filter '(($event->{Full_scan} || "") eq "yes")' /var/log/mysql/performance_log.txt > full_scan_queries.log7. Save Results to a Review Table

pt-query-digest --user=monitor --password='P@ssw0rd' --review h=192.168.1.50,D=performance_db,t=query_review --create-review-table /var/log/mysql/performance_log.txt8. Analyze Traffic from a tcpdump Capture

tcpdump -i eth0 -nn -s 65535 -w mysql_capture.pcap port 3306 && pt-query-digest --type tcpdump mysql_capture.pcap > network_analysis.log9. Analyze a Binary Log

mysqlbinlog /var/lib/mysql/mysql-bin.000045 > binlog.sql && pt-query-digest --type=binlog binlog.sql > binlog_analysis.log10. Analyze the General Log

pt-query-digest --type=genlog /var/log/mysql/general.log > general_log_analysis.log