MySQL is so slow on Windows 8 . . . . Really?
Di Muso (del 04/03/2013 @ 15:21:30, in Informatica, linkato 6601 volte)
taken from: mablomy

It was a standard installation of MySQL (typical install option) on plain Windows 8 Standard and the same for Windows 7. The test run was a batch of 500.000 INSERT commands in an SQL script. Runtime was 4 minutes on Win7 and 4 hours Win8.
Some tests later we found out that it was only bad on InnoDB. Finally we nailed the problem down to one parameter in MySQL:
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:
  • 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 = 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 8
    This will force MSSQL to write physically to disk. And then Win8 is 30 times slower than before. ; - )
If you need more info on the different cache levels for file IO here is a very good link: