mysql_query 堆栈毁坏 多线程_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