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" <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
Re: check_mysql_health poll
On 7/15/2016 6:58 AM, Johan De Meersman wrote: Hey, I just happened upon your poll, so I'm sending you brief mail because I have a different opinion still :-) I'm also CCing the MySQL list, as I feel that more input on this might be a good thing - and it's worth some exposure anyway. I believe there are two distinct measures that can be taken: * Ratio of selects that were returned from the cache against total server queries (caching ratio) * Ratio of selects that were served from cache against selects that were inserted into the cache (statement reuse ratio) The former gives an indication of how many queries were served from the cache against the total number of questions asked. It's a useful measure to see wether it's worth the effort to see if there's ways to rewrite queries or code so that more queries become cacheable. Given that https://dev.mysql.com/doc/refman/5.7/en/server-status-variables.html#statvar_Com_xxx explicitly states that queries served from cache do NOT increment com_select, I believe that calculation requires qcache_hits, qcache_inserts AND com_select. I'm not clear on wether qcache_not_cached augments com_select, though I would suspect it does. Even if this ratio is relatively low, it's not necessarily a problem - every query served from cache is a parse/exec saved. On multitenancy you could have a database that benefits hugely from the cache, and ten others that hardly use it, and that is not a problem as such. The latter, on the other hand, tells you how many of the queries that were inserted into the cache, are actually served from cache afterwards. This requires only qcache_hits and qcache_inserts; but it is a very good measure of wether your query cache is actually providing any benefit - THIS is the ratio that should be high - if it's close to 1, it may mean you spend more time inserting and clearing than you save by the occasional cache hit. So, my suggestion would be to certainly use the latter option for the check_mysql_health check; but it may be useful in some scenarios to have a separate check for the former, too. /johan 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
check_mysql_health poll
Hey, I just happened upon your poll, so I'm sending you brief mail because I have a different opinion still :-) I'm also CCing the MySQL list, as I feel that more input on this might be a good thing - and it's worth some exposure anyway. I believe there are two distinct measures that can be taken: * Ratio of selects that were returned from the cache against total server queries (caching ratio) * Ratio of selects that were served from cache against selects that were inserted into the cache (statement reuse ratio) The former gives an indication of how many queries were served from the cache against the total number of questions asked. It's a useful measure to see wether it's worth the effort to see if there's ways to rewrite queries or code so that more queries become cacheable. Given that https://dev.mysql.com/doc/refman/5.7/en/server-status-variables.html#statvar_Com_xxx explicitly states that queries served from cache do NOT increment com_select, I believe that calculation requires qcache_hits, qcache_inserts AND com_select. I'm not clear on wether qcache_not_cached augments com_select, though I would suspect it does. Even if this ratio is relatively low, it's not necessarily a problem - every query served from cache is a parse/exec saved. On multitenancy you could have a database that benefits hugely from the cache, and ten others that hardly use it, and that is not a problem as such. The latter, on the other hand, tells you how many of the queries that were inserted into the cache, are actually served from cache afterwards. This requires only qcache_hits and qcache_inserts; but it is a very good measure of wether your query cache is actually providing any benefit - THIS is the ratio that should be high - if it's close to 1, it may mean you spend more time inserting and clearing than you save by the occasional cache hit. So, my suggestion would be to certainly use the latter option for the check_mysql_health check; but it may be useful in some scenarios to have a separate check for the former, too. /johan -- What's tiny and yellow and very, very dangerous? A canary with the root password.
