Mysql在线回收undo表空间实战
1 Mysql5.61.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 undo003innodb_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: 0mysql> commit;Query OK, 0 rows affected (7.90 sec)观察undolog已经开始膨胀了！事务commit后空间也没有回收。 ...