Capture a short full slow log and analyze it with pt-query-digest

Command

-- Temporary full capture (e.g. 10 minutes); only new connections are affected
SET GLOBAL slow_query_log_file = '/var/lib/mysql/slow.log';
SET GLOBAL log_slow_extra = ON;
SET GLOBAL long_query_time = 0;
SET GLOBAL slow_query_log = ON;
-- ... then return to the normal threshold
SET GLOBAL long_query_time = 1;

# Shell: top 10 queries by total time
pt-query-digest --limit=10 --order-by=Query_time:sum --noversion-check /var/lib/mysql/slow.log > /tmp/digest.txt
pt-query-digest --since='2026-10-08 09:00:00' --until='2026-10-08 10:00:00' --filter '$event->{db} && $event->{db} eq "shop"' --report-format=profile --noversion-check /var/lib/mysql/slow.log

What it does

Captures every query for a short window with long_query_time=0; pt-query-digest groups them by fingerprint and produces a ranked report with total / average / p95 time, rows examined and a sample query.

When to use it

When performance_schema digests are not enough: analysis with full statement text and real parameters, before / after comparison around a deploy, offline review on another host.

Caution

long_query_time is copied from the global value when a session starts; existing connections in a pool do not see the new threshold until they reconnect. long_query_time=0 writes thousands of entries per second on a busy server; watch disk space and IO, keep the window short and always revert. Copy the file off production for analysis. The user running pt-query-digest needs read access to the file (mode 640, mysql:mysql).