【dbdao.com MySQL OCP认证专题】- MySQL 5.6 - OCP 考题讲解 - 9

biotwang

【dbdao.com MySQL OCP认证专题】- MySQL 5.6 - OCP 考题讲解

9.

A database exists as a read-intensive server that is operating with query_cache_type=DEMAND.
The database is refreshed periodically, but the resultset size of the queries does not fluctuate.

—-Note the following details about this environment:

• A web application uses a limited set of queries.
• The Query Cache hit rate is high.
• All resultsets fit into the Query Cache.
• All queries are configured to use the Query Cache successfully.
• The response times for queries have recently started to increase. The cause for this has correctly been identified as the increase in the number of concurrent users accessing the web service.
  

Based solely on the information provided, what is the most likely cause for this slowdown at the database level?

A. The Query Cache is pruning queries due to an increased number of requests.
B. Query_cache_min_res_unit has been exceeded, leading to an increased performance overhead due to additional memory block lookups.
C. Mutex contention on the Query Cache is forcing the queries to take longer due to its singlethreaded nature.
D. The average resultset of a query is increasing due to an increase in the number of users requiring SQL statement execution.

--------------------------------------------------------------------
答案：C

分析：这是一个读密集型数据库，数据库会在一段时间后刷新，但是其查询的结果集大小波动不大。而所有结果集都在Query Cache中，且网页应用使用一套有限的查询语句。且Query Cache hit rate很高。
因此A，D错，请求通过的应用查询，查询语句数量有限，结果集都能放在Query Cache中，相同查询语句的请求不会增多Query Cache中的资源的占用，因此清理查询并非主要矛盾。
B也错，因此Query_cache_min_res_unit设置过大，仅会造成Query Cache中碎片过多。如果请求的结果集都能在Query Cache中，这就和碎片没什么关系了。
C正确，尽管官方文档中未大量解释Query Cache Mutex争用问题，在线程运行查询语句时，会在Query Cache中先获取Mutex锁，之后开始查询匹配的查询语句和结果集。如果找到后返回结果。
如果未找到匹配，在执行查询后，需要将查询语句和结果集插入Query Cache中，这也会需要获取锁。尽管这个时间所需非常短，但是在读密集的情况下，资源争用会导致线程排队等待现象。

参考：
http://dev.mysql.com/doc/refman/5.7/en/query-cache.html
http://dev.mysql.com/doc/refman/ ... -configuration.html