Mysql：UNDO Tablespaces && UNDO Logs

15.6.3.4 Undo Tablespaces

Undo tablespaces contain undo logs, which are collections of undo log records that contain information about how to undo the latest change by a transaction to a clustered index record. Undo logs exist within undo log segments, which are contained within rollback segments. The innodb_rollback_segments variable defines the number of rollback segments allocated to each undo tablespace.

Two default undo tablespaces are created when the MySQL instance is initialized. Default undo tablespaces are created at initialization time to provide a location for rollback segments that must exist before SQL statements can be accepted. A minimum of two undo tablespaces is required to support automated truncation of undo tablespaces. See Truncating Undo Tablespaces.

Default undo tablespaces are created in the location defined by the innodb_undo_directory variable. If the innodb_undo_directory variable is undefined, default undo tablespaces are created in the data directory. Default undo tablespace data files are named undo_001 and undo_002. The corresponding undo tablespace names defined in the data dictionary are innodb_undo_001 and innodb_undo_002.

As of MySQL 8.0.14, additional undo tablespaces can be created at runtime using SQL. See Adding Undo Tablespaces.

The initial size of an undo tablespace data file depends on the innodb_page_size value. For the default 16KB page size, the initial undo tablespace file size is 10MiB. For 4KB, 8KB, 32KB, and 64KB page sizes, the initial undo tablespace files sizes are 7MiB, 8MiB, 20MiB, and 40MiB, respectively.

Adding Undo Tablespaces

Because undo logs can become large during long-running transactions, creating additional undo tablespaces can help prevent individual undo tablespaces from becoming too large. As of MySQL 8.0.14, additional undo tablespaces can be created at runtime using CREATE UNDO TABLESPACE syntax.

CREATE UNDO TABLESPACE tablespace_name ADD DATAFILE 'file_name.ibu';

The undo tablespace file name must have an .ibu extension. It is not permitted to specify a relative path when defining the undo tablespace file name. A fully qualified path is permitted, but the path must be known to InnoDB. Known paths are those defined by the innodb_directories variable. Unique undo tablespace file names are recommended to avoid potential file name conflicts when moving or cloning data.

At startup, directories defined by the innodb_directories variable are scanned for undo tablespace files. (The scan also traverses subdirectories.) Directories defined by the innodb_data_home_dir, innodb_undo_directory, and datadir variables are automatically appended to the innodb_directories value, regardless of whether the innodb_directories variable is defined explicitly. An undo tablespace can therefore reside in paths defined by any of those variables.

If the undo tablespace file name does not include a path, the undo tablespace is created in the directory defined by the innodb_undo_directory variable. If that variable is undefined, the undo tablespace is created in the data directory.

Note

The InnoDB recovery process requires that undo tablespace files reside in known directories. Undo tablespace files must be discovered and opened before redo recovery and before other data files are opened to permit uncommitted transactions and data dictionary changes to be rolled back. An undo tablespace not found before recovery cannot be used, which can cause database inconsistencies. An error message is reported at startup if an undo tablespace known to the data dictionary is not found. The known directory requirement also supports undo tablespace portability. See Moving Undo Tablespaces.

To create undo tablespaces in a path relative to the data directory, set the innodb_undo_directory variable to the relative path, and specify the file name only when creating an undo tablespace.

To view undo tablespace names and paths, query INFORMATION_SCHEMA.FILES:

SELECT TABLESPACE_NAME, FILE_NAME FROM INFORMATION_SCHEMA.FILES 
  WHERE FILE_TYPE LIKE 'UNDO LOG';

A MySQL instance supports up to 127 undo tablespaces including the two default undo tablespaces created when the MySQL instance is initialized.

Note

Prior to MySQL 8.0.14, additional undo tablespaces are created by configuring the innodb_undo_tablespaces startup variable. This variable is deprecated and no longer configurable as of MySQL 8.0.14.

Prior to MySQL 8.0.14, increasing the innodb_undo_tablespaces setting creates the specified number of undo tablespaces and adds them to the list of active undo tablespaces. Decreasing the innodb_undo_tablespaces setting removes undo tablespaces from the list of active undo tablespaces. Undo tablespaces that are removed from the active list remain active until they are no longer used by existing transactions. The innodb_undo_tablespaces variable can be configured at runtime using a SET statement or defined in a configuration file.

