Undo Tablespaces can be truncated either implicitly or explicitly in MySQL 8.0. Both methods use the same mechanism. This mechanism could cause periodic stalls on very busy systems while an undo tablespace truncate completes. This problem has been fixed in MySQL 8.0.21.
First, let’s understand the two methods that you can use to keep your undo tablespaces from growing too large.
Implicit Truncation
The implicit method is ON by default in MySQL 8.0. It uses the settings innodb_undo_log_truncate (default = ON) and innodb_max_undo_log_size (default = 1GB). With these settings, if an undo tablespace grows to be larger than 1 GB, it will be taken offline by the background purge thread in InnoDB. When all transactions associated with undo logs in the undo tablespace have completed, and all undo logs in that space are no longer needed to secure data integrity, the tablespace is ready to be truncated.
The tablespace is then deleted and a new undo tablespace is created to replace it. Once the new tablespace is ready, it is made active again.
Explicit Truncation
You can explicitly activate the truncation process on an undo tablespace whenever you think that the tablespace has grown too large on disk by issuing:
ALTER UNDO TABLESPACE tablespace_name SET INACTIVE;
You can then monitor whether the process is still waiting for undo logs to become unnecessary or if the tablespace has been completely truncated with this statement:
SELECT NAME, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
WHERE NAME = ‘tablespace_name’;
Please see this page for documentation on Undo Tablespaces in Mysql 8.0 and here for a BLOG about Undo Truncation from when the feature was introduced into MySQL 5.7.
Improvements In 8.0.21
On a very busy system, we noticed that the actual truncation can cause a performance degradation because it flushes all the pages from the undo tablespace out of the buffer pool. So, in MySQL 8.0.21, InnoDB now leaves those pages in the buffer pool after the associated undo datafile has been deleted. InnoDB knows that these pages are for tablespace IDs that have been deleted. The pages are released passively as they become least recently used. Then, at the next full checkpoint, the remaining buffers are released to be used again. This internal buffer pool management feature allows the undo tablespace truncation to be almost instantaneous.
The other part of the improvement is that new undo tablespaces are fully redo logged, which means that the initial 129 pages of an undo tablespace do not have to be flushed to disk as part of a truncate operation.
These improvements alleviate periodic stalls on extremely busy severs that our QA group encountered from time to time when undo truncation is active.
InnoDB uses a range of 512 unique tablespace IDs for a single undo tablespace. Each time an undo tablespace is truncated, it gets a new ID, assigned on a round robin basis. If things go haywire, such as when innodb_max_undo_log_size is too small on a busy system, or when the same undo tablespace is repeatedly truncated explicitly in a loop, we found that between two checkpoints, an undo tablespace could be truncated more than 512 times. If this happens, there may be pages in the buffer pool or changes in the redo log for more that 512 different incarnations of the same undo tablespace. In stress tests, this led to a failed assert in InnoDB. If our QA group can make a mess of things they certainly will. So to prevent this, InnoDB no longer permits more than 64 truncations of the same undo tablespace to occur between two checkpoints. The undo tablespace may be made inactive, but it will not be truncated again until the next checkpoint finishes.
This little performance improvement is another way in which InnoDB just keeps getting better and better.