Optimizing MySQL 8 Undo Tablespace: Controlling History List Length and Reclaiming Disk Space - ITFROMZERO - Share tobe shared! A practical guide to dealing with MySQL 8 Undo Tablespace bloat caused by long-running transactions — from detecting high History List Length and configuring purge threads, to truncating undo tablespaces and reclaiming disk space in production.

Table of Contents

The Real Problem: Undo Tablespace Eating Up Disk Space

My production database runs MySQL 8.0 with around 50GB of data, and one morning I woke up to an alert: only 2GB of disk space remaining when there had been over 20GB the day before. The undo_001 and undo_002 files in the /var/lib/mysql directory had ballooned to over 18GB each.

If you’ve ever experienced something similar, or if your queries keep getting slower despite having proper indexes, the culprit is likely a rising History List Length (HLL) caused by accumulated undo logs.

This issue is fairly common in production environments — especially when batch jobs run long transactions, or a developer forgets to commit/rollback while debugging directly on the production DB.

Root Cause: Why HLL Grows and Undo Logs Aren’t Reclaimed

MySQL InnoDB uses MVCC (Multi-Version Concurrency Control) to allow multiple transactions to read data concurrently without locking. Every time you UPDATE or DELETE a row, InnoDB doesn’t immediately remove the old data — it writes the old version to the undo log so running transactions can still see their consistent snapshot.

The problem occurs when:

A transaction is opened but not committed/rolled back for an extended period
A batch job runs UPDATEs on millions of rows within a single transaction
Replication slave lag causes the slave to still need to read old undo logs from the master

MySQL has a purge thread responsible for cleaning up old undo logs, but it can only clean up entries that no active transaction needs anymore. If there’s an old transaction still open, the purge thread is completely blocked — HLL grows unbounded, and undo files balloon.

Check your HLL right now:

-- Check current History List Length
SHOW ENGINE INNODB STATUS\G
-- Look for the line: History list length XXXX

-- Or query directly from performance_schema
SELECT
  NAME,
  SUBSYSTEM,
  COMMENT
FROM performance_schema.innodb_metrics
WHERE NAME = 'trx_rseg_history_len';

An HLL below 1,000 is normal. At 10,000 and above, it starts affecting performance. Above 100,000, queries will noticeably slow down because every read requires InnoDB to scan through numerous old versions in the undo log to find the correct snapshot for the current transaction.

Solutions

1. Identify the Transaction Blocking Purge

First, find which transaction has been holding the undo log the longest:

-- Find long-running transactions (running > 60 seconds)
SELECT
  trx_id,
  trx_state,
  trx_started,
  TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS duration_sec,
  trx_query,
  trx_tables_in_use,
  trx_tables_locked
FROM information_schema.INNODB_TRX
WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 60
ORDER BY trx_started ASC;

-- Find thread ID to kill if needed
SELECT
  p.id AS process_id,
  p.user,
  p.host,
  p.db,
  p.command,
  p.time,
  p.info
FROM information_schema.PROCESSLIST p
JOIN information_schema.INNODB_TRX t ON p.id = t.trx_mysql_thread_id
WHERE TIMESTAMPDIFF(SECOND, t.trx_started, NOW()) > 60;

If you see a transaction that’s been running for hours with no good reason, kill it:

KILL 12345; -- replace 12345 with the process_id found above

2. Increase the Number of Purge Threads

By default, MySQL only uses 4 purge threads. For write-heavy workloads, increasing this helps clean up undo logs faster:

-- Check current configuration
SHOW VARIABLES LIKE 'innodb_purge_threads';

-- Increase to 8 (no restart needed, takes effect immediately)
SET GLOBAL innodb_purge_threads = 8;

Add to /etc/mysql/mysql.conf.d/mysqld.cnf to make it persistent:

[mysqld]
innodb_purge_threads = 8
innodb_max_purge_lag = 0
innodb_max_purge_lag_delay = 300000

innodb_max_purge_lag_delay caps the maximum delay (in microseconds) that InnoDB imposes on DML operations when HLL gets too high. Setting it to 300000 (0.3 seconds) prevents the system from completely freezing when purge lag is severe.

3. Truncate Undo Tablespace to Reclaim Disk Space

This is a new feature in MySQL 8 — very useful after HLL has been addressed but the undo files are still large on disk.

First, check how many undo tablespaces you have:

-- Check the status of undo tablespaces
SELECT
  TABLESPACE_NAME,
  FILE_NAME,
  AUTOEXTEND_SIZE,
  TOTAL_EXTENTS,
  EXTENT_SIZE,
  (TOTAL_EXTENTS * EXTENT_SIZE) / 1024 / 1024 AS size_mb
FROM information_schema.FILES
WHERE FILE_TYPE = 'UNDO LOG';

MySQL 8 supports automatic truncation via the innodb_undo_log_truncate variable:

-- Enable automatic truncation (ON by default in MySQL 8)
SET GLOBAL innodb_undo_log_truncate = ON;

-- Set truncation threshold: truncate when undo file > 128MB
SET GLOBAL innodb_max_undo_log_size = 134217728; -- 128MB

