Hello.
My $0.05 :) If you find out the unique queries (they often contain date and time), add SQL_NO_CACHE to SELECT statement - this will decrease the number of inserts of unique queries. Brent Baisley <[EMAIL PROTECTED]> wrote: > You tripled the amount of memory assigned to the cache, but your > Qcache_lowmem_prunes value went up 10x and Qcache_not_cached went up > 6x. I would say that you have enough queries that are unique, that > most of your queries are getting cached then flushed before the same > query comes up again. > > The number of queries being cached went up 13x, so you are caching a > lot more queries, but it's not doing all that much good. You're > getting diminishing returns as you increase memory. The query cache > works best when you have the same exact queries being run over and > over. You know your code, so you need to think how many unique > queries you have. Perhaps you're getting close to the number of > unique queries you run and increasing your cache just a bit more will > suddenly send your hit rate way up, perhaps not. > Test in big increments. If you suddenly get a big boost in hit ratio, > work your way back down. But it may be that the query cache just > can't be used optimally with your system. > > > On Sep 15, 2005, at 1:10 PM, Anil wrote: > >> >> >> >> >> _____ >> >> From: Anil [mailto:[EMAIL PROTECTED] >> Sent: Thursday, September 15, 2005 10:36 PM >> To: 'mysql@lists.mysql.com' >> Subject: QUERY CACHE TUNING QUESTION >> >> >> >> Hi list, >> >> >> >> We are using mysql 4.0.24 on RHEL 3.0 on dell 2650 machine with 2 >> gb RAM. >> We tried to fine tune query cache . please find below the analysis >> >> >> >> >> >> >> >> PARAMETER old modified >> >> ========== ========== ================ >> >> >> >> query_cache_size 10 MB 32 MB >> >> >> >> Qcache_queries_in_cache 187 2444 >> >> >> >> Qcache_inserts 1226682 13560221 >> >> >> >> Qcache_hits 1510151 7380756 >> >> >> >> Qcache_lowmem_prunes 850128 8096499 >> >> >> >> Qcache_not_cached 2725692 16361318 >> >> >> >> Qcache_free_blocks 65 12 >> >> >> >> Qcache_free_memory 9.26 MB 28.79 MB >> >> >> >> >> >> >> >> qCACHE HIT RATE 27.64% 19.78% >> >> >> >> >> >> >> >> QCACHE HIT RATE IS CALUCULATED WITH FORMULA = (Qcache_hits/ >> (Qcache_inserts >> +Qcache_not_cached+Qcache_hits)) * 100 >> >> >> >> QCACHE HIT RATE IS CALUCULATED WITH FORMULA = (Qcache_hits/ >> (Qcache_inserts >> +Qcache_not_cached+Qcache_hits)) * 100 >> >> >> >> As per the above analysis we observed that qcache hit rate came >> down after >> increasing query_cache_size from 10 MB to 32 MB and qcache_hits got >> increased but the remaining parameters Qcache_inserts, >> Qcache_not_cached, >> Qcache_lowmem_prunes also increased because of that hit rate came down >> drastically. We didn't understand the reason behind hit . Is there any >> recommended procedure to fine tune query cache . awaiting early >> reply from >> list. >> >> >> >> >> >> Thanks in advance >> >> Anil >> >> DBA >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> > -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
