MySQL :: MySQL 8.0 Reference Manual :: 15.7.8 Truncating Undo Logs That Reside in Undo Tablespaces

5.7 current

MySQL 8.0 Reference Manual / ... / Truncating Undo Logs That Reside in Undo Tablespaces

Pre-General Availability Draft: 2017-07-17

15.7.8 Truncating Undo Logs That Reside in Undo Tablespaces

To truncate undo logs that reside in undo tablespaces, the MySQL instance must be configured with a minimum of two undo tablespaces. A minimum of two undo tablespaces ensures that one undo tablespace remains active while the other is taken offline to be truncated. The number of undo tablespaces is defined by the innodb_undo_tablespaces option. The default value is 2 as of MySQL 8.0.2, and 0 before that. Use this statement to check the value of innodb_undo_tablespaces:

mysql> SELECT @@innodb_undo_tablespaces;
+---------------------------+
| @@innodb_undo_tablespaces |
+---------------------------+
|                         2 |
+---------------------------+

For information about configuring undo tablespaces, see Section 15.7.7, “Storing InnoDB Undo Logs in Separate Tablespaces”.

Enabling Truncation of Undo Tablespaces

To truncate undo logs that reside in undo tablespaces, enable innodb_undo_log_truncate.

mysql> SET GLOBAL innodb_undo_log_truncate=ON;

When innodb_undo_log_truncate is enabled, undo tablespace files that exceed the size limit defined by innodb_max_undo_log_size are marked for truncation. innodb_max_undo_log_size is a dynamic global variable with a default value of 1024 MiB (1073741824 bytes).

mysql> SELECT @@innodb_max_undo_log_size;
+----------------------------+
| @@innodb_max_undo_log_size |
+----------------------------+
|                 1073741824 |
+----------------------------+

You can configure innodb_max_undo_log_size using a SET GLOBAL statement:

mysql> SET GLOBAL innodb_max_undo_log_size=2147483648;

When innodb_undo_log_truncate is enabled:

Undo tablespaces that exceed the innodb_max_undo_log_size setting are marked for truncation. Selection of an undo tablespace for truncation is performed in a circular fashion to avoid truncating the same undo tablespace each time.
Rollback segments residing in the selected undo tablespace are made inactive so that they are not assigned to new transactions. Existing transactions that are currently using rollback segments are allowed to complete.
The purge system frees rollback segments that are no longer needed.
After all rollback segments in the undo tablespace are freed, the truncate operation runs and the undo tablespace is truncated to its initial size. The initial size of an undo tablespace file is 10MiB.
Note

The size of an undo tablespace after a truncate operation may be larger than 10MiB due to immediate use following the completion of the operation. The innodb_undo_directory option defines the location of undo tablespace files. The default value of “.” represents the directory where InnoDB creates other log files by default.
```
mysql> SELECT @@innodb_undo_directory;
+-------------------------+
| @@innodb_undo_directory |
+-------------------------+
| .                       |
+-------------------------+
```
The rollback segments are reactivated so that they can be assigned to new transactions.

Expediting Truncation of Undo Tablespace Files

An undo tablespace cannot be truncated until its rollback segments are freed. Normally, the purge system frees rollback segments once every 128 times that purge is invoked. To expedite the truncation of undo tablespaces, use the innodb_purge_rseg_truncate_frequency option to temporarily increase the frequency with which the purge system frees rollback segments. The default innodb_purge_rseg_truncate_frequency setting is 128, which is also the maximum value.

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

To increase the frequency with which the purge thread frees rollback segments, decrease the value of innodb_purge_rseg_truncate_frequency. For example:

mysql> SET GLOBAL innodb_purge_rseg_truncate_frequency=32;

Performance Impact of Truncating Undo Tablespace Files Online

While an undo tablespace is truncated, rollback segments in that tablespace are temporarily deactivated. The remaining active rollback segments in the other undo tablespaces assume responsibility for the entire system load, which may result in a slight performance degradation. The degree of performance degradation depends on a number of factors including:

Number of undo tablespaces
Number of undo logs
Undo tablespace size
Speed of the I/O susbsystem
Existing long running transactions
System load

PREV HOME UP NEXT

User Comments