You Must Have A Killer In Your System

Every system can have a slow query that randomly seems to emerge from nowhere. The main threat from these slow queries is that it can unexpectedly bring down your site. Even though the query is not called often (less than one percent), it can definitely harm your site performance. That is why you should have a Killer in your system.  

For example:
Assume a system with 5 MySQL servers handles lots of read/write queries. In addition, let’s assume that each slow query uses the main table, runs for 15 seconds, and that there are 10 such slow queries running simultaneously. It can be assumed that each MySQL server will get at least one of these slow queries. If a write request is called after the slow query, it will block and lock all the consecutive read requests until the slow query and the write request are completed. The system will not be able to respond for at least 15 seconds. The more the slow queries are called, the less responsive the system will be.

It is a good idea to have a script killer that can kill these slow queries when necessary. Since a script killer will sacrifice the page that called the slow query, this concept works well where the slow query is called from very few and less trafficked pages. Killing these queries will allow more productive and more frequently called queries to execute while the slow query is being investigated.

A good kill script command is mk-kill (see http://www.maatkit.org/doc/mk-kill.html). I recommend configuring the script to kill only select queries and not insert/update queries. Killing an insert/update query can result in a corrupted table.

Here’s a configuration suggestion:

/usr/bin/mk-kill
--print
--daemonize
--interval 5
--busy-time 20
--ignore-info '(?i-smx:^insert|^update|^delete)'
--match-info '(?i-xsm:select)'
--log /var/jlog/admin/mk-kill.log
--execute-command '(echo "Subject: mk-kill query found on `hostname`"; tail -1 /var/log/admin/mk-kill.log)|sendmail -t [email protected],[email protected]'
--kill-query
--pid ${pidfile}
-S /var/lib/mysql/mysql.sock

Notes:

• The –ignore-info and –match-info accept Perl regular expressions (see http://www.ryerson.ca/perl/manual/pod/perlre.html).
• The –kill-query will terminate the query but will not kill the connection. This is useful if you are using connection pooling.