SELECT COUNT_STAR, SUM_TIMER_WAIT, AVG_TIMER_WAIT, EVENT_NAME
FROM performance_schema.events_waits_summary_global_by_event_name
where COUNT_STAR >
0 and EVENT_NAME
like 'wait/synch/%' order by SUM_TIMER_WAIT
desc limit 10; +
------------+------------------+----------------+--------------------------------------------+ | COUNT_STAR | SUM_TIMER_WAIT | AVG_TIMER_WAIT | EVENT_NAME |
+
------------+------------------+----------------+--------------------------------------------+ | 36847781 | 1052968694795446 | 28575867 | wait/synch/mutex/innodb/lock_mutex |
| 8096 | 81663413514785 | 10086883818 | wait/synch/cond/threadpool/timer_cond |
| 19 | 3219754571347 | 169460766775 | wait/synch/cond/threadpool/worker_cond |
| 12318491 | 1928008466219 | 156446 | wait/synch/mutex/innodb/trx_sys_mutex |
| 36481800 | 1294486175099 | 35397 | wait/synch/mutex/innodb/trx_mutex |
| 14792965 | 459532479943 | 31027 | wait/synch/mutex/innodb/os_mutex |
| 2457971 | 62564589052 | 25346 | wait/synch/mutex/innodb/mutex_list_mutex |
| 2457939 | 62188866940 | 24909 | wait/synch/mutex/innodb/rw_lock_list_mutex |
| 201370 | 32882813144 | 163001 | wait/synch/rwlock/innodb/hash_table_locks |
| 1555 | 15321632528 | 9853039 | wait/synch/mutex/innodb/dict_sys_mutex |
+
------------+------------------+----------------+--------------------------------------------+ 10 rows in
set (0.01 sec)
从上面的表可以确认,lock_mutex(在MySQL源码里对应的是lock_sys->mutex)的锁等待累积时间最长(SUM_TIMER_WAIT)。lock_sys表示全局的InnoDB锁系统,在源码里看到InnoDB加/解某个记录锁的时候(这个case里是X锁),同时需要维护lock_sys,这时会请求lock_sys->mutex。
在这个case里,因为在Searching rows for update的阶段频繁地加/解X锁,就会频繁请求lock_sys->mutex,导致lock_sys->mutex锁总等待时间过长,同时在等待的时候消耗了大量CPU。