Posted on by morgo

A followup on SHOW ENGINE INNODB MUTEX

EDIT: This post has been updated here. SHOW ENGINE INNODB MUTEX will return in MySQL 5.7.8.

In November, I wrote about the command SHOW ENGINE INNODB MUTEX and sought feedback as to how it continues to be used post introduction of performance_schema.
The outcome from this feedback is that the MySQL team has decided to remove this command from MySQL 5.7.
These decisions are not light ones for the team to make. I wanted to thank everyone for their feedback, and also share why this decision was an important one for the future of MySQL:

  1. By design, data collection for SHOW ENGINE INNODB MUTEX was always enabled, with no switch to disable it. We believe that for some workloads being able to disable the collection is useful, and in the case of performance_schema this is possible.
  2. performance_schema overlaps with this functionality but provides a superset of features, including timing information. The noted exception to this is that spinning information is not yet available in performance_schema.
  3. Comparable information to SHOW ENGINE INNODB MUTEX can be re-generated by creating views on performance_schema tables, offering a migration path to users affected and those desiring an interface that requires fewer key-strokes.
  4. The InnoDB mutex code was recently refactored as part of our server cleanup. After refactoring, the InnoDB code can now mix several mutex types internally including spin locks, system mutexes (POSIX) and InnoDB home brewed ones. The display output for SHOW ENGINE INNODB MUTEX doesn’t really account for these differences in its status column. Grouping all mutex types together becomes less intuitive, and would require either some change to the output, or a coarse (inaccurate) mixing of the types:
mysql> show engine innodb mutex;
+--------+----------------------------+-------------------+
| Type   | Name                       | Status            |
+--------+----------------------------+-------------------+
| InnoDB | log/log0log.c:775          | os_waits=26       |
| InnoDB | log/log0log.c:771          | os_waits=1        |
| InnoDB | buf/buf0buf.c:1208         | os_waits=3219     |
..