MySQL Bugs: #11182: very slow loads of mysql Dump files on windows

Bug #11182	very slow loads of mysql Dump files on windows
Submitted:	8 Jun 2005 19:01	Modified:	19 Jun 2005 12:22
Reporter:	Dan Armbrust	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	mysql Ver 14.7 Distrib 4.1.12a, for Win	OS:	Windows (Windows XP + SP2)
Assigned to:		CPU Architecture:	Any

Description:
I dumped a set of tables from our mysql server on linux (Fedora Core 3, x86) using the following command:

mysqldump -u root -p -c -Q -S /tmp/mysql.sock LexGrid3 codingScheme codingSchemeMultiAttributes [snip other table names] > test.sql

Its about 3.5 million rows total, in 14 different InnoDB tables (with foreign keys between several of the tables, and quite a few indexes, etc)

Using the following command to reload the data to another linux server, it takes about 1 hour to load the DB:

mysql -u root -p LexGridEVS < test.sql

If I take the same data, and try to load it onto a Windows hosted mysql server, it takes something on the order of 24 hours to load.  

While the load is running, the CPU usage on the windows box is near 0.

I chopped my file down to 1000 line (this will populate 3 of my tables fully, and start the 4th) and tried running it on both windows and linux.

The windows load time was 2 minutes.  The Linux load time was 2 seconds.

I'll attach the data as a seperate comment.

How to repeat:
Try to bulk-load data onto mysql on Windows XP.  Note the speed compared to bulk loading on other platforms.

I forgot to note that the windows and linux machines are both very similar in hardware specs - Windows (XP SP2) is a 3 Ghz hyperthreaded Intel P4, while the linux (Fedora Core 3) server is a dual cpu AMD 2600.

Linux machine has 3 GB of RAM, Windows has 2 GB.

Please test with the below server start option:

innodb_flush_log_at_trx_commit = 0

and verify your buffer sizes for more adequate value for your
hardware.

That fixes the problem on windows.  Is this caused by the fact that linux buffers the flush to disk commands, while windows does not?

Shouldn't you consider changing the default value for this on the windows packages when this causes such massive slowdowns on a non-buffered disks?

Thanks for looking into this.

Hi!

If you think we can change default settings of mysql server, please submit a feature request.

I am not sure about changing a server default behavior to "less safe" one, but this should be added to FAQ "SLOW, WINDOWS", otherwise MySQL on Windows gets a bad rep in a hurry.
I am not sure why is this problem with MySQL 5, as I never saw this problem in my previous lives with MySQL 4.x (but then, I was not doing the DBA work, may be our databases were set "rrrright").