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.
configuration option defines the number of undo tablespaces used
InnoDB. The default and minimum value is
2. You can configure
startup or while the server is running.
deprecated and will be removed in a future release.
creates the specified number of undo tablespaces and adds them
to the list of active undo tablespaces. Decreasing the
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”.
Undo tablespace files are created in the location defined by the
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
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
datadir are automatically
appended to the
value, regardless of whether the
innodb_directories variable is
defined explicitly. The
innodb_directories scan also
Undo tablespace file names are in the form of
NNN is an undo space number between 1
and 127. The undo space number and undo space ID are related as
undo space number = 0xFFFFFFF0 - undo space ID
undo space ID = 0xFFFFFFF0 - undo space number
The default size of an undo tablespace file is 10MiB.
The default setting for
128, which is also the maximum value. Each rollback segment can
support a maximum of 1023 data-modifying transactions.