For most, not all, production servers, these two are the 'right' settings: query_cache_type = OFF query_cache_size = 0 Both are needed to avoid some code paths from being unnecessarily followed. (Maybe someday, that will be fixed, too.)
I recommend only 50M as the max for _size. Here are some metrics to look at to see if the QC is worth having. (Of course, you have to run with it ON or DEMAND for a while to get values for these.) Qcache_free_memory / query_cache_size -- good value..bad value: 0%,100% -- Meaning: Pct Query Cache free -- What to do if 'bad': lower query_cache_size Qcache_lowmem_prunes / Uptime -- good value..bad value: 0,15 -- Meaning: Query Cache spilling -- What to do if 'bad': increase query_cache_size Qcache_not_cached / Uptime -- good value..bad value: 0,80 -- Meaning: SQL_CACHE attempted, but ignored -- What to do if 'bad': Rethink caching; tune qcache Qcache_free_blocks * 4096 / query_cache_size -- good value..bad value: 0,1 -- Meaning: Fragmentation in qcache -- What to do if 'bad': decrease query_cache_min_res_unit Qcache_hits / Qcache_inserts -- good value..bad value: 10,1 -- Meaning: Hit to insert ratio -- high is good Qcache_hits / (Qcache_hits + Com_select) -- good value..bad value: 100%,25% -- Meaning: Hit ratio -- What to do if 'bad': Use "_type=DEMAND" and use "SELECT SQL_NO_CACHE" more often Qcache_total_blocks * query_cache_min_res_unit / Qcache_queries_in_cache -- Meaning: estimate of query size -- What to do if 'bad': adjust query_cache_min_res_unit Qcache_queries_in_cache -- Meaning: Queries cached (Com_select + Qcache_hits) / (Com_insert + Com_update + Com_delete + Com_replace) -- Meaning: Read to write ratio > -----Original Message----- > From: Johan De Meersman [mailto:vegiv...@tuxera.be] > Sent: Monday, July 15, 2013 11:53 PM > To: shawn green; mysql@lists.mysql.com > Subject: Re: Mysql cache issues??? > > Shawn, > > I can't help but wonder wether that first paragraph means there are > concrete plans to redo the qc? > > > shawn green <shawn.l.gr...@oracle.com> 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. > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
