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