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

我们在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 tables
2. change buffer
3. doublewrite buffer
4. 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 sec
MySQL thread id 42, OS thread handle 0x7f8baaccc700, query id 7900290 localhost root
show engine innodb status
Trx 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 checksum
13 FIL_PAGE_INDEX
19272 FIL_PAGE_UNDO_LOG
230 FIL_PAGE_INODE
1 FIL_PAGE_IBUF_FREE_LIST
892 FIL_PAGE_TYPE_ALLOCATED
2 FIL_PAGE_IBUF_BITMAP
195 FIL_PAGE_TYPE_SYS
1 FIL_PAGE_TYPE_TRX_SYS
1 FIL_PAGE_TYPE_FSP_HDR
1 FIL_PAGE_TYPE_XDES
0 FIL_PAGE_TYPE_BLOB
0 FIL_PAGE_TYPE_ZBLOB
0 other
3 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。

关注GitHub今日热榜，专注挖掘好用的开发工具，致力于分享优质高效的工具、资源、插件等，助力开发者成长！