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.