为什么mysql的ibdata1文件会不断增大
背景
DBA在运维数据库的过程中，常常会有这样的烦恼：InnoDB存储引擎的共享表空间文件ibdata1逐步增大，更严重甚至将磁盘打满影响服务。 此篇文章主要围绕两个问题进行分析：
- ibdata1文件为什么会增大？
- ibdata1文件在同集群的主从库间并不一定同步增长？
问题现象
某客户RDS实例磁盘使用率从30%迅速增长到100%。经过排查发现，ibdata1文件在30分钟左右的时间内从默认的32MB快速涨到50GB，最终导致磁盘打满。
原因分析
ibdata1文件的存储内容
ibdata1是一个用来构建InnoDB系统表空间的文件，文件中存储了以下几部分信息:
- Data dictionary。
- Double write buffer。
- Insert buffer。
- Rollback segments。
- UNDO space。
- Foreign key constraint system tables。
其中，rollback segment和undo space是导致ibdata1文件增大的主要原因。
相关参数说明：
- innodb_data_file_path：指定InnoDB system tablespace文件。例如ibdata1:32M:autoextend，ibdata1为文件名；32M为文件初始大小；autoextend说明文件可以自动扩展。
- innodb_data_home_dir：指定InnoDB system tablespace文件的存放路径。
- innodb_autoextend_increment：指定InnoDB system tablespace 文件自动扩展步长。
- innodb_file_per_table：该参照控制InnoDB表的数据存放于系统表空间（ibdata1），还是存放于独立表空间（.ibd文件）。
ibdata1文件增大的原因
-
原因1：使用InnoDB共享表空间存储数据
参数innodb_file_per_table，控制innodb引擎采用共享表空间存储还是独立表空间存储。
参数innodb_file_per_table为OFF时，innodb采用共享表空间存储。除了存储上面的内容外，新创建表的数据和索引也存放在ibdata1文件中。所以，随着表数目和数据量的增长，ibdata1文件也会逐渐增长。
参数innodb_file_per_table为ON时，innodb采用独立表空间存储。新创建表的数据和索引存放在各自的ibd文件中。ibdata1文件只存储上述内容，一般情况下，文件并不会有明显增长。
这种情况，ibdata1文件在主从间会同步增长。
-
原因2：UNDO LOG堆积
InnoDB为了实现MVCC多版本并发控制，通过记录更新操作的undo log和redo log来实现。而innoDB的undo log是存放在ibdata1共享表空间的。undo log堆积，是导致ibdata1文件增大的主要原因。这种ibdata1增长的情况一般发生在主库，从库采用单线程同步一般不会有明显问题。
导致undo log堆积的常见场景：
-
事务长期间不提交或回滚，导致undo log堆积
当事务A对一行数据进行修改时，InnoDB会把对事务A可见的旧版本数据存储一份在undo log中，如果此时又有事务B要修改这行数据，InnoDB又会把对事务B可见的旧版本数据再存储一份在undo log中，以此类推。 如果这条数据当前有N个事务要对其进行修改，那么innoDB就会存储N份历史版本的undo信息。而这些undo信息就存放在ibdata1文件中，这是导致ibdata1增大的主要原因之一。常见的情况：如load data操作；大量并发事务；旧事务长时间不提交或回滚。
-
为保证大事务中读请求MVCC一致性导致undo log堆积
事务中读取大量数据的SQL长时间不能提交或回滚。在事务开始到事务提交或回滚前的这段时间内，为保证MVCC读版本一致性，对事务要读取表的数据的所有修改操作产生的undo log就一直无法被purge掉造成堆积，从而导致ibdata1增大。这也是导致ibdata1增大的主要原因之一。 常见的情况，如mysqldump逻辑备份时加了--single-transaction，同时其他事务对备份的表有大量的更新操作。
-
解决方案
如果现有系统中ibdata1文件已经增大了，怎么降下来？可以参考如下步骤处理：
- 首先购买新的RDS实例。
- 确保新实例中参数innodb_file_per_table=ON，表示使用独立表空间存储InnoDB表的数据。
- 配置DTS任务，将源RDS实例的数据迁移到新的RDS实例。
- 修改程序访问数据库的配置，使用新的RDS实例。
总结建议
通过以上分析，既然清楚了引起ibdata1文件增大的各种因素，那么如何避免ibdata1增大的风险呢？
- 确保访问数据库的程序代码中对每个事务都有配对的打开和关闭操作，避免大事务产生。
- 使用LOAD DATA批量导入数据时，控制单次导入的数据量，避免一次性导入的数据量过大。
- 执行mysqldump操作时避免添加--single-transaction配置。