#!/bin/env python
# -*- encoding:utf8 -*-
#
# Author:Skate
# Time:2014/12/01
# Function: database capaticy model
from __future__ import division
import os
import lib
import socket
import subprocess, datetime, time, signal
import argparse
#import MySQLdb
ret={}
class database:
def __init__(self,host, db, user, passwd,dbport):
con = None
try:
con = MySQLdb.connect(host, user, passwd, db,port)
except MySQLdb.Error, e:
print "MySQL Connect Error: %s" % (e.args[1])
return con
def hostname():
hostName=os.popen('hostname').readline().strip()
ret['hostName'] = hostName
def ipaddr():
csock = socket.socket(socket.AF_INET, socket.SOCK_DGRAM)
csock.connect(('8.8.8.8', 80))
(addr, port) = csock.getsockname()
csock.close()
ipfix=addr
ipaddr = os.popen("ifconfig | grep -v 127.0.0.1 | grep 'inet addr:'| awk '{print $2}'| awk -F: '{print $2}'").readlines()
vipaddr=[]
for ip in ipaddr:
ip = ip.strip()
if ip != ipfix :
vipaddr.append(ip)
ret['ipfix'] = ipfix
ret['vipaddr'] = vipaddr
def swapPage():
si = os.popen("vmstat 2 1 | grep -v procs | grep -v swpd | awk '{print $9}'").readline().strip()
so = os.popen("vmstat 2 1 | grep -v procs | grep -v swpd | awk '{print $10}'").readline().strip()
ret['si'] = si
ret['so'] = so
def load():
loadAvg=os.popen("cat /proc/loadavg | awk '{print $1}'").readline().strip()
ret['load'] = loadAvg
def mysqlResponse(port):
cmd ='%s' %lib.TCPRStAT + " -f '%M\\t%99a\\n' -p " + '%d' %(port) + ' -n 5 -t 1 -l ' + " `/sbin/ifconfig | grep 'addr:[^ ]\+' -o | cut -f 2 -d: | xargs echo | sed -e 's/ /,/g'` > " + '%s' %lib.Tcpdump_Status
os.popen(cmd)
res_max,res_avg = 0,0
with open(lib.Tcpdump_Status,'r') as f:
for line in f:
res_t = line.split()
if ( res_t[0] > res_max ) and ( res_t[0] != 'max'):
res_max = res_t[0]
if ( res_t[1] > res_avg ) and ( res_t[1] != '99_avg'):
res_avg = res_t[1]
ret['res_max'] = res_max
ret['res_avg'] = res_avg
def capaticyRatio(tiops,maxlatency,minlatency,device):
cmd_bi = 'iostat -tdmx 1 2 | grep ' + '%s' %device + "|sed -n '2,$p' | awk '{print $4}'"
cmd_bo = 'iostat -tdmx 1 2 | grep ' + '%s' %device + "|sed -n '2,$p' | awk '{print $5}'"
bi = os.popen(cmd_bi).readline().strip()
bo = os.popen(cmd_bo).readline().strip()
if bi == '':
bi=0
if bo == '':
bo=0
print bi,bo
bi = int(bi)
bo = int(bo)
if int(ret['res_max']) < maxlatency and int(ret['res_avg']) < minlatency:
capaticyratio = '%.2f' %(int(bi + bo)/int(tiops))
capaticynote = 'Database response time is very good!'
elif int(ret['res_max']) < maxlatency and int(ret['res_avg']) > minlatency:
capaticyratio = '%.2f' %(int(bi + bo)/int(tiops))
capaticynote = 'Database response time is not good,The performance of the database began to decay.'
elif int(ret['res_max']) > maxlatency and int(ret['res_avg']) < minlatency:
capaticyratio = '%.2f' %(int(bi + bo)/int(tiops))
capaticynote = 'Database response time(99%) is very good,But 1% of the requests is greater than 50ms.'
elif int(ret['res_max']) > maxlatency and int(ret['res_avg']) > minlatency:
capaticyratio = '1'
capaticynote = 'Database capaticy overfolw,The database is currently suffering a performance bottleneck!'
ret['capaticyratio'] = capaticyratio
ret['capaticynote'] = capaticynote
ret['iopstotal'] = tiops
ret['bi'] = bi
ret['bo'] = bo
def timeout_cmd(command, timeout):
"""call shell-command and either return its output or kill it
if it doesn't normally exit within timeout seconds and return None"""
cmd = command.split(" ")
start = datetime.datetime.now()
process = subprocess.Popen(cmd, stdout=subprocess.PIPE, stderr=subprocess.PIPE)
while process.poll() is None:
time.sleep(0.2)
now = datetime.datetime.now()
if (now - start).seconds> timeout:
os.kill(process.pid, signal.SIGKILL)
os.waitpid(-1, os.WNOHANG)
return None
return process.stdout.readlines()
def captureSql(port):
cmd_tcpdump='tcpdump -s 65535 -x -nn -q -tttt -i any -c 5000 port %d > %s' %(port,lib.Log_Capturesql_Tcp)
timeout_cmd(cmd_tcpdump,10)
cmd_pt_query_digest = '%s --type tcpdump %s > %s' %(lib.PT_QUERY_DIGEST,lib.Log_Capturesql_Tcp,lib.Log_Capturesql_Sql)
os.popen(cmd_pt_query_digest)
ret['Log_Capturesql'] = lib.Log_Capturesql_Sql
def output():
hostname = ret['hostName']
ipfix = ret['ipfix']
vipaddr = ret['vipaddr']
si = ret['si']
so = ret['so']
load = ret['load']
bi = ret['bi']
bo = ret['bo']
capaticyratio = float(ret['capaticyratio'])
capaticynote = ret['capaticynote']
iopstotal = ret['iopstotal']
res_max = ret['res_max']
res_avg = ret['res_avg']
Log_Capturesql = ret['Log_Capturesql']
print ""
print "hostname is: %s" %hostname
print "IP address is: %s" %ipfix
print "VIP address is: %s" % vipaddr
print "The server performance index:"
print " swap usage: si:%s so:%s " %(si,so)
print " iops : bi:%s bo:%s" %(bi,bo)
print " load: %s" %load
print " Reponse max time: %sμs" %res_max
print " Reponse avg time: %sμs" %res_avg
print " Database Capaticy: %.2f%% Total Capaticy: %s iops" %(capaticyratio*100,iopstotal)
print " Database Capaticy Notes: %s" %capaticynote
print ""
print "capture sql log path:%s" %Log_Capturesql
print ""
if __name__ == '__main__' :
parser = argparse.ArgumentParser("Database Capaticy Program usage v0.1 ,(C) Copyright Skate 2014")
parser.add_argument('--port', action='store', dest='port',type=int,default='3306',required=True,
help=" = the monitor port")
parser.add_argument('--iops', action='store', dest='iops',type=int,default='100',required=True,
help=" = The database total capaticy")
parser.add_argument('--maxlatency', action='store', dest='maxlatency',type=int,default='500000',required=True,
help=" = The maximum response time(microsecond)")
parser.add_argument('--minlatency', action='store', dest='minlatency',type=int,default='200000',required=True,
help=" = The best response time of datatase(microsecond)")
parser.add_argument('--load', action='store', dest='load',default='',
help=" = The data is loaded into monitor database,eg: \n load=user/pass@ip:port:dbname")
parser.add_argument('--device', action='store', dest='device',default='/dev/sda',required=True,
help=" = The disk partition")
results = parser.parse_args()
hostname()
ipaddr()
swapPage()
load()
port = results.port
capaticy = results.iops
maxlatency = results.maxlatency
minlatency = results.minlatency
device = results.device
load = results.load
if (load !=''):
user_info,url = load.split("@")
host,dbport,db = url.split(":")
user,passwd = user_info.split("/",1)
print host,port,db,user,passwd
mysqlResponse(port)
capaticyRatio(capaticy,maxlatency,minlatency,device)
captureSql(port)
output()
