mysql history过大_Mysql History list length 值太大引起的问题

1.环境

Mysql 主从

Mysql版本：5.1.49-log

系统：Red Hat Enterprise Linux Server release 5.4  64bit

2.表面现象

数据库操作变慢，如用主键作为条件查询，有时也会超过1秒；

主库IO使用率一直在90%以上(平常io比较低20%以内)，dirty页占总数的90%左右，脏页刷不完；持续了几个小时~

3. 从系统上看

主库的iostat的使用率一直在90%以上，Mysql data所在目录的大小一直没变；主库的ib_logfile 20分钟切换一次(主库上面的innodb log写得还是比较频繁)，而从库的ib_logfile 4小时切换一次。

4.从mysql上看

在主库 Buffer pool hit rate 993 / 1000  innodb的命中率一直维持在99%左右；

Innodb_buffer_pool_pages_dirty的大小一直在增长，dirty页占总数的90%左右，脏页刷不完。但这些脏页不像是正常操作产生的，因为主库上的读写操作都不大。并且从库上的同步没有延时，而且从库的dirty页也很少。

5. 从监控mysql报表上看

从监控到的mysql的读写，跟往常没什么区别；

而innodb 的Data_free从开始(100MB以内)

所有innodb的数据+索引的大小为：47.2GB

到恢复正常时data_free的大小为：32.3GB

所有innodb的数据+索引的大小为：45GB

可见在在innodb数据+索引基本上没减少的情况下，data_free变成32GB，这32GB来自undo的释放？为什么一下子释放这么多undo？

6. 再看看出现问题前的操作

原来3306实例下，有多个数据库，在出现问题前，刚好将一个数据库(频繁更新)迁移至其他服务器。迁移数据库 跟 这个问题 是巧合or触发？

7. 问题的原因

Undo 的释放？查看show engine innodb status中的：History list length(History list length是指在回滚空间中的未清除事务数。随着事务的提交，它的值会增加；随着清除线程的运行，它的值会减小)

发现History list length的值很大，再查回以前的crt的log中的History list length，最近这个值一直在增长，一直增长到：78883366(竟然有这么多的事务数未清除)

再看看出问题的时候，History list length的变化情况(从crt log中过滤出来，还好平时记录了操作的crt log；这回用上了)：

# grep "History list length" app190.log

History list length 78883366

History list length 73091889

History list length 70069534

History list length 66194783

History list length 62209735

History list length 49672090

History list length 31489057

History list length 30877319

History list length 33

到恢复正常时，History list length维持在100以内。

8. 查找History list length值太大的原因

为什么History list length一直未清理，innodb 在什么时候清理这些事务数？

看看innodb master thread的操作(参考：mysql技术内幕innodb存储引擎)

每10秒的操作包括：

刷新100个脏页到磁盘(可能)注：后面的版本用 innodb_io_capacity调整

合并至多5个插入缓冲(总是)

将日志缓冲刷新到磁盘(总是)

删除无用的undo页(总是)

刷新100个或者10个脏页到磁盘(总是)

产生一个检查点(总是)

Background loop 在没有用户活动(数据库空闲时)或者数据库关闭时，就会切换到这个循环，包括以下操作：

删除无用的undo页(总是)

合并20个插入缓冲(总是)

调回到主循环(总是)

不断刷新100个页，直到符合条件(可能，跳转到flush loop中完成)

由上可见，每10秒会删除无用的undo页，但为什么History list length还是这么大？

导致History list length值太大的原因，是跟迁移走的数据库(频繁更新)有关；这个数据库的应用，是采用连接池来连接mysql，并且mysql中使用的隔离级别是默认的：REPEATABLE-READ。在这个级别下，保证了多次读的结果一样(跟MVCC有关)。

而这个业务对于失败的事务不做任何操作，导致可能存在这个事务一直没commit，为了保持隔离级别REPEATABLE-READ的特性，在undo中一直保留了这个事务以后的版本号，也就导致未清理的事务数越来越大，History list length值也就越大。

可见，出现主库IO使用率一直在90%以上，是因为mysql在清理undo中的事务数。但有什么方法可以避免History list length的值太大？

9. 避免History list length值太大

1.更改业务程序中的逻辑，比如对失败事务做相关的操作；

2.在对业务没影响的情况下，可用隔离级别：read committed，在这个事务隔离级别下，只有事务commit，就会去清理undo中的事务(每10秒)。

由于对程序的更改没这么快，而且开发确认业务在 read committed 下也没影响，所以将业务的隔离级别更改为read committed：

在mysql中执行：set

globaltransaction isolation level read committed;重启业务，重连数据库后生效，更改之后History list length基本上保持在100以内。

1 show global variables like ‘%iso%‘;2 set global transaction isolation level read committed;3

4 pager grep "History list";5 show innodb status;6 nopager;

参考：

原文：http://www.cnblogs.com/xiaotengyi/p/3572719.html