Upstream fix for http://bugs.mysql.com/bug.php?id=70768 causes RW lock creations and destructions at a high rate during ibuf merge
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MySQL Server |
Unknown
|
Unknown
|
|||
Percona Server moved to https://jira.percona.com/projects/PS |
Fix Released
|
Medium
|
Laurynas Biveinis | ||
5.1 |
Invalid
|
Undecided
|
Unassigned | ||
5.5 |
Invalid
|
Undecided
|
Unassigned | ||
5.6 |
Fix Released
|
Medium
|
Laurynas Biveinis |
Bug Description
MySQL 5.6.16 fixed http://
The short-lived private dict_table_t objects such as those used in ibuf merge got these dynamic rwlatches too. Each (non-priority) rwlatch creation performs heap memory allocations, acquires rw_lock_list_mutex, creates two OS events, which in turn acquire os_sync_mutex.
Parts of a Perf report on the preliminary PS-5.6.16 branch.
- 14,88% mysqld mysqld [.] ut_delay(unsigned long)
- ut_delay(unsigned long)
- 90,02% mutex_spin_
- 47,73% rw_lock_
+ ibuf_build_
- 46,52% rw_lock_
+ 92,34% _ZL35ibuf_
+ 6,54% ibuf_merge_
+ 1,11% ibuf_rec_
...
- 6,35% mysqld libpthread-2.12.so [.] pthread_mutex_lock
- pthread_mutex_lock
- 35,31% os_event_create()
- 60,64% rw_lock_
+ ibuf_build_
- 39,36% mutex_create_
+ ibuf_build_
- 19,28% sync_array_
- 99,16% mutex_spin_
- 50,51% rw_lock_
+ ibuf_build_
- 49,26% rw_lock_
- dict_mem_
+ 92,81% _ZL35ibuf_
+ 6,48% ibuf_merge_
+ 0,71% ibuf_rec_
+ 0,82% rw_lock_
- 18,17% os_fast_
- 67,53% os_event_
- 59,21% rw_lock_
- dict_mem_
+ 91,27% _ZL35ibuf_
+ 6,96% ibuf_merge_
+ 1,77% ibuf_rec_
- 40,79% mutex_free_
- dict_mem_
+ 91,62% _ZL35ibuf_
+ 6,85% ibuf_merge_
+ 1,53% ibuf_rec_
- 32,47% dict_mem_
+ 89,70% ibuf_dummy_
+ 8,57% ibuf_merge_
+ 1,73% ibuf_rec_
- 7,98% os_event_
- 53,98% rw_lock_
- dict_mem_
+ 91,23% _ZL35ibuf_
+ 7,07% ibuf_merge_
+ 1,69% ibuf_rec_
- 46,02% mutex_free_
- dict_mem_
+ 91,32% _ZL35ibuf_
+ 6,99% ibuf_merge_
+ 1,69% ibuf_rec_
- 7,17% sync_array_
- 98,83% mutex_spin_
- 50,14% rw_lock_
+ ibuf_build_
- 49,35% rw_lock_
- dict_mem_
+ 92,97% _ZL35ibuf_
+ 6,19% ibuf_merge_
+ 0,84% ibuf_rec_
+ 1,14% rw_lock_
- 6,76% os_fast_
+ ibuf_build_
- 3,89% mutex_signal_
- 81,44% rw_lock_
- dict_mem_
+ 92,90% _ZL35ibuf_
+ 6,37% ibuf_merge_
+ 0,73% ibuf_rec_
- 18,19% rw_lock_
+ ibuf_build_
- 0,76% sync_array_
- 87,03% mutex_spin_
- 50,19% rw_lock_
+ ibuf_build_
- 46,32% rw_lock_
- dict_mem_
+ 93,59% _ZL35ibuf_
+ 5,77% ibuf_merge_
+ 0,64% ibuf_rec_
+ 1,88% pfs_mutex_
+ 0,87% mtr_commit(mtr_t*)
+ 0,53% _ZL20pfs_
+ 12,46% rw_lock_
- 3,68% mysqld mysqld [.] mutex_spin_
- mutex_spin_
- 48,40% rw_lock_
+ ibuf_build_
- 46,88% rw_lock_
- dict_mem_
+ 92,22% _ZL35ibuf_
+ 6,71% ibuf_merge_
+ 1,07% ibuf_rec_
The above causes a performance regression on MySQL 5.6.16 merge in TPS of RW Sysbench (I/O bound (!)): about 7% drop at 512 threads, 20% at 1024 threads.
Related branches
- Alexey Kopytov (community): Approve
-
Diff: 308 lines (+66/-33)12 files modifiedstorage/innobase/dict/dict0boot.cc (+8/-4)
storage/innobase/dict/dict0dict.cc (+3/-2)
storage/innobase/dict/dict0load.cc (+2/-1)
storage/innobase/dict/dict0mem.cc (+32/-12)
storage/innobase/fts/fts0fts.cc (+1/-1)
storage/innobase/handler/ha_innodb.cc (+3/-3)
storage/innobase/handler/handler0alter.cc (+1/-1)
storage/innobase/ibuf/ibuf0ibuf.cc (+3/-2)
storage/innobase/include/dict0mem.h (+10/-4)
storage/innobase/mtr/mtr0log.cc (+1/-1)
storage/innobase/page/page0zip.cc (+1/-1)
storage/innobase/pars/pars0pars.cc (+1/-1)
description: | updated |
The same reasoning as for dict_table_ t::stats_ latch probably applies to dict_table_ t::autoinc_ mutex too, which is not a regression but a pre-existing inefficiency. Considering fixing at the same time.