MySQL history list length keeps growing: find the old transaction and clear InnoDB purge lag

#Symptom

Queries that were fast get slower for no clear reason. CPU and read I/O rise, undo tablespaces grow and disk usage climbs with no matching rise in data. SHOW ENGINE INNODB STATUS shows a large and rising history list length.

SELECT NAME, COUNT
FROM information_schema.INNODB_METRICS
WHERE NAME = 'trx_rseg_history_len';

The trend is what matters most. These numbers are rough, and a level that's stable on your workload may be fine.

#Why it hurts

Updates and deletes keep old row versions in undo logs, so that consistent reads can see them. The purge threads remove those old versions once no active read view needs them.

If one old transaction keeps a read view open, purge can't move past that point. Undo records pile up, and every consistent read that touches a changed row must walk a longer version chain. So one idle transaction can slow down the whole server, even if it's doing nothing.

#Step 1: confirm that it's growing

Sample every minute for a few minutes:

SELECT NOW() AS at_time, COUNT AS history_len
FROM information_schema.INNODB_METRICS
WHERE NAME = 'trx_rseg_history_len';

And read the status:

SHOW ENGINE INNODB STATUS\G

In the TRANSACTIONS section, look for:

Trx id counter 8473920
Purge done for trx's n:o < 8100112 undo n:o < 0 state: running but idle
History list length 3248120

The gap between the trx counter and "Purge done for" shows how far behind purge is. running but idle while the list stays long is a strong sign that purge is blocked, not slow.

#Step 2: find the oldest transaction

SELECT t.trx_id,
       t.trx_started,
       TIMESTAMPDIFF(SECOND, t.trx_started, NOW()) AS age_s,
       t.trx_state,
       t.trx_mysql_thread_id AS processlist_id,
       t.trx_rows_modified,
       t.trx_isolation_level,
       p.user, p.host, p.db, p.command, p.time AS idle_s,
       LEFT(t.trx_query, 100) AS current_query
FROM information_schema.INNODB_TRX AS t
LEFT JOIN information_schema.PROCESSLIST AS p
  ON p.id = t.trx_mysql_thread_id
ORDER BY t.trx_started
LIMIT 10;

Find out what a sleeping session did, using Performance Schema:

SELECT h.thread_id, h.event_id, LEFT(h.sql_text, 150) AS sql_text
FROM performance_schema.events_statements_history AS h
JOIN performance_schema.threads AS t ON t.thread_id = h.thread_id
WHERE t.processlist_id = 12345
ORDER BY h.event_id DESC
LIMIT 10;

Check for forgotten prepared XA transactions, which don't appear with a normal session:

XA RECOVER;

Warning

Don't resolve XA transactions with XA COMMIT or XA ROLLBACK unless you know which application owns them. It's a distributed transaction, so the wrong choice can leave systems inconsistent.

#Step 3: end the transaction safely

First try to get the owner to commit or stop it. If you need to kill it, check the cost first:

• trx_rows_modified near zero: a read-only or idle session, so killing it is usually cheap.
• A large trx_rows_modified: rollback takes a long time, possibly as long as the work done so far.

KILL 12345;

On RDS, use CALL mysql.rds_kill(12345);.

Caution

Killing a big writing transaction triggers a rollback that can't be cancelled, and restarting the server won't skip it; crash recovery has to finish the rollback. Check trx_rows_modified, and tell the application team, before killing one. Check rollback progress with trx_state = 'ROLLING BACK' and trx_rows_modified going down.

After this, watch the history list length fall:

SELECT COUNT FROM information_schema.INNODB_METRICS WHERE NAME = 'trx_rseg_history_len';

#Step 4: help purge catch up

If the list falls too slowly, check the settings:

SHOW VARIABLES WHERE Variable_name IN
 ('innodb_purge_threads', 'innodb_purge_batch_size',
  'innodb_max_purge_lag', 'innodb_max_purge_lag_delay',
  'innodb_undo_log_truncate', 'innodb_max_undo_log_size',
  'innodb_io_capacity', 'innodb_io_capacity_max');

SET GLOBAL innodb_purge_batch_size = 600;

Change one at a time, and put defaults back when it has recovered. Reduce write load too, if you can: pausing big batch jobs helps a lot.

innodb_max_purge_lag deliberately delays DML to protect the server. It's a safety valve, so if you turn it on, set innodb_max_purge_lag_delay, so that delays stay within limits.

#Step 5: reclaim undo space

Clearing the list doesn't shrink undo files right away. With truncation on, InnoDB shrinks undo tablespaces that have grown beyond the limit, after they've been purged:

SELECT NAME, STATE, ROUND(FILE_SIZE/1024/1024/1024, 1) AS size_gb
FROM information_schema.INNODB_TABLESPACES
WHERE SPACE_TYPE = 'Undo' OR ROW_FORMAT = 'Undo'
ORDER BY FILE_SIZE DESC;

SET GLOBAL innodb_undo_log_truncate = ON;
SET GLOBAL innodb_max_undo_log_size = 1073741824;

Check STATE as it progresses. For versions where these columns differ, consult the docs for your build.

#Managed services

Aurora's shared storage is a special case. If nothing looks old on the writer, check the readers too.

#Prevent it next time

• Alert on rising history list length and on any transaction older than an agreed age, such as 10 minutes.
• Set timeouts: wait_timeout for abandoned sessions, and MAX_EXECUTION_TIME for long SELECTs.
• Run backups with --single-transaction on a replica, not the primary, and run big reports there too.
• Commit batch jobs in small chunks, rather than one big transaction.
• Fix apps with autocommit off that leave transactions open.
• Related: replication lag, slow query log analysis and deadlocks.