Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 38.5Mb
PDF (A4) - 38.5Mb
PDF (RPM) - 33.3Mb
HTML Download (TGZ) - 8.1Mb
HTML Download (Zip) - 8.1Mb
HTML Download (RPM) - 7.0Mb
Man Pages (TGZ) - 134.3Kb
Man Pages (Zip) - 190.2Kb
Info (Gzip) - 3.4Mb
Info (Zip) - 3.4Mb
Excerpts from this Manual

MySQL 8.0 Reference Manual  /  ...  /  Configuring Undo Tablespaces

15.7.8 Configuring 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.

Configuring the Number of 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.

Note

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

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 Section 15.7.9, “Truncating Undo Tablespaces”.

Configuring the Location of 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.


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.