Useful knowledge, thank you, Shawn. Good to see confirmed that qcache_hits + com_select is the global total - that's not always very clear in the docs.
I just noticed that when I copied the list for extra exposure, I didn't actually say what I was exposing :-p Gerhard Laußer, who maintains the check_mysql_health nagios plug-in, is currently re-factoring the code and has a poll open about whether to change the logic behind the query cache hit rate, and to what. The poll (and check) are at https://labs.consol.de/nagios/check_mysql_health/ . /Johan ----- Original Message ----- > From: "Shawn Green" <shawn.l.gr...@oracle.com> > To: "MySql" <mysql@lists.mysql.com> > Sent: Friday, 15 July, 2016 18:30:39 > Subject: Re: check_mysql_health poll > Excellent advice. > > If you read through the code, you will find that every SELECT command > will either hit the query cache (incrementing Qcache_hits) or require > execution to evaluate (incrementing Com_select). So for an average of > your Query Cache efficiency since the last restart (or the last > statistics reset) use this formula > > Efficiency in % = (Qcache_hits)/(Qcache_hits + Com_select) * 100 > > To get an average efficiency over a span of time, execute a SHOW GLOBAL > STATUS report at the start of the span and another at the end of the > span then compute that formula comparing the changes in those counters > (the deltas). > > Another way to look at reuse rate is to estimate how quickly you are > turning over the content of the Query Cache. Let's say your > Qcache_inserts rate is about 500/sec and on average you have about 5000 > queries in the cache. This gives you a very rough lifetime of about 10 > seconds for any single query result in the cache. If you are not seeing > a lot of lowmem prunes during this period, then those existing query > results are not being forced out of the cache due to space restrictions > (age), they are most likely being removed automatically due to changes > happening to the tables they are based on. > > In most cases, you gain efficiency by removing the mutex that protects > the content of the Query Cache and allowing all incoming commands to > execute in parallel rather than being serialized via that cache mutex. > You do this by setting --query-cache-type=0 (or OFF) not just by > allocating no space to the buffer. This is particularly true if you > * have a low reuse rate > * have a high churn rate > * do not have a large population of queries that are repeated (exactly) > against sets of tables that change rarely. > > -- > Shawn Green > MySQL Senior Principal Technical Support Engineer > Oracle USA, Inc. - Integrated Cloud Applications & Platform Services > Office: Blountville, TN > > Become certified in MySQL! Visit https://www.mysql.com/certification/ > for details. > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql -- Unhappiness is discouraged and will be corrected with kitten pictures. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
