[prev in list] [next in list] [prev in thread] [next in thread] 

List:       mysql
Subject:    RE: Mysql cache issues???
From:       Johan De Meersman <vegivamp () tuxera ! be>
Date:       2013-07-22 19:10:29
Message-ID: 32656d8f-3bd8-4a70-8c13-bf5e77524ed3 () email ! android ! com
[Download RAW message or body]

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 <rjames@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:vegivamp@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.green@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


[prev in list] [next in list] [prev in thread] [next in thread]