7

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

2 Answers 2

7

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
2
  • Query Cache Hit Rate = 94% which seems fairly efficient. But ratio of inserts to prune is 1.8: 1. Does that suggest the size is not big enough?
    – kidrobot
    Oct 28, 2011 at 7:15
  • Your Query cache usage seems to be good. Coming to hit ratio 1.8 : 1 is an average hit ratio, where it sounds more number of inserts compare to deletions and updates. Oct 28, 2011 at 7:32
5

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.