A Working MySQL Slow Query Log Digest Workflow

Every MySQL team I have worked with has the slow query log enabled. Almost none of them have a slow query log workflow. The log sits on disk, grows until logrotate deletes it, and gets opened exactly once per quarter, during an incident, by someone scrolling through raw entries hoping a smoking gun jumps out. That is not monitoring. That is archaeology.

The difference between the two is aggregation and baselines. A single slow log entry tells you one statement was slow once. A digest tells you which query family consumes the most cumulative time, whether its shape changed after a deploy, and whether the fix you shipped actually moved the number. MySQL 8.0 and 8.4 give you everything you need for this: a configurable slow log with extended per-statement metrics, and performance_schema statement digests as an always-on complement.

This is the pipeline I set up on production MySQL 8.x, in the order I set it up.

Pick a long_query_time strategy, not a number

The default long_query_time is 10 seconds, which on most OLTP systems means the log captures almost nothing. The opposite extreme, setting it to 0 and logging every statement, produces complete data but real overhead: every statement takes a write to the log file, and on a busy server that is both I/O and a mutex hot spot around the log.

The strategy that works is a ratchet. Start at 1 second and confirm the volume is manageable. Drop to 0.5, then 0.1, watching log growth and file system pressure at each step. Most OLTP systems can live at 0.1 to 0.5 seconds permanently, which catches the queries that actually hurt users while ignoring the well-behaved bulk. When you need full capture, for example a one-hour window around a planned deploy, drop to 0 temporarily and restore the ratcheted value afterward. long_query_time takes microsecond resolution when logging to a file, and it is a dynamic variable, so this costs you nothing but a SET GLOBAL.

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.5;
SET GLOBAL log_slow_extra = ON;
SET GLOBAL log_output = 'FILE';

-- Verify what new connections will get
SELECT @@GLOBAL.slow_query_log,
       @@GLOBAL.long_query_time,
       @@GLOBAL.log_slow_extra,
       @@GLOBAL.slow_query_log_file;

Two caveats worth writing into the runbook. First, long_query_time is evaluated per session, and existing connections keep the value they connected with, so a global change does not affect long-lived pool connections until they reconnect. Second, be careful with log_queries_not_using_indexes. It sounds like a free audit, but on schemas with small lookup tables it floods the log with harmless full scans. If you enable it, pair it with log_throttle_queries_not_using_indexes so one chatty query cannot drown the file.

Turn on log_slow_extra and stop guessing

Since MySQL 8.0.14, log_slow_extra adds a set of fields to each file-based slow log entry that used to require a Percona build: Read_first, Read_key, Read_next, Read_rnd_next and friends from the handler counters, Sort_merge_passes, Sort_rows, Created_tmp_disk_tables, Created_tmp_tables, Bytes_sent, Errno, Killed, and precise start and end timestamps. In MySQL 8.4 it is finally on by default; on 8.0 you have to ask for it.

These fields change what the log can answer. Rows_examined versus Rows_sent tells you whether a query is doing wasted work: a statement that examines two million rows to return twenty is an indexing problem regardless of how fast the box is. Created_tmp_disk_tables tells you a GROUP BY or DISTINCT spilled to disk. Sort_merge_passes above zero means the sort buffer was too small for a filesort that maybe should not exist at all. Without log_slow_extra you know a query was slow; with it you usually know why before you ever run EXPLAIN.

Digest the log with pt-query-digest

Raw slow log entries are per-execution. What you want is per-query-family: all executions of the same statement shape, with literals stripped, aggregated together. pt-query-digest from Percona Toolkit remains the standard tool for this and it works fine against stock MySQL 8.x slow logs, including the log_slow_extra fields.

pt-query-digest   --since '2026-07-07 09:00:00'   --until '2026-07-07 12:00:00'   --limit 20   /var/lib/mysql/db1-slow.log > digest-post-deploy.txt

The report ranks query classes by total cumulative time, which is the right default: a 40 ms query running 500 times per second matters more than a 30 second report that runs nightly. For each class you get call counts, latency distribution, and the rows examined to rows sent ratio. Pay particular attention to the V/M column, the variance-to-mean ratio. A query with low mean latency but high V/M is bimodal: fast most of the time, terrible sometimes. Those are the queries behind mystery p99 spikes, and a mean-based dashboard will never show them.

