Managing InnoDB History List Length and Undo Log
The History List Length (HLL) reported in the TRANSACTIONS section of SHOW ENGINE INNODB STATUS counts the number of undo log record versions that InnoDB's purge thread has not yet processed. Each committed transaction that modified rows generates undo log entries that must be retained until no active read view (MVCC snapshot) can see those old versions. A growing HLL — typically anything above 10,000 — indicates the purge thread is falling behind write throughput, causing undo tablespace bloat, increased MVCC read overhead, and eventually query slowdowns as old row versions accumulate in the undo log chain. This guide explains how to read HLL correctly, identify what is blocking purge, and tune purge parameters to keep HLL under control.
What History List Length Means for InnoDB
MVCC Read Views and the Purge Thread
InnoDB's MVCC (Multi-Version Concurrency Control) implementation keeps old row versions in the undo log so that long-running read transactions can see a consistent snapshot of the data as it existed at their transaction start time. Each active MVCC read view holds a "low watermark" transaction ID below which undo records can be purged. The purge thread continuously removes undo records whose corresponding transaction IDs fall below the oldest active read view. When a long-running SELECT, a REPEATABLE READ transaction, or an open transaction started with START TRANSACTION WITH CONSISTENT SNAPSHOT exists for many minutes or hours, it pins the read view watermark and prevents purge from advancing, causing HLL to grow linearly with write throughput.
Safe HLL Thresholds and Warning Signs
A History List Length below 1,000 is healthy for most OLTP workloads. Values between 1,000 and 10,000 indicate mild purge lag but are usually harmless unless the trend is increasing. Above 10,000, query latency for tables with many row versions begins to increase because InnoDB must traverse longer undo chains to resolve the correct visible row version. Above 100,000, expect measurable query slowdowns and significant undo tablespace disk usage growth. Above 1,000,000, the system may exhibit severe performance degradation and undo tablespace files growing to tens of gigabytes. InnoDB does not currently emit any automatic warning when HLL crosses these thresholds — you must implement external monitoring using SELECT NAME, COUNT FROM information_schema.INNODB_METRICS WHERE NAME='trx_rseg_history_len'.
Root Causes of HLL Growth
Long-Running Read-Only Transactions Blocking Purge
Even read-only transactions that never modify any data can block the InnoDB purge thread if they hold a REPEATABLE READ read view open for an extended period. Every SELECT in the default AUTOCOMMIT mode uses a fresh read view, but explicit transactions started with BEGIN or START TRANSACTION hold their read view for the transaction's entire duration. Analytics queries or reporting jobs that run long SELECTs inside an explicit transaction are among the most frequent culprits for HLL growth. The fix is to either run such queries outside explicit transactions (letting each statement get a fresh read view), switch them to READ COMMITTED isolation (which re-evaluates the read view per statement), or set innodb_read_only=ON for dedicated read replicas used exclusively for analytics.
innodb_purge_threads and Purge Queue Depth
innodb_purge_threads (default 4 in MySQL 8.0) controls how many parallel threads process the purge queue. On write-heavy servers generating millions of undo records per second, increasing innodb_purge_threads to 8 or 16 can provide linear throughput improvements in purge processing speed. Each purge thread processes a separate undo log segment, so the effective parallelism is bounded by the number of active undo tablespace rollback segments (innodb_rollback_segments, default 128). Monitor the SHOW ENGINE INNODB STATUS output line "Purge done for trx's n:o < N undo n:o < M" — if N advances slower than the current transaction counter in the TRANSACTIONS section header, purge is definitively falling behind and more threads are needed.
Reducing and Controlling Undo Log Size
Identifying and Killing the Oldest Active Transaction
The transaction holding the oldest active read view is always the key target when HLL is growing. Find it with SELECT trx_id, trx_started, NOW() - trx_started AS age_seconds, trx_query FROM information_schema.INNODB_TRX ORDER BY trx_started ASC LIMIT 5. Once identified, evaluate whether the transaction is genuinely needed or can be safely killed with KILL <thread_id>. For recurring offenders — such as a monitoring agent or ORM that opens transactions and never closes them — fix the application code to explicitly commit or roll back after every logical unit of work. Consider setting interactive_timeout and wait_timeout aggressively (e.g., 60 seconds) to force disconnection of idle-in-transaction sessions, which triggers an implicit rollback and allows purge to advance.
Undo Tablespace Configuration in MySQL 8.0+
In MySQL 8.0+, undo tablespace truncation is automatic when innodb_undo_log_truncate=ON (the default). Once an undo tablespace exceeds innodb_max_undo_log_size (default 1 GB), InnoDB marks it for truncation after all transactions referencing it have been purged. The truncation operation shrinks the tablespace file on disk and returns space to the OS, preventing indefinite undo tablespace growth. In MySQL 5.7, undo tablespace truncation required manual rotation: add two extra undo tablespaces, wait for purge to drain the old ones, then drop them. Monitor undo tablespace sizes with SELECT TABLESPACE_NAME, FILE_SIZE / 1024 / 1024 AS size_mb FROM information_schema.FILES WHERE FILE_TYPE LIKE '%UNDO%' ORDER BY FILE_SIZE DESC and alert when any tablespace exceeds innodb_max_undo_log_size × 2.