Investigating The Top Windows 2012 VPS Server Solutions
Display [COMPLETE] PROCESS LIST Command
The MySQL process list showcases the tasks carried out by the combination of the “processlist full” keywords executed within the MySQL server. The SHOW PROCESSLIST statement or the utilization of the “show processlist full” keyword serves as a singular repository of process details.
Jump To...
Key Considerations for Grasping MySQL Display Processes
A different approach to executing the MySQL “show processlist” command relies on the “Performance Schema Processlist Table.” This method eliminates the need for a mutex, distinguishing it from the default execution of the show processlist command.
If granted process privileges, one can observe all threads associated with other users. Conversely, a non-anonymous individual can access information concerning threads without process privileges, but this access is restricted to threads linked to their own activities, excluding those of other users.
The procedure to verify
The “show processlist” command displays the leading 100 characters of each statement in the info field when used without the FULL keyword. When there is a need to identify the query causing a slowdown, it is essential to inspect the table, as it provides information about currently running MySQL queries.
The “show processlist” statement or command becomes valuable when encountering an error message such as “too many connections.”
Threads can be terminated using the kill statement.
An example of the “show processlist” command is as follows:
mysql> SHOW FULL PROCESSLIST\G
Row
Id: 1
User: system user
Host:
db: NULL
Command: Connect
Time: 1030455
State: Waiting for master to send event
Info: NULL
Row
Id: 2
User: system user
Host:
db: NULL
Command: Connect
Time: 1004
State: Has read all relay log; waiting for the slave
I/O thread to update it
Info: NULL
Row
Id: 3112
User: replikator
Host: artemis:2204
db: NULL
Command: Binlog Dump
Time: 2144
State: Has sent all binlog to slave; waiting for binlog to be updated
Info: NULL
Row
Id: 3113
User: replikator
Host: iconnect2:45781
db: NULL
Command: Binlog Dump
Time: 2086
State: Has sent all binlog to slave; waiting for binlog to be updated
Info: NULL
Row
Id: 3123
User: stefan
Host: localhost
db: apollon
Command: Query
Time: 0
State: NULL
Info: SHOW FULL PROCESSLIST
The output of SHOW PROCESSLIST displays the following columns:
1. Id
The connection identifier, often denoted as “ID,” corresponds to the precise value visible in the ID column of the INFORMATION_SCHEMA processlist table. It is also found in the PROCESSLIST_ID queue of the Performance Schema threads table. Additionally, it can be retrieved using the CONNECTION_ID() command in the thread.
2. User
The MySQL user responsible for executing the statement is reflected in the “User” column. When the system user is indicated, it signifies a non-client thread initiated by the server to handle internal tasks.
3. Note
The User value representing the system user is distinct from the SYSTEM_USER privilege.
4. Host
The hostname of the client submitting the statement is identified in the “Host” column. For TCP/IP connections, the hostname is displayed in the format host_name:client_port, aiding in easier interpretation of the connection details.
5. DB
The default database associated with the thread, applicable when no specific database has been selected, is reflected in the “db” column.
6. Command
The “Command” column indicates the type of operation the thread is performing on behalf of the client. If the session is inactive, it is labeled as “Sleep.”
7. Time
The “Time” column represents the duration in seconds that the thread has been in its current state. In the case of a replica SQL thread, the value signifies the seconds between the timestamp of the preceding replicated event and the current time on the replica host.
8. State
The “State” column describes the activity, event, or condition that indicates what the thread is currently engaged in. Prolonged presence in a specific state for an extended duration may indicate potential issues that warrant further analysis.
9. Info
The actual SQL statement that the thread is executing is represented in the “Info” column. If the thread is not currently running any statement, the value is NULL.
What is the purpose of executing the 'SHOW PROCESSLIST' command in MySQL?
The ‘SHOW PROCESSLIST’ command is used to display the active processes and running threads associated exclusively with your MySQL account. If a user possesses process privileges, they can view all active threads, providing insights into the ongoing operations of each thread. This command reveals information about the executing threads and their respective tasks.
The command is as follows:
mysql> SHOW processlist;
It will showcase the ID, User, Host, and Database as the output.
+----+-----------------+-----------------+------+---------+------+------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------------+------+---------+------+------------------------+------------------+
| 4 | event_scheduler | localhost | NULL | Daemon | 968 | Waiting on empty queue | NULL |
| 9 | root | localhost:50255 | NULL | Query | 0 | starting | show processlist |
+----+-----------------+-----------------+------+---------+------+------------------------+------------------+
2 rows in set (0.00 sec)
The output will be different if we change the default database. Now, let us try the MySQL command.
mysql> SHOW processlist;
The output of the command will be:
+----+-----------------+-----------------+----------+---------+------+------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------------+----------+---------+------+------------------------+------------------+
| 4 | event_scheduler | localhost | NULL | Daemon | 1148 | Waiting on empty queue | NULL |
| 9 | root | localhost:50255 | business | Query | 0 | starting | show processlist |
+----+-----------------+-----------------+----------+---------+------+------------------------+------------------+
2 rows in set (0.00 sec)
Frequently Asked Questions
The MySQL SHOW PROCESSLIST command is employed to exhibit a list of active processes within the MySQL database. It furnishes details about each running process, encompassing the ongoing query and the duration the process has been active. This command is valuable for inspecting and diagnosing database activity, identifying the queries in execution, and gauging the runtime of each process.
To perform the MySQL SHOW PROCESSLIST command, launch the MySQL command-line client and input the subsequent command: SHOW PROCESSLIST;. This action will present a roster of currently active processes within the MySQL database.
The MySQL SHOW PROCESSLIST statement supplies details about each active process, encompassing the process ID, user, host, database, command, state, and query.
To filter the output of the MySQL SHOW PROCESSLIST command, you can use the WHERE clause to specify a condition. For example, to only show processes that are running a particular query, you could use the following command: SHOW PROCESSLIST WHERE Info like ‘%<query>%’;. Replace <query> with the text of the query you want to filter on.
To halt a running process using the MySQL SHOW PROCESSLIST command, you can utilize the KILL command, followed by the process ID. For instance, to terminate a process with the ID 1234, you can execute the following command: KILL 1234;.
Certainly, the MySQL SHOW PROCESSLIST command is a valuable tool for diagnosing and addressing slow queries. By pinpointing queries with prolonged execution times, you can initiate optimizations to enhance overall database performance and reduce query response durations.
