SHOW PROCESSLIST - MySQL

Overview

SHOW PROCESSLIST is a MySQL command that provides real-time information about all running threads and their activities within the database. It’s an essential tool for monitoring, troubleshooting, and optimizing MySQL performance.

Syntax

SHOW [FULL] PROCESSLIST [WHERE <expr>] [ORDER BY <expr>]

Parameters:

  • FULL: Display extended information, including the query text.
  • WHERE: Filter the results based on conditions.
  • ORDER BY: Sort the results by a specific column.

Options/Flags

  • -s, –socket: The socket path to use.
  • -u, –user: The username to authenticate.
  • -h, –host: The hostname to connect to.
  • -p, –password: The password to authenticate.
  • –count: Only show the number of running threads.
  • –wait: Wait for the server to respond, even if it takes several seconds.

Examples

1. Display all running threads:

SHOW PROCESSLIST;

2. Display extended information:

SHOW FULL PROCESSLIST;

3. Filter by a specific user’s activity:

SHOW PROCESSLIST WHERE user = 'my_user';

4. Sort by query execution time:

SHOW PROCESSLIST ORDER BY Time;

Common Issues

1. Connection Errors:

  • Ensure that you have the correct hostname, username, and password.
  • Check that the MySQL server is running and listening on the specified port.

2. Slow or No Response:

  • The server may be overloaded. Try again later or increase the --wait timeout.
  • A thread may be blocked. Use SHOW INNODB STATUS to check for blocking locks.

Integration

SHOW PROCESSLIST can be combined with other MySQL commands and tools:

  • mysqldumpslow: Use SHOW PROCESSLIST to identify slow queries and profile their execution.
  • pt-query-digest: Analyze the output of SHOW PROCESSLIST to gain insights into query patterns and performance bottlenecks.
  • SHOW INNODB STATUS: Check for blocking locks and other InnoDB internal information.
  • pt-stalk: Monitor server activity and track thread performance over time.
  • slow_query_log: Enable logging of slow queries for further analysis.