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:
>> 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%
> 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.
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN