MySQL - Show Processlist

MySQL - Monitoring with SHOW PROCESSLIST

MySQL is a multi-user database system where multiple clients can connect and execute operations simultaneously. Each connection to the server runs in its own thread. The SHOW PROCESSLIST command is a real-time diagnostic tool that gives you a snapshot of these threads and what they are currently doing.

What is the MySQL Process List?

The process list shows the set of threads currently executing within the MySQL server. It’s your first stop when you need to answer questions like:

• Who is connected to my database?
• What queries are currently running?
• Is a query stuck or running for a very long time?
• Why am I getting a ‘too many connections’ error?

To see all threads, you need the PROCESS privilege. Without it, you can only see your own threads. This is an important security feature.

The SHOW PROCESSLIST Command

This command displays information about the current processes. It’s invaluable for debugging live performance issues.

Syntax

SHOW [FULL] PROCESSLIST

The FULL keyword is optional. If you omit it, the Info field (which contains the SQL statement) is truncated to 100 characters. To see the complete query, you must use SHOW FULL PROCESSLIST.

Example

Using \G as a delimiter in the MySQL client formats the output vertically, which is easier to read.

SHOW FULL PROCESSLIST\G

Example Output

*************************** 1. row ***************************
     Id: 8
   User: app_user
   Host: app-server-1.internal:48916
     db: my_app
Command: Query
   Time: 25
  State: Sending data
   Info: SELECT * FROM large_table JOIN another_table ON ... WHERE ...
*************************** 2. row ***************************
     Id: 9
   User: root
   Host: localhost
     db: NULL
Command: Query
   Time: 0
  State: executing
   Info: SHOW FULL PROCESSLIST
*************************** 3. row ***************************
     Id: 10
   User: app_user
   Host: app-server-2.internal:51342
     db: my_app
Command: Sleep
   Time: 120
  State:
   Info: NULL

Interpreting the Output

Understanding the columns is key to diagnosing problems:

• Id: The connection identifier. You can use this ID with the KILL command (e.g., KILL 8;) to terminate a problematic process.
• User/Host: Who is connected and from where. This helps identify the source of a query (e.g., an application server, a cron job).
• db: The default database for the thread.
• Command: The type of command the thread is executing. Query means it’s actively running a statement. Sleep is very common and means the connection is idle, waiting for the next command from the client. A high number of Sleep threads can indicate application connection pooling issues.
• Time: The number of seconds the thread has been in its current state. A high Time for a Query command is a red flag for a long-running query.
• State: A description of what the thread is doing. This is critical for debugging. States like Locked, Copying to tmp table, or Creating sort index for an extended period indicate performance bottlenecks.
• Info: The actual SQL statement being executed (or NULL if none). Always use FULL to see the complete query.

A Modern Alternative: The Performance Schema

SHOW PROCESSLIST is a great first-look tool. For more in-depth, historical, and aggregated performance analysis, modern MySQL versions provide the Performance Schema. It’s a powerful instrumentation engine that collects performance data in a set of database tables.

For example, SELECT * FROM performance_schema.threads; provides similar information to SHOW PROCESSLIST but in a queryable table format. The Performance Schema is more complex but offers far greater insight for advanced tuning.

Monitoring Processes with a Client Program

You can also execute SHOW PROCESSLIST from an application to build custom monitoring dashboards or health checks.

PHP
NodeJS
Java
Python



// Assumes a connected $mysqli object
$sql = "SHOW FULL PROCESSLIST";
$result = $mysqli->query($sql);

if ($result) {
    $processes = $result->fetch_all(MYSQLI_ASSOC);
    print_r($processes);
    $result->free();
}



// Assumes a connected 'connection' object from mysql2/promise
async function showProcesses(connection) {
  try {
    const [rows] = await connection.execute('SHOW FULL PROCESSLIST');
    console.log('Current Processes:', rows);
  } catch (err) {
    console.error('Failed to get process list:', err);
  }
}



// Assumes a connected 'conn' object (java.sql.Connection)
String sql = "SHOW FULL PROCESSLIST";
try (Statement stmt = conn.createStatement();
     ResultSet rs = stmt.executeQuery(sql)) {

    ResultSetMetaData rsmd = rs.getMetaData();
    int columnCount = rsmd.getColumnCount();

    while (rs.next()) {
        for (int i = 1; i <= columnCount; i++) {
            System.out.printf("%s: %s, ", rsmd.getColumnName(i), rs.getString(i));
        }
        System.out.println();
    }
} catch (SQLException e) {
    e.printStackTrace();
}



# Assumes a connected 'connection' object and a 'cursor' from mysql-connector-python
try:
    cursor.execute("SHOW FULL PROCESSLIST")
    # Using a dictionary cursor makes the output easy to work with
    print("Current Processes:")
    for process in cursor.fetchall():
        print(process)
except mysql.connector.Error as err:
    print(f"Error: {err}")