mysql> select version() ;
+-------------------------------------------+
| version() |
+-------------------------------------------+
| 5.7.17_log |
+-------------------------------------------+
1 row in set (0.00 sec)
参数相关：
innodb_undo_log_truncate参数设置为1，即开启在线回收（收缩）undo log日志文件，支持动态设置。
innodb_undo_tablespaces参数必须大于或等于2，即回收（收缩）一个undo log日志文件时，要保证另一个undo log是可用的。
innodb_undo_logs: undo回滚段的数量， 至少大于等于35，默认128。
innodb_max_undo_log_size：当超过这个阀值（默认是1G），会触发truncate回收（收缩）动作，truncate后空间缩小到10M。
innodb_purge_rseg_truncate_frequency：控制回收（收缩）undo log的频率。undo log空间在它的回滚段没有得到释放之前不会收缩，
想要增加释放回滚区间的频率，就得降低innodb_purge_rseg_truncate_frequency设定值。
undo表空间日志的截断
innodb_undo_tablespaces：
另外一个表空间必须在线并且可用；表空间的数量是通过参数innodb_undo_tablespaces 来定义，默认值是0，
此参数只能在数据库初始化的时候设置，可以通过下面来查看此参数值值
mysql> SELECT @@innodb_undo_tablespaces;
+---------------------------+
| @@innodb_undo_tablespaces |
+---------------------------+
| 0 |
+---------------------------+
1 row in set (0.00 sec)
用于设定创建的undo表空间的个数，在mysql_install_db时初始化后，就再也不能被改动了；默认值为0，表示不独立设置undo的tablespace，
默认记录到ibdata中；否则，则在undo目录下创建这么多个undo文件，例如假定设置该值为4，那么就会创建命名为undo001~undo004的undo tablespace文件，
每个文件的默认大小为10M。修改该值会导致Innodb无法完成初始化，数据库无法启动，但是另两个参数可以修改；
调整参数innodb_undo_tablespaces
mysql> SELECT @@innodb_undo_tablespaces;
+---------------------------+
| @@innodb_undo_tablespaces |
+---------------------------+
| 0 |
+---------------------------+
1 row in set (0.00 sec)
innodb_undo_tablespaces=4
service mysqld restart
启动失败
报错日志：
2017-03-29T06:33:55.799819Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2017-03-29T06:33:55.844003Z 0 [ERROR] InnoDB: 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
2017-03-29T06:33:55.844025Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2017-03-29T06:33:56.444628Z 0 [ERROR] Plugin 'InnoDB' init function returned error.
2017-03-29T06:33:56.444665Z 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2017-03-29T06:33:56.444678Z 0 [ERROR] Failed to initialize plugins.
2017-03-29T06:33:56.444692Z 0 [ERROR] Aborting
原因：undo 表空间设置的数量必须在mysql初始化阶段 设置innodb_undo_log_truncate=4，初始化完成后，就不能从innodb_undo_log_truncate=0 再去调整。
必须在初始化前设置innodb_undo_log_truncate 此参数为非0，后期可以通过修改参数文件/etc/my.cnf，调整undo表空间的数量，然后重启
+---------------------------+
| @@innodb_undo_tablespaces |
+---------------------------+
| 4 |
+---------------------------+
1 row in set (0.00 sec)
mysql> set global innodb_undo_tablespaces=2;
ERROR 1238 (HY000): Variable 'innodb_undo_tablespaces' is a read only variable
--修改参数
vi /etc/my.cnf
innodb_undo_log_truncate=2
service mysqld restart
mysql> select @@innodb_undo_tablespaces;
+---------------------------+
| @@innodb_undo_tablespaces |
+---------------------------+
| 2 |
+---------------------------+
1 row in set (0.00 sec)
undo 表空间的位置控制：innodb_undo_directory
innodb_undo_directory=/var/lib/mysql/
mysql> select @@innodb_undo_directory;
+-------------------------+
| @@innodb_undo_directory |
+-------------------------+
| ./ |
+-------------------------+
1 row in set (0.00 sec)
定义了innodb 回滚段的数量，undo回滚段的数量， 至少大于等于35，默认128，当然128也是最大值
mysql> SELECT @@innodb_undo_logs;
+--------------------+
| @@innodb_undo_logs |
+--------------------+
| 128 |
+--------------------+
1 row in set (0.00 sec)
可以动态设置此变量innodb_undo_logs
mysql> SET GLOBAL innodb_undo_logs=128;
当innodb_undo_logs =35 and innodb_undo_tablespaces=2时 回滚段:
1、The first rollback segment always resides in the system tablespace (when undo tablespaces are present, this rollback segment is inactive)
2、Rollback segments 2 to 33 reside in the shared temporary tablespace (ibtmp1)
3、The 34th rollback segment resides in the first undo tablespace (if present)
4、The 35th rollback segment resides in the second undo tablespace (if present)
回滚段和表空间的关系是多对一的关系：如果回滚段的个数大于35，其余的回滚段以循环的方式放在undo 表空间
例如：你有两个undo 表空间，undo1 undo2，nnodb_undo_logs =37，那么undo1和undo2，每个表空间分配一个rollback 段
32 rollback segments are reserved for temporary table undo logs for transactions that modify temporary tables and related objects,
which means that the maximum number of rollback segments available for data-modifying transactions that generate undo records is 96.
With 96 available rollback segments, the limit on concurrent data-modifying transactions is 96K.
innodb_undo_log_truncate:
1、当undo 表空间超过innodb_max_undo_log_size 大小会标记为truncation，选择一个undo表空间进行截断， in a round-robin fashion ，避免两个表空间同时截断
2、回滚段在undo表空间是不活跃的，并且不会被新的事物所使用，现有事物使用的回滚段，允许完成。
3、清空、释放那些不在需要的回滚段
4、当所有undo表空间的回滚段释放，undo表空间会执行一个truncate 操作，undo表空间变为初始化大小值。
5、回滚段被重新激活，他们可以分配新的事物
打开undo表空间日志的截断功能：
mysql> SET GLOBAL innodb_undo_log_truncate=ON;
当你打开undo表空间截断功能后，当undo表空间超过innodb_max_undo_log_size设定的大小时，undo表空间就会发生 truncation，innodb_max_undo_log_size默认是1G
mysql> SELECT @@innodb_max_undo_log_size;
+----------------------------+
| @@innodb_max_undo_log_size |
+----------------------------+
| 1073741824 |
+----------------------------+
1 row in set (0.00 sec)
innodb_max_undo_log_size:
mysql> SET GLOBAL innodb_max_undo_log_size=2147483648;
innodb_purge_rseg_truncate_frequency:
加快undo表空间文件的truncate
undo 表空间一般不能直接truncate，需要在所有回滚段释放完后，才能truncate， purge system每128次释放一次回滚段，可以通过参数
innodb_purge_rseg_truncate_frequency 来加速释放回滚段，默认128是最大值
为了加快释放回滚段可以调整参数
SET GLOBAL innodb_purge_rseg_truncate_frequency=32;
The degree of performance degradation depends on a number of factors including:
Number of undo tablespaces
Number of undo logs
Undo tablespace size
Speed of the I/O susbsystem
Existing long running transactions
System load
测试undo truncate 功能
---安装数据库软件
rpm -ivh mysql-commercial-common-5.7.17-1.1.el7.x86_64.rpm
rpm -ivh mysql-commercial-libs-5.7.17-1.1.el7.x86_64.rpm
rpm -ivh mysql-commercial-client-5.7.17-1.1.el7.x86_64.rpm
rpm -ivh mysql-commercial-server-5.7.17-1.1.el7.x86_64.rpm
---修改参数文件
innodb_undo_directory=/var/lib/mysql
innodb_undo_logs=128
innodb_undo_log_truncate=1
innodb_purge_rseg_truncate_frequency=32
innodb_undo_tablespaces=4
innodb_max_undo_log_size=1G
---重新启动数据库
service mysqld restart；
----查看undo文件
[root@rac1 mysql]# ls -lrt
total 163912
-rw-r----- 1 mysql mysql 50331648 Mar 29 15:50 ib_logfile1
-rw-r----- 1 mysql mysql 56 Mar 29 15:50 auto.cnf
-rw------- 1 mysql mysql 1676 Mar 29 15:50 ca-key.pem
-rw-r--r-- 1 mysql mysql 1075 Mar 29 15:50 ca.pem
-rw------- 1 mysql mysql 1680 Mar 29 15:50 server-key.pem
-rw-r--r-- 1 mysql mysql 1079 Mar 29 15:50 server-cert.pem
-rw------- 1 mysql mysql 1676 Mar 29 15:50 client-key.pem
-rw-r--r-- 1 mysql mysql 1079 Mar 29 15:50 client-cert.pem
-rw-r--r-- 1 mysql mysql 452 Mar 29 15:50 public_key.pem
-rw------- 1 mysql mysql 1680 Mar 29 15:50 private_key.pem
drwxr-x--- 2 mysql mysql 8192 Mar 29 15:50 performance_schema
drwxr-x--- 2 mysql mysql 4096 Mar 29 15:50 mysql
drwxr-x--- 2 mysql mysql 8192 Mar 29 15:50 sys
-rw-r----- 1 mysql mysql 10485760 Mar 29 15:50 undo004
-rw-r----- 1 mysql mysql 10485760 Mar 29 15:50 undo003
-rw-r----- 1 mysql mysql 10485760 Mar 29 15:50 undo002
-rw-r----- 1 mysql mysql 10485760 Mar 29 15:50 undo001
-rw-r----- 1 mysql mysql 352 Mar 29 15:56 ib_buffer_pool
-rw------- 1 mysql mysql 7 Mar 29 15:56 mysql.sock.lock
srwxrwxrwx 1 mysql mysql 0 Mar 29 15:56 mysql.sock
-rw-r----- 1 mysql mysql 12582912 Mar 29 15:56 ibdata1
-rw-r----- 1 mysql mysql 50331648 Mar 29 15:56 ib_logfile0
-rw-r----- 1 mysql mysql 12582912 Mar 29 15:57 ibtmp1
可以看到有四个undo 表空间文件，初始化值为10M；
---建立测试数据表
user testcreate table t(id int,name char(20));
delimiter $$
SET AUTOCOMMIT = 0$$
create procedure test1()
begin
declare v_cnt decimal (10) default 0 ;
dd:loop
insert into t values(2,'ccccccccccccccccc');
set v_cnt = v_cnt+10 ;
if v_cnt = 10000 then leave dd;
end if;
end loop dd ;
commit；
end;$$
delimiter ;
call test1;
--为了测试回滚段大事物，关闭自动提交功能
set global AUTOCOMMIT = 0
mysql> select count(*) from t;
+----------+
| count(*) |
+----------+
| 1066367 |
+----------+
1 row in set (0.45 sec)
create table t2 as select * From t where 0=1;
---开始模拟大事物
insert into t2 select * from t;
insert into t2 select * from t;
insert into t2 select * from t;
---观察undo文件的增长情况
[root@rac1 mysql]# du -sm undo00*
12 undo001
21 undo002
33 undo003
37 undo004
[root@rac1 mysql]# du -sm undo00*
13 undo001
21 undo002
33 undo003
37 undo004
[root@rac1 mysql]# du -sm undo00*
13 undo001
21 undo002
33 undo003
37 undo004
[root@rac1 mysql]# du -sm undo00*
15 undo001
21 undo002
33 undo003
37 undo004
[root@rac1 mysql]# du -sm undo00*
15 undo001
21 undo002
33 undo003
37 undo004
[root@rac1 mysql]# du -sm undo00*
22 undo001
21 undo002
33 undo003
37 undo004
---为了验证触发undo截断功能，设置undo的最大为40m
mysql> select @@innodb_max_undo_log_size;
+----------------------------+
| @@innodb_max_undo_log_size |
+----------------------------+
| 1073741824 |
+----------------------------+
1 row in set (0.00 sec)
mysql> set global innodb_max_undo_log_size=40000000;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@innodb_max_undo_log_size;
+----------------------------+
| @@innodb_max_undo_log_size |
+----------------------------+
| 40000000 |
+----------------------------+
1 row in set (0.00 sec)
--不断的做插入数据的动作
insert into t2 select * from t;
[root@rac1 mysql]# du -sm undo00*
41 undo001
25 undo002
41 undo003
37 undo004
[root@rac1 mysql]# du -sm undo00*
41 undo001
25 undo002
41 undo003
37 undo004
[root@rac1 mysql]# du -sm undo00*
16 undo001
25 undo002
41 undo003
37 undo004
[root@rac1 mysql]# du -sm undo00*
16 undo001
25 undo002
16 undo003
37 undo004
[root@rac1 mysql]# du -sm undo00*
16 undo001
41 undo002
16 undo003
37 undo004
可以发现undo表空间有回收
888
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
