mysql port range_MySQL 5.5 5.1 服务器状态分析

最新推荐文章于 2024-03-30 18:26:34 发布

盐选博物馆

最新推荐文章于 2024-03-30 18:26:34 发布

阅读量120

点赞数

文章标签： mysql port range

本文链接：https://blog.csdn.net/weixin_33445930/article/details/113593415

版权

在MySQL中使用show status能够看到服务器当前状态,但是不方便分析,特意写了个分析脚本(没有冒犯mysqlreport企图)

目前支持linux平台下5.5和5.1版本的mysql

使用前需要修改脚本头部的参数

host="192.168.60.130"

user="dba"

pwd="123456"

port=3306

dbname="test"

这种参数非常不方便,命令行参数正在组织中,先出个生产库的报表吧,总体而言,硬盘写入比高达70(已经大于64),说明硬盘已经不堪重负了,Innodb buffer pages used ratio已经100%,内存不够了,Qcache就不应该开

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

Connection id : 4944

Current database : test

Current user : user@%

SSL : NO

MySQL VERSION : 5.1.45-community Source distribution

MySQL client info : 5.1.61

Protocol version : 10

Connection : XXX.XX.XX.XX 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 : 473274s

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

processlist rowcount =80

status time>1 threads list:

IdUserHostdbCommandTimeStateInfo

status time>1 threads count=0

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

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

Reads/Writes status

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

Reads:Writes ratio : 0.0857164519801%

QPS/TPS : 51.5407755338/s0.00167556214793/s

Table locks waited ratio : 4.10179506858e-08%

SQL PS : select=0.0441203193076/s insert=29.5896816643/s update=13.3331706369/s delete=8.54955269041/s replace=0.0/s

Bytes sent/received per second : 47641.6740937/s 73689.5984504/s

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

Slow and Sort queries status

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

Slow queries Ratio : 1.09458039629e-05%

Slow queries per second : 0.000564155225092/s

Full join per second : 0.000564155225092/s

Sort per second : merge passes=0.0003296187832/s range=0.00809467665665/s rows=1.20635192299/s scan=0.000232423500974/s

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

connections status

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

Thread cache hits(created/cached) : 98.301314459% (84/16)

Connections used ratio(Max used/Max) : 27.6666666667%(83/300)

Aborted connects ratio : 2.64914054601%

Threads running : 1

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

temp table and Open tables/files status

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

Temp tables to Disk ratio : 5.54827435561%

Open tables/table open cache/Opened tables: 191/400/511

Opened files per second : 0.00244678558298/s

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

InnoDB Status

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

Innodb buffer read hits(Disk/total) : 99.8768291196% (5216485/4235160926)

Innodb_dblwr_pages_written:Innodb_dblwr_writes : 70

Innodb buffer pages used ratio(free/total) : 100.0% (0/131072)

Innodb buffer pages dirty ratio(dirty) : 65.9233093262% (86407)

Innodb buffer pages flushed per second(flushed): 28.4868427169/s (13482082/473274s)

Innodb buffer pool pages misc : 10254.0

Innodb row lock waits per second(waits): 0.0/s (0)

Innodb row lock current waits : 0

Innodb row lock time(avg/max/total) : 8574/43757/214358

Innodb rows PS : read=2803.58150458/s inserted=326.822963019/s updated=13.3331748628/s deleted=319.585339571/s

Innodb data PS : reads=14.1728554706/s writes=19.1432109095/s fsyncs=2.91195797783/s

Innodb data pending PS: reads=0.0/s writes=0.0/s fsyncs=0.0/s

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

key buffer Status

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

key buffer used ratio : 0.0732421875%

key buffer read hit ratio : 75.0%

key buffer write hit ratio: 0%

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

Qcache Status

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

Qcache queries hits ratio(hits/inserts): 0.00138882237441% (29/20881)

Qcache hits inserts ratio(hits/inserts): 0.165581820258% (29/17485)

Qcache memory used ratio(free/total) : 0.118613243103% (33514632/33554432)

Qcache prune ratio(prunes/inserts) : 0.0% (0/17485)

Qcache block Fragmnt ratio(free/total) : 3.44827586207% (2/58)

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

Slave Status

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

Slave running status : OFF

Slave open temp tables : 0

Slave transactions per second(transactions/Uptime) : 0.0/s (0/473274)

Slave received per second (received/period) : 0.0/s (0/0.0)

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

table status

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

dbNameEngineVersionRow_formatRowsAvg_row_lengthData_lengthMax_data_lengthIndex_lengthtData_freeAuto_incrementCreate_timeUpdate_timeCheck_timeCollationChecksumCreate_optionsComment

