Thursday, May 11, 2017

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.  

Thursday, May 4, 2017

Sending request using request library

Using request library for Pirates

 Few years ago using urllib2 was a simplest way for sending and receiving  HTTP Request. But now I'm using  Request library . This  is more simple and powerful. Unfortunately Request is not include in default install and Pip  should be used . 

Installation

 Procedure is really simple  and near to standard :

MacBook-Pro-Hohlov:pirates skhohlov$ virtualenv ./env
New python executable in ./env/bin/python2.7
Also creating executable in ./env/bin/python
Installing setuptools, pip, wheel...done.
MacBook-Pro-Hohlov:pirates skhohlov$ source ./env/bin/activate
(env)MacBook-Pro-Hohlov:pirates skhohlov$ 
(env)MacBook-Pro-Hohlov:pirates skhohlov$ 
(env)MacBook-Pro-Hohlov:pirates skhohlov$ pip install requests
Collecting requests
  Using cached requests-2.13.0-py2.py3-none-any.whl
Installing collected packages: requests
Successfully installed requests-2.13.0
You are using pip version 7.1.2, however version 9.0.1 is available.
You should consider upgrading via the 'pip install --upgrade pip' command.


Few lines of code. 

 Request has a good documentation at : http://docs.python-requests.org/en/master/   It will be nice to check it if you have any misunderstanding.   Lets back to pirates game.  One of the first request is three  requests for selecting  correct segment for playing. List   is a powerful and very important  feature of the python  language.  If you  have worked  with C or C++  you should  know  how  much time requires understanding of pointers and correct using. Python  is doing this in  light and easy to understand way. Good tutorial  is here : https://www.tutorialspoint.com/python/python_lists.htm    
I've created a function  hot to send requests and select proper segment for  future requests: 



 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
def detectdefaultsegment(gamesession, config):
    """ function for detecting default user segment"""
    BaseSegmentURLList = [
    'https://kpppen3s00.plrm.zone/PiratesPpEng3/Segment00/segment.ashx',
    'https://kpppen2s00.plrm.zone/PiratesPpEng2/Segment00/segment.ashx',
    'https://kpppen1s01.plrm.zone/PiratesPPeng/Segment00/segment.ashx']
        
    gamesession.headers.update({'sign-code':config['sign-code'], 'server-method':'GetTouch','client-ver':config['client-ver']})
    for  BUrl in BaseSegmentURLList:        
        answer=gamesession.post(BUrl,data="[\""+config['userid']+"\"]")
        if answer.text[0] is not '!':
            segmenturl = BUrl
    return segmenturl

How its works ?
 Lines  3-6 are  our python list  of the  url.
 Line 8 is adding some header to the request  such as client version , sever method etc.
 Lines 9-12 are python loop:  request is send to each url .  Correct answer is started from  digits (timestamp),  incorrect is started from symbol "!".  Correct URL is returned.
All script code are placed at : https://pastebin.com/ZB4LiXuw 

Looks like I will write only SignIn function. Other part is not interested for me.

Generating Signature and Sign in function. 

Article about  swf working with swf shows how to application "crypting" request  again bad guys. 


def generateSignature(jsonstring, servercommand,config ):
    """Generate checksum for sign-code header.
    Each POST request has this one."""
    matrix_string = "The Matrix has you..."    
    return hashlib.md5((matrix_string+jsonstring+servercommand+config['userid']+config['authkey']).encode('ascii')).hexdigest()

and our first  Sign in function  is look like :


 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
def SignIn(gamesession, config, mainurl):
   """ function for SignIn. This not auth function,
   this function is used for marking for setting up flag
   for first signing in per day. """        
   JSON_STRING = """{"s":{"x":null,"gr":null,"l":"en-US","I'm":false,"s":null,"tg":0,"m":"myemail","ar":null,"de":null,"t":0,"a":false,"n":"nickname","vk":{"b":false},"u":"//cdn01.x-plarium.com/browser/content/portal/common/avatars/default_avatar.jpg","i":"pp24685509","fl":false,"np":false,"p":null,"d":"nickname;null;null;en_US;0;;","tip":null},"mr":"","l":"","i":"0","f":[""],"c":{"ht":"Portal","put":null,"i":1,"v":"1.0"},"t":-180,"rr":"https://plarium.com/en/strategy-games/pirates-tides-of-fortune/"}"""
   parsed_string = json.loads(JSON_STRING)
   print("email:{c[m]}, username:{c[n]}, userid:{c[i]}".format(c=parsed_string["s"]))
   print json.dumps(parsed_string)
   sign =  generateSignature(JSON_STRING, 'SignIn', config)
   gamesession.headers.update({'sign-code':sign, 'server-method':'SignIn','client-ver':config['client-ver'], 'signin-authSeed':config['authSeed'], 'signin-authKey':config['authkey'], 'signin-userId':config['userid']})
   answer = gamesession.post(mainurl,data=JSON_STRING)
   return answer


Main problem is understanding a string "JSON_STRING".  I've sorted out that this string contains nickname , email, link to photo etc.   Everyone  can get this string from  checking headers procedure, I've informed about this one or two weeks ago. Strings #6-8 print  this information.  Those string have debuging purpose only.  Strings #10-12 are preparing request  and sending it to server.
Other server command requires a similar job to our SignIn  function.
I have no interest for this and  I would like to do something  funny and smart  (Ex: Arduino or AI )