After traffic grew, a MySQL 8.4 server's p99 latency doubled; how do you use Performance Schema statement digests and the sys schema to find the statements responsible?
answer
- normalized statements, aggregated per digest
- on by default, cumulative since startup
- total load versus tail latency
- picoseconds, so format them
basics
~10 sQuery performance_schema.events_statements_summary_by_digest, which Performance Schema fills by default, ranking digests by SUM_TIMER_WAIT for total load and QUANTILE_99 for tail latency, or read sys.statement_analysis. Compare two snapshots to see which statements changed.
solid answer
~40 sPerformance Schema is on by default and the `statements_digest` consumer aggregates every completed statement into `events_statements_summary_by_digest`, one row per schema and normalized statement. Each row has `COUNT_STAR`, `SUM_TIMER_WAIT`, `AVG_TIMER_WAIT`, `MAX_TIMER_WAIT`, per-digest `QUANTILE_95`, `QUANTILE_99` and `QUANTILE_999`, plus counters such as `SUM_ROWS_EXAMINED`, `SUM_NO_INDEX_USED`, `SUM_CREATED_TMP_DISK_TABLES` and `SUM_LOCK_TIME`. Times are in picoseconds, so I wrap them in `FORMAT_PICO_TIME()` or use `sys.statement_analysis`, which presents the same data sorted by total latency. Because the table is cumulative, I take a baseline, then compare after the slowdown or after `TRUNCATE TABLE`. I separate digests whose count grew from digests whose per-call latency or lock time grew, and `FIRST_SEEN` exposes new statements. Then I run `EXPLAIN ANALYZE` on `QUERY_SAMPLE_TEXT`.
code
sql · 10 linesSELECT SCHEMA_NAME, LEFT(DIGEST_TEXT, 80) AS stmt, COUNT_STAR,
FORMAT_PICO_TIME(SUM_TIMER_WAIT) AS total_latency,
FORMAT_PICO_TIME(QUANTILE_99) AS p99,
FORMAT_PICO_TIME(SUM_LOCK_TIME) AS lock_time,
SUM_ROWS_EXAMINED, SUM_NO_INDEX_USED, FIRST_SEEN
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;
SELECT * FROM sys.statement_analysis LIMIT 10;go deeper
Recall that Performance Schema is on by default, that a digest groups statements that differ only in literals, and that its times are in picoseconds.
Explain the main digest columns, how sys.statement_analysis presents them, and why the table must be read as a difference between two snapshots.
Show how you separate load growth (COUNT_STAR, SUM_TIMER_WAIT) from per-call regression (QUANTILE_99, lock time, rows examined) and spot an overflowing digest table before trusting it.
Decide what statement telemetry the fleet keeps by default, how digest snapshots are exported and kept, and who owns reading them after each release.
## Where the data comes from **Performance Schema** is MySQL's built-in instrumentation engine. The `performance_schema` variable defaults to `ON` in MySQL 8.4, and the statement instruments and the `statements_digest` consumer are enabled out of the box. So on a typical server the evidence for "what got slower" already exists before anyone starts looking. A **statement digest** is a hash of the **normalized** statement text: literals are replaced and whitespace is collapsed, so `SELECT * FROM t WHERE id = 7` and `... id = 912` share one digest. When a statement completes, its statistics are added to its row in `performance_schema.events_statements_summary_by_digest`, keyed on `(SCHEMA_NAME, DIGEST)`. ## The columns that matter | Column | Use | |---|---| | `DIGEST_TEXT` | The normalized statement | | `COUNT_STAR` | Executions | | `SUM_TIMER_WAIT`, `AVG_TIMER_WAIT`, `MAX_TIMER_WAIT` | Total, mean and worst latency | | `QUANTILE_95`, `QUANTILE_99`, `QUANTILE_999` | This digest's latency percentiles, high estimates computed from histogram buckets | | `SUM_LOCK_TIME` | Time spent acquiring locks | | `SUM_ROWS_EXAMINED`, `SUM_ROWS_SENT` | Read amplification | | `SUM_NO_INDEX_USED`, `SUM_CREATED_TMP_DISK_TABLES`, `SUM_SORT_MERGE_PASSES` | Scans, on-disk temporary tables, sort spills | | `FIRST_SEEN`, `LAST_SEEN` | When the digest appeared and last ran | | `QUERY_SAMPLE_TEXT` | One real statement with literals, ready for `EXPLAIN` | All timer columns are in **picoseconds**. `FORMAT_PICO_TIME()` turns them into readable values. ## The sys schema views The **sys schema** ships with the server and wraps Performance Schema in views: - `sys.statement_analysis` lists normalized statements sorted by descending **total** latency, with `exec_count`, `avg_latency`, `lock_latency`, `rows_examined` and `full_scan` columns. - `sys.statements_with_full_table_scans`, `sys.statements_with_temp_tables` and `sys.statements_with_sorting` filter for scans, temporary tables and sorts. - `sys.statements_with_runtimes_in_95th_percentile` lists the statements whose **average** latency falls in the slowest 5 percent across all digests. It is not a per-statement p95. Use the `QUANTILE_95` column for that. Every view has an `x$` twin with raw, unformatted numbers for scripts and sorting. ## Working the p99 scenario A doubled p99 after a traffic increase has a few typical shapes, and the digest table can tell them apart: 1. **Take a baseline.** The table is cumulative since startup or the last `TRUNCATE TABLE`. Copy it to a scratch table (or truncate it, which also clears `events_statements_histogram_by_digest`), wait through a representative window, then read it again. Without a window, numbers from a quiet week dilute today's. 2. **Rank by `SUM_TIMER_WAIT`.** When traffic grows, a fast statement that now runs ten times as often can dominate server time while its average barely moves. 3. **Rank by `QUANTILE_99`.** These are the statements whose own tail got worse. Rising `SUM_LOCK_TIME` alongside them points to contention, which the lock diagnostics then explain. Rising `SUM_ROWS_EXAMINED` per call points to a plan or data-growth problem. 4. **Check `FIRST_SEEN`.** A digest first seen around the time of the regression is often a new code path or a changed ORM query. 5. **Hand the sample to the planner.** Run `EXPLAIN ANALYZE` on `QUERY_SAMPLE_TEXT` to see where the time goes. ## Digests or the slow query log? The two sources answer different questions, and an incident often needs both: | | Statement digests | Slow query log | |---|---|---| | Default state | Collecting from startup | Off until enabled | | Granularity | One aggregated row per normalized statement | One entry per execution over the threshold | | Literals | Only in `QUERY_SAMPLE_TEXT` | Every logged statement in full | | Fast statements | Counted like every other statement | Missed unless the threshold is very low | | Percentiles | Per-digest `QUANTILE_*` columns | Computed afterwards from the file | Digests show the whole workload, including the thousands of 3 ms statements that never cross `long_query_time` but add up when traffic grows. The slow log shows individual bad executions, with their real literals and timestamps. Start with the digests to find the statement, and use the slow log to see which parameter values make it slow. ## Limits to know - The table has a fixed size, `performance_schema_digests_size`, which is autosized and **not dynamic**. When it is full, statements with a new digest are added to a catch-all row whose `DIGEST` is `NULL`, and `Performance_schema_digest_lost` counts them. A large NULL row means the table has stopped telling you the truth, so truncate it or size it up at the next restart. - Digest computation stops after `max_digest_length` bytes of parsed tokens (default 1024). Two long statements that differ only after that point share a digest. - The digest table shows server time per statement. It does not show time a client spent waiting for a connection, which shows up in the application's pool metrics instead.
- Why rank MySQL digests by SUM_TIMER_WAIT rather than AVG_TIMER_WAIT after a traffic increase?Total time is what the server actually spent. A 2 ms statement whose `COUNT_STAR` grew tenfold can take more server time than a rare 2-second report, and its average barely moves. Ranking by total finds the statements that saturate the server; `QUANTILE_99` then shows which ones have a worse tail of their own.
- New statements stop appearing in events_statements_summary_by_digest. What happened?The table reached `performance_schema_digests_size`, so statistics for unseen digests now go into a catch-all row with `DIGEST` = NULL and `Performance_schema_digest_lost` increases. Truncate the table to make room, or raise the size at the next restart, because the variable is not dynamic.
The digest table is like an itemized phone bill grouped by number: it will not replay a call, but it shows at once which numbers you called most, for longest, and which ones are new this month.
- Performance Schema is off by default, so you must restart before you can diagnose.
- SUM_TIMER_WAIT and QUANTILE_99 are reported in milliseconds.
- statements_with_runtimes_in_95th_percentile shows each statement's own p95 latency.
- The digest table stores every executed statement with its literal values.
- Ranking by average latency is enough to find what traffic growth made expensive.