History list health at a glance

For MySQL 8.4 InnoDB, the chapter 4 schedule retains an old REPEATABLE READ view while updates commit. Here, 150 updates commit, the history-list metric is at least 150, and R still reads its original value. The detector identifies R as the oldest tagged transaction in this controlled run.

R> SELECT @@session.transaction_isolation AS isolation_level;
 isolation_level 
-----------------
 REPEATABLE-READ 
(1 row)

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 OK

Check 1: the history-list metric is at least 150 in this schedule.

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: find the oldest tagged transaction, a candidate for investigation.

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
    -- transaction age is not read-view age; production attribution needs more evidence
 session_name | older_than_1s 
--------------+---------------
 R            |             1 
(1 row)

A> SELECT v FROM counters;
  v  
-----
 150 
(1 row)

R> SELECT v FROM counters;
 v 
---
 0 
(1 row)

R> COMMIT;
Query OK

R releases its read view; background purge and other readers determine later cleanup, which is not asserted here.

Verified against MySQL 8.4.11 · Run it yourself · Scenario source

Turning it into monitoring

trx_rseg_history_len is a history-list measure, not retained bytes or disk usage. Graph it with transaction age and workload. Sustained growth is a reason to investigate, not proof of one forgotten reader. Write volume and purge capacity also matter. Choose thresholds from your workload and recovery objectives; this run establishes none.

The oldest trx_started row is a candidate, not the oldest read view by definition: the query reports transaction start time, not snapshot creation time. The demonstration knows when R opened its view because it controls the schedule. A production query alone does not supply that knowledge. Confirm the candidate's isolation, reads, and concurrent work before ending it.

The MySQL 8.4 purge manual states: "Purge runs on a periodic schedule." Ending a retaining reader permits cleanup of history it required; other readers and background work determine what follows. The scenario does not assert drainage, tablespace shrinkage, latency, or warning time. Investigate purge configuration as well as retaining readers.

Further reading

• MySQL docs: Purge Configuration
• The PostgreSQL counterpart: bloat & vacuum health