MariaDB Kill Process

Posted on In

If a statement is taking too long and cause the process to hang, we need to kill the process from the server.

Show Processlist

use SHOW PROCESSLIST command to list process.

Syntax

1
SHOW [FULL] PROCESSLIST

The Info colume shows the statement being executed. If you do not use the FULL keyword, only the first 100 characters of each statement are shown in the Info field.

The time colume shows the time the process has been in the current state. This time is in seconds. Use this colume to determine if a process is hang.

Sample output

1
2
3
4
5
6
7
SHOW PROCESSLIST;
+----+-----------------+-----------+------+---------+------+------------------------+------------------+----------+
| Id | User            | Host      | db   | Command | Time | State                  | Info             | Progress |
+----+-----------------+-----------+------+---------+------+------------------------+------------------+----------+
|  2 | event_scheduler | localhost | NULL | Daemon  | 2693 | Waiting on empty queue | NULL             |    0.000 |
|  4 | root            | localhost | NULL | Query   |    0 | Table lock             | SHOW PROCESSLIST |    0.000 |
+----+-----------------+-----------+------+---------+------+------------------------+------------------+----------+

Another useful command to check how many rows are locked:

1
show engine innodb status;

look under TRANSACTIONS to see the transactions and if rows are locked by the transaciton

Kill a Process

after you get the processlist_id, you can use KILL command to kill a process.

Kill syntax

1
KILL [CONNECTION | QUERY] processlist_id

KILL CONNECTION is the same as KILL with no modifier: It terminates the connection associated with the given thread or query id.

KILL QUERY terminates the statement that the connection thread_id is currently executing, but leaves the connection itself intact.

Sample use

1
KILL 14;

Reference