And for most, not all, of /my/ production servers since pretty much ever, the right setting has been on.
Again, please don't generalize based off your own usecase. I have no idea what the whole world is doing with their servers. Rick James <rja...@yahoo-inc.com> wrote: >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 -- 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
