Shulou(Shulou.com)06/01 Report--

Preface

Undo log is an important part of the InnoDB MVCC transaction feature. Undo records are generated when changes are made to records, which are stored in the system tablespace by default, but from 5.6a separate undo tablespace can be used.

Undo records store old version data. When an old transaction needs to read data, in order to read the old version data, it needs to follow the undo connection to find records that satisfy its visibility. When the version chain is long, you can think of this as a time-consuming operation.

Most of the changes to the record are insert, update, delete. The Insert operation is only visible to the current transaction before the transaction is committed, so the resulting undo log can be deleted directly after the transaction is committed (there is no visibility requirement for the data just inserted), while for update and delete, multiple versions of information need to be maintained.

Undo tablespace

MySQL5.6 can use separate undo tablespaces. Innodb_undo_tablespaces:0-126. the file size defaults to 10m after system initialization. Although it can be proposed from the system tablespace, so that the system tablespace is no longer growing rapidly because of large transactions, but the independent undo tablespace is still relatively chick, can not truncate. There is also no threshold for the relevant undo tablespace file size.

After MySQL5.7.5, the undo tablespace can be truncate. At least 2 undo tablespaces need to be configured to temporarily set to offline state when innodb_undo_spaces=2,undo tablespaces are deleted, and at least one other undo tablespace service can make server work. If configured as 1 undo tablespace, it is useless to turn on truncate, this undo tablespace file will always grow, or even burst the disk.

In the version after Mysql5.7.5, set global innodb_undo_log_truncate=on enables the truncate feature, and innodb_max_undo_log_size is the threshold for undo tablespace files. The default is 1G. If the value is exceeded, truncate will be performed automatically. If truncate is not enabled, the undo tablespace files will continue to grow.

For the selected deleted undo file, the corresponding rollback segment is marked as the rollback segment no longer used by inactive,purge recycling. After completion, the rollback segment is truncate and restored to the size of 10m, and the rollback segment is marked as active again.

Innodb_undo_logs defines the number of rollback segments used by InnoDB, which must be set to more than 35; the first resident system tablespace is inactive if an independent undo tablespace is used. Rollback segments 2-33 are sharing the temporary tablespace ibtmp1. 34th 35th is the first and second independent tablespaces, respectively.

What is the relationship between undo tablesapce and rollback segment

You can see that the undo tablespace is created when the system starts up. The default undo tablespace is placed in the system tablespace. When undo tablespaces are separated, undo tablespaces are divided into rollback segments in turn.

You can see that when the undo tablespace is proposed, the rollback segment is polling allocated to each transaction, and does not use the system tablespace, ensuring that all undo log is stored in the undo independent tablespace.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.