ANALYSIS
Look back at your question where you said
"If you have a lot of queries with small results, the default block size may lead to memory fragmentation" & "If most of your queries have large results (check the Qcache_total_blocks and Qcache_queries_in_cache status variables)"
Here is the context right from the MySQL Documentation
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.
If most of your queries have large results (check the Qcache_total_blocks and Qcache_queries_in_cache status variables), you can increase performance by increasing query_cache_min_res_unit. However, be careful to not make it too large (see the previous item).
Look at your numbers
Qcache_total_blocks
: 18381
Qcache_free_blocks
: 4250
Qcache_queries_in_cache
7027
Given these numbers
- 23.12% (4250 / 18381) of the Query Cache is fragmented
- There are 2.6158 (18381 / 7027) blocks per query
On average, you can interpret this to mean one of the following:
- 76.88% (100 - 23.12) of the blocks have data
- 76.88% (100 - 23.12) of each block has data
Each individual block's fragmentation cannot be fixed. On the other hand, the query cache's total fragmentation can be adjusted by doing one thing the Documentation says (which I highlighted and italicized) : decrease the value of query_cache_min_res_unit
.
If you want more performance (as I had italicized), you can increase performance by increasing query_cache_min_res_unit
. This will introduce more fragmentation and more query cache pruning.
YOUR ACTUAL QUESTION
Is it correct interpretation that total_blocks / queries_in_cache = average number of blocks for each cached query (in my case 2.6)?
On average, Yes
Would it make any sense to increase query_cache_min_res_unit from the default 4096?
Depends on what you want, more performance or less fragmentation.
With 23.12% fragmentation, I would work on decreasing query_cache_min_res_unit to lower the query cache's pruning activity against small queries. Strictly in terms of the query cache, performance will take care of itself. When you can drop the fragmentation percentage, you could possibly increase the query_cache_size to fit more query results IF AND ONLY IF the results are frequently accessed.
Think about this as well: each block is 1K since the Documentation says
The query_cache_size value is aligned to the nearest 1024 byte block.
Thus, 2.6K per query result. Again, this gives you more reason to focus on reducing fragmentation.
UPDATE 2014-10-28 12:53 EDT
I just remembered something that I needed to correct. The size of a block is not 1K. I believe that the size of a query cache block is actually set by query_alloc_block_size. If this is the case, then 2.6 blocks is not 2.6K as I thought before.
Given the default value for query_alloc_block_size is 8K, then 2.6 blocks would be 20.8K.
In light of this, let's look at the numbers again.
TOTAL BLOCKS
18381 (number of total blocks) X 8192 (8K default block size) rounded up
mysql> select CEILING(18381 * 8192 / power(1024,2)) QueryCacheSizeInMB;
+--------------------+
| QueryCacheSizeInMB |
+--------------------+
| 144 |
+--------------------+
FREE BLOCKS
4250 (number of free blocks) X 8192 (8K default block size) rounded up
mysql> select CEILING(4250 * 8192 / power(1024,2)) QueryCacheFreeMB;
+------------------+
| QueryCacheFreeMB |
+------------------+
| 34 |
+------------------+
Simply subtraction shows that 110M is in use for the 7027 queries.
AVERAGE SPACE PER QUERY RESULT
mysql> select format(110 * power(1024,2) / 7027 / 1024,2) KBPerQueryResult;
+------------------+
| KBPerQueryResult |
+------------------+
| 16.03 |
+------------------+
That yields 16.03K per results.
BUT WAIT ...
Mathematically, we both figured out that 2.6 blocks per query was 20.8K. Since average space per query result (16.03K) is a few bytes above 2.0 blocks, fragmentation is all but guaranteed.
SUGGESTION
The Documentation says of query_alloc_block_size
If you have problems with memory fragmentation, it might help to increase this parameter.
Therefore, you should increase query_alloc_block_size to 16K, 24K or 32K. Try any multiple of 8K until the fragmentation is lower. Due this in conjunction with either increasing or decreasing query_cache_size.
GIVE IT A TRY !!!
Best Answer
Yes and no.
The Query cache will not see, much less keep, the partial result. The page you referenced is talking only about the Query cache. The QC is populated only after the complete result is built. This gives a significant boost for a repeat of the identical query. (Similar queries are not helped any by the QC.)
InnoDB's buffer_pool (and the equivalent in MyISAM) caches whatever blocks are read from disk. Blocks that were read from disk before the killed was process will probably still be in RAM. This will provide some performance boost for repeated executions of the same or similar queries, whether killed or not.