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:

  1. OK to cache the result of a query
  2. 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.

Things are definitely a little quieter here in MariaDB land as many people embark on summer vacations to rest and recharge. However we will be at OSCON 2012. We have a booth in the non-profit/projects pavilion, amazing amounts of schwag, and the famous black vodka that Monty always brings with him. We plan to have a BoF on Thursday evening (come to D135 at 8pm), and we will be at the MySQL BoF on Wednesday evening as well, just for good measure.

Come say hi. Come tell us how you’re using MariaDB. Come tell us what you want from MariaDB. Come tell us why you switched. Or what we can do to make you switch. It should be all round fun.

See you next week!