mysql undo表空间大小_独立undo表空间在线回收

1.mysql5.5的版本上

InnoDB undo是放在系统表空间即ibdata file文件中，这样如果有比较大的事务(即需要生成大量undo的)，会撑大ibdata数据文件，

虽然空间可以重用，但文件大小不能更改。

关于回滚段的，只有这个主要的参数，用来设置多少个rollback segment。

mysql> show global variables like '%rollback_segment%';

+----------------------------+-------+

| Variable_name              | Value |

+----------------------------+-------+

| innodb_rollback_segments   | 128   |

+----------------------------+-------+

2.mysql 5.6的undo段回收

undo段的数据是放到共享表空间中,如果数据库有大量插入或者更新会不断写入大量的undo log.此时的undo log不能自动回收剩余的空间.mysql 5.6是在安装数据库之前修改undo的参数,将undo log从共享表空间分离出来,安装后就不能修改,mysql 5.7可以用动态参数进行调整.

(1).修改my.cnf启动参数文件

vim /etc/my.cnf

innodb_undo_directory = /data/mysql/mysql9001/undolog  --指定存放的目录，默认是数据目录

innodb_undo_logs = 128       --指定回滚段128K

innodb_undo_tablespaces = 4   --指定有多少个undo log文件

(2).初使化mysql

(3).重启mysql服务

3.mysql 5.7动态修改

声明:在线回收表空间的功能是建立再独立undo表空间的基础上的，如果还是用的系统共享表空间ibdata1，则不支持在线回收机制。

vim /data/mysql/mysql9001/my.cnf

innodb_undo_log_truncate           --参数设置为1,即开启在线回收(收缩)undo log日志文件,支持动态设置。

innodb_undo_tablespaces           --参数必须大于或等于2,即回收(收缩)一个undo log日志文件时,要保证另一个undo log是可用的。

innodb_undo_logs                        --回滚段的数量,至少大于等于3,默认128。

innodb_max_undo_log_size         --当超过这个阀值(默认是1G),会触发truncate回收(收缩)动作,truncate后空间缩小到10M。

innodb_purge_rseg_truncate_frequency    --控制回收(收缩)undo log的频率.undo log空间在它的回滚段没有得到释放之前不会收缩，

想要增加释放回滚区间的频率,就得降低innodb_purge_rseg_truncate_frequency设定值。

实验验证：

参数设置

update 230w条记录的一个表

观察error.log,回收了独立undo表空间

回收前的独立undo表空间文件的大小

回收后大小

注意事项：

undo的truncate主要由下面两个参数控制：innodb_purge_rseg_truncate_frequency，innodb_undo_log_truncate。

1. innodb_undo_log_truncate是开关参数。

2. innodb_purge_rseg_truncate_frequency默认128，表示purge undo轮询128次后，进行一次undo的truncate。

当设置innodb_undo_log_truncate=ON的时候， undo表空间的文件大小，如果超过了innodb_max_undo_log_size， 就会被truncate到初始大小，但有一个前提，就是表空间中的undo不再被使用。

其主要步骤如下：

1. 超过大小了之后，会被mark truncation，一次会选择一个

2. 选择的undo不能再分配新给新的事务

3. purge线程清理不再需要的rollback segment

4. 等所有的回滚段都释放了后，truncate操作，使其成为install db时的初始状态。

默认情况下， 是purge触发128次之后，进行一次rollback segment的free操作，然后如果全部free就进行一个truncate。

但mark的操作需要几个依赖条件需要满足：

1. 系统至少得有两个undo表空间，防止一个offline后，至少另外一个还能工作

2. 除了ibdata里的segment，还至少有两个segment可用

3. undo表空间的大小确实超过了设置的阈值