MySQL Bugs: #59133: MySQL 5.5 slower than 5.1 on Win7 Enterprise x64

Bug #59133	MySQL 5.5 slower than 5.1 on Win7 Enterprise x64
Submitted:	23 Dec 2010 14:26	Modified:	9 Mar 2011 15:42
Reporter:	Justin Cranford	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.5	OS:	Windows (Windows 7 Enterprise x64)
Assigned to:		CPU Architecture:	Any

Description:
For all the performance claims of 5.5 being faster than 5.1, I found the opposite to be true.

I just switched my desktop OS from WinXP Pro x32 to Win7 Ent x64. I am using the exact same hardware, and all resource hogs like anti-virus, spyware, indexing, and so on are turned off. Using the exact same hardware I found...

- If innodb_flush_log_at_trx_commit=1 (default), then MySQL 5.5.8 x64 is ~26% *slower* than MySQL 5.1.54 x64.
- If innodb_flush_log_at_trx_commit=2, then MySQL 5.5.8 x64 is ~22% *faster* than MySQL 5.1.54 x64.

Initially my benchmark suite was telling Win7+MySQL5.5 performance was 8-10 slower than my previous WinXP+MySQL5.1. I traced the issue to MySQL COMMIT performance. Average COMMIT times were ~24 milliseconds in 5.5.8 x64 versus 0.41 milliseconds in 5.1.54 x32 on WinXP. That is what led me to play with the the "innodb_flush_log_at_trx_commit" setting in my.ini. Changing it from the default "1" to either "2" or "0" boosted MySQL commit performance by *TWO* orders of magnitude, and boosted my benchmark by *ONE* order of magnitude.

Knowing my main bottleneck was COMMIT performance in MySQL, I did a comparison of 5.1.54 x64 and 5.5.8 x64 on the same Win7 Ent x64 machine. Running my benchmark suite again, I found MySQL 5.5 commits were consistently ~25% slower than on 5.1 with the default setting "innodb_flush_log_at_trx_commit=1", and consistently ~25% faster with "innodb_flush_log_at_trx_commit=2" or "innodb_flush_log_at_trx_commit=0".

Maybe I am missing something here. Are the performance improvements being quoted in the relase of 5.5 based on changing the default "innodb_flush_log_at_trx_commit" setting for ACID compliance. Is there more overhead in MySQL 5.5 to support ACID?

How to repeat:
1) Install MySQL 5.5.8 x64 and 5.1.54 x64 on the same Windows 7 Enterprise x64 PC.
2) Disable TCP-IP in my.ini, and turn on "enable-named-pipe" instead.
3) Run a 50-50 mix of READ and WRITE transactions against each database.
3a) Benchmark the relative COMMIT performance against both databases with innodb_flush_log_at_trx_commit=1.
3b) Benchmark the relative COMMIT performance against both databases with innodb_flush_log_at_trx_commit=2.
3c) Benchmark the relative COMMIT performance against both databases with innodb_flush_log_at_trx_commit=0.

Suggested fix:
COMMIT performance on MySQL 5.5.8 x64 should be faster than 5.1.54 x64 using the same OS, hardware, and benchmark suite with the default ACID compliance settings (innodb_flush_log_at_trx_commit=1). You should not have to sacrifice ACID compliance (innodb_flush_log_at_trx_commit=1) for MySQL 5.5 to be faster than 5.1.

Published 5.5 results were using sysbench. What behchmark are you using?

And to answer about specific Windows improvements in 5.5 - they were just about thread synchronization, not directly related to disk access or ACID

From your description it also sounds your old XP was not really flushing to disk. Perhaps, "advaced disk performance" was turned on in disk properties (e.g here http://maximumpcguides.com/windows-vista/enable-advanced-performance-on-your-hard-drive/, the description is for Vista, but for XP it would be quite similar) ?

wondering about enable-named-pipe? 
The urban legend about pipe being faster than TCP access to localhost  is still alive? I did not measure any significant difference, but from my memory pipe was always a tiny bit slower.

Yes .. I was also about asking Justin if this is a *named pipe issue only* or if same if repeatable with TCP connections on his environment.

I checked the Win7 SATA device driver setting. It was indeed disabled, but I did not notice any significant performance benefit by turning it on. Perhaps it is being overshadowed because I am still using the "2" setting instead of the default "1". I will try again with the default "1" to see if there is any difference.

Regarding "enable-named-pipe", it absolutely does make a big difference in our performance benchmark suite. Our application generates many thousands of short transactions (6 statements per transaction).

The following is real benchmarks I measured in the last release. I compared transaction throughput using a remote and local database (all on WinXP).

1) Remote database (TCP/IP): 300 transactions / sec
2) Local database (TCP/IP): 1600 transactions / sec
3) Local database (Named Pipe): 2600 transactions / sec

