We have been using SHOW ENGINE INNODB MUTEX command for years. It shows us mutex and rw-lock information that could be useful during service troubleshooting in case of performance problems. As Morgan Tocker announced in his blog post the command will be removed from MySQL 5.7 and we have to use performance_schema to get that info.
The documentation of MySQL also says that most of the command output has been removed from 5.6 and that we can find similar info in performance_schema. It doesn’t show any examples of how to use performance_schema or what is the query we need to use from now on. It is also important to mention that 5.6 doesn’t show any warning about the feature being deprecated.
This is a short blog post to show how to configure performance_schema and get the info we need. Hoping it will end up in the official documentation in some way.
The instruments we need are not enabled by default. Those are in wait/synch/mutex/% so the config line we need to use is:
1 | performance-schema-instrument='wait/synch/mutex/innodb/%=ON' |
Then, just compare the results from an idle Percona Server 5.6. First the output of SHOW ENGINE…
1 2 3 4 5 6 7 | mysql> show engine innodb mutex; +--------+------------------------------+------------+ | Type | Name | Status | +--------+------------------------------+------------+ | InnoDB | &buf_pool->flush_state_mutex | os_waits=1 | | InnoDB | &log_sys->checkpoint_lock | os_waits=2 | +--------+------------------------------+------------+ |
Now the results from the query that get us the mutex information from performance_schema:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 | mysql> SELECT EVENT_NAME, SUM_TIMER_WAIT/1000000000 WAIT_MS, COUNT_STAR FROM performance_schema.events_waits_summary_global_by_event_name WHERE SUM_TIMER_WAIT > 0 AND EVENT_NAME LIKE 'wait/synch/mutex/innodb/%' ORDER BY SUM_TIMER_WAIT DESC, COUNT_STAR DESC; +----------------------------------------------------+---------+------------+ | EVENT_NAME | WAIT_MS | COUNT_STAR | +----------------------------------------------------+---------+------------+ | wait/synch/mutex/innodb/log_sys_mutex | 11.1054 | 28279 | | wait/synch/mutex/innodb/buf_pool_flush_state_mutex | 9.7611 | 94095 | | wait/synch/mutex/innodb/os_mutex | 5.3339 | 58515 | | wait/synch/mutex/innodb/dict_sys_mutex | 2.4108 | 4033 | | wait/synch/mutex/innodb/flush_list_mutex | 2.3688 | 8036 | | wait/synch/mutex/innodb/lock_wait_mutex | 2.2412 | 4016 | | wait/synch/mutex/innodb/buf_pool_LRU_list_mutex | 2.1912 | 4182 | | wait/synch/mutex/innodb/fil_system_mutex | 0.9789 | 5060 | | wait/synch/mutex/innodb/mutex_list_mutex | 0.1723 | 8523 | | wait/synch/mutex/innodb/rw_lock_list_mutex | 0.1706 | 8245 | | wait/synch/mutex/innodb/srv_innodb_monitor_mutex | 0.0102 | 65 | | wait/synch/mutex/innodb/recv_sys_mutex | 0.0050 | 146 | | wait/synch/mutex/innodb/buf_pool_free_list_mutex | 0.0048 | 165 | | wait/synch/mutex/innodb/trx_mutex | 0.0020 | 105 | | wait/synch/mutex/innodb/srv_sys_mutex | 0.0012 | 11 | | wait/synch/mutex/innodb/trx_sys_mutex | 0.0010 | 29 | | wait/synch/mutex/innodb/lock_mutex | 0.0008 | 26 | | wait/synch/mutex/innodb/innobase_share_mutex | 0.0004 | 5 | | wait/synch/mutex/innodb/buf_dblwr_mutex | 0.0003 | 4 | | wait/synch/mutex/innodb/file_format_max_mutex | 0.0003 | 6 | | wait/synch/mutex/innodb/rseg_mutex | 0.0002 | 7 | | wait/synch/mutex/innodb/recv_writer_mutex | 0.0001 | 1 | | wait/synch/mutex/innodb/ut_list_mutex | 0.0001 | 1 | | wait/synch/mutex/innodb/ibuf_mutex | 0.0001 | 2 | | wait/synch/mutex/innodb/log_flush_order_mutex | 0.0000 | 1 | +----------------------------------------------------+---------+------------+ |
The difference is clear. We get much more information from Performance Schema. In my personal opinion, despite the extra resources needed by Performance Schema, the change is for the better.
Comments (3)
good job
>SUM_TIMER_WAIT/1000000000 WAIT_MS
sum_timer_wait is in nanoseconds? Then to devide it with 1000000000 to get ‘MS’ (milliseconds)? Shouldnt it be to seconds if you devide it with 1000000000?
@P4tt4nz: all values in performance schema should be in picoseconds, so this should be correct