MySQL Undo Mechanism Evolution and Management

A few days ago, I encountered a case where the Undo space became excessively large. To facilitate subsequent troubleshooting, I systematically reviewed the relevant knowledge about Undo tablespaces, hoping to help fellow readers. Author: Yang Qilong, also known as "North in the South," a senior DBA primarily responsible for database architecture design and operation platform development, excelling in database performance tuning and fault diagnosis. Produced by the Aikeqisheng Open Source Community, original content must not be used without authorization, and reprinting requires contacting the editor and specifying the source. This article is approximately 1700 words and is expected to take about 6 minutes to read.

​Introduction​
A few days ago, I encountered a case where the Undo space became excessively large (the general scenario is detailed at the end of the article). To facilitate subsequent troubleshooting, I systematically reviewed the relevant knowledge about Undo tablespaces, hoping to help fellow readers.

​Undo Mechanism​
Ensures transaction atomicity and provides data rollback. When the database crashes or a transaction is rolled back, the InnoDB transaction system can use the Undo Log for data rollback. Multi-Version Concurrency Control (MVCC) - Isolation. In the InnoDB storage engine, MVCC is implemented through the Undo Log. When transaction A reads a row of records, if the record is already occupied by another transaction B, transaction A can read the previous version of the row through the Undo Log, thus achieving non-locking reads (if there are long queries, it can lead to historical Undo not being cleaned up in time, causing the Undo Log to expand).

​Undo Tablespace Management Evolution​

• ​Before MySQL 5.6: Undo Tablespace was within ibdata, sharing the system tablespace. A common issue was that large transactions not committing caused ibdata to expand, and after transaction commit, the space could not be reclaimed, leading to significant waste of space or even disk exhaustion, while also increasing the time for physical database backups. The only solution was to rebuild the database.
• ​MySQL 5.6: InnoDB supported setting independent Undo Tablespaces, meaning Undo Logs could be stored outside of ibdata. However, this feature had certain limitations: users had to set the innodb_undo_tablespaces value during instance initialization to achieve independent Undo Tablespaces, and this setting could not be changed after initialization. The default value was 0, indicating no independent Undo Tablespace, with records stored in ibdata. Changing the innodb_undo_tablespaces value would prevent the database from starting. The Space ID of Undo Tablespaces had to start from 1, and it was impossible to add or delete Undo Tablespaces.
• ​MySQL 5.7: Introduced a feature that DBAs would appreciate - online truncate undo tablespace. This feature is controlled by the innodb_undo_log_truncate parameter.
• ​MySQL 8.0: MySQL further optimized Undo Tablespace. In version 8.0, the independent Undo Tablespace feature was enabled by default. Starting from version 8.0.3, the default number of Undo Tablespaces was adjusted from 0 to 2. It supports dynamic online addition and deletion of Undo Tablespaces. The naming of Undo Tablespaces changed from undoNNN to undo_NNN. Before version 8.0, only 128 rollback segments could be created, while starting from version 8.0, each Undo Tablespace could create 128 rollback segments, totaling innodb_rollback_segments * innodb_undo_tablespaces rollback segments. This significantly reduces lock conflicts between transactions allocated to the same rollback segment under high concurrency, thereby improving system parallel performance. The innodb_undo_truncate parameter is enabled by default, and when the Undo Tablespace size exceeds innodb_max_undo_log_size, it triggers Online Truncate. It supports Undo Tablespace encryption.

​Undo Tablespace Maintenance​
We simulate the process of Undo file growth, manual addition of Undo tablespaces, and file operations to learn about Undo tablespace management. The basic idea is: generally, a MySQL instance has 2 Undo Tablespaces. Add a new Undo Tablespace A. Set the old Undo Tablespace B to inactive, and the system automatically reclaims the Undo file space based on innodb_undo_log_truncate = ON. Set the old Undo Tablespace B to active. This process can retain the new Undo Tablespace A or set Undo Tablespace A to inactive and then delete it. Next, we test based on the above idea.

‍

1. View current Undo Tablespaces and 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

