MySQL – Show Processlist
Table of content
- What is MySQL Process List?
- The MySQL SHOW PROCESSLIST Command
- Showing Process List Using Client Program
”;
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 PROCESSLISTG
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 PROCESSLISTG
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
To retrieve information about processes running on a MySQL Server, through a PHP program, we need to execute the “SHOW PROCESSLIST” command using the mysqli function query() as follows −
$sql = "SHOW PROCESSLIST"; $mysqli->query($sql);
To retrieve information about processes running on a MySQL Server, through a JavaScript program, we need to execute the “SHOW PROCESSLIST” command using the query() function of mysql2 library as follows −
sql = "SHOW PROCESSLIST"; con.query(sql)
To retrieve information about processes running on a MySQL Server, through a Java program, we need to execute the “SHOW PROCESSLIST” command using the JDBC function executeQuery() as follows −
String sql = "SHOW PROCESSLIST"; statement.executeQuery(sql);
To retrieve information about processes running on a MySQL Server, through a Python program, we need to execute the “SHOW PROCESSLIST” command using the execute() function of the MySQL Connector/Python as follows −
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();
Output
The output obtained is as shown below −
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); }); });
Output
The output obtained is as shown below −
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(); } } }
Output
The output obtained is as shown below −
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()
Output
The output obtained is as shown below −
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'')