MySQL的响应时间变慢

关于查询缓存

默认关闭状态

query_cache_size太大,锁争用问题导致查询变慢(毫秒级)
注意: 如果将query_cache_size的大小设置为0,也不会用到查询缓存.
query_cache_limit太大,只是增加能够被缓存的数量
"如果查询缓存中有许多查询,缓存失效就会需要很长的时间并且延续整个系统的工作.因为查询缓存有一个全局锁,它会阻塞所有查询缓存的查询."

# 没有用到查询缓存
16:33:52 127.0.0.1:3306 information_schema>show profile for query 8;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000014 |
| checking permissions | 0.000002 |
| Opening tables       | 0.000007 |
| System lock          | 0.000004 |
| init                 | 0.000006 |
| optimizing           | 0.000001 |
| statistics           | 0.000007 |
| preparing            | 0.000004 |
| executing            | 0.000001 |
| Sending data         | 0.000005 |
| end                  | 0.000001 |
| query end            | 0.000001 |
| closing tables       | 0.000003 |
| freeing items        | 0.000013 |
| logging slow query   | 0.000001 |
| cleaning up          | 0.000002 |
+----------------------+----------+
16 rows in set (0.00 sec)

# 用到了查询缓存
16:36:15 127.0.0.1:3306 information_schema>show profile for query 1;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000015 |
| Waiting for query cache lock   | 0.000002 |
| Waiting on query cache mutex   | 0.000002 |
| checking query cache for query | 0.000059 |
| checking permissions           | 0.000006 |
| Opening tables                 | 0.000023 |
| System lock                    | 0.000013 |
| init                           | 0.000018 |
| optimizing                     | 0.000004 |
| statistics                     | 0.000022 |
| preparing                      | 0.000010 |
| executing                      | 0.000003 |
| Sending data                   | 0.000011 |
| end                            | 0.000003 |
| query end                      | 0.000002 |
| closing tables                 | 0.000007 |
| freeing items                  | 0.000019 |
| logging slow query             | 0.000002 |
| cleaning up                    | 0.000003 |
+--------------------------------+----------+
19 rows in set (0.00 sec)

事件回放

2012-06-18 星期一
1. 发现酒店点评的业务监控图有超时现象(从数据库取回数据的时间大于300ms),见附图1
2. 查看l-hoteldb5.h.cn6的慢查询日志.发现大量的2-3秒慢查询,基本访问qunar_comment.comments表,而且有group by操作
3. 对比以前的l-commdb2.h.cn2配置,发现l-hoteldb5/6.h.cn6都没有打开查询缓存.
4. 在晚上12点时,切换写vip,修改配置文件,打开db5/6的查询缓存参数,重启mysql.
5. 当时查看有大量sql被缓存起来,但是命中率不高.

2012-06-19 星期二
1. 问题依然存在,而且有过之而无不及.
2. 继续查看l-hoteldb5.h.cn6的问题,经刘玥,吴凌峰查看,发现l-hoteldb5.h.cn6的磁盘IO已经出现io_wait
3. 发现磁盘问题之后,将3306端口的写VIP切换到l-hoteldb6.h.cn6上面.分离与hms写库的IO压力.
4. 切换后的短时间内有改善效果,但并未完全解决.
5. 晚上10点40分左右,随着流量上升,该问题更为严重.
6. 晚上11点,修改程序,将超时时间由300ms延长至1s,然后发布程序,并将写vip切回l-hoteldb5.h.cn6
7. 问题暂时得到缓解.

2012-06-20 星期三
1. 由于超时现象依然存在,不得不思考长久的解决办法
2. 一方面优化系统和MySQL,另一方面讨论将qunar_comment等库迁移到l-hoteldb7/8.h.cn6上面
3. MySQL方面的优化,tmp_table_size由64M->512M,sort_buffer_size由32M->2M,flush_log_at_trx_commit由1->2
4. 系统方面,查看了多个MySQL实例占用CPU资源的问题
5. 下午17点40分左右,因为查询缓存的命中率实在低的要命,所以产生了再次关闭的想法.
6. 接下来,奇迹出现了,业务监控图短时间内没有出现报警.附图2
7. 后续观察,也没有出现之前那种频繁的报警,偶尔出现1,2次,属于正常现象.

对于MySQL反映慢的问题总结:
1. 首先查看系统是否出现磁盘IO瓶颈.
2. MySQL的查询缓存是否起作用了,效率如何.
3. 关于MySQL的query_cache_size大小问题,个人理解如下(已经更正):
官方文档参考:
http://dev.mysql.com/doc/refman/5.5/en/query-cache-configuration.html
http://bugs.mysql.com/bug.php?id=60074
http://www.dbasquare.com/kb/how-query-cache-can-cause-performance-problems/
关于” Be careful not to set the size of the cache too large. Due to the need for threads to lock the cache during updates, you may see lock contention issues with a very large cache.”
这句话的描述,它是对query_cache_size的大小进行说明.
关于查询缓存的锁问题,如果查询缓存中有许多查询,缓存失效就会需要很长的时间并且延续整个系统的工作.因为查询缓存有一个全局锁,它会阻塞所有查询缓存的查询.

邮件交流

附上一封来自刘玥的回复
时间 2012-6-21
主题 MySQL响应变慢处理一例_酒店评论

呃。。。不准确，其实我电话里跟你解释过了，这跟 query_cache_limit关系不太大，锁竞争也不是这么竞争的，只是query_cache_limit设得过大会导致query_cache的内存占用变大，因为很多不应该被缓存的查询结果也被缓存了。query_cache是mysql server级的缓存，只要设置了query_cache，那么每个sql过来都会去查query_cache，看是否可以命中，无一例外，这看起来很 好，但是问题在于它的锁机制，当数据表被更新时，mysql server用了一把大的拍他锁，注意，只有一把，而且拍他，锁住了整个query cache，然后遍历整个query cache，把所有涉及到更新的表的query items清理掉，这决定了所谓的query cache越大，锁争用越多query cache的作用在于读远远大于写的情况，但看起来评论数据库的读写比是70%多，并不能算是读远远大于写，而且对于innodb存储引擎来， innodb buffer的作用远远大于query cache这种myisam时代的产物。不建议在生产数据库（我们规定只允许适用innodb）上打开query cache，配好innodb_buffer_pool_size比什么都有用
如有问题随时提，附上参考文章
http://www.dbasquare.com/kb/how-query-cache-can-cause-performance-problems/