MySQL 8.0 Undo Tablespace管理

1. UNDO 基礎概念

1. 預設至少初始化2個Undo表空間,最大支援127個Undo表空間,預設表空間名稱為undo_001,undo_002
2. 8.0.14 之後UNDO表空間支援線上增加,及線上刪除

• CREATE UNDO TABLESPACE/DROP UNDO TABLESPACE
  • 不支援指定相對路徑,只支援絕對路徑,且必須是innodb_directories引數定義可識別的路徑或預設的資料目錄下
  • 動態建立的undo表空間必須以.ibu結尾

3. 8.0.23 之前Undo表空間初始大小依賴innodb_page_size的值設定,預設16K,初始檔案大小為10M,8.0.23 之後Undo表空間初始大小為16M,預設擴充套件大小單位為16M

2. UNDO 相關引數

2.1 引數含義

show variables like '%undo%';
+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| innodb_max_undo_log_size | 8589934592 |   
| innodb_undo_directory    | ./         |  
| innodb_undo_log_encrypt  | OFF        |
| innodb_undo_log_truncate | ON         |
| innodb_undo_tablespaces  | 2          |
+--------------------------+------------+

show variables like '%truncate%';
+--------------------------------------+-------+
| Variable_name                        | Value |
+--------------------------------------+-------+
| innodb_purge_rseg_truncate_frequency | 128   |
| innodb_undo_log_truncate             | ON    |
+--------------------------------------+-------+

show variables like '%segment%';
+-------------------------------+-----------+
| Variable_name                 | Value     |
+-------------------------------+-----------+
| innodb_rollback_segments      | 128       |
| innodb_segment_reserve_factor | 12.500000 |
+-------------------------------+-----------+

innodb_undo_log_truncate			-- 控制是否自動做UNDO的truncate收縮操作,預設為ON,只有為ON時,下面2個引數才生效
	innodb_max_undo_log_size		-- 控制UNDO做truncate收縮操作的閾值,當UNDO達到該值時才出發收縮操作
	innodb_purge_rseg_truncate_frequency 
		-- Batch UNDO清理的次數,預設最大值128,也就是128次後才會觸發一次UNDO的truncate,而每次清理的undo page由innodb_purge_batch_size引數決定,innodb_purge_batch_size預設為300,也就是300*128個UNDO小批次清理後才會觸發UNDO表空間的truncate(也就是UNDO表空間的收縮)操作

innodb_undo_tablespaces 		  -- 控制生成的UNDO表空間的數量,預設2個,在8.0對該引數做了廢棄,但並未提供其他引數控制UNDO數量,當前依舊可以使用該引數做UNDO表空間數量設定,通常建議設定為3(手工收縮UNDO時需要至少3個UNDO表空間)

innodb_rollback_segments			-- UNDO表空間回滾段的數量,預設為最大值128

3. UNDO 表空間運維

3.1 檢視UNDO的基本資訊

-- 可以檢視到undo的表空間名稱/檔案路徑/初始大小/擴充套件大小/磁碟檔案大小/可用空間及是否啟用的狀態等
SELECT T1.SPACE AS SPACE_ID,
       T1.NAME AS TABLESPACE_NAME,
       T2.FILE_NAME,
       ROUND(T2.INITIAL_SIZE / 1024 / 1024, 2) AS "INITIAL_SIZE(M)",
       ROUND(T2.AUTOEXTEND_SIZE / 1024 / 1024, 2) AS "AUTOEXTEND_SIZE(M)",
       ROUND(T1.FILE_SIZE / 1024 / 1024, 2) AS "FILE_SIZE_DISK(M)",
       ROUND(T2.DATA_FREE / 1024 / 1024, 2) AS "DATA_FREE(M)",
       T2.STATUS,
       T1.STATE
  FROM INFORMATION_SCHEMA.INNODB_TABLESPACES T1,
       INFORMATION_SCHEMA.FILES              T2
 WHERE T1.SPACE = T2.FILE_ID
   AND T1.ROW_FORMAT = 'Undo';

3.2 新增/active/inactive/刪除UNDO表空間

CREATE UNDO TABLESPACE

• 用來建立新的UNDO 表空間

DROP UNDO TABLESPACE

• 用來刪除UNDO 表空間

ALTER UNDO TABLESPACE xxxx SET ACTIVE

• 用來啟用UNDO的使用

ALTER UNDO TABLESPACE xxxx SET INACTIVE

• 用來關閉UNDO的使用(關閉後的UNDO才可刪除)

-- 建立一個新的UNDO表空間
CREATE UNDO TABLESPACE undo_004 ADD DATAFILE 'undo_004.ibu';

-- 可以用前面的命令檢視建立後的狀態

