Optimizing MySQL query_cache_size

Question

Is my query_cache_size too big?

mysql> SHOW VARIABLES LIKE '%query_cache%';
+------------------------------+-----------+
| Variable_name                | Value     |
+------------------------------+-----------+
| have_query_cache             | YES       |
| query_cache_limit            | 4194304   |
| query_cache_min_res_unit     | 4096      |
| query_cache_size             | 201326592 |
| query_cache_type             | ON        |
| query_cache_wlock_invalidate | OFF       |
+------------------------------+-----------+
6 rows in set (0.00 sec)

mysql> SHOW STATUS LIKE '%qcache%';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| Qcache_free_blocks      | 26643     |
| Qcache_free_memory      | 83199168  |
| Qcache_hits             | 327606097 |
| Qcache_inserts          | 19231291  |
| Qcache_lowmem_prunes    | 10628400  |
| Qcache_not_cached       | 36215     |
| Qcache_queries_in_cache | 92389     |
| Qcache_total_blocks     | 212195    |
+-------------------------+-----------+
8 rows in set (0.00 sec)

mysqlreport says:

__ Query Cache _________________________________________________________
Memory usage  109.13M of 192.00M  %Used:  56.84
Block Fragmnt  12.55%
Hits          327.76M   812.1/s
Inserts        19.24M    47.7/s
Insrt:Prune    1.81:1    21.3/s
Hit:Insert    17.04:1

Answer 1

You can analyze the usage of your query cache using the following formulas:

Current size compared with maximum available size:

To calculate the percentage used value for the query cache you can use the following formula:

((query_cache_size-Qcache_free_memory)/query_cache_size)*100

The Query Cache Hit Rate:

The percentage hit rate on the cache can be calculated as follows:

((Qcache_hits/(Qcache_hits+Qcache_inserts+Qcache_not_cached))*100)

e.g.The usage of 33% says that of all select statements executed, 33% of them can be satisfied by the cache and hence do not have to be re-executed.

Hits to Insert Ratio and Insert to Prune Ratio

These two ratios are calculated by the following two formulas:

1. Qcache_hits/Qcache_inserts
2. Qcache_inserts/Qcache_prunes

DO NOT allocate too much memory

Using less memory than available can reduce the performance, but using more memory than available can lead to worse performance or even crashes. A general resource allocation formula is:

memory=key_buffer+(sort_buffer_size+read_buffer_size)*max_connections

Answer 2

It turns out the query cache size wasn't too big. Instead queries weren't being cached due to query_cache_min_res_unit being too large.

By reducing this to 1024 closer to the average query size, I ended up having to increase query_cache_size and now get the following.

Memory usage  249.07M of 256.00M  %Used:  97.29
Block Fragmnt   1.05%
Hits           12.34M   789.6/s
Inserts       574.80k    36.8/s
Insrt:Prune    4.72:1    29.0/s
Hit:Insert    21.47:1