13.7.5.15 SHOW ENGINE Statement
SHOW ENGINE engine_name {STATUS | MUTEX}
SHOW ENGINE
displays operational information about a storage engine. It requires the PROCESS
privilege. The statement has these variants:
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 14.18, “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 14.17.2, “Monitoring InnoDB Mutex Waits Using Performance Schema”.
SHOW ENGINE INNODB MUTEX
output was removed in MySQL 5.7.2. It was revised and reintroduced in MySQL 5.7.8.
In MySQL 5.7.8, 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
Prior to MySQL 5.7.8, the
Name
field reports the source file where the mutex is implemented, and the line number in the file where the mutex is created. The line number is specific to your version of MySQL. As of MySQL 5.7.8, only the mutex name is reported. File name and line number are still reported for rwlocks.Status
The mutex status.
Prior to MySQL 5.7.8, the
Status
field displays several values ifWITH_DEBUG
was defined at MySQL compilation time. IfWITH_DEBUG
was not defined, the statement displays only theos_waits
value. In the latter case (withoutWITH_DEBUG
), the information on which the output is based is insufficient to distinguish regular mutexes and mutexes that protect rwlocks (which permit multiple readers or a single writer). Consequently, the output may appear to contain multiple rows for the same mutex. Pre-MySQL 5.7.8Status
field values include:count
indicates how many times the mutex was requested.spin_waits
indicates how many times the spinlock had to run.spin_rounds
indicates the number of spinlock rounds. (spin_rounds
divided byspin_waits
provides the average round count.)os_waits
indicates 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_yields
indicates the number of times a 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_times
indicates the amount of time (in ms) spent in operating system waits. In MySQL 5.7 timing is disabled and this value is always 0.
As of MySQL 5.7.8, the
Status
field reports the number of spins, waits, and calls. Statistics for low-level operating system mutexes, which are implemented outside ofInnoDB
, 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
does not list mutexes and rw-locks for each buffer pool block, as the amount of output would be overwhelming on systems with a large buffer pool. SHOW ENGINE INNODB MUTEX
does, however, print aggregate BUF_BLOCK_MUTEX
spin, wait, and call values for buffer pool block mutexes and rw-locks. 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:
mysql> SHOW ENGINE PERFORMANCE_SCHEMA STATUS\G
...
*************************** 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,
is the product oftbl_name
.memorysize
andcount
. For the Performance Schema as a whole,performance_schema.memory
is the sum of all the memory used (the sum of all othermemory
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).
SHOW ENGINE NDB STATUS. If the server has the NDB
storage engine enabled, SHOW ENGINE NDB STATUS
displays cluster status information such as the number of connected data nodes, the cluster connectstring, and cluster binary log epochs, as well as counts of various Cluster API objects created by the MySQL Server when connected to the cluster. Sample output from this statement is shown here:
mysql> SHOW ENGINE NDB STATUS;
+------------+-----------------------+--------------------------------------------------+
| Type | Name | Status |
+------------+-----------------------+--------------------------------------------------+
| ndbcluster | connection | cluster_node_id=7,
connected_host=198.51.100.103, connected_port=1186, number_of_data_nodes=4,
number_of_ready_data_nodes=3, connect_count=0 |
| ndbcluster | NdbTransaction | created=6, free=0, sizeof=212 |
| ndbcluster | NdbOperation | created=8, free=8, sizeof=660 |
| ndbcluster | NdbIndexScanOperation | created=1, free=1, sizeof=744 |
| ndbcluster | NdbIndexOperation | created=0, free=0, sizeof=664 |
| ndbcluster | NdbRecAttr | created=1285, free=1285, sizeof=60 |
| ndbcluster | NdbApiSignal | created=16, free=16, sizeof=136 |
| ndbcluster | NdbLabel | created=0, free=0, sizeof=196 |
| ndbcluster | NdbBranch | created=0, free=0, sizeof=24 |
| ndbcluster | NdbSubroutine | created=0, free=0, sizeof=68 |
| ndbcluster | NdbCall | created=0, free=0, sizeof=16 |
| ndbcluster | NdbBlob | created=1, free=1, sizeof=264 |
| ndbcluster | NdbReceiver | created=4, free=0, sizeof=68 |
| ndbcluster | binlog | latest_epoch=155467, latest_trans_epoch=148126,
latest_received_binlog_epoch=0, latest_handled_binlog_epoch=0,
latest_applied_binlog_epoch=0 |
+------------+-----------------------+--------------------------------------------------+
The Status
column in each of these rows provides information about the MySQL server's connection to the cluster and about the cluster binary log's status, respectively. The Status
information is in the form of comma-delimited set of name/value pairs.
The connection
row's Status
column contains the name/value pairs described in the following table.
Name | Value |
---|---|
cluster_node_id |
The node ID of the MySQL server in the cluster |
connected_host |
The host name or IP address of the cluster management server to which the MySQL server is connected |
connected_port |
The port used by the MySQL server to connect to the management server (connected_host ) |
number_of_data_nodes |
The number of data nodes configured for the cluster (that is, the number of [ndbd] sections in the cluster config.ini file) |
number_of_ready_data_nodes |
The number of data nodes in the cluster that are actually running |
connect_count |
The number of times this mysqld has connected or reconnected to cluster data nodes |
The binlog
row's Status
column contains information relating to NDB Cluster Replication. The name/value pairs it contains are described in the following table.
Name | Value |
---|---|
latest_epoch |
The most recent epoch most recently run on this MySQL server (that is, the sequence number of the most recent transaction run on the server) |
latest_trans_epoch |
The most recent epoch processed by the cluster's data nodes |
latest_received_binlog_epoch |
The most recent epoch received by the binary log thread |
latest_handled_binlog_epoch |
The most recent epoch processed by the binary log thread (for writing to the binary log) |
latest_applied_binlog_epoch |
The most recent epoch actually written to the binary log |
See Section 21.6, “NDB Cluster Replication”, for more information.
The remaining rows from the output of SHOW ENGINE NDB STATUS
which are most likely to prove useful in monitoring the cluster are listed here by Name
:
NdbTransaction
: The number and size ofNdbTransaction
objects that have been created. AnNdbTransaction
is created each time a table schema operation (such asCREATE TABLE
orALTER TABLE
) is performed on anNDB
table.NdbOperation
: The number and size ofNdbOperation
objects that have been created.NdbIndexScanOperation
: The number and size ofNdbIndexScanOperation
objects that have been created.NdbIndexOperation
: The number and size ofNdbIndexOperation
objects that have been created.NdbRecAttr
: The number and size ofNdbRecAttr
objects that have been created. In general, one of these is created each time a data manipulation statement is performed by an SQL node.NdbBlob
: The number and size ofNdbBlob
objects that have been created. AnNdbBlob
is created for each new operation involving aBLOB
column in anNDB
table.NdbReceiver
: The number and size of anyNdbReceiver
object that have been created. The number in thecreated
column is the same as the number of data nodes in the cluster to which the MySQL server has connected.
SHOW ENGINE NDB STATUS
returns an empty result if no operations involving NDB
tables have been performed during the current session by the MySQL client accessing the SQL node on which this statement is run.