python统计MYSQL常用的一些性能参数指标

#!/usr/bin/envpython#-*-encoding:utf8-*-#Author:[e-mail]#Created:2009-12-14from__future__importdivisionimportMySQLdb,random,datetime,time,os,sysimportConfigParserimportstring,os,sys#readmysqlconfigfileconfig_file=/root/.my.cnf#目前应用/etc/my.cnf文件if(os.path.exists(config_file)):cf=ConfigParser.ConfigParser()cf.read(config_file)host=cf.get(client,host)user=cf.get(client,user)password=cf.get(client,password)db=else:host='192.168.200.225'user='root'password='p0o9i8u7'db=''#today=time.strftime(%Y-%m-%d%H:%M:%S,time.localtime())#----------------------------------------------------------------------defgetConn(host,user,passwd,db='jobmd_new',port=3306,charset=''):try:conn=MySQLdb.connect(host=host,user=user,passwd=passwd,db=db,port=port,charset=charset)returnconnexceptMySQLdb.Error,e:printError%d:%s%(e.args[0],e.args[1])sys.exit(1)#----------------------------------------------------------------------defcloseConn(conn):closemysqlconnectionconn.close()#----------------------------------------------------------------------defgetValue(conn,query):getvalueofquerycursor=conn.cursor()cursor.execute(query)result=cursor.fetchone()returnint(result[1])defgetQuery(conn,query):getmorequeriescursor=conn.cursor()cursor.execute(query)result=cursor.fetchall()returnresultQuestions=showglobalstatuslike'Questions'Uptime=showglobalstatuslike'Uptime'Com_commit=showglobalstatuslike'Com_commit'Com_rollback=showglobalstatuslike'Com_rollback'Key_reads=showglobalstatuslike'Key_reads'Key_read_requests=showglobalstatuslike'Key_read_requests'Key_writes=showglobalstatuslike'Key_writes'Key_write_requests=showglobalstatuslike'Key_write_requests'Have_innodb=showglobalvariableslike'have_innodb'Innodb_buffer_pool_reads=showglobalstatuslike'Innodb_buffer_pool_reads'Innodb_buffer_pool_read_requests=showglobalstatuslike'Innodb_buffer_pool_read_requests'Qcache_hits=showglobalstatuslike'Qcache_hits'Qcache_inserts=showglobalstatuslike'Qcache_inserts'Open_tables=showglobalstatuslike'Open_tables'Opened_tables=showglobalstatuslike'Opened_tables'Threads_created=showglobalstatuslike'Threads_created'Connections=showglobalstatuslike'Connections'Com_select=showglobalstatuslike'Com_select'Com_insert=showglobalstatuslike'Com_insert'Com_update=showglobalstatuslike'Com_update'Com_delete=showglobalstatuslike'Com_delete'Com_replace=showglobalstatuslike'Com_replace'Table_locks_waited=showglobalstatuslike'Table_locks_waited'Table_locks_immediate=showglobalstatuslike'Table_locks_immediate'Created_tmp_tables=showglobalstatuslike'Created_tmp_tables'Created_tmp_disk_tables=showglobalstatuslike'Created_tmp_disk_tables'Slow_queries=showglobalstatuslike'Slow_queries'Select_full_join=showglobalstatuslike'Select_full_join'if__name__==__main__:conn=getConn(host,user,password,db)Questions=getValue(conn,Questions)Uptime=getValue(conn,Uptime)Com_commit=getValue(conn,Com_commit)Com_rollback=getValue(conn,Com_rollback)Key_reads=getValue(conn,Key_reads)Key_read_requests=getValue(conn,Key_read_requests)Key_writes=getValue(conn,Key_writes)Key_write_requests=getValue(conn,Key_write_requests)Qcache_hits=getValue(conn,Qcache_hits)Qcache_inserts=getValue(conn,Qcache_inserts)Open_tables=getValue(conn,Open_tables)Opened_tables=getValue(conn,Opened_tables)Threads_created=getValue(conn,Threads_created)Connections=getValue(conn,Connections)Com_select=getValue(conn,Com_select)Com_insert=getValue(conn,Com_insert)Com_update=getValue(conn,Com_update)Com_delete=getValue(conn,Com_delete)Com_replace=getValue(conn,Com_replace)Table_locks_immediate=getValue(conn,Table_locks_immediate)Table_locks_waited=getValue(conn,Table_locks_waited)Created_tmp_tables=getValue(conn,Created_tmp_tables)Created_tmp_disk_tables=getValue(conn,Created_tmp_disk_tables)Slow_queries=getValue(conn,Slow_queries)Select_full_join=getValue(conn,Select_full_join)print_____GerneralInformation___________________#QPS=Questions/SecondsQPS=str(round(Questions/Uptime,5))printQPS(Queryperseconds):+QPS#TPS=(Com_commit+Com_rollback)/SecondsTPS=str(round((Com_commit+Com_rollback)/Uptime,5))printTPS(Transactinperseconds):+TPS#Read/WritesRatiorwr=str(round((Com_select+Qcache_hits)/(Com_insert+Com_update+Com_delete+Com_replace)*100,5))+%printRead/WritesRatio:+rwr+\nprint_____CacheUsage___________________#Key_buffer_read_hits=(1-Key_reads/Key_read_requests)*100%#Key_buffer_write_hits=(1-Key_writes/Key_write_requests)*100%Key_buffer_read_hits=str(round((1-Key_reads/Key_read_requests)*100,5))+%Key_buffer_write_hits=str(round((1-Key_writes/Key_write_requests)*100,5))+%printMyISAMKeybufferreadratio(99.3%-99.9%target):+str(Key_buffer_read_hits)printMyISAMKeybufferwriteratio:+str(Key_buffer_write_hits)+\n#Query_cache_hits=(Qcache_hits/(Qcache_hits+Qcache_inserts))*100%Query_cache_hits=str(round(((Qcache_hits/(Qcache_hits+Qcache_inserts))*100),5))+%printQuerycachehitsratio:+Query_cache_hits+\ncursor=conn.cursor()cursor.execute(Have_innodb)result=cursor.fetchone()Have_innodb=result[1]if(Have_innodb==YES):Innodb_buffer_pool_reads=getValue(conn,Innod