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
Please reload

Categories
Please reload

Search By Tags
Please reload

Archive
Please reload

Certified Development

We design, build, develop, manage, advise and host Laravel and Magento

Ecomus logo

London, Brighton and the UK

©2008 - 2020 Ecomus Ltd.  All Rights Reserved