I tried to run tuningprimer.sh to tweak my mysql settings. I already increased mysql query cache few times to current 2GB but it's still not enough according to test results:
> QUERY CACHE Query cache is enabled Current query_cache_size = 2.00 G
> Current query_cache_used = 1.72 G Current query_cache_limit = 8 M
> Current Query cache Memory fill ratio = 86.22 % Current
> query_cache_min_res_unit = 4 K However, 3906616 queries have been
> removed from the query cache due to lack of memory Perhaps you should
> raise query_cache_size MySQL won't cache query results that are larger
> than query_cache_limit in size
As you can see 3.9 mil queries were removed from cache because its size. I'm afraid about indexing such a big cache.
I mean that maybe search in such a big cache file can take more time and resources than execute the query itself.
What do you think ? Should I still increase mysql cache ?
BTW: server runs Litespeed + mysql + php 5.3 and has 24GB of RAM, current memory usage is 12GB
Best Answer
If you have a lot of rather small result sets to cache, your query_cache_min_res_unit might be set too large. As query_cache_min_res_unit is the minimum allocation size for a cached result set, even with 2 GB of query cache you would not be able to store more than 512,000 results. Take a look at the Qcache_total_blocks and Qcache_queries_in_cache variables from the output of
SHOW STATUS LIKE '%qcache%';- if you see a ratio near 1:1, you might benefit from lowering query_cache_min_res_unit value.The lock contention problems would still be an issue, but you would see the limit hit when "SHOW PROCESSLIST" lists an excessive number of "Waiting for query cache lock" proceses.