import
import_mysql
importre
importtime
importsys
importgetopt
tudou@Gyyx
host="192.168.60.130"
user="dba"
pwd="123456"
port=3306
dbname="test"
classmysqlpulse(object):
def__init__(self,dbs):
self.conn=MySQLdb.connect(host=dbs['host'],port=dbs['port'],user=dbs['user'],passwd=dbs['passwd'],db=dbs['db'])
cursor=self.conn.cursor()
sql="select current_user();"
cursor.execute(sql)
self.currentuser=cursor.fetchall()
sql="show global variables;"
cursor.execute(sql)
self.variablestmp=cursor.fetchall()
self.variables={}
forrowinself.variablestmp:
self.variables.setdefault(row[0],row[1])
#
self.statusList=[]
self.statustmpList=[]
self.statustmp=()
self.mysqlstatus={}
sql="show global status"
inx=1
foriinrange(inx):
cursor.execute(sql)
self.statustmp=cursor.fetchall()
self.mysqlstatus={}
forrowinself.statustmp:
self.mysqlstatus.setdefault(row[0],row[1])
self.statustmpList.append(self.statustmp)
self.statusList.append(self.mysqlstatus)
if(i
time.sleep(5)
#
sql="show engine 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()
fortmpdatabaseinself.databases:
if(str(tmpdatabase[0])!="information_schema"andstr(tmpdatabase[0])!="mysql"andstr(tmpdatabase[0])!="performance_schema"):
sql="show table status from `"+str(tmpdatabase[0])+"`"
cursor.execute(sql)
tmptables=cursor.fetchall()
fortmptableintmptables:
tableinfo=[]
tableinfo.append(tmpdatabase[0])
tableinfoindex=len(tmptable)
foridxinrange(tableinfoindex):
tableinfo.append(tmptable[idx])
self.tables.append(tableinfo)
#
sql="show processlist"
cursor.execute(sql)
self.processlist=cursor.fetchall()
self.Com_select=long(self.mysqlstatus['Com_select'])
self.Qcache_hits=long(self.mysqlstatus['Qcache_hits'])
self.Com_insert=long(self.mysqlstatus['Com_insert'])
self.Com_insert_select=long(self.mysqlstatus['Com_insert_select'])
self.Com_update=long(self.mysqlstatus['Com_update'])
self.Com_update_multi=long(self.mysqlstatus['Com_update_multi'])
self.Com_delete=long(self.mysqlstatus['Com_delete'])
self.Com_delete_multi=long(self.mysqlstatus['Com_delete_multi'])
self.Com_replace=long(self.mysqlstatus['Com_replace'])
self.Com_replace_select=long(self.mysqlstatus['Com_replace_select'])
self.reads=self.Com_select +self.Qcache_hits
self.writes=self.Com_insert +self.Com_insert_select +self.Com_update+self.Com_update_multi +self.Com_delete+self.Com_delete_multi+self.Com_replace+self.Com_replace_select
self.ratio=100.0
if(self.writes!=0):
self.ratio=(float(self.reads)/float(self.writes))*100
self.Threads_created=long(self.mysqlstatus['Threads_created'])
self.Connections=long(self.mysqlstatus['Connections'])
if(self.mysqlstatus.has_key('Innodb_buffer_pool_read_ahead')):
self.Innodb_buffer_pool_read_ahead=long(self.mysqlstatus['Innodb_buffer_pool_read_ahead'])
else:
self.Innodb_buffer_pool_read_ahead=long(self.mysqlstatus['Innodb_buffer_pool_read_ahead_rnd'])+long(self.mysqlstatus['Innodb_buffer_pool_read_ahead_seq'])
self.Innodb_buffer_pool_read_requests=long(self.mysqlstatus['Innodb_buffer_pool_read_requests'])
self.Innodb_buffer_pool_reads=long(self.mysqlstatus['Innodb_buffer_pool_reads'])
self.Created_tmp_tables=long(self.mysqlstatus['Created_tmp_tables'])
self.Created_tmp_disk_tables=long(self.mysqlstatus['Created_tmp_disk_tables'])
self.TDR=0.0
if(self.Created_tmp_tables!=0):
self.TDR=(float(self.Created_tmp_disk_tables)/float(self.Created_tmp_tables))*100
self.Questions=long(self.mysqlstatus['Questions'])
self.Uptime=long(self.mysqlstatus['Uptime_since_flush_status'])
self.Com_commit=long(self.mysqlstatus['Com_commit'])
self.Com_rollback=long(self.mysqlstatus['Com_rollback'])
self.Aborted_clients=long(self.mysqlstatus['Aborted_clients'])
self.Aborted_connects=long(self.mysqlstatus['Aborted_connects'])
self.Slow_queries=long(self.mysqlstatus['Slow_queries'])
self.Select_full_join=long(self.mysqlstatus['Slow_queries'])
self.Select_full_range_join=long(self.mysqlstatus['Select_full_range_join'])
self.Select_range=long(self.mysqlstatus['Select_range'])
self.Select_range_check=long(self.mysqlstatus['Select_range_check'])
self.Select_scan=long(self.mysqlstatus['Select_scan'])
self.Open_tables=long(self.mysqlstatus['Open_tables'])
self.table_open_cache=long(self.variables['table_open_cache'])
self.Opened_tables=long(self.mysqlstatus['Opened_tables'])
self.Max_used_connections=long(self.mysqlstatus['Max_used_connections'])
self.max_connections=long(self.variables['max_connections'])
self.Bytes_received=long(self.mysqlstatus['Bytes_received'])
self.Bytes_sent=long(self.mysqlstatus['Bytes_sent'])
self.Threads_running=long(self.mysqlstatus['Threads_running'])
self.thread_cache_size=long(self.variables['thread_cache_size'])
if(self.variables.has_key('thread_concurrency')):
self.thread_concurrency=long(self.variables['thread_concurrency'])
else:
self.thread_concurrency=0
self.Opened_files=long(self.mysqlstatus['Opened_files'])
self.Innodb_dblwr_pages_written=long(self.mysqlstatus['Innodb_dblwr_pages_written'])
self.Innodb_dblwr_writes=long(self.mysqlstatus['Innodb_dblwr_writes'])
self.innodb_thread_concurrency=long(self.variables['innodb_thread_concurrency'])
self.Innodb_rows_deleted=long(self.mysqlstatus['Innodb_rows_deleted'])
self.Innodb_rows_inserted=long(self.mysqlstatus['Innodb_rows_inserted'])
self.Innodb_rows_read=long(self.mysqlstatus['Innodb_rows_read'])
self.Innodb_rows_updated=long(self.mysqlstatus['Innodb_rows_updated'])
self.Innodb_row_lock_current_waits=long(self.mysqlstatus['Innodb_row_lock_current_waits'])
self.Innodb_row_lock_time=long(self.mysqlstatus['Innodb_row_lock_time'])
self.Innodb_row_lock_time_avg=long(self.mysqlstatus['Innodb_row_lock_time_avg'])
self.Innodb_row_lock_time_max=long(self.mysqlstatus['Innodb_row_lock_time_max'])
self.Innodb_row_lock_waits=long(self.mysqlstatus['Innodb_row_lock_waits'])
self.Innodb_buffer_pool_pages_data=long(self.mysqlstatus['Innodb_buffer_pool_pages_data'])
self.Innodb_buffer_pool_pages_dirty=long(self.mysqlstatus['Innodb_buffer_pool_pages_dirty'])
self.Innodb_buffer_pool_pages_flushed=long(self.mysqlstatus['Innodb_buffer_pool_pages_flushed'])
self.Innodb_buffer_pool_pages_free=long(self.mysqlstatus['Innodb_buffer_pool_pages_free'])
self.Innodb_buffer_pool_pages_misc=long(self.mysqlstatus['Innodb_buffer_pool_pages_misc'])
self.Innodb_buffer_pool_pages_total=long(self.mysqlstatus['Innodb_buffer_pool_pages_total'])
self.Innodb_data_fsyncs=long(self.mysqlstatus['Innodb_data_fsyncs'])
self.Innodb_data_read=long(self.mysqlstatus['Innodb_data_read'])
self.Innodb_data_reads=long(self.mysqlstatus['Innodb_data_reads'])
self.Innodb_data_writes=long(self.mysqlstatus['Innodb_data_writes'])
self.Innodb_data_written=long(self.mysqlstatus['Innodb_data_written'])
self.Innodb_data_pending_fsyncs=long(self.mysqlstatus['Innodb_data_pending_fsyncs'])
self.Innodb_data_pending_reads=long(self.mysqlstatus['Innodb_data_pending_reads'])
self.Innodb_data_pending_writes=long(self.mysqlstatus['Innodb_data_pending_writes'])
self.have_query_cache=self.variables['have_query_cache']
self.query_cache_type=self.variables['query_cache_type']
self.query_cache_size=long(self.variables['query_cache_size'])
self.Qcache_free_blocks=long(self.mysqlstatus['Qcache_free_blocks'])
self.Qcache_free_memory=long(self.mysqlstatus['Qcache_free_memory'])
self.Qcache_inserts=long(self.mysqlstatus['Qcache_inserts'])
self.Qcache_lowmem_prunes=long(self.mysqlstatus['Qcache_lowmem_prunes'])
self.Qcache_not_cached=long(self.mysqlstatus['Qcache_not_cached'])
self.Qcache_queries_in_cache=long(self.mysqlstatus['Qcache_queries_in_cache'])
self.Qcache_total_blocks=long(self.mysqlstatus['Qcache_total_blocks'])
self.Query_cache_hits_ratio=0
self.Qcache_prune_ratio=0
if(self.Qcache_inserts!=0):
self.Query_cache_hits_ratio=float(self.Qcache_hits)/float(self.Qcache_hits+self.Qcache_inserts)*100
self.Qcache_prune_ratio=(float(self.Qcache_lowmem_prunes)/float(self.Qcache_inserts))*100
if(self.mysqlstatus.has_key('Slave_heartbeat_period')):
self.Slave_heartbeat_period=self.mysqlstatus['Slave_heartbeat_period']
else:
self.Slave_heartbeat_period=0.00
self.Slave_open_temp_tables=long(self.mysqlstatus['Slave_open_temp_tables'])
if(self.mysqlstatus.has_key('Slave_received_heartbeats')):
self.Slave_received_heartbeats=self.mysqlstatus['Slave_received_heartbeats']
else:
self.Slave_received_heartbeats=0
self.Slave_retried_transactions=long(self.mysqlstatus['Slave_retried_transactions'])
self.Slave_running=self.mysqlstatus['Slave_running']
self.key_cache_block_size=long(self.variables['key_cache_block_size'])
self.key_buffer_size=long(self.variables['key_buffer_size'])
self.Key_blocks_not_flushed=long(self.mysqlstatus['Key_blocks_not_flushed'])
self.Key_blocks_unused=long(self.mysqlstatus['Key_blocks_unused'])
self.Key_blocks_used=long(self.mysqlstatus['Key_blocks_used'])
self.Key_read_requests=long(self.mysqlstatus['Key_read_requests'])
self.Key_reads=long(self.mysqlstatus['Key_reads'])
self.Key_write_requests=long(self.mysqlstatus['Key_write_requests'])
self.Key_writes=long(self.mysqlstatus['Key_writes'])
self.key_buffer_read_hit_ratio=0
self.key_buffer_write_hit_ratio=0
if(self.Key_read_requests!=0):
self.key_buffer_read_hit_ratio=(1-(float(self.Key_reads)/float(self.Key_read_requests)))*100
if(self.Key_write_requests!=0):
self.key_buffer_write_hit_ratio=(1-(float(self.Key_writes)/float(self.Key_write_requests)))*100
self.Sort_merge_passes=long(self.mysqlstatus['Sort_merge_passes'])
self.Sort_range=long(self.mysqlstatus['Sort_range'])
self.Sort_rows=long(self.mysqlstatus['Sort_rows'])
self.Sort_scan=long(self.mysqlstatus['Sort_scan'])
self.Table_locks_immediate=long(self.mysqlstatus['Table_locks_immediate'])
self.Table_locks_waited=long(self.mysqlstatus['Table_locks_waited'])
self.Table_locks_waited_ratio=0
if(self.Table_locks_immediate!=0):
self.Table_locks_waited_ratio=float(self.Table_locks_waited)/float(self.Table_locks_immediate)
defabs(self,left,right):
returncmp(left[8],right[8])
defdesc(self,left,right):
returncmp(right[8],left[8])
defprintstatus(self):
print"============MySQL status============"
print"get status times="+str(len(self.statustmpList))
forrowinself.statustmp:
printrow[0]+"\t:"+row[1]
defprintinnodbstatus(self):
printself.innodbstatus[0][2]
defprinttablestatus(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)
forrowinself.tables:
tableinfolen=len(row)
tableinfo=""
foridxinrange(tableinfolen):
tableinfo+=str(row[idx])+"\t\t"
printtableinfo
defprintprocesslist(self):
print"============processlist============="
print"processlist rowcount ="+str(len(self.processlist))+"\nstatus time>1 threads list:"
print"Id\tUser\t\tHost\t\t\tdb\t\tCommand\t\tTime\t\tState\tInfo\t"
threadscount=0
forrowinself.processlist:
if(str(row[4])!="Sleep"andlong(row[5])>1):
printstr(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>1 threads count="+str(threadscount)
defprintmysqlinfo(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.variables['have_openssl']
#print "Current pager : "
#print "Using outfile : "
#print "Using delimiter : "
print"MySQL VERSION : "+self.variables['version']+" "+self.variables['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.variables['character_set_server']
print"Db characterset : "+self.variables['character_set_database']
print"Client characterset : "+self.variables['character_set_client']
print"Conn. characterset : "+self.variables['character_set_connection']
print"collation_connection : "+self.variables['collation_connection']
print"collation_database : "+self.variables['collation_database']
print"collation_server : "+self.variables['collation_server']
print"Uptime : "+self.mysqlstatus['Uptime']+"s"
defprintQcachestatus(self):
if(self.have_query_cache=="YES"andself.query_cache_type!="OFF"andself.query_cache_size>0):
print"------------------------------------"
print"Qcache Status"
print"------------------------------------"
print"Qcache queries hits ratio(hits/inserts): "+str(float(self.Qcache_hits)/float(self.reads))+"% ("+str(self.Qcache_hits)+"/"+str(self.reads)+")"
print"Qcache hits inserts ratio(hits/inserts): "+str(self.Query_cache_hits_ratio)+"% ("+str(self.Qcache_hits)+"/"+str(self.Qcache_inserts)+")"
print"Qcache memory used ratio(free/total) : "+ str((1-(float(self.Qcache_free_memory)/float(self.query_cache_size)))*100)+"% ("+str(self.Qcache_free_memory)+"/"+str(self.query_cache_size)+")"
print"Qcache prune ratio(prunes/inserts) : "+ str(self.Qcache_prune_ratio)+"% ("+str(self.Qcache_lowmem_prunes)+"/"+str(self.Qcache_inserts)+")"
print"Qcache block Fragmnt ratio(free/total) : "+ str((float(self.Qcache_free_blocks)/float(self.Qcache_total_blocks))*100)+"% ("+str(self.Qcache_free_blocks)+"/"+str(self.Qcache_total_blocks)+")"
defprintUptimesinceflushstatus(self):
print"------------------------------------"
print"Reads/Writes status"
print"------------------------------------"
print"Reads:Writes ratio : "+str(self.ratio)+"%"
print"QPS/TPS : "+str(float(self.Questions)/float(self.Uptime))+"/s\t"+str((float(self.Com_commit) + float(self.Com_rollback))/float(self.Uptime))+"/s"
print"Table locks waited ratio : "+str(self.Table_locks_waited_ratio)+"%"
print"SQL PS : select="+str(float(self.Com_select+self.Qcache_hits)/float(self.Uptime))+"/s insert="+str(float(self.Com_insert+self.Com_insert_select)/float(self.Uptime))+"/s update="+str(float(self.Com_update+self.Com_update_multi)/float(self.Uptime))+"/s delete="+str(float(self.Com_delete+self.Com_delete_multi)/float(self.Uptime))+"/s replace="+str(float(self.Com_replace+self.Com_replace_select)/float(self.Uptime))+"/s"
print"Bytes sent/received per second : "+str(float(self.Bytes_sent)/float(self.Uptime))+"/s "+str(float(self.Bytes_received)/float(self.Uptime))+"/s"
print"------------------------------------"
print"Slow and Sort queries status"
print"------------------------------------"
print"Slow queries Ratio : "+str(float(self.Slow_queries)/float(self.Questions))+"%"
print"Slow queries per second : "+str(float(self.Slow_queries)/float(self.Uptime))+"/s"
print"Full join per second : "+str(float(self.Select_full_join)/float(self.Uptime))+"/s"
print"Sort per second : merge passes="+str(float(self.Sort_merge_passes)/float(self.Uptime))+"/s range="+str(float(self.Sort_range)/float(self.Uptime))+"/s rows="+str(float(self.Sort_rows)/float(self.Uptime))+"/s scan="+str(float(self.Sort_scan)/float(self.Uptime))+"/s"
print"------------------------------------"
print"connections status"
print"------------------------------------"
print"Thread cache hits(created/cached) : "+str((1-(float(self.Threads_created)/float(self.Connections)))*100)+"% ("+str(self.Threads_created)+"/"+str(self.thread_cache_size)+")"
print"Connections used ratio(Max used/Max) : "+str((float(self.Max_used_connections)/float(self.max_connections))*100)+"%("+str(self.Max_used_connections)+"/"+str(self.max_connections)+")"
print"Aborted connects ratio : "+str((float(self.Aborted_clients+self.Aborted_connects)/float(self.Connections))*100)+"%"
print"Threads running : "+str(self.Threads_running)
print"------------------------------------"
print"temp table and Open tables/files status"
print"------------------------------------"
print"Temp tables to Disk ratio : "+str(self.TDR)+"%"
print"Open tables/table open cache/Opened tables: "+str(self.Open_tables)+"/"+str(self.table_open_cache)+"/"+str(self.Opened_tables)
print"Opened files per second : "+str(float(self.Opened_files)/float(self.Uptime))+"/s"
defprintInnoDBStatus(self):
print"------------------------------------"
print"InnoDB Status"
print"------------------------------------"
print"Innodb buffer read hits(Disk/total) : "+str((1-(float(self.Innodb_buffer_pool_reads+self.Innodb_buffer_pool_read_ahead))/float(self.Innodb_buffer_pool_read_requests))*100)+"% ("+str(self.Innodb_buffer_pool_reads+self.Innodb_buffer_pool_read_ahead)+"/"+str(self.Innodb_buffer_pool_read_requests)+")"
print"Innodb_dblwr_pages_written:Innodb_dblwr_writes : "+str(self.Innodb_dblwr_pages_written/self.Innodb_dblwr_writes)
print"Innodb buffer pages used ratio(free/total) : "+str((1-(float(self.Innodb_buffer_pool_pages_free)/float(self.Innodb_buffer_pool_pages_total)))*100)+"% ("+str(self.Innodb_buffer_pool_pages_free)+"/"+str(self.Innodb_buffer_pool_pages_total)+")"
print"Innodb buffer pages dirty ratio(dirty) : "+str((float(self.Innodb_buffer_pool_pages_dirty)/float(self.Innodb_buffer_pool_pages_total))*100)+"% ("+str(self.Innodb_buffer_pool_pages_dirty)+")"
print"Innodb buffer pages flushed per second(flushed): "+str(float(self.Innodb_buffer_pool_pages_flushed)/float(self.Uptime))+"/s ("+str(self.Innodb_buffer_pool_pages_flushed)+"/"+str(self.Uptime)+"s)"
print"Innodb buffer pool pages misc : "+str(float(self.Innodb_buffer_pool_pages_misc))
print"Innodb row lock waits per second(waits): "+str(float(self.Innodb_row_lock_current_waits)/float(self.Uptime))+"/s ("+str(self.Innodb_row_lock_current_waits)+")"
print"Innodb row lock current waits : "+str(self.Innodb_row_lock_current_waits)
print"Innodb row lock time(avg/max/total) : "+str(self.Innodb_row_lock_time_avg)+"/"+str(self.Innodb_row_lock_time_max)+"/"+str(self.Innodb_row_lock_time)
print"Innodb rows PS : read="+str(float(self.Innodb_rows_read)/float(self.Uptime))+"/s inserted="+str(float(self.Innodb_rows_inserted)/float(self.Uptime))+"/s updated="+str(float(self.Innodb_rows_updated)/float(self.Uptime))+"/s deleted="+str(float(self.Innodb_rows_deleted)/float(self.Uptime))+"/s"
print"Innodb data PS : reads="+str(float(self.Innodb_data_reads)/float(self.Uptime))+"/s writes="+str(float(self.Innodb_data_writes)/float(self.Uptime))+"/s fsyncs="+str(float(self.Innodb_data_fsyncs)/float(self.Uptime))+"/s"
print"Innodb data pending PS: reads="+str(float(self.Innodb_data_pending_reads)/float(self.Uptime))+"/s writes="+str(float(self.Innodb_data_pending_writes)/float(self.Uptime))+"/s fsyncs="+str(float(self.Innodb_data_pending_fsyncs)/float(self.Uptime))+"/s"
defprintkeystatus(self):
print"------------------------------------"
print"key buffer Status"
print"------------------------------------"
print"key buffer used ratio : "+str((float(self.Key_blocks_used*self.key_cache_block_size)/float(self.key_buffer_size))*100)+"%"
print"key buffer read hit ratio : "+str(self.key_buffer_read_hit_ratio)+"%"
print"key buffer write hit ratio: "+str(self.key_buffer_write_hit_ratio)+"%"
defprintslavestatus(self):
print"------------------------------------"
print"Slave Status"
print"------------------------------------"
print"Slave running status : "+self.Slave_running
print"Slave open temp tables : "+str(self.Slave_open_temp_tables)
print"Slave transactions per second(transactions/Uptime) : "+str(float(self.Slave_retried_transactions)/float(self.Uptime))+"/s ("+str(self.Slave_retried_transactions)+"/"+str(self.Uptime)+")"
print"Slave received per second (received/period) : "+str(float(self.Slave_received_heartbeats)/float(self.Uptime))+"/s ("+str(self.Slave_received_heartbeats)+"/"+str(self.Slave_heartbeat_period)+")"
defprintflushstatus(self):
print"=========MySQL status pulse========="
self.printUptimesinceflushstatus()
self.printInnoDBStatus()
self.printkeystatus()
self.printQcachestatus()
self.printslavestatus()
self.printtablestatus()
def__del__(self):
self.conn.close()
if__name__ =='__main__':
opts, args = getopt.getopt(sys.argv[1:],"hi:o:")
#print opts
forop, valueinopts:
ifop =="-h":
host=value
printvalue
elifop =="-u":
user=value
printvalue
elifop =="-P":
port=value
printvalue
elifop =="-d":
dbname=value
printvalue
elifop =="-p":
pwd=value
printvalue
if(len(pwd)==0):
pwd = getpass.getpass('password: ')
dbs={'host':host,'port':port,'user':user,'passwd':pwd,'db':dbname}
pulse=mysqlpulse(dbs)
pulse.printmysqlinfo()
pulse.printprocesslist()
pulse.printflushstatus()
#printstatus(statustmp, statustmpList)
#printinnodbstatus(innodbstatus)