MySQL8.0 Undo Tablespace管理詳解

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

-- 可以檢視到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';

-- 建立一個新的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)

-- 可以使用以下命令開啟對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%';

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一樣,說明都在使用
+----------------------------------+-------+

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

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

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

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