Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 38.2Mb
PDF (A4) - 38.3Mb
PDF (RPM) - 33.1Mb
HTML Download (TGZ) - 8.1Mb
HTML Download (Zip) - 8.1Mb
HTML Download (RPM) - 7.0Mb
Man Pages (TGZ) - 133.2Kb
Man Pages (Zip) - 189.3Kb
Info (Gzip) - 3.4Mb
Info (Zip) - 3.4Mb
Excerpts from this Manual SHOW ENGINE Syntax


SHOW ENGINE displays operational information about a storage engine. It requires the PROCESS privilege. The statement has these variants:


SHOW ENGINE INNODB STATUS displays extensive information from the standard InnoDB Monitor about the state of the InnoDB storage engine. For information about the standard monitor and other InnoDB Monitors that provide information about InnoDB processing, see Section 15.16, “InnoDB Monitors”.

SHOW ENGINE INNODB MUTEX displays InnoDB mutex and rw-lock statistics.


InnoDB mutexes and rwlocks can also be monitored using Performance Schema tables. See Section 15.15.2, “Monitoring InnoDB Mutex Waits Using Performance Schema”.

Mutex statistics collection is configured dynamically using the following options:

  • To enable the collection of mutex statistics, run:

    SET GLOBAL innodb_monitor_enable='latch';
  • To reset mutex statistics, run:

    SET GLOBAL innodb_monitor_reset='latch';
  • To disable the collection of mutex statistics, run:

    SET GLOBAL innodb_monitor_disable='latch';

Collection of mutex statistics for SHOW ENGINE INNODB MUTEX can also be enabled by setting innodb_monitor_enable='all', or disabled by setting innodb_monitor_disable='all'.

SHOW ENGINE INNODB MUTEX output has these columns:

  • Type

    Always InnoDB.

  • Name

    For mutexes, the Name field reports only the mutex name. For rwlocks, the Name field reports the source file where the rwlock is implemented, and the line number in the file where the rwlock is created. The line number is specific to your version of MySQL.

  • Status

    The mutex status. This field reports the number of spins, waits, and calls. Statistics for low-level operating system mutexes, which are implemented outside of InnoDB, are not reported.

    • spins indicates the number of spins.

    • waits indicates the number of mutex waits.

    • calls indicates how many times the mutex was requested.

SHOW ENGINE INNODB MUTEX skips the mutexes and rw-locks of buffer pool blocks, as the amount of output can be overwhelming on systems with a large buffer pool. (There is one mutex and one rw-lock in each 16K buffer pool block, and there are 65,536 blocks per gigabyte.) SHOW ENGINE INNODB MUTEX also does not list any mutexes or rw-locks that have never been waited on (os_waits=0). Thus, SHOW ENGINE INNODB MUTEX only displays information about mutexes and rw-locks outside of the buffer pool that have caused at least one OS-level wait.

Use SHOW ENGINE PERFORMANCE_SCHEMA STATUS to inspect the internal operation of the Performance Schema code:

*************************** 3. row ***************************
  Type: performance_schema
  Name: events_waits_history.size
Status: 76
*************************** 4. row ***************************
  Type: performance_schema
  Name: events_waits_history.count
Status: 10000
*************************** 5. row ***************************
  Type: performance_schema
  Name: events_waits_history.memory
Status: 760000
*************************** 57. row ***************************
  Type: performance_schema
  Name: performance_schema.memory
Status: 26459600

This statement is intended to help the DBA understand the effects that different Performance Schema options have on memory requirements.

Name values consist of two parts, which name an internal buffer and a buffer attribute, respectively. Interpret buffer names as follows:

  • An internal buffer that is not exposed as a table is named within parentheses. Examples: (pfs_cond_class).size, (pfs_mutex_class).memory.

  • An internal buffer that is exposed as a table in the performance_schema database is named after the table, without parentheses. Examples: events_waits_history.size, mutex_instances.count.

  • A value that applies to the Performance Schema as a whole begins with performance_schema. Example: performance_schema.memory.

Buffer attributes have these meanings:

  • size is the size of the internal record used by the implementation, such as the size of a row in a table. size values cannot be changed.

  • count is the number of internal records, such as the number of rows in a table. count values can be changed using Performance Schema configuration options.

  • For a table, tbl_name.memory is the product of size and count. For the Performance Schema as a whole, performance_schema.memory is the sum of all the memory used (the sum of all other memory values).

In some cases, there is a direct relationship between a Performance Schema configuration parameter and a SHOW ENGINE value. For example, events_waits_history_long.count corresponds to performance_schema_events_waits_history_long_size. In other cases, the relationship is more complex. For example, events_waits_history.count corresponds to performance_schema_events_waits_history_size (the number of rows per thread) multiplied by performance_schema_max_thread_instances ( the number of threads).

User Comments
User comments in this section are, as the name implies, provided by MySQL users. The MySQL documentation team is not responsible for, nor do they endorse, any of the information provided here.
Sign Up Login You must be logged in to post a comment.