Movatterモバイル変換

• Default Undo Tablespaces

• Undo Tablespace Size

• Adding Undo Tablespaces

• Dropping Undo Tablespaces

• Moving Undo Tablespaces

• Configuring the Number of Rollback Segments

• Truncating Undo Tablespaces

• Undo Tablespace Status Variables

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. SeeTruncating Undo Tablespaces.

Default undo tablespaces are created in the location defined by theinnodb_undo_directory variable. If theinnodb_undo_directory variable is undefined, default undo tablespaces are created in the data directory. Default undo tablespace data files are namedundo_001 andundo_002. The corresponding undo tablespace names defined in the data dictionary areinnodb_undo_001 andinnodb_undo_002.

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

Prior to MySQL 8.0.23, the initial size of an undo tablespace depends on theinnodb_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. As of MySQL 8.0.23, the initial undo tablespace size is normally 16MiB. The initial size may differ when a new undo tablespace is created by a truncate operation. In this case, if the file extension size is larger than 16MB, and the previous file extension occurred within the last second, the new undo tablespace is created at a quarter of the size defined by theinnodb_max_undo_log_size variable.

Prior to MySQL 8.0.23, an undo tablespace is extended four extents at a time. From MySQL 8.0.23, an undo tablespace is extended by a minimum of 16MB. To handle aggressive growth, the file extension size is doubled if the previous file extension happened less than 0.1 seconds earlier. Doubling of the extension size can occur multiple times to a maximum of 256MB. If the previous file extension occurred more than 0.1 seconds earlier, the extension size is reduced by half, which can also occur multiple times, to a minimum of 16MB. If theAUTOEXTEND_SIZE option is defined for an undo tablespace, it is extended by the greater of theAUTOEXTEND_SIZE setting and the extension size determined by the logic described above. For information about theAUTOEXTEND_SIZE option, seeSection 17.6.3.9, “Tablespace AUTOEXTEND_SIZE Configuration”.

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 usingCREATE UNDO TABLESPACE syntax.

CREATE UNDO TABLESPACEtablespace_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 toInnoDB. Known paths are those defined by theinnodb_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 theinnodb_directories variable are scanned for undo tablespace files. (The scan also traverses subdirectories.) Directories defined by theinnodb_data_home_dir,innodb_undo_directory, anddatadir variables are automatically appended to theinnodb_directories value regardless of whether theinnodb_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 theinnodb_undo_directory variable. If that variable is undefined, the undo tablespace is created in the data directory.

To create undo tablespaces in a path relative to the data directory, set theinnodb_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, queryINFORMATION_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.

As of MySQL 8.0.14, undo tablespaces created usingCREATE UNDO TABLESPACE syntax can be dropped at runtime usingDROP 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 usingALTER UNDO TABLESPACE syntax so that the tablespace is no longer used for assigning rollback segments to new transactions.

ALTER UNDO TABLESPACEtablespace_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. SeeTruncating Undo Tablespaces.) Once the undo tablespace is empty, it can be dropped.

DROP UNDO TABLESPACEtablespace_name;

The state of an undo tablespace can be monitored by querying the Information SchemaINNODB_TABLESPACES table.

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

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

The default undo tablespaces (innodb_undo_001 andinnodb_undo_002) created when the MySQL instance is initialized cannot be dropped. They can, however, be made inactive using anALTER UNDO TABLESPACEtablespace_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.

Undo tablespaces created withCREATE UNDO TABLESPACE syntax can be moved while the server is offline to any known directory. Known directories are those defined by theinnodb_directories variable. Directories defined byinnodb_data_home_dir,innodb_undo_directory, anddatadir are automatically appended to theinnodb_directories value regardless of whether theinnodb_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 andinnodb_undo_002) created when the MySQL instance is initialized must reside in the directory defined by theinnodb_undo_directory variable. If theinnodb_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 theinnodb_undo_directory variable configured to the new directory.

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

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

The default setting forinnodb_rollback_segments is 128, which is also the maximum value. For information about the number of transactions that a rollback segment supports, seeSection 17.6.6, “Undo Logs”.

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 theinnodb_undo_log_truncate variable. For example:

mysql> SET GLOBAL innodb_undo_log_truncate=ON;

When theinnodb_undo_log_truncate variable is enabled, undo tablespaces that exceed the size limit defined by theinnodb_max_undo_log_size variable are subject to truncation. Theinnodb_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 theinnodb_undo_log_truncate variable is enabled:

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 TABLESPACEtablespace_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 frominactive toempty.

To check the state of an undo tablespace, query the Information SchemaINNODB_TABLESPACES table.

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

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

ALTER UNDO TABLESPACEtablespace_name SET ACTIVE;

An undo tablespace in anempty state can also be dropped. SeeDropping 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 theinnodb_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 theinnodb_purge_rseg_truncate_frequency setting. For example, to have the purge thread look for undo tablespaces once every 32 times that purge is invoked, setinnodb_purge_rseg_truncate_frequency to 32.

mysql> SET GLOBAL innodb_purge_rseg_truncate_frequency=32;

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 extent to which performance is affected depends on a number of factors:

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 andpurge subsystem counters are provided for monitoring background activities associated with undo log truncation. For counter names and descriptions, query the Information SchemaINNODB_METRICS table.

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

For information about enabling counters and querying counter data, seeSection 17.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 tablespace truncate operations, which can occur ifinnodb_max_undo_log_size is set too low on a busy system, for example. If the limit is exceeded, an undo tablespace can still be made inactive, but it is not truncated until after the next checkpoint. the limit was raised from 64 to 50,000 in MySQL 8.0.22.

Undo Tablespace Truncation Recovery

An undo tablespace truncate operation creates a temporaryundo_space_number_trunc.log file in the server log directory. That log directory is defined byinnodb_log_group_home_dir. If a system failure occurs during the truncate operation, the temporary log file permits the startup process to identify undo tablespaces that were being truncated and to continue the operation.

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, seeSection 7.1.10, “Server Status Variables”.