How to automatically kill slow MySQL queries after N seconds?

Good MySQL related application requires right performance tuning. Without right tuning of MySQL configuration, application connections started failing and application became slowing down.

If you are using persistent connection, there may be so many connections which are running but in ‘Sleep’ mode currently. If you have MySQL >= 5.1, where there is a processlist in the INFORMATION_SCHEMA. Generate the KILL QUERY commands in bulk from within the MySQL client for query running longer than 20 minutes (1200 seconds):

You can do WHERE clauses against the INFO field to look for a specific query, the TIME field against long running queries, or the DB field against a specific database.

To run add this query in crontab, create a shell file with following content:

Shell file with above content will kill all queries running more than 1200 seconds (20 mins).

Here is another variation:

Shell file with above content will kill all queries running more than 1200 seconds (20 mins) with adding all queries in log file and running same log file afterwards.

Comments

comments

5 thoughts on “How to automatically kill slow MySQL queries after N seconds?

  1. I conceive this web site has got some very excellent information for everyone :D. “Experience is not what happens to you it’s what you do with what happens to you.” by Aldous Huxley.

  2. Heya i’m for the first time here. I found this board
    and I in finding It really useful & it helped me out a lot.
    I’m hoping to present one thing back and aid others such as you aided me.

  3. I love what you guys tend to be up too. This kind of clever work and exposure!
    Keep up the amazing works guys I’ve included you guys to
    our blogroll.

  4. If you are going for best contents like myself, just pay a visit
    this web page every day as it offers feature contents, thanks

Comments are closed.