mysql query cache lock导致的性能问题

最近线上今天出现detail页面很慢的情况，通过btrace定位到是这条SQL：

select id,status,convert(buyable,UNSIGNED) as buyable,category,sender_id,album_id,parent_id,add_datetime,favorite_count,like_count,photo_id,group_id from message_message where id=?

 

 

但这条SQL通过pk查询不应该慢呀，去mysql 执行show processlist发现大量thread 在停在

Waiting on query cache mutex

 

 

处理，线上观察一台mysql慢，一台不满：

s10:

mysql> show variables like '%query_cache_size%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| query_cache_size | 0     |
+------------------+-------+
1 row in set (0.00 sec)

mysql> show variables like '%query_cache_size%';
+------------------+-----------+
| Variable_name    | Value     |
+------------------+-----------+
| query_cache_size | 134217728 |
+------------------+-----------+
1 row in set (0.00 sec)

 

mysql> show variables like '%query_cache_size%';
+------------------+-----------+
| Variable_name    | Value     |
+------------------+-----------+
| query_cache_size | 134217728 |
+------------------+-----------+
1 row in set (0.00 sec)

mysql> set global query_cache_size=0;
Query OK, 0 rows affected (0.42 sec)

mysql> show variables like '%query_cache_size%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| query_cache_size | 0     |
+------------------+-------+
1 row in set (0.00 sec)

参考：

http://www.mysqlperformanceblog.com/2012/09/05/write-contentions-on-the-query-cache/

http://dinglin.iteye.com/blog/1625195

http://dev.mysql.com/doc/refman/5.0/en/query-cache-configuration.html