MySQL query caching: limited to a maximum cache size of 128 MB?

Question

My application is very database intensive so I've tried really hard to make sure the application and the MySQL database are working as efficiently as possible together.

Currently I'm tuning the MySQL query cache to get it in line with the characteristics of queries being run on the server.

query_cache_size is the maximum amount of data that may be stored in the cache and query_cache_limit is the maximum size of a single resultset in the cache.

My current MySQL query cache is configured as follows:

query_cache_size=128M
query_cache_limit=1M

tuning-primer.sh gives me the following tuning hints about the running system:

QUERY CACHE
Query cache is enabled
Current query_cache_size = 128 M
Current query_cache_used = 127 M
Current query_cache_limit = 1 M
Current Query cache Memory fill ratio = 99.95 %
Current query_cache_min_res_unit = 4 K
However, 21278 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

And mysqltuner.pl gives the following tuning hints:

[OK] Query cache efficiency: 31.3% (39K cached / 125K selects)
[!!] Query cache prunes per day: 2300654

Variables to adjust:
    query_cache_size (> 128M)

Both tuning scripts suggest that I should raise the query_cache_size. However, increasing the query_cache size over 128M may reduce performance according to mysqltuner.pl (see http://mysqltuner.pl/mysqltuner.pl).

How would you tackle this problem? Would you increase the query_cache_size despite mysqltuner.pl's warning or try to adjust the querying logic in some way? Most of the data access is handled by Hibernate, but quite a lot of hand-coded SQL is used in the application as well.

Answer 1

Usually "too big cache size" warnings are issued under assumption that you have few physical memory and the cache itself well need to be swapped or will take resources that are required by the OS (like file cache).

If you have enough memory, it's safe to increase query_cache size (I've seen installations with 1GB query cache).

But are you sure you are using the query cache right? Do have lots of verbatim repeating queries? Could you please post the example of a typical query?