Syntax:
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 {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.

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
http://dev.mysql.com/doc/refman/5.1/en/innodb-monitors.html.

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

o Type

  Always InnoDB.

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

o Status

  The mutex status. The fields contains several values:

  o count indicates how many times the mutex was requested.

  o spin_waits indicates how many times the spinlock had to run.

  o spin_rounds indicates the number of spinlock rounds. (spin_rounds
    divided by spin_waits provides the average round count.)

  o 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).

  o 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).

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

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

o Type

  Always InnoDB.

o Name

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

o Status

  This field displays the same values as previously described (count,
  spin_waits, spin_rounds, os_waits, os_yields, os_wait_times), but
  only if UNIV_DEBUG was defined at MySQL compilation time (for
  example, in include/univ.h in the InnoDB part of the MySQL source
  tree). If UNIV_DEBUG was not defined, the statement displays only the
  os_waits value. 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
  allow multiple readers or a single writer). Consequently, the output
  may appear to contain multiple rows for the same mutex.

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

URL: http://dev.mysql.com/doc/refman/5.1/en/show-engine.html