-- Check truncation status
SELECT
  NAME,
  COMMENT
FROM performance_schema.innodb_metrics
WHERE NAME LIKE '%undo%truncate%';

Add to the config file to make it persistent:

[mysqld]
innodb_undo_log_truncate = ON
innodb_max_undo_log_size = 134217728
innodb_purge_rseg_truncate_frequency = 128

innodb_purge_rseg_truncate_frequency determines how often the purge thread checks whether truncation is needed. The smaller the value, the more frequently truncation occurs (default is 128).

4. Continuously Monitor HLL

Write a simple monitoring script to alert when HLL exceeds the threshold:

#!/bin/bash
# /usr/local/bin/check_mysql_hll.sh

MYSQL_CMD="mysql -u monitor_user -pYOUR_PASSWORD -N -e"
THRESHOLD=10000

HLL=$($MYSQL_CMD "SELECT COUNT FROM performance_schema.innodb_metrics WHERE NAME = 'trx_rseg_history_len'" 2>/dev/null)

if [ -z "$HLL" ]; then
  echo "ERROR: Cannot connect to MySQL"
  exit 2
fi

if [ "$HLL" -gt "$THRESHOLD" ]; then
  echo "WARNING: MySQL HLL = $HLL (threshold: $THRESHOLD)"
  # Send alert via Telegram, Slack, etc.
  exit 1
fi

echo "OK: MySQL HLL = $HLL"
exit 0

Add to crontab to run every 5 minutes:

*/5 * * * * /usr/local/bin/check_mysql_hll.sh >> /var/log/mysql_hll_check.log 2>&1

Best Approach: Combining Prevention with Automatic Reclamation

After going through the undo tablespace bloat incident, here’s the process I found most effective:

Step 1 — Configure MySQL from the Start (add to mysqld.cnf):

[mysqld]
# Automatic undo tablespace truncation
innodb_undo_log_truncate = ON
innodb_max_undo_log_size = 134217728

# Increase purge threads for write-heavy workloads
innodb_purge_threads = 8
innodb_purge_rseg_truncate_frequency = 64

# Limit delay when HLL is high
innodb_max_purge_lag_delay = 300000

# Automatically kill transactions that run too long
# (MySQL 8.0.26+)
innodb_lock_wait_timeout = 50
wait_timeout = 28800
interactive_timeout = 28800

Step 2 — Add constraints at the application layer: Don’t let a single transaction handle too many rows. If you need to UPDATE 1 million rows, split it into batches of 10,000 rows at a time:

-- Instead of:
UPDATE orders SET status = 'archived' WHERE created_at < '2023-01-01';

-- Use a batch loop:
SET @batch_size = 10000;
repeat_loop: LOOP
  UPDATE orders
  SET status = 'archived'
  WHERE created_at < '2023-01-01'
    AND status != 'archived'
  LIMIT @batch_size;
  
  IF ROW_COUNT() = 0 THEN
    LEAVE repeat_loop;
  END IF;
  
  -- Add a small sleep to give the purge thread time to clean up
  DO SLEEP(0.1);
END LOOP;

Step 3 — If the undo file has already bloated and you need to reclaim space immediately: MySQL 8 cannot shrink undo files automatically right away if active transactions are still running. Emergency procedure:

-- 1. Kill all long-running transactions
SELECT CONCAT('KILL ', p.id, ';')
FROM information_schema.PROCESSLIST p
JOIN information_schema.INNODB_TRX t ON p.id = t.trx_mysql_thread_id
WHERE TIMESTAMPDIFF(SECOND, t.trx_started, NOW()) > 300;
-- Copy the results and run each KILL command

-- 2. Create a new undo tablespace (MySQL 8.0.14+)
CREATE UNDO TABLESPACE undo_003
  ADD DATAFILE 'undo_003.ibu';

-- 3. Mark old undo tablespaces as INACTIVE for truncation
ALTER UNDO TABLESPACE undo_001 SET INACTIVE;
ALTER UNDO TABLESPACE undo_002 SET INACTIVE;

-- 4. Wait for MySQL to auto-truncate (monitor status)
SELECT TABLESPACE_NAME, STATE
FROM information_schema.INNODB_TABLESPACES
WHERE TABLESPACE_TYPE = 'UNDO';
-- Wait until STATE = 'empty'

-- 5. Once empty, reactivate
ALTER UNDO TABLESPACE undo_001 SET ACTIVE;
ALTER UNDO TABLESPACE undo_002 SET ACTIVE;

-- 6. Drop the temporary tablespace if no longer needed
ALTER UNDO TABLESPACE undo_003 SET INACTIVE;
-- Wait for it to be empty, then DROP
DROP UNDO TABLESPACE undo_003;

This optimization made my production database significantly more stable — average query time dropped from 200ms to 40–60ms once HLL was controlled below 1,000, and disk space is no longer being eaten away by uncleaned undo logs.

The most important takeaway: don’t wait for an incident to happen before taking action. Add HLL monitoring to your alerting system early, and review your batch job code to ensure no transaction holds locks for more than 5 minutes.