I expected performance to be better for a local database due to shorter communication latency, but not 5x faster throughput. I could only conclude our application was super sensitive to communication latency.

This point is only reinforced by the difference between TCP/IP and named pipe for the same local database. Switching from TCP/IP to named pipe increased transaction throughput by another 60%. It is not as pronounced as the TCP/IP comparison, but the results are readily reproducable and consistent.

My conclusion is applications that use big transactions will not see much benefit from named pipes, but apps that use small/short transactions will definitely benefit from using named pipes over TCP/IP (or UNIX socket on *NIX).

Interesting. sysbench I was using has quite small transactions, and pipe was not a big deal there.  Anyway, if you have a benchmark, it makes sense to describe that benchmark,so other people at least attempt to script it. To my knowledge, the only tools MySQL performance QA team has are sysbench and DBT2 (sort-of TPCC)

Unfortunately I cannot post the benchmark, but the lesson is to always do your own benchmark. One man's benchmark may or may not apply to your own workload.

I ran my benchmark again through 8 different scenarios to test each of these configuration options:

1) innodb_flush_log_at_trx_commit ("1" or "2")
2) Turn off Windows write-cache buffer flushing on the device (Win 7 Device Mgr)
3) Named Pipe versus TCP/IP

When the defaults in MySQL and Win7 Device Mgr are used, performance is terrible. Commits take ~27 milliseconds each. The benefit of using Named Pipe (27.148msec) versus TCP/IP (27.335msec) is there, but trivial.

Setting "innodb_flush_log_at_trx_commit=2" OR checking "Turn off Windows write-cache buffer flushing on the device" for the HDD in Windows 7 Device Manager drastically improved COMMIT performance in MySQL. It drops down to 0.25-0.75 milliseconds, or about 100x faster. Again the benefit of Named Pipe was there, but it is much more significant because commit times are on the same order of latency times.

The best possible configuration I found was change both defaults in MySQL and Win. Average commit times dropped to 0.2404msec with Named Pipe, and 0.3362 with TCP/IP.

I am not going to make a generalization because everyone should always do their own benchmarking, but optimal performance for me was using these three configuration settins.

1) MySQL my.ini

- enable-named-pipe
- innodb_flush_log_at_trx_commit=2 (or 0 if you wish)

2) Check "Turn off Windows write-cache buffer flushing on the device" in Windows 7/Vista Device Manager (if-and-only-if UPS or laptop battery backup)

3) Use Named Pipe (or UNIX socket) instead of "TCP/IP".

4) Disable last access modification time (Windows reg hack, *NIX fstab noatime)

>Unfortunately I cannot post the benchmark, but the lesson is to always do your > own benchmark. One man's benchmark may or may not apply to your own workload.

This is true, but if one is writing DBMS then it is best to run everybody else's emulated workloads. Simple tools like sysbench are fine more of the times, more complicated tools like TPCC are better, but the more different scenarios, the better coverage it gets  would descrease probability for regression.

Justin, please upload as files the my.ini files for both the 5.1 and 5.5 versions. SHOW GLOBAL STATUS output just after the test on both and SHOW INNODB STATUS just before it ends would also be nice, check that the averages are over more than 0 seconds, a few lines from the top. INNODB STATUS just before the end because the results will change as soon as the test ends and it starts doing more background work.

One change between 5.1 and 5.5 is that concurrent transaction commits for InnoDB now work provided sync_binlog is 0, but didn't work in 5.1. if you're using 0 then as a test you might try setting sync_binlog = 10000 to see if that changes things. sync_binlog =1 is often mentioned but that causes a binary log flush at every transaction commit and would be expected to make things slower. I suggested trying 100000 as a value that's not 0 but won't add many fsyncs.

I'm a little surprised that you had to do anything to disable last access time tracking. The default at least for consumer Windows 7 is the same as Vista, last access time logging turned off. So there should be no need to try to use a noatime equivalent.

Most benchmarks of MySQL, whoever does them, have the binary log turned off, innodb_support_xa turned off and innodb_flush_log_at_trx_commit = 0. That's an assumption that files never become damaged (so binlog not needed), that order of transactions in the binary log can be different from InnoDB (unsafe assumption, because restores may not match production data) and that the server never has a power loss (else recent transactions might be lost). The reason people tend to use those settings is that they disable a lot of fsyncs and those are very slow. But that's benchmarks. If your real world performance is worse with the same settings and hardware, we want to work with you to find out why, so we can either explain it or fix it.

If you have one of our support contracts please open a support issue about this as well.

In addition to the sync_binlog setting I mentioned, you might also usefully check the effect of changing innodb_commit_concurrency from 0/unlimited if you have sync_binlog set to 0. This may be helpful for I/O limited workloads, with settings such as 4, 8 or 16 possibly being useful.

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".