Prior to MySQL 8.0.14, deactivated undo tablespaces cannot be removed. Manual removal of undo tablespace files is possible after a slow shutdown but is not recommended, as deactivated undo tablespaces may contain active undo logs for some time after the server is restarted if open transactions were present when shutting down the server. As of MySQL 8.0.14, undo tablespaces can be dropped using DROP UNDO TABALESPACE syntax. See Dropping Undo Tablespaces.

Dropping Undo Tablespaces

As of MySQL 8.0.14, undo tablespaces created using CREATE UNDO TABLESPACE syntax can be dropped at runtime using DROP UNDO TABALESPACE syntax.

An undo tablespace must be empty before it can be dropped. To empty an undo tablespace, the undo tablespace must first be marked as inactive using ALTER UNDO TABLESPACE syntax so that the tablespace is no longer used for assigning rollback segments to new transactions.

ALTER UNDO TABLESPACE tablespace_name SET INACTIVE;

After an undo tablespace is marked as inactive, transactions currently using rollback segments in the undo tablespace are permitted to finish, as are any transactions started before those transactions are completed. After transactions are completed, the purge system frees the rollback segments in the undo tablespace, and the undo tablespace is truncated to its initial size. (The same process is used when truncating undo tablespaces. See Truncating Undo Tablespaces.) When the undo tablespace is empty, it can be dropped.

DROP UNDO TABLESPACE tablespace_name;

Note

Alternatively, the undo tablespace can be left in an empty state and reactivated later, when needed, by issuing an ALTER UNDO TABLESPACE tablespace_name SET ACTIVE statement.

The state of an undo tablespace can be monitored by querying the INFORMATION_SCHEMA.INNODB_TABLESPACES table.

SELECT NAME, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES 
  WHERE NAME LIKE tablespace_name;

An inactive state indicates that rollback segments in an undo tablespace are no longer used by new transactions. An empty state indicates that an undo tablespace is empty and ready to be dropped, or made active again using an ALTER UNDO TABLESPACE tablespace_name SET ACTIVE statement. Attempting to drop an undo tablespace that is not empty returns an error.

The default undo tablespaces (innodb_undo_001 and innodb_undo_002) created when the MySQL instance is initialized cannot be dropped. They can, however, be made inactive using an ALTER UNDO TABLESPACE tablespace_name SET INACTIVE statement. Before a default undo tablespace can be made inactive, there must be an undo tablespace to take its place. A minimum of two active undo tablespaces are required at all times to support automated truncation of undo tablespaces.

Moving Undo Tablespaces

Undo tablespaces created with CREATE UNDO TABLESPACE syntax can be moved while the server is offline to any known directory. Known directories are those defined by the innodb_directories variable. Directories defined by innodb_data_home_dir, innodb_undo_directory, and datadir are automatically appended to the innodb_directories value regardless of whether the innodb_directories variable is defined explicitly. Those directories and their subdirectories are scanned at startup for undo tablespaces files. An undo tablespace file moved to any of those directories is discovered at startup and assumed to be the undo tablespace that was moved.

The default undo tablespaces (innodb_undo_001 and innodb_undo_002) created when the MySQL instance is initialized must always reside in the directory defined by the innodb_undo_directory variable. If the innodb_undo_directory variable is undefined, default undo tablespaces reside in the data directory. If default undo tablespaces are moved while the server is offline, the server must be started with the innodb_undo_directory variable configured to the new directory.

The I/O patterns for undo logs make undo tablespaces good candidates for SSD storage.

Configuring the Number of Rollback Segments

The innodb_rollback_segments variable defines the number of rollback segments allocated to each undo tablespace and to the global temporary tablespace. The innodb_rollback_segments variable can be configured at startup or while the server is running.

The default setting for innodb_rollback_segments is 128, which is also the maximum value. For information about the number of transactions that a rollback segment supports, see Section 15.6.6, “Undo Logs”.

Truncating Undo Tablespaces

There are two methods of truncating undo tablespaces, which can be used individually or in combination to manage undo tablespace size. One method is automated, enabled using configuration variables. The other method is manual, performed using SQL statements.

