| 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: | |
| Category: | MySQL Server: Locking | Severity: | S2 (Serious) |
| Version: | 5.5.3pre/5.5.4pre | OS: | Any |
| Assigned to: | Dmitry Lenev | CPU Architecture: | Any |
[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).
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