You are here

Home » Blogs » Shinguz's blog

MySQL Query Cache does not work with Complex Queries in Transactions

Wed, 2011-06-29 13:28 — Shinguz

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)
Qcache_hits 3 3 3 3 4
Qcache_inserts 47 48 48 49 49
Qcache_not_cached 46 46 47 47 47
Qcache_queries_in_cache 0 1 1 2 2
Com_select 91 92 93 94 94

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.

Taxonomy upgrade extras: 
english
transaction
query cache
autocommit
general query log

Comments

Can you give some sensible definition of complex Query? How many tables are affected, are all tables using the same table engine (which), which type of joins are used, ... Having to guess what a complex Query might be won't make trying to reproduce that behavior easier.
danieljcomment Thu, 2011-06-30 07:55

It was just a complex Join Query as far as I can remember: 
SELECT * FROM a
JOIN b ON ...
JOIN c ON ...
...
WHERE ...
Possibly it had some GROUP BY or DISTINCT in it. Regards, Oli
olicomment Thu, 2011-06-30 08:01

see this. this bug was still alive... problem isn't' query complex or not complex. i see it in 5.5.13 community & redhat 5.1.52_log http://bugs.mysql.com/bug.php?id=42197 USE yourdatabase; set autocommit=OFF; CREATE TABLE t1 (a INT) ENGINE=InnoDB; INSERT INTO t1 VALUE (1); COMMIT; SELECT * FROM t1; COMMIT; -- BEGIN; SELECT * FROM t1; SELECT * FROM t1; COMMIT; SHOW STATUS LIKE "Qcache_hits"; # Should be 2 COMMIT; drop table t1; commit;
lukecomment Tue, 2011-07-05 11:27

Hi luke, Great! Thank you for the hint. I somehow missed this bug... :( Oli
olicomment Tue, 2011-07-05 11:43

It looks really evil as you said. I could reproduce it with the following sequence: 
SHOW GLOBAL STATUS
WHERE variable_name = 'Qcache_hits'
   OR variable_name = 'Qcache_inserts'
   OR variable_name = 'Qcache_not_cached'
   OR variable_name = 'Qcache_queries_in_cache'
   OR variable_name = 'Com_select';

SET autocommit=0; SELECT * FROM test; COMMIT;
SHOW GLOBAL STATUS
WHERE variable_name = 'Qcache_hits'
   OR variable_name = 'Qcache_inserts'
   OR variable_name = 'Qcache_not_cached'
   OR variable_name = 'Qcache_queries_in_cache'
   OR variable_name = 'Com_select';

SET AUTOCOMMIT=0; SELECT * FROM test; SELECT * FROM test; COMMIT;
SHOW GLOBAL STATUS
WHERE variable_name = 'Qcache_hits'
   OR variable_name = 'Qcache_inserts'
   OR variable_name = 'Qcache_not_cached'
   OR variable_name = 'Qcache_queries_in_cache'
   OR variable_name = 'Com_select';
I would say, that this bug drastically reduces efficiency of the Query Cache for any framework using transactions! I also tried MariaDB 5.2.7 and Percona Server 20.4 (5.5.13) and there the problem is not solved yet as well. The only Workaround I see at the moment: Do NOT use AUTOCOMMIT=0 if you do not need transactions (for example for simple SELECTs).
olicomment Wed, 2011-07-06 09:41

After further investigations with MariaDB developers we found out, that the Problem can be solved when an explicit BEGIN or START TRANSACTION is set in front of the SELECT. For example: 
SET autocommit=0; BEGIN; SELECT * FROM test; COMMIT;
This is similar to other problems we have seen earlier this year where a customers got back several rows on a PK lookup (which IMHO is a Bug)! As a result: Always use BEGIN or START TRANSACTION when you set autocommit=0!
olicomment Wed, 2011-07-06 10:36

I believe that we documented the query cache's behavior, and the actual mechanism for it, in High Performance MySQL 2nd Edition. The blog post looks like FromDual has found an additional bug, though. I don't think this is going to fundamentally change. The Query Cache is such a problem on so many servers that I can't recommend it. If there is a 70% chance that it will help, and a 5% chance that it will cause complete system lockups, it has to be disabled.
Baron Schwartzcomment Sun, 2011-07-10 23:53

Hi Baron, >90% of the MySQL installations have 1 or 2 concurrent running queries. And most of the MySQL users do not have the time or the capabilities to tune queries, applications or add indexes. I know that you, Domas and others think the Query Cache should NOT be used at all. However from what we see on the market it is for more MySQL users useful l than it harms. Regards, Oli
olicomment Mon, 2011-07-11 08:05

We found that there were similar problems with table names with a dash/minus in the name (e.g. `table-name`).
This seems to be fixed in MySQL 5.6.9 and 5.7.0. See bugs #55556, #62237 and #64821.

olicomment Sat, 2014-10-18 22:05