Understanding and Managing MySQL Undo Tablespaces

1 Introduction

The author encountered a case where the Undo tablespace grew excessively and decided to systematically review Undo tablespace concepts to help readers troubleshoot similar issues.

2 Undo Working Mechanism

Ensuring Transaction Atomicity and Rollback

When a database crashes or a transaction is rolled back, InnoDB uses the Undo Log to restore data to its previous state.

Multi‑Version Concurrency Control (MVCC) – Isolation

InnoDB implements MVCC by reading previous row versions from the Undo Log, enabling non‑locking reads; however, long‑running queries can prevent old Undo data from being cleaned, causing Undo Log bloat.

3 History of Undo Tablespace Management

3.1 Before MySQL 5.6

Undo tablespaces were stored inside the shared ibdata system tablespace. Large uncommitted transactions caused ibdata to expand permanently, wasting space and lengthening physical backups; rebuilding the database was the only remedy.

3.2 MySQL 5.6

InnoDB introduced independent Undo tablespaces, but they required setting innodb_undo_tablespaces at instance initialization and could not be changed later. The Space ID must start at 1 and cannot be added or removed.

Set innodb_undo_tablespaces during initialization; default is 0 (no independent Undo tablespace).

Changing innodb_undo_tablespaces after initialization prevents the server from starting.

Undo tablespace IDs must start from 1 and cannot be increased or deleted.

3.3 MySQL 5.7

A new feature called online Truncate Undo tablespace was added, controlled by the innodb_undo_log_truncate parameter.

3.4 MySQL 8.0

Further optimizations include:

Independent Undo tablespaces are enabled by default; the default number changed from 0 to 2 starting with 8.0.3.

Dynamic online addition and removal of Undo tablespaces are supported.

Undo tablespace naming changed from undoNNN to undo_NNN .

Each Undo tablespace can now contain 128 rollback segments, giving a total of innodb_rollback_segments * innodb_undo_tablespaces segments, reducing lock contention under high concurrency.

The Innodb_undo_truncate parameter (enabled by default) triggers online truncation when the Undo size exceeds innodb_max_undo_log_size .

Undo tablespaces can be encrypted.

4 Undo Tablespace Maintenance

The following practical steps illustrate how to simulate Undo growth, add a new Undo tablespace, and reclaim space.

Typical MySQL instances start with two Undo tablespaces.

Add a new Undo tablespace A.

Set the old Undo tablespace B to inactive; the system automatically reclaims space using innodb_undo_log_truncate=ON .

Reactivate the old Undo tablespace B.

Optionally keep the new Undo tablespace A or set it inactive and drop it.

1. View current Undo tablespaces and their status

mysql> SELECT NAME, STATE
        FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
        WHERE NAME LIKE '%undo%';
+-----------------+--------+
| NAME            | STATE  |
+-----------------+--------+
| innodb_undo_001| active |
| innodb_undo_002| active |
+-----------------+--------+
2 rows in set (0.02 sec)

2. Add a new Undo tablespace

Creating a tablespace with a name starting with innodb_ is prohibited:

mysql> create undo tablespace innodb_undo_003 add datafile 'undo_003.ibu';
ERROR 3119 (42000): InnoDB: Tablespace names starting with `innodb_` are reserved.

File names must end with .ibu .

3. Create an Undo tablespace

mysql> create undo tablespace undo_003 add datafile 'undo_003.ibu';
Query OK, 0 rows affected (0.14 sec)
... (subsequent SELECT shows the new tablespace as active)

4. Automatic reclamation of Undo space

Set innodb_undo_002 to inactive so the system can truncate it.

mysql> ALTER UNDO TABLESPACE innodb_undo_002 SET INACTIVE;
Query OK, 0 rows affected (0.00 sec)
... (SELECT shows state "empty", indicating truncation can occur)

System tablespaces prefixed with innodb_ cannot be dropped because they are reserved.

mysql> DROP UNDO TABLESPACE innodb_undo_002;
ERROR 3119 (42000): InnoDB: Tablespace names starting with `innodb_` are reserved.

5. Drop the newly created Undo tablespace

mysql> ALTER UNDO TABLESPACE undo_003 SET INACTIVE;
Query OK, 0 rows affected (0.00 sec)
... (state becomes "empty")
mysql> DROP UNDO TABLESPACE undo_003;
Query OK, 0 rows affected (0.01 sec)

5 Undo‑Related Parameters

innodb_undo_directory : Directory where Undo files are stored.

innodb_undo_log_truncate : Enables/disables online Undo truncation; ON by default in MySQL 8.0.

innodb_undo_tablespaces : Number of Undo tablespaces created at initialization; can be increased dynamically; must be ≥2 for online truncation.

innodb_purge_rseg_truncate_frequency : Controls how many purge batches (default max 128) occur before a rollback segment is truncated; the batch size is set by innodb_purge_batch_size (default 300).

innodb_max_undo_log_size : Maximum size of an Undo tablespace file; exceeding this triggers truncation, which reduces the file to about 10 MB.

6 Practical Exercise with Reward

The article ends with a challenge: a MySQL 8.0 production cluster experiences a daily Undo file growth of ~270 GB. Readers are invited to suggest solutions; two participants will receive a book from the "Big Wisdom, Small Skills" series.