Over a million developers have joined DZone.

Getting Mutex Information from MySQL's performance_schema

· Performance Zone

Evolve your approach to Application Performance Monitoring by adopting five best practices that are outlined and explored in this e-book, brought to you in partnership with BMC.

Written by  for The MySQL Performance Blog

We have been using the 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:

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…

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:

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.

Learn tips and best practices for optimizing your capacity management strategy with the Market Guide for Capacity Management, brought to you in partnership with BMC.

Topics:

Published at DZone with permission of Peter Zaitsev, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

SEE AN EXAMPLE
Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.
Subscribe

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}