Is it possible mysql to auto kill queries which took more than let`s say 20 seconds ?
Asked
Active
Viewed 4,603 times
2
-
You mean abort a running query? Something like `Ctrl c`? Or do you mean programming auto abort after 20 sec? – Shervin Asgari Feb 10 '11 at 10:16
-
2that's dangerous if the query took more than 20 seconds is a write operation, which might lead to table corruption – ajreal Feb 10 '11 at 10:22
-
@ajreal: If this is the case, MySQL should fix this bug. A table should never ever get corrupted when you cancel the query. A dbms should be reliable, that's why you use a dbms in the first place. – Frank Heikens Feb 10 '11 at 11:44
-
possible duplicate of [Setup MySQL query timeout](http://stackoverflow.com/questions/2137084/setup-mysql-query-timeout) – Shankar Narayana Damodaran Jan 07 '15 at 09:07
3 Answers
2
I guess you are looking for maatkit utility called mk-kill that will kill queries that match certain criteria.
shantanuo
- 31,689
- 78
- 245
- 403
-
do we need to install anything for it ???? or we can simply execute the command , cuz for me it says , mk-kill command not found – Hussain Akhtar Wahid 'Ghouri' Dec 26 '12 at 07:55
-
1The tool is now known as `pt-kill`, from Percona. You will need to install it. See http://www.percona.com/software/percona-toolkit – Stefan Lasiewski Apr 24 '13 at 21:26
1
Install the RubyGem mysql_manager (sudo gem install mysql_manager) and then add a command like this to your crontab:
mysql-manager --kill --kill:user api --kill:max-query-time 30 --log:level DEBUG
For more options, run mysql-manager --help.
You might need to specify an alternative --db:dsn, --db:username, or --db:password.
Read more about it here: https://github.com/osterman/mysql_manager
Erik Osterman
- 559
- 4
- 7
1
It's possible to write a program which does it. Your program would use SHOW PROCESSLIST to discover the currently running queries and how long they've been running, then issue a KILL query to terminate one.
Dan Grossman
- 51,866
- 10
- 112
- 101