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.11 · 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.