Mysql – Hits to Insert Ratio and Insert to Prune Ratio

MySQL

I'm confused with Insert to Prune Ratio.

As I understood, for example Hits to Insert Ratio:

Hit:Insert     0.47:1

is very bad (more inserts, then hits, hence – server waste resources to insert in to QCache, but – it's rarely give results back to clients) – is this one correct?

But about next one – Insert to Prune Ratio… I'm really confused.
On same server I have this ratio:

Insrt:Prune 1.96:1

Inserts more then deletes from cache?.. But – is it bad? Or good? What is adequate value?

mysql> SHOW STATUS LIKE 'qcache%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Qcache_free_blocks      | 212     |
| Qcache_free_memory      | 460208  |
| Qcache_hits             | 1292474 |
| Qcache_inserts          | 2773956 |
| Qcache_lowmem_prunes    | 1417292 |
| Qcache_not_cached       | 9557843 |
| Qcache_queries_in_cache | 872     |
| Qcache_total_blocks     | 2251    |
+-------------------------+---------+
8 rows in set (0.09 sec)

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

Best Answer

Hit:Insert     0.47:1

means "every inserted entry was requested 0.5 times, before it was removed". (Yes, that's not good.)

Insert:Prune    1.96:1

means "for every second insert, I had to prune/remove one existing entry". An old rule of thumb states "a value between 10 and 20 percent is acceptable", while your value is 50%.

Another helpful number is the query cache hit rate, which is

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

in your case thats roughly 9%, meaning only 9% of the queries can be satisfied by the query cache.

Recommendations reach from "give the query cache more RAM" over "play around with query_cache_min_res_unit" to "give the query cache less RAM"... which is not a really helpful advice.

Since you have set query_cache_size=8M I would recommend to raise this value. 8 Megabytes seems very low to me.