The SQL with the most accumulated latency is not necessarily the SQL that caused the incident; the useful question is which statement changed during the incident window, and why.

Technology and product capabilities in this series are evaluated as of June 30, 2026.

Situation

The preceding EC2 triage article stopped after classifying a MySQL incident by layer. Rising mysqld CPU, more running threads, and no lock chain or EBS limit signal make SQL a credible branch—not yet the root cause.

MySQL 8.4 groups normalized statements by digest with counts, timing, row, sort, and temporary-table evidence. Histograms preserve latency distributions, the slow log retains eligible executions, and EXPLAIN ANALYZE adds actual iterator behavior.

Each source answers a different question. A defensible investigation joins them in time instead of asking an LLM to interpret a lifetime ranking. Aurora-specific behavior belongs to the next article.

The Problem

A high-ranking digest can mean three different things:

  • traffic amplification: execution count rose while cost per call remained stable;
  • runtime regression: calls remained comparable while mean or tail latency rose;
  • work amplification: each execution examined, sorted, materialized, or returned more data.

Lifetime totals blur those cases. Digest normalization replaces literal values, which helps group a query shape but can hide selectivity skew. An average hides a bad tail. A slow-log threshold omits statements below that threshold, even when a frequently executed medium-cost statement consumes the interval. A sampled SQL text can contain secrets or personal data. A plan generated later may not reproduce the incident’s data distribution, session settings, schema, or cache state.

The core question is: which interval delta, execution, and plan behavior prove that a statement caused the resource and latency change?

Symptoms

Enter this workflow only after layered triage makes SQL or workload behavior a credible candidate.

Incident evidencePlausible interpretationEvidence still required
Digest executions rise; mean latency is stableDemand amplificationCaller, release, tenant, or job attribution
Executions are stable; mean and tail latency riseRuntime regressionWait context and an incident-equivalent plan
Rows examined rise faster than rows returnedAccess-path or selectivity changePredicates, chosen index, estimates, and actual rows
One digest adds most interval latencyLeading workload contributorProof that it precedes or explains the system symptom
Many unrelated digests slow togetherShared resource or contention victimizationHost, wait, lock, and storage correlation

First Five Checks

1. Preserve digest identity and collection state

Snapshot performance_schema.events_statements_summary_by_digest at the baseline and incident boundaries. Key rows by both SCHEMA_NAME and DIGEST; the same normalized text in different default schemas is not necessarily the same object access.

Capture at least:

SELECT SCHEMA_NAME,
       DIGEST,
       DIGEST_TEXT,
       COUNT_STAR,
       SUM_TIMER_WAIT,
       AVG_TIMER_WAIT,
       SUM_LOCK_TIME,
       SUM_ROWS_EXAMINED,
       SUM_ROWS_SENT,
       SUM_CREATED_TMP_DISK_TABLES,
       SUM_SORT_MERGE_PASSES,
       SUM_NO_INDEX_USED,
       FIRST_SEEN,
       LAST_SEEN,
       QUANTILE_95,
       QUANTILE_99
FROM performance_schema.events_statements_summary_by_digest
WHERE DIGEST IS NOT NULL;

Record server identity, uptime, collection time, MySQL version, Performance Schema sizing, enabled consumers, and resets. If the table fills, unmatched statements accumulate under a NULL schema and digest; that is coverage loss. Restarts and truncation invalidate subtraction. Keep QUERY_SAMPLE_TEXT and raw literals in a restricted tier; export redacted text.

2. Rank interval change, not lifetime cost

Subtract the first snapshot’s counters from the second for each schema and digest. Calculate:

interval executions       = delta COUNT_STAR
interval total latency    = delta SUM_TIMER_WAIT
interval mean latency     = delta SUM_TIMER_WAIT divided by delta COUNT_STAR
rows examined per call    = delta SUM_ROWS_EXAMINED divided by delta COUNT_STAR
rows sent per call        = delta SUM_ROWS_SENT divided by delta COUNT_STAR

Reject deltas crossing a restart, reset, or coverage loss. Rank both added total latency and change from baseline: the first finds contribution; the second finds regression.

MySQL’s sys.statement_performance_analyzer() can create, save, compare, and clean up snapshots with a delta action. A collector can implement the same interval contract externally without truncating summaries.

QUANTILE_95 and QUANTILE_99 are high estimates from cumulative histograms; do not subtract them. Subtract bucket counts in events_statements_histogram_by_digest, then recompute interval quantiles.

3. Recover representative executions safely

The digest identifies a shape, not the literal values that produced it. Correlate approved slow-log records, restricted statement history, application traces, or sanitized replay samples.

