原文链接:http://blog.csdn.net/ylqmf/article/details/7878498
利用值班时间用python写了个mysql 实时监控脚本,使用前要确认安装python和mysqldb:
yum install python MySQL-python -y
直接执行脚本就可以了 python monitor.py
下面是执行后的结果,硬盘和网卡监控尚未加入:
'''
Created on 2012-8-16
MySQL real time status
@author: tudou@b2c.xiaomi.com
'''
import MySQLdb,os,time
from decimal import Decimal
mysql_host='localhost'
unix_socket='/tmp/mysql.sock'
mysql_user='root'
mysql_pwd='123456'
mysql_db='test'
disk_list=['sda']
sleep_time=2
class mysqlrealtimestatus(object):
def __init__(self,conf):
self.conf=conf
self.db=db(conf)
self.previoustatus=None
self.nextstatus=None
self.previousdisk=None
self.nextdisk=None
def run(self):
while 1:
i=os.system('clear')
self.getstatus()
time.sleep(self.conf['sleep']);
def getstatus(self):
self.previoustatus = self.nextstatus
sql = "show global status;"
self.nextstatus = dict(self.db.execute(sql))
#print self.nextstatus
sql="show full processlist;"
set = self.db.execute(sql,'dict')
self.now = time.strftime('%H:%M:%S',time.localtime(time.time()))
if self.previoustatus!=None and long(self.nextstatus['Uptime_since_flush_status'])>long(self.previoustatus['Uptime_since_flush_status']):
self.computer();
print('==========================slow sql==========================')
#mysqlrealtimestatus.printl(('id','user','host','db','command','time','state','info'),8)
for process in set:
if str(process['Command'])=='Query' and int(process['Time'])>2:
print('Id:'+str(process['Id'])+'\t'+
'User:'+str(process['User'])+'\t'+
'Host:'+str(process['Host'])+'\t'+
'db:'+str(process['db'])+'\t'+
'Command:'+str(process['Command'])+'\t'+
'Time:'+str(process['Time'])+'\t'+
'State:'+str(process['State']))
print('Info:'+str(process['Info']))
print('---------------------------------------------------------------------------------')
def computer(self):
ops=Decimal(self.relcount('Questions'))/Decimal(self.relcount('Uptime_since_flush_status'))
tps=(Decimal(self.relcount('Com_commit'))+Decimal(self.relcount('Com_rollback')))/Decimal(self.relcount('Uptime_since_flush_status'))
sps=Decimal(self.relcount('Com_select')+self.relcount('Qcache_hits'))/Decimal(self.relcount('Uptime_since_flush_status'))
ips=Decimal(self.relcount('Com_insert')+self.relcount('Com_insert_select'))/Decimal(self.relcount('Uptime_since_flush_status'))
ups=Decimal(self.relcount('Com_update')+self.relcount('Com_update_multi'))/Decimal(self.relcount('Uptime_since_flush_status'))
dps=Decimal(self.relcount('Com_delete')+self.relcount('Com_delete_multi'))/Decimal(self.relcount('Uptime_since_flush_status'))
rps=Decimal(self.relcount('Com_replace')+self.relcount('Com_replace_select'))/Decimal(self.relcount('Uptime_since_flush_status'))
bsent_ps=Decimal(self.relcount('Bytes_sent'))/Decimal(self.relcount('Uptime_since_flush_status'))
if(bsent_ps<0):
bsent_ps=Decimal(self.status['Bytes_sent'])/Decimal(self.status['Uptime_since_flush_status'])
breceived_ps=Decimal(self.relcount('Bytes_received'))/Decimal(self.relcount('Uptime_since_flush_status'))
if(breceived_ps<0):
breceived_ps=Decimal(self.status['Bytes_received'])/Decimal(self.status['Uptime_since_flush_status'])
if Decimal(self.relcount('Innodb_buffer_pool_read_requests'))>0:
ib_read_hits=1-Decimal(self.relcount('Innodb_buffer_pool_reads')+self.relcount('Innodb_buffer_pool_read_ahead'))/Decimal(self.relcount('Innodb_buffer_pool_read_requests'))
else:
ib_read_hits=1
ib_used_percent=1-Decimal(self.nextstatus['Innodb_buffer_pool_pages_free'])/Decimal(self.nextstatus['Innodb_buffer_pool_pages_total'])
ib_dirty_page_percent=Decimal(self.nextstatus['Innodb_buffer_pool_pages_dirty'])/Decimal(self.nextstatus['Innodb_buffer_pool_pages_total'])
if(self.nextstatus.has_key('Innodb_row_lock_waits')):
ir_lock_waits_ps=Decimal(self.relcount('Innodb_row_lock_waits'))/Decimal(self.relcount('Uptime_since_flush_status'))
else:
ir_lock_waits_ps=0
if(self.relcount('Questions')>0):
sq_percent=Decimal(self.relcount('Slow_queries'))/Decimal(self.relcount('Questions'))
else:
sq_percent=0
sq_ps=Decimal(self.relcount('Slow_queries'))/Decimal(self.relcount('Uptime_since_flush_status'))
if(self.relcount('Created_tmp_tables')>0):
td_percent=Decimal(self.relcount('Created_tmp_disk_tables'))/Decimal(self.relcount('Created_tmp_tables'))
else:
td_percent=0
opened_tables_ps=Decimal(self.relcount('Opened_tables'))/Decimal(self.relcount('Uptime_since_flush_status'))
if(self.nextstatus.has_key('Opened_files')):
opened_files_ps=Decimal(self.relcount('Opened_files'))/Decimal(self.relcount('Uptime_since_flush_status'))
else:
opened_files_ps=0
if(self.relcount('Connections')>0):
thread_cache_hits=1-Decimal(self.relcount('Threads_created'))/Decimal(self.relcount('Connections'))
else:
thread_cache_hits=1
mysqlrealtimestatus.printl(('time','ops','tps','sps','ips','ups','dps','rps','bsps','brps','%ihpct','%upct','%dpct','ilwps','%sqpct','%tdpct','ofps','%tcpct'))
mysqlrealtimestatus.println((self.now,
mysqlrealtimestatus.dFormat(ops),
mysqlrealtimestatus.dFormat(tps),
mysqlrealtimestatus.dFormat(sps),
mysqlrealtimestatus.dFormat(ips),
mysqlrealtimestatus.dFormat(ups),
mysqlrealtimestatus.dFormat(dps),
mysqlrealtimestatus.dFormat(rps),
mysqlrealtimestatus.dFormat(bsent_ps),
mysqlrealtimestatus.dFormat(breceived_ps),
mysqlrealtimestatus.perF(ib_read_hits),
mysqlrealtimestatus.perF(ib_used_percent),
mysqlrealtimestatus.perF(ib_dirty_page_percent),
mysqlrealtimestatus.dFormat(ir_lock_waits_ps),
mysqlrealtimestatus.perF(sq_percent),
mysqlrealtimestatus.perF(td_percent),
mysqlrealtimestatus.dFormat(opened_files_ps),
mysqlrealtimestatus.perF(thread_cache_hits)
))
#i=os.system('dstat -cglmpdy --tcp')
loadavg=self.load_stat()
mem=self.memory_stat()
swap=self.swap_stat()
self.previousdisk=self.nextdisk
self.nextdisk=self.disk_stat()
mysqlrealtimestatus.printl(('time','lavg1','lavg5','lavg15','mTotal','mUsed','Buffer','Cached','mFree','swapt','swapu',),8)
mysqlrealtimestatus.println((self.now,
mysqlrealtimestatus.dFormat(loadavg['lavg_1']),
mysqlrealtimestatus.dFormat(loadavg['lavg_5']),
mysqlrealtimestatus.dFormat(loadavg['lavg_15']),
mysqlrealtimestatus.dFormat(Decimal(str(mem['MemTotal']))),
mysqlrealtimestatus.dFormat(Decimal(str(mem['MemUsed']))),
mysqlrealtimestatus.dFormat(Decimal(str(mem['Buffers']))),
mysqlrealtimestatus.dFormat(Decimal(str(mem['Cached']))),
mysqlrealtimestatus.dFormat(Decimal(str(mem['MemFree']))),
mysqlrealtimestatus.dFormat(Decimal(str(swap['swapt']))*1024),
mysqlrealtimestatus.dFormat(Decimal(str(swap['swapu']))*1024)
),8)
#print
#!/usr/bin/env python
def load_stat(self):
loadavg = {}
f = open("/proc/loadavg")
con = f.read().split()
f.close()
loadavg['lavg_1']=Decimal(con[0])
loadavg['lavg_5']=Decimal(con[1])
loadavg['lavg_15']=Decimal(con[2])
return loadavg
#!/usr/bin/env python
def memory_stat(self):
mem = {}
f = open("/proc/meminfo")
lines = f.readlines()
f.close()
for line in lines:
if len(line) < 2: continue
name = line.split(':')[0]
var = line.split(':')[1].split()[0]
mem[name] = long(var) * 1024.0
mem['MemUsed'] = mem['MemTotal'] - mem['MemFree'] - mem['Buffers'] - mem['Cached']
return mem
def disk_stat(self):
disk=[]
f = open("/proc/diskstats")
lines = f.readlines()
f.close()
for disk_name in disk_list:
for row in lines:
if str(row).find(' '+disk_name+' ')>0:
con=str(row).split(' ')
disk.append({'disk_name':disk_name,'rcount':con[2],'rrcount':con[3],'rdcount':con[3],'rtime':con[4],'wcount':con[5],'rwcount':con[6],'wdcount':con[7],'wtime':con[8],})
break
return disk
def swap_stat(self):
swap={}
f = open("/proc/swaps")
l = f.readlines()
f.close()
con=str(l[1]).split('\t')
swap['swapt']=con[1]
swap['swapu']=con[2]
return swap
#!/usr/bin/env python
def net_stat(self):
net = []
f = open("/proc/net/dev")
lines = f.readlines()
f.close()
for line in lines[2:]:
con = line.split()
intf = {}
intf['interface'] = con[0].lstrip(":")
intf['ReceiveBytes'] = int(con[1])
intf['ReceivePackets'] = int(con[2])
intf['ReceiveErrs'] = int(con[3])
intf['ReceiveDrop'] = int(con[4])
intf['ReceiveFifo'] = int(con[5])
intf['ReceiveFrames'] = int(con[6])
intf['ReceiveCompressed'] = int(con[7])
intf['ReceiveMulticast'] = int(con[8])
intf['TransmitBytes'] = int(con[9])
intf['TransmitPackets'] = int(con[10])
intf['TransmitErrs'] = int(con[11])
intf['TransmitDrop'] = int(con[12])
intf['TransmitFifo'] = int(con[13])
intf['TransmitFrames'] = int(con[14])
intf['TransmitCompressed'] = int(con[15])
#intf['TransmitMulticast'] = int(con[16])
"""
intf = dict(
zip(
( 'interface','ReceiveBytes','ReceivePackets',
'ReceiveErrs','ReceiveDrop','ReceiveFifo',
'ReceiveFrames','ReceiveCompressed','ReceiveMulticast',
'TransmitBytes','TransmitPackets','TransmitErrs',
'TransmitDrop', 'TransmitFifo','TransmitFrames',
'TransmitCompressed','TransmitMulticast' ),
( con[0].rstrip(":"),int(con[1]),int(con[2]),
int(con[3]),int(con[4]),int(con[5]),
int(con[6]),int(con[7]),int(con[8]),
int(con[9]),int(con[10]),int(con[11]),
int(con[12]),int(con[13]),int(con[14]),
int(con[15]),int(con[16]))
)
)
"""
net.append(intf)
return net
def relcount(self,param):
return Decimal(self.nextstatus[param])-Decimal(self.previoustatus[param])
@staticmethod
def println(param,s=7):
p=""
for i in param:
if type(i)==type(""):
p+=i+" "
else:
p+=str(i[0]).ljust(s)
print p
@staticmethod
def printl(param,s=7):
p=""
for i in param:
if str(i)=='time':
p+=str(i)+" "
else:
p+=str(i).ljust(s)
print p
@staticmethod
def perF(param):
return mysqlrealtimestatus.dFormat(param*100)
@staticmethod
def dFormat(val):
k=1024
m=k*k
g=k*m
t=k*g
p=k*t
dp=0
dm=""
if(val!=0):
if(val>p):
dp=p
dm="P"
elif(val>t):
dp=t
dm="T"
elif(val>g):
dp=g
dm="G"
elif(val>m):
dp=m
dm="M"
elif(val>k):
dp=k
dm="k"
else:
dp=1
return ["%2.2f" % (Decimal(val)/Decimal(dp)) +dm]
else:
return ["%2.2f" % 0]
'''
'''
class MySQLHelper(object):
@staticmethod
def getConn(conf):
pot = 3306
if(conf.has_key('port')):
pot=conf['port']
dbname='test'
if(conf.has_key('db')):
dbname=conf['db']
if(conf.has_key('socket')):
return MySQLdb.connect(host=conf['host'],unix_socket=conf['socket'],user=conf['user'],passwd=conf['pwd'],db=dbname)
else:
return MySQLdb.connect(host=conf['host'],port=pot,user=conf['user'],passwd=conf['pwd'],db=dbname)
'''
'''
class db (object):
def __init__(self,conf):
self.conn=None
self.conn=MySQLHelper.getConn(conf)
def execute(self,sql,mod=''):
if(mod=='dict'):
cursor=self.conn.cursor(MySQLdb.cursors.DictCursor)
else:
cursor=self.conn.cursor()
cursor.execute(sql)
set=cursor.fetchall()
return set
def executeNoQuery(self,sql,param={}):
cursor=self.conn.cursor()
try:
if(param=={}):
rownum=cursor.execute(sql)
else:
rownum=cursor.executemany(sql,param)
self.conn.commit()
return rownum
finally:
cursor.close()
def __del__(self):
if (self.conn!=None):
self.conn.close()
if __name__ == '__main__':
conf={'host':mysql_host,'socket':unix_socket,'user':mysql_user,'pwd':mysql_pwd,'db':mysql_db,'sleep':sleep_time}
status=mysqlrealtimestatus(conf);
status.run();
原文链接: http://blog.csdn.net/ylqmf/article/details/7878498