Monitor MySQL InnoDB locks

monitor.py

#!/usr/bin/env python
# coding: utf-8

# Copyright © 2017, figo GmbH
# This work is free. You can redistribute it and/or modify it under the
# terms of the Do What The Fuck You Want To Public License, Version 2,
# as published by Sam Hocevar. See the COPYING file or http://www.wtfpl.net/
# for more details.

import argparse
import csv
import socket
import sys
import time
import urlparse


for modulename in ['pymysql', 'MySQLdb']:
    try:
        mysql = __import__(modulename)
        break
    except ImportError:
        pass
else:
    sys.stderr.write("error: Missing module 'mysql' or 'pymysql'\n")
    sys.stderr.write("error: Install python-mysql or pymysql\n")
    sys.stderr.flush()
    sys.exit(2)


QUERY = """
SELECT now() AS "now", lck.*, trx.*
FROM   information_schema.innodb_locks          AS `lck`
       INNER JOIN information_schema.innodb_trx AS `trx`
       ON (lck.lock_trx_id = trx.trx_id)
"""


def get_port(port):
    try:
        return int(port)
    except ValueError:
        pass

    try:
        return socket.getservbyname(port)
    except socket.error:
        pass

    raise ValueError("Invalid port or protocol name {!r}".format(port))


def run(conn, output, freq):
    writer = None
    while True:
        cur = conn.cursor()
        try:
            cur.execute(QUERY)
            if writer is None:
                writer = csv.DictWriter(output, fieldnames=[c[0] for c in cur.description])
                writer.writeheader()

            result = cur.fetchone()
            while result is not None:
                data = {c[0]: r for c, r in zip(cur.description, result)}
                writer.writerow(data)
                result = cur.fetchone()
        finally:
            cur.close()
        time.sleep(freq)

if __name__ == '__main__':
    parser = argparse.ArgumentParser(description="Monitors innodb locks")
    parser.add_argument('--output', '-o', default=sys.stdout, type=argparse.FileType('w'))
    parser.add_argument('--frequency', '-f', default=5, help="Polling frequency (in seconds)",
                        type=float)
    parser.add_argument('url', metavar='mysql://login:password@host:port/database')

    args = parser.parse_args()

    parsed_url = urlparse.urlparse(args.url)
    if parsed_url.scheme != 'mysql':
        raise parser.error("We don't support any other database than MySQL")

    login = parsed_url.username or ''
    password = parsed_url.password or ''
    host = parsed_url.hostname or ''
    try:
        port = get_port(parsed_url.port) if parsed_url.port else ''
    except ValueError:
        raise parser.error("Invalid port or service name {!r}".format(parsed_url.port))
    db = parsed_url.path.strip('/') or 'test'

    kwargs = dict(host=host, user=login, passwd=password, db=db, port=port)
    kwargs = {k: v for k, v in kwargs.items() if v}
    sys.stderr.write("Connection args: {!r}\n".format(kwargs))
    connection = mysql.connect(**kwargs)
    try:
        run(connection, args.output, args.frequency)
    finally:
        connection.close()