List:General Discussion« Previous MessageNext Message »
From:shawn green Date:July 15 2013 2:46pm
Subject:Re: Mysql cache issues???
View as plain text  
Hello Reindl,

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.

Regards,
-- 
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN
Thread
Mysql cache issues???Egoitz Aurrekoetxea15 Jul
  • Re: Mysql cache issues???Johan De Meersman15 Jul
    • Re: Mysql cache issues???Egoitz Aurrekoetxea15 Jul
  • Re: Mysql cache issues???Hartmut Holzgraefe15 Jul
    • Re: Mysql cache issues???Reindl Harald15 Jul
      • Re: Mysql cache issues???shawn green15 Jul
        • Re: Mysql cache issues???Reindl Harald15 Jul
          • Re: Mysql cache issues???Egoitz Aurrekoetxea15 Jul
            • Re: Mysql cache issues???Reindl Harald15 Jul
            • Re: Mysql cache issues???shawn green15 Jul
              • Re: Mysql cache issues???Johan De Meersman16 Jul
                • RE: Mysql cache issues???Rick James22 Jul
                  • RE: Mysql cache issues???Johan De Meersman22 Jul
    • Re: Mysql cache issues???Johan De Meersman15 Jul