欢迎来到 GreatSQL社区共享的MySQL技能文章，如有疑问或想学习的内容，能够在下方评论区留言，看到后会进行解答
- GreatSQL社区原创内容未经授权不得随意运用，转载请联系小编并注明来源。
布景介绍
在项目选型中，在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/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: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 74
Server version: 8.0.25-15 GreatSQL, Release 15, Revision c7feae175e0
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
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*
4.1G /app/dbdata/datanode3307/log/undo_001
71G /app/dbdata/datanode3307/log/undo_002 -----12小时接连稳定性压测，导致节点undo过大，到达71G
2、增加新的undo表空间undo003。体系默许是2个undo，巨细设置4G
mysql[(none)]>
mysql[(none)]> create undo tablespace undo003 add datafile '/app/dbdata/datanode3307/log/undo003.ibu';
Query OK, 0 rows affected (0.21 sec)
注意：创立增加新的undo有必要以.ibu结尾，否则触发如下错误提示
mysql[(none)]> create undo tablespace undo003 add datafile '/app/dbdata/datanode3307/log/undo_003.' ;
ERROR 3121 (HY000): The ADD DATAFILE filepath must end with '.ibu'.
3、检查体系中的undo表空间信息，如下：
mysql[(none)]> select * from information_schema.INNODB_TABLESPACES where name like '%undo%';
+------------+-----------------+------+------------+-----------+---------------+------------+---------------+-------------+----------------+-----------------+----------------+---------------+------------+--------+
| SPACE | NAME | FLAG | ROW_FORMAT | PAGE_SIZE | ZIP_PAGE_SIZE | SPACE_TYPE | FS_BLOCK_SIZE | FILE_SIZE | ALLOCATED_SIZE | AUTOEXTEND_SIZE | SERVER_VERSION | SPACE_VERSION | ENCRYPTION | STATE |
+------------+-----------------+------+------------+-----------+---------------+------------+---------------+-------------+----------------+-----------------+----------------+---------------+------------+--------+
| 4294967279 | innodb_undo_001 | 0 | Undo | 16384 | 0 | Undo | 4096 | 4311744512 | 4311764992 | 0 | 8.0.25 | 1 | N | active |
| 4294967278 | innodb_undo_002 | 0 | Undo | 16384 | 0 | Undo | 4096 | 76067897344 | 76068229120 | 0 | 8.0.25 | 1 | N | active |
| 4294967277 | undo003 | 0 | Undo | 16384 | 0 | Undo | 4096 | 16777216 | 16777216 | 0 | 8.0.25 | 1 | N | active |
+------------+-----------------+------+------------+-----------+---------------+------------+---------------+-------------+----------------+-----------------+----------------+---------------+------------+--------+
3 rows in set (0.03 sec)
4、检查到上述视图中 innodb_undo_002 巨细到达76067897344 （约71G）其状况state为active。手动将其设置为 inactive，使其主动触发 innodb_undo_log_truncate 回收。
mysql[(none)]> alter undo tablespace innodb_undo_002 set inactive;
Query OK, 0 rows affected (0.00 sec)
5、检查对应视图如下
mysql[(none)]> select * from information_schema.INNODB_TABLESPACES where name like '%undo%';
+------------+-----------------+------+------------+-----------+---------------+------------+---------------+------------+----------------+-----------------+----------------+---------------+------------+--------+
| SPACE | NAME | FLAG | ROW_FORMAT | PAGE_SIZE | ZIP_PAGE_SIZE | SPACE_TYPE | FS_BLOCK_SIZE | FILE_SIZE | ALLOCATED_SIZE | AUTOEXTEND_SIZE | SERVER_VERSION | SPACE_VERSION | ENCRYPTION | STATE |
+------------+-----------------+------+------------+-----------+---------------+------------+---------------+------------+----------------+-----------------+----------------+---------------+------------+--------+
| 4294967279 | innodb_undo_001 | 0 | Undo | 16384 | 0 | Undo | 4096 | 4311744512 | 4311764992 | 0 | 8.0.25 | 1 | N | active |
| 4294967151 | innodb_undo_002 | 0 | Undo | 16384 | 0 | Undo | 4096 | 16777216 | 2179072 | 0 | 8.0.25 | 1 | N | empty |
| 4294967277 | undo003 | 0 | Undo | 16384 | 0 | Undo | 4096 | 16777216 | 16777216 | 0 | 8.0.25 | 1 | N | active |
+------------+-----------------+------+------------+-----------+---------------+------------+---------------+------------+----------------+-----------------+----------------+---------------+------------+--------+
3 rows in set (0.01 sec)
此时能够检查对应操作体系目录中的 undo_002巨细，innodb_undo_002 FILE_SIZE 16777216 默许巨细 2179072 ，STATE 为 empty
mysql[(none)]> system du -sh /app/dbdata/datanode3307/log/undo*
4.1G /app/dbdata/datanode3307/log/undo_001
2.1M /app/dbdata/datanode3307/log/undo_002
16M /app/dbdata/datanode3307/log/undo003.ibu
6、从头将其设置为active状况
mysql[(none)]> alter undo tablespace innodb_undo_002 set active;
Query OK, 0 rows affected (0.01 sec)
mysql[(none)]> select * from information_schema.INNODB_TABLESPACES where name like '%undo%';
+------------+-----------------+------+------------+-----------+---------------+------------+---------------+------------+----------------+-----------------+----------------+---------------+------------+--------+
| SPACE | NAME | FLAG | ROW_FORMAT | PAGE_SIZE | ZIP_PAGE_SIZE | SPACE_TYPE | FS_BLOCK_SIZE | FILE_SIZE | ALLOCATED_SIZE | AUTOEXTEND_SIZE | SERVER_VERSION | SPACE_VERSION | ENCRYPTION | STATE |
+------------+-----------------+------+------------+-----------+---------------+------------+---------------+------------+----------------+-----------------+----------------+---------------+------------+--------+
| 4294967279 | innodb_undo_001 | 0 | Undo | 16384 | 0 | Undo | 4096 | 4311744512 | 4311764992 | 0 | 8.0.25 | 1 | N | active |
| 4294967151 | innodb_undo_002 | 0 | Undo | 16384 | 0 | Undo | 4096 | 16777216 | 2195456 | 0 | 8.0.25 | 1 | N | active |
| 4294967277 | undo003 | 0 | Undo | 16384 | 0 | Undo | 4096 | 16777216 | 16777216 | 0 | 8.0.25 | 1 | N | active |
+------------+-----------------+------+------------+-----------+---------------+------------+---------------+------------+----------------+-----------------+----------------+---------------+------------+--------+
3 rows in set (0.01 sec)
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.
mysql[(none)]>
8、新创立增加的能够正常设置为inactive之后，运用drop方法删除，如下：
mysql[(none)]> alter undo tablespace undo003 set inactive;
Query OK, 0 rows affected (0.00 sec)
mysql[(none)]> drop undo tablespace undo003;
Query OK, 0 rows affected (0.01 sec)
mysql[(none)]> select * from information_schema.INNODB_TABLESPACES where name like '%undo%';
+------------+-----------------+------+------------+-----------+---------------+------------+---------------+------------+----------------+-----------------+----------------+---------------+------------+--------+
| SPACE | NAME | FLAG | ROW_FORMAT | PAGE_SIZE | ZIP_PAGE_SIZE | SPACE_TYPE | FS_BLOCK_SIZE | FILE_SIZE | ALLOCATED_SIZE | AUTOEXTEND_SIZE | SERVER_VERSION | SPACE_VERSION | ENCRYPTION | STATE |
+------------+-----------------+------+------------+-----------+---------------+------------+---------------+------------+----------------+-----------------+----------------+---------------+------------+--------+
| 4294967279 | innodb_undo_001 | 0 | Undo | 16384 | 0 | Undo | 4096 | 4311744512 | 4311764992 | 0 | 8.0.25 | 1 | N | active |
| 4294967151 | innodb_undo_002 | 0 | Undo | 16384 | 0 | Undo | 4096 | 16777216 | 2244608 | 0 | 8.0.25 | 1 | N | active |
+------------+-----------------+------+------------+-----------+---------------+------------+---------------+------------+----------------+-----------------+----------------+---------------+------------+--------+
2 rows in set (0.01 sec)
总结
经过以上操作咱们就能够针对unod因遇到大事务，undo持续增长的状况下，经过新增临时undo，手动开释体系默许的2个undo表空间 巨细。
当然截断 UNDO 表空间文件对数据库功能是有一定的影响的，尽量在相对空闲时刻进行。
当UNDO表空间被截断时，UNDO表空间中的回滚段将被停用。其他UNDO表空间中的活动回滚段负责整个体系负载，这或许会导致功能略有下降。功能受影响的程度取决于许多因素:
- 1、UNDO表空间的数量
- 2、UNDO记录日志的数据量
- 3、UNDO表空间巨细
- 4、磁盘I/O体系的速度
- 5、现有长期运转的事务
那么避免潜在功能影响的最简单的方法:
- 1、就是经过 create undo tablespace undo_XXX add datafile ‘/path/undo_xxx.ibu’;多增加几个UNDO表空间。
- 2、磁盘上如果条件允许选用高功能的SSD来存储数据，存储REDO,UNDO等。
- 引起UNDO过度胀大的原因大多数是因为根底数据量大，事务并发高，表相关操作较频频，出现大且长的事物操作，导致UNDO一直处于active状况，不能及时开释回滚段等原因，大事物引起的问题由来已久，即使咱们能规避99%的大事物，但实际事务遇到那1%的大事物刚性需求发过来时，这还要咱们的MySQL各种场景，各种架构和事务层好好磨合磨合。
Enjoy GreatSQL :)
文章引荐：
GreatSQL MGR FAQ
mp.weixin.qq.com/s/J6wkUpGXw…
万答#12，MGR整个集群挂掉后，如何才干主动选主，不必手动干涉
mp.weixin.qq.com/s/07o1poO44…
『2021数据技能嘉年华ON LINE』：《MySQL高可用架构演进及实践》
mp.weixin.qq.com/s/u7k99y6i7…
一条sql语句慢在哪之抓包分析
mp.weixin.qq.com/s/AYibbzl86…
万答#15，都有哪些状况或许导致MGR服务无法发动
mp.weixin.qq.com/s/inSGpd0Q_…
技能共享 | 为什么MGR一致性模式不引荐AFTER
mp.weixin.qq.com/s/rNeq479RN…
关于 GreatSQL
GreatSQL是由万里数据库保护的MySQL分支，专注于提高MGR可靠性及功能，支撑InnoDB并行查询特性，是适用于金融级应用的MySQL分支版别。
Gitee:
gitee.com/GreatSQL/Gr…
GitHub:
github.com/GreatSQL/Gr…
Bilibili：
space.bilibili.com/1363850082/…
微信&QQ群：
可搜索增加GreatSQL社区助手微信老友，发送验证信息“加群”加入GreatSQL/MGR交流微信群
QQ群：533341697
微信小助手：wanlidbc
本文由博客一文多发渠道 OpenWrite 发布！
