Help shape the future of SHOW ENGINE INNODB MUTEX

Before there was PERFORMANCE_SCHEMA, there was SHOW ENGINE INNODB MUTEX. As I wrote yesterday, these commands are useful for exposing internal locking operations that happen inside of MySQL. They are mainly useful for MySQL developers, but have some appeal for DBAs as well.

As part of an ongoing effort to cleanup the MySQL code-base and increase usability, the MySQL development team is interested in hearing feedback from users who currently use the command SHOW ENGINE INNODB MUTEX.

Specifically:

  • Is there a use case for the command that is not well satisfied by Performance Schema?
  • Would you be adversely affected if SHOW ENGINE INNODB MUTEX were to be deprecated?
  • What tools do you use to analyze mutex contention, and what features could we add to benefit you?

Please leave a comment, or get in touch. We would love to hear from you.

Published by

morgo

I joined MySQL AB in 2006, left, and am now back at Oracle working on the MySQL team. I’ve also worked at Percona and InPowered.

  • Valerii Kravchuk

    See http://bugs.mysql.com/bug.php?id=68079 on how it is used and try to explain how you can get notably more/better information with PERFORMANCE_SCHEMA. Then count number of keystrokes/steps etc with PERFORMANCE_SCHEMA and make your own conclusions.

  • Valerii Kravchuk

    To put it simple: leave it and re-implement/improve based on P_S if needed. For many years ahead there will be more good DBAs who know how to use this output better and faster than equivalent P_S queries, especially if you will NOT make them a part of the product itself (with ps_helper schema or something else).

  • Mark Callaghan

    SHOW ENGINE INNODB MUTEX
    1) trivial to use, no user manual needed to configure
    2) much less overhead than PS
    3) given less overhead, it can always be enabled.
    4) PS for mutexes must be enabled at mysqld start. Who wants to restart a production server?
    5) Dimitri almost always disables PS for benchmarks. Why?
    6) Don’t need helper stored procedures to use SHOW. Hint – if extensive manual and helper stored procedures are required maybe you are doing it wrong.

  • J Galbraith

    To get a quick overview of the top sources of contention on a running server I’ll issue the following from the command line:

    mysql -p -e ‘show engine innodb mutex;’ | sed ‘s/os_waits=//’ | awk ‘{print $2,$3;}’ | sort -rnk 2 | head

    TBH, I’ve avoided using PS due to the overhead and complexity to configure though I know it may be a much more powerful tool.

  • Eric Bergen

    The problem with PS is that it has become a dumping ground for all things status in mysql. It seems like there is little to no thought in to deciding if things should go into IS or PS. I get the feeling in 5.7 and beyond we will be forced to run PS because everything will live there. Example slave status:
    http://dev.mysql.com/doc/refman/5.7/en/performance-schema-replication-tables.html

  • petermclarty

    Having spent around 14 years doing Oracle DBA work, I see the similarities in PS to the views (queue Mark Leith’s work) in an Oracle database that DBA’s so rely on for understanding the inner workings of that database. For those that have never been part of the Oracle DBA community then I suggest you read Mark Leiths work and implement the sys schema. Quality real time views of activities through PS will provide much better understanding of the workings of the databases workings and you will be able to graph the mutexes and latches and so many buffer use parameters that your ability to tune will rise dramatically. To ultimately have features similar and as usable to Oracle SQL trace function and Optimizer Trace function will be great so my feelings are press ahead with PS and make sure its not a burden. Benchmarks will untimately be done with it on as it will hopefully become so darn useful as part of benchmarking that it would be silly to turn it off. The days of out hardwaring some problems with a big buffer cache will hopefully start to die with improved PS data.
    I would say if SHOW ENGINE INNODB MUTEX stays base it off PS data and allow the underlying data be available to tools like Percona, NewRelic, Monyog and Enterprise Manager to draw graphs that provide longer visual representations of usage of these mutexes