MySQL writes to the slow log after a statement finishes and initial table locks are acquired. Eligibility depends on long_query_time and min_examined_row_limit; optional no-index logging has separate controls. This is threshold-selected evidence, not a complete trace. Lowering thresholds adds I/O, exposure, and volume.

Preserve default schema, relevant optimizer variables, bound-value class, schema, index visibility, and statistics state. Redact literals before model access while retaining a controlled DBA mapping.

4. Compare estimates with execution behavior

Start with non-executing EXPLAIN FORMAT=JSON or EXPLAIN FORMAT=TREE in an incident-equivalent environment. Preserve access paths, join order, keys, estimated rows, filters, materialization, sorts, and hash joins.

EXPLAIN ANALYZE runs the statement and returns estimates plus actual iterator timing, rows, and loops in tree format. It can repeat expensive work. MySQL 8.4 permits it for SELECT, TABLE, and multi-table UPDATE and DELETE statements.

Never paste model-generated SQL after EXPLAIN ANALYZE. Use reviewed read-only SQL on a representative clone or replica, an execution budget, and a kill path. Do not execute a modifying form for evidence.

5. Prove the regression mechanism

Compare baseline and incident executions, mean and tail latency, rows examined and sent per call, disk temporary tables, sort passes, selected keys, and plans. Join this with bounded wait and host evidence to distinguish a cause from a victim.

A changed path plus a large estimate-to-actual gap supports a cardinality mechanism, but does not prove stale statistics. Correlated columns, parameter skew, expressions, and missing histograms can also mislead estimates. An unchanged plan can become expensive because calls, input range, result size, or concurrency changed.

Decision Tree

flowchart TD
    A[SQL candidate from layered triage] --> B{Reliable digest delta exists}
    B -->|no| C[repair collection and preserve individual executions]
    B -->|yes| D{Execution count increased}
    D -->|yes| E{Cost per call stayed stable}
    E -->|yes| F[traffic amplification — attribute the caller]
    E -->|no| G[traffic and runtime changed — investigate both]
    D -->|no| H{Mean or tail latency increased}
    H -->|no| I[SQL regression not supported — revisit shared layers]
    H -->|yes| J{Rows examined or spill work increased}
    J -->|yes| K[work amplification — compare plan and selectivity]
    J -->|no| L{Lock or shared wait evidence increased}
    L -->|yes| M[statement may be a victim — prove the blocker or resource]
    L -->|no| N[compare iterator timing and execution context]

The tree separates attribution from remediation. Neither classification authorizes a change.

Correlate Evidence with the LLM

Give the model calculated facts and immutable references: digest and window IDs, interval executions and latency, rows examined and sent per call, baseline and incident plan artifacts, host and wait references, and a flag showing that raw SQL is unavailable. Require observations, ranked hypotheses, evidence for and against, missing evidence, and one bounded diagnostic. Confidence is not a measured probability without calibration.

A useful answer distinguishes call-volume growth from more work per call. A scan alone does not justify an index.

In Practice

MySQL’s statement-summary documentation defines digest aggregation by schema and normalized statement, cumulative timing and work counters, first and last observation times, latency quantiles, and sampling fields. It also documents the finite digest table. The derived operational rule is to preserve coverage and compute window deltas before ranking SQL.

The statement_performance_analyzer() documentation provides an explicit snapshot, save, and delta workflow over digest summaries. This supports the article’s central pattern: interval comparison is a database-native diagnostic method, not an LLM inference.

MySQL documents that EXPLAIN ANALYZE runs the statement and reports actual iterator timing, rows, and loops beside optimizer estimates. The derived safety rule is that actual-plan collection needs an execution boundary, approval policy, timeout, and representative target.

The slow query log documentation defines threshold and row-examination filters. Its absence cannot disprove a high-frequency workload contribution.

Together, digest deltas identify changed workload, representative executions restore hidden context, and plan evidence explains the mechanism. The LLM correlates those artifacts; it does not create them.

Remediation Options

Choose a change only after proving the mechanism and expected metric.

Proven mechanismCandidate responseRequired proof before production
Call volume increasedFix caller loop, cache, batch, or apply admission controlCaller attribution and capacity or correctness impact
Poor access pathRewrite predicate or join, or add a deliberately designed indexRepresentative plan, read benefit, write and storage cost, DDL impact
Estimate errorRefresh key statistics or create a targeted histogramEstimate gap, affected plans, sampling behavior, and regression test
Sort or temporary-table workReduce selected data, rewrite grouping, or add suitable access orderOperator-level evidence and memory or disk consequence
Oversized resultPaginate, project fewer columns, or change API contractConsumer behavior and correctness constraints
Parameter skewSplit statement shapes or use a reviewed optimizer control temporarilyReproduction across value classes and version-specific behavior
Statement is a victimFix the blocker or shared resourceTemporal lock, wait, host, or storage proof

