MySQL8.0 Undo Tablespace管理详解

MySQL8.0 Undo Tablespace管理详解

目录1. UNDO 基础概念2. UNDO 相关参数2.1 参数含义3. UNDO 表空间运维3.1 查看UNDO的基本信息3.2 添加/active/inactive/删除UNDO表空间3.3 影响UNDO inactive(truncate)性能的因素4. UNDO 的监控4.1 UNDO的监控指标4.2 UNDO的状态值5. UNDO 大小对并发数的限制5.1 UNDO 记录的类型及大小5.2 UNDO各场景下支持的读写并发5.2.1 场景1: 每个事务都执行一个INSERT or UPDATE(DELETE)5.2.2 场景2: 每个事务都执行一个INSERT and UPDATE(DELETE)5.2.3 场景3: 每个事务都执行一个INSERT or UPDATE(DELETE) 到临时表5.2.4 场景4: 每个事务都执行一个INSERT and UPDATE(DELETE) 到临时表6. 参考链接

1. UNDO 基础概念

默认至少初始化2个Undo表空间,最大支持127个Undo表空间,默认表空间名称为undo_001,undo_0028.0.14 之后UNDO表空间支持在线增加,及在线删除

CREATE UNDO TABLESPACE/DROP UNDO TABLESPACE不支持指定相对路径,只支持绝对路径,且必须是innodb_directories参数定义可识别的路径或默认的数据目录下动态创建的undo表空间必须以.ibu结尾

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转为activeALTER 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. 参考链接

到此这篇关于MySQL8.0 Undo Tablespace管理详解的文章就介绍到这了,更多相关MySQL8.0 Undo Tablespace内容请搜索以前的文章或继续浏览下面的相关文章希望大家以后多多支持！

您可能感兴趣的文章:mysql日志文件之undo log和redo logMySQL事务日志(redo log和undo log)的详细分析MySQL回滚日志(undo log)的作用和使用详解Mysql中undo、redo与binlog的区别浅析MySQL中的redo log和undo log日志详解MySQL系列之redo log、undo log和binlog详解详解MySQL 重做日志（redo log）与回滚日志（undo logo）