为什么 MySQL里的 ibdata1 文件不断增长

ibdata1存了什么？ 当你启用了

innodb_file_per_table，表被存储在他们自己的表空间里，但是共享表空间仍然在存储其它的 InnoDB 内部数据：

数据字典，也就是 InnoDB 表的元数据 变更缓冲区 双写缓冲区 撤销日志 其中的一些在 Percona

服务器上可以被配置来避免增长过大的。例如你可以通过 innodbibufmax_size设置最大变更缓冲区，或设置

innodbdoublewritefile 来将双写缓冲区存储到一个分离的文件。 MySQL 5.6

版中你也可以创建外部的撤销表空间，所以它们可以放到自己的文件来替代存储到 ibdata1。可以看看这个文档。 什么引起 ibdata1

增长迅速？ 当 MySQL 出现问题通常我们需要执行的第一个命令是： SHOW ENGINE INNODB STATUS/G

这将展示给我们一些很有价值的信息。我们从** TRANSACTION（事务）**部分开始检查，然后我们会发现这个： ---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天前创建的相当老的事务。这个状态是活动的，这意味着 InnoDB

已经创建了一个数据的快照，所以需要在撤销日志中维护旧页面，以保障数据库的一致性视图，直到事务开始。如果你的数据库有大量的写入任务，那就意味着存储了大量的撤销页。

如果你找不到任何长时间运行的事务，你也可以监控INNODB STATUS 中的其他的变量，“History list

length（历史记录列表长度）”展示了一些等待清除 *** 作。这种情况下问题经常发生，因为清除线程（或者老版本的主线程）不能像这些记录进来的速度一样快地处理撤销。

我怎么检查什么被存储到了 ibdata1 里了？ 很不幸，MySQL 不提供查看什么被存储到 ibdata1

共享表空间的信息，但是有两个工具将会很有帮助。第一个是马克·卡拉汉制作的一个修改版 innochecksum ，它发布在这个漏洞报告里。

它相当易于使用： # ./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%。 第二个检查表空间内容的方式是杰里米·科尔制作的 InnoDB Ruby 工具。它是个检查 InnoDB

的内部结构的更先进的工具。例如我们可以使用 space-summary 参数来得到每个页面及其数据类型的列表。我们可以使用标准的 Unix

工具来统计撤销日志页的数量： # innodb_space -f /var/lib/mysql/ibdata1 space-summary |

grep UNDO_LOG | wc -l 19272 尽管这种特殊的情况下，innochedcksum

更快更容易使用，但是我推荐你使用杰里米的工具去了解更多的 InnoDB 内部的数据分布及其内部结构。 好，现在我们知道问题所在了。下一个问题：

我该怎么解决问题？ 这个问题的答案很简单。如果你还能提交语句，就做吧。如果不能的话，你必须要杀掉线程开始回滚过程。那将停止 ibdata1

的增长，但是很显然，你的软件会出现漏洞，有些人会遇到错误。现在你知道如何去鉴定问题所在，你需要使用你自己的调试工具或普通的查询日志来找出谁或者什么引起的问题。

如果问题发生在清除线程，解决方法通常是升级到新版本，新版中使用一个独立的清除线程替代主线程。更多信息查看该文档

有什么方法回收已使用的空间么？ 没有，目前还没有一个容易并且快速的方法。InnoDB 表空间从不收缩...参见10

年之久的漏洞报告，最新更新自詹姆斯·戴（谢谢）： 当你删除一些行，这个页被标为已删除稍后重用，但是这个空间从不会被回收。唯一的方法是使用新的

ibdata1 启动数据库。要做这个你应该需要使用 mysqldump 做一个逻辑全备份，然后停止 MySQL

并删除所有数据库、ib_logfile、ibdata1 文件。当你再启动 MySQL 的时候将会创建一个新的共享表空间。然后恢复逻辑备份。

方法1．使用reset master命令

方法2．使用purge master logs to命令

方法3．使用purge master logs before命令

方法4．在my.ini配置文件[mysqld]选项组中设置expire_logs_days参数

摘抄自11.2.6 二进制日志的清理 MySQL核心技术与最佳实践！

有没有办法让MySQL中不慢日志rows

一个普通WEB站点的页面常常需要查询N条SQL语句后才能得出页面结果，当网站访问速度慢而前端做了大量优化工作以后，数据库瓶颈的查找也是WEB优化的一个重要部分。

MySQL中提供了一个慢查询的日志记录功能，可以把查询SQL语句时间大于多少秒的语句写入慢查询日志，日常维护中可以通过慢查询日志的记录信息快速准确地判断问题所在。

开启慢查询功能

log-slow-queries 慢查询日志文件路径

long_query_time 超过多少秒的查询就写入日志

打开my.cnf配置文件，加入以下代码：

log-slow-queries = /tmp/mysql-slow.log

long_query_time = 2

如果是windows则在my.ini中加入

my.ini

复制代码 代码如下:

log_slow_queries

long_query_time = 2

保存退出，重启MySQL即可。

关于long_query_time设置

通常我们设置long_query_time的值为2，表示查询SQL语句超过两秒的就记录，通常2秒就够了，默认是10秒。然而，对于许多WEB程序来说，2秒的查询还是太长了。的确在许多站点中，一个SQL语句超过1秒的执行时间都算慢的了。

mysql5.1.21以后才提供更细粒度的long_query_time设定，之前的版本只能以秒做单位。

查看日志

复制代码 代码如下:

[root@lizhong tmp]# tail -f /tmp/mysql_slow.log

Time: 120815 23:22:11

User@Host: root[root] @ localhost []

Query_time: 9.869362 Lock_time: 0.000035 Rows_sent: 1 Rows_examined: 6261774

SET timestamp=1294388531

select count(*) from blog

第一行：执行时间

第二行：执行用户

第三行（重要）：

Query_time SQL执行的时间,越长则越慢

Lock_time 在MySQL服务器阶段(不是在存储引擎阶段)等待表锁时间

Rows_sent 查询返回的行数

Rows_examined 查询检查的行数

最后

1、日志不能说明一切问题，知识表象，可能跟锁表、系统繁忙的偶发性有关，当然，如果某条SQL语句经常查询慢那基本可以判断是可以再次优化的。

2、不要开启log-queries-not-using-indexes没有索引查询记录功能，这个功能实际用处不大。就是记录SQL查询的时候，没有索引的通通记录。虽然索引对查询的速度有影响，但要看数据量大小。因为开启了这个功能以后，select * from tab这样的查询也会被记录在日志中，很快日志文件就会被垃圾信息给充满，从而影响主要的查询慢日志记录的查看。

3、MySQL自带了mysqldumpslow工具用来分析slow query日志，或者其它工具也可以，通过工具配合可以更好的分析。

---