Bug #17984 MySQL works very slow on Windows(2003/XP-pro) on AMD64 processor
Submitted: 6 Mar 2006 19:34 Modified: 29 Jun 2006 18:57
Reporter: Eugene Drobitko Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server Severity:S5 (Performance)
Version:5.0.19, 5.0.18, 4.1.18 OS:Windows (Windows x64)
Assigned to: CPU Architecture:Any

[6 Mar 2006 19:34] Eugene Drobitko
Description:
We founded that an execution some SQL sequence on Windows 2003(64bit system) on AMD64 is very slow.
During execution the MySQL's CPU usage is less than 3%.
Seems this behaviour is on all windows platforms on AMD64 processor We've checked on Windows 2003 64Bit Enterprise Edition, Windows 2003 32bit Standard Edition, Windows 2003 32bit Enterprise Edition, Windows XP 64 bit Professional.
The execution of attached binlogs sequence gets about 3.5 minutes
For comparison the execution the same binlogs sequence on Windows 2003 32Bit on AMD Athlon (32bit processor) gets only 40 seconds, during excecution time the MySQL's CPU usage is more than 50-60%.

How to repeat:
How to reproduce
1)create empty databse sw2_std
2)there is zip archive, unzip it to /mysql/bin
2)execute attached binlogs sequence by command
	>mysqlbinlog.exe logs.000001 | mysql -h localhost -u root -P 3326 -p
change here 3326 to used port.
[6 Mar 2006 19:38] Eugene Drobitko
Just a comment in each case the same configuration files was used.
[9 Mar 2006 13:42] Valeriy Kravchuk
Thank you for a problem report. I had changed severity to a more appropriate one. Please, send the my.ini file used for both 32-bit and 64-bit tests. 

Have you tried to work with Intel 64-bit Xeons (EM64T)? Just want to check that problem is AMD64-related. In my case 5.0.18 works OK on 64-bit XP edition on Xeon...
[9 Mar 2006 20:56] Eugene Drobitko
used config file

Attachment: ew.cnf (text/plain), 1.74 KiB.

[9 Mar 2006 21:01] Eugene Drobitko
Unfortunately we have no XEON-64 bit machines.
We already played with memory and performance setting in config file. but saw no appreciable changes.
[16 Mar 2006 10:11] MySQL Verification Team
On our AMD64 machine running Win2003 64-bit took 2 mins 2sec to import the binlogs. CPU was between 1% and 6%.
[23 Mar 2006 6:45] Valeriy Kravchuk
I've also checked with 64-bit XP Pro on (busy!) Dell with Xeon 3GHz (with hyperthreading enabled). Binlog was loaded by MySQL 5.0.18 faster than in 10 seconds, with temporary increase of CPUs usage to more than 50%.

