• 企业400电话
  • 微网小程序
  • AI电话机器人
  • 电商代运营
  • 全 部 栏 目

    企业400电话 网络优化推广 AI电话机器人 呼叫中心 网站建设 商标✡知产 微网小程序 电商运营 彩铃•短信 增值拓展业务
    Mysql在线回收undo表空间实战记录

    1 Mysql5.6

    1.1 相关参数

    MySQL 5.6增加了参数innodb_undo_directory、innodb_undo_logs和innodb_undo_tablespaces这3个参数,可以把undo log从ibdata1移出来单独存放。

    默认参数:

    mysql> show variables like '%undo%';
    +-------------------------+-------+
    | Variable_name      | Value |
    +-------------------------+-------+
    | innodb_undo_directory  | .   |
    | innodb_undo_logs    | 128  |
    | innodb_undo_tablespaces | 0   |
    +-------------------------+-------+

    实例初始化是修改innodb_undo_tablespaces:

    mysql_install_db ...... --innodb_undo_tablespaces
    
    $ ls
    ...
    undo001 undo002 undo003

    1.2 使用

    初始化实例之前,我们只需要设置innodb_undo_tablespaces参数(建议大于等于3)即可将undo log设置到单独的undo表空间中。如果需要将undo log放到更快的设备上时,可以设置innodb_undo_directory参数,但是一般我们不这么做,因为现在SSD非常普及。innodb_undo_logs可以默认为128不变。

    undo log可以存储于ibdata之外。但这个特性依然鸡肋:

    1.3 大事务测试

    mysql> create table test.tbl( id int primary key auto_increment, name varchar(200));
    Query OK, 0 rows affected (0.03 sec)
    
    mysql> start transaction;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> insert into test.tbl(name) values(repeat('1',00));
    Query OK, 1 row affected (0.00 sec)
    
    mysql> insert into test.tbl(name) select name from test.tbl;
    Query OK, 1 row affected (0.00 sec)
    Records: 1 Duplicates: 0 Warnings: 0
    
    ...
    
    mysql> insert into test.tbl(name) select name from test.tbl;
    Query OK, 2097152 rows affected (24.84 sec)
    Records: 2097152 Duplicates: 0 Warnings: 0
    
    mysql> commit;
    Query OK, 0 rows affected (7.90 sec)

    观察undolog已经开始膨胀了!事务commit后空间也没有回收。

    $ du -sh undo*
    10M  undo001
    69M  undo002
    10M  undo003

    2 Mysql5.7

    5.7引入了在线truncate undo tablespace

    2.1 相关参数

    必要条件:

    启动参数:

    2.2 清理过程

    1. undo表空间大小超过innodb_max_undo_log_size后,标记该表空间需要清理。标记会循环进行,避免一个表空间被反复清理。
    2. 标记表空间内的回滚段变为非活跃状态,正在运行的事务等待执行完。
    3. 开始purge
    4. 释放undo表空间中的所有回滚段后,运行truncate并将undo表空间截断为其初始大小,初始大小由innodb_page_size决定,默认16KB的大小对应表空间为10MB
    5. 重新激活回滚段,以便将它们分配给新事务

    2.3 性能建议

    truncate表空间时避免影响性能的最简单方法是增加撤消表空间的数量

    2.4 大事务测试

    配置8个undo表空间,innodb_purge_rseg_truncate_frequency=10

    mysqld --initialize ... --innodb_undo_tablespaces=8

    开始测试

    mysql> show global variables like '%undo%';
    +--------------------------+------------+
    | Variable_name      | Value   |
    +--------------------------+------------+
    | innodb_max_undo_log_size | 1073741824 |
    | innodb_undo_directory  | ./     |
    | innodb_undo_log_truncate | ON     |
    | innodb_undo_logs     | 128    |
    | innodb_undo_tablespaces | 8     |
    +--------------------------+------------+
    
    mysql> select @@innodb_purge_rseg_truncate_frequency;
    +----------------------------------------+
    | @@innodb_purge_rseg_truncate_frequency |
    +----------------------------------------+
    |                   10 |
    +----------------------------------------+
    
    select @@innodb_max_undo_log_size;
    +----------------------------+
    | @@innodb_max_undo_log_size |
    +----------------------------+
    |          10485760 |
    +----------------------------+
    
    mysql> create table test.tbl( id int primary key auto_increment, name varchar(200));
    Query OK, 0 rows affected (0.03 sec)
    
    mysql> start transaction;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> insert into test.tbl(name) values(repeat('1',00));
    Query OK, 1 row affected (0.00 sec)
    
    mysql> insert into test.tbl(name) select name from test.tbl;
    Query OK, 1 row affected (0.00 sec)
    Records: 1 Duplicates: 0 Warnings: 0
    
    ...
    
    mysql> insert into test.tbl(name) select name from test.tbl;
    Query OK, 2097152 rows affected (24.84 sec)
    Records: 2097152 Duplicates: 0 Warnings: 0
    
    mysql> commit;
    Query OK, 0 rows affected (7.90 sec)

    undo表空间情况,膨胀到100MB+后成功回收

    $ du -sh undo*
    10M    undo001
    10M    undo002
    10M    undo003
    10M    undo004
    10M    undo005
    10M    undo006
    125M   undo007
    10M    undo008

    $ du -sh undo*
    10M    undo001
    10M    undo002
    10M    undo003
    10M    undo004
    10M    undo005
    10M    undo006
    10M    undo007
    10M    undo008

    3 Reference

    https://dev.mysql.com/doc/ref...

    总结

    以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,谢谢大家对脚本之家的支持。

    您可能感兴趣的文章:
    • MySQL 清除表空间碎片的实例详解
    • 解析mysql 表中的碎片产生原因以及清理
    • MySQL的表空间是什么
    • Mysql脏页flush及收缩表空间原理解析
    • MySQL InnoDB表空间加密示例详解
    • 深度解析MySQL 5.7之临时表空间
    • mysql Innodb表空间卸载、迁移、装载的使用方法
    • MySQL中查询所有数据库占用磁盘空间大小和单个库中所有表的大小的sql语句
    • MySQL 表空间碎片的概念及相关问题解决
    上一篇:MySQL使用Replace操作时造成数据丢失的问题解决
    下一篇:简单谈谈MySQL数据透视表
  • 相关文章
  • 

    © 2016-2020 巨人网络通讯 版权所有

    《增值电信业务经营许可证》 苏ICP备15040257号-8

    Mysql在线回收undo表空间实战记录 Mysql,在线,回收,undo,表,空间,