一次mysql的.ibd文件过大处理过程记录

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

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

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

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

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

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

最后如图：

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

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

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

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

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

问了DBA 大佬后，说是修改tmpdir参数即可（默认是指向tmp目录）：

熟练的vim my.cnf

在[mysqld]下添加：

tmpdir = /ssd_data2/158mysql/107.sla

重启mysql实例

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

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

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

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

这里值得一提的是：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文件过大处理内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

赞 (0)

相关推荐

  • MySQL 利用frm文件和ibd文件恢复表数据

    frm文件和ibd文件简介 在MySQL中,如果我们使用了默认的存储引擎innodb创建一张表,那么在文件夹下面就会出现表名.frm和表名.ibd两个文件,如果我们使用的是Myisam存储引擎,那么就会出现三个文件,这里我们给出例子: [root@ /data/yeyz]#ll total 580 -rw-rw---- 1 mysql mysql 8586 Apr 3 17:44 a.frm -rw-rw---- 1 mysql mysql 0 Apr 3 17:44 a.MYD -rw-rw-

  • MySQL单表ibd文件恢复方法详解

    前言: 随着innodb的普及,innobackup也成为了主流备份方式.物理备份对于新建slave,全库恢复的需求都能从容应对. 但当面临单表数据误删,或者单表误drop的情况,如果使用物理全备进行恢复呢? 下文将进行详细分析. 恢复过程中需要用到的工具,percona data recover tool : https://launchpad.net/percona-innodb-recovery-tool 情况一:误删部分数据,需要用最近一次备份覆盖 来自同一台机器的ibd恢复覆盖,且备份

  • 一次mysql的.ibd文件过大处理过程记录

    一次mysql的.ibd文件过大处理过程记录

    一条zabbix微信的磁盘告警打破了往常的宁静 收到告警之后发现是mysql的datadir目录,按着平时习惯开始排查:过程就不说了,最后发现某个库的目录大小异常,然后进去查看之后发现jdp_tb_trade.ibd过大,达到46G:跟真实数据量不符,就此打算对它下手处理. 那么,我们知道ibd文件是每个数据库里面每个表的数据空间,每个表的数据和索引都会存在自已的表空间中. 这么重要的东西肯定不能直接在线上操作,毕竟之前完全不知道处理这个东西会产生什么影响,那接下来就是测试环境的再现过程了: 测

  • Mysql binlog日志文件过大的解决

    Mysql binlog日志文件过大的解决

    目录 1.相关binlog配置 2.binlog相关高级设置 2.1 改变binlog模式 2.2 相关SQL操作binlog 磁盘突然报错使用率过大,排查原因,发现mysql的binlog文件占用过大 命令 ls -l -h mysql-binlog是MySQL数据库的二进制日志,用于记录用户对数据库操作的SQL语句((除了数据查询语句)信息.可以使用mysqlbin命令查看二进制日志的内容. 可以通过设置my.cof配置文件的方式限制binlog文件的输出. 1.相关binlog配置 vim

  • Mysql通过ibd文件恢复数据的详细步骤

    恢复步骤 1.创建数据库(随意创建) 2.创建数据表(备注:表结构要和要恢复的表结构一致,row_format要和ibd文件的row_format一致,否则,会提示两者不一致. 当前row_format=dynamic) 3.表的属性查看 我们使用:show table status like ‘matlab’\G,查看表的属性 备注:创建表时候的row_format和表属性的不一致,基于innodb是,要把row_format设置成dynamic时,需要修改mysql的全局配置,直接在myql

  • 详解angularjs4部署文件过大解决过程

    这是我人生的第一篇文章,写得不好,请见谅! 本人是一个java web开发工程师,对angularjs4小有接触,最近看到一个漂亮的angularjs4的后台模板–angle,于是就在CSDN下载来测试一下,点击这里下载 模板的一些照片 相信有经验的都知道,要运行先安装nodejs,配置nodejs环境等等,这里就不细说了,网上有很多这样的文章,而我参考的文章是,点击这里 所有东西都搞掂了,然后就是打包部署,放在服务器玩玩了, 下面就是我遇到的问题和解决办法: 1.ng build 打包,加载的

  • vue实现一个单文件组件的完整过程记录

    vue实现一个单文件组件的完整过程记录

    目录 前言 单文件组件 基本概念 简单的loader 解析组件内容 注册组件 获取脚本内容 Data URI和Object URI 动态导入 实现 行为层 兼容性问题及其他 总结 前言 前端开发人员只要了解过vue.js框架可能都知道单文件组件.vue.js中的单文件组件允许在一个文件中定义一个组件的所有内容.这是一个非常有用的解决方案,在浏览器网页中已经开始提倡这种机制.但是不幸的是,这个概念自从2017年8月被提出以来,到现在没有任何进展,像是已经要消亡了一样.然而,深入研究这个主题并试着使

  • Innodb中mysql快速删除2T的大表方法示例

    Innodb中mysql快速删除2T的大表方法示例

    前言 本文主要给大家介绍了关于Innodb中mysql快速删除2T的大表的相关内容,分享出来供大家参考学习,下面话不多说了,来一起看看详细的介绍吧 来,先来看小漫画陶冶一下情操 OK,这里就说了.假设,你有一个表erp,如果你直接进行下面的命令 drop table erp 这个时候所有的mysql的相关进程都会停止,直到drop结束,mysql才会恢复执行.出现这个情况的原因就是因为,在drop table的时候,innodb维护了一个全局锁,drop完毕锁就释放了. 这意味着,如果在白天,访

  • 解决Mysql收缩事务日志和日志文件过大无法收缩问题

    一.MS SQL SERVER 2005 --1.清空日志       exec('DUMP TRANSACTION 数据库名 WITH NO_LOG') --2.截断事务日志:      exec('BACKUP LOG 数据库名 WITH NO_LOG') --3.收缩数据库文件(如果不压缩,数据库的文件不会减小      exec('DBCC SHRINKDATABASE(数据库名) ') --4.设置自动收缩      exec('EXEC sp_dboption 数据库名,autosh

  • MySQL如何优雅的删除大表实例详解

    前言 删除表,大家下意识想到的命令可能是直接使用DROP TABLE "表名",这是初生牛犊的做法,因为当要删除的表达空间到几十G,甚至是几百G的表时候.这样一条命令下去,MySQL可能就直接夯住了,外在表现就是QPS急速下降,客户请求变慢. 解决办法 1.业务低峰时间手动执行删除 这个可能就需要DBA不辞辛劳,大晚上爬起来删表了. 2.先清除数据,最后再删除的方式 譬如1000万条数据,写脚本每次删除20万,睡眠一段时间,继续执行.这样也能做到对用户无感知. 3.对表文件(idb文件

随机推荐