MyKiller

mykiller.py

# This is a Python script that runs as a daemon and will monitor and
# eventually kill queries that are taking too long.

import os
import time

import pymysql as mysql


timeout_min = 2
timeout_max = 10
loadavg_min = 5
loadavg_max = 15

timeout_diff = timeout_max - timeout_min
loadavg_diff = loadavg_max - loadavg_min

mysql_con = mysql.connect(host='localhost', user='smf', passwd='smf', db='smf')
mysql_cur = mysql_con.cursor(mysql.cursors.DictCursor)

last_killed_query = None

while True:
    time.sleep(1)

    mysql_cur.execute('SHOW FULL PROCESSLIST')
    processes = [process for process in mysql_cur.fetchall()
                 if process['Command'] == 'Query'
                 and process['Info'] != 'SHOW FULL PROCESSLIST']

    if not processes:
        print('zZzZ...')
        continue

    process = sorted(processes,
                     key=lambda process: process['Time'],
                     reverse=True)[0]
    query_parts = process['Info'].split()
    query = ' '.join(query_parts)
    query_verb = query_parts[0].upper()

    if not process['Info']:
        continue

    loadavg = os.getloadavg()[0]
    if loadavg < loadavg_min:
        timeout = timeout_max
    elif loadavg > loadavg_max:
        timeout = timeout_min
    else:
        timeout = (timeout_min +
                   abs(timeout_diff - ((loadavg - loadavg_min) /
                                       loadavg_diff * timeout_diff)))

    load = int(process['Time'] / timeout * 100)
    print(('Current load: %d%%\t\tLongest query: %d secs (max %d secs)'
           '\t\t%d total queries in execution')
          % (load, process['Time'], timeout, len(processes)))

    if ((query_verb == 'SELECT' and process['Time'] > timeout)
            or process['Time'] > timeout * 2):
        if (query_verb in ('REPAIR', 'CHECK', 'OPTIMIZE', 'ALTER')):
            print(('Not killing this query because it is protected from up on '
                   'high by the Prince of Darkness:'))
            print('>>> ' + query)
            continue
        if last_killed_query != process['Id']:
            print('-!- The next query has timed out and will have to die:')
            print('>>> ' + query)
            last_killed_query = process['Id']
            try:
                mysql_cur.execute('KILL QUERY %d' % process['Id'])
            except:
                pass
        else:
            print('-!- That query is refusing to die...')