mysql 4.0性能_MySQL性能状态分析 4.0 5.1 5.5 mysqlpulse--MySQL Status monitor

默认操作是执行两次show

status,用时约为1分钟,可以通过修改脚本头部的参数来改变

times=2#执行次数

slow=60#间隔秒数

输出内容分为三列

第一列为total值,是服务器启动到当前的统计值,第二列是当前一分钟内的统计值,第三列是可参照的值.tudou@gyyx

[plain] view plaincopy

=============MySQL info=============

Connection id  : 35

Current database  : zabbix

Current user  : root@%

SSL  : DISABLED

MySQL VERSION  : 5.1.59-log MySQL Community

Server (GPL)

MySQL client info  : 5.1.61

Protocol version  : 10

Connection  :

10.14.23.235 via TCP/IP

Server characterset  : latin1

Db  characterset

: latin1

Client characterset  : latin1

Conn.  characterset  : latin1

collation_connection : latin1_swedish_ci

collation_database  : latin1_swedish_ci

collation_server  :

latin1_swedish_ci

Uptime  : 214s

============processlist=============

processlist rowcount =16

status time>0 threads list:

Id  User  Host  db  Command  Time  State  Info

status time>0 threads count=0

=========MySQL status pulse=========

-----------------------------------------------------------------------------------------------------------------------------

Reads/Writes status  total

current

proposal

-----------------------------------------------------------------------------------------------------------------------------

Reads:Writes ratio(Reads/Writes)  : 1:1.73 (631.40k/1.07M)  1:1.99

(163.59k/326.09k)

QPS(Questions/Uptime)  : 8.07k/s (1.69M/214.00)

8.17k/s (489.91k/60.00)

TPS(Questions/Uptime)  : 1.68/s (359.00/214.00)

1.65/s (99.00/60.00)

Table locks waited ratio(waited/immediate): 0:1 (0.00/1.68M)

0:1 (0.00/489.05k)  0:1

select per second(select/Uptime)  : 2.95k/s (631.40k/214.00)  2.73k/s (163.59k/60.00)

insert per second(insert/Uptime)  : 15.52/s (3.24k/214.00)  7.58/s

(455.00/60.00)

update per second(update/Uptime)  : 5.08k/s (1.06M/214.00)  5.43k/s

(325.64k/60.00)

delete per second(delete/Uptime)  : 14.13/s (2.95k/214.00)  0.17/s

(10.00/60.00)

replace per second(replace/Uptime)  : 0.00/s

(0.00/214.00)  0.00/s

(0.00/60.00)

Bytes sent per second(sent/Uptime)  : 1.58M/s

(338.24M/214.00)  1.56M/s (93.52M/60.00)

Bytes received per second(re/Uptime)  : 1.00M/s (214.10M/214.00)

1.04M/s

(62.13M/60.00)

------------------------------------

Slow and Sort queries status

------------------------------------

Slow queries Ratio(Slow/Questions)  : 0.00%

(83.00/1.69M)  0.00% (8.00/489.91k)  Lower than 0

Slow queries PS(Slow/Uptime)  : 0.39/s

(83.00/214.00)  0.13/s

(8.00/60.00)  Lower than 0

Full join PS(full/Uptime)  : 0.00/s

(0.00/214.00)  0.00/s

(0.00/60.00)  Lower than 0

Sort merge passes PS(merge/Uptime)  : 0.00/s

(0.00/214.00)  0.00/s

(0.00/60.00)  Lower than 0

Sort range PS(range/Uptime)  : 0.22/s

(48.00/214.00)  0.12/s

(7.00/60.00)  Lower than 0

Sort rows PS(rows/Uptime)  : 12.11/s

(2.53k/214.00)  10.80/s (648.00/60.00)

Lower than 0

Sort scan PS(scan/Uptime)  : 0.82/s

(176.00/214.00)  0.82/s (49.00/60.00)

Lower than 0

------------------------------------

connections status

------------------------------------

Thread cache hits(created/Total)  : 59.09% (18.00/44.00)  87.50% (1.00/8.00)  Higher than 0

Connections used ratio(Max used/Max)  : 11.92% (18.00/151.00)

0.66% (1.00/151.00)

Lower than

90

Aborted connects ratio(Aborted/Max)  : 0.00% (0.00/151.00)

0.00%

(0.00/151.00)  Lower than 0