ldbcommon_histMyISAM10Dynamic00028147497671065510240None2012-01-12 09:35:102012-01-12 09:35:10Nonelatin1_swedish_ciNone

testip_dictMyISAM10Dynamic8308422187614428147497671065510240None2012-01-17 09:18:592012-01-17 09:19:01Nonelatin1_swedish_ciNone

testpush_spreadaccountMyISAM10Dynamic30824746028147497671065510240None2012-02-03 10:02:422012-03-12 18:13:14Nonelatin1_swedish_ciNone

dmdbaccount_basicInnoDB10Compact0016384000None2012-03-08 08:24:49NoneNonelatin1_swedish_ciNone

dmdbchar_basicInnoDB10Compact0016384000None2012-03-08 08:24:49NoneNonelatin1_swedish_ciNone

dmdbchar_costInnoDB10Compact0016384000None2012-03-08 08:24:49NoneNonelatin1_swedish_ciNone

dmdbchar_increInnoDB10Compact0016384000None2012-03-08 08:24:49NoneNonelatin1_swedish_ciNone

dmdbchar_salaryInnoDB10Compact001638400012012-03-08 08:24:49NoneNonelatin1_swedish_ciNone

dmdbcharge_item_costInnoDB10Compact0016384016384012012-03-08 08:24:49NoneNonelatin1_swedish_ciNone

dmdbcharge_item_saleInnoDB10Compact0016384016384012012-03-08 08:24:49NoneNonelatin1_swedish_ciNone

dmdbcoin_costInnoDB10Compact001638400012012-03-08 08:24:49NoneNonelatin1_swedish_ciNone

dmdbgame_baiguidiInnoDB10Compact001638400012012-03-08 08:24:49NoneNonelatin1_swedish_ciNone

dmdbgame_chongwutaowangInnoDB10Compact001638400012012-03-08 08:24:49NoneNonelatin1_swedish_ciNone

dmdbgame_wanyaokuInnoDB10Compact001638400012012-03-08 08:24:49NoneNonelatin1_swedish_ciNone

dmdbgame_yabiaoInnoDB10Compact001638400012012-03-08 08:24:50NoneNonelatin1_swedish_ciNone

dmdbitem_exchangeInnoDB10Compact001638400012012-03-08 08:24:50NoneNonelatin1_swedish_ciNone

dmdbparty_contrib_convertInnoDB10Compact001638400012012-03-08 08:24:50NoneNonelatin1_swedish_ciNone

dmdbreputation_convertInnoDB10Compact0016384016384012012-03-08 08:24:50NoneNonelatin1_swedish_ciNone

ldbantiaddiction_logInnoDB10Compact33426782637824031784964194304316672012-03-08 08:06:31NoneNonelatin1_swedish_ciNone

ldbapply_logInnoDB10Compact72898144105021440126320641142947849077932012-03-08 08:06:18NoneNonelatin1_swedish_ciNone

ldbbehavior_logInnoDB10Compact0016384032768012012-03-08 08:08:51NoneNonelatin1_swedish_ciNone

ldbblock_logInnoDB10Compact440262028929280074055684194304436552012-03-08 08:08:40NoneNonelatin1_swedish_ciNone

ldbcampaign_logInnoDB10Compact7369361801333329920106119168290455552103605282012-03-08 08:10:23NoneNonelatin1_swedish_ciNone

ldbclient_action_logInnoDB10Compact0016384049152012012-03-08 08:08:40NoneNonelatin1_swedish_ciNone

ldbclient_performance_logInnoDB10Compact0016384049152012012-03-08 08:09:05NoneNonelatin1_swedish_ciNone

ldbcoin_consume_logInnoDB10Compact6322811673564160638976094371843532872012-03-08 08:10:23NoneNonelatin1_swedish_ciNone

ldbcoin_order_logInnoDB10Compact911115224204144640013878886452428809216682012-03-08 08:08:51NoneNonelatin1_swedish_ciNone

ldbcollect_data_logInnoDB10Compact36624637213647872001050214413841203218090672012-03-08 08:06:19NoneNonelatin1_swedish_ciNone

ldbcombat_logInnoDB10Compact18852223421068804915204194304479862012-03-08 08:08:57NoneNonelatin1_swedish_ciNone

ldbcommon_logInnoDB10Compact2058455219452313088057786368136314880102741332012-03-08 08:10:23NoneNonelatin1_swedish_ciNone

ldbcost_coin_logInnoDB10Compact55519189105021440073400322627582012-03-08 08:08:57NoneNonelatin1_swedish_ciNone

ldbcsc_combat_logInnoDB10Compact0016384049152012012-03-08 08:08:57NoneNonelatin1_swedish_ciNone

ldbcsl_combat_logInnoDB10Compact0016384032768012012-03-08 08:08:14NoneNonelatin1_swedish_ciNone

ldbcsl_match_logInnoDB10Compact0016384032768012012-03-08 08:07:42NoneNonelatin1_swedish_ciNone

ldbcsp_combat_logInnoDB10Compact0016384032768012012-03-08 08:09:05NoneNonelatin1_swedish_ciNone

ldbcsp_match_logInnoDB10Compact0016384032768012012-03-08 08:08:14NoneNonelatin1_swedish_ciNone

ldbdebug_logInnoDB10Compact1872729233437649408013569228898566144138290842012-03-08 08:08:14NoneNonelatin1_swedish_ciNone

ldbencourage_detail_logInnoDB10Compact558737821135360231014494371841142172012-03-08 08:08:40NoneNonelatin1_swedish_ciNone

ldbequipment_logInnoDB10Compact97844438637786419201494056962726297631696442012-03-08 08:09:02NoneNonelatin1_swedish_ciNone

ldberrand_logInnoDB10Compact29215711594656005120309919744813694976305935772012-03-08 08:09:05NoneNonelatin1_swedish_ciNone

ldbgbuy_action_logInnoDB10Compact0016384065536012012-03-08 08:08:40NoneNonelatin1_swedish_ciNone

ldbgift_logInnoDB10Compact638171971259929602113536136314881781352012-03-08 08:10:23NoneNonelatin1_swedish_ciNone

ldbgm_logInnoDB10Compact480233015892480557056419430443832012-03-08 08:08:40NoneNonelatin1_swedish_ciNone

ldbgs_logInnoDB10Compact3430027594535680317849673400323586482012-03-08 08:07:46NoneNonelatin1_swedish_ciNone

ldbhistory_credit_logInnoDB10Compact0016384000None2012-03-08 08:07:42NoneNonelatin1_swedish_ciNone

ldbimportant_logInnoDB10Compact125899214766002049024011995709447340032170864012012-03-08 08:08:40NoneNonelatin1_swedish_ciNone

ldbimportant_pet_logInnoDB10Compact1493176626378240327680419430414242012-03-08 08:08:40NoneNonelatin1_swedish_ciNone

ldbitem_transfer_logInnoDB10Compact49166353431689272320079560704025794969688577622012-03-08 08:06:18NoneNonelatin1_swedish_ciNone

ldblevel_up_logInnoDB10Compact9367520118890752084377601604321289759392012-03-08 08:06:19NoneNonelatin1_swedish_ciNone

ldblogin_logInnoDB10Compact2169745353766541824025585254433764147238852532012-03-08 08:08:51NoneNonelatin1_swedish_ciNone

ldbmaterial_logInnoDB10Compact4470121194535680211353673400322236412012-03-08 08:08:02NoneNonelatin1_swedish_ciNone

ldbmoney_logInnoDB10Compact6590371449555148807256473617406361623998852012-03-08 08:08:14NoneNonelatin1_swedish_ciNone

ldbnummary_logInnoDB10Compact332669822732851202844262420866662434584572012-03-08 08:07:46NoneNonelatin1_swedish_ciNone

ldbpet_level_up_logInnoDB10Compact2297471413258777604212326411114905613115412012-03-08 08:09:53NoneNonelatin1_swedish_ciNone

ldbpet_logInnoDB10Compact3789131037393232384095715328109890764828990532012-03-08 08:08:51NoneNonelatin1_swedish_ciNone

ldbproperty_bind_logInnoDB10Compact7147771215868614144054738944744488968303052012-03-08 08:08:40NoneNonelatin1_swedish_ciNone

ldbpurchase_platform_logInnoDB10Compact1614052834567859202469068852428801609772012-03-08 08:08:14NoneNonelatin1_swedish_ciNone

ldbsafe_logInnoDB10Compact332886699233013248036225024283115524055052012-03-08 08:06:31NoneNonelatin1_swedish_ciNone

ldbsale_logInnoDB10Compact4058510342106880635699283886084835442012-03-08 08:09:53NoneNonelatin1_swedish_ciNone

ldbstatInnoDB10Compact426279842106880211353652428801955052012-03-08 08:08:40NoneNonelatin1_swedish_ciNone

ldbswitch_logInnoDB10Compact65765824416061235205467340831457280078085022012-03-08 08:08:14NoneNonelatin1_swedish_ciNone

