8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | 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.
Identify the problem thread.
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>
Kill the thread.
mysql> KILL CONNECTION 7114;
Check the thread is gone, using
SHOW PROCESSLIST
again, or a direct query.mysql> SELECT * FROM information_schema.processlist WHERE id = 7114; Empty set (0.00 sec) mysql>
For more information see:
Hope this helps. Regards Tim...