| Bug #52102 | InnoDB Plugin shows performance drop compared to InnoDB (Windows) | ||
|---|---|---|---|
| Submitted: | 16 Mar 2010 16:20 | Modified: | 15 Dec 2010 0:32 |
| Reporter: | Jonathan Miller | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: InnoDB Plugin storage engine | Severity: | S5 (Performance) |
| Version: | 5.0, 5.1, 5.5.5m3 | OS: | Windows (Any) |
| Assigned to: | Calvin Sun | CPU Architecture: | Any |
[16 Mar 2010 16:20]
Jonathan Miller
[28 Mar 2010 13:44]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/104522 2996 Vladislav Vaintroub 2010-03-28 Bug #52102 InnoDB Plugin shows performance drop compared to InnoDB (Windows) Based on benchmark results, removed Innodb's usage of heavyweight Windows kernel object (Mutex), replaced with lightweight user-mode mostly CRITICAL_SECTION, aka os_fast_mutex_t in Innodb terminology. Problem: In high concurrency situations innodb on Windows shows unusually high context switching, resulting from locking kernel mutexes. xperf profiler sampled on context switches shows the highest usage rate coming from WaitForSingleObject). The CPU usage drops to ~60%, as do the benchmark values (# of transaction per second drops at about the same rate).Note also that the performance drop was mostly seen after the "atomics" patch was checked in, which would use slow kernel mutex more often, after a short atomics spinning. Solution:Use CRITICAL_SECTION. It is is highly optimized for the purpose of serializing threads inside of the same process, so there is no reason to fallback to kernel objects (which is designedto serialize different processes) and expensive syscalls . Note, that fast mutexes were also used on any system other than Windows up to now, and the patch mainlly removes the Windows specific code. Also, fix os_fast_mutex_trylock to actually match the definition (it should be TryEnterCriticalSection, not EnterCriticalSection) Also, fix os_thread_yield to use SwitchToThread() instead of Sleep(0). Sleep(0) does not yield to lower priority threads on older Windows.
[1 Apr 2010 12:55]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/104890 3537 Sergey Vojtovich 2010-04-01 Applying InnoDB snapshot, fixes BUG#52102. Detailed revision comments: r6840 | calvin | 2010-03-19 00:32:23 +0200 (Fri, 19 Mar 2010) | 6 lines branches/zip: Fix Bug #52102 InnoDB Plugin shows performance drop comparing to builtin InnoDB (Windows only). Disable Windows atomics by default. Approved by: Inaam
[6 Apr 2010 7:59]
Bugs System
Pushed into 5.1.46 (revid:sergey.glukhov@sun.com-20100405111026-7kz1p8qlzglqgfmu) (version source revid:svoj@sun.com-20100401151005-c6re90vdvutln15d) (merge vers: 5.1.46) (pib:16)
[28 Apr 2010 4:10]
James Day
The fix for this is in the InnoDB Plugin version 1.0.7 which was included with MySQL 5.1.46.
[5 May 2010 15:14]
Bugs System
Pushed into 5.1.47 (revid:joro@sun.com-20100505145753-ivlt4hclbrjy8eye) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[10 May 2010 13:31]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/107852 3068 Vladislav Vaintroub 2010-05-10 Bug#52102: performance drop in Innodb (Windows). Fix innodb synchronization primitives on Windows, - use critical section for os_mutex_t, - use condition variables if available for os_event_t. - fix incorrect implementation of os_mutex_trylock Also, fix compile problems on 64 bit Windows.
[10 May 2010 15:20]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/107875 3068 Vladislav Vaintroub 2010-05-10 Bug#52102: performance drop in Innodb (Windows). Fix innodb synchronization primitives on Windows, - use critical section for os_mutex_t, - use condition variables if available for os_event_t. - fix incorrect implementation of os_mutex_trylock Also, fix compile problems on 64 bit Windows.
[28 May 2010 5:55]
Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)
[28 May 2010 6:24]
Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100524190941-nuudpx60if25wsvx) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[28 May 2010 6:52]
Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100524185725-c8k5q7v60i5nix3t) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[15 Jun 2010 8:14]
Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100615080459-smuswd9ooeywcxuc) (version source revid:mmakela@bk-internal.mysql.com-20100415070122-1nxji8ym4mao13ao) (merge vers: 5.1.47) (pib:16)
[15 Jun 2010 8:31]
Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100615080558-cw01bzdqr1bdmmec) (version source revid:mmakela@bk-internal.mysql.com-20100415070122-1nxji8ym4mao13ao) (pib:16)
[17 Jun 2010 11:57]
Bugs System
Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 12:36]
Bugs System
Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 13:23]
Bugs System
Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[28 Jul 2010 22:36]
James Day
Two parts to this fix. The first part disabled atomics use in the plugin and is in InnoDB Plugin version 1.0.7 which was included with MySQL 5.1.46. This corrects the speed issue but increases memory use back to similar levels to the built in InnoDB because the atomics implementation used fewer kernel objects. The second part is an improvement that lets atomics be used again but it's too risky to include in stable 5.1, so it's included in 5.5 and later only. Kernel objects take memory and the difference between the two ways may show up as being unable to start the server after upgrading, if the system was on the edge of operating system limits on how many objects can be created. It's possible that increasing the amount of RAM available for non-paged pool memory may help, or alternatively reducing RAM allocated to the InnoDB buffer pool may be necessary. The purpose of the buffer pool RAM is to increase performance but the lower RAM allocation may still improve performance with this fix, because the fix produces an increase in performance. Which is best would depend on the specifics of the application. For more details on the non-paged pool, how it is related to kernel objects and how to adjust the limits, see this Microsoft Technet article: http://blogs.technet.com/b/markrussinovich/archive/2009/03/26/3211216.aspx Note that configuring the system so that InnoDB uses almost all of the available resources can result in an unstable system and that can cause crashes in MySQL or other applications running on the system. You should use the suggestions in the Technet article to monitor both the paged and non-paged pool usage and limits and ensure that they aren't excessive.
[28 Jul 2010 23:16]
James Day
If affected by the increase in resource usage of the first fix, you may see these messages in your error log as symptoms: InnoDB: Could not create a Windows event semaphore; Windows error 1450 That may be repeated many times as the server tries to start up. You may also see: InnoDB: Assertion failure in thread 1234 in file .\os\os0sync.c line 234 InnoDB: Failing assertion: SetEvent(event->handle) The line number may vary and the thread number will be different. It's also possible, but less likely, that the only symptom will be system instability or crashes of MySQL or another application if you are unfortunate and happen to be just at the edge of what is possible. It's not likely that you will start to see these symptoms until your allocations to the InnoDB buffer pool on 64 bit systems start to exceed 62G but individual systems vary and it depends in part on the other applications that are in use. They may be seen at lower allocations on 32 bit systems.
[4 Aug 2010 7:51]
Bugs System
Pushed into mysql-trunk 5.5.6-m3 (revid:alik@sun.com-20100731131027-1n61gseejyxsqk5d) (version source revid:alik@sun.com-20100731074942-o840woifuqioxxe4) (merge vers: 5.5.6-m3) (pib:18)
[4 Aug 2010 8:03]
Bugs System
Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804080001-bny5271e65xo34ig) (version source revid:alik@sun.com-20100731075120-qz9z8c25zum2wgmm) (merge vers: 5.6.99-m4) (pib:18)
[4 Aug 2010 8:19]
Bugs System
Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804081533-c1d3rbipo9e8rt1s) (version source revid:alik@sun.com-20100731075120-qz9z8c25zum2wgmm) (merge vers: 5.6.99-m4) (pib:18)
[4 Aug 2010 9:01]
Bugs System
Pushed into mysql-next-mr (revid:alik@ibmvm-20100804081630-ntapn8bf9pko9vj3) (version source revid:alik@sun.com-20100731075120-qz9z8c25zum2wgmm) (pib:20)
[4 Aug 2010 9:10]
James Day
The buffer pool size limit for the restored older handling comes when the server hits the 16 million handles per process limit in Windows. This is described in more detail at http://blogs.technet.com/b/markrussinovich/archive/2009/09/29/3283844.aspx?PageIndex=2 . The limit for MySQl 5.5 is expected to be less affected by this because of the new way it works.
[4 Aug 2010 9:59]
Roel Van de Paar
Marked bug #22268 as a duplicate of this one.
[4 Aug 2010 10:21]
Roel Van de Paar
This issue can be seen with crashes like this: - InnoDB: Assertion failure in thread 1136 in file .\os\os0sync.c line 234 or 236 - InnoDB: Assertion failure in thread 8740 in file .\srv\srv0srv.c line 1357 It looks like MySQL uses an excessive amount of event handlers in Windows. This can easily be seen by turning on the "handles" column in task manager. Other Windows applications (including things like Firefox and IE) use at maximum a few thousand handles. However, even by default, mysqld uses ~38K: ========== C:\mysql\bin>C:\Users\Roel\Downloads\Handle\handle -p mysqld.exe -s Handle v3.42 Copyright (C) 1997-2008 Mark Russinovich Sysinternals - www.sysinternals.com Handle type summary: ALPC Port : 4 Desktop : 1 Directory : 2 EtwRegistration : 25 Event : 37850 <------ File : 28 IoCompletion : 2 Key : 6 KeyedEvent : 1 Mutant : 25 Thread : 22 Timer : 3 TpWorkerFactory : 4 WindowStation : 2 Total handles: 37975 ========== And, with a 4Gb innodb_buffer_pool this increases to a huge 1.2 Million (on 5.1.49): ========== Handle type summary: ALPC Port : 4 Desktop : 1 Directory : 2 EtwRegistration : 25 Event : 1204377 <------ File : 28 IoCompletion : 2 Key : 6 KeyedEvent : 1 Mutant : 25 Thread : 22 Timer : 3 TpWorkerFactory : 4 WindowStation : 2 Total handles: 1204502 ========== However, with 5.5.6m3 (which includes the fix from this bug): ========== Handle type summary: ALPC Port : 4 Desktop : 1 Directory : 2 EtwRegistration : 25 Event : 642 <------ File : 18 IoCompletion : 2 Key : 5 KeyedEvent : 1 Mutant : 1 Thread : 20 Timer : 3 TpWorkerFactory : 4 WindowStation : 2 Total handles: 730 ========== These values were checked with this tool (and verified in task manager): http://technet.microsoft.com/en-au/sysinternals/bb896655.aspx Vladislav is right on various points in his [4 Aug 1:22] comment on bug #22268: o About 2Gb of nonpaged pool usage for a ~62Gb innodb_buffer_pool However, based on research, this small amount of nonpaged pool usage is not of any concern. o The maximum amount of handles in windows is 16 million handles. This limit is apparently hardcoded. This amount is reached around the innodb_buffer_pool=63Gb allocation area. (=70Gb always crashed the server). Also interesting is that the handles are allocated *immediately* and stay constant, but them some other resource depletion seems to happen (looks like some sort of "filling/exhaustion" effect). See attached png image. Also see questions inside image. The issue is also very easy to reproduce on various versions of Windows (Win7, W2K8, W2K8R2): Adjust the paging file size (virtual memory) to be 130Gb orso (the issue is not reproducible when this is not sufficiently large). The more memory you have in a machine, likely the less you need to allocate here. Basically, we want to try and assign a 70GB pool. THe issue can be reproduced on a low-memory machine just fine. Then set the innodb_buffer_pool to 70Gb and start mysql. Main questions: #1 Why so many event handles? #2 Why are there 4 handles per InnoDB page (63Gb @ 16k/page = 4 128 768 pages with 16 Million handles = 4 per page)? #3 Can this fix be backported to 5.1? (E/R values?)
[4 Aug 2010 10:40]
Roel Van de Paar
Task manager overview & questions
Attachment: 52102.png (image/x-png, text), 106.55 KiB.
[4 Aug 2010 11:15]
Roel Van de Paar
This issue would likely be even more pronounced on XP with handles limit at 10K (http://support.microsoft.com/kb/327699)
[14 Dec 2010 9:03]
Roel Van de Paar
Actual changelog entry: InnoDB Storage Engine: Improved performance and scalability on Windows systems, especially for Windows Vista and higher. Re-enabled the use of atomic instructions on Windows systems. For Windows Vista and higher, reduced the number of event handles used. To compile on Windows systems now requires Windows SDK v6.0 or later; either upgrade to Visual Studio 2008 or 2010, or for Visual Studio 2005, install Windows SDK Update for Windows Vista. (Bug#22268)
[15 Dec 2010 1:04]
Roel Van de Paar
Public changelog entry above was for 5.6, this one is recorded for 5.1: InnoDB Storage Engine: Fixed a performance issue on Windows systems that affected the InnoDB Plugin, by turning off atomic instructions. (Bug#52102) http://dev.mysql.com/doc/refman/5.1/en/news-5-1-46.html
