Oracle数据库数据恢复、性能优化来问问AskMaclean - ParnassusData诗檀软件旗下网站

找回密码
注册
搜索
热搜: 活动 交友 discuz
发新帖

205

积分

14

好友

29

主题
发表于 2016-7-19 17:18:31 | 查看: 409| 回复: 0
【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
您需要登录后才可以回帖 登录 | 注册

扫码加入微信Oracle小密圈


QQ|手机版|Archiver|Oracle数据库数据恢复、性能优化来问问AskMaclean - ParnassusData诗檀软件旗下网站

GMT+8, 2018-1-23 15:24 , Processed in 0.066164 second(s), 22 queries .

Powered by Discuz! X2.5

© 2001-2012 Comsenz Inc.

回顶部
TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569