Mysql在线回收undo表空间实战记录
1Mysql5.6
1.1相关参数
MySQL5.6增加了参数innodb_undo_directory、innodb_undo_logs和innodb_undo_tablespaces这3个参数,可以把undolog从ibdata1移出来单独存放。
- innodb_undo_directory:指定单独存放undo表空间的目录,默认为.(即datadir),可以设置相对路径或者绝对路径。该参数实例初始化之后虽然不可直接改动,但是可以通过先停库,修改配置文件,然后移动undo表空间文件的方式去修改该参数。
默认参数:
mysql>showvariableslike'%undo%'; +-------------------------+-------+ |Variable_name|Value| +-------------------------+-------+ |innodb_undo_directory|.| |innodb_undo_logs|128| |innodb_undo_tablespaces|0| +-------------------------+-------+
- innodb_undo_tablespaces:指定单独存放的undo表空间个数,例如如果设置为3,则undo表空间为undo001、undo002、undo003,每个文件初始大小默认为10M。该参数我们推荐设置为大于等于3,原因下文将解释。该参数实例初始化之后不可改动
实例初始化是修改innodb_undo_tablespaces:
mysql_install_db......--innodb_undo_tablespaces $ls ... undo001undo002undo003
- innodb_rollback_segments:默认128个。每个回滚段可同时支持1024个在线事务。这些回滚段会平均分布到各个undo表空间中。该变量可以动态调整,但是物理上的回滚段不会减少,只是会控制用到的回滚段的个数。
1.2使用
初始化实例之前,我们只需要设置innodb_undo_tablespaces参数(建议大于等于3)即可将undolog设置到单独的undo表空间中。如果需要将undolog放到更快的设备上时,可以设置innodb_undo_directory参数,但是一般我们不这么做,因为现在SSD非常普及。innodb_undo_logs可以默认为128不变。
undolog可以存储于ibdata之外。但这个特性依然鸡肋:
- 首先你必须在install实例的时候就指定好独立Undotablespace,在install完成后不可更改。
- Undotablepsace的spaceid必须从1开始,无法增加或者删除undotablespace。
1.3大事务测试
mysql>createtabletest.tbl(idintprimarykeyauto_increment,namevarchar(200)); QueryOK,0rowsaffected(0.03sec) mysql>starttransaction; QueryOK,0rowsaffected(0.00sec) mysql>insertintotest.tbl(name)values(repeat('1',00)); QueryOK,1rowaffected(0.00sec) mysql>insertintotest.tbl(name)selectnamefromtest.tbl; QueryOK,1rowaffected(0.00sec) Records:1Duplicates:0Warnings:0 ... mysql>insertintotest.tbl(name)selectnamefromtest.tbl; QueryOK,2097152rowsaffected(24.84sec) Records:2097152Duplicates:0Warnings:0 mysql>commit; QueryOK,0rowsaffected(7.90sec)
观察undolog已经开始膨胀了!事务commit后空间也没有回收。
$du-shundo* 10Mundo001 69Mundo002 10Mundo003
2Mysql5.7
5.7引入了在线truncateundotablespace
2.1相关参数
必要条件:
- innodb_undo_tablespaces:最少有两个,这样一个在清理的时候可以使用另一个,该参数实例初始化之后不可改动
- innodb_rollback_segments:回滚段的个数,总会有一个回滚段分配给系统表空间,32个保留给临时表空间。所以如果想使用undo表空间的话,这个值要至少为33。例如使用两个undo表空间,这个值就配35。如果设置多个undo表空间,系统表空间中的回滚段会变成非活跃状态。
启动参数:
- innodb_undo_log_truncate=on
- innodb_max_undo_log_size:超过这个值的表空间会标记为truncate,动态参数默认是1G
- innodb_purge_rseg_truncate_frequency:指定purge操作被唤起多少次之后才释放rollbacksegments。当undo表空间里面的rollbacksegments被释放时,undo表空间才会被truncate。由此可见,该参数越小,undo表空间被尝试truncate的频率越高。
2.2清理过程
- undo表空间大小超过innodb_max_undo_log_size后,标记该表空间需要清理。标记会循环进行,避免一个表空间被反复清理。
- 标记表空间内的回滚段变为非活跃状态,正在运行的事务等待执行完。
- 开始purge
- 释放undo表空间中的所有回滚段后,运行truncate并将undo表空间截断为其初始大小,初始大小由innodb_page_size决定,默认16KB的大小对应表空间为10MB
- 重新激活回滚段,以便将它们分配给新事务
2.3性能建议
truncate表空间时避免影响性能的最简单方法是增加撤消表空间的数量
2.4大事务测试
配置8个undo表空间,innodb_purge_rseg_truncate_frequency=10
mysqld--initialize...--innodb_undo_tablespaces=8
开始测试
mysql>showglobalvariableslike'%undo%'; +--------------------------+------------+ |Variable_name|Value| +--------------------------+------------+ |innodb_max_undo_log_size|1073741824| |innodb_undo_directory|./| |innodb_undo_log_truncate|ON| |innodb_undo_logs|128| |innodb_undo_tablespaces|8| +--------------------------+------------+ mysql>select@@innodb_purge_rseg_truncate_frequency; +----------------------------------------+ |@@innodb_purge_rseg_truncate_frequency| +----------------------------------------+ |10| +----------------------------------------+ select@@innodb_max_undo_log_size; +----------------------------+ |@@innodb_max_undo_log_size| +----------------------------+ |10485760| +----------------------------+ mysql>createtabletest.tbl(idintprimarykeyauto_increment,namevarchar(200)); QueryOK,0rowsaffected(0.03sec) mysql>starttransaction; QueryOK,0rowsaffected(0.00sec) mysql>insertintotest.tbl(name)values(repeat('1',00)); QueryOK,1rowaffected(0.00sec) mysql>insertintotest.tbl(name)selectnamefromtest.tbl; QueryOK,1rowaffected(0.00sec) Records:1Duplicates:0Warnings:0 ... mysql>insertintotest.tbl(name)selectnamefromtest.tbl; QueryOK,2097152rowsaffected(24.84sec) Records:2097152Duplicates:0Warnings:0 mysql>commit; QueryOK,0rowsaffected(7.90sec)
undo表空间情况,膨胀到100MB+后成功回收
$du-shundo*
10M undo001
10M undo002
10M undo003
10M undo004
10M undo005
10M undo006
125M undo007
10M undo008$du-shundo*
10M undo001
10M undo002
10M undo003
10M undo004
10M undo005
10M undo006
10M undo007
10M undo008
3Reference
https://dev.mysql.com/doc/ref...
总结
以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,谢谢大家对毛票票的支持。