You can store
undo logs in one or more
tablespaces outside of the
This layout is different from the default configuration in which
undo logs reside in the
The I/O patterns for undo logs make undo 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, undo logs stored in undo tablespaces can be
truncated. For more information, see
Section 14.7.8, “Truncating Undo Logs That Reside in Undo Tablespaces”.
The undo tablespace feature involves the following configuration options:
Defines the number of tablespace files that rollback segments are divided between when you use a non-zero
innodb_undo_logssetting. This non-dynamic startup option can only be configured when initializing the MySQL instance.
The path where undo tablespaces are created. The location can be changed later by shutting down the server, moving undo tablespace files (
undofiles) to a new directory, updating the
innodb_undo_directorysetting, and restarting the server.
Defines the number of rollback segments used for data-modifying transactions that generate undo records.
innodb_rollback_segments, which remains available for backward compatibility.
The following procedure assumes the configuration is performed on a test instance prior to production deployment.
Chose a directory location where you want
InnoDBto create separate undo tablespaces for the undo logs. Specify the directory path as the argument to the
innodb_undo_directoryoption 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
Decide on a starting value for the
innodb_undo_logsoption, which defines the number of rollback segments used by
InnoDB. (Undo logs exist within undo log segments, which are contained within rollback segments.) You can start with a relatively low value and increase it over time to examine the effect on performance.
One rollback segment is always assigned to the system tablespace, and 32 rollback segments are reserved for use by temporary tables and are hosted in the temporary tablespace (
ibtmp1). Therefore, to allocate rollback segments to undo tablespaces,
innodb_undo_logsmust be set to a value greater than 33. For example, if you have two undo tablespaces (
innodb_undo_logsmust be set to 35 to assign one rollback segment to each of the two undo tablespaces.Note
When you configure separate undo tablespaces, the rollback segment in the system tablespace is rendered inactive.
Decide on a non-zero value for the
innodb_undo_tablespacesoption. The rollback segments specified by the
innodb_undo_logsvalue are divided between this number of separate tablespaces. The
innodb_undo_tablespacesvalue is fixed for the life of the MySQL instance, so if you are uncertain about the optimal value, estimate on the high side.
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.7.6, “Copying File-Per-Table Tablespaces to Another Instance” for more information.
Benchmark the performance of I/O intensive workloads.
Periodically increase the value of
innodb_undo_logsand rerun performance tests. Find the value where you stop experiencing gains in I/O performance.
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 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
The typical best practice of keeping the
system tablespace on a hard drive and moving the
to SSD is assisted by moving the undo information into separate
The physical undo tablespace files are named
N is the space ID, including leading
Prior to MySQL 5.7.18, space IDs were assigned to undo tablespaces in a consecutive order starting with space ID 1. As of MySQL 5.7.18, The first undo tablespace can be assigned a space ID other than 1. Space ID values for undo tablespaces are still assigned in a consecutive order.
MySQL instances containing separate undo tablespaces cannot be downgraded to earlier releases such as MySQL 5.5 or 5.1.