Re: QUERY CACHE TUNING QUESTION
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 122668213560221 >> >> >> >> Qcache_hits 1510151 7380756 >> >> >> >> Qcache_lowmem_prunes 8501288096499 >> >> >> >> Qcache_not_cached2725692 16361318 >> >> >> >> Qcache_free_blocks 6512 >> >> >> >> Qcache_free_memory9.26 MB 28.79 MB >> >> >> >> >> >> >> >> qCACHE HIT RATE27.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]
Re: QUERY CACHE TUNING QUESTION
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 122668213560221 Qcache_hits 1510151 7380756 Qcache_lowmem_prunes 8501288096499 Qcache_not_cached2725692 16361318 Qcache_free_blocks 6512 Qcache_free_memory9.26 MB 28.79 MB qCACHE HIT RATE27.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 -- Brent Baisley Systems Architect Landover Associates, Inc. Search & Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
QUERY CACHE TUNING QUESTION
_ 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 122668213560221 Qcache_hits 1510151 7380756 Qcache_lowmem_prunes 8501288096499 Qcache_not_cached2725692 16361318 Qcache_free_blocks 6512 Qcache_free_memory9.26 MB 28.79 MB qCACHE HIT RATE27.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
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_prunes850128 8096499 Qcache_not_cached 2725692 16361318 Qcache_free_blocks 65 12 Qcache_free_memory9.26 MB 28.79 MB qCACHE HIT RATE27.64% 19.78% 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
