The
SHOW PROCESSLIST
statement returns information about the active connections. The PROCESSLIST
table in the information_schema
database contains additional complete information; we will discuss this. Also, the threads
table in the performance_schema
database includes the same information returned by SHOW PROCESSLIST
, plus some extra columns. Normally, SHOW PROCESSLIST
returns sufficient information and is less verbose. The PROCESSLIST
table has the advantage that its contents can be used in a stored program.
The following example shows the contents of the PROCESSLIST
table and the output of SHOW PROCESSLIST
:
MariaDB [(none)]> SELECT * FROM information_schema.PROCESSLIST G *************************** 1. row *************************** ID: 5 USER: root HOST: localhost DB: NULL COMMAND: Query TIME: 0 STATE: Filling schema table INFO: SELECT * FROM information_schema.PROCESSLIST TIME_MS: 0.652 STAGE: 0 MAX_STAGE: 0 PROGRESS: 0.000 MEMORY_USED: 82920 EXAMINED_ROWS: 0 QUERY_ID: 11 1 row in set (0.00 sec) MariaDB [(none)]> SHOW PROCESSLIST G *************************** 1. row *************************** Id: 5 User: root Host: localhost db: NULL Command: Query Time: 0 State: closing tables Info: SHOW PROCESSLIST Progress: 0.000 1 row in set (0.00 sec)
The following table shows the descriptions of the columns of the PROCESSLIST
table:
PROCESSLIST table column |
SHOW PROCESSLIST statement column |
Description |
---|---|---|
|
|
This shows the connection ID. |
|
|
This shows the username as it appears after |
|
|
This shows the hostname as it appears after |
|
|
This shows the default database (that changes after the USE statement). |
|
|
This shows the type of the issued command (see the next table for the possible values). |
|
|
This shows the seconds elapsed since the process switched to the current state. |
|
|
This shows the state of the process (see the table in the States of the process section for the possible values). |
|
|
This shows the statement that is being executed, if any. Unless |
|
This is similar to | |
|
If the statements support the progress reporting, the job can consist of multiple stages. These columns provide the number of the current stage and final stage. | |
|
|
If the statement supports the progress reporting, this is the percentage of the completed job (as |
|
This shows the quantity of memory used by this process. This feature is added in MariaDB 10.0. | |
|
This shows the number of rows read by the process and is added in MariaDB 10.0. | |
|
This shows the ID of the current statement, if any, being executed. This feature is added in MariaDB 10.0. |
Querying the threads
table causes no locks. However, note that activating the performance_schema
database causes an overhead, which affects most server activities. This is the reason why performance_schema
is disabled by default in MariaDB 10.0. It stores information about all threads, including internal threads, and not only the connections with the clients. The threads
table contains all the columns from SHOW PROCESSLIST
, written in uppercase with the PROCESSLIST_
prefix. It also contains the following extra columns:
THREAD_ID
: This is the ID of the thread. It is different from the value of PROCESSLIST_ID
, which is the value that is shown by SHOW PROCESSLIST
.NAME
: This indicates the thread types. There are many possible values that reflect the internal MariaDB structures. For example, the value for a thread that is associated to a client connection is thread/sql/one_connection
.TYPE
: This is BACKGROUND
for internal threads and FOREGROUND
for threads that are visible via SHOW PROCESSLIST
.PARENT_THREAD_ID
: This is the ID of the thread that created it.ROLE
: This is always NULL
. These are currently ignored in the performance_schema
database.INSTRUMENTED
: This indicates whether the thread activities are tracked in the performance_schema
database.The THREAD_ID
column can be used to join the threads
table with other tables in the performance_schema
database. Consider the following example:
SELECT * FROM performance_schema.events_statements_current s LEFT JOIN performance_schema.threads t ON s.THREAD_ID = t.THREAD_ID;
The following table describes the values of the COMMAND
column:
Value |
Description |
---|---|
|
This means that the process sends the binary log contents to a slave |
|
This means that the process changes the current user |
|
This means that the process deallocates a prepared statement |
|
This means that the process is a slave thread connected to a master |
|
This means that the process is a slave process connected to a master |
|
This means that the process creates a database |
|
This means that the process itself is an internal thread |
|
This means that the process generates the debug information |
|
This means that the process executes an |
|
This means that the process erases a database |
|
This means that a fatal error has occurred in the process |
|
This means that the process executes a prepared statement |
|
This means that the process fetches rows from the result of a prepared statement |
|
This means that the process retrieves information about table columns |
| |
|
This means that the process terminates another process |
|
This means that the process retrieves a large amount of data from prepared statement results |
|
This means that the process replies to a ping |
|
This means that the process makes a prepared statement |
|
This means that the process gathers information about the existing processes |
|
This means that the process executes a statement |
|
This means that the process exits |
|
This means that the process flushes tables, hosts, or caches; or resets status variables or replication information |
|
This means that the process registers a new slave |
|
This means that the process resets a prepared statement |
|
This means that the process changes a connection option |
|
This means that the process stops the server |
|
This means that the process waits for a new statement from the client |
|
This means that the process gathers information about the server's status |
|
This means that the process sends a whole table to a slave |
The STATE
column indicates exactly what the state is doing. It has several possible values. Here, we only list the main values:
While examining the list of processes, you may notice that one of them is slow and is blocking other processes. Or perhaps, you may notice several sleeping processes. In this case, you may want to terminate them. The KILL
statement can be used for this purpose. MariaDB supports more clauses for this command, if compared to the MySQL syntax. The syntax for KILL
is as follows:
KILL [ HARD | SOFT ] [ CONNECTION | QUERY [ID] ] < id | USER user_name >
By default, KILL
terminates a connection. The CONNECTION
keyword just makes this clear for the one who reads the statement. If the QUERY
keyword is specified, only the statement executed by a connection is killed, while the connection itself remains open.
The ID of the connection or statement to be killed must be specified. Even with the QUERY
keyword, the thread ID must be specified; MariaDB expects a query ID only if the ID
keyword is specified too. Remember that the connection ID becomes visible by querying the PROCESSLIST
table, not with SHOW PROCESSLIST
.
As an alternative, you can specify the USER
clause followed by an account name or a username to destroy all the connections or queries that belong to the specified account or user. To kill all connections or statements that belong to our account, we can also specify CURRENT_USER()
.
Killing a query or connection with the SOFT
option, which is the default option, can be slower but it is also safer. The HARD
keyword is a faster but more brutal way to terminate operations, which should only be used if SOFT KILL
takes too long. After a HARD KILL
, we are likely to experience data corruption. This is how the command works:
KILL
command sets a flag for the target connection. If a connection is flagged by KILL
, SHOW PROCESSLIST
shows 'Killed'
in the Info
column.HARD KILL
command was requested. If so, the connection or statement is terminated immediately, probably leaving a table in an inconsistent state.But in the current versions, this difference only concerns the repairing of Aria
or MyISAM
tables, and the creation of an index on such tables. Remember that, in the first case, the data could be even more corrupted than before, and in the second case the new index will need to be rebuilt.
The following example shows how to kill a statement without closing the connection.
In a mysql command-line client, let's execute this statement:
MariaDB [(none)]> DO SLEEP(2000);
The preceding statement is not harmful for anyone, but it is good for our example because it makes the thread pause for 2000
seconds.
Next, let's open another client and execute the following statement:
MariaDB [(none)]> SHOW PROCESSLIST; +----+-----------------+-----------+------+---------+-------+------------------------+------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +----+-----------------+-----------+------+---------+-------+------------------------+------------------+----------+ … | 9 | root | localhost | NULL | Query | 8 | User sleep | DO SLEEP(2000) | 0.000 | +----+-----------------+-----------+------+---------+-------+------------------------+------------------+----------+ 3 rows in set (0.00 sec)
Many rows are returned, but we only care about the one that shows the previous statement. The thread ID is 9
. Let's use this information to terminate the statement:
MariaDB [(none)]> KILL QUERY 9; Query OK, 0 rows affected (0.00 sec)
We can verify that the KILL
statement worked by using SHOW PROCESSLIST
.
An inactive connection is automatically closed by the server when the timeout specified with the wait_timeout
server variable expires.