13.7.5.16 SHOW ENGINE Syntax

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 {NDB | NDBCLUSTER} STATUS

Older (and now deprecated) synonyms are SHOW INNODB STATUS for SHOW ENGINE INNODB STATUS and SHOW MUTEX STATUS for SHOW ENGINE INNODB MUTEX. SHOW INNODB STATUS and SHOW MUTEX STATUS are removed in MySQL 5.5.

In MySQL 5.0, SHOW ENGINE INNODB MUTEX is invoked as SHOW MUTEX STATUS. The latter statement displays similar information but in a somewhat different output format.

SHOW ENGINE BDB LOGS formerly displayed status information about BDB log files. As of MySQL 5.1.12, the BDB storage engine is not supported, and this statement produces a warning.

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.6.9, “InnoDB Monitors”.

SHOW ENGINE INNODB MUTEX displays InnoDB mutex statistics. From MySQL 5.1.2 to 5.1.14, the statement displays the following output fields:

From MySQL 5.1.15 on, the statement displays the following output fields:

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.

If the server has the NDBCLUSTER 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=192.168.0.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 rows with connection and binlog in the Name column were added to the output of this statement in MySQL 5.1. 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.

NameValue
cluster_node_idThe node ID of the MySQL server in the cluster
connected_hostThe host name or IP address of the cluster management server to which the MySQL server is connected
connected_portThe port used by the MySQL server to connect to the management server (connected_host)
number_of_data_nodesThe 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_nodesThe number of data nodes in the cluster that are actually running
connect_countThe number of times this mysqld has connected or reconnected to cluster data nodes

The binlog row's Status column contains information relating to MySQL Cluster Replication. The name/value pairs it contains are described in the following table.

NameValue
latest_epochThe 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_epochThe most recent epoch processed by the cluster's data nodes
latest_received_binlog_epochThe most recent epoch received by the binary log thread
latest_handled_binlog_epochThe most recent epoch processed by the binary log thread (for writing to the binary log)
latest_applied_binlog_epochThe most recent epoch actually written to the binary log

See Section 17.6, “MySQL 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:

Note

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.