Overview
If you ever see MySQL disk usage bloated up even after deleting millions of rows or queries suddenly starting to slow down, you might be dealing with large Undo logs.
In this blog, we will demystify the Innodb Undo Log, explain why it needs purging and look at ways to manually purge or speed up the purge of undo logs.
What are undo logs
When you update or delete a row in MySQL (InnoDB), the data page in memory (within InnoDB buffer pool) is immediately modified with new value or flagged with a delete mark, while an old version of the row is stored in a backing structure called the Undo Log. This allows currently active transactions (those started before changes are committed) to see a consistent snapshot of the data as it existed before the changes, and it allows transaction to Rollback if something goes wrong.
In this post you will find the term history list length used multiple times. The History List Length (HLL) is the metric tied to Undo Logs and Multi-Version Concurrency Control (MVCC), it is the count of undo logs that are being kept around so that long-running tasks can still see the database exactly as it looked when they started.
Understanding the problems of high History List Length
These old versions act like a linked list of history. Once all transactions that needed the old data have finished, those undo log records are no longer necessary. It is important to understand that a high History List Length (HLL) is primarily an indicator of an underlying issue rather than a standalone problem. It typically stems from one of two scenarios: either long-running transactions are active (requiring old row versions for data consistency), or the purge process is failing to keep pace with the write workload. In either case, a high HLL signals potential issues with application behavior or database tuning. When these old versions accumulate as a linked list of history that cannot be removed, it leads to several negative impacts:
-
Storage bloat: Unpurged records bloat both undo tablespaces and .ibd datafiles. While MySQL 8.0+ automatically truncates purged undo logs to return physical space to the OS, purging also crucially allows InnoDB to recycle internal .ibd space for future writes, preventing severe table fragmentation.
-
Performance degradation: Unpurged logs degrade performance on two fronts. First, scans become slower because frequently updated rows build up massive chains of old versions. If an old transaction reads these rows, the database has to traverse a long row specific chain of the old version to reconstruct the data. Second, these stale undo pages flood the InnoDB buffer pool, evicting frequently accessed “hot” data and straining system-wide memory resources causing overall performance degradation.
Purge: How undo logs are removed?
Purge is a removal process to delete an old version of a row and its index record physically when the row is no longer required for multi-version concurrency control (MVCC) or rollback.
The purge process is handled by a set of dedicated background threads (the Purge Threads).
-
Identification: The purge coordinator checks the history list, It identifies undo log records that are older than the oldest active transaction view.
-
Processing: It distributes the work to the worker threads.
-
Cleanup:
-
Delete-marked records: It physically removes rows that were marked for deletion.
-
Update undo logs: It frees the undo log pages.
-
-
Truncation: If configured, it truncates the undo tablespace files on disk to return space to the OS.
Strategies to speed up purging of undo logs
There are few methods which involve MySQL configuration changes to help speed up the purging process.
Prerequisite
Before starting configuring the flags, the root cause of a skyrocketing History List Length must be addressed. In almost all cases, a massive HLL is caused by a long running transaction that is actively preventing InnoDB from cleaning up old row versions.
While a deep dive into troubleshooting rogue transactions is outside the scope of this post, the first step should always be to identify and terminate the offending query. It can be found by inspecting the SHOW ENGINE INNODB STATUS report, checking the SHOW FULL PROCESSLIST, or querying the information_schema.innodb_trx table to spot transactions that have been open for an abnormally long time.
Increase purge threads
The most direct way to speed up the purge process is to increase the throughput by adding parallelism.
| Flags | innodb_purge_threads |
| Description | Specifies the number of background threads dedicated entirely to the innodb purge operation of undo logs. |
| Recommendation | For an instance with 2vCPU to 4vCPU, leave this as the default value of 4. For larger instances with 8vCPUs or more, increase the value between 0.5x to 1x of total vCPU count. For example,on a 16vCPUs machine, you can increase the value in the range of 8 to 16. Just keep in mind that setting this too high can increase CPU contention, and the absolute maximum allowed value is 32. |
| Pros | Utilize multi-core CPUs for parallel processing of history list. |
| Cons | Increase CPU contention. |
Increase purge batch size
Purge batch size configuration controls how many undo log pages are grouped in one go.
| Flags | innodb_purge_batch_size |
| Description | Determines the number of undo log pages that the background threads parse and process in a single batch. |
| Recommendation | Increase the flag to 1000 or 5000 depending on the storage system’s IOPS capacity. For example: With modern NVMe or high performance SSDs supporting 20,000+ IOPS, the flag can be set between 3000 to 5000. With standard SSDs supporting 3000-10000 IOPS, the flag can be set between 1000 to 2000. With HDD supporting less than 2000 IOPS, the flag value can be set between 300-1000 |
| Pros | Reduces overhead of context switching by purging more data together in one batch. |
| Cons | Because the purge threads are requesting larger blocks of data from the disk at once, they consume more I/O bandwidth in aggressive bursts. If the batch size is set too high for the storage subsystem to handle, these background reads/writes will clog the I/O queue. |
Note: Ensure innodb_purge_batch_size does not exceed innodb_io_capacity, as setting the batch size higher than your I/O limit provides no performance benefit.
Increase InnoDB IO budget
Increasing Innodb’s I/O budget by setting innodb_io_capacity and innodb_io_capacity_max flags allows the purge thread to perform more IOPS when removing old versions and truncating undo tablespaces.
| Flags | innodb_io_capacity, innodb_io_capacity_max |
| Description | These variables define the baseline and peak I/O operations per second that InnoDB is permitted to use for background tasks. |
| Recommendation | Depending on storage system and IOPS capacity increase these values in increments of 500 while monitoring the purge process. |
| Pros | Purging old records modifies data and undo pages in the buffer pool, creating “dirty” pages. If innodb_io_capacity is set too low, page flushing bottlenecks, exhausting buffer pool free space and stalling the purge threads. Increasing these I/O flags allows page cleaners to flush aggressively, ensuring continuous free memory for the purge process to proceed. |
| Cons | Setting it too high on slow storage can negatively impact SQL queries, leading to increased latency. |
Aggressive undo tablespace truncation
While it does not speed up purging, this method can speed up disk space reclamation.
| Flags | innodb_undo_log_truncate, innodb_purge_rseg_truncate_frequency |
| Description | The innodb_undo_log_truncate flag when enabled allows the truncation of undo logs when they are above the threshold value defined by innodb_max_undo_log_size by marking them for truncation. The innodb_purge_rseg_truncate_frequency flag can be used to expedite truncation of undo tablespaces |
| Recommendation | Ensure innodb_undo_log_truncate is set to ON. Set innodb_purge_rseg_truncate_frequency to 1-10, to check for truncation more often. |
| Pros | Help to keep the disk usage low and return space to the operating system faster. |
| Cons | Can increase the number of IO operations. |
Throttling writes
If the purge threads simply cannot keep up with the workload, you can force the database to slow down write operations to let the purge catch up. The flags innodb_max_purge_lag and innodb_max_purge_lag_delay injects a delay into the DMLs operations to help purge lag to process deleted records.
| Flags | innodb_max_purge_lag, innodb_max_purge_lag_delay |
| Description | These settings act as a safety valve, throttling user queries when the system’s background purge operations fall critically behind. |
| Recommendation | Warning: This configuration should be treated as a last resort, applied only when all other optimizations have failed. It is critical to set both innodb_max_purge_lag and innodb_max_purge_lag_delay together, enabling the lag threshold alone can result in an uncapped delay that may halt your application entirely. To implement this effectively, first monitor your current History List Length (HLL) and set innodb_max_purge_lag to a value below it, for instance, setting it to 1 million if your HLL is currently 2 million. The throttling will only trigger once the HLL exceeds this defined limit. You can then control the intensity of the throttle via innodb_max_purge_lag_delay, which is measured in microseconds. While 10ms (10,000 microseconds) is a recommended starting point, you must ensure the application’s specific architecture can tolerate this added latency without experiencing timeouts. |
| Pros | Add delay to DMLs to provide purge threads more time to process deleted records. |
| Cons | Can cause performance impact and increased latency by delaying writes. |
Manual purging of undo logs
The manual purging of undo logs only applies to MySQL 8.0+ versions whereas prior versions rely exclusively on innodb_undo_log_truncation flag for automatic cleanup.
While InnoDB usually handles undo log purging automatically, manual purging becomes necessary when the undo tablespace becomes too large. If your instance is running dangerously low on disk space or the performance is dropped because of high HLL and you cannot afford to wait for the automatic background truncation to slowly reclaim the storage, taking manual control such as deactivating a specific undo tablespace allows you to quickly force the cleanup. This drains the inactive logs, immediately returns storage space to the operating system, and has the added benefit of accelerating the overall purging process.
Steps for manual purging:
- Get the current state of undo logs
SELECT NAME, STATE , FILE_SIZE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where name like '%undo%';
SELECT TABLESPACE_NAME, FILE_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE LIKE 'UNDO LOG' and tablespace_name like '%undo%';
- Create two new temporary undo tablespaces
CREATE UNDO TABLESPACE undo_tempspace_for_truncation1 ADD DATAFILE '/mysql/datadir/undo_003.ibu';
CREATE UNDO TABLESPACE undo_tempspace_for_truncation2 ADD DATAFILE '/mysql/datadir/undo_004.ibu';
- Mark existing undo tablespaces as inactive
ALTER UNDO tablespace innodb_undo_001 SET INACTIVE;
ALTER UNDO tablespace innodb_undo_002 SET INACTIVE;
Validate old undo tablespaces are inactive and the new temporary tablespace is shown active
SELECT NAME, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where name like '%undo%';
- Monitor and wait for the truncation. The undo_001 and undo_002 should go back to default (16MB).
SELECT NAME, STATE , FILE_SIZE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where name like '%undo%';
- Swap active-inactive tablespaces i.e. mark new temporary undo tablespaces inactive and old undo tablespaces active.
ALTER UNDO tablespace innodb_undo_001 SET ACTIVE;
ALTER UNDO tablespace innodb_undo_002 SET ACTIVE;
ALTER UNDO tablespace undo_tempspace_for_truncation1 SET INACTIVE;
ALTER UNDO tablespace undo_tempspace_for_truncation2 SET INACTIVE;
- Drop temporary created undo tablespaces in step#2
drop undo tablespace undo_tempspace_for_truncation1;
drop undo tablespace undo_tempspace_for_truncation2;
Conclusion
Effective management of Undo Logs is essential for maintaining a healthy and performant MySQL environment. A high History List Length (HLL) acts as a silent performance killer, leading to significant disk bloat and slowing down active transactions that must traverse long chains of older row versions to reconstruct their required data snapshots. By implementing the strategies discussed, such as increasing parallelism through innodb_purge_threads, tuning the innodb_purge_batch_size for better throughput, or performing manual tablespace truncation in MySQL 8.0+, we can ensure that the purge process keeps pace with heavy write workloads. Monitoring these metrics proactively allows for a lean database that remains responsive even under intense pressure.