Skip navigation links
12.4.5.10 SHOW ENGINES Syntax »
Section Navigation      [Toggle]

12.4.5.9. SHOW ENGINE Syntax

SHOW ENGINE engine_name {LOGS | STATUS }

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

SHOW ENGINE BDB LOGS
SHOW ENGINE INNODB STATUS
SHOW ENGINE NDB STATUS
SHOW ENGINE NDBCLUSTER STATUS

SHOW ENGINE BDB LOGS displays status information about existing BDB log files. It returns the following fields:

  • File

    The full path to the log file.

  • Type

    The log file type (BDB for Berkeley DB log files).

  • Status

    The status of the log file (FREE if the file can be removed, or IN USE if the file is needed by the transaction subsystem)

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.2.14.2, “SHOW ENGINE INNODB STATUS and the InnoDB Monitors”.

Older (and now deprecated) synonyms for these statements are SHOW [BDB] LOGS and SHOW INNODB STATUS.

SHOW ENGINE can be used as of MySQL 4.1.2.

Beginning with MySQL 4.1.3, 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 NDB STATUS;
+-----------------------+---------+------+--------+
| free_list             | created | free | sizeof |
+-----------------------+---------+------+--------+
| NdbTransaction        |       5 |    0 |    208 |
| NdbOperation          |       4 |    4 |    660 |
| NdbIndexScanOperation |       1 |    1 |    736 |
| NdbIndexOperation     |       0 |    0 |   1060 |
| NdbRecAttr            |     645 |  645 |     72 |
| NdbApiSignal          |      16 |   16 |    136 |
| NdbLabel              |       0 |    0 |    196 |
| NdbBranch             |       0 |    0 |     24 |
| NdbSubroutine         |       0 |    0 |     68 |
| NdbCall               |       0 |    0 |     16 |
| NdbBlob               |       2 |    2 |    204 |
| NdbReceiver           |       2 |    0 |     68 |
+-----------------------+---------+------+--------+
12 rows in set (0.00 sec)

The most useful of the rows from the output of this statement are described in the following list:

  • 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 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.

MySQL Enterprise.  The SHOW ENGINE engine_name STATUS statement provides valuable information about the state of your server. For expert interpretation of this information, subscribe to the MySQL Enterprise Monitor. For more information, see http://www.mysql.com/products/enterprise/advisors.html.