Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 38.9Mb
PDF (A4) - 39.0Mb
PDF (RPM) - 38.0Mb
HTML Download (TGZ) - 10.8Mb
HTML Download (Zip) - 10.8Mb
HTML Download (RPM) - 9.5Mb
Man Pages (TGZ) - 211.0Kb
Man Pages (Zip) - 320.0Kb
Info (Gzip) - 3.5Mb
Info (Zip) - 3.5Mb
Excerpts from this Manual

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

14.7.7 Configuring Undo Tablespaces

Undo logs may 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. 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 14.7.8, “Truncating Undo Tablespaces”.

To configure separate 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 may only be configured when initializing a new MySQL instance, as the innodb_undo_tablespaces option can only be set at initialization time. The specified setting is fixed for the life of the MySQL instance.


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

  1. 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 MySQL data directory.

  2. 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, and 32 rollback segments are reserved for the temporary tablespace (ibtmp1). Therefore, to allocate rollback segments to undo tablespaces, set innodb_rollback_segments to a value greater than 33. For example, if you have two undo tablespaces, set innodb_rollback_segments to 35 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.

  3. Define the number of undo tablespaces using the innodb_undo_tablespaces option. This setting is fixed for the life of the MySQL instance, so if you are uncertain about the optimal value, estimate on the high side.

  4. Create a new MySQL test instance using the option values you have chosen.

  5. Use a realistic workload on your test instance with data volume similar to your production servers to test the configuration.

  6. Benchmark the performance of I/O intensive workloads.

  7. Periodically increase the value of innodb_rollback_segments and rerun performance tests until there are no further improvements in I/O performance.

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.