Skip to main content

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.


 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
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.  

Comments

Popular posts from this blog

Upgrade postgres 9.4 to 9.6 at Debian linux

I'm using virtual server based on Debian Linux for some my free projects.  One of them is based on Postgres with PostGIS extension.   This windy rainy day is a good time for executing some upgrade. Also I would like to add partman extension for partituoning.
Checking new version. Update package list :
aptitude update Get: 1 http://ftp.debian.org/debian jessie-updates InRelease [145 kB] Get: 2 http://security.debian.org stretch/updates InRelease [62.9 kB] Get: 3 http://security.debian.org jessie/updates InRelease [63.1 kB]
 Check for new version:


dpkg-query -l postgresql* Desired=Unknown/Install/Remove/Purge/Hold| Status=Not/Inst/Conf-files/Unpacked/halF-conf/Half-inst/trig-aWait/Trig-pend|/ Err?=(none)/Reinst-required (Status,Err: uppercase=bad)||/ Name Version Ar…

Postfix can not start via systemd (simple fix)

Solving problem related to systemd process I like postfix.   This is really smart and secure mail server. I'm helping above  dozen clients around the world and  tunning  postfix is really fun task. This morning I was downgrading postfix  to the stable version for one of the my friends and come across interesting issue. 
root@newserver:/etc/init.d# systemctl status postfix ● postfix.service Loaded: masked (/dev/null; bad) Active: inactive (dead) since вт 2017-06-13 14:35:41 EEST; 1h 48min ago Main PID: 25145 (code=exited, status=0/SUCCESS)чер 13 14:47:09 newserver systemd[1]: Stopped postfix.service.чер 13 14:47:29 newserver systemd[1]: Stopped postfix.service.чер 13 14:58:22 newserver systemd[1]: Stopped postfix.service.чер 13 14:58:23 newserver systemd[1]: Stopped postfix.service.чер 13 15:05:20 newserver systemd[1]: Stopped postfix.service.чер 13 15:29:06 newserver systemd[1]: Stopped postfix.service.чер 13 15:29:06 newserver systemd[1]: Stopped postfix.service.чер 13 15:…

Small script for one of the plarium game

few notes about google games.
Goggle informed that access to the all games be closed after June, 30. I played "Pirates: Tides of fortune" and decided to limit my game time using small script. Detail is below.
Some history
First internet games were updated network games only. 15 years ago internet connection requires a lot of money and nobody had a problem with lagging of the other players. Usually games uses personal communication protocol. Warbirds, Aces High and Eve online use this way.
Next part use a browser and trivial HTTP protocol. One of the popular game in this area is travian . Travian player uses browser and every browser (PC, cell phone, tablet) can be used for playing. Of course, popularity of the game is related to graphics. Trivial HTTP does not have good power in this and other technology is used for this side. One of them is Flash . Unfortunately flash requires a lot of CPU : "Pirates" was near to froze at my old celeron with 512M…