Threads running  : 1.00

------------------------------------

temp table and Open tables/files status

------------------------------------

Temp tables to Disk ratio(disk/tmp)  : 9.24% (23.00/249.00)

4.29% (3.00/70.00)

Lower than

0

Open tables/table open cache/Opened tables:

(120.00/512.00/127.00)  (15.00/512.00/15.00)

1:1:1

Opened files PS(Opened/Uptime)  : 1.15/s (246.00/214.00)

0.47/s (28.00/60.00)  Lower than 0

------------------------------------

InnoDB Status

------------------------------------

Innodb buffer read hits(Disk/total)  : 99.96% (7.38k/17.53M)

99.99% (533.00/4.00M)

Higher than 99.99

Innodb dblwr pages written:dblwr writes  :

85.65:1 (16.06k/192.00)  Lower than 64:1

Innodb buffer pages used ratio(free/total): 84.75%

(3.66k/24.00k)  Lower than

99.99

Innodb buffer pages dirty ratio(dirty)  : 35.43% (8.50k/24.00k)

Innodb buffer pages flushed PS(flushed)  :

76.84/s (16.06k/214.00)  93.18/s (5.46k/60.00)

Innodb buffer pool pages misc  : 437.00

Innodb row lock waits PS(waits/Uptime)  : 0.00/s (0.00/214.00)  0.00/s (0.00/60.00)

Innodb row lock current waits  : 0.00  0.00

Innodb row lock time avg  : 0.00  0.00

Innodb row lock time max  : 0.00  0.00

Innodb row lock time total  : 0.00

0.00

Innodb rows read PS(read/Uptime)  : 15.28k/s (3.19M/214.00)  12.93k/s

(775.73k/60.00)

Innodb rows inserted PS(inserted/Uptime)  :

2.96k/s (634.44k/214.00)  2.86k/s (171.32k/60.00)

Innodb rows updated PS(updated/Uptime)  : 5.08k/s (1.06M/214.00)  5.43k/s

(325.64k/60.00)

Innodb data reads PS(reads/Uptime)  : 39.18/s

(8.19k/214.00)  11.40/s (684.00/60.00)

Innodb data writes PS(writes/Uptime)  : 184.47/s (38.55k/214.00)

31.27/s

(1.83k/60.00)

Innodb data fsyncs PS(fsyncs/Uptime)  : 3.61/s (773.00/214.00)

4.12/s (247.00/60.00)

Innodb data pending reads PS(reads/Uptime): 0.00/s (0.00/214.00)

0.00/s (0.00/60.00)

Innodb data pending writes PS(write/Uptime): 0.00/s

(0.00/214.00)  0.00/s

(0.00/60.00)

Innodb data pending fsyncs PS(fsync/Uptime): 0.00/s

(0.00/214.00)  0.00/s

(0.00/60.00)

------------------------------------

key buffer Status

------------------------------------

key buffer used ratio(used/size)  : 0.00% (0.00/384.00M)  Lower than 99.99

key buffer read hit ratio(reads/request)  :

100.00% (0.00/0.00)  100.00% (0.00/0.00)  Higher than 99.99

key buffer write hit ratio(writes/request): 100.00% (0.00/0.00)

100.00%

(0.00/0.00)  Higher than 99.99

------------------------------------

Qcache Status

------------------------------------

Qcache queries hits ratio(hits/reads)  : 0.44% (2.75k/631.40k)  0.53% (883.00/163.59k)  Higher

than 30.00

Qcache hits inserts ratio(hits/inserts)  :

3.18% (2.75k/86.63k)  774.56%

(883.00/114.00)  Higher than 300.00

Qcache memory used ratio(free/total)  : 65.51% (11.04M/32.00M)

Qcache prune ratio(prunes/inserts)  : 25.28%

(21.90k/86.63k)  0.00% (0.00/114.00)

Qcache block Fragmnt ratio(free/total)  : 31.20% (3.33k/10.66k)

------------------------------------

Slave Status

------------------------------------

Slave running status  : OFF

OFF

Slave open temp tables  : 0.00  0.00

Slave transactions PS(transactions/Uptime): 0.00/s (0.00/214.00)

0.00/s (0.00/60.00)

Slave received PS(heartbeats/Uptime)  : 0.00/s (0.00/214.00)

0.00/s (0.00/60.00)

python源码:

