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
Actually, we kill them:
Optionally, you can select queries by host, username, database, etc.
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
WHERE
`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.