时间：2020-10-23 13:13:37 | 栏目：Mysql | 点击：次
1 Mysql5.6
1.1 相关参数
MySQL 5.6增加了参数innodb_undo_directory、innodb_undo_logs和innodb_undo_tablespaces这3个参数，可以把undo log从ibdata1移出来单独存放。
默认参数：
mysql> show variables like '%undo%'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | innodb_undo_directory | . | | innodb_undo_logs | 128 | | innodb_undo_tablespaces | 0 | +-------------------------+-------+
实例初始化是修改innodb_undo_tablespaces：
mysql_install_db ...... --innodb_undo_tablespaces $ ls ... undo001 undo002 undo003
1.2 使用
初始化实例之前，我们只需要设置innodb_undo_tablespaces参数（建议大于等于3）即可将undo log设置到单独的undo表空间中。如果需要将undo log放到更快的设备上时，可以设置innodb_undo_directory参数，但是一般我们不这么做，因为现在SSD非常普及。innodb_undo_logs可以默认为128不变。
undo log可以存储于ibdata之外。但这个特性依然鸡肋：
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 相关参数
必要条件：
启动参数：
2.2 清理过程
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
3 Reference
https://dev.mysql.com/doc/ref...
总结