List:General Discussion« Previous MessageNext Message »
From:Reindl Harald Date:July 15 2013 3:27pm
Subject:Re: Mysql cache issues???
View as plain text  

Am 15.07.2013 16:46, schrieb shawn green:
> 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.

https://raw.github.com/major/MySQLTuner-perl/master/mysqltuner.pl

> 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

true

> 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.

without measure it - these are webservers with some hundret domains
the content is not changed that often, you can expect that you
have a lot of cache hits in such environments

> 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

i would say my caches are working perfectly (not only the mysql cache, also opcache etc.)
since whe have generate times down to 0.006 seconds for a typical CMS page here which
runs in more than 200 installations on the main machine, at high load mysqld is never
the problem

without the query cache the overall performance drops by 30-40%


Attachment: [application/pgp-signature] OpenPGP digital signature signature.asc
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