Feed Aggregator

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

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.

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!

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 …

Hi luke,

Great! Thank you for the hint. I somehow missed this bug… :(

Oli

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;

Great, thanks a lot! it is surprising to find out how old are the versions being used outside.

Hi Wlad,

Please find the numbers here:

MySQL Version

Version % 4.0 0.2% 4.1 2.7% 5.0 62.4% 5.1 29.6% 5.2 (MariaDB) 1.7% 5.5 3.5% 100.0%

Please do NOT compare the numbers with the numbers above. They have a slightly different base but the result should not change significantly…

PS: Roland: Better like this?

Hi cypherinfo,

Indeed, that would be interesting…

Unfortunately we do not have these numbers. CMS is nothing we have directly to do with and it seems like CMS do not typically have problems with their (MySQL) databases.

What we see, when it is related to MySQL, most often Typo3 is used. But that is not the answer to your question. Sorry!

Somebody who is a CMS specialist should take care of this question! Is somebody willing to set-up a poll for this?

Oli

it would be very interesting to study the relationship and genesis about the major cms platforms like Joomla! and their databses (GPL, Open source or not); the trend on using a commercial or free database.

Thanks for the post, interesting insights.

Just one remark: recently I bumped into an article that argues why Pie charts are bad - esp. “fancy” 3D ones. When I started reading it, I didn’t think I’d be convinced, but after seeing the examples in the article I changed my mind. When I was trying to read the Pie charts in this articles I had an immediate recollection - it’s really hard to estimate the size of the Pie slices when they are oddly sized, in particular the smaller ones.

It’s a good thing that you still also print the actual figures, but in that case the Pie charts are redundant and distracting. If you have same time, please read http://www.perceptualedge.com/articles/08-21-07.pdf - it’s really insightful IMO.

Seufzg!

I thought this question would pop up…! I try if I find some time to gather them…

Oli, do you have stas for MySQL versions used? Would be interesting to see it here.

Possibly in the 4^th graph the values are not too correct. I do not know for example where MySQL packages distributed with the SuSE distribution count and where MySQL Packages FOR the SuSE distribution count. Windows on the 4^th graph is completely missing so they are counted in the MySQL% packages…

It’s interesting to see that there are more production servers using Gentoo then Suse. Maybe Gentoo could/should have more attention from Mysql/Oracle? Just a thought …

One of our partners recently asked me on what platforms do we usually see MySQL installed out there…

The last 5 years I gave the answer: Typically it is 80% Linux, 10% Windows, 5% Solaris and 5% all others. But this was only the picture of my limited view and I was not sure how objective this was.

This time I really wanted to know it and so I collected the information of about 570 MySQL installations of customers.

The following numbers came out:

Operating System

OS cnt % Mac OSX 3 0.5% FreeBSD 5 0.9% Windows 60 10.5% Solaris Sparc 3 0.5% Solaris x86 7 1.2% Debian Linux 110 19.2% Red Hat Linux 196 34.1% other Linux 179 31.2% SuSE Linux 7 1.2% AIX 3 0.5% Unknown 1 0.2% 574 100.0%

Operating System

OS cnt % BSD 8 1.4% Windows 60 10.5% Solaris 10 1.7% Linux 492 85.7% AIX 3 0.5% Unknown 1 0.2% 574 100.0%

Platform

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

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.

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 …

Hi rhousand,

Pros of DRBD are mostly data consistency and reliability. DRBD is a sync replication and you only have your data in one place. The Cons are Performance and Complexity.

Pros of Master/Slave or Master/Master Replication: It performs often better and it is easier to set-up. The Cons are: You can easily get inconsistent data and when it breaks it can be tricky to set-it up again.

When you prefer integrity of data over performance and complexity does not scare you I would recommend to choose DRBD. If integrity is not a big issue then I would go for Master/Master Replication.

Just one thing: With Master/Master Replication you CANNOT increase I/O throughput! You just can flatten peaks!