MySQL InnoDB History List Length: Why It Grows and How to Bring It Down

The InnoDB history list length is one number in SHOW ENGINE INNODB STATUS that tells you whether MySQL is keeping up with cleaning old row versions. When it climbs from a few thousand to millions, queries slow down, undo tablespaces grow, and the server keeps doing extra work for data that should have been gone long ago. In most cases the root cause is not purge itself but a transaction that has been open for far too long.

What the history list length actually counts

InnoDB keeps old versions of changed rows in undo logs, so that other transactions can still read a consistent snapshot. According to the MySQL purge documentation, the transaction system keeps a list of transactions that have index records delete-marked by UPDATE or DELETE. The length of that list is the purge lag, shown as History list length in the TRANSACTIONS section.

Purge is the background process that removes delete-marked records and frees undo once no transaction can need them any more. The documentation says the value is typically low, usually under a few thousand, and that a write-heavy workload or long-running transactions, even read-only ones, can make it grow.

How to check it

-- the classic place
SHOW ENGINE INNODB STATUS\G
--   ------------
--   TRANSACTIONS
--   ------------
--   Trx id counter 48211973
--   Purge done for trx's n:o < 48211950 undo n:o < 0 state: running but idle
--   History list length 1843

-- the same number, easy to graph
SELECT name, count
FROM information_schema.INNODB_METRICS
WHERE name = 'trx_rseg_history_len';

The trx_rseg_history_len counter in INNODB_METRICS is enabled by default, so you can collect it with any monitoring system and alert on the trend. A single high reading after a batch job is fine; a line that only goes up is not.

Why it grows

1. A long-running transaction holds an old read view

This is the most common reason. Under REPEATABLE READ, the default isolation level, a transaction must keep seeing data as it was when its read view was created. The documentation explains that InnoDB therefore has to keep the undo needed for that view until every transaction depending on it has finished. One session that opened a transaction an hour ago blocks purge of everything changed since then, on every table.

The documentation lists two typical examples:

• A mysqldump --single-transaction running while there is a lot of concurrent DML.
• A SELECT after disabling autocommit, with no COMMIT or ROLLBACK afterwards. Connection pools and ORMs that leave sessions in an open transaction cause the same effect.

Other usual suspects are reporting queries that run for hours, interactive sessions left open in a SQL client, and application bugs that start a transaction and then wait on an external API.

2. Purge simply cannot keep up

With sustained heavy UPDATE and DELETE traffic, purge can fall behind even without long transactions. The multi-versioning chapter warns that in that case tables can keep growing with dead rows until everything becomes disk-bound and slow.

Find the transaction holding purge back

SELECT t.trx_id, t.trx_state, t.trx_started,
       TIMESTAMPDIFF(SECOND, t.trx_started, NOW()) AS age_s,
       t.trx_mysql_thread_id, t.trx_rows_modified,
       p.user, p.host, p.command, p.time, LEFT(p.info, 80) AS current_sql
FROM information_schema.INNODB_TRX t
LEFT JOIN information_schema.PROCESSLIST p ON p.id = t.trx_mysql_thread_id
ORDER BY t.trx_started
LIMIT 10;

Look at the oldest rows. A transaction that is RUNNING for thousands of seconds while the connection shows Sleep is the classic culprit: the application opened it and forgot it. A transaction with a large trx_rows_modified may also be holding locks, which is the story behind Lock wait timeout exceeded.

Fixes, from most to least important

1. Remove long transactions. Commit regularly, including in transactions that only read; the MySQL documentation recommends exactly that. Fix connection pool and ORM settings so sessions return with autocommit on and no open transaction.
2. Move long reads away from the primary. Long reporting queries and logical backups on a busy primary hold old read views. Run them on a replica, or use a physical backup tool for large datasets.
3. Batch big DML. Delete or update in chunks of thousands of rows with a commit after each chunk, instead of one statement touching tens of millions.
4. Give purge more threads if it is truly the bottleneck. In MySQL 8.4 innodb_purge_threads defaults to 1 on servers with 16 or fewer logical processors and 4 otherwise (MySQL 8.0 used 4). The range is 1 to 32, and it is not dynamic, so a change needs a restart. More threads help mostly when DML is spread over many tables. innodb_purge_batch_size defaults to 300.
5. Throttle writers as a last resort. innodb_max_purge_lag (default 0, no limit) delays DML when the history list exceeds the threshold, capped by innodb_max_purge_lag_delay (microseconds, default 0). Both are dynamic. This protects the server but slows the application, so use it carefully.

Undo tablespaces and disk space

A long history list means undo tablespaces keep growing. MySQL creates two default undo tablespaces, and at least two are needed for automatic truncation. With innodb_undo_log_truncate (default ON), an undo tablespace larger than innodb_max_undo_log_size (default 1 GiB) is marked for truncation, but only after purge has freed its rollback segments. innodb_purge_rseg_truncate_frequency (default 128) controls how often that happens. Check the current size:

SELECT name, file_size, allocated_size, state
FROM information_schema.INNODB_TABLESPACES
WHERE space_type = 'Undo';

The point: as long as the old transaction is open, truncation cannot help. Undo that is still needed cannot be removed, and if the disk fills up, you are dealing with problems like MySQL error 1114: the table is full. See the undo tablespaces documentation for adding, inactivating and dropping undo tablespaces.

What changes on large servers

• The damage scales with write rate, not with the age of the transaction alone. On a server doing tens of thousands of writes per second, one hour of blocked purge can mean tens of gigabytes of undo and millions of delete-marked index records.
• Reads get slower for everyone. Queries that touch recently changed rows may need to walk long version chains in undo, and delete-marked records that are not purged yet still have to be skipped in index scans. On big tables this shows up as higher CPU and I/O with no obvious change in the queries.
• Recovery takes time after the blocker goes away. Once the long transaction ends, purge has to work through the whole backlog. On a large server that can take hours and adds I/O while it runs. Plan for it instead of expecting the number to drop at once.
• Logical backups of big databases are a common cause. A mysqldump --single-transaction of several terabytes can run for many hours on the primary and hold one read view the whole time. Use a replica or physical backups.
• Replicas have their own purge. Each server manages its own undo. Long reports on a replica can grow the history list there while the primary looks healthy, and a replica in that state may also fall behind in replication. Monitor the metric on every server.
• Undo space can be large and slow to reclaim. Leave disk headroom for undo on big servers, and do not count on truncation to free space quickly while purge is still catching up.