List:General Discussion« Previous MessageNext Message »
From:Johan De Meersman Date:July 16 2013 6:52am
Subject:Re: Mysql cache issues???
View as plain text  
Shawn, 

I can't help but wonder wether that first paragraph means there are concrete plans to redo
the qc?


shawn green <shawn.l.green@stripped> wrote:
>Hello Egoitz,
>
>On 7/15/2013 1:35 PM, Egoitz Aurrekoetxea wrote:
>> -----BEGIN PGP SIGNED MESSAGE-----
>> Hash: SHA1
>>
>> On 15/07/13 17:27, Reindl Harald wrote:
>>>
>>>... snip...
>>> 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....
>> ... signature snipped ...
>>
>
>Until we redesign the query cache, those stalls will remain. It is 
>unwize to keep so many sets of query results around if they are not 
>actually being used.
>
>As has been covered already, the freeze required to perform the purge
>of 
>all results associated with a specific table can at times be extended 
>(durations of 20-30 minutes are not unusual with cache sizes around 
>1GB). What you may find is that even if some of your results are reused
>
>frequently for a short period of time, they are not reused at all
>beyond 
>a certain moment. This means you have hundreds or thousands of sets of 
>query results sitting idle in your cache.  Reduce the size of your
>cache 
>until you start to see your reuse rate or efficiency rate decline 
>significantly. You may be surprised how small that is for your
>workload.
>
>To achieve scalability: customize your cache structures to your
>workload 
>(this may mean caching the results somewhere other than MySQL),
>optimize 
>your tables for efficient storage and retrieval, and optimize your 
>queries to be as efficient as practical. There are other scalability 
>options such as replication and sharding that can also be introduced 
>into your production environment to reduce the cost of computation on 
>each copy (or portion) of your data. However, this is a topic best 
>handled in a separate thread.

-- 
Sent from Kaiten Mail. Please excuse my brevity.
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