ldbtask_logInnoDB10Compact3181905208662732800031532646456098816081873932012-03-08 08:08:14NoneNonelatin1_swedish_ciNone

ldbuser_logInnoDB10Compact31316913911224753152093503488270532608159980922012-03-08 08:07:46NoneNonelatin1_swedish_ciNone

mdbacc_infoInnoDB10Compact0016384000None2012-03-08 08:11:55NoneNonelatin1_swedish_ciNone

mdbaccountInnoDB10Compact0016384000None2012-03-08 08:11:55NoneNonelatin1_swedish_ciNone

mdbadmin_logInnoDB10Compact001638400012012-03-08 08:11:55NoneNonelatin1_swedish_ciNone

mdbchar_infoInnoDB10Compact2884564432124885401604624220167340032None2012-03-08 16:40:42NoneNonelatin1_swedish_ciNone

mdbchild_infoInnoDB10Compact17619340960063744051118086291456None2012-03-08 08:11:31NoneNonelatin1_swedish_ciNone

mdbcsc_sign_infoInnoDB10Compact0016384000None2012-03-08 08:11:32NoneNonelatin1_swedish_ciNone

mdbcsl_dist_infoInnoDB10Compact00163840163840None2012-03-08 08:11:32NoneNonelatin1_swedish_ciNone

mdbcsl_match_infoInnoDB10Compact00163840491520None2012-03-08 08:11:55NoneNonelatin1_swedish_ciNone

mdbcsl_person_infoInnoDB10Compact00163840163840None2012-03-08 08:11:56NoneNonelatin1_swedish_ciNone

mdbcsw_sign_infoInnoDB10Compact0016384000None2012-03-08 08:11:31NoneNonelatin1_swedish_ciNone

mdbdraft_infoInnoDB10Compact0016384000None2012-03-08 08:11:55NoneNonelatin1_swedish_ciNone

mdbitem_infoInnoDB10Compact65334795513600859136018903203845242880None2012-03-08 08:11:29NoneNonelatin1_swedish_ciNone

mdblogin_infoInnoDB10Compact0016384000None2012-03-08 08:11:55NoneNonelatin1_swedish_ciNone

mdbparty_basic_infoInnoDB10Compact307693212992000None2012-03-08 08:11:41NoneNonelatin1_swedish_ciNone

mdbparty_members_infoInnoDB10Compact316581795668864004194304None2012-03-08 08:11:32NoneNonelatin1_swedish_ciNone

mdbparty_skills_infoInnoDB10Compact38444131589248004194304None2012-03-08 08:11:31NoneNonelatin1_swedish_ciNone

mdbpet_infoInnoDB10Compact273678342293654220801036943366291456None2012-03-08 08:11:32NoneNonelatin1_swedish_ciNone

mdbserver_statusInnoDB10Compact10163816384000None2012-03-08 08:11:32NoneNonelatin1_swedish_ciNone

mdbshop_infoInnoDB10Compact593248147456000None2012-03-08 08:11:55NoneNonelatin1_swedish_ciNone

mdbsync_infoInnoDB10Compact0016384000None2012-03-08 08:11:31NoneNonelatin1_swedish_ciNone

mdbupgrade_char_infoInnoDB10Compact63784132637824016220164194304None2012-03-08 08:11:32NoneNonelatin1_swedish_ciNone

testgame_maintenanceInnoDB10Compact12136516384000132012-03-08 08:11:57NoneNonelatin1_swedish_ciNone

testipdbInnoDB10Compact39107672637824004194304None2012-03-08 08:11:57NoneNonelatin1_swedish_ciNone

testpushaccountInnoDB10Compact0016384000None2012-03-08 08:11:57NoneNonelatin1_swedish_ciNone

下面是脚本:

import MySQLdb

import _mysql

import re

import time

import sys

import getopt

tudou@Gyyx

host="192.168.60.130"

user="dba"

pwd="123456"

port=3306

dbname="test"

class mysqlpulse(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={}

for row in self.variablestmp:

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

self.statusList=[]

self.statustmpList=[]

self.statustmp=()

self.mysqlstatus={}

sql="show global status"

inx=1

for i in range(inx):

cursor.execute(sql)

self.statustmp=cursor.fetchall()

self.mysqlstatus={}

for row in self.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()

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()

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)

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>1 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>1 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.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"

def printQcachestatus(self):

if(self.have_query_cache=="YES" and self.query_cache_type!="OFF" and self.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)+")"

def printUptimesinceflushstatus(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"

def printInnoDBStatus(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"

def printkeystatus(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)+"%"

def printslavestatus(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)+")"

def printflushstatus(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:")