Coleta de informações sobre possiveis deadlocks no mysql 5.6 minuto a minuto

collect_information_db.py

import pymysql
import os
import json
import logging
from datetime import datetime

def get_connection(secret_dict):

    port = int(secret_dict['port']) if 'port' in secret_dict else 3306
    dbname = secret_dict['dbname'] if 'dbname' in secret_dict else None

    # Try to obtain a connection to the db
    try:
        conn = pymysql.connect(secret_dict['host'], user=secret_dict['username'],
                               passwd=secret_dict['password'], port=port, db=dbname, connect_timeout=5)
        logger.info("get_connection: Connection established")
        return conn
    except pymysql.OperationalError:
        return None


def get_credentials():
    secret_dict = {}
    secret_dict['host'] = os.environ['MYSQL_HOST']
    secret_dict['username'] = os.environ['MYSQL_USERNAME']
    secret_dict['password'] = os.environ['MYSQL_PASSWORD']
    secret_dict['port'] = os.environ['MYSQL_PORT']
    return secret_dict


def get_querys():
    querys = ["SELECT  r.trx_id waiting_trx_id,  r.trx_mysql_thread_id waiting_thread,  r.trx_query waiting_query,  b.trx_id blocking_trx_id,  b.trx_mysql_thread_id blocking_thread,  b.trx_query blocking_query FROM       information_schema.innodb_lock_waits w INNER JOIN information_schema.innodb_trx b  ON b.trx_id = w.blocking_trx_id INNER JOIN information_schema.innodb_trx r  ON r.trx_id = w.requesting_trx_id"]
    querys.append("select * from information_schema.processlist")
    querys.append("SHOW ENGINE INNODB STATUS")
    querys.append("show open tables")
    querys.append("show global status")
    return querys


def handler(event, context):
    conn = get_connection(get_credentials())
    with conn.cursor() as cur:
        for query in get_querys():
            logger.info("QUERY: "+query)
            cur.execute(query)
            for row in cur:
                for i in row:
                    logger.info(i)


if __name__ == "__main__":
    filename='/var/log/collect-information/collect_information_mysql_theads-{:%Y-%m-%d-%H-%M-%S}.log'.format(datetime.now())
    logging.basicConfig(filename=filename, filemode='a', format='%(asctime)s - %(name)s - %(levelname)s - %(message)s')
    logger = logging.getLogger()
    logger.setLevel(logging.INFO)
    #handler1 = logging.StreamHandler(sys.stdout)
    #handler1.setLevel(logging.INFO)
    formatter = logging.Formatter(
        '%(asctime)s - %(name)s - %(levelname)s - %(message)s')
    #handler1.setFormatter(formatter)
    #logger.addHandler(handler1)
    handler(None, None)

consult.sql

    SELECT  r.trx_id waiting_trx_id,  r.trx_mysql_thread_id waiting_thread,  r.trx_query waiting_query,  b.trx_id blocking_trx_id,  b.trx_mysql_thread_id blocking_thread,  b.trx_query blocking_query FROM       information_schema.innodb_lock_waits w INNER JOIN information_schema.innodb_trx b  ON b.trx_id = w.blocking_trx_id INNER JOIN information_schema.innodb_trx r  ON r.trx_id = w.requesting_trx_id;
    select * from information_schema.processlist;
    SHOW ENGINE INNODB STATUS;
    show open tables;
    show global status;

crontab

* * * * * sh /srv/exec_collect_information_db.sh >> /var/log/collect-information/crontab.log 2>&1

exec_collect_information_db.sh

export  MYSQL_HOST=$HOST
export  MYSQL_USERNAME=$USERNAME
export  MYSQL_PASSWORD=$PASSWORD
export  MYSQL_PORT=$PORT
python /srv/collect_information_db.py