phpknight Posted March 27, 2008 Share Posted March 27, 2008 Hi, I am trying to opimize my query cache but am having issues. Most of my queries are very small, so I have set the query_cache_min_res_unit to 1K. As you can see, although I have over 100M of free memory available, Qcache_lowmem_prunes is 400K. Shouldn't it be zero, though? Since each block is only 1K and there are 22K blocks, I am only using 22MB of memory. So, I cannot see why anything is being pruned at all. I am thinking of possible changing query_alloc_block_size and query_prealloc_size to 1K as well to see if that fixes it. I cannot figure out why it is not utilizing the memory. There are only 9K queries in the cache, it has pruned 400K, but there is still over 100M of unused space. Hopefully that makes sense. Please send along any advice. SETTINGS have_query_cache YES query_alloc_block_size 8192 query_cache_limit 1048576 query_cache_min_res_unit 1024 query_cache_size 134217728 query_cache_type ON query_cache_wlock_invalidate OFF query_prealloc_size 8192 PERFORMANCE Qcache_free_blocks 1606 Qcache_free_memory 116406976 Qcache_hits 4648341 Qcache_inserts 513814 Qcache_lowmem_prunes 413401 Qcache_not_cached 3093 Qcache_queries_in_cache 9188 Qcache_total_blocks 20185 Quote Link to comment Share on other sites More sharing options...
fenway Posted March 28, 2008 Share Posted March 28, 2008 Are you sure that you make sure all the counters where at zero before you started this analysis? Quote Link to comment Share on other sites More sharing options...
phpknight Posted March 29, 2008 Author Share Posted March 29, 2008 Yes. I restarted mysql and checked it out. Quote Link to comment Share on other sites More sharing options...
fenway Posted March 29, 2008 Share Posted March 29, 2008 Yes. I restarted mysql and checked it out. And you're checking this after how long? What kinds of queries? Have you read this yet? Or this? Quote Link to comment Share on other sites More sharing options...
phpknight Posted March 29, 2008 Author Share Posted March 29, 2008 Fenway, I got it working now. Just checked. Here are my settings and explanation. query_cache_min_res_unit 1024 query_alloc_block_size 1024 query_prealloc_size 8192 Basically, people talk a lot about query_cache_min_res_unit, which I changed. BUT, this seemed to have no real affect if you don't set query_alloc_block_size to 1024 as well. Otherwise, it continues to allocate in 8K chunks. Also, unless I made a mistake twice in the config file, it appears that query_prealloc_size cannot go below 8K. If you put 1K, it has no effect. So, once I changed the alloc_block to match the res_unit, it was working properly. I am thinking it was allocating things by a factor of 8x, and this was leaving 7/8 of the memory untouched. Please let me know what you think about this. Quote Link to comment Share on other sites More sharing options...
fenway Posted March 30, 2008 Share Posted March 30, 2008 Sounds reasonable... to be honest, I've never made it smaller, only larger. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.