Another one MySQL monitor

Detecting MySQL loading

I've seen strange and randomize increasing of the MySQL db loading but mytop did not help to sorted out with this issue. I'm not Mike Tyson and my reaction is more slow then CPU. If I don't catch this dynamically I will catch this statistically.

Main idea

Select active request by cron and write it ti file. After this statistic analyze for long period of time sort this problem out.

Selecting information from MySQL

Please add python mod MySQLdb and set correct credentials up.

import os, MySQLdb

def main():
    """ checking process list and save it to file"""
    LOGFILENAME = 'dbusage.log'
    MYSQLUSERNAME = 'root'
    MYSQLPASSWORD = 'password'
    MYSQLHOST = 'localhost'
    logfile = open(LOGFILENAME, 'a')
    mydb = MySQLdb.connect(host = MYSQLHOST,
                           user = MYSQLUSERNAME,
                           passwd = MYSQLPASSWORD)
    mycursor = mydb.cursor()
    mycursor.execute("""SELECT   TIME_MS, INFO   FROM information_schema.processlist WHERE  COMMAND='Query' ORDER BY TIME_MS DESC  LIMIT 15 ;""")
    sqlresult = mycursor.fetchall()
    for executingtime, sqlrequest  in sqlresult:
        logfile.write(str(time.time()) +';'+str(executingtime)+';'+sqlrequest+'\n')
    logfile.close()
    return 0

if __name__ == "__main__":
    """ collect data about active mysql running sql and save it to file"""
    main()

After this add executing this script by cron daemon.

Analyzing data

I've ignored 'COMMIT' request but you can update it easy. Script analyzes log file and build dictionary with based on command & table and summarize running time .

!-- HTML generated using hilite.me -->

#!/usr/bin/env python
import os
import time
import argparse
from itertools import cycle


def analizesql(sql):
    """ Analize SQL and returt commandand usedtable as sum of two
    string such as COMMITsession """
    sqllastlist = sql.split(' ')
    sqlcommand = sqllastlist[0].rstrip()
    nofound = True
    if sqlcommand == 'UPDATE':
        usedtable = sqllastlist[1]
    elif sqlcommand == 'SELECT':
        sqllistcycle = cycle(sqllastlist)
        nextelement = sqllistcycle.next()
        while nofound:
            thiselement, nextelement = nextelement, sqllistcycle.next()
            if thiselement == 'FROM':
                nofound = False
                usedtable = nextelement
    #  elif sqlcommand == 'COMMIT\n':
    #      sqlcommand
    else:
        usedtable = ''
    return sqlcommand+usedtable


def timeinsecfromstr(timestr):
    """Convert  string such as 1h 1d 1m  to seconds  """
    switcher = {
      "M": 60, 
      "H": 3600,
      "D": 86400,
      }
    deltatype =timestr[-1:].capitalize()
    return  int(timestr[:-1])* switcher.get(deltatype)
def main():
    """ """
    parser = argparse.ArgumentParser()
    parser.add_argument('--timedelta', type=str, help='time in format int + M H D', default='1H')
    timeinsec= parser.parse_args().timedelta
    STARTTIMEHOURREPORT = time.time() - timeinsecfromstr(timeinsec)
    logfile = open('./dbusage.log', 'r')
    usagedict = {}
    linecount = 0 
    for line in logfile:
        if float(line.split(';')[0]) > STARTTIMEHOURREPORT:
     linecount+=1
            usagetime = float(line.split(';')[1])
            currentsql = line.split(';')[2]
            commandtable = analizesql(currentsql)
            if usagedict.has_key(commandtable):
                usagedict[commandtable] += usagetime
            else:
                usagedict[commandtable] = usagetime
    usagedict_s = [(usagetime, commandtable) for commandtable, usagetime in usagedict.iteritems()]
    usagedict_s.sort(reverse=True)
    print("="*56)
    print("Total records detected:%s" %(linecount,))
    print("="*56)
    for v, k in usagedict_s:
 print('{0:<35} {1:>20}'.format(k,v/linecount))
    print("="*56)
if __name__ == "__main__":
    main()

Also you can set different period of time for longer checking. All questions are welcomed.

skhohlov

Search This Blog