MySQL问答系列之如何避免ibdata1文件大小暴涨
0、导读
ibdata1文件是什么？
ibdata1是一个用来构建innodb系统表空间的文件，这个文件包含了innodb表的元数据、撤销记录、修改buffer和双写buffer。如果file-per-table选项打开的话，该文件则不一定包含所有表的数据。当innodb_file_per_table选项打开的话，新创建表的数据和索引则不会存在系统表空间中，而是存放在各自表的.ibd文件中.
显然这个文件会越来越大，innodb_autoextend_increment选项则指定了该文件每次自动增长的步进，默认是8M.
是什么原因导致ibdata1文件会越来越大？
ibdata1存放数据，索引和缓存等，是MYSQL的最主要的数据。所以随着数据库越来越大，表也会越大，这个无法避免的。如果时间长了，越来越大，我们在处理日志和空间的时候就不是那么方便了，就不知从何入手了。接下来我们就要处理下这样的情况，分库存储数据。
遇到InnoDB的共享表空间文件ibdata1文件大小暴增时，应该如何处理？
1、问题背景
用MySQL/InnoDB的童鞋可能也会有过烦恼，不知道为什么原因，ibdata1文件莫名其妙的增大，不知道该如何让它缩回去，就跟30岁之后男人的肚腩一样，汗啊，可喜可贺的是我的肚腩还没长出来，hoho~
正式开始之前，我们要先知道ibdata1文件是干什么用的。
ibdata1文件是InnoDB存储引擎的共享表空间文件，该文件中主要存储着下面这些数据：
- datadictionary
- doublewritebuffer
- insertbuffer/changebuffer
- rollbacksegments
- undospace
- Foreignkeyconstraintsystemtables
另外，当选项innodb_file_per_table=0时，在ibdata1文件中还需要存储InnoDB表数据&索引。ibdata1文件从5.6.7版本开始，默认大小是12MB，而在这之前默认大小是10MB，其相关选项是innodb_data_file_path，比如我一般是这么设置的：
innodb_data_file_path=ibdata1:1G:autoextend
当然了，无论是否启用了innodb_file_per_table=1，ibdata1文件都必须存在，因为它必须存储上述InnoDB引擎所依赖&必须的数据，尤其是上面加粗标识的rollbacksegments和undospace，它俩是引起ibdata1文件大小增加的最大原因，我们下面会详细说。
2、原因分析
我们知道，InnoDB是支持MVCC的，它和ORACLE类似，采用undolog、redolog来实现MVCC特性的。在事务中对一行数据进行修改时，InnoDB会把这行数据的旧版本数据存储一份在undolog中，如果这时候有另一个事务又要修改这行数据，就又会把该事物最新可见的数据版本存储一份在undolog中，以此类推，如果该数据当前有N个事务要对其进行修改，就需要存储N份历史版本（和ORACLE略有不同的是，InnoDB的undolog不完全是物理block，主要是逻辑日志，这个可以查看InnoDB源码或其他相关资料）。这些undolog需要等待该事务结束后，并再次根据事务隔离级别所决定的对其他事务而言的可见性进行判断，确认是否可以将这些undolog删除掉，这个工作称为purge（purge工作不仅仅是删除过期不用的undolog，还有其他，以后有机会再说）。
那么问题来了，如果当前有个事务中需要读取到大量数据的历史版本，而该事务因为某些原因无法今早提交或回滚，而该事务发起之后又有大量事务需要对这些数据进行修改，这些新事务产生的undolog就一直无法被删除掉，形成了堆积，这就是导致ibdata1文件大小增大最主要的原因之一。这种情况最经典的场景就是大量数据备份，因此我们建议把备份工作放在专用的slaveserver上，不要放在masterserver上。
另一种情况是，InnoDB的purge工作因为本次filei/o性能是在太差或其他的原因，一直无法及时把可以删除的undolog进行purge从而形成堆积，这是导致ibdata1文件大小增大另一个最主要的原因。这种场景发生在服务器硬件配置比较弱，没有及时跟上业务发展而升级的情况。
比较少见的一种是在早期运行在32位系统的MySQL版本中存在bug，当发现待purge的undolog总量超过某个值时，purge线程直接放弃抵抗，再也不进行purge了，这个问题在我们早期使用32位MySQL5.0版本时遇到的比较多，我们曾经遇到这个文件涨到100多G的情况。后来我们费了很大功夫把这些实例都迁移到64位系统下，终于解决了这个问题。
最后一个是，选项innodb_data_file_path值一开始就没调整或者设置很小，这就必不可免导致ibdata1文件增大了。Percona官方提供的my.cnf参考文件中也一直没把这个值加大，让我百思不得其解，难道是为了像那个经常被我吐槽的xx那样，故意留个暗门，好方便后续帮客户进行优化吗？（我心理太阴暗了，不好不好~~）
稍微总结下，导致ibdata1文件大小暴涨的原因有下面几个：
- 有大量并发事务，产生大量的undolog；
- 有旧事务长时间未提交，产生大量旧undolog；
- filei/o性能差，purge进度慢；
- 初始化设置太小不够用；
- 32-bit系统下有bug。
稍微题外话补充下，另一个热门数据库PostgreSQL的做法是把各个历史版本的数据和原数据表空间存储在一起，所以不存在本案例的问题，也因此PostgreSQL的事务回滚会非常快，并且还需要定期做vaccum工作（具体可参见PostgreSQL的MVCC实现机制，我可能说的不是完全正确哈）
3、解决方法建议
看到上面的这些问题原因描述，有些同学可能觉得这个好办啊，对ibdata1文件大小进行收缩，回收表空间不就结了吗。悲剧的是，截止目前，InnoDB还没有办法对ibdata1文件表空间进行回收/收缩，一旦ibdata1文件的肚子被搞大了，只能把数据先备份后恢复再次重新初始化实例才能恢复原先的大小，或者把依次把各个独立表空间文件备份恢复到一个新实例中，除此外，没什么更好的办法了。
当然了，这个问题也并不是不能防范，根据上面提到的原因，相应的建议对策是：
- 升级到5.6及以上（64-bit），采用独立undo表空间，5.6版本开始就支持独立的undo表空间了，再也不用担心会把ibdata1文件搞大；
- 初始化设置时，把ibdata1文件至少设置为1GB以上；
- 增加purge线程数，比如设置innodb_purge_threads=8；
- 提高filei/o能力，该上SSD的赶紧上；
- 事务及时提交，不要积压；
- 默认打开autocommit=1，避免忘了某个事务长时间未提交；
- 检查开发框架，确认是否设置了autocommit=0，记得在事务结束后都有显式提交或回滚。
总结
以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，如果有疑问大家可以留言交流，谢谢大家对毛票票的支持。