Killing long running MySQL queries automatically

August 5, 2016

If you are having issues on a busy site with long running MySQl queries you could use the great percona tool pt-kill. It can run as a demon and keep an eye on your database without you having to do anything except check the log table.


To download the percona pt-kill script use:
<pre class="brush: bash; gutter: false">wget</pre>

This is the full command to monitor a databases select processes
<pre class="brush: sql; gutter: false">pt-kill --busy-time=300 --kill-query
The options are:

* --busy-time=300 (Only include queries that have been running for over 5 minutes)
* --kill-query (Only kill the query and not the connection as that can be reused)
* --database (Database to monitor)
* --host (host)
* --user (user to use, need to make sure it has permission to kill processes)
* --create-log-table (Create a log table if it doesn't exist)
* --vervbose (Don't use on live)
* --log-dsn=D=your_db,h=your_host,u=tsr,t=pt_kill_log (This is the connection details to where the log table will be)
* --match-info="^(select|SELECT|Select)" (Use this to only match to select statements, this will allow all updates, deletes and inserts to still be run)
* --log=pt_kill.log ( log file to be written to when in demon mode)
* --daemonize (run as background process)


Further details here: <a href=""></a> 

Share on Facebook
Share on Twitter
Please reload

Recent Posts