Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question

2 Answers 2

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
share|improve this answer
    
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 '11 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. –  Gopinath Oct 28 '11 at 7:32

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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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