Compare it with Shane's results presented in the previous comment. It is surely a problem with low performance of 5.0.18 on AMD64 and 64-bit Windows versions.
[24 Mar 2006 6:30] Valeriy Kravchuk
64-bit version of 5.0.19 (http://dev.mysql.com/get/Downloads/MySQL-5.0/mysql-5.0.19-winx64.zip) does not solve this problem also. The same sample binlog was loaded after 2 minutes and almost 30 seconds (on the same AMD64 Windows 2003 machine as in Shane's test), and CPU load was around 1%-7%, with a couple of spikes to 10% +.
[25 Mar 2006 18:35] MySQL Verification Team
In a simple test of 10000 loops, I found that CREATE TABLE is around 3 times slower on the AMD64 box.  I shall attempt to time each individual call.
[3 Apr 2006 16:13] MySQL Verification Team
php to show a slow down on AMD64

Attachment: profile1.php (application/octet-stream, text), 769 bytes.

[3 Apr 2006 16:19] MySQL Verification Team
The above profile1.php script takes 38 seconds to run on our AMD64/Win2003. But took 8 seconds on my Intel P4/Win2000.

AMD64 results:
---------------
TOTAL TIME: 38.369929075
AVERAGE A->B: 0.000471844
AVERAGE B->C: 0.034787918

Intel32 results:
----------------
TOTAL TIME: 9.252086878
AVERAGE A->B: 0.002118689
AVERAGE B->C: 0.006777430
[11 Apr 2006 17:34] Mark Matthews
This doesn't appear to happen on _all_ AMD64 platforms that we've tested on, so the bug team is working on isolating what the issue really is.
[25 Apr 2006 17:57] Martin Brunecky
I doubt the problem is 64-bit specific.
I have the same problem with > an ORDER of a MAGNITUDE slower INNODB table loads (and Hibernate inserts/updates) on MySQL 5.0.18, 5.0.19 on both:

Windows XP Professional SP 2, Dell Optipex GX280 Pentium 4 3.2 GHz   and
Windows 2000 SP 4, (non-brand) Pentium 4 3.00 GHz (dual)

When I say SLOW I am comparing to MySQL 5.0.16 on RedHat Linux 3.x on Dell Power Edge 3.0 GHz (dual), and by order of magnitude I mean 10 times. Loading a ZIP code table (~70,000 rows by $ mysql < zipcodes.sql) on Windows means a LONG lunch, while the same thing loads in about 1 minute on Linux.

The behavior is reminiscent of doing a DNS name lookup on each query (which is a nonsence, as here the connection remains open) -- or some other network file system lookup activity. But in general, I believe that the miss-behavior is related to "something" in (our) network setup.

drop table if exists `zipcodes`;
CREATE TABLE `zipcodes` (
  `Id`        int(11) NOT NULL auto_increment,
  `Version`   int(11) default '0',
  `City`      varchar(64) collate utf8_bin default '',
  `State`     char(2) collate utf8_bin default '',
  `ZIP`       int(11) default NULL,
  `Area`      int(11) default NULL,
  `Fips`      int(11) default NULL,
  `County`    varchar(64) collate utf8_bin default '',
  `Preferred` char(1) collate utf8_bin default '',
  `Type`      char(1) collate utf8_bin default '',
  PRIMARY KEY     (`Id`),
  KEY `CityIdx`   (`City`),
  KEY `StateIdx`  (`State`),
  KEY `CountyIdx` (`County`),
  KEY `ZipIdx`    (`ZIP`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='ZipZode 5 Databas
e';

/*!40101 SET NAMES utf8 */;
INSERT INTO `zipcodes` VALUES (2,0,'100 Palms','CA',92274,760,6065,'Riverside','
N','');
INSERT INTO `zipcodes` VALUES (3,0,'1000 Palms','CA',92276,760,6065,'Riverside',
'N','');
INSERT INTO `zipcodes` VALUES (4,0,'12 Mile','IN',46988,574,18017,'Cass','N','')
;
INSERT INTO `zipcodes` VALUES (5,0,'29 Palms','CA',92277,760,6071,'San Bernardin
o','N','');
[25 Apr 2006 18:14] Martin Brunecky
Oops, never mind, bug 11182 says:

Please test with the below server start option:
innodb_flush_log_at_trx_commit = 0

Even though this makes InnoDB "unsafe" (unless you have a battery backup), it explains the problem - on Linux, you never get a true, physical write-thru, all the way to the disk track.

While at that, it would be a good addition to FAQ...
[6 May 2006 20:32] MySQL Verification Team
New timings:

win2003-amd64, with only option "Enable write caching on the disk"
---------------------------------------------------------------
TOTAL TIME: 94.081572056
AVERAGE A->B: 0.020807106
AVERAGE B->C: 0.067836740

win2003-amd64 with options "Enable write caching on the disk" and "Enable advanced performance"
--------------------------------------------------------
TOTAL TIME: 10.458479881
AVERAGE A->B: 0.000523505
AVERAGE B->C: 0.006615723

The above options are accessible in Device manager, in the properties of the hard disk, beneath the Policies table.  Please test this and let us know if results improve.
[8 May 2006 15:53] James Day
As Microsoft notes in their description of this option: "By enabling write caching, file system corruption and/or data loss could occur if the machine experiences a power, device or system failure and cannot be shutdown properly." http://support.microsoft.com/?kbid=259716

The expected results of turning on the write caching option are as follows:

1. Writes get faster. Perhaps 20 or more times as fast sometimes.

2. The system no longer has ACID guarantees.

3. InnoDB tablespace corruption requiring restore from backup and binary log should be expected if there is unexpected power loss while the server is writing data.

The safe way to get the faster speed is to use a write caching disk controller with battery backup and hard drive and OS write caching disabled. Alternatively, it's worth considering whether the application is as efficient as possible, doing things like inserting multiple records per transaction instead of one.

ACID has a performance penalty: each transaction has to wait for one revolution of the hard drive holding the InnoDB log unless you have a write caching disk controller. That's the price you pay for ACID guarantees.
[10 May 2006 11:09] Valeriy Kravchuk
Eugene,

Please, check options in Device manager, as explained by Shane on May 6. Can you confirm the same performance increase?

On my Xeon-based XP 64-bit write cache was on by default (an no obvious way to switch it off).
[28 May 2006 22:40] Eugene Drobitko
Hi, Valeriy
I'm sorry, I was on vacation last 20 days.

I've checked. Yes, the performance increases significantly  in that case.
[29 May 2006 18:57] Valeriy Kravchuk
Is that performance increase enough, in your case, to mark this report as not a (MySQL's) bug?
[29 Jun 2006 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".