Should I increase MySQL cache?

Question

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

Answer 1

The docs say:

"Be careful not to set the size of the cache too large. Due to the need for threads to lock the cache during updates, you may see lock contention issues with a very large cache."

Mind you it doesn't define what very large is... Edit: Looking at this bug I think you are in the territory of very large.

If this is a search service perhaps you should consider something like Solr?

Answer 2

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.

Answer 3

You may want to read this: http://www.percona.com/files/presentations/MySQL_Query_Cache.pdf before you start using a huge cache. I had an 850GB mysql db storing mostly demographics data and my query cache was 32M based on my query plan.

I had started with a large cache and after a few years of using mysql with much performance tuning and cleaning up queries I dropped it to the 32M and it worked well.

"High performance Mysql" has a great chapter on the query cache as well.

Refs:

http://www.percona.com/files/presentations/MySQL_Query_Cache.pdf