Undo tablespaces contain undo logs, which are collections of records containing information about how to undo the latest change by a transaction to a clustered index record.
Undo logs are stored in the system tablespace by default but can be stored in one or more undo tablespaces instead. Using undo tablespaces can reducing the amount of space required for undo logs in any one tablespace. The I/O patterns for undo logs also make undo tablespaces good candidates for SSD storage.
The number of undo tablespaces used by InnoDB
is controlled by the
innodb_undo_tablespaces
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.
This procedure describes how to configure undo tablespaces. When undo tablespaces are configured, undo logs are stored in the undo tablespaces instead of the system tablespace.
The number of undo tablespaces can only be configured when initializing a MySQL instance and is fixed for the life of the instance, so it is recommended that you perform the following procedure on a test instance with a representative workload before deploying the configuration to a production system.
To configure undo tablespaces:
Specify a directory location for undo tablespaces using the
innodb_undo_directory
variable. 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
variable. Start with a relatively low value and increase it incrementally over time to examine the effect on performance. The default setting forinnodb_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, setinnodb_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 add 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 configuration settings 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.