pt-query-digest and Percona Tools: Analysing WordPress Slow Query Logs

MySQL’s slow query log captures every query that exceeds a configurable threshold, but reading thousands of raw log lines is impractical. Percona’s pt-query-digest aggregates them into a ranked report, showing the queries with the highest total execution time, call count, and worst-case duration.

Problem: The MySQL slow query log records individual slow queries, but on a busy WordPress server it accumulates thousands of entries — identifying the most impactful queries to optimise requires aggregating and analysing patterns across the log.

Solution: Use pt-query-digest from Percona Toolkit to parse the slow query log — it groups similar queries by fingerprint, ranks them by total execution time, and outputs the call count, average duration, and worst-case example for each. Run it with pt-query-digest /var/log/mysql/slow.log | head -200 to get an immediate priority list.

The examples below enable the slow query log, run pt-query-digest against it, interpret the output, and use EXPLAIN to fix the top offender in a WordPress database.

-- Enable the slow query log at runtime (no restart needed)
SET GLOBAL slow_query_log        = ON;
SET GLOBAL slow_query_log_file   = '/var/log/mysql/slow.log';
SET GLOBAL long_query_time       = 0.5;    -- log queries slower than 500ms
SET GLOBAL log_queries_not_using_indexes = ON;  -- also log full-table-scans

-- Persist in my.cnf for survival across restarts:
-- [mysqld]
-- slow_query_log        = 1
-- slow_query_log_file   = /var/log/mysql/slow.log
-- long_query_time       = 0.5
-- log_queries_not_using_indexes = 1

-- Rotate the log without restarting MySQL:
-- FLUSH SLOW LOGS;

-- Check current status:
SHOW VARIABLES LIKE 'slow_query%';
SHOW GLOBAL STATUS LIKE 'Slow_queries';

Run pt-query-digest and interpret the report:

# Install Percona Toolkit
sudo apt install percona-toolkit

# Analyse the slow log — generates a ranked summary
pt-query-digest /var/log/mysql/slow.log > /tmp/slow-report.txt

# Key fields in the report:
# Rank  Query ID  Response time  Calls  R/Call  Apdex  V/M  Item
#    1  0xABC...  48.3261 42.5%  12345  0.0039  1.00   0.01 SELECT wp_postmeta...

# Filter to just the top 5 queries
pt-query-digest --limit=5 /var/log/mysql/slow.log

# Analyse a live running instance (requires PROCESS privilege)
pt-query-digest --processlist h=127.0.0.1,u=root

# The EXPLAIN for a typical WordPress slow query:
# "SELECT * FROM wp_postmeta WHERE meta_key = '_price' ORDER BY meta_value+0 LIMIT 20"

-- Fix the top offending query: missing composite index on wp_postmeta
EXPLAIN SELECT post_id, meta_value
FROM wp_postmeta
WHERE meta_key = '_price'
ORDER BY CAST(meta_value AS DECIMAL(10,2))
LIMIT 20;
-- type: ALL → full table scan — this is the problem

-- Add a composite index
ALTER TABLE wp_postmeta ADD INDEX idx_meta_key_value (meta_key, meta_value(20));

-- Re-run EXPLAIN to confirm it now uses the index
EXPLAIN SELECT post_id, meta_value
FROM wp_postmeta
WHERE meta_key = '_price'
ORDER BY CAST(meta_value AS DECIMAL(10,2))
LIMIT 20;
-- type: ref, key: idx_meta_key_value — index used

NOTE: Run pt-query-digest weekly in production and track the top-5 slow queries over time. A query that was fast last month but appears in the top-5 now usually signals that a table has grown past the point where its current index is effective.