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