Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual Excerpts from this Manual

MySQL 5.7 Reference Manual  /  ...  /  Storing InnoDB Undo Logs in Separate Tablespaces

14.4.7 Storing InnoDB Undo Logs in Separate Tablespaces

You can store InnoDB undo logs in one or more separate undo tablespaces outside of the system tablespace. This layout is different from the default configuration where the undo log is part of the system tablespace. The I/O patterns for the undo log make these tablespaces good candidates to move to SSD storage, while keeping the system tablespace on hard disk storage. Users cannot drop the separate tablespaces created to hold InnoDB undo logs, or the individual segments inside those tablespaces. However, as of MySQL 5.7.5, undo logs stored in undo tablespaces can be truncated. For more information, see Section 14.4.8, “Truncating Undo Logs That Reside in Undo Tablespaces”.

Because these files handle I/O operations formerly done inside the system tablespace, we broaden the definition of system tablespace to include these new files.

Undo logs are also referred to as rollback segments.

This feature involves the following new or renamed configuration options:

Because the InnoDB undo log feature involves setting two non-dynamic startup variables (innodb_undo_tablespaces and innodb_undo_directory), this feature can only be enabled when initializing a MySQL instance.

Usage Notes

To use this feature, follow these steps:

  1. Decide on a path to hold the undo logs. You will specify that path as the argument to the innodb_undo_directory option in your MySQL configuration file or startup script. If no path is specified, undo tablespaces are created in the MySQL data directory, as defined by datadir.

  2. Decide on a starting value for the innodb_undo_logs option. You can start with a relatively low value and increase it over time to examine the effect on performance.

    As of MySQL 5.7.2, 32 of 128 undo logs were reserved as non-redo undo logs (rollback segments) for temporary table transactions. To allocate undo logs to undo tablespaces, innodb_undo_logs must be set to a value greater than 33. For example, if you have two undo tablespaces (innodb_undo_tablespaces=2), innodb_undo_logs must be set to 35 to assign one undo log to each of the two undo tablespaces.

    • The first undo log (rollback segment) always resides in the system tablespace (when undo tablespaces are present, this undo log is inactive).

    • Undo logs 2 to 33 reside in the shared temporary tablespace (ibtmp1).

    • The 34th undo log resides in the first undo tablespace.

    • The 35th undo log resides in the second undo tablespace.

  3. Decide on a non-zero value for the innodb_undo_tablespaces option. The multiple undo logs specified by the innodb_undo_logs value are divided between this number of separate tablespaces (represented by .ibd files). This value 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 instance, using the values you chose in the configuration file or in your MySQL startup script. Use a realistic workload with data volume similar to your production servers. Alternatively, use the transportable tablespaces feature to copy existing database tables to your newly configured MySQL instance. See Section 14.4.6, “Copying File-Per-Table Tablespaces to Another Server” for more information.

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

  6. Periodically increase the value of innodb_undo_logs and rerun performance tests. Find the value where you stop experiencing gains in I/O performance.

  7. Deploy a new production instance using the ideal settings for these options. Set it up as a slave server in a replication configuration, or transfer data from an earlier production instance.

Performance and Scalability Considerations

Keeping the undo logs in separate files allows the MySQL team to implement I/O and memory optimizations related to this transactional data. For example, because the undo data is written to disk and then rarely used (only in case of crash recovery), it does not need to be kept in the file system memory cache, in turn allowing a higher percentage of system memory to be devoted to the InnoDB buffer pool.

The typical SSD best practice of keeping the InnoDB system tablespace on a hard drive and moving the per-table tablespaces to SSD, is assisted by moving the undo information into separate tablespace files.


The physical tablespace files are named undoN, where N is the space ID, including leading zeros.

MySQL instances containing separate undo tablespaces cannot be downgraded to earlier releases such as MySQL 5.5 or 5.1.


As of MySQL 5.7.5, you can truncate undo logs that reside in undo tablespaces. For more information, see Section 14.4.8, “Truncating Undo Logs That Reside in Undo Tablespaces”.

Download this Manual
User Comments
Sign Up Login You must be logged in to post a comment.