Run the digest on a schedule, not just during incidents. A daily digest diffed against yesterday is a cheap regression detector, and the --since and --until flags make it easy to scope a digest to exactly the window around a deploy.

Use performance_schema digests as the always-on layer

The slow log only sees statements that crossed the threshold. performance_schema sees everything. The events_statements_summary_by_digest table aggregates every statement by normalized digest since the last server restart or truncation, with counts, cumulative and average timer waits, rows examined, temporary table counts, and, in 8.0, latency quantiles.

SELECT schema_name,
       LEFT(digest_text, 80) AS query_family,
       count_star AS calls,
       ROUND(sum_timer_wait / 1e12, 1) AS total_s,
       ROUND(avg_timer_wait / 1e9, 2) AS avg_ms,
       ROUND(quantile_95 / 1e9, 2) AS p95_ms,
       sum_rows_examined,
       sum_rows_sent,
       sum_created_tmp_disk_tables
FROM performance_schema.events_statements_summary_by_digest
WHERE schema_name IS NOT NULL
ORDER BY sum_timer_wait DESC
LIMIT 20;

Timer columns are in picoseconds, hence the divisions. Two operational details matter here. The table is bounded by performance_schema_digests_size; once it fills, new digests collapse into a catch-all row with a NULL digest, and if that row is accumulating meaningful time you need a bigger table. And digest text is truncated at max_digest_length bytes, default 1024, which can merge distinct long queries into one family. Both are worth checking once, then leaving alone.

The honest comparison is that the slow log gives you per-execution forensic detail with timestamps and connection context, while digests give you complete but pre-aggregated coverage. You want both, and they answer different questions during an incident. I wrote up how this pair compares to the PostgreSQL approach in MySQL slow query log vs pg_stat_statements, and the short version is that MySQL makes you assemble the workflow yourself.

Keep before and after deploy baselines

The single highest-value habit in this whole pipeline: snapshot the digest table before and after every deploy. events_statements_summary_by_digest is cumulative since restart, so point-in-time reads are hard to compare. A snapshot table fixes that.

CREATE TABLE IF NOT EXISTS ops.digest_snapshots AS
SELECT CAST('init' AS CHAR(64)) AS label, NOW() AS captured_at, d.*
FROM performance_schema.events_statements_summary_by_digest d
WHERE 1 = 0;

INSERT INTO ops.digest_snapshots
SELECT 'pre-deploy-2026-07-08', NOW(), d.*
FROM performance_schema.events_statements_summary_by_digest d;

After the deploy has soaked for an hour, take a second snapshot and diff the two: for each digest, subtract counts and cumulative time, then rank by the change in total time. A query family whose per-call average doubled, or whose call count grew 10x because someone removed application-side caching, shows up immediately, with the deploy boundary as the obvious cause. Without the baseline, you are staring at a top-20 list trying to remember whether that query was always there.

When a suspect surfaces, the next step is a plan-level look, and that deserves care in MySQL. Reading EXPLAIN in MySQL versus EXPLAIN ANALYZE in PostgreSQL is a different skill, and confusing the two wastes incident time.

Operational hygiene that keeps the pipeline alive

A few small things keep this workflow from rotting. Rotate the slow log with FLUSH SLOW LOGS after moving the file, not by truncating in place, so you never lose entries mid-write. Keep digests from at least the last four deploys so you can distinguish a new regression from a slow drift. Document the ratcheted long_query_time value and the temporary full-capture procedure in the runbook, because the person handling the next incident will not be the person who set this up. And resist the temptation to alert directly on slow log volume; alert on the digest-level changes, which are far less noisy.

Where MonPG fits

MonPG today is a PostgreSQL monitoring platform: query history, plan context, locks, vacuum, and replication evidence for Postgres fleets. We are currently building MySQL monitoring (coming soon), and this digest workflow is exactly the kind of manual pipeline it is designed to replace, with continuous digest history, deploy-boundary baselines, and regression detection handled for you instead of by cron jobs and snapshot tables. MonPG does not monitor MySQL yet, and I would rather say that plainly than imply otherwise. If your team also runs PostgreSQL, the same evidence-first workflow already exists there, and you can start there today.