List:Internals« Previous MessageNext Message »
From:Baron Schwartz Date:December 1 2007 10:23pm
Subject:A heuristic for query cache memory allocation
View as plain text  
Would it make sense to use a query's table list as a heuristic for how
large a query cache block to allocate for results?  The current
strategy of a fixed minimum doesn't work great when there's a mixture
of large and small results to cache: it causes fragmentation in the
cache.  However, it seems to me that queries that have the same table
list are likely to be similar queries (possibly the same query with
different parameters) and therefore have similar result sizes.  Maybe
it would be possible to maintain statistics about each unique set of
tables used, or simply scan the table lists upon insertion, and
predict a good size to allocate.  (I have no idea whether this could
be done efficiently, I'm just suggesting it).

It could also be possible to calculate the variance of the sizes, so
the server could know whether it has accumulated enough data on the
particular list of tables and whether that data is a reliable
predictor of the result size.  If it decides it doesn't have enough
data, it could fall back to the configured default.

This could allow the server to self-tune query_cache_min_res_unit per query.

What do you think?

Regards
Baron
Thread
A heuristic for query cache memory allocationBaron Schwartz1 Dec
  • Re: A heuristic for query cache memory allocationOleksandr \"Sanja\" Byelkin23 Jan
    • RE: A heuristic for query cache memory allocationRick James23 Jan
    • Re: A heuristic for query cache memory allocationOleksandr \"Sanja\" Byelkin3 Feb
      • Re: A heuristic for query cache memory allocationBaron Schwartz3 Feb
        • RE: A heuristic for query cache memory allocationRick James4 Feb