温馨提示×

温馨提示×

您好,登录后才能下订单哦!

密码登录×
登录注册×
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》

mysql的.ibd文件过大如何处理

发布时间:2022-09-23 14:47:32 来源:亿速云 阅读:676 作者:iii 栏目:开发技术

这篇文章主要介绍了mysql的.ibd文件过大如何处理的相关知识,内容详细易懂,操作简单快捷,具有一定借鉴价值,相信大家阅读完这篇mysql的.ibd文件过大如何处理文章都会有所收获,下面我们一起来看看吧。

一条zabbix微信的磁盘告警打破了往常的宁静

收到告警之后发现是mysql的datadir目录,按着平时习惯开始排查;过程就不说了,最后发现某个库的目录大小异常,然后进去查看之后发现jdp_tb_trade.ibd过大,达到46G;跟真实数据量不符,就此打算对它下手处理。

那么,我们知道ibd文件是每个数据库里面每个表的数据空间,每个表的数据和索引都会存在自已的表空间中。

这么重要的东西肯定不能直接在线上操作,毕竟之前完全不知道处理这个东西会产生什么影响,那接下来就是测试环境的再现过程了:

测试环境:配置直接cp线上的my.cnf

然后建库建表,插入数据,使该表的ibd文件增大

最后如图:

mysql的.ibd文件过大如何处理

该文件46G,表里面的数据也有八百多万条,接下来就是再现线上环境的操作了(线上环境增删操作多),先删个10数据,并且用优化命令对该表进行优化(optimize):

mysql的.ibd文件过大如何处理

但是发现在等待该命令执行结果的过程中,根目录一直在增长:

mysql的.ibd文件过大如何处理

直到跟目录被占用百分百之后,优化命令报错了:

mysql的.ibd文件过大如何处理

报错之后跟目录空间瞬间释放了:

mysql的.ibd文件过大如何处理

这里我当时猜想到是因为临时表的问题,但是不知道怎么改临时表的存储目录,那肯定是不懂就问。

问了DBA 大佬后,说是修改tmpdir参数即可(默认是指向tmp目录):

熟练的vim my.cnf

在[mysqld]下添加:

tmpdir = /ssd_data2/158mysql/107.sla

重启mysql实例

在mysql命令符下查看该参数目录是否生效:

mysql的.ibd文件过大如何处理

那就再执行一遍优化命令:

mysql的.ibd文件过大如何处理

mysql的.ibd文件过大如何处理

成功了,文件也缩小了一个G。

接下来我又进一步测试,删除表里面数据,只保留10万条数据;再执行optimize命令,并且观察目录占用大小情况:

mysql的.ibd文件过大如何处理

mysql的.ibd文件过大如何处理

mysql的.ibd文件过大如何处理

这里值得一提的是:optimize命令执行时间只用了15分钟,通过观察目录的变化发现临时表大小大概在45G左右。

接下来是总结:

1)我原以为做一些小小的改动(只删除了10条数据)会很快得到实验的结果,结果可以在图上面看到optimize命令执行了一个半小时;但是后面我再一次测试发现只用了15分钟,可能是服务器上其他业务影响了,时间上不好下结论。
这个命令会产生锁表的效应,所以时间上需要注意。

2)学习知识点:

1、ibd文件为何物,里面是放什么东西的:
上面也说到,是放表的元数据,索引。

2、optimize这个命令的相关知识,会对数据库造成什么影响等:
已知有:
执行过程中会锁表
会产生临时表,占用一定的空间
会影响主从延迟
(欢迎留言补充)

3、tmpdir这个参数:
临时表指定存放目录
可以跟innodb_tmpdir参数对比学习

4、这里要提一个参数 “innodb_file_per_table=1”
配置文件里最好把这个参数打开,因为生产环境用的是innoDB的引擎,然后innodb会默认将所有库的表数据都存储在一个共享空间中:ibdata1,这样不方便我们平时的优化。
该参数是让每个表都会产生一个独立的ibd文件(也就是数据空间)

3)为什么会产生这样的事情呢?:

个人理解:平时我们删除数据时,会使得表的ibd文件产生空隙:也就是说,删除数据之后它会傻傻的空在哪里,如果没有数据补进来就会一直空着;然后重复这增加,删除一系列操作之后,该文件随着时间的推移变得越来越大。

关于“mysql的.ibd文件过大如何处理”这篇文章的内容就介绍到这里,感谢各位的阅读!相信大家对“mysql的.ibd文件过大如何处理”知识都有一定的了解,大家如果还想学习更多知识,欢迎关注亿速云行业资讯频道。

向AI问一下细节

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

AI