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存储引擎的共享表空间⽂件，该⽂件中主要存储着下⾯这些数据：
data dictionarydouble write buffer
insert buffer/change bufferrollback segmentsundo space
Foreign key constraint system tables
另外，当选项 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 引擎所依赖&必须的数据，尤其是上⾯加粗标识的 rollback segments 和 undo space，它俩是引起 ibdata1 ⽂件⼤⼩增加的最⼤原因，我们下⾯会详细说。2、原因分析
我们知道，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 ⼯作不仅仅是删除过期不⽤的 undolog，还有其他，以后有机会再说）。
那么问题来了，如果当前有个事务中需要读取到⼤量数据的历史版本，⽽该事务因为某些原因⽆法今早提交或回滚，⽽该事务发起之后⼜有⼤量事务需要对这些数据进⾏修改，这些新事务产⽣的 undo log 就⼀直⽆法被删除掉，形成了堆积，这就是导致 ibdata1 ⽂件⼤⼩增⼤最主要的原因之⼀。这种情况最经典的场景就是⼤量数据备份，因此我们建议把备份⼯作放在专⽤的slave server 上，不要放在 master server 上。
另⼀种情况是，InnoDB的 purge ⼯作因为本次 file i/o 性能是在太差或其他的原因，⼀直⽆法及时把可以删除的 undo log 进⾏purge 从⽽形成堆积，这是导致 ibdata1 ⽂件⼤⼩增⼤另⼀个最主要的原因。这种场景发⽣在服务器硬件配置⽐较弱，没有及时跟上业务发展⽽升级的情况。
⽐较少见的⼀种是在早期运⾏在32位系统的MySQL版本中存在bug，当发现待 purge 的 undo log 总量超过某个值时，purge线程直接放弃抵抗，再也不进⾏ purge 了，这个问题在我们早期使⽤32位MySQL 5.0版本时遇到的⽐较多，我们曾经遇到这
个⽂件涨到100多G的情况。后来我们费了很⼤功夫把这些实例都迁移到位系统下，终于解决了这个问题。
最后⼀个是，选项 innodb_data_file_path 值⼀开始就没调整或者设置很⼩，这就必不可免导致 ibdata1 ⽂件增⼤了。Percona官⽅提供的 my.cnf 参考⽂件中也⼀直没把这个值加⼤，让我百思不得其解，难道是为了像那个经常被我吐槽的xx那样，故意留个暗门，好⽅便后续帮客户进⾏优化吗？（我⼼理太阴暗了，不好不好~~）稍微总结下，导致ibdata1⽂件⼤⼩暴涨的原因有下⾯⼏个：
有⼤量并发事务，产⽣⼤量的undo log；
有旧事务长时间未提交，产⽣⼤量旧undo log；file i/o性能差，purge进度慢；初始化设置太⼩不够⽤；32-bit系统下有bug。
稍微题外话补充下，另⼀个热门数据库 PostgreSQL 的做法是把各个历史版本的数据 和 原数据表空间 存储在⼀起，所以不存在本案例的问题，也因此 PostgreSQL 的事务回滚会⾮常快，并且还需要定期做 vaccum ⼯作（具体可参见PostgreSQL的MVCC实现机制，我可能说的不是完全正确哈）3、解决⽅法建议
看到上⾯的这些问题原因描述，有些同学可能觉得这个好办啊，对 ibdata1 ⽂件⼤⼩进⾏收缩，回收表空间不就结了吗。悲剧的是，截⽌⽬前，InnoDB 还没有办法对 ibdata1 ⽂件表空间进⾏回收/收缩，⼀旦 ibdata1 ⽂件的肚⼦被搞⼤了，只能把数据先备份后恢复再次重新初始化实例才能恢复原先的⼤⼩，或者把依次把各个独⽴表空间⽂件备份恢复到⼀个新实例中，除此外，没什么更好的办法了。
当然了，这个问题也并不是不能防范，根据上⾯提到的原因，相应的建议对策是：
升级到5.6及以上（-bit），采⽤独⽴undo表空间，5.6版本开始就⽀持独⽴的undo表空间了，再也不⽤担⼼会把ibdata1 ⽂件搞⼤；
初始化设置时，把 ibdata1 ⽂件⾄少设置为1GB以上；增加purge线程数，⽐如设置 innodb_purge_threads = 8；提⾼file i/o能⼒，该上SSD的赶紧上；事务及时提交，不要积压；
默认打开autocommit = 1，避免忘了某个事务长时间未提交；
检查开发框架，确认是否设置了 autocommit=0，记得在事务结束后都有显式提交或回滚。总结
以上就是这篇⽂章的全部内容了，希望本⽂的内容对⼤家的学习或者⼯作具有⼀定的参考学习价值，如果有疑问⼤家可以留⾔交流，谢谢⼤家对的⽀持。