In this tutorial we are going to help you limit mysql queries execution time.

Sometimes , mysql queries last more time than usual or expected , due to poor sql connection scripts. These queries create load and sometimes huge loads!

So what we did about that. Used the pt-kill script from persona toolkit

www.percona.com/doc/percona-toolkit/2.1/pt-kill.html‎

 

---

 

SSH login to your server

#Go to /usr/bin or where ever your path works ( echo $path to see it )

Cd /usr/bin

# download pt-kill

wget www.percona.com/get /pt-kill

# make it executable

chmod +x pt-kill

# create the startup file

nano /etc/init.d/pt-kill

# here it opens the editor to insert start and stop scripts

# there are several commands to use with pt-kill you can fine theme #in the persona website. Our script check for a user database #connections and kill them if they run more than 60 sec. Also log #killed queries and send an email

#Script start here ----------------------------------

#!/bin/sh

#

# pt-kill   This shell script takes care of starting and stopping

#               the pt-kill services.

#

# chkconfig: - 60 20

# description: pt-kill stops long running MySQL queries

#

# probe: true

# Source function library.

. /etc/rc.d/init.d/functions

RETVAL=0

# See how we were called.

case "$1" in

start)

   echo -n $"Starting pt-kill: "

   pt-kill \

     --pid /var/run/pt-kill.pid \

     --daemonize \

     --interval 5 \

     --busy-time 60 \

     --wait-after-kill 15 \

     --user databaseusername \

     --password databasepassword \

     --log /var/log/mysql-kill.log \

     --print \

     --execute-command '(echo "Subject: pt-kill query found on `hostname`"; tail -1 /var/log/mysql-kill.log)|/usr/sbin/sendmail -t Αυτή η διεύθυνση ηλεκτρονικού ταχυδρομείου προστατεύεται από τους αυτοματισμούς αποστολέων ανεπιθύμητων μηνυμάτων. Χρειάζεται να ενεργοποιήσετε τη JavaScript για να μπορέσετε να τη δείτε.' \

     --kill-query

   RETVAL=$?

   echo

   [ $RETVAL -ne 0 ] && exit $RETVAL

;;

stop)

       # Stop daemons.

            echo -n $"Shutting down pt-kill: "

       killproc pt-kill

       echo

      ;;

restart)

      $0 stop

       $0 start

       ;;

*)

     echo $"Usage: pt-kill {start|stop}"

       RETVAL=3

       ;;

esac

exit $RETVAL

#Script ends here ----------------------------------

Change databaseusername with your mysql username

Change databasepassword with your mysql database password

Change busy-time with your time to end mysql queries ( in our example is 60 sec )

Put your email in the report email

Save it and check it !

# run script

/etc/init.d/ pt-kill start

#If complains about not executable etc run

chmod +x /etc.init.d/pt-kill

# if you want to start auto when server reboots run

chkconfig –level 345 pt-kill on

 This article was created by greek-hosting.gr . Fill free to reproduce it but please add a link to us