Re: check_mysql_health poll
- Date: Sat, 16 Jul 2016 12:42:25 +0200 (CEST)
- From: Johan De Meersman <vegivamp@xxxxxxxxx>
- Subject: 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/ .
----- Original Message -----
> From: "Shawn Green" <shawn.l.green@xxxxxxxxxx>
> To: "MySql" <mysql@xxxxxxxxxxxxxxx>
> 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