Search

Identify and Kill Queries with MySQL Command-Line Tool

Long-running MySQL queries keep other transactions from accessing the necessary tables to execute a request, leaving your users on hold. To kill these queries, you’ll need to access the environment’s MySQL Database.






Identify Long-Running Queries


After successfully creating a local MySQL connection to the site’s database, run the following command to show a list of active threads:

show processlist;


Review the Time field to identify the longest running query and run the following command to kill it:


kill <thread_id>;


Note: Replace <thread_id> with the ID of the query you want to terminate.


Kill All Queries

If a large number of bad requests are blocking valid queries, you can clear them out without having to run kill on every individual thread.


Execute the following to generate kill commands from the PROCESSLIST table:


mysql> SELECT GROUP_CONCAT(CONCAT('KILL ',id,';') SEPARATOR ' ') 'Paste the following query to kill all processes' FROM information_schema.processlist WHERE user<>'system user'\G


Copy the provided query in the output and run as instructed.

Need more details?
Contact us

We are here to assist.

email : info@innofoundry.com 
phone : +1 (415) 669-4289

Location: Austin, Texas, United States

© 2009 - 2018 Innofoundry LLC. 
Innofoundry® is a Registered Trademark of Innofoundry LLC