QueryKings | The long transaction you forgot: InnoDB history list length and purge lag

A common shape of incident: nothing changed. No deploy, no schema change, no traffic spike. Yet queries that ran in 3 ms now take 300 ms, the data directory is growing steadily, and the slow query log is full of statements that look fine in EXPLAIN. Restarting the application "fixes" it for a while.

That pattern is usually InnoDB purge lag, and behind it is a transaction somebody opened and never closed.

What the history list actually is

InnoDB gives you consistent reads through multi-version concurrency control. When a row is modified, the previous version is written to an undo log record. A transaction that started earlier sees the older version by walking that undo chain backwards from the current row.

Undo records can be discarded only once no open transaction could still need them. The purge threads do that work in the background. The count of undo log records not yet purged is reported as history list length.

The consequence is the part people miss: a single open read-view transaction pins undo for the whole server. It does not matter that the transaction only touched one table. If it began at time T and is still open, InnoDB must keep every version of every row that has changed since T. On a write-heavy system that is millions of records an hour.

The damage is not primarily disk. It is that reads get slower, because reconstructing an old row version means traversing a longer undo chain. A point lookup by primary key on a hot, frequently-updated row can end up reading hundreds of undo records to produce one answer. The plan is unchanged; the work per row is not.

Measuring it

Start with the number itself:

SELECT count FROM information_schema.innodb_metrics
WHERE name = 'trx_rseg_history_len';

Or from SHOW ENGINE INNODB STATUS, under the TRANSACTIONS section:

History list length 4823194

There is no universal threshold. A few thousand on a busy OLTP server is normal and self-clearing. Steady growth over hours, or a value in the millions that does not come back down, means purge is not keeping up or is blocked outright. Graph it; the shape tells you more than the value.

Then find the transaction responsible:

SELECT trx_id,
       trx_state,
       trx_started,
       TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS age_secs,
       trx_mysql_thread_id AS conn_id,
       trx_rows_modified,
       trx_query
FROM information_schema.innodb_trx
ORDER BY trx_started ASC
LIMIT 10;

The oldest row is the one holding the read view. Two results are worth expecting:

trx_query is NULL and trx_state is RUNNING. The transaction is open but idle — a connection that ran a SELECT inside an explicit transaction, then went back to the pool without a COMMIT or ROLLBACK. This is the single most common cause we find.
trx_query is a long analytical SELECT. A reporting query, an mysqldump without --single-transaction discipline, or a nightly export that now runs for four hours because the table grew.

Join to the connection to identify the culprit:

SELECT t.trx_id, t.trx_started, p.user, p.host, p.db, p.time, p.state
FROM information_schema.innodb_trx t
JOIN information_schema.processlist p ON p.id = t.trx_mysql_thread_id
ORDER BY t.trx_started ASC;

The user and host pair is what turns a database problem into a code review: it names the service, and usually the ORM session or the framework middleware that opens a transaction per request and only commits on writes.

Undo tablespace growth, and why it does not shrink

While purge is behind, the undo tablespaces grow. On MySQL 8.0 and later, undo lives in separate tablespaces (undo_001, undo_002, and any you added) and supports truncation:

SELECT tablespace_name, file_name, ROUND(file_size/1024/1024) AS size_mb
FROM information_schema.files
WHERE file_type = 'UNDO LOG';

innodb_undo_log_truncate is ON by default in 8.0, and undo tablespaces larger than innodb_max_undo_log_size (1 GB by default) are truncated once they are no longer needed. "No longer needed" is the operative clause: truncation cannot happen while the old read view is still open. Space is not reclaimed until the blocking transaction ends and purge catches up, and then it happens on its own. Adding disk buys time; it does not solve anything.

If you are still on 5.7 with undo in the system tablespace, the growth is inside ibdata1 and it never comes back without a logical dump and reload. That is one of the quieter arguments for finishing the 8.x upgrade.

Fixing it now

During an incident, the sequence is short:

Confirm the oldest transaction and its age from innodb_trx.
Confirm it is genuinely idle or genuinely expendable — check with whoever owns that service if there is any doubt. Killing a transaction that has modified rows means a rollback, and a large rollback is itself an outage-shaped event.
KILL <conn_id> for the connection, not KILL QUERY, if the transaction is idle. KILL QUERY on an idle transaction does nothing; the transaction stays open.
Watch history list length. It should fall steadily. If it does not, purge itself is the bottleneck.

If purge is the bottleneck — the number falls too slowly on a write-heavy server with no old transactions left — the levers are innodb_purge_threads (default 4; raising it to 8 helps on many-core servers with high delete/update churn, and requires a restart) and innodb_max_purge_lag, which throttles incoming DML once the history list exceeds a threshold. Use innodb_max_purge_lag with care and always with innodb_max_purge_lag_delay set to bound the added delay; an unbounded throttle turns a slow server into a stopped one. We reach for it rarely, and only after the real fix is scheduled.

Keeping it from coming back

The durable fixes are not server settings.

Bound transaction lifetime in the application. Do not open a transaction per web request. Open it around the write, commit, and get out. Frameworks that wrap every request in BEGIN are the usual offender, and the setting to change is in your code, not my.cnf.
Never hold a transaction across an external call. The same rule that prevents lock wait timeouts prevents purge lag, for the same reason.
Give reporting its own replica and accept lag there. A four-hour analytical query on a dedicated replica costs you nothing; on the primary it costs everyone.
Alert on transaction age, not just history list length. A simple check — oldest trx_started older than five minutes — catches the cause before the symptom. This is one of the highest-value, lowest-effort database alerts you can add, and most monitoring setups do not have it.
Audit the batch jobs. mysqldump --single-transaction deliberately holds a long read view; that is how it gets a consistent snapshot. Point it at a replica, or use a snapshot-based backup, or accept the purge cost knowingly during a quiet window.

What good looks like

On a healthy write-heavy server, history list length sawtooths within a band and returns to a low baseline; undo tablespace size is stable; and the oldest open transaction is measured in milliseconds, not minutes. Those three facts on one dashboard make an entire class of "nothing changed but everything is slow" incidents diagnosable in about ninety seconds.

If your history list is in the millions right now and you cannot find the transaction holding it, send us the innodb_trx output and the TRANSACTIONS section of SHOW ENGINE INNODB STATUS. It is usually visible in both, and it is usually one connection.