History list health at a glance
Chapter 4 proved the mechanism — one idle reader pins the undo of every later commit. This is the production version: the two queries that turn "the undo tablespace keeps growing" from a mystery into a name:
R opens a read view and goes quiet; the write workload keeps humming.
R> BEGIN;
Query OK
R> SELECT v FROM counters;
v
---
0
(1 row)
A> CALL bump(150); -- 150 committed single-row transactions
Query OKCheck 1 — is history piling up? (Baseline is 'usually less than a few thousand'.)
M> SELECT count >= 150 AS history_backlog
FROM information_schema.INNODB_METRICS WHERE name = 'trx_rseg_history_len';
history_backlog
-----------------
1
(1 row)Check 2 — who is the oldest read view that purge is waiting for?
M> SELECT CAST(sn.variable_value AS CHAR) AS session_name,
timestampdiff(SECOND, t.trx_started, now()) >= 1 AS older_than_1s
FROM information_schema.innodb_trx t
JOIN performance_schema.threads th ON th.processlist_id = t.trx_mysql_thread_id
JOIN performance_schema.user_variables_by_thread sn ON sn.thread_id = th.thread_id
WHERE sn.variable_name = 'session_name'
ORDER BY t.trx_started LIMIT 1
-- end this transaction and purge catches up on its own
session_name | older_than_1s
--------------+---------------
R | 1
(1 row)
R> COMMIT;
Query OKVerified against MySQL 8.4.10 · Run it yourself · Scenario source
Turning it into monitoring
The metric to graph is trx_rseg_history_len from information_schema.INNODB_METRICS, the queryable form of the History list length you'd otherwise read off SHOW ENGINE INNODB STATUS. The manual's baseline is "typically a low value, usually less than a few thousand", so what you alert on is sustained growth, never an absolute number. A write burst spikes the value and purge absorbs the spike on its own; a line that climbs and stays climbed is the signal you care about.
Once it does stay grown, the second query names the culprit. The oldest read view — the ORDER BY trx_started LIMIT 1 row — is purge's entire blocker, so ending that one transaction drains the backlog with no further action from you. What you shouldn't do is reach for purge tuning (innodb_purge_threads and friends) while the oldest transaction is hours old. Purge isn't slow here; it's forbidden, and no amount of tuning lifts a ban.
Unlike PostgreSQL there's no table-level bloat to inspect and no VACUUM scheduling to audit — undo lives centrally, so this one metric plus one attribution query is the whole checkup.
Undo growth stays invisible in query latency right up until it doesn't: version chains lengthen, the undo tablespace fills, and by then you're already in the incident. Catch it on the graph instead, where a slow climb in trx_rseg_history_len buys you days of warning and hands you a single transaction to go end.