SHOW ENGINE engine_name {STATUS | MUTEX}
SHOW ENGINE displays operational
information about a storage engine. The following statements
currently are supported:
SHOW ENGINE INNODB STATUS SHOW ENGINE INNODB MUTEX SHOW ENGINE PERFORMANCE_SCHEMA STATUS
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 13.6.14.2, “SHOW ENGINE INNODB
STATUS and the InnoDB Monitors”.
SHOW ENGINE INNODB
MUTEX displays InnoDB mutex
statistics. The statement displays the following fields:
TypeAlways
InnoDB.NameThe source file where the mutex is implemented, and the line number in the file where the mutex is created. The line number may change depending on your version of MySQL.
StatusThe mutex status. This field displays several values if
UNIV_DEBUGwas defined at MySQL compilation time (for example, ininclude/univ.hin theInnoDBpart of the MySQL source tree). IfUNIV_DEBUGwas not defined, the statement displays only theos_waitsvalue. In the latter case (without UNIV_DEBUG), the information on which the output is based is insufficient to distinguish regular mutexes and mutexes that protect rw-locks (which permit multiple readers or a single writer). Consequently, the output may appear to contain multiple rows for the same mutex.countindicates how many times the mutex was requested.spin_waitsindicates how many times the spinlock had to run.spin_roundsindicates the number of spinlock rounds. (spin_roundsdivided byspin_waitsprovides the average round count.)os_waitsindicates the number of operating system waits. This occurs when the spinlock did not work (the mutex was not locked during the spinlock and it was necessary to yield to the operating system and wait).os_yieldsindicates the number of times a the thread trying to lock a mutex gave up its timeslice and yielded to the operating system (on the presumption that permitting other threads to run will free the mutex so that it can be locked).os_wait_timesindicates the amount of time (in ms) spent in operating system waits, if thetimed_mutexessystem variable is 1 (ON). Iftimed_mutexesis 0 (OFF), timing is disabled, soos_wait_timesis 0.timed_mutexesis off by default.
Information from this statement can be used to diagnose system
problems. For example, large values of
spin_waits and spin_rounds
may indicate scalability problems.
Use SHOW ENGINE
PERFORMANCE_SCHEMA STATUS to inspect the internal
operation of the Performance Schema code:
mysql> SHOW ENGINE PERFORMANCE_SCHEMA STATUS\G
...
*************************** 3. row ***************************
Type: performance_schema
Name: events_waits_history.row_size
Status: 76
*************************** 4. row ***************************
Type: performance_schema
Name: events_waits_history.row_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
...
The intent of this statement is to help the DBA to understand the effects that different options have on memory requirements.
Name values consist of two parts, which name
an internal buffer and an attribute of the buffer, respectively:
Internal buffers that are exposed as a table in the
performance_schemaare named after the table. Examples:events_waits_history.row_size,mutex_instances.row_count.Internal buffers that are not exposed as a table are named within parentheses. Examples:
(pfs_cond_class).row_size,(pfs_mutex_class).memory.Values that apply to Performance Schema as a whole begin with
performance_schema. Example:performance_schema.memory.
Attributes have these meanings:
row_sizecannot be changed. It is the size of the internal record used by the implementation.row_countcan be changed depending on the configuration options.For a table,
tbl_name.memoryrow_sizemultiplied byrow_count. For Performance Schema as a whole,performance_schema.memoryis the sum of all the memory used (the sum of all othermemoryvalues).
In some cases, there is a direct relationship between a
configuration parameter and a SHOW ENGINE
value. For example,
events_waits_history_long.row_count
corresponds to
performance_schema_events_waits_history_long_size.
In other cases, the relationship is more complex. For example,
events_waits_history.row_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).