Getting mutex information from MySQL’s performance_schema

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:

Then, just compare the results from an idle Percona Server 5.6. First the output of SHOW ENGINE…

Now the results from the query that get us the mutex information from performance_schema:

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.

Share this post

Comments (3)

  • zhaogongpo Reply

    good job

    January 6, 2015 at 9:39 pm
  • p4tt4nz Reply

    >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?

    January 12, 2015 at 8:11 am
  • DarkKnightCZ Reply

    @P4tt4nz: all values in performance schema should be in picoseconds, so this should be correct

    February 10, 2015 at 3:23 am

Leave a Reply