MySQL 5.7在线收缩undo表空间

IT那活儿 2023-01-16

1350

点击上方“IT那活儿”公众号，关注后了解更多内容，不管IT什么活儿，干就完了！！！

通过建立知识库管理，积累、保存信息和知识资产，为团队内部信息和知识的传播提供平台，实现团队内知识共享，也使得企业知识得到很好的精炼和沉淀。

知识点描述

在mysql较早的版本中，undo是个较为头疼的问题，空间急剧增大，却不能得到释放，在mysql5.7中引入了新的参数，innodb_undo_log_truncate，开启后可在线收缩拆分出来的undo表空间。

在满足以下2个条件下，undo表空间文件可在线收缩：

1）innodb_undo_tablespaces>=2
因为truncate undo表空间时，该文件处于inactive状态，如果只有1个undo表空间，那么整个系统在此过程中将处于不可用状态。为了尽可能降低truncate对系统的影响，建议将该参数最少设置为3；
2）innodb_undo_logs>=35（默认128）
因为在MySQL 5.7中，第一个undo log永远在系统表空间中，另外32个undo log分配给了临时表空间，即ibtmp1，至少还有2个undo log才能保证2个undo表空间中每个里面至少有1个undo log；

满足以上2个条件后，把innodb_undo_log_truncate设置为ON即可开启undo表空间的自动truncate，这还跟如下2个参数有关：

1）innodb_max_undo_log_size
undo表空间文件超过此值即标记为可收缩，默认1G，可在线修改；
2）innodb_purge_rseg_truncate_frequency
指定purge操作被唤起多少次之后才释放rollback segments。当undo表空间里面的rollback segments被释放时，undo表空间才会被truncate。由此可见，该参数越小，undo表空间被尝试truncate的频率越高。

基本也就是InnoDB的purge线程，会根据innodb_undo_log_truncate开关的设置，和innodb_max_undo_log_size设置的文件大小阈值，以及truncate的频率来进行空间回收和rollback segment的重新初始化。

标准指导操作

1. MySQL 5.7关于undo log参数如下

mysql> show global variables like '%undo%';
+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| innodb_max_undo_log_size | 1073741824 |
| innodb_undo_directory | ./ |
| innodb_undo_log_truncate | OFF |
| innodb_undo_logs | 128        |
| innodb_undo_tablespaces | 0 |
+--------------------------+------------+
5 rows in set (0.00 sec)
 
mysql> show global variables like '%truncate%'; 
+--------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------+-------+
| innodb_purge_rseg_truncate_frequency | 128 |
| innodb_undo_log_truncate | OFF |
+--------------------------------------+-------+
2 rows in set (0.01 sec)

innodb_undo_log_truncate
参数设置为1，即开启在线回收（收缩）undo log日志文件，支持动态设置。
innodb_undo_tablespaces
参数必须大于或等于2，即回收（收缩）一个undo log日志文件时，要保证另一个undo log是可用的。
innodb_undo_logs
undo回滚段的数量，至少大于等于35，默认128。
innodb_max_undo_log_size
当超过这个阀值（默认是1G），会触发truncate回收（收缩）动作，truncate后空间缩小到10M。
innodb_undo_directory
undo文件存放的位置。
innodb_purge_rseg_truncate_frequency
控制回收（收缩）undo log的频率，undo log空间在它的回滚段没有得到释放之前不会收缩，想要增加释放回滚区间的频率，就得降低innodb_purge_rseg_truncate_frequency设定值。

2. MySQL5.7中undo表空间的truncate实例

1）首先设置如下参数

# 为了实验方便,我们减小该值;
innodb_max_undo_log_size = 100M
innodb_undo_log_truncate = ON
innodb_undo_logs = 128  
innodb_undo_tablespaces = 3
# 为了实验方便,我们减小该值;
innodb_purge_rseg_truncate_frequency = 10

2）创建表

mysql> create table t1(id int primary key auto_increment,name varchar(200));

3）插入测试数据

mysql> insert into t1(name) values(repeat('a',200));
Query OK, 1 row affected (0.01 sec)
 
mysql> insert into t1(name) select name from t1;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
 
mysql> insert into t1(name) select name from t1;
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
 
mysql> insert into t1(name) select name from t1;
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
 
...
 
mysql> insert into t1(name) select name from t1;
Query OK, 8388608 rows affected (2 min 11.31 sec)
Records: 8388608 Duplicates: 0 Warnings: 0

这时undo表空间文件大小如下，可以看到有一个undo文件已经超过了100M。

-rw-r----- 1 mysql mysql 13M Feb 17 17:59 undo001
-rw-r----- 1 mysql mysql 128M Feb 17 17:59 undo002
-rw-r----- 1 mysql mysql 64M Feb 17 17:59 undo003

此时，为了，让purge线程运行，可以运行几个delete语句：

mysql> delete from t1 limit 1;
Query OK, 1 row affected (0.00 sec)
 
mysql> delete from t1 limit 1;
Query OK, 1 row affected (0.00 sec)
 
mysql> delete from t1 limit 1;
Query OK, 1 row affected (0.00 sec)
 
mysql> delete from t1 limit 1;
Query OK, 1 row affected (0.00 sec)

再查看undo文件大小：

-rw-r----- 1 mysql mysql 13M Feb 17 18:05 undo001
-rw-r----- 1 mysql mysql 10M Feb 17 18:05 undo002
-rw-r----- 1 mysql mysql 64M Feb 17 18:05 undo003

可以看到，超过100M的undo文件已经收缩到10M了。

相较于之前的版本，undo单独出来，并可以在线收缩，基本上可以满足日常需要，不会因为文件增大空间不足等问题。

END

本文作者：钟章龙(上海新炬中北团队）

本文来源：“IT那活儿”公众号

文章转载自IT那活儿，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

MySQL 5.7在线收缩undo表空间

本文作者：钟章龙(上海新炬中北团队）

本文来源：“IT那活儿”公众号

评论