The automated method does not require monitoring undo tablespace size and, once enabled, it performs deactivation, truncation, and reactivation of undo tablespaces without manual intervention. The manual truncation method may be preferable if you want to control when undo tablespaces are taken offline for truncation. For example, you may want to avoid truncating undo tablespaces during peak workload times.

Automated Truncation

Automated truncation of undo tablespaces requires a minimum of two active undo tablespaces, which ensures that one undo tablespace remains active while the other is taken offline to be truncated. By default, two undo tablespaces are created when the MySQL instance is initialized.

To have undo tablespaces automatically truncated, enable the innodb_undo_log_truncate variable. For example:

mysql> SET GLOBAL innodb_undo_log_truncate=ON;

When the innodb_undo_log_truncate variable is enabled, undo tablespaces that exceed the size limit defined by the innodb_max_undo_log_size variable are subject to truncation. The innodb_max_undo_log_size variable is dynamic and has a default value of 1073741824 bytes (1024 MiB).

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

When the innodb_undo_log_truncate variable is enabled:

Default and user-defined 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 permitted to finish.
The purge system frees rollback segments that are no longer in use.
After all rollback segments in the undo tablespace are freed, the truncate operation runs and truncates the undo tablespace to its initial size. The initial size of an undo tablespace depends on the innodb_page_size value. For the default 16KB page size, the initial undo tablespace file size is 10MiB. For 4KB, 8KB, 32KB, and 64KB page sizes, the initial undo tablespace files sizes are 7MiB, 8MiB, 20MiB, and 40MiB, respectively.

The size of an undo tablespace after a truncate operation may be larger than the initial size due to immediate use following the completion of the operation.

The innodb_undo_directory variable defines the location of default undo tablespace files. If the innodb_undo_directory variable is undefined, default undo tablespaces reside in the data directory. The location of all undo tablespace files including user-defined undo tablespaces created using CREATE UNDO TABLESPACE syntax can be determined by querying the INFORMATION_SCHEMA.FILES table:
```
SELECT TABLESPACE_NAME, FILE_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE LIKE 'UNDO LOG';
```
Rollback segments are reactivated so that they can be assigned to new transactions.

Manual Truncation

Manual truncation of undo tablespaces requires a minimum of three active undo tablespaces. Two active undo tablespaces are required at all times to support the possibility that automated truncation is enabled. A minimum of three undo tablespaces satisfies this requirement while permitting an undo tablespace to be taken offline manually.

To manually initiate truncation of an undo tablespace, deactivate the undo tablespace by issuing the following statement:

ALTER UNDO TABLESPACE tablespace_name SET INACTIVE;

After the undo tablespace is marked as inactive, transactions currently using rollback segments in the undo tablespace are permitted to finish, as are any transactions started before those transactions are completed. After transactions are completed, the purge system frees the rollback segments in the undo tablespace, the undo tablespace is truncated to its initial size, and the undo tablespace state changes from inactive to empty.

Note

When an ALTER UNDO TABLESPACE tablespace_name SET INACTIVE statement deactivates an undo tablespace, the purge thread looks for that undo tablespaces at the next opportunity. Once the undo tablespace is found and marked for truncation, the purge thread returns with increased frequency to quickly empty and truncate the undo tablespace.

To check the state of an undo tablespace, query the INFORMATION_SCHEMA.INNODB_TABLESPACES table.

SELECT NAME, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES 
  WHERE NAME LIKE tablespace_name;

Once the undo tablespace is in an empty state, it can be reactivated by issuing the following statement:

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 that frequency, decrease the innodb_purge_rseg_truncate_frequency setting. For example, to have the purge thread look for undo tabespaces once every 32 timees that purge is invoked, set innodb_purge_rseg_truncate_frequency to 32.

mysql> SET GLOBAL innodb_purge_rseg_truncate_frequency=32;

When the purge thread finds an undo tablespace that requires truncation, the purge thread returns with increased frequency to quickly empty and truncate the undo tablespace.

Performance Impact of Truncating Undo Tablespace Files

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 amount of performance degradation 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 impacting performance when truncating undo tablespaces 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 Status Variables

The following status variables permit tracking the total number of undo tablespaces, implicit (InnoDB-created) undo tablespaces, explicit (user-created) undo tablespaces, and the number of active undo tablespaces:

