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

 更新时间:2019年09月10日 10:03:01   作者:Jackgo   我要评论
这篇文章主要给大家介绍了关于Mysql在线回收undo表空间的相关资料,文中通过示例代码介绍的非常详细,对大家学习或者使用Mysql具有一定的参考学习价值,需要的朋友们下面来一起学习学习吧

1 Mysql5.6

1.1 相关参数

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

  • innodb_undo_directory:指定单独存放undo表空间的目录,默认为.(即datadir),可以设置相对路径或者绝对路径。该参数实例初始化之后虽然不可直接改动,但是可以通过先停库,修改配置文件,然后移动undo表空间文件的方式去修改该参数。

默认参数:

mysql> show variables like '%undo%';
+-------------------------+-------+
| Variable_name      | Value |
+-------------------------+-------+
| innodb_undo_directory  | .   |
| innodb_undo_logs    | 128  |
| innodb_undo_tablespaces | 0   |
+-------------------------+-------+
  • innodb_undo_tablespaces:指定单独存放的undo表空间个数,例如如果设置为3,则undo表空间为undo001、undo002、undo003,每个文件初始大小默认为10M。该参数我们推荐设置为大于等于3,原因下文将解释。该参数实例初始化之后不可改动

实例初始化是修改innodb_undo_tablespaces:

mysql_install_db ...... --innodb_undo_tablespaces
$ ls
...
undo001 undo002 undo003
  • innodb_rollback_segments:默认128个。每个回滚段可同时支持1024个在线事务。这些回滚段会平均分布到各个undo表空间中。该变量可以动态调整,但是物理上的回滚段不会减少,只是会控制用到的回滚段的个数。

1.2 使用

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

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

  • 首先你必须在install实例的时候就指定好独立Undo tablespace, 在install完成后不可更改。
  • Undo tablepsace的space id必须从1开始,无法增加或者删除undo tablespace。

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 相关参数

必要条件:

  • innodb_undo_tablespaces:最少有两个,这样一个在清理的时候可以使用另一个,该参数实例初始化之后不可改动
  • innodb_rollback_segments:回滚段的个数,总会有一个回滚段分配给系统表空间,32个保留给临时表空间。所以如果想使用undo表空间的话,这个值要至少为33。例如使用两个undo表空间,这个值就配35。如果设置多个undo表空间,系统表空间中的回滚段会变成非活跃状态。

启动参数:

  • innodb_undo_log_truncate=on
  • innodb_max_undo_log_size:超过这个值的表空间会标记为truncate,动态参数默认是1G
  • innodb_purge_rseg_truncate_frequency:指定purge操作被唤起多少次之后才释放rollback segments。当undo表空间里面的rollback segments被释放时,undo表空间才会被truncate。由此可见,该参数越小,undo表空间被尝试truncate的频率越高。

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...

总结

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

相关文章

  • mysql8.0.2离线安装配置方法图文教程

    mysql8.0.2离线安装配置方法图文教程

    这篇文章主要为大家详细介绍了mysql8.0.2离线安装配置方法图文教程,具有一定的参考价值,感兴趣的小伙伴们可以参考一下
    2018-05-05
  • MySQL索引之聚集索引介绍

    MySQL索引之聚集索引介绍

    在MySQL中,InnoDB引擎表是(聚集)索引组织表(clustered index organize table),而MyISAM引擎表则是堆组织表(heap organize table)
    2015-12-12
  • mysql批量更新多条记录的同一个字段为不同值的方法

    mysql批量更新多条记录的同一个字段为不同值的方法

    最近在工作的时候突然想到了一个问题,就是mysql如何才能实现批量更新多条记录的同一个字段为不同值,于是就动手实践起来了,发现其中的学问还是挺多的,所以想着就总结下来分享给大家,有需要的朋友们可以参考借鉴,下面来一起看看吧。
    2016-12-12
  • Mysql中禁用与启动触发器教程【推荐】

    Mysql中禁用与启动触发器教程【推荐】

    在使用MYSQL过程中,经常会使用到触发器,但是有时使用不当会造成一些麻烦。下面小编给大家带来了Mysql中禁用与启动触发器教程,感兴趣的朋友一起看看吧
    2018-08-08
  • MySQL binlog 远程备份方法详解

    MySQL binlog 远程备份方法详解

    以前备份binlog时,都是先在本地进行备份压缩,然后发送到远程服务器中,但是这种备份方式非常的不安全,下面通过本文给大家介绍MySQL binlog 远程备份的方法,需要的朋友一起学习吧
    2016-05-05
  • Linux利用UDF库实现Mysql提权

    Linux利用UDF库实现Mysql提权

    根据MySQL函数族的可扩展机制,意味着用户可以自己建立包含有自定义函数的动态库来创建自定义函数,简称udf
    2014-07-07
  • Centos7下mysql 8.0.15 安装配置图文教程

    Centos7下mysql 8.0.15 安装配置图文教程

    这篇文章主要为大家详细介绍了Centos7下mysql 8.0.15 安装配置图文教程,具有一定的参考价值,感兴趣的小伙伴们可以参考一下
    2019-03-03
  • 关于mysql基础知识的介绍

    关于mysql基础知识的介绍

    本篇文章是对mysql的基础知识进行了详细的分析介绍,需要的朋友参考下
    2013-06-06
  • macOS安装Solr并索引MySQL

    macOS安装Solr并索引MySQL

    这篇文章主要介绍了macOS安装Solr并索引MySQL的相关资料,非常不错具有参考借鉴价值,需要的朋友可以参考下
    2016-11-11
  • MySQL的慢日志线上问题及优化方案

    MySQL的慢日志线上问题及优化方案

    给大家详细分析了MySQL慢日志线上问题分析及功能优化方案,需要的朋友跟着学习下吧。
    2017-12-12

最新评论