Documentation Home
MySQL 5.6 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 31.5Mb
PDF (A4) - 31.5Mb
PDF (RPM) - 30.6Mb
HTML Download (TGZ) - 7.6Mb
HTML Download (Zip) - 7.7Mb
HTML Download (RPM) - 6.6Mb
Man Pages (TGZ) - 187.5Kb
Man Pages (Zip) - 302.1Kb
Info (Gzip) - 2.9Mb
Info (Zip) - 2.9Mb
Excerpts from this Manual

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

14.7.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 in which undo logs reside in the system tablespace. 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.

The undo tablespace feature involves the following configuration options:

  • innodb_undo_tablespaces

    Defines the number of tablespace files that rollback segments are divided between when you use a non-zero innodb_undo_logs setting. This non-dynamic startup option can only be configured when initializing the MySQL instance.

  • innodb_undo_directory

    The path where undo tablespaces are created. The location can be changed later by shutting down the server, moving undo tablespace files (undoN.ibd files) to a new directory, updating the innodb_undo_directory setting, and restarting the server.

  • innodb_undo_logs

    Defines the number of rollback segments used for data-modifying transactions that generate undo records. innodb_undo_logs replaces innodb_rollback_segments, which remains available for backward compatibility.

Configuring Separate Undo Tablespaces

The following procedure assumes the configuration is performed on a test instance prior to production deployment.

  1. Chose a directory location where you want InnoDB to create separate undo tablespaces for the undo logs. Specify the directory 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. For embedded MySQL installations, an absolute path must be specified.

  2. Decide on a starting value for the innodb_undo_logs option, 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.

  3. Decide on a non-zero value for the innodb_undo_tablespaces option. The rollback segments specified by the innodb_undo_logs value are divided between this number of separate undo tablespaces. The innodb_undo_tablespaces 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.7.6, “Copying File-Per-Table Tablespaces to Another Instance” 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 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 InnoDB buffer pool.

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

Internals

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

Prior to MySQL 5.6.36, space IDs were assigned to undo tablespaces in a consecutive order starting with space ID 1. As of MySQL 5.6.36, 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.


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