MySQL 5.6Big query and large transaction monitoring script (Python 2)

Can be configured in Zabbix as a template for monitoring

#!/ usr/bin/env python#import MySQLdb,MySQLdb.cursorsimport sys,timefrom datetime import datetimeinnodb_lock_output_file = '/tmp/innodb_lock_output.log'# socket_dir = '/var/lib/mysql/mysql.sock'time_step = 1db_host = '127.0.0.1'db_port = 23306db_user = 'zabbix'db_pass = 'l8ka65'f = open(innodb_lock_output_file,'a')current_time_stamp = int(time.time()) - time_stepcurrent_time = time.ctime()result = ''# print sys.argvif len(sys.argv) 2: print "Usage: %s current_lock | current_running" % sys.argv[0] exit()db = MySQLdb.connect(host=db_host, user=db_user, passwd=db_pass, charset='utf8', port = db_port # unix_socket=socket_dir )conn = db.cursor(MySQLdb.cursors.DictCursor)db.select_db('information_schema')now_time_sql = 'select now() as now_time;'conn.execute(now_time_sql)current_time = conn.fetchall()[0]['now_time']result += str(current_time)result += '\n'lock_sql = ''' SELECT * FROM INNODB_TRX where TIMESTAMPDIFF(SECOND, trx_started, now()) > 1 ORDER BY trx_started LIMIT 1 '''running_sql = '''select user,host,db,time,State,info from PROCESSLIST where TIME > 30 and COMMAND 'Sleep' and COMMAND 'Binlog Dump' and user 'system user' and lower(info) not like '%alter%table%' order by TIME DESC LIMIT 1 '''if sys.argv[1] == 'current_lock': conn.execute(lock_sql) query_result = conn.fetchall() locks = conn.rowcount if locks > 0: cur_time = datetime.now() print (cur_time - query_result[0]['trx_started']).seconds else: print 0 # print result for item in query_result: for each in item: # print each result += str(each) result += '\t' result += ':==>>>>\t' result += str(item[each]) result += '\n' result += '\n' result += '\n' # print result if locks > 0: f.write(result)elif sys.argv[1] == 'current_running': conn.execute(running_sql) query_result = conn.fetchall() thread_count = conn.rowcount if thread_count > 0 : f.write(result) for item in conn.fetchall(): f.write(str(item) + '\n') f.write('\n\n\n\n') print query_result[0]['time'] else: print 0else: print "Usage: %s current_lock | current_running" % sys.argv[0]conn.close()db.close()f.close()

execute scripts

# python innodb_lock_monitor.py current_running# python innodb_lock_monitor.py current_lock

Slow query statements are recorded in a text file

]# tail -300 /tmp/innodb_lock_output.logblocking_trx_state :==>>>> RUNNINGrequesting_SQL :==>>>> delete who_cart,who_cart_ext from who_cart left join who_cart_ext on who_cart.rec_id = who_cart_ext.cart_id where who_cart.rec_id=1469638027