MySQL 5.6 Reference Manual Including MySQL NDB Cluster 7.3-7.4 Reference Guide
An undo log is a collection of undo log records associated with a single read-write transaction. An undo log record contains information about how to undo the latest change by a transaction to a clustered index record. If another transaction needs to see the original data as part of a consistent read operation, the unmodified data is retrieved from undo log records. Undo logs exist within undo log segments, which are contained within rollback segments. By default, rollback segments are physically part of the system tablespace, but they can also reside in undo tablespaces. For more information, see Section 14.6.3.3, “Undo Tablespaces”.
InnoDB
supports 128 rollback segments. The
innodb_rollback_segments
variable
defines the number of rollback segments used by
InnoDB
.
The number of transactions that a rollback segment supports depends on the number of undo slots in the rollback segment and the number of undo logs required by each transaction.
The number of undo slots in a rollback segment differs according
to InnoDB
page size.
InnoDB Page Size | Number of Undo Slots in a Rollback Segment (InnoDB Page Size / 16) |
---|---|
4096 (4KB) |
256 |
8192 (8KB) |
512 |
16384 (16KB) |
1024 |
A transaction is assigned up to two undo logs, one for each of the following operation types:
Undo logs are assigned as needed. For example, a transaction that
performs INSERT
,
UPDATE
, and
DELETE
operations is assigned two
undo logs. A transaction that performs only
INSERT
operations is assigned a
single undo log. Undo logs are assigned to a transaction from a
rollback segment that is also assigned to the transaction.
An undo log assigned to a transaction remains tied to the
transaction for its duration. For example, an undo log assigned to
a transaction for an INSERT
operation is used for all INSERT
operations performed by that transaction.
Given the factors described above, the following formulas can be
used to estimate the number of concurrent read-write transactions
that InnoDB
is capable of supporting.
A transaction can encounter a concurrent transaction limit error
before reaching the number of concurrent read-write transactions
that InnoDB
is capable of supporting. This
occurs when the rollback segment assigned to a transaction runs
out of undo slots. In such cases, try rerunning the transaction.
If each transaction performs either an
INSERT
or an
UPDATE
or
DELETE
operation, the number of
concurrent read-write transactions that
InnoDB
is capable of supporting is:
(innodb_page_size
/ 16) *innodb_rollback_segments
If each transaction performs an
INSERT
and an
UPDATE
or
DELETE
operation, the number of
concurrent read-write transactions that
InnoDB
is capable of supporting is:
(innodb_page_size
/ 16 / 2) *innodb_rollback_segments