mysql> SHOW STATUS LIKE 'Innodb_undo_tablespaces%';
+----------------------------------+-------+
| Variable_name                    | Value |
+----------------------------------+-------+
| Innodb_undo_tablespaces_total    | 2     |
| Innodb_undo_tablespaces_implicit | 2     |
| Innodb_undo_tablespaces_explicit | 0     |
| Innodb_undo_tablespaces_active   | 2     |
+----------------------------------+-------+

For status variable descriptions, see Section 5.1.10, “Server Status Variables”.

15.6.6 Undo Logs

An undo log is a collection of undo log records associated with a single read-write transaction. An undo log record contains information about how to undo the latest change by a transaction to a clustered index record. If another transaction needs to see the original data as part of a consistent read operation, the unmodified data is retrieved from undo log records. Undo logs exist within undo log segments, which are contained within rollback segments. Rollback segments reside in undo tablespaces and in the global temporary tablespace.

Undo logs that reside in the global temporary tablespace are used for transactions that modify data in user-defined temporary tables. These undo logs are not redo-logged, as they are not required for crash recovery. They are used only for rollback while the server is running. This type of undo log benefits performance by avoiding redo logging I/O.

For information about data-at-rest encryption for undo logs, see Undo Log Encryption.

Each undo tablespace and the global temporary tablespace individually support a maximum of 128 rollback segments. The innodb_rollback_segments variable defines the number of rollback segments.

The number of transactions that a rollback segment supports depends on the number of undo slots in the rollback segment and the number of undo logs required by each transaction.

The number of undo slots in a rollback segment differs according to InnoDB page size.

InnoDB Page Size	Number of Undo Slots in a Rollback Segment (InnoDB Page Size / 16)
`4096 (4KB)`	`256`
`8192 (8KB)`	`512`
`16384 (16KB)`	`1024`
`32768 (32KB)`	`2048`
`65536 (64KB)`	`4096`

A transaction is assigned up to four undo logs, one for each of the following operation types:

INSERT operations on user-defined tables
UPDATE and DELETE operations on user-defined tables
INSERT operations on user-defined temporary tables
UPDATE and DELETE operations on user-defined temporary tables

Undo logs are assigned as needed. For example, a transaction that performs INSERT, UPDATE, and DELETE operations on regular and temporary tables requires a full assignment of four undo logs. A transaction that performs only INSERT operations on regular tables requires a single undo log.

A transaction that performs operations on regular tables is assigned undo logs from an assigned undo tablespace rollback segment. A transaction that performs operations on temporary tables is assigned undo logs from an assigned global temporary tablespace rollback segment.

An undo log assigned to a transaction remains tied to the transaction for its duration. For example, an undo log assigned to a transaction for an INSERT operation on a regular table is used for all INSERT operations on regular tables performed by that transaction.

Given the factors described above, the following formulas can be used to estimate the number of concurrent read-write transactions that InnoDB is capable of supporting.

Note

A transaction can encounter a concurrent transaction limit error before reaching the number of concurrent read-write transactions that InnoDB is capable of supporting. This occurs when a rollback segment assigned to a transaction runs out of undo slots. In such cases, try rerunning the transaction.

When transactions perform operations on temporary tables, the number of concurrent read-write transactions that InnoDB is capable of supporting is constrained by the number of rollback segments allocated to the global temporary tablespace, which is 128 by default.

If each transaction performs either an INSERT or an UPDATE or DELETE operation, the number of concurrent read-write transactions that InnoDB is capable of supporting is:
```
(innodb_page_size / 16) * innodb_rollback_segments * number of undo tablespaces
```
If each transaction performs an INSERT and an UPDATE or DELETE operation, the number of concurrent read-write transactions that InnoDB is capable of supporting is:
```
(innodb_page_size / 16 / 2) * innodb_rollback_segments * number of undo tablespaces
```
If each transaction performs an INSERT operation on a temporary table, the number of concurrent read-write transactions that InnoDB is capable of supporting is:
```
(innodb_page_size / 16) * innodb_rollback_segments 
```
If each transaction performs an INSERT and an UPDATE or DELETE operation on a temporary table, the number of concurrent read-write transactions that InnoDB is capable of supporting is:
```
(innodb_page_size / 16 / 2) * innodb_rollback_segments 
```