On 7/15/2013 10:28 AM, Reindl Harald wrote:
> Am 15.07.2013 16:00, schrieb Hartmut Holzgraefe:
>> On 15.07.2013 15:31, Egoitz Aurrekoetxea wrote:
>>> Could Mysql cache cause a performance penalty, when invalidating
>>> queries of a big Mysql cache zone?
>> can, and *will* ... see also http://dom.as/tech/query-cache-tuner/
> Optimal size for your query cache: 0
> thats ridiculous
>> cache is locked while entries are being purged to prevent handing out
>> cached results that may already be out-of-date, and the more active
>> cache entries that need to be purged the longer it stays locked ...
> but in most cases the benefit outbeats this overhead massive
> since you have more read than write
> [OK] Query cache efficiency: 93.6% (40M cached / 42M selects)
> nobody can tell me that all these 40M queries would have been
> faster without cache by reduce the overhead
I just want to verify your that your efficiency formula.
Query Cache Efficiency = Qcache_hits / (Qcache_hits + Com_select)
The way yours is described it looks like it may be a simple ratio of
Qcache_hits to Com_select which would be inaccurate.
Another way of judging the efficiency of the cache is to compare how
many times a cached result is actually reused (on average). Compute a
very broad reuse rate with:
Reuse estimate = Qcache_hits/Qcache_inserts
If that ratio is under about 5 you have very poor reuse. If it is less
than 1, you are getting no reuse at all for some of the results you are
caching. If that's the case, strongly consider disabling the cache or
using more selective caching techniques as already discussed.
I'm not trying to pick on you, Reindl. Your cache may be doing
splendidly well. I just didn't want anyone else to see your efficiency
rating and derive the wrong formula on their own.
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN