Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 34.6Mb
PDF (A4) - 34.6Mb
PDF (RPM) - 32.2Mb
HTML Download (TGZ) - 8.2Mb
HTML Download (Zip) - 8.2Mb
HTML Download (RPM) - 7.0Mb
Man Pages (TGZ) - 147.5Kb
Man Pages (Zip) - 208.8Kb
Info (Gzip) - 3.1Mb
Info (Zip) - 3.1Mb


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

Pre-General Availability Draft: 2017-12-13

15.7.7 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.8, “Truncating Undo Tablespaces”.

Configuring the Location of Undo Tablespace

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.

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
Sign Up Login You must be logged in to post a comment.