As the name indicates, an undo log record contains information about how to undo the recent changes by a transaction. When a transaction writes data, it always makes writes on the tablespace files. InnoDB Undo log stores copy of data that is being modified by any current transaction. So, at this point in time if any other transaction queries for the original data (row) which is being modified,  the undo logs provide the same and serve the purpose. This is what provides a consistent read view ( based on isolation ) during any data modifications.

Here in the above representation, Transaction T1 modifies the data (Data-1). During the time of modification to ensure the reads are consistent, transactions T2  and T3 are given access only to the copy (previous row version) of Data -1 which is stored in the “UNDO” logs.

This is where the Multi versioning feature of InnoDB uses the information to build earlier versions of a row for a consistent read.

Location of Undo Logs :

Undo logs exist within undo log segments, which are contained within rollback segments.  Rollback segments reside in

• System tablespace
• Undo tablespaces,
• Temporary tablespace.

InnoDB supports a maximum of 128 rollback segments, 32 of which are allocated to the temporary tablespace. This leaves 96 rollback segments that can be assigned to transactions that modify data in regular tables. 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 Row Structure:

InnoDB adds three fields to each row stored in the database.

DB_TRX_ID:

A 6-byte DB_TRX_ID field indicates the transaction identifier for the last transaction that inserted or updated the row.

DB_ROLL_PTR:

Each row also contains a 7-byte DB_ROLL_PTR field called the roll pointer. The roll pointer points to an undo log record written to the rollback segment. If the row was updated, the undo log record contains the information necessary to rebuild the content of the row before it was updated

DB_ROW_ID:

6-byte DB_ROW_ID  field contains a row ID that increases monotonically as new rows are inserted.

What is the History List? :

The History list length is the number of un-purged transactions in the undo space in InnoDB’s data file. When the transaction performs updates and commits this number increases.When the purge process removes old versions it decreases.This is used for cleaning up useless versions of rows after a transaction making sure that no active transaction still needs the history. There is a background process that keeps reading from this list – the main goal of this process is to free up undo log pages and unlinking them from the global history list to make them available for reuse. This thread is named as purge thread.

InnoDB expresses the total amount of history present in the system as a history list length, which can be seen in SHOW ENGINE INNODB STATUS.

History list length can be seen in the transactions section in SHOW ENGINE INNODB STATUS.

 
------------ TRANSACTIONS ------------ 
Trx id counter 2134175355 
Purge done for trx's n:o < 2125991086 undo n:o < 0 state: running butidle 
History list length 3462570 
LIST OF TRANSACTIONS FOR EACH SESSION:
	

Undo Log Chain :

The roll pointer points to an undo log record written to the rollback segment. If the row was updated, the undo log record contains the information necessary to rebuild the content of the row before it was updated.Every undo record contains a reference to its previous undo record except for an initial record insert. Thus a  chain of all the previous versions of records is formed.

Every record contains a reference to its most recent undo record, called a rollback pointer or ROLL_PTR, and every Undo record contains a reference to its previous undo record except for an initial record (inserts), forming a chain of all previous versions of a record. In this way, any previous version of a record can be easily constructed, as long as the undo records ( “history”) still persists in the undo logs.

Factors affecting the growth of undo logs :

There are a couple of factors that affects the Undo logs growth.

Long-running transaction :

As the long-running transaction has an old read view, purging of undo logs (history) for the entire system will be stalled until the transaction completes. This can cause the total size of undo logs to grow (rather than reusing the same space over and over as it normally would), causing the system tablespace (ibdata1) to grow and further it can’t be shrunk.

Transaction Isolation level :

Transaction isolation level is one of the important factors that is indirectly related to the history maintained in the form of undo logs.

SERIALIZABLE /  REPEATABLE READ isolation level is the strictest and requires a lot of history keeping. In a write-heavy database, depending on the isolation level, reverting the version of a large number of rows to very old versions might be required.
It gets worse in the case of long-running transactions as long-running transactions will block purge operations and the history list of old versions will keep on growing continuously. This will cause the size of the undo logs to grow. Also, whenever the long-transaction is completed/killed, it might be hard for the purge thread to keep up with the work ahead of it. That can also cause severe performance issues.

If the application is aware of a long-running transaction which cannot be improved or avoided, it must consider using READ COMMITTED isolation level in order to avoid these issues as in  READ COMMITTED, InnoDB does not need to maintain history length when other transactions have committed changes and can avoid the InnoDB history length to grow drastically. We will cover variables related to undo logs in upcoming days.

Optimize performance, enhance reliability, and ensure data security with Mydbops' MySQL InnoDB Cluster Consulting and Support Services. Our team can help you fine-tune temporary tablespace configuration, troubleshoot performance issues, and implement best practices. Contact us today to schedule a free consultation.

{{cta}}