List:General Discussion« Previous MessageNext Message »
From:Johan De Meersman Date:July 22 2013 7:10pm
Subject:RE: Mysql cache issues???
View as plain text  
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@stripped> 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@stripped]
>> Sent: Monday, July 15, 2013 11:53 PM
>> To: shawn green; mysql@stripped
>> 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@stripped> 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.
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