8.14 Examining Thread Information

When you are attempting to ascertain what your MySQL server is doing, it can be helpful to examine the process list, which is the set of threads currently executing within the server. Process list information is available from these sources:

The SHOW [FULL] PROCESSLIST statement: Section 13.7.6.29, “SHOW PROCESSLIST Syntax”
The SHOW PROFILE statement: Section 13.7.6.31, “SHOW PROFILES Syntax”
The INFORMATION_SCHEMA PROCESSLIST table: Section 25.19, “The INFORMATION_SCHEMA PROCESSLIST Table”
The mysqladmin processlist command: Section 4.5.2, “mysqladmin — Client for Administering a MySQL Server”
The Performance Schema threads table, stage tables, and lock tables: Section 26.12.17, “Performance Schema Miscellaneous Tables”, Section 26.12.5, “Performance Schema Stage Event Tables”, Section 26.12.12, “Performance Schema Lock Tables”.

Access to threads does not require a mutex and has minimal impact on server performance. INFORMATION_SCHEMA.PROCESSLIST and SHOW PROCESSLIST have negative performance consequences because they require a mutex. threads also shows information about background threads, which INFORMATION_SCHEMA.PROCESSLIST and SHOW PROCESSLIST do not. This means that threads can be used to monitor activity the other thread information sources cannot.

You can always view information about your own threads. To view information about threads being executed for other accounts, you must have the PROCESS privilege.

Each process list entry contains several pieces of information:

Id is the connection identifier for the client associated with the thread.
User and Host indicate the account associated with the thread.
db is the default database for the thread, or NULL if none is selected.
Command and State indicate what the thread is doing.
Most states correspond to very quick operations. If a thread stays in a given state for many seconds, there might be a problem that needs to be investigated.
Time indicates how long the thread has been in its current state. The thread's notion of the current time may be altered in some cases: The thread can change the time with SET TIMESTAMP = value. For a thread running on a slave that is processing events from the master, the thread time is set to the time found in the events and thus reflects current time on the master and not the slave.
Info contains the text of the statement being executed by the thread, or NULL if it is not executing one. By default, this value contains only the first 100 characters of the statement. To see the complete statements, use SHOW FULL PROCESSLIST.
The sys schema processlist view, which presents information from the Performance Schema threads table in a more accessible format: Section 27.4.3.22, “The processlist and x$processlist Views”
The sys schema session view, which presents information about user sessions (like the sys schema processlist view, but with background processes filtered out): Section 27.4.3.33, “The session and x$session Views”

The following sections list the possible Command values, and State values grouped by category. The meaning for some of these values is self-evident. For others, additional description is provided.

Rechercher dans le manuel MySQL

Traduction non disponible

Le manuel MySQL n'est pas encore traduit en français sur l'infobrol. Seule la version anglaise est disponible pour l'instant.

Document créé le 26/06/2006, dernière modification le 26/10/2018
Source du document imprimé : https://www.gaudry.be/mysql-rf-thread-information.html

L'infobrol est un site personnel dont le contenu n'engage que moi. Le texte est mis à disposition sous licence CreativeCommons(BY-NC-SA). Plus d'info sur les conditions d'utilisation et sur l'auteur.

Références

Manuel MySQL : https://dev.mysql.com/

Ces références et liens indiquent des documents consultés lors de la rédaction de cette page, ou qui peuvent apporter un complément d'information, mais les auteurs de ces sources ne peuvent être tenus responsables du contenu de cette page.
L'auteur de ce site est seul responsable de la manière dont sont présentés ici les différents concepts, et des libertés qui sont prises avec les ouvrages de référence. N'oubliez pas que vous devez croiser les informations de sources multiples afin de diminuer les risques d'erreurs.

Table des matières Haut