详解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 |
+——————+