Re: check_mysql_health poll

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" <[email protected]>
> To: "MySql" <[email protected]>
> 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