Find the most expensive queries (performance_schema digest)

Command

SELECT SCHEMA_NAME AS db,
       LEFT(DIGEST_TEXT, 80) AS query,
       COUNT_STAR AS calls,
       ROUND(SUM_TIMER_WAIT / 1e12, 2) AS total_sec,
       ROUND(AVG_TIMER_WAIT / 1e9, 2) AS avg_ms,
       ROUND(QUANTILE_95 / 1e9, 2) AS p95_ms,
       ROUND(100 * SUM_TIMER_WAIT / SUM(SUM_TIMER_WAIT) OVER (), 1) AS pct_total,
       ROUND(SUM_ROWS_EXAMINED / NULLIF(COUNT_STAR, 0)) AS rows_exam_per_call,
       ROUND(SUM_ROWS_SENT / NULLIF(COUNT_STAR, 0)) AS rows_sent_per_call,
       SUM_NO_INDEX_USED AS no_index,
       SUM_CREATED_TMP_DISK_TABLES AS tmp_disk,
       LAST_SEEN
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME IS NOT NULL
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 20;

-- Server-wide top wait events
SELECT events, total, total_latency, avg_latency FROM sys.waits_global_by_latency LIMIT 10;

What it does

Ranks normalized queries by total time with call count, average and p95 latency, share of total, rows examined / sent per call, executions without an index and temp tables spilled to disk. The second query shows which waits (IO, locks, redo) the time is spent on.

When to use it

First stop when CPU / IO load rises or users report 'the database is slow'; works even when the slow log is off.

Caution

Values are cumulative since the last restart or TRUNCATE TABLE performance_schema.events_statements_summary_by_digest; take two snapshots and diff them to see current activity. The table is capped at performance_schema_digests_size rows; once full, new queries are aggregated into a single row with a NULL DIGEST. DIGEST_TEXT is cut at performance_schema_max_digest_length (1024) bytes. Times are in picoseconds. A high rows_exam_per_call / rows_sent_per_call ratio is the clearest sign of a missing index.