8i | 9i | 10g | 11g | 12c | 13c | 18c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » Mysql » Here

MySQL : Killing Threads (PROCESSLIST, KILL)

A thread in MySQL is the equivalent to a session in an Oracle database. This article describes how to kill threads (or sessions) in MySQL Databases.

Identify Threads (PROCESSLIST)

The simplest way to identify threads is to use the SHOW PROCESSLIST command.

mysql> SHOW PROCESSLIST;
+------+------------+-----------------------+-------+---------+------+-------+------------------+
| Id   | User       | Host                  | db    | Command | Time | State | Info             |
+------+------------+-----------------------+-------+---------+------+-------+------------------+
| 7114 | user1      | 123.123.123.123:65032 | pma   | Sleep   | 1718 |       | NULL             |
| 7120 | root       | 123.123.123.123:52486 | mysql | Query   |    0 | init  | show processlist |
+------+------------+-----------------------+-------+---------+------+-------+------------------+
2 rows in set (0.02 sec)

mysql>

The information presented includes the "Id", which is the thread_id we will refer to later. It also contains information about the user associated with the thread and the work they are doing (Info) if any.

An alternative to using the SHOW PROCESSLIST command is to query the INFORMATION_SCHEMA.PROCESSLIST table.

mysql> SELECT * FROM information_schema.processlist ORDER BY id;
+------+------------+-----------------------+-------+---------+------+-----------+----------------------------------------------------------+
| ID   | USER       | HOST                  | DB    | COMMAND | TIME | STATE     | INFO                                                     |
+------+------------+-----------------------+-------+---------+------+-----------+----------------------------------------------------------+
| 7114 | user1      | 123.123.123.123:65032 | pma   | Sleep   | 2112 |           | NULL                                                     |
| 7120 | root       | 123.123.123.123:52486 | mysql | Query   |    0 | executing | SELECT * FROM information_schema.processlist ORDER BY id |
+------+------------+-----------------------+-------+---------+------+-----------+----------------------------------------------------------+
2 rows in set (0.01 sec)

mysql>

Killing threads (KILL)

Once you've identified the problem thread, you can use the KILL command to kill it. There are basic two variations on the KILL command.

# Kill the entire connection.
KILL thread_id;
KILL CONNECTION thread_id;

# Terminate the currently executing statement, but leave the connection intact.
KILL QUERY thread_id;

So a typical scenario may look something like the following.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.