Mysql – Optimize MySQL query_cache_min_res_unit

MySQLperformanceperformance-tuningquery-cache

My query cache settings are the following:

mysql> SHOW GLOBAL STATUS LIKE 'Q%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 2270     |
| Qcache_free_memory      | 6580864  |
| Qcache_hits             | 12802676 |
| Qcache_inserts          | 2094054  |
| Qcache_lowmem_prunes    | 111676   |
| Qcache_not_cached       | 137257   |
| Qcache_queries_in_cache | 7559     |
| Qcache_total_blocks     | 18240    |
| Queries                 | 15571718 |
| Questions               | 15571715 |
+-------------------------+----------+

As you can see, I have quite high in the value of Qcache_lowmem_prunes, but I still have quite some free memory (6MB out of total 32MB query cache size)

I am looking to see if anyway can further reduce the Qcache_lowmem_prunes.

Should I use a lower value of query_cache_min_res_unit? (Which is 4096 for now)

Best Answer

Your query_cache_min_res_unit is 4KB and you have 6MB of query cache free? Look at this:

6MB = 6144 KB
6144 KB divided by 4KB/query = 1536 queries.

You can potentially fit up 1536 small query results in that 6MB of free space in the query cache. That 6MB is most likely being viewed as memory fragmentation. Why?

According to the MySQL Documentation on Query Cache Configuration

If you have a lot of queries with small results, the default block size may lead to memory fragmentation, as indicated by a large number of free blocks. Fragmentation can force the query cache to prune (delete) queries from the cache due to lack of memory. In this case, decrease the value of query_cache_min_res_unit. The number of free blocks and queries removed due to pruning are given by the values of the Qcache_free_blocks and Qcache_lowmem_prunes status variables.

That being the case, you may as well go for the minimum value for query_cache_min_res_unit: 512. This will save you the step of lowering it multiple times until you get better status results. At the very least, it should reduce the fragmentation.

Related Question