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 dictionary
double write buffer
insert buffer/change buffer
rollback segments
undo 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 工作不仅仅是删除过期不用的 undo log，还有其他，以后有机会再说）。

那么问题来了，如果当前有个事务中需要读取到大量数据的历史版本，而该事务因为某些原因无法今早提交或回滚，而该事务发起之后又有大量事务需要对这些数据进行修改，这些新事务产生的 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的情况。后来我们费了很大功夫把这些实例都迁移到64位系统下，终于解决了这个问题。

最后一个是，选项 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及以上（64-bit），采用独立undo表空间，5.6版本开始就支持独立的undo表空间了，再也不用担心会把 ibdata1 文件搞大；
初始化设置时，把 ibdata1 文件至少设置为1GB以上；
增加purge线程数，比如设置 innodb_purge_threads = 8；
提高file i/o能力，该上SSD的赶紧上；
事务及时提交，不要积压；
默认打开autocommit = 1，避免忘了某个事务长时间未提交；
检查开发框架，确认是否设置了 autocommit=0，记得在事务结束后都有显式提交或回滚。
总结

---

凤城市15379112020： mysql中有几个root怎么解决 - ？
燕彪替马： 有几个root很正常的,因为可以设置不同登录地址不同密码,不同权限的.比如 root 在本地登录,什么权限,什么密码 比如 root 在远程某个IP登录,什么权限,什么密码 比如 root 在远某个IP段登录,什么权限,什么密码 这样做可以更灵活得控制root的权限.如果你觉得麻烦,可以保留 host 是 % 的那个,其它删了即可.没有 % 的话,修改一个或者添加一个都可以 记得操作后执行 “flush privileges;”不然有可能再也登不上来.

凤城市15379112020： 如何解决mysql5 - ？
燕彪替马： 解决方法:1)剪切出安装目录\MYOA\data5\TD_OA的flow_data_35.ibd和flow_data_35.frm两个文件;2)启动MySQL5_OA服务,使用备份的flow_data_35.sql导入到TD_OA库中.如果提示flow_data_35表已经存在不能导入,则继续按后续步骤...

凤城市15379112020： 如何避免mysql死锁问题 - ？
燕彪替马： 处理方式:1. 在表上建立一个聚集索引.2. 对语句更新的相关字段建立包含索引.如何预防死锁1.尽量避免并发的执行涉及到修改数据的语句. 2.编写应用程序,让进程持有锁的时间尽可能短,这样其它进程就不必花太长的时间等待锁被释放.

凤城市15379112020： 我想把表单里的数据插入mysql数据库,要过滤哪些不安全的字符? - 技术？
燕彪替马： 防止sql注入可以分为过滤输入(对输入的数据进行过滤)和转义输出(对发送到数据库的数据进行转义).如果你只是过滤输入而没有转义输出,你很可能会遇到数据库错误(合法的数据也可能影响SQL查询的正确格式),但这也不可靠,合法的数据还可能改变SQL语句的行为.另一方面,如果你转义了输出,而没有过滤输入,就能保证数据不会影响SQL语句的格式,同时也防止了多种常见SQL注入攻击的方法.当然,还是要坚持同时使用这两个步骤

凤城市15379112020： 怎么解决 blind sql injection - ？
燕彪替马： 怎么解决 blind sql injection –运行这条语句的数据库用户需要拥有FILE权限,否则不能执行INTO OUTFILE命令.–在MySQL服务运行的主机中必须存在一个可写的Web目录,否则你不能访问你刚刚上传的Webshell.你可以将代码写入到总是可写的目录像/ttmp,但是你没有权限访问它.

凤城市15379112020： 怎么解决MySQLIntegrityConstraintVi - ？
燕彪替马： 下图是MySQLIntegrityConstraintViolationException异常的源码,图中的①②③④是MySQLIntegrityConstraintViolationException对象的4种构造器.参数的含义分别是:reason(原因), String SQLState(SQL状态), int vendorCode(数据库中...

凤城市15379112020： MySQL安装时出现各种filed 我该怎么解决??? - ？
燕彪替马： 解决办法:打开my.ini,查找 sql-mode=＂STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION＂ 修改为 sql-mode=＂NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION＂ 然后重启MYSQL

凤城市15379112020： 用odbc情况下 怎么防止SQL 注入 - 技术 - ？
燕彪替马： 我不知道ODBC连接是不是不用SQL语句查询,如果是用SQL的话那就用mysql_real_escape_string()函数有什么问题吗?tianshibao 发表于 2009-6-15 16:59[i][/url][/b]谢谢回复$project_id = $_GET[\'project_id\'];$conn = odbc_connect(\'Offshore...

凤城市15379112020： 怎么解决mysql服务无法启动的问题 - ？
燕彪替马： 解决方法一:1、打开my.ini文件,找到default-storage-engine=InnoDB这一行,把它改成default-storage-engine=MyISAM; 2、删除在MySQL安装目录下的Data目录中的ib_logfile0和ib_logfile1;3、找到在配置MySQL服务器时指定的InfoDB目录...

凤城市15379112020： mysql写入中文有乱码如何解决? - ？
燕彪替马： 1、看一下mysql的字符集:mysql> show variables like '%char%';2、看一下PHP和mysql的字符集是否一样.