You can store
undo logs in one or more
tablespaces outside of the
This layout is different from the default configuration where the
undo log is part of the
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 15.7.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:
undo log feature involves
setting two non-dynamic startup variables
feature can only be enabled when initializing a MySQL instance.
To use this feature, follow these steps:
Decide on a path to hold the undo logs. You will specify that 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. You can start with a relatively low value and increase it over time to examine the effect on performance.
One undo log is always assigned to the system tablespace, and as of MySQL 5.7.2, 32 undo logs are reserved for use by temporary tables and are hosted in the temporary tablespace (
ibtmp1). Therefore, to allocate undo logs 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 undo log to each of the two undo tablespaces.Note
When you configure separate undo tablespaces, the undo log in the system tablespace is rendered inactive.
Decide on a non-zero value for the
innodb_undo_tablespacesoption. The multiple undo logs specified by the
innodb_undo_logsvalue are divided between this number of separate tablespaces (represented by
.ibdfiles). 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.
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 15.7.6, “Copying File-Per-Table Tablespaces to Another Server” 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 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
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
N is the space ID, including leading
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 15.7.8, “Truncating Undo Logs That Reside in Undo Tablespaces”.