Rechercher dans le manuel MySQL

13.7.6.15 SHOW ENGINE Syntax

Inhaltsverzeichnis
  1. 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:

  1. SHOW ENGINE INNODB STATUS
  2. SHOW ENGINE INNODB MUTEX
  3. 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 15.16, “InnoDB Monitors”.

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 Section 15.15.2, “Monitoring InnoDB Mutex Waits Using Performance Schema”.

Mutex statistics collection is configured dynamically using the following options:

  • To enable the collection of mutex statistics, run:

    1. SET GLOBAL innodb_monitor_enable='latch';

  • To reset mutex statistics, run:

    1. SET GLOBAL innodb_monitor_reset='latch';

  • To disable the collection of mutex statistics, run:

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

  • Type

    Always InnoDB.

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

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

    • spins indicates the number of spins.

    • waits indicates the number of mutex waits.

    • calls indicates how many times the mutex was requested.

SHOW ENGINE INNODB MUTEX skips the mutexes and rw-locks of buffer pool blocks, as the amount of output can be overwhelming on systems with a large buffer pool. (There is one mutex and one rw-lock in each 16K buffer pool block, and there are 65,536 blocks per gigabyte.) 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:

  1. mysql> SHOW ENGINE PERFORMANCE_SCHEMA STATUS\G
  2. ...
  3. *************************** 3. row ***************************
  4.   Type: performance_schema
  5.   Name: events_waits_history.size
  6. Status: 76
  7. *************************** 4. row ***************************
  8.   Type: performance_schema
  9.   Name: events_waits_history.count
  10. Status: 10000
  11. *************************** 5. row ***************************
  12.   Type: performance_schema
  13.   Name: events_waits_history.memory
  14. Status: 760000
  15. ...
  16. *************************** 57. row ***************************
  17.   Type: performance_schema
  18.   Name: performance_schema.memory
  19. Status: 26459600
  20. ...

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:

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

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

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

Buffer attributes have these meanings:

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

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

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

  1. mysql> SHOW ENGINE NDB STATUS;
  2. +------------+-----------------------+--------------------------------------------------+
  3. | Type       | Name                  | Status                                           |
  4. +------------+-----------------------+--------------------------------------------------+
  5. | ndbcluster | connection            | cluster_node_id=7,
  6.   connected_host=198.51.100.103, connected_port=1186, number_of_data_nodes=4,
  7.   number_of_ready_data_nodes=3, connect_count=0                                         |
  8. | ndbcluster | NdbTransaction        | created=6, free=0, sizeof=212                    |
  9. | ndbcluster | NdbOperation          | created=8, free=8, sizeof=660                    |
  10. | ndbcluster | NdbIndexScanOperation | created=1, free=1, sizeof=744                    |
  11. | ndbcluster | NdbIndexOperation     | created=0, free=0, sizeof=664                    |
  12. | ndbcluster | NdbRecAttr            | created=1285, free=1285, sizeof=60               |
  13. | ndbcluster | NdbApiSignal          | created=16, free=16, sizeof=136                  |
  14. | ndbcluster | NdbLabel              | created=0, free=0, sizeof=196                    |
  15. | ndbcluster | NdbBranch             | created=0, free=0, sizeof=24                     |
  16. | ndbcluster | NdbSubroutine         | created=0, free=0, sizeof=68                     |
  17. | ndbcluster | NdbCall               | created=0, free=0, sizeof=16                     |
  18. | ndbcluster | NdbBlob               | created=1, free=1, sizeof=264                    |
  19. | ndbcluster | NdbReceiver           | created=4, free=0, sizeof=68                     |
  20. | ndbcluster | binlog                | latest_epoch=155467, latest_trans_epoch=148126,
  21.   latest_received_binlog_epoch=0, latest_handled_binlog_epoch=0,
  22.   latest_applied_binlog_epoch=0                                                         |
  23. +------------+-----------------------+--------------------------------------------------+

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

See Section 22.6, “NDB 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.


Suchen Sie im MySQL-Handbuch

Deutsche Übersetzung

Sie haben gebeten, diese Seite auf Deutsch zu besuchen. Momentan ist nur die Oberfläche übersetzt, aber noch nicht der gesamte Inhalt.

Wenn Sie mir bei Übersetzungen helfen wollen, ist Ihr Beitrag willkommen. Alles, was Sie tun müssen, ist, sich auf der Website zu registrieren und mir eine Nachricht zu schicken, in der Sie gebeten werden, Sie der Gruppe der Übersetzer hinzuzufügen, die Ihnen die Möglichkeit gibt, die gewünschten Seiten zu übersetzen. Ein Link am Ende jeder übersetzten Seite zeigt an, dass Sie der Übersetzer sind und einen Link zu Ihrem Profil haben.

Vielen Dank im Voraus.

Dokument erstellt 26/06/2006, zuletzt geändert 26/10/2018
Quelle des gedruckten Dokuments:https://www.gaudry.be/de/mysql-rf-show-engine.html

Die Infobro ist eine persönliche Seite, deren Inhalt in meiner alleinigen Verantwortung liegt. Der Text ist unter der CreativeCommons-Lizenz (BY-NC-SA) verfügbar. Weitere Informationen auf die Nutzungsbedingungen und dem Autor.

Referenzen

  1. Zeigen Sie - html-Dokument Sprache des Dokuments:en Manuel MySQL : https://dev.mysql.com/

Diese Verweise und Links verweisen auf Dokumente, die während des Schreibens dieser Seite konsultiert wurden, oder die zusätzliche Informationen liefern können, aber die Autoren dieser Quellen können nicht für den Inhalt dieser Seite verantwortlich gemacht werden.
Der Autor Diese Website ist allein dafür verantwortlich, wie die verschiedenen Konzepte und Freiheiten, die mit den Nachschlagewerken gemacht werden, hier dargestellt werden. Denken Sie daran, dass Sie mehrere Quellinformationen austauschen müssen, um das Risiko von Fehlern zu reduzieren.

Inhaltsverzeichnis Haut