Database: mysql:undo log

1, Definition

Files required for transaction rollback.

2, Function

  • Rollback the transaction
  • MVCC control: when a user reads a row of record, if the record has been occupied by other transactions, the current transaction can read the previous row version information through undo log, so as to realize consistent non locking reading.

    The snapshot version here is implemented through undo log.

3, Storage location

undo log segment in the shared tablespace.

4, Attention

  • Logically restore undo to the original database. All changes are logically cancelled, but the data structure and the page itself may be very different after rollback.
  • Undo log will generate redo log, that is, the generation of undo log will be accompanied by the generation of redo log, because undo log also needs persistence protection.

5, undo storage management

The InnoDB storage engine manages undo in the same way. But this paragraph is different from the previous paragraph. Firstly, the InnoDB storage engine has rollback segments. 1024 undo log segments are recorded in each rollback segment, and the undo page application is made in each undo log segment.

It should be noted that the process of allocating pages in undo log segment and writing undo log also needs to be written to redo log. When a transaction is committed, the InnoDB storage engine will do the following two things:

  • Put undo log into the list for future purge operations
  • Judge whether the page of undo log can be reused. If so, it can be allocated to the next transaction

The undo log and the page of the undo log cannot be deleted immediately after the transaction is committed. This is because there may be other transactions that need to get the version before the row record through undo log. When submitting the story service, put the undo log into a linked list. Whether the undo log and the page of the undo log can be finally deleted is determined by the purge thread.

1. undo settings

mysql> show variables like 'innodb_undo%';
| Variable_name            | Value |
| innodb_undo_directory    | ./    |
| innodb_undo_log_truncate | OFF   |
| innodb_undo_logs         | 128   |
| innodb_undo_tablespaces  | 0     |

mysql> show variables like 'datadir%';
| Variable_name | Value           |
| datadir       | /var/lib/mysql/ |
  • innodb_undo_directory: used to set the path where the rollback segment file is located. This means that the rollback segment can be stored outside the shared tablespace, that is, it can be set as a separate tablespace.
  • innodb_undo_logs: used to set the number of rollback segment s. The default value is 128.
  • innodb_undo_tablespaces: used to set the number of files constituting rollback segments, so that rollback segments can be evenly distributed among multiple files.

6, undo reuse

If a separate undo page is allocated for each transaction, it will waste storage space, especially for OLTP application types. Because the page may not be released immediately when the transaction is committed. Assuming that the TPS (transaction per second) of the delete and update operations of an application is 1000 and an undo page is allocated for each transaction, 1000 * 60 pages are required in one minute, and the storage space required is about 1GB. If the number of purge pages per second is 20, this design has a high requirement on disk space. Therefore, undo pages can be reused in the design of InnoDB storage engine.

When the transaction is committed, first put the undo log into the linked list, and then judge whether the usage space of the undo page is less than 3 / 4. If so, it means that the undo page can be reused, and then the new undo log is recorded after the current undo log.

//View undo log quantity
Trx id counter 13090
Purge done for trx's n:o < 13087 undo n:o < 0 state: running but idle
History list length 49
---TRANSACTION 421304033991416, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421304033990496, not started
0 lock struct(s), heap size 1136, 0 row lock(s)

History list length 49 indicates the number of undo log s

7, undo log format

1. undo log classification

In the InnoDB storage engine, undo log s are divided into:

  • insert undo log: refers to the undo log generated during the insert operation. Because the record of insert operation is only visible to the transaction itself and not to other transactions (this is a requirement of transaction isolation), the undo log can be deleted directly after the transaction is committed. No purge operation is required.
  • update undo log: records the undo log generated by delete and update operations. The undo log may need to provide MVCC mechanism, so it cannot be deleted when the transaction is committed. When submitting, put the undo log linked list and wait for the purge thread to delete it finally.

8, undo log recycle

1. Recycling mechanism

In general: the InnoDB storage engine uses the purge thread to find the undo log from the history list first, and then the undo log from the undo page.

In the process of purging, the InnoDB storage engine first finds the first record to be cleaned from the history list, which is trx1. After cleaning, the InnoDB storage engine will continue to find whether there are records that can be cleaned in the page where the undo log of trx1 is located. Here, it will find transaction trx3, and then find trx5. However, it is found that trx5 is referenced by other transactions and cannot be cleaned up, so it goes to the history list again, It is found that the last record at this time is trx2, then find the page where trx2 is located, and then clean up the records of transactions trx6 and trx4 in turn. Because all pages in undo page2 have been cleaned up, the undo page can be reused.

2. Advantages

Avoid a large number of random read operations, so as to improve the efficiency of purge.

3. Settings related to recycling

//The number of undo page s to be cleaned for each purge operation
mysql> show variables like 'innodb_purge_batch_size';
| Variable_name           | Value |
| innodb_purge_batch_size | 300   |

//Used to control the length of history list. If the length is greater than this parameter, it will "delay" the operation of DML. The default value of this parameter is 0, which means that there are no restrictions on history list.
mysql> show variables like 'innodb_max_purge_lag';
| Variable_name        | Value |
| innodb_max_purge_lag | 0     |

//When InnoDB_ max_ purge_ When lag is greater than 0, the DML operation will be delayed. The delay algorithm is: delay = ((length(history_list)-innodb_max_purge_lag)*10)-5
//To control the maximum number of milliseconds of delay. That is, when the above calculated delay value is greater than this parameter, set delay to innodb_max_purge_lag_delay to avoid unlimited waiting of other SQL threads due to slow purge operation.
mysql> show variables like 'innodb_max_purge_lag_delay';
| Variable_name              | Value |
| innodb_max_purge_lag_delay | 0     |

Tags: Database

Posted by itsjareds on Wed, 25 May 2022 13:51:44 +0300