You can manage the size of an UNDO
tablespace through the dynamic variable innodb_max_undo_log_size
, which is 1 GB by default, and the number of UNDO
tablespaces through innodb_undo_tablespaces
, which is 2 GB by default and dynamic from MySQL 8.0.2.
By default, innodb_undo_log_truncate
is enabled. Tablespaces that exceed the threshold value defined by innodb_max_undo_log_size
are marked for truncation. Only undo tablespaces can be truncated. Truncating undo logs that reside in the system tablespace is not supported. For truncation to occur, there must be at least two undo tablespaces.
Verify the size of the UNDO
logs:
shell> sudo ls -lhtr /var/lib/mysql/undo_00* -rw-r-----. 1 mysql mysql 19M Oct 7 17:43 /var/lib/mysql/undo_002 -rw-r-----. 1 mysql mysql 16M Oct 7 17:43 /var/lib/mysql/undo_001
Suppose you want to reduce the files that are more than 15 MB. Remember, only one undo tablespace can be truncated. Selection of an undo tablespace for truncation...