Additional languages
« 13.5.4.11 SHOW DATABASES Syntax
13.5.4.13 SHOW ENGINES Syntax »
Section Navigation      [Toggle]

13.5.4.12. SHOW ENGINE Syntax

SHOW ENGINE engine_name {LOGS | STATUS | MUTEX}

SHOW ENGINE displays log or status information about a storage engine. The following statements currently are supported: 

SHOW ENGINE INNODB STATUS
SHOW ENGINE INNODB MUTEX
SHOW ENGINE NDB STATUS
SHOW ENGINE NDBCLUSTER STATUS

Older (and now deprecated) synonyms for SHOW ENGINE INNODB STATUS and SHOW ENGINE INNODB MUTEX are SHOW INNODB STATUS and SHOW MUTEX STATUS.

SHOW ENGINE INNODB STATUS displays extensive information about the state of the InnoDB storage engine.

The InnoDB Monitors provide additional information about InnoDB processing. See Section 14.5.11.1, “SHOW ENGINE INNODB STATUS and the InnoDB Monitors”.

SHOW ENGINE INNODB MUTEX displays InnoDB mutex statistics. The output fields are:

  • Type

    Always InnoDB.

  • Name

    The mutex name and the source file where it is implemented. Example: &pool->mutex:mem0pool.c

    The mutex name indicates its purpose. For example, the log_sys mutex is used by the InnoDB logging subsystem and indicates how intensive logging activity is. The buf_pool mutex protects the InnoDB buffer pool.

  • Status

    The mutex status. The fields contains several values:

    • 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 by spin_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 the thread trying to lock a mutex gave up its timeslice and yielded to the operating system (on the presumption that allowing 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, if the timed_mutexes system variable is 1 (ON). If timed_mutexes is 0 (OFF), timing is disabled, so os_wait_times is 0. timed_mutexes is 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.

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 binlog epochs, as well as counts of various Cluster API objects created by the MySQL Server when connected to the cluster.

If the server has the NDBCLUSTER storage engine enabled, SHOW ENGINE NDB STATUS can be used to display cluster status information. Sample output from this statement is shown here: 

mysql> SHOW ENGINE NDBCLUSTER 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                                                         |
+------------+-----------------------+--------------------------------------------------+
14 rows in set (0.00 sec)

Two rows, with the Name values connection and binlog, 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:

Name Value
cluster_node_id The node ID of the MySQL server in the cluster
connected_host The hostname 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 active connections 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:

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 binlog thread
latest_handled_binlog_epoch The most recent epoch processed by the binlog thread (for writing to the binlog)
latest_applied_binlog_epoch The most recent epoch actually written to the binlog

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

  • NdbTransaction: The number and size of NdbTransaction objects that have been created. An NdbTransaction is created each time a table schema operation (such as CREATE TABLE or ALTER TABLE) is performed on an NDB table.

  • NdbOperation: The number and size of NdbOperation objects that have been created.

  • NdbIndexScanOperation: The number and size of NdbIndexScanOperation objects that have been created.

  • NdbIndexOperation: The number and size of NdbIndexOperation objects that have been created.

  • NdbRecAttr: The number and size of NdbRecAttr 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 of NdbBlob objects that have been created. An NdbBlob is created for each new operation involving a BLOB column in an NDB table.

  • NdbReceiver: The number and size of any NdbReceiver object that have been created. The number in the created column is the same as the number of data nodes in the cluster to which the MySQL server has connected.

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.

SHOW ENGINE NDBCLUSTER STATUS is a synonym for SHOW ENGINE NDB STATUS.

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 no longer supported, and this statement produces a warning.