ALTER UNDO TABLESPACE tablespace_name  SET ACTIVE; 

An undo tablespace in an empty  state can also be dropped. See Dropping Undo Tablespaces. 

## Expediting Automated Truncation of Undo Tablespaces 

The purge thread is responsible for emptying and truncating undo tablespaces. By default, the purge thread looks for undo tablespaces to truncate once every 128 times that purge is invoked. The frequency with which the purge thread looks for undo tablespaces to truncate is controlled by the innodb_purge_rseg_truncate_frequency  variable, which has a default setting of 128. 

mysql> SELECT @@innodb_purge_rseg_truncate_frequency; +----------------------------------------+ | @@innodb_purge_rseg_truncate_frequency | +----------------------------------------+ | 128 | +----------------------------------------+ 

To increase the frequency, decrease the innodb_purge_rseg_truncate_frequency  setting. For example, to have the purge thread look for undo tabespaces once every 32 times that purge is invoked, set innodb_purge_rseg_truncate_frequency  to 32. 

## Performance Impact of Truncating Undo Tablespace Files 

mysql> SET GLOBAL innodb_purge_rseg_truncate_frequency=32; 

When an undo tablespace is truncated, the rollback segments in the undo tablespace are deactivated. The active rollback segments in other undo tablespaces assume responsibility for the entire system load, which may result in a slight performance degradation. The extent to which performance is affected depends on a number of factors: 

•Number of undo tablespaces 

•Number of undo logs 

•Undo tablespace size 

•Speed of the I/O susbsystem 

•Existing long running transactions 

•System load 

The easiest way to avoid the potential performance impact is to increase the number of undo tablespaces. 

## Monitoring Undo Tablespace Truncation 

As of MySQL 8.0.16, undo  and purge  susbsystem counters are provided for monitoring background activities associated with undo log truncation. For counter names and descriptions, query the INFORMATION_SCHEMA.INNODB_METRICS  table. 

SELECT NAME, SUBSYSTEM, COMMENT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME LIKE ’%truncate%’; 

For information about enabling counters and querying counter data, see Section 15.15.6, “InnoDB INFORMATION_SCHEMA Metrics Table” . 

## Undo Tablespace Truncation Limit 

As of MySQL 8.0.21, the number of truncate operations on the same undo tablespace between checkpoints is limited to 64. The limit prevents potential issues caused by an excessive number of undo 

