You are here
MySQL Query Cache does not work with Complex Queries in Transactions
We did recently a review of one of our customers systems and we found that the Query Cache was disabled even thought it had significant more read than write queries.
When we asked the customer why he has not enabled the Query Cache he mentioned a review that was done a few years ago and which stated that the Query Cache hit ratio was non optimal.
This was verified on a testing system which had the Query Cache enabled by accident.
But we all thought that the Query Cache would make sense in this situation so we investigated a bit more.
They have a Java application where they do pretty complex queries (10 to 30-way Joins) and they Connect with Connector/J to the database. We tried it out in the application on a dedicated system and verified that the Query Cache was not serving our queries but the query did a full dive to the data.
So first we were looking in the MySQL documentation if there is anything stated why the queries could not be stored in the Query Cache.
There are many situation when the query cache cannot be used [ 1 ] but non of those situations matched to our case. But it was clearly stated: The query cache also works within transactions when using InnoDB tables.
In an old but usually reliable source from 2006 we found the statement: Might not work with transactions [ 2 ]. This looks a bit suspicious...
To find out why the Query was not served from the Query Cache, we enabled the General Query Log and cut out the sequence which was not working as expected.The sequence sent by Connector/J looks as follows(1):
AUTOCOMMIT=0; SELECT complex Query; COMMIT; ROLLBACK; AUTOCOMMIT=1;
This sequence we were running manually in the mysql client twice (to see if Query Cache was used).
Then we did the same thing in the mysql client with the following "sequence" (2) twice as well:
SELECT complex Query;
When we were comparing the MySQL
GLOBAL STATUS variables we found the following:
|Status||before tests||after (1)||after (1)||after (2)||after (2)|
It looks like the complex Query is cached in the Query Cache within a Transaction started with
AUTOCOMMIT but then not served in a second request. When the same complex Query is run with
AUTOCOMMIT enabled it is served from the Query Cache as expected but the first Query does NOT see the cached Query from Sequence (1)!
This could be a possible explanation why the Query Cache in our customers situation had a very bad Hit-Ratio.
Unfortunately we could not reproduce this problem with a simple query on our own testing systems. But we are working on it and try to figure out when and why it happens.
This problem affects possibly all Java application using the Connector/J with transactions and possibly other programming languages as well which will run the same sequences of commands. Further it looks like it only affects complex joins.
A way out of this situation would be to not use transactions :-( or to not use too complex multi-join queries.
The tests where done with MySQL 5.1.34 and newer.
If you can reproduce this behavior please let us know.