MySQL is so slow on Windows... Really?

Last week a customer called me and reported that MySQL was 30 times slower than MS SQL server. Oooops. That's a lot. No way to argue or throw smoke bombs. 30 times slower!

It was a standard installation of MySQL (typical install option) on plain Windows 7 and the same for MS SQL Server 2008. The test run was a batch of 30.000 INSERT commands in an SQL script. Runtime was 1 minute on MSSQL and 30 minutes on MySQL.

Some tests later we found out that it was only bad on InnoDB. MyISAM was as fast as MSSQL. (I didn't care which one was a bit faster. I didn't care as long as InnoDB was 30 times slower) Finally we nailed the problem down to one parameter in MySQL:

innodb_flush_log_at_trx_commit

Each INSERT statement is a single transaction (autocommit mode). MySQL is configured very faithfully and ensures that each transaction is really stored on disk. This is necessary for ACID compliance. D in ACID stands for 'durability'. To store data durable, at least the log file has to be written physically. That's why MySQL when a transaction commits forces the operating system to flush its buffers and even forces the disk cache to flush its buffer. That's the meaning of flush_log_at_trx_commit = 1 in the my.ini or my.cnf file.

MSSQL is much more relaxed with your data. It writes the data to disk device. But it may stay in the disk cache, and MSSQL does not care. If you have a crash, your data is not up-to-date on the physical disk and you may lose data. This is definitely not ACID compliant. Microsoft documented this here:

http://support.microsoft.com/default.aspx?scid=kb;en-us;234656

• By default, the disk cache is enabled. Use the 'Disk Properties', Hardware tab to access the 'Properties', 'Policy' tab to control the disk cache setting. (Note Some drives do not honor this setting. These drives require a specific manufacturer utility to disable cache.)
• ...
• Disk caching should be disabled in order to use the drive with SQL Server.

So to have a fair comparison beween MSSQL and MySQL either

• set innodb_flush_log_at_trx_commit = 0 (or 2)
  This forces the flush to disk only once per second and brings good performance but data is not 100% safe on disk (unless you have a battery backed write cache)
• disable the disk cache in Windows 7
  This will force MSSQL to write physically to disk. And then MSSQL is 30 times slower than before. ;-)

Lessons learned:

• Think a lot about how to do a fair comparison.
• Either run (unsafe and fast), or (safe and slow) or (safe and fast and expensive) with a battery backed write cache controller
• Read the manual for MSSQL. There may be important news on page 3647+x.

The funny thing, that confused me a lot: On my Mac InnoDB was at around 1 minute. And even MySQL on Windows 7 in Virtual Box on my Mac was about 1 minute. The reason is: MySQL on Mac makes a fsync() that does not flush the disk cache. It flushes only the buffer cache of the operating system. And Windows 7 on Virtual Box: The disk is a plain file on the host OS. And this file is of course buffered in the host OS. So Virtualization may destroy your ACID compliance...

If you need more info on the different cache levels for file IO here is a very good link:

http://smallvoid.com/article/hard-disk-cache.html

"How to find the source of queries in MySQL Query Analyzer" or "SQL comments in Query Analyzer"

MySQL Enterprise Monitor offers a tool called "Query Analyzer" (QuAn). QuAn sits between any client app and the MySQL server and logs every query and its runtime statistics. A very cool tool for analyzing your SQL. More information is available here.

If you identify a query, that needs some improvement, sometimes it is hard to identify the source of that query as well. With hundreds of different PHP scripts for example it is not easy to know, which one issued the query, that you want to modify.

A good way to achieve this is adding C-style SQL comments. Let's look at an example:

SELECT * FROM mytable /*main.php*/;

Query Analyzer will strip that comment off before archiving the query. This is ok, because QuAn wants to consolidate all similar queries and this comment is irrelevant for the query.

But you can use version specific comments in MySQL. QuAn cannot ignore these comments,

because they may be relevant for query execution. And this is the solution for our problem:

SELECT * FROM mytable /*!99999 main.php*/;

This SQL comment will be executed on MySQL version 9.99.99 and later. But this version does not exist. So in reality the comment gets not executed at all. Currently we are at MySQL 5.1.42 so it will take some time before we reach MySQL 9.99.99 ;-)

The comment is in QuAn and will be logged. So you see the comment when monitoring the query and you know immediately, where that query came from.