pt-kill

I've started using pt-kill on an internal MySQL system to snipe queries that run too long. It works very well. For reasons unknown to me, we have code that logs to a table but does so serially and LOCKS all the tables involved in the logging effort. At times this will cause bottlenecks and the tables will be locked for 30 minutes and eventually the server will reach max connections and crash because nothing can get through. I've demonized pt-kill and it watches for any queries that match the specific databases, specific user, busy time and then kills it. I like how to logs every query it kills either to a file or to table so I can keep track of how often it is working.

Here are my examples:

This first example is more judicious as it only kills one query at a time and then waits:

pt-kill --interval=5 --busy-time=10 --create-log-table --log-dsn=h=localhost,D=percona,t=kill_log --daemonize --user=USERNAME --password=xxxxxxx --victims=oldest --wait-after-kill=10 --match-command=Query --match-info "^(Lock|LOCK|lock)" --match-user=app_user --match-db=logs --kill-query

This second examples will kill more queries more freuqently which it catches any and all with the busy-time of 10 seconds (notices the victims is set to all):

pt-kill --interval=5 --busy-time=10 --create-log-table --log-dsn=h=localhost,D=percona,t=kill_log --daemonize --user= USERNAME --password= xxxxxxx --victims=all --wait-after-kill=1 --match-command=Query --match-info "^(Lock|LOCK|lock)" --match-user= app_user --match-db= logs --kill-query