Stop ibdata1 Growing Forever by Tuning MySQL Undo Log Purge (2026)

The ibdata1 file in MySQL grows indefinitely because the undo log, which tracks transaction changes, isn’t being purged efficiently.

Here’s what’s happening and how to fix it:

The Problem: Undo Log Bloat

When transactions modify data, MySQL writes the "before" image of the modified data to the undo tablespace (which is part of ibdata1 by default). This undo information is crucial for features like transaction rollback, MVCC (Multi-Version Concurrency Control), and point-in-time recovery. However, if this undo information isn’t cleaned up (purged) promptly after transactions commit, it accumulates, leading to the ibdata1 file growing uncontrollably.

Common Causes and Fixes

Long-Running Transactions: A single, very long-running transaction can hold onto undo log records for an extended period, preventing them from being purged.
- Diagnosis: Check for long-running transactions.
```
SHOW PROCESSLIST;
```
  Look for queries with a Time value significantly higher than others.
- Fix: Identify and, if possible, optimize or terminate the long-running transaction. If it’s a legitimate, slow process, you might need to adjust your application logic.
- Why it works: Once the transaction commits or is terminated, the associated undo log records become eligible for purging.
Low innodb_purge_threads: This setting controls how many threads are dedicated to purging undo log records. If it’s too low, purging can’t keep up with the rate of transaction commits.
- Diagnosis: Check the current setting.
```
SHOW VARIABLES LIKE 'innodb_purge_threads';
```
- Fix: Increase innodb_purge_threads. A common recommendation is to set it to 4 or 8 for busy systems. You’ll need to edit your my.cnf (or my.ini) file:
```
[mysqld]
innodb_purge_threads = 8
```
  Then restart MySQL.
- Why it works: More threads can process and remove unneeded undo log records concurrently, speeding up the purge process.
High innodb_max_purge_lag: This is a dynamic variable that limits the amount of undo log that can be generated. If it’s reached, MySQL will pause DML operations to prevent further undo log generation. While this is a safety mechanism, if it’s consistently hit, it indicates a bottleneck.
- Diagnosis: Monitor Innodb_row_lock_waits and Innodb_rows_read status variables. If Innodb_row_lock_waits is high and Innodb_rows_read is relatively low, it might indicate the purge system is struggling. Also, check the SHOW ENGINE INNODB STATUS output for information related to purge.
- Fix: Increase innodb_max_purge_lag. This is a dynamic variable, so you can set it without a restart:
```
SET GLOBAL innodb_max_purge_lag = 1000000000; -- Example: 1GB
```
  It’s often beneficial to set this in your my.cnf as well for persistence.
```
[mysqld]
innodb_max_purge_lag = 1000000000
```
- Why it works: Increasing this limit allows more undo log to be generated before DML operations are throttled, giving the purge threads more headroom to catch up. However, this is a temporary band-aid if the underlying purge mechanism is too slow.
innodb_purge_batch_size Too Small: This parameter controls how many undo log records are processed in a single purge operation. A small batch size means more operations are needed to clear the same amount of undo log.
- Diagnosis: Observe SHOW ENGINE INNODB STATUS. Look at the PURGE THREAD section for statistics like purge ops and purge time.
- Fix: Increase innodb_purge_batch_size. A common starting point is 1000 or 10000.
```
[mysqld]
innodb_purge_batch_size = 10000
```
  Restart MySQL after changing this.
- Why it works: Larger batches allow purge threads to process more undo records per operation, reducing the overhead of starting and stopping purge cycles.
Not Using innodb_file_per_table: If you are still using the older, single ibdata1 file for all InnoDB data and indexes (instead of separate .ibd files per table), shrinking ibdata1 is extremely difficult and often requires a full dump and restore. The purge process can reclaim space within ibdata1, but it won’t release that space back to the operating system.
- Diagnosis: Check if innodb_file_per_table is enabled.
```
SHOW VARIABLES LIKE 'innodb_file_per_table';
```
  If it’s OFF, you’re in this situation.
- Fix: Enable innodb_file_per_table and migrate your data. This is a multi-step process:
  1. Enable the setting in my.cnf:
```
[mysqld]
innodb_file_per_table = ON
```
  2. Restart MySQL.
  3. For each table, run ALTER TABLE table_name ENGINE=InnoDB;. This will move the table’s data and indexes into its own .ibd file.
  4. After migrating all tables, you can potentially drop the old ibdata1 file after a full backup and verification, but this is risky. A safer approach is to create a new MySQL instance with innodb_file_per_table=ON and dump/restore your data into it.
- Why it works: innodb_file_per_table=ON ensures that each table’s data is stored in its own .ibd file. When data is deleted or updated, the space within these .ibd files can be reclaimed and reused by MySQL, and more importantly, the .ibd file itself can be shrunk by OPTIMIZE TABLE or when dropping tables.
Old MySQL Versions (Pre-5.6): In older versions of MySQL, the undo log management was less sophisticated.
- Diagnosis: Check your MySQL version.
```
SELECT VERSION();
```
- Fix: Upgrade to a modern, supported version of MySQL (8.0+ is highly recommended). Newer versions have significantly improved undo log management and purge capabilities.
- Why it works: Newer versions implement more efficient purge algorithms and better control over undo retention, making the process more robust.

After the Fix

Once you’ve addressed the root cause, the purge threads will start cleaning up the old undo log records. This process can take a significant amount of time, especially if ibdata1 has grown very large. Monitor SHOW ENGINE INNODB STATUS to see the purge thread activity. You won’t see ibdata1 shrink on disk unless you’ve migrated to innodb_file_per_table=ON and performed table optimizations or a full instance rebuild.

The next error you might encounter is related to transaction isolation levels if the purge process falls too far behind, or potentially Innodb_data_too_free if the purge process is too efficient and tries to free space that isn’t actually available yet.