An invisible index tests removing an existing secondary index from optimizer consideration with fast restoration. Building a new invisible index still consumes resources, storage, and write maintenance.

Rollback Plan

Define rollback per change type before execution:

  1. Application SQL: retain the previous release artifact and route; restore it if correctness, latency, or error criteria fail.
  2. New index: capture build impact and dependent plans. If the index regresses other reads, make it invisible before deciding whether to drop it; dropping still requires dependency and write-path review.
  3. Existing index removal: test invisibility first. Restore visibility if workload or errors regress; do not begin with DROP INDEX.
  4. Statistics or histograms: save the prior histogram JSON and affected plans. ANALYZE TABLE can influence other statements, so validate a workload set—not only the target digest. Restore a user-defined prior histogram where applicable or drop the new histogram under an approved plan.
  5. Optimizer hint or control: scope it narrowly, give it an expiration condition, and remove it after the durable fix passes.
  6. Traffic containment: record the original limit and restore gradually while watching the same digest and user-impact metrics.

Reduce collection if slow-log volume, instrumentation overhead, replica lag, duration, or exposure exceeds budget.

Where It Breaks

Failure modeFalse conclusionControl
Lifetime digest totals ranked as incident dataA historically expensive statement caused the eventSnapshot and subtract comparable windows
Digest table overflow or reset ignoredMissing shapes appear absent or counters regressRecord null digest share, uptime, sizing, and reset provenance
Normalization hides literal skewOne plan represents every executionPreserve redacted value classes and representative samples
Averages used without histogramsTail regression disappearsSnapshot histogram buckets and calculate interval tails
Slow-log absence treated as proofNo SQL was slowRecord thresholds and correlate digest deltas
Later EXPLAIN treated as incident planCurrent statistics explain past behaviorPreserve plan, schema, variables, and statistics in time
EXPLAIN ANALYZE run casuallyDiagnosis repeats expensive work or modifies dataUse reviewed read-only SQL, a representative target, and an execution budget
Scan automatically triggers an indexWrite cost and selectivity are ignoredProve access benefit and total workload impact
Many statements slow togetherEvery query independently regressedRevisit waits, locks, host, and storage evidence
Raw SQL sent to the modelLiterals leak sensitive dataSeparate restricted raw evidence from redacted model input

What the LLM Cannot Do

Explicit operational boundaries govern the LLM:

  • No Raw Customer Data: The LLM must not receive raw rows, bind values, or unnormalized statement text from the slow query log, events_statements_history, or SHOW PROCESSLIST. Send digest text, digest hashes, timings, and row-examined counts — never literals.
  • No Autonomous Production Execution: The LLM cannot run ALTER TABLE, SET GLOBAL, KILL, index DDL, replication commands, or instance modifications. It proposes; a human authorizes, applies, validates, and holds the rollback condition.
  • No Causal Conclusion Without Contradicting Evidence: Every hypothesis must cite both the telemetry that supports it and the telemetry that would falsify it. A ranked hypothesis with no disconfirming test attached is an assertion, not a diagnosis.
  • No Silent Gap-Filling: Where the evidence pack lacks a required signal, the LLM must name it explicitly under missing evidence rather than inferring a plausible value. “Not collected” and “collected and normal” are different findings and must never be merged.
  • No Change Without a Human Gate: Production changes require human authorization, a stated validation signal, and a defined rollback condition agreed before the change is applied.

What to Do Next

  • Problem: MySQL’s “top SQL” views combine lifetime cost, demand, per-call work, and tail behavior, while normalized digests and threshold logs omit context needed for causal proof.
  • Solution: Compare schema-and-digest snapshots by incident window, classify traffic versus runtime versus work amplification, recover representative executions safely, and join plan behavior to host, wait, and release evidence.
  • Proof: A second DBA should reproduce the leading digest and mechanism, identify contradictions, and predict the validation metric.
  • Action: Add digest snapshots, histogram buckets, collection-state metadata, restricted sample references, plan artifacts, and deterministic delta calculations to the MySQL evidence pack before enabling LLM correlation.

The next article applies the same evidence discipline to Aurora MySQL, where database load, Aurora-specific waits, distributed storage, readers, replicas, and failover change the diagnostic boundary.

Sources