Bulk Stop Queries

As a small addition to the article Cleaning the database server .

Sometimes, a large number of database queries accumulate in the queue, the execution of which must be stopped without restarting MySQL.

To solve this problem, we use this method:

We get a list of long-running queries, in this case - SELECTs that run longer than 100 seconds
SELECT CONCAT('KILL ', id, ';') 
FROM information_schema.processlist 
`info` LIKE('SELECT%') 
AND `time` > 100 
INTO OUTFILE '/tmp/killList.sql'; 

Actually, we kill them:
SOURCE /tmp/killList.sql; 

Optionally, you can select queries by host, username, database, etc.

Also popular now: