Mysql中怎么达成在线回收undo表空间
发布时间：2022-02-12 14:54:22 所属栏目：MySql教程 来源：互联网
导读：这期内容当中小编将会给大家带来有关Mysql中怎么实现在线回收undo表空间，文章内容丰富且以专业的角度为大家分析和叙述，阅读完这篇文章希望大家可以有所收获。 1 Mysql5.6 1.1 相关参数 MySQL 5.6增加了参数innodb_undo_directory、innodb_undo_logs和inn
|
这期内容当中小编将会给大家带来有关Mysql中怎么实现在线回收undo表空间，文章内容丰富且以专业的角度为大家分析和叙述，阅读完这篇文章希望大家可以有所收获。 1 Mysql5.6 1.1 相关参数 MySQL 5.6增加了参数innodb_undo_directory、innodb_undo_logs和innodb_undo_tablespaces这3个参数，可以把undo log从ibdata1移出来单独存放。 innodb_undo_directory：指定单独存放undo表空间的目录，默认为.（即datadir），可以设置相对路径或者绝对路径。该参数实例初始化之后虽然不可直接改动，但是可以通过先停库，修改配置文件，然后移动undo表空间文件的方式去修改该参数。 默认参数： mysql> show variables like '%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 ... undo001 undo002 undo003 innodb_rollback_segments：默认128个。每个回滚段可同时支持1024个在线事务。这些回滚段会平均分布到各个undo表空间中。该变量可以动态调整，但是物理上的回滚段不会减少，只是会控制用到的回滚段的个数。 1.2 使用 初始化实例之前，我们只需要设置innodb_undo_tablespaces参数（建议大于等于3）即可将undo log设置到单独的undo表空间中。如果需要将undo log放到更快的设备上时，可以设置innodb_undo_directory参数，但是一般我们不这么做，因为现在SSD非常普及。innodb_undo_logs可以默认为128不变。 undo log可以存储于ibdata之外。但这个特性依然鸡肋： 首先你必须在install实例的时候就指定好独立Undo tablespace, 在install完成后不可更改。 Undo tablepsace的space id必须从1开始，无法增加或者删除undo tablespace。 1.3 大事务测试 mysql> create table test.tbl( id int primary key auto_increment, name varchar(200)); Query OK, 0 rows affected (0.03 sec) mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> insert into test.tbl(name) values(repeat('1',00)); Query OK, 1 row affected (0.00 sec) mysql> insert into test.tbl(name) select name from test.tbl; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 ... mysql> insert into test.tbl(name) select name from test.tbl; Query OK, 2097152 rows affected (24.84 sec) Records: 2097152 Duplicates: 0 Warnings: 0 mysql> commit; Query OK, 0 rows affected (7.90 sec) 观察undolog已经开始膨胀了！事务commit后空间也没有回收。 $ du -sh undo* 10M undo001 69M undo002 10M undo003 2 Mysql5.7 5.7引入了在线truncate undo tablespace 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操作被唤起多少次之后才释放rollback segments。当undo表空间里面的rollback segments被释放时，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> show global variables like '%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> create table test.tbl( id int primary key auto_increment, name varchar(200)); Query OK, 0 rows affected (0.03 sec) mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> insert into test.tbl(name) values(repeat('1',00)); Query OK, 1 row affected (0.00 sec) mysql> insert into test.tbl(name) select name from test.tbl; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 ... mysql> insert into test.tbl(name) select name from test.tbl; Query OK, 2097152 rows affected (24.84 sec) Records: 2097152 Duplicates: 0 Warnings: 0 mysql> commit; Query OK, 0 rows affected (7.90 sec) undo表空间情况，膨胀到100MB+后成功回收 $ du -sh undo* 10M undo001 10M undo002 10M undo003 10M undo004 10M undo005 10M undo006 125M undo007 10M undo008 $ du -sh undo* 10M undo001 10M undo002 10M undo003 10M undo004 10M undo005 10M undo006 10M undo007 10M undo008 上述就是小编为大家分享的Mysql中怎么实现在线回收undo表空间了，如果刚好有类似的疑惑，不妨参照上述分析进行理解。 （编辑：昌吉站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
站长推荐