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

14.2.5 InnoDB Undo Logs

An undo log (or rollback segment) is a storage area that holds copies of data modified by active transactions. If another transaction needs to see the original data (as part of a consistent read operation), the unmodified data is retrieved from this storage area. By default, this area is physically part of the system tablespace. However, as of MySQL 5.6.3, undo logs can reside in separate undo tablespaces. For more information, see Section 14.4.7, “Storing InnoDB Undo Logs in Separate Tablespaces”. For more information about undo logs and multi-versioning, see Section 14.2.3, “InnoDB Multi-Versioning”.

InnoDB supports 128 undo logs. As of MySQL 5.7.2, 32 of 128 undo logs were reserved as non-redo undo logs for temporary table transactions. Each transaction that updates a temporary table (excluding read-only transactions) is assigned two undo logs, one redo-enabled undo log and one non-redo undo log. Read-only transactions are only assigned non-redo undo logs, as read-only transactions are only permitted to modify temporary tables.

This leaves 96 available undo logs, each of which supports up to 1023 concurrent data-modifying transactions, for a total limit of approximately 96K concurrent data-modifying transactions. The 96K limit assumes that transactions do not modify temporary tables. If all data-modifying transactions also modify temporary tables, the total limit would be approximately 32K concurrent data modifying transactions. For more information about undo logs that are reserved for temporary table transactions, see Section 14.2.6, “InnoDB Temporary Table Undo Logs”.

The innodb_undo_logs option defines the number of undo logs used by InnoDB.

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