mysql配置优化实践

我们游戏采用了mysql作为数据库软件，几经优化，已经相对稳定，有连续几个月稳定运行的记录，不过最近发生了比较诡异的现象，数据库有100多个进程突然锁住，用show processlist命令查看，几分钟之内一直是这些进程在执行相同的sql，没有任何结束的迹象，导致tomcat应用服务器飙升到线程池的最大线程，500个线程，然后停止响应。

 

show processlist看到的lock现象很多都是memery engine的表或者myisam engine的表，偶尔会有innodb engine的表，这些lock都是死死的lock，没有一点缓解的现象，由于当时情况比较紧急，游戏内玩家都无法操作，因此运维一般都保留show processlist的记录，然后重启db，重启要花费20分钟左右。一般一个小时左右能恢复正常，玩家可以继续游戏。

 

这样的情况在某个服发生比较频繁，甚至一个礼拜发生一次，非常困扰，老大要求必须解决这个问题，因此用了不少工具去查询mysql的运行情况，包括 mysqlreport，mysql-turner等工具，这些在 <<high performance msyql 2ed>>中有推荐。这两个工具用下来，发现mysql-turner比较傻瓜式，会提供优化的建议，但是内容比较少，而mysqlreport提供了比较详细的mysql运行信息，大约有100行，而且对这些信息做了完善的注释。

 

在mysql运行正常的情况下，这两个工具看到的mysql状态都是正常的，只是一些小地方引起了我的注意: mysql query cache的配置，我们开启了mysql 的query cache，但是根据这篇文档, mysql query cache，query cache是一把双刃剑，用得不好反而对系统性能有影响，不少文章指出，innodb为主的数据库，不需要开启query cache，因为innodb有自己的buffer pool等机制，而且我们游戏中有大量的insert操作，会导致cache频繁失效。mysqlreport的报告显示，Qcache hit : insert = 0.4 :1 ，可见cache的命中率非常低，详细可见 http://hackmysql.com/mysqlreportguide#qc_report:ratios

 

后来主程又发了一个mysql query cache的bug过来，http://bugs.mysql.com/bug.php?id=43758 ，开启query cache可能导致thread驻留在'freeing items'状态，而我们采用的mysql版本正好在5.1.36版本之前，因此可能是这个问题导致的。因为每次死锁发生的时候，查看运维的系统负载日志发现，系统的load并不高。也有同事说是因为执行排行等比较耗时的存储过程导致的，我想如果是这个原因的话，最多是部分table被锁住，而且这个innodb的锁可以自行解锁，只是时间问题，从mysql的status看，一直有innodb-lock存在，之前战场的部分table发生锁的现象，也是会自行解锁，或者kill掉一些mysql进程之后可以解锁。

 

之后将mysql query cache关闭了，现在运行两天下来没有发现大的异常，一个明显改进的现象是，mysql的slow log数量减少了很多，从之前的几千降低到了几十，我们slow-log的最少时间是2s，另外inndodb-lock的平均时间减少到1/2，

 

Innodb_row_lock_time_avg

13ms

继续观察一下mysql的运行状态，希望能够在其他参数上做出优化，并且杜绝死锁的发生。