MySQL SHOW PROCESSLIST

MySQL SHOW PROCESSLIST Statement

The SHOW PROCESSLIST statement in MySQL provides information about currently running threads (or processes) in the server. It is primarily used to monitor and debug MySQL performance issues by examining active queries and connections.

Syntax

SHOW [FULL] PROCESSLIST;

• FULL: Optional keyword to show the complete query for each process. Without FULL, queries longer than 100 characters are truncated.

Output Columns

When you execute SHOW PROCESSLIST, it returns the following columns:

Column	Description
Id	The connection ID of the thread.
User	The MySQL user associated with the thread.
Host	The host (IP address or hostname) and port of the client.
db	The default database for the thread (or NULL if no database is selected).
Command	The type of command the thread is executing (e.g., Sleep, Query, Connect).
Time	The duration (in seconds) the thread has been in its current state.
State	The current status of the thread, describing what it is doing.
Info	The query or operation being executed (may be truncated without FULL).

Examples

1. View All Active Threads

SHOW PROCESSLIST;

Sample Output:

+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host      | db   | Command | Time | State | Info             |
+----+------+-----------+------+---------+------+-------+------------------+
| 10 | root | localhost | test | Query   |    0 | NULL  | SELECT * FROM orders |
| 11 | root | localhost | NULL | Sleep   |   30 |       | NULL             |
+----+------+-----------+------+---------+------+-------+------------------+

2. View Complete Query Details

To see the full query for each thread, use the FULL keyword:

SHOW FULL PROCESSLIST;

Output:

+----+------+-----------+------+---------+------+-------+-------------------------------+
| Id | User | Host      | db   | Command | Time | State | Info                          |
+----+------+-----------+------+---------+------+-------+-------------------------------+
| 10 | root | localhost | test | Query   |    0 | NULL  | SELECT * FROM orders WHERE id = 1 |
+----+------+-----------+------+---------+------+-------+-------------------------------+

3. Use INFORMATION_SCHEMA.PROCESSLIST for Querying

The INFORMATION_SCHEMA.PROCESSLIST table provides the same information as SHOW PROCESSLIST, but allows for more advanced querying.

Example: Retrieve only running queries for a specific user:

SELECT Id, User, Host, db, Command, Time, State, Info
FROM INFORMATION_SCHEMA.PROCESSLIST
WHERE User = 'root';

Understanding Output Columns

• Command:

  • Sleep: The connection is idle and waiting for the client.
  • Query: A query is being executed.
  • Connect: The thread is waiting for a connection to complete.
  • Binlog Dump: A thread for binary log replication.

• State:

  • Describes what the thread is doing, such as:
    • Sending data: The server is sending data to the client.
    • Sorting result: The server is sorting data for the query.
    • Locked: The thread is waiting for a table lock.
    • Writing to net: The server is writing data to the network.

Managing Threads

1. Kill a Thread: Use the thread's Id from SHOW PROCESSLIST to terminate it:

   KILL thread_id;
   

   Example:

   KILL 10;
   

2. Monitor Long-Running Queries: Identify and terminate queries that are running for an extended period:

   SELECT Id, User, Host, Time, Info
   FROM INFORMATION_SCHEMA.PROCESSLIST
   WHERE Time > 60; -- Queries running for more than 60 seconds
   

Best Practices

1. Limit SHOW PROCESSLIST Output:

   • For high-traffic servers, only display threads for the current user by default. Use FULL or query INFORMATION_SCHEMA.PROCESSLIST for a more targeted view.

2. Monitor Regularly:

   • Use SHOW PROCESSLIST or its INFORMATION_SCHEMA equivalent in scripts to monitor server health and identify bottlenecks.

3. Automate Monitoring:

   • Automate query monitoring to detect long-running or stuck queries and alert administrators.

4. Combine with Logs:

   • Use SHOW PROCESSLIST alongside MySQL slow query logs for comprehensive performance analysis.

Conclusion

The SHOW PROCESSLIST command is a powerful tool for monitoring and debugging MySQL server activity. Whether you’re troubleshooting slow queries, managing active connections, or ensuring efficient server performance, understanding and leveraging SHOW PROCESSLIST effectively can significantly improve database management.