We have received a lot of requests from our user community regarding the ability to  truncate UNDO tablespaces (‘truncate’ here means that the size of the tablespace is reset back to what it was when it was first created). We are happy to say that we’ve now been able to implement this anticipated feature.

Introduction

The InnoDB UNDO tablespace(s) host rollback segments that hold rollback information related to database changes. This information is used to rollback a transaction and to retrieve the previous version of a record that has been updated or deleted for multi-version concurrency control (MVCC). Once a transaction is committed, InnoDB will discard the related UNDO log records. UNDO log records for updates or deletes will be kept around as long as there exists an open transaction that may access older versions of the records. When all such open transactions are committed then the associated UNDO log records can be discarded.

Currently the InnoDB purge thread(s) will try to free UNDO log pages containing old UNDO log records so that those pages can be re-used. But depending on the retention window (dictated by active transactions that need those UNDO log records) the UNDO tablespace can continue to grow in size, continuously allocating new pages. Because of that, with multiple active long running transactions the total UNDO tablespace size could continue to increase and grow considerably over time.

In order to re-claim the space and help reduce the disk foot-print of an UNDO tablespace that has grown much larger than is necessary, we have introduced a new option to truncate an UNDO tablespace.

Turning This Feature “ON”

Whether or not to support truncation of UNDO tablespaces is controlled using the innodb_undo_log_truncate option, which is turned “OFF” by default. Users can turn that “ON” and “OFF” dynamically, so as to support the feature on an as needed basis.

There are a few important semantics that affect when the actual truncate operation can run:

• Truncate will happen only if UNDO logs are configured to use UNDO tablespaces. It will not be possible to truncate UNDO logs space that is stored within the system tablespace.

Condition: --innodb-undo-tablespaces > 0

• Since the truncate is being done online we don’t want to halt server operations, so for truncate to progress we need to have at least 2 UNDO tablespaces and at least 2 REDO enabled UNDO logs configured to use these UNDO tablespaces.

Condition: --innodb-undo-tablespaces >= 2 and --innodb-undo-logs >= 35

(The value of 35 comes from how the UNDO logs (rollback segments or rsegs) are allocated—0: REDO enabled rseg allocated in the system tablespace, 1-32: non-REDO enabled rsegs allocated in the temporary tablespace, 33-n: REDO enabled rsegs allocated in UNDO tablespaces)

How It Works

Provided all the above mentioned conditions are met, then InnoDB will try to do the following:

1. Select/Mark an UNDO tablespace for truncate. This is done in a round-robin fashion to avoid bias selection.
2. Make all the rollback segments (rsegs) residing in the selected UNDO tablespace inactive. Inactive means that these rollback segments will not be allocated to new transactions. Existing transactions that are using these rollback segments will continue to progress without being affected.
3. The purge system will continue to free rollback segments that are no longer needed. This marks the pages allocated to rollback segments as being free and reduces the logical size of the rollback segments.
4. Once all rollback segments residing within UNDO tablespaces are freed, an actual file system truncate operation is carried out on the marked UNDO tablespace. It is then re-initialized to the default size that was set when it was created new.
5. The truncated rollback segments are made active again so that they can be allocated to new transactions.

Accelerating the Freeing of Rollback Segments

As mentioned previously, the purge thread will try to free rollback segments that are no longer needed. Truncation of UNDO tablespaces cannot proceed until it has freed all of the rollback segments.

Previously the purge thread used to invoke the truncation of rollback segments every 128th time a purge was run. This was done to preserve performance, since the truncation activity can be resource intensive.

This static value of 128 is now replaced with the innodb_purge_rseg_truncate_frequency option, giving users more flexibility to control how frequently the freeing of rollback segments should occur.

innodb_purge_rseg_truncate_frequency: Default: 128, Min: 1, Max: 128

For example:
mysql> SET GLOBAL innodb_purge_rseg_truncate_frequency = 64;

Users can continue to use the default setting of 128 during normal operations to maintain the overall performance. This freeing/truncation operations can be accelerated as needed by reducing this variable and thus increasing the frequency.

Minimum Size of UNDO Tablespace to Truncate

We don’t want to truncate each and every tablespace. Neither should this operation result in continuous action such that one after another tablespace is qualified for truncate.

To address this issue there is some minimum size of UNDO tablespace criterion that needs to be met (dictated by innodb_max_undo_logs_size)

innodb_max_undo_logs_size: Threshold after which UNDO tablespace will qualify for truncate.  Default: 1 GB, Min: 10 MB, Max: ULONGLONG_MAX

For example:
mysql> SET GLOBAL innodb_max_undo_logs_size = 100M;

Only if the UNDO tablespace size exceeds the configured threshold will it be considered for truncation. Users should tune the value according to their operating environment and needs.

Performance Impacts

With the feature turned “OFF” there should not be any performance impact whatsoever. With the feature turned “ON”, however, there can be some performance impact. Given that the truncation operations are being done online while the server continues to accept new requests, there can be a performance impact on the user transactions. Let’s understand where the potential impact comes from.

As stated above, rollback segments residing in UNDO tablespaces are marked for truncation and are made inactive. This of course then limits the related available UNDO log management resources.

For example, if you have 2 UNDO tablespaces and 128 UNDO logs, and 95 of these are redo rsegs residing in those 2 UNDO tablespaces, then taking 1 of the UNDO tablespaces “offline” in order to truncate it means that we’re effectively making 48 UNDO logs unavailable for transaction processing, thus reducing the UNDO log processing resources by half. This will of course have some impact on the transaction processing rate (TPS) while the truncation operation runs.

The exact performance impact depends on a number of factors:

• The number of UNDO tablespaces
• The number of UNDO logs
• The size of the UNDO tablespace
• The speed of the I/O subsystem
• Whether or not any long running transactions exist
• Whether or not the system is otherwise heavily loaded

Based on our internal testing we found that — with innodb_undo_tablespaces=8, innnodb_undo_logs=128, and innodb_max_undo_logs_size=100M — at 128 threads the sysbench OLTP_RW workload drop in TPS is less than 5% on a server with an average IO subsystem.

Once the truncate operation completes and all of the rollback segments are made active again the performance will naturally improve again as more resources become available. The performance should in fact be slightly improved compared to before the truncation operation because UNDO log record allocation should be faster as the UNDO tablespace is smaller and less fragmented.

Summary

We now have a server option that allows you to reclaim some of the disk space used for UNDO tablespace management dynamically, without having to stop mysqld. This feature can be turned “ON” and “OFF” dynamically and on an as needed basis, for example when the overall server load is expected to be light (e.g. at 4AM every Saturday).

The related configuration options are:

1. To enable the general feature: --innodb-undo-log-truncate
2. To configure undo-tablespaces: --innodb-undo-tablespaces
3. To configure undo-logs: --innodb-undo-logs
4. To accelerate rsegs free: --innodb_purge_rseg_truncate_frequency
5. To set size threshold: --innodb_max_undo_logs_size

We look forward to your feedback on this new feature! Please let us know if you encounter any seeming bugs, or if you have more general feedback. Thank you!