《Mysql实例MySQL InnoDB | 如何避免ibdata1文件暴涨》要点：
本文介绍了Mysql实例MySQL InnoDB | 如何避免ibdata1文件暴涨，希望对您有用。如果有疑问，可以联系我们。
用MySQL/InnoDB的童鞋可能也会有过烦恼,不知道为什么原因,ibdata1文件莫名其妙的增大,不知道该如何让它缩回去,就跟30岁之后男人的肚腩一样,汗啊,可喜可贺的是我的肚腩还没长出来,hoho~MYSQL应用
正式开始之前,我们要先知道ibdata1文件是干什么用的.MYSQL应用
ibdata1文件是InnoDB存储引擎的共享表空间文件,该文件中主要存储着下面这些数据：MYSQL应用
MYSQL应用
另外,当选项 innodb_file_per_table = 0 时,在ibdata1文件中还需要存储 InnoDB 表数据&索引.ibdata1文件从5.6.7版本开始,默认大小是12MB,而在这之前默认大小是10MB,其相关选项是 innodb_data_file_path,比如我一般是这么设置的：MYSQL应用
innodb_data_file_path = ibdata1:1G:autoextendMYSQL应用
当然了,无论是否启用了 innodb_file_per_table = 1,ibdata1文件都必须存在,因为它必须存储上述 InnoDB 引擎所依赖&必须的数据,尤其是上面加粗标识的 rollback segments 和 undo space,它俩是引起 ibdata1 文件大小增加的最大原因,我们下面会详细说.MYSQL应用
我们知道,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应用
另一种情况是,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应用
最后一个是,选项 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应用
转载请注明本页网址：
http://www.vephp.com/jiaocheng/5717.html
