MySQL - Show Processlist

MySQL database provides a multi-user environment, that allows multiple clients to access the database at the same time. A process is defined as the operations performed by a user on the MySQL Server. Multiple processes can be run on a MySQL Server concurrently by multiple users.

What is MySQL Process List?

The MySQL process list is defined as the list of operations currently being performed by the set of user threads executing within the server.

If a user has the PROCESS privilege, they can see all threads in a server, including threads of other users. But if a user does not have such privilege, non-anonymous users have access to information about their own threads only; while anonymous users have no access to thread information.

To retrieve information about these processes running on a MySQL Server, we can use the SHOW PROCESSLIST command.

The MySQL SHOW PROCESSLIST Command

The MySQL SHOW PROCESSLIST command is used to display information about the current processes running on a MySQL Server.

This statement is especially useful when dealing with a "too many connections" error, as it provides details about these connections and their operations. Additionally, MySQL reserves one extra connection for administrators with CONNECTION_ADMIN privilege (or SUPER privilege in older versions), to ensure they can always access the system.

Syntax

Following is the syntax of the SHOW PROCESSLIST Command −

SHOW [FULL] PROCESSLIST

Here, the FULL keyword is optional. But if you omit the FULL keyword, SHOW PROCESSLIST displays only the first 100 characters of each statement in the Info field.

Example

Let us see an example to show the usage of SHOW PROCESSLIST command. We will use the '\G' delimiter to print the information table vertically −

SHOW PROCESSLIST\G

Output

Following is the output obtained −

*************************** 1. row ***************************
     Id: 5
   User: event_scheduler
   Host: localhost
     db: NULL
Command: Daemon
   Time: 1065209
  State: Waiting on empty queue
   Info: NULL
*************************** 2. row ***************************
     Id: 56
   User: root
   Host: localhost:51995
     db: customers
Command: Query
   Time: 0
  State: init
   Info: SHOW PROCESSLIST
2 rows in set (0.00 sec)

Example

Now, let us also use the FULL keyword with the SHOW PROCESSLIST command as shown in the following example −

SHOW FULL PROCESSLIST\G

Output

The output obtained is as shown below −

*************************** 1. row ***************************
     Id: 5
   User: event_scheduler
   Host: localhost
     db: NULL
Command: Daemon
   Time: 1065138
  State: Waiting on empty queue
   Info: NULL
*************************** 2. row ***************************
     Id: 56
   User: root
   Host: localhost:51995
     db: customers
Command: Query
   Time: 0
  State: init
   Info: SHOW FULL PROCESSLIST
2 rows in set (0.00 sec)

Output Explanation

The output result-set obtained from the SHOW PROCESSLIST command has the following columns −

• Id − It is the identity of a connection.

• User − This holds the name of a MySQL user who issued the statement.

• Host − The host name of the client issuing the statement (except for system user, as there is no host for it). The host name for TCP/IP connections is represented in "host_name:client_port" format to make it easier to determine the actions of a client.

• db − This is the default database for the thread, or NULL if none has been selected.

• Command − Shows the type of command the corresponding thread is executing on behalf of the client, or shows Sleep if the session is idle.

• Time − The time in seconds that the thread has been in its current state.

• State − An action, event, or state that indicates 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.

• Info − The statement the thread is executing. If it is executing no statement, NULL is shown.

Showing Process List Using Client Program

We can also show the process list using Client Program.

Syntax

$sql = "SHOW PROCESSLIST";
$mysqli->query($sql);

sql = "SHOW PROCESSLIST";
con.query(sql)

String sql = "SHOW PROCESSLIST";
statement.executeQuery(sql);

show_processlist_query = "SHOW PROCESSLIST"
cursorObj.execute(show_processlist_query)

Example

Following are the programs −

$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$db = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $db);
if ($mysqli->connect_errno) {
    printf("Connect failed: %s
", $mysqli->connect_error);
    exit();
}
//printf('Connected successfully.
');
$sql = "SHOW PROCESSLIST";
if($result = $mysqli->query($sql)){
    printf("Show query executed successfully...!\n");
    printf("Process list: \n");
    while($row = mysqli_fetch_array($result)){
        print_r($row);
    }
}
if($mysqli->error){
    printf("Error message: ", $mysqli->error);
}
$mysqli->close();

Show query executed successfully...!
Process list: 
Array
(
    [0] => 5
    [Id] => 5
    [1] => event_scheduler
    [User] => event_scheduler
    [2] => localhost
    [Host] => localhost
    [3] =>
    [db] =>
    [4] => Daemon
    [Command] => Daemon
    [5] => 886450
    [Time] => 886450
    [6] => Waiting on empty queue
    [State] => Waiting on empty queue
    [7] =>
    [Info] =>
)
Array
(
    [0] => 602
    [Id] => 602
    [1] => root
    [User] => root
    [2] => localhost:54978
    [Host] => localhost:54978
    [3] => tutorials
    [db] => tutorials
    [4] => Sleep
    [Command] => Sleep
    [5] => 2994
    [Time] => 2994
    [6] =>
    [State] =>
    [7] =>
    [Info] =>
)
Array
(
    [0] => 641
    [Id] => 641
    [1] => root
    [User] => root
    [2] => localhost:56444
    [Host] => localhost:56444
    [3] => tutorials
    [db] => tutorials
    [4] => Query
    [Command] => Query
    [5] => 0
    [Time] => 0
    [6] => init
    [State] => init
    [7] => SHOW PROCESSLIST
    [Info] => SHOW PROCESSLIST
)  

var mysql = require('mysql2');
var con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "Nr5a0204@123"
});