[python] view plaincopy

import MySQLdb

import _mysql

import re

import time

import sys

import getpass

from decimal import Decimal

#tudou@gyyx

port=3306

dbname="test"

times=2

slow=60

version=Decimal("5.5")

class tools(object):

@staticmethod

def

println(header="",all="",current="",mark=""):

print header.ljust(42)+": "+all.ljust(30)+"

"+current.ljust(30)+" "+mark

@staticmethod

def dRatio(param1,param2):

p1=param1

p2=param2

re=""

if(p1==0):

if(p2==0):

re="0:0"

else:

re="0:1"

else:

if(p2==0):

re="1:0"

else:

if(p1==p2):

re="1:1"

elif(p1>p2):

re= tools.dFormat(Decimal(p1)/Decimal(p2))+":1"

else:

re="1:"+tools.dFormat(Decimal(p2)/Decimal(p1))

return re+" "+tools.remarks([p1, p2])

@staticmethod

def remarks(params):

#print params

arr=[]

for param in params:

arr.append(tools.dFormat(param))

return "("+"/".join(arr)+")"

@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 mysqlvariables(object):

def

__init__(self,variables):

self.variables=variables

def param(self,param1):

if(self.variables.has_key(param1)):

return

long(self.variables[param1])

else:

if(param1=='table_open_cache'):

return

long(self.variables['table_cache'])

else:

return 0L

def dFormat(self,param1):

return tools.dFormat(self.param(param1))

def

strFormat(self,param1):

if(self.variables.has_key(param1)):

return

self.variables[param1]

else:

if(param1=='character_set_server'):

return 'null'

elif(param1=='table_open_cache'):

return

self.variables['table_cache']

else:

return 'null'

class mysqlstatuscomputer(object):

def

__init__(self,statuslist):

self.statuslist=statuslist

self.length=len(statuslist)

self.second=self.seconds()

def getval(self,param):

if(type(param)==type("")):

return

self.param(param)

elif(type(param)==type([])):

return

self.arange(param)

else:

return

param

def param(self,param1):

if(self.length>1):

if(self.statuslist[0].has_key(param1)):

return

long(self.statuslist[1][param1])-long(self.statuslist[0][param1])

else:

if(param1=='Innodb_buffer_pool_read_ahead' and version>5):

return

(long(self.statuslist[1]['Innodb_buffer_pool_read_ahead_rnd'])+long(self.statuslist[1]['Innodb_buffer_pool_read_ahead_seq']))-(long(self.statuslist[0]['Innodb_buffer_pool_read_ahead_rnd'])+long(self.statuslist[0]['Innodb_buffer_pool_read_ahead_seq']))

elif(param1=='Uptime_since_flush_status'):

return

long(self.statuslist[1]['Uptime'])-long(self.statuslist[0]['Uptime'])

else:

return 0L

else:

if(self.statuslist[0].has_key(param1)):

return

long(self.statuslist[0][param1])

else:

if(param1=='Innodb_buffer_pool_read_ahead' and version>5):

return

long(self.statuslist[0]['Innodb_buffer_pool_read_ahead_rnd'])+long(self.statuslist[0]['Innodb_buffer_pool_read_ahead_seq'])

elif(param1=='Uptime_since_flush_status'):

return long(self.statuslist[0]['Uptime'])

else:

return 0L

def seconds(self):

return self.param('Uptime_since_flush_status')

'''''

param1+param2+....params

'''

def arange(self,params):

re=0

for param in params:

re+=self.getval(param)

return re

'''''

param1+param2

'''

def

add(self,param1,param2):

return self.getval(param1)+self.getval(param2)

'''''

param1-param2

'''

def

sub(self,param1,param2):

return self.getval(param1)-self.getval(param2)

'''''

param1*param2

'''

def

ext(self,param1,param2):

return self.getval(param1)*self.getval(param2)

'''''

param1/param2

'''

def

per(self,param1,param2):

a=self.getval(param1)

b=self.getval(param2)

if(b!=0):

return

Decimal(a)/Decimal(b)

else:

return

Decimal(0)

'''''

param/seconds

'''

def ps(self,param):

val=self.getval(param)

if(self.second>0):

return

Decimal(val)/Decimal(self.second)

else:

return

Decimal(0)

'''''

format param to P T G M

K

'''

def format(self,param):

val=self.getval(param)

