文章详情

短信预约-IT技能 免费直播动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

MySQL5.7怎么在线收缩undo表空间

2023-06-06 01:11

关注

这篇文章主要讲解了“MySQL5.7怎么在线收缩undo表空间”,文中的讲解内容简单清晰,易于学习与理解,下面请大家跟着小编的思路慢慢深入,一起来研究和学习“MySQL5.7怎么在线收缩undo表空间”吧!

1. MySQL 5.5时代的undo log

在MySQL5.5以及之前,大家会发现随着数据库上线时间越来越长,ibdata1文件(即InnoDB的共享表空间,或者系统表空间)会越来越大,这会造成2个比较明显的问题:

(1)磁盘剩余空间越来越小,到后期往往要加磁盘;

(2)物理备份时间越来越长,备份文件也越来越大。

这是怎么回事呢?

原因除了数据量自然增长之外,在MySQL5.5以及之前,InnoDB的undo log也是存放在ibdata1里面的。一旦出现大事务,这个大事务所使用的undo log占用的空间就会一直在ibdata1里面存在,即使这个事务已经关闭。

那么问题来了,有办法把上面说的空闲的undo log占用的空间从ibdata1里面清理掉吗?答案是没有直接的办法,只能全库导出sql文件,然后重新初始化mysql实例,再全库导入。

2. MySQL 5.6时代的undo log

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

下面对这3个参数做一下解释:

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

(2)innodb_undo_tablespaces,指定单独存放的undo表空间个数,例如如果设置为3,则undo表空间为undo001、undo002、undo003,每个文件初始大小默认为10M。该参数我们推荐设置为大于等于3,原因下文将解释。该参数实例初始化之后不可改动;

(3)innodb_undo_logs,指定回滚段的个数(早期版本该参数名字是innodb_rollback_segments),默认128个。每个回滚段可同时支持1024个在线事务。这些回滚段会平均分布到各个undo表空间中。该变量可以动态调整,但是物理上的回滚段不会减少,只是会控制用到的回滚段的个数。

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

3. MySQL 5.7时代的undo log

那么问题又来了,undo log单独拆出来后就能缩小了吗?MySQL 5.7引入了新的参数,innodb_undo_log_truncate,开启后可在线收缩拆分出来的undo表空间。在满足以下2个条件下,undo表空间文件可在线收缩:

(1)innodb_undo_tablespaces>=2。因为truncate undo表空间时,该文件处于inactive状态,如果只有1个undo表空间,那么整个系统在此过程中将处于不可用状态。为了尽可能降低truncate对系统的影响,建议将该参数最少设置为3;

(2)innodb_undo_logs>=35(默认128)。因为在MySQL 5.7中,第一个undo log永远在系统表空间中,另外32个undo log分配给了临时表空间,即ibtmp1,至少还有2个undo log才能保证2个undo表空间中每个里面至少有1个undo log;

满足以上2个条件后,把innodb_undo_log_truncate设置为ON即可开启undo表空间的自动truncate,这还跟如下2个参数有关:

(1)innodb_max_undo_log_size,undo表空间文件超过此值即标记为可收缩,默认1G,可在线修改;

(2)innodb_purge_rseg_truncate_frequency,指定purge操作被唤起多少次之后才释放rollback segments。当undo表空间里面的rollback segments被释放时,undo表空间才会被truncate。由此可见,该参数越小,undo表空间被尝试truncate的频率越高。

4. MySQL 5.7的undo表空间的truncate示例

(1) 首先确保如下参数被正确设置:

# 为了实验方便,我们减小该值
innodb_max_undo_log_size = 100M
innodb_undo_log_truncate = ON
innodb_undo_logs = 128  
innodb_undo_tablespaces = 3
# 为了实验方便,我们减小该值
innodb_purge_rseg_truncate_frequency = 10

(2) 创建表:

mysql> create table t1(    -> id int primary key auto_increment,
   -> name varchar(200));
Query OK, 0 rows affected (0.13 sec)

(3)插入测试数据

mysql> insert into t1(name) values(repeat('a',200));Query OK, 1 row affected (0.01 sec)mysql> insert into t1(name) select name from t1;Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates:  Warnings:

mysql> insert into t1(name) select name from t1;Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates:  Warnings:

mysql> insert into t1(name) select name from t1;Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates:  Warnings:

...

mysql> insert into t1(name) select name from t1;Query OK, 8388608 rows affected (2 min 11.31 sec)
Records: 8388608  Duplicates:  Warnings:

这时undo表空间文件大小如下,可以看到有一个undo文件已经超过了100M:

-rw-r----- 1 mysql mysql  13M Feb 17 17:59 undo001-rw-r----- 1 mysql mysql 128M Feb 17 17:59 undo002-rw-r----- 1 mysql mysql  64M Feb 17 17:59 undo003

此时,为了,让purge线程运行,可以运行几个delete语句:

mysql> delete from t1 limit 1;Query OK, 1 row affected (0.00 sec)mysql> delete from t1 limit 1;Query OK, 1 row affected (0.00 sec)mysql> delete from t1 limit 1;Query OK, 1 row affected (0.00 sec)mysql> delete from t1 limit 1;Query OK, 1 row affected (0.00 sec)

再查看undo文件大小:

-rw-r----- 1 mysql mysql  13M Feb 17 18:05 undo001-rw-r----- 1 mysql mysql  10M Feb 17 18:05 undo002-rw-r----- 1 mysql mysql  64M Feb 17 18:05 undo003

可以看到,超过100M的undo文件已经收缩到10M了。

感谢各位的阅读,以上就是“MySQL5.7怎么在线收缩undo表空间”的内容了,经过本文的学习后,相信大家对MySQL5.7怎么在线收缩undo表空间这一问题有了更深刻的体会,具体使用情况还需要大家实践验证。这里是编程网,小编将为大家推送更多相关知识点的文章,欢迎关注!

阅读原文内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-数据库
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