【MySQL】测试undo表空间独立管理时在线收缩

MySQL5.7

初始化时my.cnf中要添加：innodb_undo_tablespaces = 3 ，如此才能开启undo独立表空间。

root (none)>show global variables like '%undo%';
+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| innodb_max_undo_log_size | 1073741824 |
| innodb_undo_directory    | ./         |
| innodb_undo_log_truncate | OFF        |
| innodb_undo_logs         | 128        |
| innodb_undo_tablespaces  | 0          |
+--------------------------+------------+
5 rows in set (0.01 sec)

[mysql@rhel75 data]$ du -sh ibdata1
12M ibdata1
[mysql@rhel75 data]$ du -sh undo00*
10M undo001
10M undo002
10M undo003
[mysql@rhel75 data]$

先修改参数：

set global innodb_max_undo_log_size=60*1024*1024;
set global innodb_undo_log_truncate =ON;
set global innodb_purge_rseg_truncate_frequency=10;

innodb_purge_rseg_truncate_frequency：
Defines the frequency with which the purge system frees rollback segments in terms of the number of times that purge is invoked. An undo tablespace cannot be truncated until its rollback segments are freed. Normally, the purge system frees rollback segments once every 128 times that purge is invoked. The default value is 128. Reducing this value increases the frequency with which the purge thread frees rollback segments.
—值越小释放回滚段的频率越高。
根据调用清除的次数，定义清除系统释放回滚段的频率。在释放回滚段之前，不能截断undo表空间。通常，清洗系统每调用128次清洗就释放回滚段一次。默认值是128。减少此值会增加清除线程释放回滚段的频率。

root my1>show global variables like '%undo%';
+--------------------------+----------+
| Variable_name            | Value    |
+--------------------------+----------+
| innodb_max_undo_log_size | 62914560 |
| innodb_undo_directory    | ./       |
| innodb_undo_log_truncate | ON       |
| innodb_undo_logs         | 128      |
| innodb_undo_tablespaces  | 3        |
+--------------------------+----------+
5 rows in set (0.01 sec)

root my1>show global variables like '%truncate%';
+--------------------------------------+-------+
| Variable_name                        | Value |
+--------------------------------------+-------+
| innodb_purge_rseg_truncate_frequency | 10    |
| innodb_undo_log_truncate             | ON    |
+--------------------------------------+-------+
2 rows in set (0.00 sec)

测试生成大量undo：
（1）建百万大表t1
root my1>call p_test3();
Query OK, 1 row affected (18 min 28.20 sec)
问：为何插入速度这么慢？-----因为一插入一提交，而不是统一提交（set autocommit=0)。

（2）生成undo

root my1>update t1 set i=2;
Query OK, 999999 rows affected (5.60 sec)
Rows matched: 1000000  Changed: 999999  Warnings: 0

root my1>update t1 set i=1;
Query OK, 1000000 rows affected (5.80 sec)
Rows matched: 1000000  Changed: 1000000  Warnings: 0

root my1>update t1 set i=3;
Query OK, 1000000 rows affected (6.00 sec)
Rows matched: 1000000  Changed: 1000000  Warnings: 0

root my1>update t1 set i=4;
Query OK, 1000000 rows affected (6.34 sec)
Rows matched: 1000000  Changed: 1000000  Warnings: 0

[mysql@rhel75 data]$ du -sh undo00*
52M undo001
52M undo002
96M undo003
[mysql@rhel75 data]$ du -sh undo00*
52M undo001
52M undo002
16M undo003
[mysql@rhel75 data]$
—可以看到undo3个文件变大，当undo003大于60M时，会触发truncate操作。