Mysql – Determine if queries have small or large results

MySQLquery-cache

In the MySQL documentation for Query cache, specifically on the topic of query_cache_min_res_unit "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)"

#my.cnf
query_cache_size = 20M
#SQL
SHOW STATUS LIKE "%qcache%";
Qcache_free_blocks  4250
Qcache_free_memory  13633864
Qcache_queries_in_cache 7027
Qcache_total_blocks 18381

Is it correct interpretation that total_blocks / queries_in_cache = average number of blocks for each cached query (in my case 2.6)? Would it make any sense to increase query_cache_min_res_unit from the default 4096?

edit
I don't believe the above interpretation is correct any more. A block can be any size between query_cache_min_res_unit (4K) to query_cache_limit (default 1MB) and each of query, result and table will consume a block. Therefore total_blocks / queries_in_cache shows the uniqueness of tables in queries – i.e as there are more queries on the same table, total_blocks / queries_in_cache would approach 2.
Two different queries using the same and only 1 table- (3 + 2 = 5 = total blocks) / (2 = queries in cache) = 2.5
Ten different queries using the same and only 1 table- (3 + 2 + 2 + 2 + 2 + 2 + 2 + 2 + 2 + 2) / 10 = 2.1

However I'm still struggling to reconcile this with my memory values as I would have thought that some approximation of the average query size would be given by the formula

 (query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache
 (20971520 - 13633864) / 7027 = 1044.2

Since 1044 is below the query_cache_min_res_unit, I must be wrong (or bad at maths, or both). And besides, this doesn't count for the each blocks consumed by the query and table(s). I think that a more accurate formula is below, but of course this gives a negative so again, something is off.

(
 (query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache) - 
 (Qcache_queries_in_cache - Qcache_queries_in_cache) * query_cache_min_res_unit
)

Conclusion so far
Very unlikely to change any settings as I can't make sense of it. I would be thankful if the average size of queries in cache can be determined from these values as it seems like it should be possible – but my numbers don't add up.

Best Answer

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 !!!

Related Question