Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 44.5Mb
PDF (A4) - 44.5Mb
PDF (RPM) - 40.2Mb
HTML Download (TGZ) - 10.5Mb
HTML Download (Zip) - 10.5Mb
HTML Download (RPM) - 9.1Mb
Man Pages (TGZ) - 204.6Kb
Man Pages (Zip) - 311.6Kb
Info (Gzip) - 3.9Mb
Info (Zip) - 3.9Mb
Excerpts from this Manual

15.6.3.4 Undo Tablespaces

By default, undo logs reside in two undo tablespaces. The I/O patterns for undo logs make undo tablespaces good candidates for SSD storage. Because undo logs can become large during long-running transactions, having undo logs in multiple undo tablespaces reduces the maximum size of any one undo tablespace.

The number of undo tablespaces used by InnoDB is controlled by the innodb_undo_tablespaces configuration option.

Note

The innodb_undo_tablespaces configuration option is deprecated and will be removed in a future release.

Configuring Undo Tablespaces

The innodb_undo_tablespaces configuration option defines the number of undo tablespaces used by InnoDB. The default and minimum value is 2. You can configure innodb_undo_tablespaces at startup or while the server is running.

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. However, undo tablespaces that are removed from the active list remain active until they are no longer used by existing transactions. Undo tablespaces are made inactive rather than removed so that the number of active undo tablespaces can easily be increased again.

Undo tablespaces or individual segments inside those tablespaces cannot be dropped. However, undo logs stored in undo tablespaces can be truncated. For more information, see Truncating Undo Tablespaces.

Undo tablespace files are created in the location defined by the innodb_undo_directory configuration option. This option is typically used to place undo logs on a different storage device. If a path is not specified, undo tablespaces are created in the MySQL data directory, as defined by datadir. The innodb_undo_directory option is non-dynamic. Configuring it requires restarting the server.

At startup, the directories defined by the innodb_directories variable are scanned for undo tablespace files. Directories defined by innodb_data_home_dir, innodb_undo_directory, and datadir are automatically appended to the innodb_directories argument value, regardless of whether the innodb_directories variable is defined explicitly. The innodb_directories scan also traverses subdirectories.

Undo tablespace file names are in the form of undo_NNN, where NNN is an undo space number between 1 and 127. The undo space number and undo space ID are related as follows:

  • undo space number = 0xFFFFFFF0 - undo space ID

  • undo space ID = 0xFFFFFFF0 - undo space number

The default size of an undo tablespace file is 10MiB.

Configuring the Number of Rollback Segments

The innodb_rollback_segments configuration option defines the number of rollback segments allocated to each undo tablespace. This option can be configured at startup or while the server is running.

The innodb_rollback_segments configuration option also defines the number of rollback segments assigned to the temporary tablespace.

The default setting for innodb_rollback_segments is 128, which is also the maximum value. Each rollback segment can support a maximum of 1023 data-modifying transactions.

Truncating Undo Tablespaces

To truncate undo tablespaces, the MySQL instance must be configured with a minimum of two undo tablespaces, which is the default and minimum value in MySQL 8.0. 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. Use this statement to check the value of innodb_undo_tablespaces:

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

innodb_undo_tablespaces is deprecated and will be removed in a future release.

For information about configuring undo tablespaces, see Section 15.6.3.4, “Undo Tablespaces”.

Enabling Truncation of Undo Tablespaces

To truncate 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:

  1. 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.

  2. 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 complete.

  3. The purge system frees rollback segments that are no longer needed.

  4. 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 depends on the innodb_page_size value. For the default 16KB InnoDB 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 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 |
    +-------------------------+
    | .                       |
    +-------------------------+
  5. 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


User Comments
User comments in this section are, as the name implies, provided by MySQL users. The MySQL documentation team is not responsible for, nor do they endorse, any of the information provided here.
Sign Up Login You must be logged in to post a comment.