kolbekegel

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

kolbekegel's picture

Kolbe Kegel is a Principal Support Engineer. 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.

kolbekegel

<p>I&#39;m not quite sure why, but the bazaar commit message at&nbsp;http://bazaar.launchpad.net/~mysql/mysql-server/5.5/revision/2661.803.1 is different now than what it was when I posted this entry. Here&#39;s the commit message I see from the bzr command-line client:</p> <pre> $ bzr log -r 2661.803.1 ------------------------------------------------------------ revno: 2661.803.1 committer: Mattias Jonsson <mattias.jonsson@oracle.com> branch nick: b11761296-5.1_no_qc timestamp: Mon 2012-02-20 22:59:11 +0100 message: Bug#11761296: 53775: QUERY ON PARTITIONED TABLE RETURNS CACHED 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) </mattias.jonsson@oracle.com></pre>
kolbekegel

<p>Good news: Jon Stephens at Oracle has updated the documentation to reflect the unavailability of the Query Cache for tables using Partitioning.</p> <p>&nbsp;</p> <p>http://dev.mysql.com/doc/refman/5.5/en/query-cache.html</p> <p>http://dev.mysql.com/doc/refman/5.5/en/partitioning-limitations.html</p> <p>http://dev.mysql.com/doc/refman/5.5/en/query-cache-operation.html</p> <p>http://dev.mysql.com/doc/refman/5.5/en/news-5-5-23.html</p>
kolbekegel

<p>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&#39;m glad to see that they&#39;re planning on including the fix in the next release of MariaDB:&nbsp;http://blog.mariadb.org/the-query-cache-and-partitions/</p> <p>&nbsp;</p> <p>Kolbe</p>

Newsletter Signup

Subscribe to get MariaDB tips, tricks and news updates in your inbox: