Bug #52289 performance regression for MyISAM in sysbench OLTP_RW test
Submitted: 23 Mar 2010 2:04 Modified: 15 Jul 2010 19:54
Reporter: Alexey Stroganov Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Locking Severity:S2 (Serious)
Version:5.5.3pre/5.5.4pre OS:Any
Assigned to: Dmitry Lenev CPU Architecture:Any
Triage: Triaged: D2 (Serious)

[23 Mar 2010 2:04] Alexey Stroganov
Description:
While running sysbench/OLTP_RW test for MySQL 5.5.4pre I've observed notable drop in performance for MyISAM engine:

#
# Test: OLTP_RW:throughput, TPS
#
# Server 1 - 5.1.40sp1
# Server 2 - 5.5.2-m2
# Server 3 - 5.5.4-m3
# Server 4 - 5.5.4-m3-runtime-exp
#
#            Server 1 Server 2 Server 3 Server 4
# Thread       MYISAM   MYISAM   MYISAM   MYISAM
          4    451.86   440.31   438.70   438.94
         16    452.64   443.39   417.77   414.20
         64    443.99   435.64   356.31   352.10
        128    441.34   435.00   301.28   300.28
        256    442.31   432.10   249.99   243.05

The suspect is new MDL code pushed to 5.5.3.

oprofile info for 5.5.2(OLTP_RW/MyISAM/256 threads):
----------------------------------------------------
CPU: Core 2, speed 2000 MHz (estimated)
Counted CPU_CLK_UNHALTED events (Clock cycles when not halted) with a unit mask of 0x00 (Unhalted core cycles) count 100000
samples  %        image name               symbol name
207599   10.0861  mysqld                   my_hash_sort_simple
74013     3.5959  mysqld                   ptr_compare_0
50241     2.4409  mysqld                   my_lengthsp_8bit
37264     1.8105  vmlinux-2.6.16.46-0.12-smp __oom_kill_task
34919     1.6965  mysqld                   bmove512
32278     1.5682  libc-2.4.so              memcpy
28597     1.3894  mysqld                   my_strnncollsp_simple

oprofile info for 5.5.4(OLTP_RW/MyISAM/256 threads):
----------------------------------------------------
CPU: Core 2, speed 2000 MHz (estimated)
Counted CPU_CLK_UNHALTED events (Clock cycles when not halted) with a unit mask of 0x00 (Unhalted core cycles) count 100000
samples  %        image name               symbol name
196081    9.4305  mysqld                   my_pthread_fastmutex_lock
111851    5.3795  mysqld                   MDL_lock::find_deadlock(MDL_ticket*, Deadlock_detection_context*)
101926    4.9021  vmlinux-2.6.16.46-0.12-smp sys_init_module
92900     4.4680  vmlinux-2.6.16.46-0.12-smp move_task_off_dead_cpu
90243     4.3402  vmlinux-2.6.16.46-0.12-smp module_address_lookup
82975     3.9907  vmlinux-2.6.16.46-0.12-smp do_sched_setscheduler
77944     3.7487  vmlinux-2.6.16.46-0.12-smp pci_add_new_bus
66379     3.1925  libpthread-2.4.so        pthread_cond_wait@@GLIBC_2.3.2
64550     3.1045  vmlinux-2.6.16.46-0.12-smp rebalance_tick
50972     2.4515  libpthread-2.4.so        pthread_mutex_trylock
46953     2.2582  libpthread-2.4.so        pthread_mutex_lock
45842     2.2048  mysqld                   MDL_object_lock::incompatible_waiting_types_bitmap() const
44016     2.1169  libpthread-2.4.so        __pthread_mutex_cond_lock
39762     1.9123  mysqld                   MDL_ticket::is_incompatible_when_waiting(enum_mdl_type) const
37370     1.7973  libpthread-2.4.so        __pthread_mutex_unlock_usercnt

How to repeat:
Run sysbench/OLTP_RW test for 5.5.3/5.5./4 MyISAM
[23 Mar 2010 19:42] Alexey Stroganov
Checked issue on Solaris 10/x64:

#
# Test: OLTP_RW/MyISAM, throughput TPS
#
# Solaris/x64:

threads      5.5.2    5.5.4-trunk   5.5.4-runtime-exp
---------------------------------------------
128          201        141           123
256          204        124           122
[24 Mar 2010 9:13] 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/104158

3002 Dmitry Lenev	2010-03-24
      Tentative fix for bug #52289 "performance regression for
      MyISAM in sysbench OLTP_RW test".
      
      Improve concurrency in MDL_lock::find_deadlock() method
      by trying to reduce time during which MDL_lock::m_rwlock
      is held. This is done by avoiding unnecessary iterations
      through lists of granted or waiting tickets if we know
      that they don't contain tickets conflicting with the one
      which wait is being analyzed by this method.
[25 Mar 2010 5:07] Dmitry Lenev
Investigation showed that this regression is mostly caused by thundering herd problem which occurs when MDL_lock::wake_up_waiters() wake-ups all contexts
waiting on the lock and not only those which can continue and not cause problem to each other (e.g. several requests for SNRW lock are woken up).
[3 Jun 2010 11: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/110095

3037 Konstantin Osipov	2010-06-03
      A code review comment for Bug#52289.
      
      Encapsulate the deadlock detection functionality into 
      a visitor class, and separate it from the wait-for graph
      traversal code.
      
      Use "Internal iterator" and "Visitor" patterns to 
      achieve the desired separation of responsibilities.
      
      Add comments.
[3 Jun 2010 13:23] 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/110108

3037 Konstantin Osipov	2010-06-03
      A code review comment for Bug#52289.
      
      Encapsulate the deadlock detection functionality into 
      a visitor class, and separate it from the wait-for graph
      traversal code.
      
      Use "Internal iterator" and "Visitor" patterns to 
      achieve the desired separation of responsibilities.
      
      Add comments.
[3 Jun 2010 13:39] 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/110111

3037 Konstantin Osipov	2010-06-03
      A code review comment for Bug#52289.
      
      Encapsulate the deadlock detection functionality into 
      a visitor class, and separate it from the wait-for graph
      traversal code.
      
      Use "Internal iterator" and "Visitor" patterns to 
      achieve the desired separation of responsibilities.
      
      Add comments.
[3 Jun 2010 14:09] 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/110119

3037 Konstantin Osipov	2010-06-03
      A code review comment for Bug#52289.
      
      Encapsulate the deadlock detection functionality into 
      a visitor class, and separate it from the wait-for graph
      traversal code.
      
      Use "Internal iterator" and "Visitor" patterns to 
      achieve the desired separation of responsibilities.
      
      Add comments.
     @ sql/mdl.cc
        Encapsulate deadlock detection into a class.
     @ sql/mdl.h
        Adjust for a rename of a class.
[3 Jun 2010 14:33] 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/110125

3038 Konstantin Osipov	2010-06-03
      A follow up for the previous patch, titled:
      A code review comment for Bug#52289.
      Encapsulate the deadlock detection functionality into
      a visitor class...
      
      Remove a race introduced by omission: 
      initialize iterators under a read lock on the object.
     @ sql/mdl.cc
        Initialize iterators under a read lock on the object.
[7 Jun 2010 13:41] 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/110356

3042 Dmitry Lenev	2010-06-07
      Follow-up for bug#52289 "performance regression
      for MyISAM in sysbench OLTP_RW test".
      
      Fixes compilation warnings about local variable
      shadowing I_P_List_fast_push_back::last member.
      
      Renamed I_P_List_fast_push_back::last member
      to m_last. Also to keep member naming consistent
      renamed I_P_List::first member to m_first.
[7 Jun 2010 15:23] Dmitry Lenev
The following fix for this bug was pushed into mysql-trunk-runtime
team tree http://lists.mysql.com/commits/110312.
[22 Jun 2010 13:08] Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100622130139-u05awgya93zvbsop) (version source revid:marko.makela@oracle.com-20100603095032-v5ptkkzt1bhz0m1d) (merge vers: 5.1.48) (pib:16)
[22 Jun 2010 13:10] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100622130623-r7yhm89fz9n5t9nb) (version source revid:alik@sun.com-20100622130528-187gd949sa9b6pa6) (pib:16)
[15 Jul 2010 19:54] Paul Dubois
Noted in 5.5.5 changelog.

For a concurrent load of 16 or more connections containing many LOCK
TABLES WRITE statements for the same table, server throughput was
significantly lower for MySQL 5.5.3 and 5.5.4 than for earlier 
versions (10%-40% lower depending on concurrency).