List:General Discussion« Previous MessageNext Message »
From:Egoitz Aurrekoetxea Date:July 15 2013 5:35pm
Subject:Re: Mysql cache issues???
View as plain text  
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 15/07/13 17:27, Reindl Harald wrote:
> 
> 
> 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%
> 


Hi,

The query cache hit rate is near 90%.... so I assume it's doing all
properly... now I'm using 1GB as cache.... but... I will do some
tries... till I see some significant behavior either due to success or
failure... I was basically wondering what did you though about
performance penalty due to the mysql cache... just that...

Thank you very much then....

Regards,.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.19 (GNU/Linux)

iQEcBAEBAgAGBQJR5DLWAAoJEG0sn91fOssvE9UIAK9YZQpCgbwyntabJKxfGmsm
gMTrvKcu0dFCTIlCv9xT5g0t/6ozJrxRdtBki80WYF/natdsRYXDT2htPzKr0s06
lexUfJEU/3DG+iS1VXKANSbDdhZhO73nj2N5yc5ss2Vk8Q+XfR6K+o4bn/RtWsVg
0nf9ygbA2QKo32Vb2DfJlxEe+7ar+p4+/gA9xz6jyQpZ49s7E3fZCPu3rLRFRZY+
TdBVRKjR6vXvfJtR5kYN4nZIrEN9Lgn6QGlwv9Y9aA2S+AUzOh7MIxVP3UmAe3nd
SgySuO9xT0hIJ8OQpPYs2ZkG+c+Bl3BSghjyfmUzNK1iZDZaJCbbR6X2sFE1WLQ=
=Jg5O
-----END PGP SIGNATURE-----
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