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

An article translated by myself, originally from the official MySQL Server team blog:

Original link: http://mysqlserverteam.com/mysql-8-0-2-more-flexible-undo-tablespace-management/

At the same time, it was also released in Lao Ye Teahouse:

Https://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ==&mid=2653930802&idx=1&sn=db6c9acab8fb41da004f36ff90355258&chksm=bd3b5f588a4cd64e25e1beeac66d6a71beaa2ff0617ea8e46d3af5f9a3aecb4ff744b8d06b26&scene=38#wechat_redirect

In the MySQL 8.0.2 DMR release, we will improve the manageability of UNDO tablespaces for InnoDB.

0 has several major improvements:

1. You can freely create or delete UNDO tablespaces at any time

2. Regardless of whether InnoDB recovery is required or not, you can also change the relevant settings before startup.

3. Even when the InnoDB engine is busy, you can increase or decrease the number of UNDO tablespaces.

0 innodb_undo_tablespaces:

The UNDO tablespace includes a rollback segment, which in turn includes UNDO logs. UNDO logs are used to 'roll back' transactions and create older versions of data needed for MVCC to ensure the consistency of database snapshots in a transaction.

Previously, when the database was initialized, the number of UNDO tablespaces for InnoDB was determined. It can now be set to any value between 0,127 at any time and can be modified through the configuration file read at startup, or on the command line, or via online 'SET GLOBAL INNODB_UNDO_TABLESPACES=n'.

When the number of UNDO tablespaces is set to 0 (no separate UNDO tablespaces are used), all rollback segments are stored in the system tablespaces. This is an old schema that could not be supported in a separate UNDO tablespace storage rollback segment scenario prior to version 5.6. We try not to use system tablespaces as much as possible in this way, so the default value is not set to 2. In future releases, the minimum value of this option is 2, which indicates that the system tablespace will not be used as any rollback segment. So please do not set innodb_undo_tablespaces=0 in your configuration file.

0 innodb_undo_log_truncate:

We set the minimum value for UNDO tablespaces to 2 because at least one other UNDO tablespace is required when one UNDO tablespace is emptied. InnoDB will perform a UNDO cleanup operation after the end of a large transaction to shrink the size of the UNDO table space. Previously, the default value for innodb_undo_log_truncate was OFF, but in version 8.0.2 it defaulted to ON.

0 innodb_rollback_segments:

The selection can be set to any value between 1 and 128 at any time. You can use the configuration file read at startup, pass parameters directly on the command line, or execute the command 'SET GLOBAL INNODB_ROLLBACK_SEGMENTS=n' online after startup.

This option was used for the number of rollback segments that can be supported by the entire server. Now the number of rollback segments per UNDO table space, allowing concurrent transactions to use more rollback segments, this option still defaults to 128.

0 innodb_undo_logs:

This option was introduced in 5. 6 as an alternative to innodb_rollback_segments or aliases. The terminology in InnoDB is a little confusing, 'Undo Logs' is stored in the rollback segment, which is a file segment of the UNDO tablespace. In version 8.0.2, we intend to switch from this option to the Innodb_rollback_segments option. This deprecated warnings prompt will be added in the latest release of 5.7.19.

0 UNDO tablespace naming and location:

The UNDO tablespace is located in the directory specified by innodb_undo_directory. If this option is not used, it is placed in 'datadir'. Previously, they were named 'undo001',' undo002'. In the 8.0.2 DMR version, they are called 'undo_001',' undo_002' and so on. The reason for the renaming is that a new header page is included in the new UNDO table space, which maps the location of each rollback segment. In version 5.6, when a separate UNDO tablespace is used, the rollback segment header page number is tracked by the system tablespace, and the number of rollback segments for the entire instance is limited to 128.

Because each UNDO tablespace can use this new page to track its own rollback segments, these are really new UNDO tablespace types and require different naming conventions. This is why innodb_rollback_segments now defines the number of rollback segments per UNDO tablespace rather than the number of entire MySQL instances.

0 automatic upgrade:

Prior to this change, the system tablespace tracks all rollback segments, whether they are in the system tablespace or the UNDO tablespace. If you start MySQL 8.0.2 now on an existing database that uses system tablespaces to track rollback segments, at least two new UNDO tablespaces will be automatically generated. This would be normal because the default value before innodb_undo_tablespaces was 0. The MySQL 5.7database will go directly through the upgrade process, which includes creating a new DD from the old FRM file. At least two new UNDO tablespaces will also be created as part of this process. Rollback segments and UNDO tablespaces that already exist in the system tablespace will still be recognized and used by InnoDB if there is still an uncleared undo log. However, InnoDB does not assign these old rollback segments to any new transactions. So once the UNDO recovery is complete, these UNDO logs are no longer needed, and the old UNDO tablespaces will be deleted.

0 more benefits:

This new feature allows you to dynamically add more UNDO tablespaces and rollback segments as the database grows. With more UNDO tablespaces, it is easier to clean up UNDO tablespaces to reduce disk space consumption for rollback segments. In addition, more rollback segments mean that concurrent transactions can use separate rollback segments as much as possible to reduce contention for the same resources.

Thanks for using MySQL!

P.p1 {margin: 0.0px 0.0px 0.0px 0.0px; line-height: 19.0px; font: 13.0px 'Helvetica Neue'} author's official Wechat account (continuously updated)

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.