961
MySQL 8.0.2起UNDO表空間管理更靈活
在MySQL 8.0.2 DMR版本中，我們將提高InnoDB的UNDO表空間易管理性。
In MySQL 8.0.2 DMR we will introduce features which make managing undo tablespaces easier in InnoDB.
主要有幾點提升：
1、可以隨時自由地創建或刪除UNDO表空間；
2、無論是否需要進行InnoDB恢複，也可以在啟動前更改相關設置；
3、即便InnoDB引擎處於繁忙狀態時，也可以增加或減少UNDO表空間的數量。
The main improvement is that you can now create and drop undo tablespaces at any time.
You can change the config file setting before any startup, whether recovery is needed or not.
And you can either increase or decrease the number of undo tablespaces while the engine is busy.
innodb_undo_tablespaces: UNDO表空間包括回滾段，而回滾段又包括UNDO日誌。UNDO日誌用於'回滾'事務和創建MVCC所需要的舊版本數據，以便在一個事務中保證數據庫快照的一致性。
innodb_undo_tablespaces: Undo Tablespaces contain Rollback Segments which in turn contain undo logs. Undo Logs are used to ‘rollback’ transactions and to create earlier versions of data which is used by Multi Version Concurrency Control to present a consistent image of the database during a transaction.
以前，當數據庫初始化時，InnoDB的UNDO表空間的數量是確定的。而現在可以隨時將其值設置為0~127之間的任意值，並可通過啟動時讀取的配置文件，或者命令行，或者通過在線‘SET GLOBAL INNODB_UNDO_TABLESPACES=n’進行修改。
Previously, the number of undo tablespaces that InnoDB uses was established when the database was initialized. It can now be set to any value between 0 and 127 at any time; at startup in either the config file or on the command line, or while online by issuing ‘SET GLOBAL INNODB_UNDO_TABLESPACES=n’.
當UNDO表空間數量設置為0時（不使用獨立UNDO表空間），所有的回滾段將存儲在係統表空間中。這是在 5.6版本之前，無法支持獨立UNDO表空間存儲回滾段情景下的舊模式。我們嚐試通過這種方式盡量不使用係統表空間，所以默認值不會設置為2。在將來的版本中該選項最小值是2，這表明係統表空間將不會被用作任何回滾段。所以請不要在你的配置文件中設置innodb_undo_tablespaces=0。
When you choose zero undo tablespaces, all rollback segments are tracked by the system tablespace. This is the old way of storing Rollback Segments before separate Undo Tablespaces were added in version 5.6. We are trying to move away from using the system tablespace in this way, so the default value is not set to 2. In the near future, the minimum value will become 2, which means that the system tablespace will not be used for any rollback segments. So please do not keep innodb_undo_tablespaces=0 in your config files.
innodb_undo_log_truncate：我們將UNDO表空間的最小值設為2，因為當一個undo表空間被清空時，至少還需要有另一個undo表空間。InnoDB將在大事務結束後進行UNDO清除操作以收縮UNDO表空間大小。以前，innodb_undo_log_truncate的默認值為OFF，而在8.0.2版本該值默認為ON。
innodb_undo_log_truncate: We chose a minimum of 2 undo tablespaces because you need at least 2 in order for one of them to be truncated. Undo truncation allows InnoDB to shrink the undo tablespace size after unusually large transactions. Previously, the innodb_undo_log_truncate setting defaulted to OFF. With version 8.0.2 it defaults to ON.
innodb_rollback_segments: 選擇可以隨時設置為1~128之間的任何值。可通過啟動時讀取的配置文件，或直接在命令行中傳遞參數，或者啟動後在線執行命令‘SET GLOBAL INNODB_ROLLBACK_SEGMENTS=n’。
innodb_rollback_segments: This can now be set to any value between 1 and 128 at any time; at startup in either the config file or the command line, or while online by issuing ‘SET GLOBAL INNODB_ROLLBACK_SEGMENTS=n’.
這個選項曾是用於整個服務器可以支持的回滾段數。現在為每一個UNDO表空間的回滾段數，允許並發事務使用更多的回滾段數，該選項默認值仍為128。
This setting used to be the number of rollback segments that the whole server could support. It is now the number of rollback segments in each undo tablespace, allowing a greater number of rollback segments to be used by concurrent transactions. The default value is still 128.
innodb_undo_logs：該選項在5.6中作為innodb_rollback_segments的替代或者別名所引入。在InnoDB中術語有一點兒混亂，‘Undo Logs’存儲在回滾段中，這是UNDO表空間的文件段。在8.0.2版本，我們打算棄用該選項改用Innodb_rollback_segments選項。在最新的發布的5.7.19版本中將增加這個不建議使用的warnings提示。
innodb_undo_logs: This setting was introduced in 5.6 as an alternate or alias of innodb_rollback_segments. It was a little confusing in terminology since in InnoDB, ‘Undo Logs’ are stored in Rollback Segments, which are file segments of an Undo Tablespace. In v8.0.2, we are dropping the use of this setting and requiring Innodb_rollback_segments to be used instead. The latest released version 5.7.19 contains deprecation warnings if it is used.
UNDO表空間命名和位置：UNDO表空間位於innodb_undo_directory所指定的目錄中。如果該選項沒有被使用，則放在‘datadir’中。以前，他們被命名為‘undo001’, ‘undo002’。在8.0.2 DMR版本，他們被稱作‘undo_001’, ‘undo_002’等。改名的原因是在新的UNDO表空間中包含了一個新的頭頁麵，其映射了每一個回滾段的位置。而在5.6版本中，當使用獨立UNDO表空間時，其回滾段頭頁麵號由係統表空間所跟蹤，並且限製整個實例的回滾段數為128。
Undo Tablespace Name and Location: Undo tablespaces are located in the directory specified by the setting innodb_undo_directory. If that setting is not used, they are created in the ‘datadir’ location. Previously they had names like ‘undo001’, ‘undo002’, etc. In v8.0.2 DMR they have names like ‘undo_001’, ‘undo_002’, etc. The reason for the name change is that these newer undo tablespaces contain a new header page that maps the locations of each rollback segment it contains. In version 5.6 when separate undo tablespaces were introduced, their rollback segment header page numbers were tracked in the system tablespace which limited the number of rollback segments for the whole instance to 128.
由於每個UNDO表空間可以使用這個新頁來跟蹤自己的回滾段，這些是真正新的UNDO表空間類型，並需要有不同的命名約定。這也是innodb_rollback_segments現在定義每個UNDO表空間回滾段數量而不是整個MySQL實例數量的原因。
Since each undo tablespace can now track its own rollback segments with this new page, these are really new types of undo tablespaces and need to have a different naming convention. This is also the reason that innodb_rollback_segments now defines the number of rollback segments per undo tablespace instead of the number for the whole MySQL instance.
自動升級：
在此更變之前，係統表空間會跟蹤所有回滾段，無論他們位於係統表空間還是UNDO表空間。如果現在在一個使用係統表空間跟蹤回滾段的已經存在了的數據庫上啟動MySQL 8.0.2，將自動生成至少兩個新的UNDO表空間。這將是很正常的，因為innodb_undo_tablespaces之前的默認值為0。MySQL 5.7數據庫將直接通過升級進程，其中包括從舊的FRM文件創建新的DD。作為此進程的一部分，還將創建至少兩個新的UNDO表空間。已經存在於係統表空間中的回滾段和undo表空間，如果尚有未清除的undo log，則它們仍然會被InnoDB識別並使用。但是InnoDB不會將這些老的回滾段分配給任何新的事務。所以一旦UNDO恢複完成，並不再需要這些UNDO日誌，舊的UNDO表空間也將被刪除。
Automatic Upgrade: Before this change, the system tablespace tracked all rollback segments whether they were in the system tablespace or in undo tablespaces. If you start MySQL 8.0.2 on an existing database that uses the system tablespace to track rollback segments, at least 2 new undo tablespaces will be generated automatically. This will be common since the previous default value for innodb_undo_tablespaces was 0. Mysql 5.7 databases will go through an upgrade process which among other things will create the new DD from the old FRM files. As part of this process, at least 2 new undo tablespaces will be created as well. InnoDB can still use the existing rollback segments and undo tablespaces defined in the system tablespace if they have undo logs in them at startup. But it will not assign these old rollback segments to any new transactions. So once undo recovery is finished and these undo logs are not needed anymore, the old undo tablespaces are deleted.
更多好處：
此新特性允許你在數據庫規模增長時，動態地添加更多的UNDO表空間和回滾段。使用更多的UNDO表空間，可以更輕鬆地通過UNDO表空間清除來減少用於存放回滾段的磁盤空間消耗。此外，更多的回滾段意味著並發事務可盡可能的使用單獨的回滾段，以減少相同資源的爭用。
Advantages and Benefits: This change allows you to dynamically add more undo tablespaces and rollback segments as a database installation grows. With more undo tablespaces, it is easier to use undo tablespace truncation to minimize the disk space dedicated to rollback segments. Also, more rollback segments mean that concurrent transactions are more likely to use separate rollback segments for their undo logs which results in less contention for the same resources.
Thanks for using MySQL!
原文發布時間為：2017-09-07
翻譯團隊：知數堂藏經閣項目 - 天一閣
團隊成員：天一閣-神諭、 天一閣-冷鋒 、天一閣-郭相成
譯文校稿：葉師傅
原文出處：https://mysqlserverteam.com/mysql-8-0-2-more-flexible-undo-tablespace-management/
原文作者：Kevin Lewis
本文來自雲棲社區合作夥伴“老葉茶館”，了解相關信息可以關注“老葉茶館”微信公眾
最後更新：2017-09-07 12:02:29