How to kill long run mysql queries
As a DatabaseÂ AdministratorÂ you should be aware about the slow queries . Â When you are running a high traffic website with multiple mysql servers , you have to be very careful about long running queries, ideally all queriesÂ shouldÂ get executed below 2 seconds for high traffic site as there are thousands of database queries are coming to the mysql read servers. If one query get locked in MyISAM then theÂ subsequentÂ all the queries will get locked and finally your site will collapse.
The slow query log consists of all SQL statements that took more than long_query_time seconds to execute and required at least min_examined_row_limit rows to be examined. The time to acquire the initial table locks is not counted as execution time. mysqld writes a statement to the slow query log after it has been executed and after all locks have been released, so log order might be different from execution order.
The best way to handle this situation is using a third party tool called mkill (Twitter is using this utility to kill long running queries.)