return tools.dFormat(val)

def strFormat(self,param):

return self.statuslist[0][param]

'''''

param1:param2

(param1/param2)

'''

def

ratioF(self,param1,param2):

p1=self.getval(param1)

p2=self.getval(param2)

return tools.dRatio(p1, p2)

'''''

param1/param2 %

(param1/param2)

'''

def

perF(self,param1,param2):

return self.format(self.per(param1, param2)*100)

+ "% "+self.remark(param1, param2)

'''''

1-(param1/param2) %

(param1/param2)

'''

def

dperF(self,param1,param2):

return self.format((Decimal(1)-self.per(param1,

param2))*100) + "% "+self.remark(param1, param2)

'''''

param1/(param1+param2) %

(param1/param2)

'''

def

aperF(self,param1,param2):

return

self.format(self.per(param1,self.add(param1, param2))*100) + "%

"+self.remark(param1, param2)

'''''

param/seconds %

(param/seconds)

'''

def psF(self,param):

return self.format(self.ps(param))+"/s

"+self.remark(param, self.second)

'''''

(param1/param2)

'''

def

remark(self,param1,param2):

params=[]

params.append(param1)

params.append(param2)

return self.remarks(params)

'''''

(param1/param2/.../params)

'''

def remarks(self,params):

#print params

arr=[]

for param in params:

arr.append(self.format(self.getval(param)))

return "("+"/".join(arr)+")"

class mysqlstatusmonitor(object):

def __init__(self,conn):

self.inx=times

self.conn=conn

self.statusList=[]

self.statustmpList=[]

self.totalstatus={}

self.totaltmpstatus=()

self.getstatus()

def getstatus(self):

statustmp=()

mysqlstatus={}

sql="show status"

cursor=self.conn.cursor()

for i in range(self.inx):

cursor.execute(sql)

statustmp=cursor.fetchall()

mysqlstatus={}

for row in

statustmp:

mysqlstatus.setdefault(row[0],row[1])

self.statustmpList.append(statustmp)

self.statusList.append(mysqlstatus)