// Connecting to MySQL
con.connect(function (err) {
    if (err) throw err;
    console.log("Connected!");
    console.log("--------------------------");

    // Create a new database
    sql = "Create Database TUTORIALS";
    con.query(sql);

    sql = "USE TUTORIALS";
    con.query(sql);

    sql = "SHOW PROCESSLIST"
    con.query(sql, function(err, result){
      if (err) throw err
      console.log("**usage of SHOW PROCESSLIST command**");
      console.log(result);
      console.log("--------------------------");
    });

    sql = "SHOW FULL PROCESSLIST"
    con.query(sql, function(err, result){
      if (err) throw err
      console.log("**usage of FULL keyword with the SHOW PROCESSLIST command:**");
      console.log(result);
    });
});  

 
Connected!
--------------------------
**usage of SHOW PROCESSLIST command**
[
  {
    Id: 5,
    User: 'event_scheduler',
    Host: 'localhost',
    db: null,
    Command: 'Daemon',
    Time: 1279159,
    State: 'Waiting on empty queue',
    Info: null
  },
  {
    Id: 310,
    User: 'root',
    Host: 'localhost:64181',
    db: null,
    Command: 'Sleep',
    Time: 6,
    State: '',
    Info: null
  },
  {
    Id: 311,
    User: 'root',
    Host: 'localhost:64182',
    db: null,
    Command: 'Sleep',
    Time: 4,
    State: '',
    Info: null
  },
  {
    Id: 329,
    User: 'root',
    Host: 'localhost:64371',
    db: 'tutorials',
    Command: 'Query',
    Time: 0,
    State: 'init',
    Info: 'SHOW PROCESSLIST'
  }
]
--------------------------
**usage of FULL keyword with the SHOW PROCESSLIST command:**
[
  {
    Id: 5,
    User: 'event_scheduler',
    Host: 'localhost',
    db: null,
    Command: 'Daemon',
    Time: 1279159,
    State: 'Waiting on empty queue',
    Info: null
  },
  {
    Id: 310,
    User: 'root',
    Host: 'localhost:64181',
    db: null,
    Command: 'Sleep',
    Time: 6,
    State: '',
    Info: null
  },
  {
    Id: 311,
    User: 'root',
    Host: 'localhost:64182',
    db: null,
    Command: 'Sleep',
    Time: 4,
    State: '',
    Info: null
  },
  {
    Id: 329,
    User: 'root',
    Host: 'localhost:64371',
    db: 'tutorials',
    Command: 'Query',
    Time: 0,
    State: 'init',
    Info: 'SHOW FULL PROCESSLIST'
  }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class ProcessList {
  public static void main(String[] args) {
    String url = "jdbc:mysql://localhost:3306/TUTORIALS";
    String user = "root";
    String password = "password";
    ResultSet rs;
    try {
      Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();
            //System.out.println("Database connected successfully...!");
            String sql = "SHOW PROCESSLIST";
            rs = st.executeQuery(sql);
            System.out.println("Show statement executed successfully...!");
            System.out.println("ProcessList: ");
            while(rs.next()) {
              String p_list = rs.getString(1);
              System.out.println(p_list);
            }
    }catch(Exception e) {
      e.printStackTrace();
    }
  }
}

Show statement executed successfully...!
ProcessList: 
5
13
43  

import mysql.connector
# Establishing the connection
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
# Creating a cursor object
cursorObj = connection.cursor()
# show trigger
show_processlist_query = "SHOW PROCESSLIST"
cursorObj.execute(show_processlist_query)
result = cursorObj.fetchall()
print("Processlist in the database:")
for row in result:
    print(row)
# close the cursor and connection
cursorObj.close()
connection.close()

Processlist in the database:
(5, 'event_scheduler', 'localhost', None, 'Daemon', 1029217, 'Waiting on empty queue', None)
(322, 'root', 'localhost:56077', 'tut', 'Sleep', 309, '', None)
(384, 'root', 'localhost:58907', 'tut', 'Query', 0, 'init', 'SHOW PROCESSLIST')