When a MySQL server is overloaded, the first instinct is to look for the slowest query. That usually finds a report that takes eight seconds and runs twice a day. The query actually hurting the server is more often one that takes 4 ms and runs 3,000 times per second. What matters is total time: duration multiplied by frequency.

This guide is the sequence I use to find the queries that matter on MySQL 8, from "what is running right now" to "what used the most time over the last day". It needs no extra tools for the first steps, and Percona Toolkit for the last.

Step 1: what is running right now

SELECT id, user, host, db, command, time, state, LEFT(info, 120) AS query
FROM information_schema.processlist
WHERE command <> 'Sleep'
ORDER BY time DESC
LIMIT 20;

Or the sys schema's richer view, which includes statement latency, lock time and the full query:

SELECT conn_id, user, db, command, state, time, current_statement, statement_latency, lock_latency
FROM sys.processlist
WHERE command <> 'Sleep'
ORDER BY time DESC
LIMIT 20;

What to look for:

  • Many threads in the same state, for example "Sending data", "Waiting for table metadata lock" or "Waiting for row lock". The state often points at the cause faster than the query text.
  • The same query text repeated across dozens of connections.
  • Long-running transactions holding locks: check information_schema.innodb_trx for old trx_started values.
SELECT trx_id, trx_started, trx_mysql_thread_id, trx_rows_locked, LEFT(trx_query, 100)
FROM information_schema.innodb_trx
ORDER BY trx_started
LIMIT 10;

The processlist is a snapshot. Fast, frequent queries rarely show up in it, because each one is gone in milliseconds. That is why the next step matters most.

Step 2: statement digests in performance_schema

MySQL 8 groups executed statements by digest: the normalised query text, with literals replaced by ?. For each digest it keeps counts and timings since the last reset. This is the most useful view for finding load:

SELECT
  schema_name,
  LEFT(digest_text, 100)                         AS query,
  count_star                                     AS calls,
  ROUND(sum_timer_wait / 1e12, 1)                AS total_s,
  ROUND(avg_timer_wait / 1e9, 2)                 AS avg_ms,
  sum_rows_examined                              AS rows_examined,
  sum_rows_sent                                  AS rows_sent,
  sum_no_index_used                              AS no_index_used
FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC
LIMIT 10;

Timers are in picoseconds, hence the division. A typical result on an overloaded server:

| query                                                   | calls    | total_s | avg_ms | rows_examined | no_index_used |
| SELECT * FROM `cart_items` WHERE `session_id` = ?       |  4120331 | 18922.4 |   4.59 | 98953069596   |       4120331 |
| SELECT `id` , `price` FROM `products` WHERE `id` IN (...)| 2210981  |  3104.7 |   1.40 | 2210981       |             0 |
| UPDATE `sessions` SET `last_seen` = ? WHERE `id` = ?    | 12991002 |  1988.1 |   0.15 | 12991002      |             0 |

The first row is the culprit: under 5 ms on average, but 4.1 million calls since the last reset, examining about 24,000 rows per call and returning one or two, with no index used. That is a missing index on cart_items.session_id. It would never appear in a slow query log with a one-second threshold.

Useful columns

Column Tells you
sum_timer_wait Total time: sort by this first
count_star Frequency; very high counts may mean an N+1 pattern in the application
sum_rows_examined / sum_rows_sent A large ratio means scanning: missing or wrong index
sum_no_index_used, sum_no_good_index_used Full scans
sum_created_tmp_disk_tables Temporary tables spilling to disk
sum_sort_merge_passes Sorts too big for memory
first_seen, last_seen Whether a query is new, for example from a recent deploy

Reset for a clean window

The summary accumulates since server start or the last reset. To measure a specific period:

CALL sys.ps_truncate_all_tables(FALSE);   -- or TRUNCATE the summary table
-- wait 15 minutes under normal load, then run the digest query again

Step 3: shortcuts in the sys schema

The sys schema wraps performance_schema in readable views:

SELECT * FROM sys.statement_analysis LIMIT 10;                      -- top digests by total latency
SELECT * FROM sys.statements_with_full_table_scans LIMIT 10;        -- scans
SELECT * FROM sys.statements_with_temp_tables LIMIT 10;
SELECT * FROM sys.schema_unused_indexes;                             -- indexes never used since start
SELECT * FROM sys.schema_table_statistics ORDER BY total_latency DESC LIMIT 10;   -- hottest tables

schema_table_statistics answers a different question well: which tables are busiest, which often leads straight to the right part of the application.

Step 4: history with the slow query log and pt-query-digest

performance_schema keeps aggregates in memory. For history and for exact query samples, the slow query log is still valuable. To catch fast, frequent queries, use a low threshold for a limited time:

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.05;          -- 50 ms; use 0 to log everything, briefly
SET GLOBAL log_slow_extra = ON;             -- MySQL 8.0.14+: rows examined, bytes sent and more

Logging every query on a busy server produces large files quickly; enable it for an hour, not a week. Then summarise with pt-query-digest:

pt-query-digest --limit 10 /var/log/mysql/mysql-slow.log > digest.txt

# Profile
# Rank Query ID           Response time    Calls   R/Call V/M   Item
# ==== ================== ================ ======= ====== ===== ==========
#    1 0x8E1D...          5512.3012 61.2%  1.2M    0.0045  0.01 SELECT cart_items
#    2 0x21B9...          1014.8803 11.3%  98.1k   0.0103  0.02 SELECT products

Each item then shows the query fingerprint, distribution of latencies, example query with real values, and EXPLAIN-ready text. Real values matter: an index may work for most values and not for a few very common ones.

Step 5: confirm with EXPLAIN

With the candidate query and a real example:

EXPLAIN ANALYZE
SELECT * FROM cart_items WHERE session_id = 'c1f0e7a2-...';

-> Filter: (cart_items.session_id = 'c1f0e7a2-...')  (cost=2421 rows=2402) (actual time=0.03..4.71 rows=2 loops=1)
    -> Table scan on cart_items  (cost=2421 rows=24018) (actual time=0.02..3.95 rows=24018 loops=1)

A table scan of 24,000 rows to return two, millions of times a day. The fix:

ALTER TABLE cart_items ADD INDEX idx_cart_items_session (session_id), ALGORITHM=INPLACE, LOCK=NONE;

After the index, the digest's average dropped from 4.6 ms to 0.03 ms, and total CPU on the server fell by about half.

Common mistakes

Mistake Why it hurts Do this instead
Sorting by slowest single query Misses frequent fast queries Sort digests by total time
Only using the processlist Fast queries are invisible in snapshots performance_schema digests
Slow log threshold of 1-2 seconds Logs nothing useful on busy OLTP systems Temporarily lower it, then summarise
Leaving full logging on Disk and I/O load Time-box low-threshold logging
Indexing from digest text alone Literal values change plans EXPLAIN ANALYZE with real examples

Key takeaways

  • The queries that overload MySQL are usually fast and frequent; rank by total time.
  • processlist and innodb_trx show what is happening right now, including locks.
  • events_statements_summary_by_digest and the sys views show load over time.
  • A temporary low slow-log threshold plus pt-query-digest gives history and real examples.
  • Confirm with EXPLAIN ANALYZE before adding indexes.

References