What Is MySQL Undo Tablespace Truncation?

This article explains the function and mechanism of the MySQL undo tablespace truncation process. You will learn how MySQL manages the size of undo tablespaces by reclaiming disk space occupied by inactive undo logs, why this process is crucial for database performance and storage efficiency, and how the InnoDB storage engine automates this maintenance task.

Understanding Undo Tablespaces

In MySQL’s InnoDB storage engine, undo tablespaces contain undo logs. These logs are collections of records that contain information about how to undo the latest change by a transaction to a clustered index record. Undo logs serve two primary purposes: supporting transaction rollbacks and facilitating Multi-Version Concurrency Control (MVCC) to ensure consistent reads.

Over time, heavy write operations and long-running transactions cause these undo logs to grow. Without a management mechanism, undo tablespaces would continuously expand, consuming large amounts of disk storage that could not be reclaimed without restarting the database or rebuilding the tablespaces.

The Function of Undo Tablespace Truncation

The primary function of the undo tablespace truncation process is to reclaim physical disk space by shrinking the files associated with undo tablespaces.

When truncation is triggered, MySQL performs the following actions:

1. Reclaims Disk Space: It deallocates physical disk space occupied by unused undo logs and returns it to the operating system.
2. Prevents Storage Exhaustion: It prevents the storage volume from filling up due to temporary spikes in transaction volume or long-running queries.
3. Maintains Performance: By keeping undo tablespaces at an optimal size, MySQL ensures efficient memory and I/O utilization during transaction processing.

How the Truncation Process Works

MySQL automates the truncation process using a system of active and inactive states for undo tablespaces. For truncation to occur, a minimum of two undo tablespaces must exist so that one remains active while the other is being truncated.

The truncation workflow follows these steps:

1. Reaching the Threshold

MySQL monitors the size of the undo tablespaces. When an undo tablespace exceeds the size configured by the innodb_max_undo_log_size system variable, it becomes a candidate for truncation.

2. Deactivation

The purge coordinator thread marks the target undo tablespace as inactive. Once marked inactive, no new transactions are assigned to it. Any active transactions currently using the tablespace are allowed to finish.

3. Purging Existing Data

The database purge threads continue to process and release old undo log pages within the inactive tablespace. The tablespace cannot be truncated until all transactions referencing its undo logs have committed and been purged.

4. Truncation and Reactivation

Once the tablespace is entirely free of active transactions and history, MySQL physically truncates the tablespace file on disk, resetting it to its default initial size. After truncation is complete, the tablespace is marked as active again and made available to accept new transaction logs.

Key Configuration Variables

The truncation process is governed by a few key MySQL configuration parameters:

• innodb_undo_log_truncate: Enables or disables the automatic truncation of undo tablespaces.
• innodb_max_undo_log_size: Defines the threshold size (default is 1GB) at which an undo tablespace is targeted for truncation.
• innodb_purge_rseg_truncate_frequency: Controls the frequency with which the purge thread checks if undo tablespaces need truncation, defined by the number of times the purge thread is invoked.