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

SHOW ENGINE INNODB MUTEX displays InnoDB mutex and rw-lock statistics.

*Note*:

InnoDB mutexes and rwlocks can also be monitored using Performance
Schema tables. See
https://dev.mysql.com/doc/refman/8.0/en/monitor-innodb-mutex-waits-perf
ormance-schema.html.

Mutex statistics collection is configured dynamically using the
following options:

o To enable the collection of mutex statistics, run:

SET GLOBAL innodb_monitor_enable='latch';

o To reset mutex statistics, run:

SET GLOBAL innodb_monitor_reset='latch';

o To disable the collection of mutex statistics, run:

SET GLOBAL innodb_monitor_disable='latch';

Collection of mutex statistics for SHOW ENGINE INNODB MUTEX can also be
enabled by setting innodb_monitor_enable='all', or disabled by setting
innodb_monitor_disable='all'.

SHOW ENGINE INNODB MUTEX output has these columns:

o Type

Always InnoDB.

o Name

For mutexes, the Name field reports only the mutex name. For rwlocks,
the Name field reports the source file where the rwlock is
implemented, and the line number in the file where the rwlock is
created. The line number is specific to your version of MySQL.

o Status

The mutex status. This field reports the number of spins, waits, and
calls. Statistics for low-level operating system mutexes, which are
implemented outside of InnoDB, are not reported.

o spins indicates the number of spins.

o waits indicates the number of mutex waits.

o calls indicates how many times the mutex was requested.

SHOW ENGINE INNODB MUTEX does not list mutexes and rw-locks for each
buffer pool block, as the amount of output would be overwhelming on
systems with a large buffer pool. SHOW ENGINE INNODB MUTEX does,
however, print aggregate BUF_BLOCK_MUTEX spin, wait, and call values
for buffer pool block mutexes and rw-locks. SHOW ENGINE INNODB MUTEX
also does not list any mutexes or rw-locks that have never been waited
on (os_waits=0). Thus, SHOW ENGINE INNODB MUTEX only displays
information about mutexes and rw-locks outside of the buffer pool that
have caused at least one OS-level wait.

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.size
Status: 76
*************************** 4. row ***************************
Type: performance_schema
Name: events_waits_history.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
...

This statement is intended to help the DBA understand the effects that
different Performance Schema options have on memory requirements.

Name values consist of two parts, which name an internal buffer and a
buffer attribute, respectively. Interpret buffer names as follows:

o An internal buffer that is not exposed as a table is named within
parentheses. Examples: (pfs_cond_class).size,
(pfs_mutex_class).memory.

o An internal buffer that is exposed as a table in the
performance_schema database is named after the table, without
parentheses. Examples: events_waits_history.size,
mutex_instances.count.

o A value that applies to the Performance Schema as a whole begins with
performance_schema. Example: performance_schema.memory.

Buffer attributes have these meanings:

o size is the size of the internal record used by the implementation,
such as the size of a row in a table. size values cannot be changed.

o count is the number of internal records, such as the number of rows
in a table. count values can be changed using Performance Schema
configuration options.

o For a table, tbl_name.memory is the product of size and count. For
the Performance Schema as a whole, performance_schema.memory is the
sum of all the memory used (the sum of all other memory values).

In some cases, there is a direct relationship between a Performance
Schema configuration parameter and a SHOW ENGINE value. For example,
events_waits_history_long.count corresponds to
performance_schema_events_waits_history_long_size. In other cases, the
relationship is more complex. For example, events_waits_history.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).

SHOW ENGINE NDB STATUS If the server has the NDB 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=198.51.100.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 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: https://dev.mysql.com/doc/refman/8.0/en/show-engine.html