mysql undo文件占用空间很大 mysql undo表空间

转载

漫步云端的猪 2023-12-09 21:29:37

文章标签 mysql undo文件占用空间很大 oracle 数据库 java mysql 文章分类 MySQL 数据库

背景介绍

项目选型中，在KVM(16c 16G ssd160G )的 Linux7.6系统上部署了MySQL MGR集群 (GreatSQL 8.0.25)。

使用 sysbench 创建了100仓数据，且针对表创建为 partition 表，进行连续12小时的稳定下压测，来评估对应的架构能支撑的业务并发数，以及最高的TPS/QPS是多少。

在使用256并发连续进行12小时压测后，发现节点的SSD磁盘空间使用率达到 95% 以上，当时第一时间查看log目录，发现log目录已经达到 100G+，以为是 binlog 设置的时间太长导致的 binlog 没有及时清理造成的，去清理 binlogbinlog 过期时间设置的 1800s，实际 binlog 和 MGR 的 relay-group 空间占用在11G左右而 du -sh * 查看到的日志文件大小时，发现其中undo大小1个是71G另一个4.1G，且MGR的3个节点的undo均是这个情况，急需释放空间。

但是MySQL8.0是否支持类似Oracle似的undo在线的替换来进行收缩呢？答案是肯定的，而且和Oracle有些类似。

Oracle/MySQL undo表空间设置自动扩展，如果业务上有跑批量或者大表的DML操作时，引起大事物或针对多张大表关联更新时间较长，可能短时间内会将undo"撑大"，Oracle可通过创建一个新的undo，通过在线替换的方式，将膨胀的undo使用drop删除以释放空间。

MySQL 8.0同样可以使用这种方式来处理，因大事物或长事物引起的undo过大占用空间较多的情况。

方法如下

1、添加新的undo文件undo003。mysql8.0中默认innodb_undo_tablespace为2个，不足2个时，不允许设置为inactive，且默认创建的undo受保护，不允许删除。

2、将膨胀的 undo 临时设置为inactive，以及 innodb_undo_log_truncate=on，自动 truncate 释放膨胀的undo空间。

3、重新将释放空间之后的undo设置为active，可重新上线使用。

具体操作如下：

[greatdb@mysql ~]$ mysql -ugreatsql -pgreatsql -h172.16.130.15 -P3307
mysql[(none)]> show variables like '%undo%';
+--------------------------+-----------------------------+
| Variable_name | Value |
+--------------------------+-----------------------------+
| innodb_max_undo_log_size | 4294967296 |
| innodb_undo_directory | /app/dbdata/sqlnode3306/log |
| innodb_undo_log_encrypt | OFF |
| innodb_undo_log_truncate | ON |
| innodb_undo_tablespaces | 2 |
+--------------------------+-----------------------------+

5 rows in set (0.01 sec)

1.查看undo大小

mysql[(none)]> system du -sh /app/dbdata/datanode3307/log/undo*

2、添加新的undo表空间undo003。系统默认是2个undo，大小设置4G

mysql[(none)]> create undo tablespace undo003 add datafile '/app/dbdata/datanode3307/log/undo003.ibu';
mysql[(none)]> create undo tablespace undo003 add datafile '/app/dbdata/datanode3307/log/undo_003.' ;

3、查看系统中的undo表空间信息，如下：

mysql[(none)]> select * from information_schema.INNODB_TABLESPACES where name like '%undo%';

4、查看到上述视图中 innodb_undo_002 大小达到76067897344 （约71G）其状态state为active。手动将其设置为 inactive，使其自动触发 innodb_undo_log_truncate 回收。

mysql[(none)]> alter undo tablespace innodb_undo_002 set inactive;

5、查看对应视图如下

mysql[(none)]> select * from information_schema.INNODB_TABLESPACES where name like '%undo%';

此时可以查看对应操作系统目录中的 undo_002大小，innodb_undo_002 FILE_SIZE 16777216 默认大小 2179072 ，STATE 为 empty

mysql[(none)]> system du -sh /app/dbdata/datanode3307/log/undo*

6、重新将其设置为active状态

mysql[(none)]> alter undo tablespace innodb_undo_002 set active;
mysql[(none)]> select * from information_schema.INNODB_TABLESPACES where name like '%undo%';

7、有人说，为什么不能直接针对膨胀的undo设置为inactive，系统默认创建的undo表空间默认2个，处于active小于2个时，会有如下提示：

mysql[(none)]> mysql[(none)]> show variables like 'innodb_undo_tablespaces';
+--------------------------+-----------------------------+
| Variable_name | Value |
+--------------------------+-----------------------------+
| innodb_undo_tablespaces | 2 |
+--------------------------+-----------------------------+
5 rows in set (0.01 sec)
mysql[(none)]> alter undo tablespace innodb_undo_002 set inactive;
ERROR 3655 (HY000): Cannot set innodb_undo_002 inactive since there would be less than 2 undo tablespaces left active.

8、新创建添加的可以正常设置为inactive之后，使用drop方式删除，如下：

mysql[(none)]> alter undo tablespace undo003 set inactive;
mysql[(none)]> drop undo tablespace undo003;
mysql[(none)]> select * from information_schema.INNODB_TABLESPACES where name like '%undo%';