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 percona.com/get/pt-kill</pre>
&nbsp;

This is the full command to monitor a databases select processes
<pre class="brush: sql; gutter: false">pt-kill --busy-time=300 --kill-query
--database=your_db
--host=your_ip
--user=your_user
--create-log-table
--verbose
--log-dsn=D=your_db,h=your_ip,u=tsr,t=pt_kill_log
--match-info=&quot;^(select|SELECT|Select)&quot;
--log=pt_kill.log
--daemonize</pre>
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)

&nbsp;

Further details here: <a href="https://www.percona.com/doc/percona-toolkit/2.1/pt-kill.html">https://www.percona.com/doc/percona-toolkit/2.1/pt-kill.html</a> 

Share on Facebook
Share on Twitter
Please reload

Recent Posts