5分钟了解MySQL5.7的undo log在线收缩新特性

①首先我们需要调整my.cnf参数

1 2 3 4 5 6

innodb_undo_directory=/data/undolog innodb_undo_tablespaces=4 innodb_undo_logs=128 innodb_max_undo_log_size=1G innodb_purge_rseg_truncate_frequency innodb_undo_log_truncate=1

Warning:警告

其中innodb_undo_directory参数要在数据库初始化时就需要写入my.cnf，否则会报如下错误：

Expected to open 4 undo tablespaces but was able to find only 0 undo tablespaces. Set the innodb_undo_tablespaces parameter to the correct value and retry. Suggested value is 0

 

 

 

Part2:MySQL5.7在线回收undolog

①首先对一张100万行的表进行整表更新

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

mysql> show variables like 'innodb_undo%'; +--------------------------+---------------+ | Variable_name            | Value         | +--------------------------+---------------+ | innodb_undo_directory    | /data/undolog | | innodb_undo_log_truncate | ON            | | innodb_undo_logs         | 128           | | innodb_undo_tablespaces  | 4             | +--------------------------+---------------+ 4 rows in set (0.00 sec)     mysql> update helei set c1=1; Query OK, 1000000 rows affected (45.48 sec) Rows matched: 1000000  Changed: 1000000  Warnings: 0   mysql> update helei set c2=222; Query OK, 1000000 rows affected (43.25 sec) Rows matched: 1000000  Changed: 1000000  Warnings: 0   mysql> update helei set c4='heleiheleiheleiheleihel'; Query OK, 1000000 rows affected (10.28 sec) Rows matched: 1000000  Changed: 1000000  Warnings: 0

 

 

②注意undolog的日志大小

[root@HE3 undolog]# ls -lshrt

total 412M

128M -rw-r----- 1 mysql mysql 128M Sep 26 16:56 undo004

 76M -rw-r----- 1 mysql mysql  76M Sep 26 16:56 undo003

136M -rw-r----- 1 mysql mysql 136M Sep 26 16:56 undo001

 72M -rw-r----- 1 mysql mysql  72M Sep 26 16:56 undo002

 

③error.log日志

1 2 3 4 5 6 7 8

2016-09-26T23:51:06.062828Z 0 [Note] InnoDB: Truncating UNDO tablespace with space identifier 1 2016-09-26T23:51:06.159077Z 0 [Note] InnoDB: Completed truncate of UNDO tablespace with space identifier 1 2016-09-26T23:51:06.159101Z 0 [Note] InnoDB: Truncating UNDO tablespace with space identifier 2 2016-09-26T23:51:06.242355Z 0 [Note] InnoDB: Completed truncate of UNDO tablespace with space identifier 2 2016-09-26T23:51:06.242378Z 0 [Note] InnoDB: Truncating UNDO tablespace with space identifier 3 2016-09-26T23:51:06.313036Z 0 [Note] InnoDB: Completed truncate of UNDO tablespace with space identifier 3 2016-09-26T23:51:06.313060Z 0 [Note] InnoDB: Truncating UNDO tablespace with space identifier 4 2016-09-26T23:51:06.403003Z 0 [Note] InnoDB: Completed truncate of UNDO tablespace with space identifier 4

 

④观察物理文件

[root@HE3 undolog]# ls -lshrt

total 168M

76M -rw-r----- 1 mysql mysql 76M Sep 26 16:56 undo003

10M -rw-r----- 1 mysql mysql 10M Sep 26 16:56 undo001

10M -rw-r----- 1 mysql mysql 10M Sep 26 16:56 undo004

72M -rw-r----- 1 mysql mysql 72M Sep 26 16:56 undo002

可以看到超过100M设定值的undo日志已经回收，默认为10M，undo log空间得以释放

Part3:意义

该功能降低了磁盘的使用率，并且可以提高诸如Xtrabackup这类物理备份软件的备份速度。

 

——总结——

MySQL5.7的undo log在线回收功能，相比MySQL5.6更加灵活和实用，DBA们再也不必因为ibdata1文件越来越大，导致磁盘空间被占、物理备份越来越慢而头疼了。由于笔者的水平有限，编写时间也很仓促，文中难免会出现一些错误或者不准确的地方，不妥之处恳请读者批评指正。

原文转自：乐搏学院http://www.learnbo.com/front/article/cmsIndex