Finding long running INNODB transactions

Notes:
1. The script prints out elapsed time since transaction started, MySQL thread id, and the kill statement for transactions running longer than a defined threshold value, in seconds. Just copy, paster, and then execute the kill statement if you want to terminate the long transaction(s);
2. Adjust shellCmd variable;
3. Adjust longRunningThreshold value as needed. It is measured in seconds;
4. No special libraries/modules needed, as long as there is a working mysql client;
5. re module is used for regex processing. Good place to find examples of regular expression search and grouping. A status variable is used to assist locating MySQL thread id once a transaction running longer than the defined threshold is found.

import re, shlex, subprocess 

def runCmd(cmd):
    proc = subprocess.Popen(shlex.split(cmd), stdout=subprocess.PIPE, stderr=subprocess.PIPE, shell=False)
    out, err = proc.communicate()
    ret = proc.returncode
    return (ret, out, err)

shellCmd = """mysql -h hostName -e "show innodb status\\G""""
longRunningThreshold = 600

returnCode, stdOut, stdErr = runCmd(shellCmd)

targetTransactionFound = False
for line in stdOut.split('\n'):
    if targetTransactionFound:
        match = re.search(r'^MySQL\sthread\sid\s(\d+),', line)
        if match:
            print 'MySQL thread id', match.group(1), ' has been running for ', secondsTransactionElapsed, ' seconds'
            print 'To kill it, run: kill', match.group(1)
            targetTransactionFound = False
    else:
        match = re.search(r'^---TRANSACTION\s\w+,\sACTIVE\s(\d+)\ssec', line)
        if match:
            if (long(match.group(1)) > longRunningThreshold):
                targetTransactionFound = True
                secondsTransactionElapsed = match.group(1)

3 Comments »

  1. Vasil Dimov Said,

    May 31, 2011 @

    Hello,

    Have you tried extracting the data from the 3 INFORMATION_SCHEMA InnoDB tables about transactions: innodb_trx, innodb_locks and innodb_lock_waits? They are described in http://dev.mysql.com/doc/innodb-plugin/1.0/en/innodb-information-schema-transactions.html

    Thanks!

  2. Finding and killing long running InnoDB transactions with Events | Mark Leith Said,

    May 31, 2011 @

    [...] seen a number of solutions for finding long running transactions or sessions within InnoDB / MySQL now. [...]

  3. Haidong Ji Said,

    May 31, 2011 @

    Thanks Vasil for the link.

    It looks interesting. I will find some time to dig into it. Mark Leith’s blog post on the same subject is also pretty interesting.

RSS feed for comments on this post · TrackBack URI

Leave a Comment