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
--
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]
