MySQL 5.6 Reference Manual Including MySQL NDB Cluster 7.3-7.4 Reference Guide
Undo tablespaces contain undo logs, which are collections of undo
log records that contain information about how to undo the latest
change by a transaction to a clustered index record. Undo logs
exist within undo log segments, which are contained within
rollback segments. The
innodb_rollback_segments
variable
defines the number of rollback segments allocated to each undo
tablespace.
Undo logs can be stored in one or more undo tablespaces instead of the system tablespace. This layout differs from the default configuration in which undo logs reside in the system tablespace. The I/O patterns for undo logs make undo tablespaces good candidates for SSD storage, while keeping the system tablespace on hard disk storage.
The number of undo tablespaces used by InnoDB
is controlled by the
innodb_undo_tablespaces
configuration option. This option can only be configured when
initializing the MySQL instance. It cannot be changed afterward.
Undo tablespaces and individual segments inside those tablespaces cannot be dropped.
To configure undo tablespaces for a MySQL instance, perform the following steps. It is assumed that you are performing the procedure on a test instance prior to deploying the configuration to a production system.
The number of undo tablespaces can only be configured when initializing a MySQL instance and is fixed for the life of the instance.
Specify a directory location for undo tablespaces using the
innodb_undo_directory
configuration option. If a directory location is not
specified, undo tablespaces are created in the data
directory.
Define the number of rollback segments using the
innodb_rollback_segments
configuration option. Start with a relatively low value and
increase it incrementally over time to examine the effect on
performance. The default setting for
innodb_rollback_segments
is
128, which is also the maximum value.
One rollback segment is always assigned to the system
tablespace. Therefore, to allocate rollback segments to undo
tablespaces, set
innodb_rollback_segments
to
a value greater than 1. For example, if you have two undo
tablespaces, set
innodb_rollback_segments
to
3 to assign one rollback segment to each of the two undo
tablespaces. Rollback segments are distributed among undo
tablespaces in a circular fashion.
When you configure separate undo tablespaces, the rollback segment in the system tablespace is rendered inactive.
Define the number of undo tablespaces using the
innodb_undo_tablespaces
option. The specified number of undo tablespaces is fixed
for the life of the MySQL instance, so if you are uncertain
about an optimal value, estimate on the high side.
Create a new MySQL test instance using the option values you have chosen.
Use a realistic workload on your test instance with data volume similar to your production servers to test the configuration.
Benchmark the performance of I/O intensive workloads.
Periodically increase the value of
innodb_rollback_segments
and rerun performance tests until there are no further
improvements in I/O performance.