- 12.7.5 SHOW Syntax
- 12.7.5.1 SHOW AUTHORS Syntax
- 12.7.5.2 SHOW BINARY LOGS Syntax
- 12.7.5.3 SHOW BINLOG EVENTS Syntax
- 12.7.5.4 SHOW CHARACTER SET Syntax
- 12.7.5.5 SHOW COLLATION Syntax
- 12.7.5.6 SHOW COLUMNS Syntax
- 12.7.5.7 SHOW CONTRIBUTORS Syntax
- 12.7.5.8 SHOW CREATE DATABASE Syntax
- 12.7.5.9 SHOW CREATE EVENT Syntax
- 12.7.5.10 SHOW CREATE FUNCTION Syntax
- 12.7.5.11 SHOW CREATE PROCEDURE Syntax
- 12.7.5.12 SHOW CREATE TABLE Syntax
- 12.7.5.13 SHOW CREATE TRIGGER Syntax
- 12.7.5.14 SHOW CREATE VIEW Syntax
- 12.7.5.15 SHOW DATABASES Syntax
- 12.7.5.16 SHOW ENGINE Syntax
- 12.7.5.17 SHOW ENGINES Syntax
- 12.7.5.18 SHOW ERRORS Syntax
- 12.7.5.19 SHOW EVENTS Syntax
- 12.7.5.20 SHOW FUNCTION CODE Syntax
- 12.7.5.21 SHOW FUNCTION STATUS Syntax
- 12.7.5.22 SHOW GRANTS Syntax
- 12.7.5.23 SHOW INDEX Syntax
- 12.7.5.24 SHOW MASTER STATUS Syntax
- 12.7.5.25 SHOW OPEN TABLES Syntax
- 12.7.5.26 SHOW PLUGINS Syntax
- 12.7.5.27 SHOW PRIVILEGES Syntax
- 12.7.5.28 SHOW PROCEDURE CODE Syntax
- 12.7.5.29 SHOW PROCEDURE STATUS Syntax
- 12.7.5.30 SHOW PROCESSLIST Syntax
- 12.7.5.31 SHOW PROFILE Syntax
- 12.7.5.32 SHOW PROFILES Syntax
- 12.7.5.33 SHOW RELAYLOG EVENTS Syntax
- 12.7.5.34 SHOW SLAVE HOSTS Syntax
- 12.7.5.35 SHOW SLAVE STATUS Syntax
- 12.7.5.36 SHOW STATUS Syntax
- 12.7.5.37 SHOW TABLE STATUS Syntax
- 12.7.5.38 SHOW TABLES Syntax
- 12.7.5.39 SHOW TRIGGERS Syntax
- 12.7.5.40 SHOW VARIABLES Syntax
- 12.7.5.41 SHOW WARNINGS 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
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 13.3.14.2, “SHOW ENGINE INNODB
STATUS and the InnoDB Monitors”.
SHOW ENGINE INNODB
MUTEX displays InnoDB mutex
statistics. The statement displays the following fields:
-
TypeAlways
InnoDB. -
NameThe 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.
-
StatusThe mutex status. This field displays several values if
UNIV_DEBUGwas defined at MySQL compilation time (for example, ininclude/univ.iin theInnoDBpart of the MySQL source tree). IfUNIV_DEBUGwas not defined, the statement displays only theos_waitsvalue. 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 permit multiple readers or a single writer). Consequently, the output may appear to contain multiple rows for the same mutex.countindicates how many times the mutex was requested.spin_waitsindicates how many times the spinlock had to run.spin_roundsindicates the number of spinlock rounds. (spin_roundsdivided byspin_waitsprovides the average round count.)os_waitsindicates 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_yieldsindicates 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 permitting other threads to run will free the mutex so that it can be locked).os_wait_timesindicates the amount of time (in ms) spent in operating system waits, if thetimed_mutexessystem variable is 1 (ON). Iftimed_mutexesis 0 (OFF), timing is disabled, soos_wait_timesis 0.timed_mutexesis 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.
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.row_size
Status: 76
*************************** 4. row ***************************
Type: performance_schema
Name: events_waits_history.row_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
...
The intent of this statement is to help the DBA to understand the effects that different options have on memory requirements.
Name values consist of two parts, which name
an internal buffer and an attribute of the buffer, respectively:
Internal buffers that are exposed as a table in the
performance_schemadatabase are named after the table. Examples:events_waits_history.row_size,mutex_instances.row_count.Internal buffers that are not exposed as a table are named within parentheses. Examples:
(pfs_cond_class).row_size,(pfs_mutex_class).memory.Values that apply to the Performance Schema as a whole begin with
performance_schema. Example:performance_schema.memory.
Attributes have these meanings:
row_sizecannot be changed. It is the size of the internal record used by the implementation.row_countcan be changed depending on the configuration options.For a table,
tbl_name.memoryrow_sizemultiplied byrow_count. For the Performance Schema as a whole,performance_schema.memoryis the sum of all the memory used (the sum of all othermemoryvalues).
In some cases, there is a direct relationship between a
configuration parameter and a SHOW ENGINE
value. For example,
events_waits_history_long.row_count
corresponds to
performance_schema_events_waits_history_long_size.
In other cases, the relationship is more complex. For example,
events_waits_history.row_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).
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.
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 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 16.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:
NdbTransaction: The number and size ofNdbTransactionobjects that have been created. AnNdbTransactionis created each time a table schema operation (such asCREATE TABLEorALTER TABLE) is performed on anNDBtable.NdbOperation: The number and size ofNdbOperationobjects that have been created.NdbIndexScanOperation: The number and size ofNdbIndexScanOperationobjects that have been created.NdbIndexOperation: The number and size ofNdbIndexOperationobjects that have been created.NdbRecAttr: The number and size ofNdbRecAttrobjects 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 ofNdbBlobobjects that have been created. AnNdbBlobis created for each new operation involving aBLOBcolumn in anNDBtable.NdbReceiver: The number and size of anyNdbReceiverobject that have been created. The number in thecreatedcolumn 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.
User Comments
Add your own comment.