- 7.5 Optimizing the MySQL Server
- 7.5.1 How Compiling and Linking Affects the Speed of MySQL
- 7.5.2 System Factors and Startup Parameter Tuning
- 7.5.3 Tuning Server Parameters
- 7.5.4 Controlling Query Optimizer Performance
- 7.5.5 The MySQL Query Cache
- 7.5.6 Examining Thread Information
- 7.5.6.1 Thread Command Values
- 7.5.6.2 General Thread States
- 7.5.6.3 Delayed-Insert Thread States
- 7.5.6.4 Query Cache Thread States
- 7.5.6.5 Replication Master Thread States
- 7.5.6.6 Replication Slave I/O Thread States
- 7.5.6.7 Replication Slave SQL Thread States
- 7.5.6.8 Replication Slave Connection Thread States
- 7.5.6.9 MySQL Cluster Thread States
- 7.5.6.10 Event Scheduler Thread States
- 7.5.7 How MySQL Uses Threads for Client Connections
- 7.5.8 How MySQL Uses Memory
- 7.5.9 Enabling Large Page Support
- 7.5.10 How MySQL Uses Internal Temporary Tables
- 7.5.11 How MySQL Uses DNS
[+/-]
- 7.5.6.1. Thread Command Values
- 7.5.6.2. General Thread States
- 7.5.6.3. Delayed-Insert Thread States
- 7.5.6.4. Query Cache Thread States
- 7.5.6.5. Replication Master Thread States
- 7.5.6.6. Replication Slave I/O Thread States
- 7.5.6.7. Replication Slave SQL Thread States
- 7.5.6.8. Replication Slave Connection Thread States
- 7.5.6.9. MySQL Cluster Thread States
- 7.5.6.10. Event Scheduler Thread States
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] PROCESSLISTstatement: Section 12.4.5.31, “SHOW PROCESSLISTSyntax”The
SHOW PROFILEstatement: Section 12.4.5.33, “SHOW PROFILESSyntax”The
INFORMATION_SCHEMAPROCESSLISTtable: Section 20.22, “TheINFORMATION_SCHEMA PROCESSLISTTable”The mysqladmin processlist command: Section 4.5.2, “mysqladmin — Client for Administering a MySQL Server”
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:
Idis the connection identifier for the client associated with the thread.UserandHostindicate the account associated with the thread.dbis the default database for the thread, orNULLif none is selected.-
CommandandStateindicate 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.
Timeindicates 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 withSET TIMESTAMP =. 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.valueInfocontains the text of the statement being executed by the thread, orNULLif it is not executing one. By default, this value contains only the first 100 characters of the statement. To see the complete statements, useSHOW FULL PROCESSLIST.
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.
User Comments
Add your own comment.