Mysql实例MySQL InnoDB | 如何避免ibdata1文件暴涨

我们知道,InnoDB是支持MVCC的,它和ORACLE类似,采用 undo log、redo log来实现MVCC特性的.在事务中对一行数据进行修改时,InnoDB 会把这行数据的旧版本数据存储一份在undo log中,如果这时候有另一个事务又要修改这行数据,就又会把该事物最新可见的数据版本存储一份在undo log中,以此类推,如果该数据当前有N个事务要对其进行修改,就需要存储N份历史版本(和ORACLE略有不同的是,InnoDB的undo log不完全是物理block,主要是逻辑日志,这个可以查看 InnoDB 源码或其他相关资料).这些 undo log 需要等待该事务结束后,并再次根据事务隔离级别所决定的对其他事务而言的可见性进行判断,确认是否可以将这些 undo log 删除掉,这个工作称为 purge(purge 工作不仅仅是删除过期不用的 undo log,还有其他,以后有机会再说).MysqL应用

那么问题来了,如果当前有个事务中需要读取到大量数据的历史版本,而该事务因为某些原因无法今早提交或回滚,而该事务发起之后又有大量事务需要对这些数据进行修改,这些新事务产生的 undo log 就一直无法被删除掉,形成了堆积,这就是导致 ibdata1 文件大小增大最主要的原因之一.这种情况最经典的场景就是大量数据备份,因此我们建议把备份工作放在专用的 slave server 上,不要放在 master server 上.MysqL应用

另一种情况是,255);">InnoDB的 purge 工作因为本次 file I/O 性能是在太差或其他的原因,一直无法及时把可以删除的 undo log 进行purge 从而形成堆积,这是导致 ibdata1 文件大小增大另一个最主要的原因.这种场景发生在服务器硬件配置比较弱,没有及时跟上业务发展而升级的情况.MysqL应用

比较少见的一种是在早期运行在32位系统的MysqL版本中存在BUG,当发现待 purge 的 undo log 总量超过某个值时,purge 线程直接放弃抵抗,再也不进行 purge 了,这个问题在我们早期使用32位MysqL 5.0版本时遇到的比较多,我们曾经遇到这个文件涨到100多G的情况.后来我们费了很大功夫把这些实例都迁移到64位系统下,终于解决了这个问题.MysqL应用

最后一个是,255);">选项 innodb_data_file_path 值一开始就没调整或者设置很小,这就必不可免导致 ibdata1 文件增大了.Percona官方提供的 my.cnf 参考文件中也一直没把这个值加大,让我百思不得其解,难道是为了像那个经常被我吐槽的xx那样,故意留个暗门,好方便后续帮客户进行优化吗?(我心理太阴暗了,不好不好~~)MysqL应用

稍微总结下,导致ibdata1文件大小暴涨的原因有下面几个：MysqL应用

有大量并发事务,产生大量的undo log；MysqL应用

有旧事务长时间未提交,产生大量旧undo log；MysqL应用

file I/O性能差,purge进度慢；MysqL应用

初始化设置太小不够用；MysqL应用

32-bit系统下有BUG.MysqL应用

 MysqL应用

稍微题外话补充下,另一个热门数据库 Postgresql 的做法是把各个历史版本的数据 和 原数据表空间 存储在一起,所以不存在本案例的问题,也因此 Postgresql 的事务回滚会非常快,并且还需要定期做 vaccum 工作(具体可参见Postgresql的MVCC实现机制,我可能说的不是完全正确哈)MysqL应用

看到上面的这些问题原因描述,有些同学可能觉得这个好办啊,对 ibdata1 文件大小进行收缩,回收表空间不就结了吗.悲剧的是,截止目前,InnoDB 还没有办法对 ibdata1 文件表空间进行回收/收缩,一旦 ibdata1 文件的肚子被搞大了,只能把数据先备份后恢复再次重新初始化实例才能恢复原先的大小,或者把依次把各个独立表空间文件备份恢复到一个新实例中,除此外,没什么更好的办法了.MysqL应用

当然了,这个问题也并不是不能防范,根据上面提到的原因,相应的建议对策是：MysqL应用

升级到5.6及以上(64-bit),采用独立undo表空间,5.6版本开始就支持独立的undo表空间了,再也不用担心会把 ibdata1 文件搞大；MysqL应用

初始化设置时,把 ibdata1 文件至少设置为1GB以上；MysqL应用

增加purge线程数,比如设置 innodb_purge_threads = 8；MysqL应用

提高file I/O能力,该上SSD的赶紧上；MysqL应用

事务及时提交,不要积压；MysqL应用

默认打开autocommit = 1,避免忘了某个事务长时间未提交；MysqL应用

检查开发框架,确认是否设置了 autocommit=0,记得在事务结束后都有显式提交或回滚.MysqL应用

以上是内存溢出为你收集整理的Mysql实例MySQL InnoDB | 如何避免ibdata1文件暴涨全部内容，希望文章能够帮你解决Mysql实例MySQL InnoDB | 如何避免ibdata1文件暴涨所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。