The system defaults to allocating 2 innodb-prefixed tablespaces. If you manually add an Undo Tablespace, creating a tablespace with an innodb-prefixed name will result in an error, indicating that the name is reserved. The file must end with .ibu, otherwise, an error will also occur.

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

‍

3. Create Undo Tablespace

Create an Undo Tablespace file undo_003.

mysql > create undo tablespace undo_003 add datafile 'undo_003.ibu';
Query OK, 0 rows affected (0.14 sec)
mysql > SELECT NAME, STATE 
        FROM INFORMATION_SCHEMA.INNODB_TABLESPACES 
        WHERE NAME LIKE '%undo%';

‍

NAME | STATE
innodb_undo_001 | active
innodb_undo_002 | active
undo_003 | active
3 rows in set (0.00 sec)

‍

4. Automatically reclaim Undo file space

Set innodb_undo_002 to inactive, allowing the system to automatically shrink the Undo file.

mysql > ALTER UNDO TABLESPACE innodb_undo_002 SET INACTIVE;
Query OK, 0 rows affected (0.00 sec)
mysql > SELECT NAME, STATE 
        FROM INFORMATION_SCHEMA.INNODB_TABLESPACES 
        WHERE NAME LIKE '%undo%';

‍

NAME | STATE
innodb_undo_001 | active
innodb_undo_002 | empty
undo_003 | active
3 rows in set (0.00 sec)

‍

We cannot delete the system-default innodb-prefixed Undo Tablespace; the system will prompt that the space is reserved.

mysql > DROP UNDO TABLESPACE innodb_undo_002;
ERROR 3119 (42000): InnoDB: Tablespace names starting with `innodb_` are reserved.
mysql > ALTER UNDO TABLESPACE innodb_undo_002 SET ACTIVE;
Query OK, 0 rows affected (0.00 sec)

‍

5. Delete the new Undo Tablespace

mysql > ALTER UNDO TABLESPACE undo_003 SET INACTIVE;
Query OK, 0 rows affected (0.00 sec)
mysql > SELECT NAME, STATE 
        FROM INFORMATION_SCHEMA.INNODB_TABLESPACES 
        WHERE NAME LIKE '%undo%';

‍

NAME | STATE
innodb_undo_001 | active
innodb_undo_002 | active
undo_003 | empty
3 rows in set (0.00 sec)
mysql > DROP UNDO TABLESPACE undo_003;
Query OK, 0 rows affected (0.01 sec)
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.00 sec)

‍

Undo-related Parameters​

• innodb_undo_directory: Directory for storing Undo files.
• innodb_undo_log_truncate: Used to enable/disable the Truncate Undo feature, can be dynamically adjusted. Default is ON in MySQL 8.0.
• innodb_undo_tablespaces: Default is 2, used to set the number of Undo Tablespaces during instance initialization, this parameter can be dynamically adjusted. To achieve online Truncate Undo, this parameter needs to be greater than or equal to 2, because when truncating one Undo Log file, another must be available. Manually maintained, it can be set to 3 or more.
• innodb_purge_rseg_truncate_frequency: Default maximum value 128, used to control the frequency of purging rollback segments. That is, after 128 times, it will trigger an Undo Truncate, and the number of Undo Pages cleaned each time is determined by the innodb_purge_batch_size parameter.
• innodb_purge_batch_size: Default is 300, meaning 300×128 Undo batches are cleaned before triggering an Undo tablespace shrink operation. The smaller this parameter, the higher the frequency of attempting to Truncate the Undo tablespace.
• innodb_max_undo_log_size: Controls the size of the Undo tablespace file, and when it exceeds this threshold, it will attempt to Truncate. The size after Truncate is default to 10M.

​Practical Application​
Theory is shallow without practice. Previously, we introduced the theoretical knowledge of Undo. Now, DBA Xiao Ming encountered a problem: a MySQL production instance (version 8.0) with a primary-secondary architecture, where one secondary database provides read-only services. The Undo file ballooned to about 270G in a day. What ideas do you have to help Xiao Ming solve the Undo tablespace recycling issue?

‍