if(i

time.sleep(slow)

else:

self.totalstatus=mysqlstatus

self.totaltmpstatus=statustmp

def getstatusList(self):

return self.statusList

def

getstatustmpList(self):

return self.statustmpList

def gettotalstatus(self):

return self.totalstatus

def

gettotaltmpstatus(self):

return self.totaltmpstatus

class mysqlpulse(object):

def

__init__(self,conn,monitor):

self.conn=conn

self.version=version

cursor=conn.cursor()

sql="select current_user();"

cursor.execute(sql)

self.currentuser=cursor.fetchall()

sql="show variables;"

cursor.execute(sql)

self.variablestmp=cursor.fetchall()

self.variables={}

for row in self.variablestmp:

self.variables.setdefault(row[0],row[1])

#

self.statusList=monitor.getstatusList()

self.statustmpList=monitor.getstatustmpList()

self.statustmp=monitor.gettotaltmpstatus()

self.mysqlstatus=monitor.gettotalstatus()

self.totalcomputer=mysqlstatuscomputer([self.mysqlstatus])

self.computer=mysqlstatuscomputer(self.statusList)

self.mysqlvariables=mysqlvariables(self.variables)

#

#sql="SHOW INNODB STATUS;"

#cursor.execute(sql)

#self.innodbstatus=cursor.fetchall()

#ver=""

#match=re.compile(r'^([\d]+\.[\d]+)').match(set[0][0])

#if match:

#  ver=match.group(1)

#del set

sql="show databases"

cursor.execute(sql)

self.tables=[]

self.databases=cursor.fetchall()

for tmpdatabase in self.databases:

if(str(tmpdatabase[0])!="information_schema" and

str(tmpdatabase[0])!="mysql" and

str(tmpdatabase[0])!="performance_schema"):

sql="show table status from

`"+str(tmpdatabase[0])+"`"

cursor.execute(sql)

tmptables=cursor.fetchall()

for tmptable in tmptables:

tableinfo=[]

tableinfo.append(tmpdatabase[0])

tableinfoindex=len(tmptable)

for idx in range(tableinfoindex):

tableinfo.append(tmptable[idx])

self.tables.append(tableinfo)

#

sql="SHOW PROCESSLIST;"

cursor.execute(sql)

self.processlist=cursor.fetchall()

def abs(self,left,right):

return cmp(left[8],right[8])

def desc(self,left,right):

return cmp(right[8],left[8])

def printstatus(self):

print "============MySQL status============"

print "get status

times="+str(len(self.statustmpList))

for row in self.statustmp:

print

row[0]+"\t:"+row[1]

def

printinnodbstatus(self):

print self.innodbstatus[0][2]

def

printtablestatus(self):

print "------------------------------------"

print "table status"

print "------------------------------------"

print

"db\t\tName\t\tEngine\t\tVersion\t\tRow_format\t\tRows\t\tAvg_row_length\t\tData_length\t\tMax_data_length\t\tIndex_lengtht\t\tData_free\t\tAuto_increment\t\tCreate_time\t\tUpdate_time\t\tCheck_time\t\tCollation\t\tChecksum\t\tCreate_options\t\tComment"

self.tables.sort(cmp=self.desc)

for row in self.tables:

tableinfolen=len(row)

tableinfo=""

for idx in

range(tableinfolen):

tableinfo+=str(row[idx])+"\t\t"

print

tableinfo

def

printprocesslist(self):

print "============processlist============="

print "processlist rowcount

="+str(len(self.processlist))+"\nstatus time>0 threads list:"

print

"Id\tUser\t\tHost\t\t\tdb\t\tCommand\t\tTime\t\tState\tInfo\t"

threadscount=0

for row in self.processlist:

if(str(row[4])!="Sleep" and long(row[5])>1):

print

str(row[0])+"\t"+str(row[1])+"\t\t"+str(row[2])+"\t"+str(row[3])+"\t\t"+str(row[4])+"\t\t"+str(row[5])+"\t\t"+str(row[6])+"\t"+str(row[7])

threadscount+=1

print "status time>0 threads

count="+str(threadscount)

def printmysqlinfo(self):

print "=============MySQL info============="

print "Connection id  :

"+str(self.conn.thread_id())

print "Current database  : "+dbname

print "Current user  :

"+str(self.currentuser[0][0])

print "SSL  : "+self.mysqlvariables.strFormat('have_openssl')

#print "Current pager  : "

#print "Using outfile  : "

#print "Using delimiter  : "

self.version=Decimal(self.mysqlvariables.strFormat('version')[0:3])

print "MySQL VERSION  :

"+self.mysqlvariables.strFormat('version')+"

"+self.mysqlvariables.strFormat('version_comment')

print "MySQL client info  : "+_mysql.get_client_info()

print "Protocol version  : "+str(self.conn.get_proto_info())

print "Connection  : "+self.conn.get_host_info()

print "Server characterset  :

"+self.mysqlvariables.strFormat('character_set_server')

print "Db  characterset  :

"+self.mysqlvariables.strFormat('character_set_database')

print "Client characterset  :

"+self.mysqlvariables.strFormat('character_set_client')

print "Conn.  characterset

:

"+self.mysqlvariables.strFormat('character_set_connection')

print "collation_connection :

"+self.mysqlvariables.strFormat('collation_connection')

print "collation_database  :

"+self.mysqlvariables.strFormat('collation_database')

print "collation_server  :

"+self.mysqlvariables.strFormat('collation_server')

print "Uptime  :

"+self.mysqlstatus['Uptime']+"s"

def

printQcachestatus(self):

if(self.mysqlvariables.strFormat("have_query_cache")=="YES" and

self.mysqlvariables.strFormat("query_cache_type")!="OFF" and

self.mysqlvariables.param("query_cache_size")>0):

print

"------------------------------------"

print

"Qcache Status"

print

"------------------------------------"

tools.println("Qcache queries hits

ratio(hits/reads)",self.totalcomputer.perF("Qcache_hits",

["Com_select", "Qcache_hits"]),self.computer.perF("Qcache_hits",

["Com_select", "Qcache_hits"]),"Higher than 30.00")

tools.println("Qcache hits inserts

ratio(hits/inserts)",self.totalcomputer.perF("Qcache_hits",

"Qcache_inserts"),self.computer.perF("Qcache_hits",

"Qcache_inserts"),"Higher than 300.00")

tools.println("Qcache memory used

ratio(free/total)",self.totalcomputer.dperF("Qcache_free_memory",self.mysqlvariables.param("query_cache_size")))

tools.println("Qcache prune

ratio(prunes/inserts)",self.totalcomputer.perF("Qcache_lowmem_prunes",

"Qcache_inserts"),self.computer.perF("Qcache_lowmem_prunes",

"Qcache_inserts"))

