Tuning recommendations from mysqltuner.pl: query_cache_limit

Question

The mysqltuner.pl script gives me the following recommendation:

query_cache_limit (> 1M, or use smaller result sets)

And MySQL status output shows:

mysql> SHOW STATUS LIKE 'Qcache%';
+-------------------------+------------+
| Variable_name           | Value      |
+-------------------------+------------+
| Qcache_free_blocks      | 12264      | 
| Qcache_free_memory      | 1001213144 | 
| Qcache_hits             | 3763384    | 
| Qcache_inserts          | 54632419   | 
| Qcache_lowmem_prunes    | 0          | 
| Qcache_not_cached       | 6656246    | 
| Qcache_queries_in_cache | 55280      | 
| Qcache_total_blocks     | 122848     | 
+-------------------------+------------+
8 rows in set (0.00 sec)

From the status output above, how can I judge whether or nor the suggested increase in query_cache_limit is needed?

Answer 1

Your best bet is to set up some kind of test harness that executes a realistic (defined by your scenario) load on your database, and then run that test against MySql with different settings. Tuning is such an art in itself that it is very difficult to give an all embracing answer without knowing your exact needs.

From http://dev.mysql.com/tech-resources/articles/mysql-query-cache.html:

The Qcache_free_memory counter provides insight into the cache's free memory. Low amounts observed vs. total allocated for the cache may indicate an undersized cache, which can be remedied by altering the global variable query_cache_size.

Qcache_hits and Qcache_inserts shows the number of times a query was serviced from the cache and how many queries have been inserted into the cache. Low ratios of hits to inserts indicate little query reuse or a too-low setting of the query_cache_limit, which serves to govern the RAM devoted to each individual query cache entry. Large query result sets will require larger settings of this variable.

Another indicator of poor query reuse is an increasing Qcache_lowmem_prunes value. This indicates how often MySQL had to remove queries from the cache to make use for incoming statements. Other reasons for an increasing number of Qcache_lowmem_prunes are an undersized cache, which can't hold the needed amount of SQL statements and result sets, and memory fragmentation in the cache which may be alleviated by issuing a FLUSH QUERY CACHE statement. You can remove all queries from the cache with the RESET QUERY CACHE command.

The Qcache_not_cached counter provides insight into the number of statements executed against MySQL that were not cacheable, due to either being a non-SELECT statement or being explicitly barred from entry with a SQL_NO_CACHE hint.

Your hits-to-inserts ratio is something like 1:15 or 6%, so it looks like your settings could do with some finetuning (although, as I said, you are the best judge of that as you know your requirements best).