As of MySQL 5.6.3, you can store the
undo log in one or more
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
undo logs, or the individual
segments inside those
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.
The undo logs are also known as the 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 on a fast storage device to hold the undo
logs. You will specify that path as the argument to the
in your MySQL configuration file or startup script.
Decide on a non-zero 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.
Decide on a non-zero value for the
option. The multiple undo logs specified by the
innodb_undo_logs value are divided between
this number of separate tablespaces (represented by
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.
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.5.5, “Copying Tablespaces to Another Server (Transportable Tablespaces)” for more information.
Benchmark the performance of I/O intensive workloads.
Periodically increase the value of
innodb_undo_logs and re-do the performance
tests. Find the value where you stop experiencing gains in I/O
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 filesystem 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
Currently, MySQL instances containing separate undo tablespaces cannot be downgraded to earlier releases such as MySQL 5.5 or 5.1.