Mysql ibdata1简述

What is stored in ibdata1?

当启用innodb_file_per_table时，表存储在它们自己的表空间中，但共享表空间仍用于存储其他InnoDB的内部数据：

    数据字典也就是InnoDB表的元数据
    改变缓冲区
    双写缓冲区
    撤消日志

其中一些可以在Percona Server上配置，以避免变得太大。例如，您可以使用innodb_ibuf_max_size为更改缓冲区设置最大大小，或者使用innodb_doublewrite_file将doublewrite缓冲区存储在单独的文件中。

在MySQL 5.6中，您还可以创建外部UNDO表空间，以便它们位于自己的文件中，而不是存储在ibdata1中。文档链接：http://dev.mysql.com/doc/refman/5.6/en/innodb-performance.html#innodb-undo-tablespace


What is causing the ibdata1 to grow that fast?
当出现MySQL问题时、通畅需要运行的第一个命令是：

SHOW ENGINE INNODB STATUSG

这将展示非常有价值的信息。开始从检查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中维护旧页面，以便能够在启动事务后提供数据库的一致视图。如果数据库被大量写入任务，则意味着存储了大量的撤消页面。

如果找不到任何长时间运行的事务，还可以监控INNODB STATUS中的另一个变量，即“History list length（历史记录列表长度）”。它显示待处理的清除操作的数量。在这种情况下，问题通常是由于清除线程（或旧版本中的主线程）无法以与它们进入的速度相同的速度处理撤消记录。

How can I check what is being stored in the ibdata1?
不幸的是，MySQL没有提供ibdata1共享表空间上存储内容的信息，但有两个工具非常有用。首先是由Mark Callaghan制作并在此错误报告中发表的innochecksum的修改版本。http://bugs.mysql.com/bug.php?id=57611

它很容易使用：

 ./innochecksum /var/lib/mysql/ibdata1
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

全部的 20608 中有 19272 个撤销日志页。这占用了表空间的 93%。

 

检查表空间内容的第二种方法是由Jeremy Cole制作的InnoDB Ruby工具。它是检查InnoDB内部的更高级工具。例如，我们可以使用space-summary参数来获取包含每个页面及其数据类型的列表。我们可以使用标准的Unix工具来获取UNDO_LOG页面的数量：

 innodb_space -f /var/lib/mysql/ibdata1 space-summary | grep UNDO_LOG | wc -l
19272

 

尽管这种特殊的情况下，innochedcksum 更快更容易使用，但是推荐使用杰里米的工具去了解更多的 InnoDB 内部的数据分布及其内部结构。
好，现在知道问题所在了。下一个问题：

How can I solve the problem?
这个问题的答案很简单。如果仍然可以提交该查询，请执行此操作。如果不是，将不得不杀死线程以启动回滚过程。这将阻止ibdata1的增长，但显然某个软件有一个错误或有人犯了错误。既然已经知道如何确定问题所在，所以需要使用自己的调试工具或常规查询日志查找导致问题的人员或原因。

如果问题是由清除线程引起的，那么解决方案通常是升级到更新的版本，可以使用专用的清除线程而不是主线程。有关以下文档链接的更多信息。http://dev.mysql.com/doc/innodb/1.1/en/innodb-improved-purge-scheduling.html

Is there any way to recover the used space?
不，至少在一个简单快捷的方式是不可能的。 InnoDB表空间从未缩小
请参阅James Day最近更新的以下10年的错误报告http://bugs.mysql.com/bug.php?id=1341

删除某些行时，页面将标记为已删除，以便稍后重复使用，但永远不会恢复该空间。唯一的方法是使用新的ibdata1启动数据库。要做到这一点，需要使用mysqldump进行完整的逻辑备份。然后停止MySQL并删除所有数据库，ib_logfile *和ibdata *文件。再次启动MySQL时，它将创建一个新的共享表空间。然后，恢复逻辑转储


Summary
当ibdata1文件在MySQL中增长太快时，通常是Mysql长时间运行被遗忘的事务引起的。尝试尽快解决问题（提交或终止事务），因为如果没有缓慢的mysqldump进程，将无法回收浪费的磁盘空间。

还建议监视数据库以避免这类问题。MySQL监控插件包含一个Nagios脚本，如果发现运行的事务太旧，可以提醒。


本文出处：https://www.percona.com/blog/2013/08/20/why-is-the-ibdata1-file-continuously-growing-in-mysql/