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.