转载

【MySQL】ibdata文件增大的原因

背景
早上和一个同事讨论技术问题，谈到ibdata文件会随着数据的使用而增大，而且在事务隔离级别RR 隔离级别下要比RC 隔离级别的大。本文尝试分析两个问题之一
a ibdata 文件随着数据库的使用而增大。
b RR 隔离级别下比RC 隔离级别下增长的快大。
ibdata1 存放哪些数据？
表数据/索引 (innodb_file_per_table=0 时)
data dictionary aka metadata of InnoDB tables
undo 表空间回滚段相关记录
doublewrite buffer
change buffer
什么原因导致ibdata 迅速增大呢？
从ibdata1文件存放的内容来分析
我们都知道innodb的表有两种存放方式：
当innodb_file_per_table=0时也即共享表空间方式,所有表的索引/数据统一存放在一个共享表空间中ibdata1文件，随着数据量的增大,共享表空间的文件大小也迅速增长,同时空间回收困难；
当innodb_file_per_table=1时也即独占表空间方式,也就是一张表一个表空间(ibd文件)，表中的索引和数据存放在独立的表空间中,执行drop/truncate 操作可以快速的回收独立表空间。
对于buffer 可以分别使用参数 innodb_doublewrite_file ，innodb_ibuf_max_size控制大小，他们带来的空间增长可以忽略不计，最终 ibdata最终的大小取决于事务的大小/长短。
a 数据库系统中存在长时间未提交的事务,或者在同一个事务中更新/插入很多数据，导致innodb需要维护很大的undo segments来保障一致性读。
通过执行命令
SHOW ENGINE INNODB STATUSG
---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
例子中显示有一个长达 1256288秒(14年)的事务未提交。active 意味着innodb在undo segment里面创建数据的快照以便提供一致性读。如果数据库存在大量的长事务，就要耗费大量的undo segment。
b 5.5版本之前 purge undo thread是和master thread 共用的。存在大量长事务的时候导致purge undo的速度小于undo segment产生的速度。通过 innodb 参数 History list length

------------
TRANSACTIONS
------------
Trx id counter 43831607347
Purge done for trx's n:o < 43831607342 undo n:o < 0 state: running but idle
History list length 2308
LIST OF TRANSACTIONS FOR EACH SESSION:

History list length 2308 表示有 2308个事务没有清理，过大的值意味着purge thread 速度达到了瓶颈。5.5 版本开始MySQL 将purge thread 和master thread 分开，我们可以通过调整参数来加快purge undo的速度。
# yzsql 3311 param purge

Variable_name Value
innodb_max_purge_lag 0
innodb_max_purge_lag_delay 0
innodb_purge_batch_size 300
innodb_purge_threads 12

如何查看ibdata文件中的内容呢？
MySQL 官方并没有提供工具查ibata存储了什么内容，不过我们可以通过如下两种工具
innochecksum，(感谢 Mark Callaghan)。
./innochecksum /var/lib/mysql/ibdata1

0 bad checksum
13 FIL_PAGE_INDEX
19272 FIL_PAGE_UNDO_LOG --占用了总ibdata1 的93%
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占用了总ibdata1 的93%。
第二个工具：innodb_space ( made by Jeremy Col)可以清晰地分析出ibdata1的组成（该工具需要bindata环境）

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

如何解决 ibdata1 不停的增大呢？
坦白的说我们没有方法阻止其不停的增大，但是我们可以使用
1 规范开发同学的数据库使用习惯，使用短小的事务替代大事务，并确保每个事务都有commit机制。
2 增加purge thread 数量，加快purge undo的速度尽快释放undo空间。
3 升级到5.6 版本独立出undo 表空间来保持ibdata文件在一个合理的大小。
当然我们也没有优雅的办法上增大的ibdata文件缩小，这个文件只增加不减小。

参考资料
[1] Reasons for run-away main Innodb Tablespace
[2] Why is the ibdata1 file continuously growing in MySQL?
[3] http://bugs.mysql.com/bug.php?id=1341

正文到此结束