2

I am migrating an application from MySQL 5.6 to MariaDB 10. One major new feature of MariaDB is the subquery cache. I am trying to determine the expected effect this cache will have on our application before enabling and testing it. Under the "Implementation" section, the knowledge base article says this:

Every subquery cache creates a temporary table where the results and all parameters are stored. It has a unique index over all parameters. First the cache is created in a MEMORY table (if doing this is impossible the cache becomes disabled for that expression). When the table grows up to the minimum of tmp_table_size and max_heap_table_size, the hit rate will be checked:

  • if the hit rate is really small (<0.2) the cache will be disabled.
  • if the hit rate is moderate (<0.7) the table will be cleaned (all records deleted) to keep the table in memory
  • if the hit rate is high the table will be converted to a disk table (for 5.3.0 it can only be converted to a disk table).

The first thing I'm trying to determine is what exactly it means by, "Every subquery cache." Since it states that the caches are created in temporary tables, it implies that they must be scoped to the current connection. So my guess is that there is one subquery_cache table created per connection. (I doubt every query has its own cache, because then the line about the cache "growing" wouldn't make sense.) But that's just a guess; I can't find any more details. Perhaps these temporary tables are indeed shared between connections somehow, unlike regular temp tables. Or perhaps there are multiple caches per connection.

Can anyone confirm the number and scope of subquery caches created?

My next question is whether there are any options for tuning the cache. Since we are moving from an optimized MySQL application, we don't currently have any queries that make extensive use of cachable subqueries (since such queries would be very slow under MySQL). We do use plenty of subqueries though, just not in ways that are likely to cause a lot of cache hits. Given that, I expect that enabling the cache will simply create an overhead in temporary table creation and lookup for each subquery, as well as memory used to store the cache tables (which apparently can't be tuned separately from the main tmp_table_size and max_heap_table_size variables). Given that I expect to find that our application as it currently exists is more efficient without the cache enabled. The ideal though would be if it supports something like the 'DEMAND' setting for the main query cache (query_cache), which allows you to specify exactly which queries should use the cache.

Since no such features are documented anywhere, I expect they don't exist, but it would be fantastic if anyone has knowledge regarding why not, or when we might expect them to.

1

The cache needs to return the same results as running the subquery would, so the scope has to be limited by active transaction too.

But seeing that the cache can only be enabled or disabled as an optimizer feature and not configured otherwise, my guess is that it actually is instantiated once for each specific cacheable subquery for a query where the optimizer decides it is worth it.

That seems to be confirmed by: "It has a unique index over all parameters." - this is not doable if you store results for more than one subquery in the same table.

So it seems to me that the cache is instantiated once for each cacheable subquery and its lifetime would be limited by the outer query execution.

Edit: I checked some source code (disclaimer: I definitely do not fully understand the code as MariaDB is quite big and complex piece of software and my knowledge of C++ is far from expert level). The cache is implemented by the Expression_cache_tmptable. The table used to store the cached results is created in the init() method which is being called from some methods in Item_cache_wrapper and AFAIK lifespan of various Items is single (outer) query execution as different types of Items keep and manage different parts of the query (sort of expression tree if I understand it correctly).

From that I deduce that one instance of the subquery cache is created for each cacheable subquery at the time query is being "prepared" for execution. It is then initialized (and the temp table created) when it is first accessed during the query execution (so only if the subquery is actually executed at least once). The condition of "<0.2" is then checked (for each subquery independently) after 200 misses (that will probably be after processing some 200+ rows of the outer query) to see if the cache has any positive effect, and it is disabled early if it does not help. Then, if the cache table grows too much, it is checked again according to the conditions listed in the manual.

So the subquery cache is not tuneable, but seems to be made as unintrusive as possible. The optimizer will ever instantiate it for subqueries which can at least theoretically use it and it will be disabled early in the query execution if it does not prove useful.

If you are still worried about it slowing your queries down, you can disable it globally by configuring the optimizer_switch and then enable it only for selected queries by calling

SET SESSION optimizer_switch='subquery_cache=on';

before the query execution and optionally turn it off afterwards.

  • What would be great is if the documentation just showed the schema of the cache table. I still don't have a good mental picture of it, even after scanning that init method. I'll take your word for it that 'items' relate to a single (outer) query. So your impression is that a temp table is created for every outer query that contains at least one subquery. All the parameters of the tables involved in the outer query are added to the table and indexed, then each subquery that is encountered is checked against the table, and added to it (with results) if not already present? – Nathan Stretch Oct 5 '15 at 8:42
  • Good point about being able to set the optimizer switch for the session, even right around a particular query. Obviously that would allow you to control it on a per query basis. (Didn't think of it because I wasn't thinking of the cache as a per-query device, but now that you've pointed that out, it makes sense.) I expect the impact will be very minimal though anyway, as you say; the only real issue would be high frequency, short queries involving subqueries, where the overhead from the temp table could be significant as a percentage of total query time. – Nathan Stretch Oct 5 '15 at 8:45
  • No, a separate temp table is created for each (cacheable) subquery so there can be more than one of them during the execution of one query. It shows in the explain extended .. ; show warnings; output as for instance (just simplified excerpt from a real query I encountered this morning) <expr_cache><db.table.column>(..subquery code ..) so I expect the table to look like (db_table_column int UNIQUE, result bool/int/dataset) not sure how a subquery returning multiple rows is handled, but I think from what I have seen that the entire IN (sub) or (sub) = X is the expression cached. – jkavalik Oct 5 '15 at 8:56
  • Imho creating in-memory temp table just as a data structure should not be very costly, just some allocations, the "temp table" created this way probably does not have to be "registered" etc as a user-defined tmp table has. No idea if the entire thing can be totally ignored by optimizer for trivial queries, but I do not think there are many queries with a "dependent subquery" which can be deemed trivial. And for other subqueries than "dependent" the cache cannot be used at all. – jkavalik Oct 5 '15 at 9:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.