为什么MySQL里的ibdata1文件一直变大？

本文的原文地址在此：http://www.percona.com/blog/2013/08/20/why-is-the-ibdata1-file-continuously-growing-in-mysql/，以下是译文。
-----------------------------------------------------------这是一条分割线-----------------------------------------------------------

August 20, 2013 by Miguel Angel

我们在Percona Support上，经常会收到关于这个问题的提问。

这个问题的浅层来源是服务器监视程序发出了一个关于MySQL服务器存储的警告：服务器的磁盘马上就要满了。

在经过一系列排查以后，你会发现大部分的磁盘空间都被InnoDB的共享表空间文件ibdata1占用。可是你已经把innodb_file_per_table设为了1，为什么ibdata1文件还会这么大，ibdata1里到底都包含什么内容？

当把innodb_file_per_table设为了1以后，所有的表数据都存储在各自独立的表空间文件里，不过共享表空间文件（即ibdata1）依然存储了InnoDB的以下数据：

1. data dictionary aka metadata of InnoDB tables2. change buffer3. doublewrite buffer4. undo logs

有的数据在Percona Server中可以通过配置保存到其他地方，以避免ibdata1过大。例如你能够通过改变innodb_ibuf_max_size改变change buffer的大小，通过改变innodb_doublewrite_file，将doublewrite buffer的内容存储到一个单独的文件中。

如果是MySQL5.6，同样可以为undo log配置一个独立的表空间文件，而不是存储在ibdata1中。细节可查看官方文档。

是什么原因导致ibdata1增长过快？

当MySQL碰到这个问题时，我们通常会使用下面的命令：

SHOW ENGINE INNODB STATUS\G

这个命令会给我们提供非常有价值的信息。我们找到TRANSACTIONS这一节，并发现以下内容

---TRANSACTION 36E, ACTIVE 1256288 secMySQL thread id 42, OS thread handle 0x7f8baaccc700, query id 7900290 localhost rootshow engine innodb statusTrx read view will not see trx with id >= 36F, sees < 36F

这是最常见的问题：一个创建于14天前的事务，且状态是激活的（ACTIVE）。这就意味着从这个事务开始的至今，InnoDB将所有的旧数据都写入到了undo日志中。如果这个事务有很多写入任务，那么意味着ibdata1中存储了非常多的undo日志数据。

如果你当前没有任何长期运行的事务，INNODB STATUS同样可以为你提供另外一个有意义的变量：History list length，指在回滚空间中的未清除的事务数。当purge线程（老版本是master线程）删除undo记录的速度赶不上写入的速度时，也会造成History list length增大。

我如何查看ibdata1中存储的数据？

很遗憾，MySQL没有提供类似的工具。不过有另外两个工具可以查看ibdata1中的数据。其中之一是Mark Callaghan在这个bug反馈报告中提交的一个修改过的innochecksum（注意，MySQL自带的innochecksum是有bug的，使用后没有效果，如果要想用，需要自己下载bug反馈报告中的源代码编译。——译者注），非常易用的小东西，如下：

# <code>./innochecksum /var/lib/mysql/ibdata1</code>0 bad checksum13 FIL_PAGE_INDEX19272 FIL_PAGE_UNDO_LOG230 FIL_PAGE_INODE1 FIL_PAGE_IBUF_FREE_LIST892 FIL_PAGE_TYPE_ALLOCATED2 FIL_PAGE_IBUF_BITMAP195 FIL_PAGE_TYPE_SYS1 FIL_PAGE_TYPE_TRX_SYS1 FIL_PAGE_TYPE_FSP_HDR1 FIL_PAGE_TYPE_XDES0 FIL_PAGE_TYPE_BLOB0 FIL_PAGE_TYPE_ZBLOB0 other3 max index_id

这里UNDO_LOG占用了19272的大小，而所有的数据加起来才20608（13+19272+230+1+892+2+195+1+1+1），相当于当前ibdata1中，93%都被UNDO_LOG占用。

另外一种查看工具是由Jeremy Cole写的InnoDB Ruby Tools。这个一个用来检查InnoDB内部构成的更高级的工具。例如通过space-summary参数，我们能获得一个包含每一页（every page）的数据类型的清单。之后我们能使用标准的Unix工具获得UNDO_LOG所占页的总数

# innodb_space -f /var/lib/mysql/ibdata1 space-summary | grep UNDO_LOG | wc -l19272

尽管这个案例中innochecksum相比Jeremy的工具要快且容易一些，但我还是推荐你使用Jeremy的工具。因为这样你可以看到数据在InnoDB里如如何分布的，还可以看到InnoDB的内部情况。

好了，现在我们已经知道造成ibdata1庞大的原因了，那么下一个问题是：

我如何才能解决这个问题？

这个问题不难。如果你还能够提交（commit），就这么干。如果你已经无法提交，则只能杀掉这个线程，再使用rollback来重启。不过你需要注意，杀死进程只能停止ibdata1的继续变大，真正的问题原因是你的 软件有bug，或是其他什么原因造成了错误。现在你已经知道怎么找到问题在哪，你还需要用debug工具或日志来确定到底是什么导致了这些问题。

如果问题原因是purge线程导致的，那么你将其升级到最新版本，以使用purge线程取代master线程。更多细节请参阅官方文档。

是否有办法恢复这些用掉的磁盘空间？

没有，至少没有一个简单迅速的方法能做到这一点。InnoDB表空间从来不会变小>_<。这是一个历史悠久的bug（超过10年）：

当你删掉一些行，page只会被标为“已删除”，然后等待今后的使用，而不是归还这些空间。唯一的办法是让数据库使用一个全新的ibdata1。为了做到这一点，你需要使用mysqldump对整个数据库进行逻辑备份；之后停掉MySQL；在然后删除所有的数据文件（包括ib_logfile*和ibdata*文件）。之后在重启MySQL时，MySQL会重建表空间文件（大小与配置文件指定的相同——译者注）。再使用mysqldump恢复备份的数据。

总结

造成ibdata1大小飞速增长的最常见的原因，通常是MySQL中被我们所遗忘的，但长期处于激活状态的transaction。你必须要尽快解决这个问题（commit或kill这个transaction），因为你没有任何办法归还这些磁盘空间，除非你愿意忍受mysqldump那缓慢的备份/恢复过程。

要想避免这种问题，可以监控数据库。我们提供的MySQL监控插件能够及时的警告你那些长期处于激活状态的transaction。