You are here

Kolbe Kegel
A customer opened an issue recently to ask why the query cache wasn't working after he upgraded to MySQL 5.5.25. The reason really ended up surprising me. As of MySQL 5.5.23, the Query Cache is disabled for partitioned tables! This is a "fix" for bug #53775. At first I thought perhaps the fix for the bug had resulted in the query cache being inadvertently disabled for partitioned tables, but the comments that go along with the commit make it pretty clear that disabling the query cache was the intended "fix". You can review the commit message and the code changed at revision 2661.803.1 in the MySQL Server 5.5 repository. I agree with Mikael Ronstrom, who wrote on bug #53775, "Disabling the query cache for partitioned tables is a bit too harsh of a solution." However, the change in behavior is not nearly as harsh as the complete failure to update the documentation to reflect this pretty serious change in functionality. Not even the entry in the MySQL 5.5.23 release notes makes any mention of the effect of the change, only a small statement about the initial bug report. There's no change to How the Query Cache Operates, which explains conditions under which queries are not cached, nor to Restrictions and Limitations on Partitioning, which explains some perhaps unexpected side-effects of using Partitioning (I think the complete unavailability of query caching would qualify). So if you're seeing lower query cache utilization and you use partitioned tables, this change could very well be the explanation you're looking for.

About the Author

Kolbe Kegel's picture

Kolbe Kegel is the MariaDB Enterprise Tech Lead at MariaDB Corporation. Kolbe has worked with MySQL since 2005, first at MySQL, later at Sun Microsystems after its acquisition of MySQL Inc., then at Oracle after its acquisition of Sun.

Kolbe Kegel

I'm not quite sure why, but the bazaar commit message at is different now than what it was when I posted this entry. Here's the commit message I see from the bzr command-line client:

$ bzr log -r 2661.803.1
revno: 2661.803.1
committer: Mattias Jonsson 
branch nick: b11761296-5.1_no_qc
timestamp: Mon 2012-02-20 22:59:11 +0100
                                          RESULT FROM PREVIOUS TRANSACTION
  The current Query Cache API is not fully compatible with
  the partitioning engine.
  There is no good way to implement support for QC due to:
  1) a static callback for ha_partition would need to have access
  to all partition names and call the underlying callback for each
  [sub]partition with the correct name.
  2) pruning would be impossible, even if one used the ulonglong
  engine_data due to if engine_data is changed, the table is
  invalidated by the QC.
  So the only viable solution to avoid incorrect data is to not allow
  caching of queries using partitioned tables.
  (There are some extra changes, due to removal of \r as line break)>
Kolbe Kegel
Kolbe Kegel

This issue initially came to my attention when a SkySQL customer reported that he was seeing problems with query cache utilization. I was able to identify that it was related to partitioning and to this change in behavior made by Oracle. I escalated the issue to MariaDB and they had a fix in MariaDB along with custom binaries for the customer in just a few days. I'm glad to see that they're planning on including the fix in the next release of MariaDB: