MySQL案例分析--QueryCache-阿里云开发者社区

开发者社区> 数据库> 正文
登录阅读全文

MySQL案例分析--QueryCache

简介: QueryCache联动内容:http://blog.itpub.net/29510932/viewspace-1694922/ --------------------------------------------------------------------...
QueryCache联动内容:http://blog.itpub.net/29510932/viewspace-1694922/
-------------------------------------------------------------------------------------------------正文---------------------------------------------------------------------------------------------------------------
案例发生于生产环境,由于是临时的技术支持,截图没有了.....

场景:
M
ySQL-5.5.47, 隔离级别RR

背景:
业务人员反应数据库操作时不时出现非常明显的卡顿, 与此同时慢日志中出现大量的SQL;
问题的现象&排查:
1.先看监控图表: 发现CPU使用率,IO吞吐量, IO Utils, 网卡in/out流量, 内存的使用率都没有异常现象----应该不是服务器资源造成的;
2.登录到生产库,看一下processlist的情况,并没有明显的长连接或者长事务,咨询了开发人员,在应用层也没有手动开启长事务----应该和特殊的SQL或者事务没什么关系;
3.开发人员反馈并没有触发器/存储过程/定时任务----又排除几个影响因素;
4.查看了一下当天的慢日志,在某一个随机的时间点, 会出现很多的慢查询, 而且慢查询都是成片成片出现的, 成片出现的慢查询都有相同的TIMESTAMP----随机事件,可能是非人为的;
5.这种成片出现的慢查询, 全部是以Insert, Update这种DML作为前N条;

问题分析:
在排查过程中,初步得出的结论:
可能是SQL的问题; 而且设置的隔离级别是RR, 结合排查的第五点,感觉可能会是锁等待导致的;
在生产环境查看了一下这些DML表的结构,发现相关的索引都有, 也在测试环境试了一下, 发现这些DML并没有出现锁等待的现象;

用mysqldumpslow给某个时间点的近300条慢查询语句做了个统计, 发现DML语句大约只有10%多一点的样子,绝大多数还是select;
之后粗略看了一下这些select语句, explain的结果基本都是const, 说明这些语句本身并没有问题;

在这个过程中,发现一个现象:
这些select语句,约70%都是DML的那几张表, 而且不论什么类型select语句,记录下来的时间都是基本一致的,2.78-2.80秒之间;
疑点1:虽然不是很确定这种现象是不是有什么意义, 不过看上去像是由于这几张表进行了DML, 结果堵住了很多对这几张表的查询;
疑点2:除非是IO出现了波动或者峰值/没有索引/锁等待, 否则DML语句应该很少会出现在慢查询日志里面;

猜测是cache或者是buffer的设置问题, 于是去my.cnf检查一下配置, 然后看到了query_cache_size的设置;query_cache_size=256M
参考联动内容,其实Query Cache远没有发挥想象中
作用,不过>=5.6.8和5.7的版本中,query_cache_type默认都是关闭的, 5.5并不太清楚, 难道是这个Query_cache的问题?
翻了一下5.5的文档,发现query_cache_type的默认值是开启的, 但是size的默认值是0, 意味着如果什么都不动,那就是屏蔽了Query Cache,
然而配置文件里面修改了size, 所以Query Cache就开启了;

Query Cache的缺陷,在另一篇博文里面有描述, 对应到这次案例中的疑点1和疑点2, 做出几个推断:
在某个时间点内,应用层发起较多的DML,这些DML会尝试获取cache中某几张表的mutex,所以在慢查询中,在每个时间点,都是最先出现了Insert和Update(互相等待mutex);
由于Query Cache中,由于相关表的数据被清理,select会重新生成对应的result写入到Query Cache, 并且还存在并发的DML语句,导致频繁的在清空和写入Query Cache;
由于Query Cache设置得比较大,如果保存了大量的数据,那么在获取mutex, 并清理数据的时候, 也会消耗更多的时间;

联系运维人员,查看了一下生产库的Qcache的status,如下图

可以看到在mysql启动之后,Qcache触发了约1.2亿次的insert,但是只有约870万次的缓存命中;
这一组统计数据也基本验证了Query Cache不好用这一事实;

处理方式:
考虑到Qcache的统计结果,在线上库关掉应该也不会有什么问题,所以和相关运维人员说明了情况,在最近的维护时间内关掉这个Query Cache,然后观察一段时间;
反馈结果:
卡顿现象暂时消失了,慢日志也不再出现;
PS:Query Cache的问题多多,除非几乎没有DML,否则还是尽量不要开启的比较好

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享: