List:General Discussion« Previous MessageNext Message »
From:shawn green Date:July 15 2013 5:51pm
Subject:Re: Mysql cache issues???
View as plain text  
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.

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