Optimize MySQL query_cache_min_res_unit

Question

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)

score 8 · Answer 1 · edited Sep 28 '15 at 15:23

8

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.

edited Sep 28 '15 at 15:23

dhaupin

115
6

answered Jun 30 '14 at 18:09

RolandoMySQLDBA

171,728
30
293
486

Is it possible to estimate the `query_cache_min_res_unit` from existing status rather than than trial and error? – Howard Jul 02 '14 at 06:12
Actually it is strange that I've total block of `18240`, since `18240 * 4096 = 71MB`, but I've only 32MB of query cache set. – Howard Jul 02 '14 at 07:07
Do I get the bounty ??? – RolandoMySQLDBA Jul 08 '14 at 15:04
@RolandoMySQLDBA: I think, 6GB written by you in above answer should be replaced by 6MB. – ursitesion Nov 13 '14 at 14:26

score 0 · Answer 2 · answered Sep 28 '15 at 17:01

The query cache stores the result of the query (data sent to the client).
In order to analyze your avg queries data set size, I would turn on slow query log and set it to 0. (for 24 hours). This will capture all your select statements. Running pt-query-digest, you can look at the most executed queries which will also give you the Avg, 95% of the data set size (row returned). Depending on which Mysql server version you use, you can set slow query log to full verbosity (Only Percona supports this I believe).

I think this method will give you a good perspective about your "select" queries on your system.

Optimize MySQL query_cache_min_res_unit

2 Answers2

Linked