The Query Cache and Partitions
Like others we were not satisfied with the fix for a bug in MySQL which caused the query cache and partitioning to not work reliably together. The bug, in simple terms, was that if the query cache was enabled and you used partitioned tables and if a partitioned table was using a transactional engine like InnoDB or XtraDB, the query cache could, under certain circumstances, return incorrect results.
Returning incorrect results is a definite, high-priority bug. However, the upstream fix was to disable all caching of queries from partitioned tables. We wanted a better solution because the query cache can be very useful and beneficial for partitioned tables, just like it is useful and beneficial for non-partitioned tables.
The root of the problem was that the query cache did not have any visibility into partitioned tables. In particular it didn’t know anything about a given table’s storage engine, including if the table was transactional or not. This lack of information prevented the query cache from intelligently caching and returning the cached results of queries.
We solved this by creating a way for the query cache to talk to the underlying storage engine of a partitioned table to see if it is:
- OK to cache the result of a query
- OK to return a cached result
With that information in hand the query cache can now properly cache new queries and provide correct cached results for duplicate queries every time, no matter if the table is partitioned (or not), or transactional (or not).
The patch is already in the MariaDB 5.5 source on Launchpad and will be in our next release of MariaDB 5.5.
Update: Monty has also written about this on his blog.
just tell me, why mysql don´t do this? it´s very hard? time expensive? or maybe a oracle problem?
I don’t know. Our patch (like everything else in MariaDB) is licensed under the GPL v2. So they are free to take and incorporate it into their community edition.
Daniel,
Great job, we look forward to testing this and using it. The query cache particularly makes sense with reporting servers, where it is more likely to have partitioned tables.
Thanks!
-ivan
Don’t thank me, I’m just the messenger. 🙂
Sanja Byelkin is the developer who created the patch. I’m glad you like it!
Hello Daniel.
I’m aware this post is old, but I was looking at https://mariadb.com/kb/en/mariadb/query-cache/#queries-stored-in-the-query-cache and it says “A query will also not be added to the cache if: … It accesses a partitioned table (>=MariaDB/MySQL 5.5.23)”.
I think this contradicts this post, so I wonder if a query over a partitioned table will or won’t use the query cache.
Thank you very much!
Thanks Leandro, that documentation page appeared to be incorrect – it has been fixed.