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:
None 
Category:MySQL Server: InnoDB Plugin storage engine Severity:S5 (Performance)
Version:5.0, 5.1, 5.5.5m3 OS:Microsoft Windows (Any)
Assigned to: Calvin Sun CPU Architecture:Any
Triage: Triaged: D2 (Serious)

[16 Mar 2010 16:20] Jonathan Miller
Description:
Performance testing on Windows shows that innodb-plugin has performance drops compared to regular innodb engine.

I will upload the report/graphs after opening this bug.

Configuration Used:

[mysqld]
sql-mode="NO_ENGINE_SUBSTITUTION"
user=root
port=3306
max_connections=500
max_connect_errors=10 
table_cache=2048 
max_allowed_packet=1048576 
binlog_cache_size=1048576 
max_heap_table_size=67108864 
sort_buffer_size=65536 
join_buffer_size=1048576 
thread_cache=16 
query_cache_size=0 
ft_min_word_len=4 
transaction_isolation=REPEATABLE-READ 
tmp_table_size=64M 
skip-locking 
server-id=1 
innodb_status_file=0 
innodb_data_file_path=ibdata1:100M:autoextend 
innodb_buffer_pool_size=1300M 
innodb_additional_mem_pool_size=20M 
innodb_log_file_size=100M 
innodb_log_files_in_group=2 
innodb_log_buffer_size=8M 
innodb_flush_log_at_trx_commit=2 
innodb_lock_wait_timeout=300 
innodb_locks_unsafe_for_binlog=0 
innodb_thread_concurrency=0 
# Uncomment for Linux testing
#innodb_flush_method=O_DIRECT
ignore-builtin-innodb
plugin-load=innodb=ha_innodb_plugin.dll;innodb_trx=ha_innodb_plugin.dll;innodb_locks=ha_innodb_plugin.dll;innodb_cmp=ha_innodb_plugin.dll;innodb_cmp_reset=ha_innodb_plugin.dll;innodb_cmpmem=ha_innodb_plugin.dll;innodb_cmpmem_reset=ha_innodb_plugin.dll
plugin_dir=R:\mysql\mysql-enterprise-5.1.40sp1-winx64\lib\plugin
 

How to repeat:
Run sysbench-std using AB2 Linux (client) -> Windows (server)
[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