-- 可以將已有的UNDO表示為inactive(也可理解為UNDO表空間收縮)
-- PS:設定為INACTIVE的表空間的STATE為empty,表示這個表空間不包含任何事務回滾資料,且表空間也收縮為預設大小
ALTER UNDO TABLESPACE undo_003 SET INACTIVE;

-- 可以將inactive的UNDO轉為active
ALTER UNDO TABLESPACE innodb_undo_001 SET ACTIVE;

-- 可以將inactive的UNDO表空間進行刪除
-- PS:預設以innodb_開頭初始化的undo表空間不可被刪除
DROP UNDO TABLESPACE innodb_undo_001;
ERROR: 3119 (42000): InnoDB: Tablespace names starting with `innodb_` are reserved.

-- 非系統預設的UNDO在inactive後可被刪除
ALTER UNDO TABLESPACE undo_003 SET ACTIVE;
Query OK, 0 rows affected (0.0030 sec)

3.3 影響UNDO inactive(truncate)效能的因素

• UNDO 表空間的大小
• UNDO 表空間的數量
• UNDO LOGS的數量(實際INSERT/UPDATE/DELETE這類事務回滾段的資料量)
• 磁碟IO的能力/當前系統的負載
• 是否存在長事務在使用該UNDO表空間

PS:通常對錶空間做收縮前最簡單避免效能的方式是提前建立一個UNDO表空間,收縮完後再刪除或一直保留均可

4. UNDO 的監控

4.1 UNDO的監控指標

-- 可以使用以下命令開啟對UNDO的監控採集
SET GLOBAL innodb_monitor_enable=module_undo;
SET GLOBAL innodb_monitor_enable=module_purge;

-- 使用該命令檢視UNDO truncate的次數及耗時等資訊
SELECT NAME,SUBSYSTEM,COUNT,STATUS,COMMENT 
FROM INFORMATION_SCHEMA.INNODB_METRICS 
WHERE NAME LIKE '%truncate%';

4.2 UNDO的狀態值

SHOW STATUS LIKE 'Innodb_undo_tablespaces%';
+----------------------------------+-------+
| Variable_name                    | Value |
+----------------------------------+-------+
| Innodb_undo_tablespaces_total    | 4     |  -- 總共的UNDO表空間數量
| Innodb_undo_tablespaces_implicit | 2     |  -- 這裡implicit其實表示的初始化建立的預設UNDO表空間個數,這種UNDO不可被刪除
| Innodb_undo_tablespaces_explicit | 2     |  -- 這裡explicit其實表示手工顯式建立的UNDO表空間的個數
| Innodb_undo_tablespaces_active   | 4     |	-- 表示處於active的UNDO表空間的個數,可以看到當前和total一樣,說明都在使用
+----------------------------------+-------+

5. UNDO 大小對並行數的限制

5.1 UNDO 記錄的型別及大小

UNDO LOGS包含的是事務最後一次修改的聚簇索引記錄(MySQL是聚簇索引表,也就是包含了一行完整的記錄)

• 當innodb_page_size 為16KB預設值時,undo 的slot槽為1024個
  • 16KB*1024/16=1024個槽

UNDO一共有以下4中紀錄檔型別

• INSERT 使用者自定義的表
• UPDATE and DELETE 使用者自定義的表
• INSERT 自定義的臨時表
• UPDATE and DELETE 自定義的臨時表

5.2 UNDO各場景下支援的讀寫並行

5.2.1 場景1: 每個事務都執行一個INSERT or UPDATE(DELETE)

並行公式: (innodb_page_size / 16) * innodb_rollback_segments * number of undo tablespaces

select 16*1024/16*128*2;
+------------------+
| 16*1024/16*128*2 |
+------------------+
|      262144.0000 |
+------------------+

5.2.2 場景2: 每個事務都執行一個INSERT and UPDATE(DELETE)

並行公式:(innodb_page_size / 16 / 2) * innodb_rollback_segments * number of undo tablespaces

select 16*1024/16/2*128*2;
+--------------------+
| 16*1024/16/2*128*2 |
+--------------------+
|    131072.00000000 |
+--------------------+

5.2.3 場景3: 每個事務都執行一個INSERT or UPDATE(DELETE) 到臨時表

並行公式: (innodb_page_size / 16) * innodb_rollback_segments

select 16*1024/16*128;
+----------------+
| 16*1024/16*128 |
+----------------+
|    131072.0000 |
+----------------+

5.2.4 場景4: 每個事務都執行一個INSERT and UPDATE(DELETE) 到臨時表

並行公式:(innodb_page_size / 16 / 2) * innodb_rollback_segments

select 16*1024/16/2*128;
+------------------+
| 16*1024/16/2*128 |
+------------------+
|   65536.00000000 |
+------------------+

6. 參考連結

• https://dev.mysql.com/doc/refman/8.0/en/innodb-undo-logs.html

• https://dev.mysql.com/doc/refman/8.0/en/innodb-undo-tablespaces.html