Killing long running MySQL queries automatically

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. &nbsp; 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>

Recent Posts
Categories
Search By Tags
Archive

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