tools.println("Qcache block Fragmnt

ratio(free/total)",self.totalcomputer.perF("Qcache_free_blocks",

"Qcache_total_blocks"))

def

printUptimesinceflushstatus(self):

print

"-----------------------------------------------------------------------------------------------------------------------------"

print "Reads/Writes status  total  current  proposal  "

print

"-----------------------------------------------------------------------------------------------------------------------------"

tools.println("Reads:Writes

ratio(Reads/Writes)",self.totalcomputer.ratioF(["Com_select",

"Qcache_hits"],

["Com_insert","Com_insert_select","Com_update","Com_update_multi","Com_delete","Com_delete_multi","Com_replace","Com_replace_select"]),self.computer.ratioF(["Com_select",

"Qcache_hits"],

["Com_insert","Com_insert_select","Com_update","Com_update_multi","Com_delete","Com_delete_multi","Com_replace","Com_replace_select"]))

tools.println("QPS(Questions/Uptime)",self.totalcomputer.psF("Questions"),self.computer.psF("Questions"))

tools.println("TPS(Questions/Uptime)",self.totalcomputer.psF(["Com_commit","Com_rollback"]),self.computer.psF(["Com_commit","Com_rollback"]))

tools.println("Table locks waited

ratio(waited/immediate)",self.totalcomputer.ratioF("Table_locks_waited","Table_locks_immediate"),self.computer.ratioF("Table_locks_waited","Table_locks_immediate"),"0:1")

tools.println("select per

second(select/Uptime)",self.totalcomputer.psF(["Com_select","Qcache_hits"]),self.computer.psF(["Com_select","Qcache_hits"]))

tools.println("insert per

second(insert/Uptime)",self.totalcomputer.psF(["Com_insert","Com_insert_select"]),self.computer.psF(["Com_insert","Com_insert_select"]))

tools.println("update per

second(update/Uptime)",self.totalcomputer.psF(["Com_update","Com_update_multi"]),self.computer.psF(["Com_update","Com_update_multi"]))

tools.println("delete per

second(delete/Uptime)",self.totalcomputer.psF(["Com_delete","Com_delete_multi"]),self.computer.psF(["Com_delete","Com_delete_multi"]))

tools.println("replace per

second(replace/Uptime)",self.totalcomputer.psF(["Com_replace","Com_replace_select"]),self.computer.psF(["Com_replace","Com_replace_select"]))

tools.println("Bytes sent per

second(sent/Uptime)",self.totalcomputer.psF("Bytes_sent"),self.computer.psF("Bytes_sent"))

tools.println("Bytes received per

second(re/Uptime)",self.totalcomputer.psF("Bytes_received"),self.computer.psF("Bytes_received"))

print "------------------------------------"

print "Slow and Sort queries status"

print "------------------------------------"

tools.println("Slow queries

Ratio(Slow/Questions)",self.totalcomputer.perF("Slow_queries",

"Questions"),self.computer.perF("Slow_queries", "Questions"),"Lower

than 0")

tools.println("Slow queries

PS(Slow/Uptime)",self.totalcomputer.psF("Slow_queries"),self.computer.psF("Slow_queries"),"Lower

than 0")

tools.println("Full join

PS(full/Uptime)",self.totalcomputer.psF("Select_full_join"),self.computer.psF("Select_full_join"),"Lower

than 0")

tools.println("Sort merge passes

PS(merge/Uptime)",self.totalcomputer.psF("Sort_merge_passes"),self.computer.psF("Sort_merge_passes"),"Lower

than 0")

tools.println("Sort range

PS(range/Uptime)",self.totalcomputer.psF("Sort_range"),self.computer.psF("Sort_range"),"Lower

than 0")

tools.println("Sort rows

PS(rows/Uptime)",self.totalcomputer.psF("Sort_rows"),self.computer.psF("Sort_rows"),"Lower

than 0")

tools.println("Sort scan

PS(scan/Uptime)",self.totalcomputer.psF("Sort_scan"),self.computer.psF("Sort_scan"),"Lower

than 0")

print "------------------------------------"

print "connections status"

print "------------------------------------"

tools.println("Thread cache

hits(created/Total)",self.totalcomputer.dperF("Threads_created",

"Connections"),self.computer.dperF("Threads_created",

"Connections"),"Higher than 0")

tools.println("Connections used ratio(Max

used/Max)",self.totalcomputer.perF("Max_used_connections",self.mysqlvariables.param("max_connections")),self.computer.perF("Max_used_connections",self.mysqlvariables.param("max_connections")),"Lower

than 90")

tools.println("Aborted connects

ratio(Aborted/Max)",self.totalcomputer.perF(["Aborted_clients","Aborted_connects"],self.mysqlvariables.param("max_connections")),self.computer.perF(["Aborted_clients","Aborted_connects"],self.mysqlvariables.param("max_connections")),"Lower

than 0")

tools.println("Threads

running",self.totalcomputer.format("Threads_running"))

print "------------------------------------"

print "temp table and Open tables/files status"

print "------------------------------------"

tools.println("Temp tables to Disk

ratio(disk/tmp)",self.totalcomputer.perF("Created_tmp_disk_tables",

"Created_tmp_tables"),self.computer.perF("Created_tmp_disk_tables",

"Created_tmp_tables"),"Lower than 0")

tools.println("Open tables/table open

cache/Opened

tables",self.totalcomputer.remarks(["Open_tables",self.mysqlvariables.param("table_open_cache"),"Opened_tables"]),self.computer.remarks(["Open_tables",self.mysqlvariables.param("table_open_cache"),"Opened_tables"]),"1:1:1")

tools.println("Opened files

PS(Opened/Uptime)",self.totalcomputer.psF("Opened_files"),self.computer.psF("Opened_files"),"Lower

than 0")

def

printInnoDBStatus(self):

print "------------------------------------"

print "InnoDB Status"

print "------------------------------------"

tools.println( "Innodb buffer read

hits(Disk/total)",self.totalcomputer.dperF(["Innodb_buffer_pool_reads","Innodb_buffer_pool_read_ahead"],

"Innodb_buffer_pool_read_requests"),self.computer.dperF(["Innodb_buffer_pool_reads","Innodb_buffer_pool_read_ahead"],

"Innodb_buffer_pool_read_requests"),"Higher than 99.99")

tools.println( "Innodb dblwr pages written:dblwr

writes",self.totalcomputer.ratioF("Innodb_dblwr_pages_written",

"Innodb_dblwr_writes"),"","Lower than 64:1")

tools.println( "Innodb buffer pages used

ratio(free/total)",self.totalcomputer.dperF("Innodb_buffer_pool_pages_free",

"Innodb_buffer_pool_pages_total"),"","Lower than 99.99")

tools.println( "Innodb buffer pages dirty

ratio(dirty)",self.totalcomputer.perF("Innodb_buffer_pool_pages_dirty",

"Innodb_buffer_pool_pages_total"))

tools.println( "Innodb buffer pages flushed

PS(flushed)",self.totalcomputer.psF("Innodb_buffer_pool_pages_flushed"),self.computer.psF("Innodb_buffer_pool_pages_flushed"))

tools.println( "Innodb buffer pool pages

misc",self.totalcomputer.format("Innodb_buffer_pool_pages_misc"))

tools.println( "Innodb row lock waits

PS(waits/Uptime)",self.totalcomputer.psF("Innodb_row_lock_current_waits"),self.computer.psF("Innodb_row_lock_current_waits"))

tools.println( "Innodb row lock current

waits",self.totalcomputer.format("Innodb_row_lock_current_waits"),self.computer.format("Innodb_row_lock_current_waits"))

tools.println( "Innodb row lock time

avg",self.totalcomputer.format("Innodb_row_lock_time_avg"),self.computer.format("Innodb_row_lock_time_avg"))

tools.println( "Innodb row lock time

max",self.totalcomputer.format("Innodb_row_lock_time_max"),self.computer.format("Innodb_row_lock_time_max"))

tools.println( "Innodb row lock time

total",self.totalcomputer.format("Innodb_row_lock_time"),self.computer.format("Innodb_row_lock_time"))

tools.println( "Innodb rows read

PS(read/Uptime)",self.totalcomputer.psF("Innodb_rows_read"),self.computer.psF("Innodb_rows_read"))

tools.println( "Innodb rows inserted

PS(inserted/Uptime)",self.totalcomputer.psF("Innodb_rows_inserted"),self.computer.psF("Innodb_rows_inserted"))

tools.println( "Innodb rows updated

PS(updated/Uptime)",self.totalcomputer.psF("Innodb_rows_updated"),self.computer.psF("Innodb_rows_updated"))

tools.println( "Innodb data reads

PS(reads/Uptime)",self.totalcomputer.psF("Innodb_data_reads"),self.computer.psF("Innodb_data_reads"))

tools.println( "Innodb data writes

PS(writes/Uptime)",self.totalcomputer.psF("Innodb_data_writes"),self.computer.psF("Innodb_data_writes"))

tools.println( "Innodb data fsyncs

PS(fsyncs/Uptime)",self.totalcomputer.psF("Innodb_data_fsyncs"),self.computer.psF("Innodb_data_fsyncs"))

tools.println( "Innodb data pending reads

PS(reads/Uptime)",self.totalcomputer.psF("Innodb_data_pending_reads"),self.computer.psF("Innodb_data_pending_reads"))

tools.println( "Innodb data pending writes

PS(write/Uptime)",self.totalcomputer.psF("Innodb_data_pending_writes"),self.computer.psF("Innodb_data_pending_writes"))

tools.println( "Innodb data pending fsyncs

PS(fsync/Uptime)",self.totalcomputer.psF("Innodb_data_pending_fsyncs"),self.computer.psF("Innodb_data_pending_fsyncs"))

def printkeystatus(self):

print "------------------------------------"

print "key buffer Status"

print "------------------------------------"

tools.println( "key buffer used

ratio(used/size)",self.totalcomputer.perF(self.totalcomputer.ext("Key_blocks_used",self.mysqlvariables.param("key_cache_block_size")),

self.mysqlvariables.param("key_buffer_size")),"","Lower than

99.99")

tools.println( "key buffer read hit

ratio(reads/request)",self.totalcomputer.dperF("Key_reads",

"Key_read_requests"),self.computer.dperF("Key_reads",

"Key_read_requests"),"Higher than 99.99")

tools.println( "key buffer write hit

ratio(writes/request)",self.totalcomputer.dperF("Key_writes",

"Key_write_requests"),self.computer.dperF("Key_writes",

"Key_write_requests"),"Higher than 99.99")

def

printslavestatus(self):

print "------------------------------------"

print "Slave Status"

print "------------------------------------"

tools.println( "Slave running

status",self.totalcomputer.strFormat("Slave_running"),self.computer.strFormat("Slave_running"))

tools.println( "Slave open temp

tables",self.totalcomputer.format("Slave_open_temp_tables"),self.computer.format("Slave_open_temp_tables"))

tools.println( "Slave transactions

PS(transactions/Uptime)",self.totalcomputer.psF("Slave_retried_transactions"),self.computer.psF("Slave_retried_transactions"))

tools.println( "Slave received

PS(heartbeats/Uptime)",self.totalcomputer.psF("Slave_received_heartbeats"),self.computer.psF("Slave_received_heartbeats"))

def

printflushstatus(self):

print "=========MySQL status pulse========="

self.printUptimesinceflushstatus()

if(self.version> Decimal("5")):

self.printInnoDBStatus()

self.printkeystatus()

self.printQcachestatus()

self.printslavestatus()

#self.printtablestatus()

class mysqlmonitor(object):

def __init__(self,dbs):

self.conn=MySQLdb.connect(host=dbs['host'],port=dbs['port'],user=dbs['user'],passwd=dbs['passwd'],db=dbs['db'])

self.monitor=mysqlstatusmonitor(self.conn)

self.pulse=mysqlpulse(self.conn,self.monitor)

def du(self):

self.pulse.printmysqlinfo()

self.pulse.printprocesslist()

self.pulse.printflushstatus()

#printstatus(statustmp, statustmpList)

#printinnodbstatus(innodbstatus)

def __del__( self ):

self.conn.close()

if __name__ == '__main__':

host =

raw_input("Host[localhost]:")

if not host.strip():host =

"localhost"

user =

raw_input("User[root]:")

if not user.strip():user =

"root"

pwd =

getpass.getpass("Password %s@%s:" % (user,host))

dbs={'host':host,'port':port,'user':user,'passwd':pwd,'db':dbname}

monitor=mysqlmonitor(dbs)

monitor.du()