java-mans
- 浏览: 11383385 次
-
文章分类
最新评论
-
wahahachuang8：
我觉得这种东西自己开发太麻烦了，就别自己捣鼓了，找个第三方，方 ...
WebSocket和node.js -
xhpscdx：
写的这么详细，全面，对架构师的工作职责，个人能力都进行了梳理。 ...
架构师之路---王泽宾谈架构师的职责 -
xgbzsc：
是http://www.haoservice.com 吗？
android WIFI定位 -
lehehe：
http://www.haoservice.com/docs/ ...
android WIFI定位 -
lehehe：
http://www.haoservice.com/docs/ ...
android WIFI定位
MySQL 5.5 5.1 服务器状态分析
在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: Id User Host db Command Time State Info status time>1 threads count=0 =========MySQL status pulse========= ------------------------------------ Reads/Writes status ------------------------------------ Reads:Writes ratio : 0.0857164519801% QPS/TPS : 51.5407755338/s 0.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 ------------------------------------ db Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_lengtht Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment ldb common_hist MyISAM 10 Dynamic 0 0 0 281474976710655 1024 0 None 2012-01-12 09:35:10 2012-01-12 09:35:10 None latin1_swedish_ci None test ip_dict MyISAM 10 Dynamic 83084 22 1876144 281474976710655 1024 0 None 2012-01-17 09:18:59 2012-01-17 09:19:01 None latin1_swedish_ci None test push_spreadaccount MyISAM 10 Dynamic 308 24 7460 281474976710655 1024 0 None 2012-02-03 10:02:42 2012-03-12 18:13:14 None latin1_swedish_ci None dmdb account_basic InnoDB 10 Compact 0 0 16384 0 0 0 None 2012-03-08 08:24:49 None None latin1_swedish_ci None dmdb char_basic InnoDB 10 Compact 0 0 16384 0 0 0 None 2012-03-08 08:24:49 None None latin1_swedish_ci None dmdb char_cost InnoDB 10 Compact 0 0 16384 0 0 0 None 2012-03-08 08:24:49 None None latin1_swedish_ci None dmdb char_incre InnoDB 10 Compact 0 0 16384 0 0 0 None 2012-03-08 08:24:49 None None latin1_swedish_ci None dmdb char_salary InnoDB 10 Compact 0 0 16384 0 0 0 1 2012-03-08 08:24:49 None None latin1_swedish_ci None dmdb charge_item_cost InnoDB 10 Compact 0 0 16384 0 16384 0 1 2012-03-08 08:24:49 None None latin1_swedish_ci None dmdb charge_item_sale InnoDB 10 Compact 0 0 16384 0 16384 0 1 2012-03-08 08:24:49 None None latin1_swedish_ci None dmdb coin_cost InnoDB 10 Compact 0 0 16384 0 0 0 1 2012-03-08 08:24:49 None None latin1_swedish_ci None dmdb game_baiguidi InnoDB 10 Compact 0 0 16384 0 0 0 1 2012-03-08 08:24:49 None None latin1_swedish_ci None dmdb game_chongwutaowang InnoDB 10 Compact 0 0 16384 0 0 0 1 2012-03-08 08:24:49 None None latin1_swedish_ci None dmdb game_wanyaoku InnoDB 10 Compact 0 0 16384 0 0 0 1 2012-03-08 08:24:49 None None latin1_swedish_ci None dmdb game_yabiao InnoDB 10 Compact 0 0 16384 0 0 0 1 2012-03-08 08:24:50 None None latin1_swedish_ci None dmdb item_exchange InnoDB 10 Compact 0 0 16384 0 0 0 1 2012-03-08 08:24:50 None None latin1_swedish_ci None dmdb party_contrib_convert InnoDB 10 Compact 0 0 16384 0 0 0 1 2012-03-08 08:24:50 None None latin1_swedish_ci None dmdb reputation_convert InnoDB 10 Compact 0 0 16384 0 16384 0 1 2012-03-08 08:24:50 None None latin1_swedish_ci None ldb antiaddiction_log InnoDB 10 Compact 33426 78 2637824 0 3178496 4194304 31667 2012-03-08 08:06:31 None None latin1_swedish_ci None ldb apply_log InnoDB 10 Compact 72898 144 10502144 0 12632064 114294784 907793 2012-03-08 08:06:18 None None latin1_swedish_ci None ldb behavior_log InnoDB 10 Compact 0 0 16384 0 32768 0 1 2012-03-08 08:08:51 None None latin1_swedish_ci None ldb block_log InnoDB 10 Compact 44026 202 8929280 0 7405568 4194304 43655 2012-03-08 08:08:40 None None latin1_swedish_ci None ldb campaign_log InnoDB 10 Compact 736936 180 133332992 0 106119168 290455552 10360528 2012-03-08 08:10:23 None None latin1_swedish_ci None ldb client_action_log InnoDB 10 Compact 0 0 16384 0 49152 0 1 2012-03-08 08:08:40 None None latin1_swedish_ci None ldb client_performance_log InnoDB 10 Compact 0 0 16384 0 49152 0 1 2012-03-08 08:09:05 None None latin1_swedish_ci None ldb coin_consume_log InnoDB 10 Compact 63228 116 7356416 0 6389760 9437184 353287 2012-03-08 08:10:23 None None latin1_swedish_ci None ldb coin_order_log InnoDB 10 Compact 911115 224 204144640 0 138788864 5242880 921668 2012-03-08 08:08:51 None None latin1_swedish_ci None ldb collect_data_log InnoDB 10 Compact 366246 372 136478720 0 10502144 138412032 1809067 2012-03-08 08:06:19 None None latin1_swedish_ci None ldb combat_log InnoDB 10 Compact 18852 223 4210688 0 491520 4194304 47986 2012-03-08 08:08:57 None None latin1_swedish_ci None ldb common_log InnoDB 10 Compact 2058455 219 452313088 0 57786368 136314880 10274133 2012-03-08 08:10:23 None None latin1_swedish_ci None ldb cost_coin_log InnoDB 10 Compact 55519 189 10502144 0 0 7340032 262758 2012-03-08 08:08:57 None None latin1_swedish_ci None ldb csc_combat_log InnoDB 10 Compact 0 0 16384 0 49152 0 1 2012-03-08 08:08:57 None None latin1_swedish_ci None ldb csl_combat_log InnoDB 10 Compact 0 0 16384 0 32768 0 1 2012-03-08 08:08:14 None None latin1_swedish_ci None ldb csl_match_log InnoDB 10 Compact 0 0 16384 0 32768 0 1 2012-03-08 08:07:42 None None latin1_swedish_ci None ldb csp_combat_log InnoDB 10 Compact 0 0 16384 0 32768 0 1 2012-03-08 08:09:05 None None latin1_swedish_ci None ldb csp_match_log InnoDB 10 Compact 0 0 16384 0 32768 0 1 2012-03-08 08:08:14 None None latin1_swedish_ci None ldb debug_log InnoDB 10 Compact 1872729 233 437649408 0 135692288 98566144 13829084 2012-03-08 08:08:14 None None latin1_swedish_ci None ldb encourage_detail_log InnoDB 10 Compact 5587 378 2113536 0 2310144 9437184 114217 2012-03-08 08:08:40 None None latin1_swedish_ci None ldb equipment_log InnoDB 10 Compact 978444 386 377864192 0 149405696 27262976 3169644 2012-03-08 08:09:02 None None latin1_swedish_ci None ldb errand_log InnoDB 10 Compact 2921571 159 465600512 0 309919744 813694976 30593577 2012-03-08 08:09:05 None None latin1_swedish_ci None ldb gbuy_action_log InnoDB 10 Compact 0 0 16384 0 65536 0 1 2012-03-08 08:08:40 None None latin1_swedish_ci None ldb gift_log InnoDB 10 Compact 63817 197 12599296 0 2113536 13631488 178135 2012-03-08 08:10:23 None None latin1_swedish_ci None ldb gm_log InnoDB 10 Compact 4802 330 1589248 0 557056 4194304 4383 2012-03-08 08:08:40 None None latin1_swedish_ci None ldb gs_log InnoDB 10 Compact 34300 275 9453568 0 3178496 7340032 358648 2012-03-08 08:07:46 None None latin1_swedish_ci None ldb history_credit_log InnoDB 10 Compact 0 0 16384 0 0 0 None 2012-03-08 08:07:42 None None latin1_swedish_ci None ldb important_log InnoDB 10 Compact 12589921 476 6002049024 0 1199570944 7340032 17086401 2012-03-08 08:08:40 None None latin1_swedish_ci None ldb important_pet_log InnoDB 10 Compact 1493 1766 2637824 0 327680 4194304 1424 2012-03-08 08:08:40 None None latin1_swedish_ci None ldb item_transfer_log InnoDB 10 Compact 4916635 343 1689272320 0 795607040 257949696 8857762 2012-03-08 08:06:18 None None latin1_swedish_ci None ldb level_up_log InnoDB 10 Compact 93675 201 18890752 0 8437760 160432128 975939 2012-03-08 08:06:19 None None latin1_swedish_ci None ldb login_log InnoDB 10 Compact 2169745 353 766541824 0 255852544 337641472 3885253 2012-03-08 08:08:51 None None latin1_swedish_ci None ldb material_log InnoDB 10 Compact 44701 211 9453568 0 2113536 7340032 223641 2012-03-08 08:08:02 None None latin1_swedish_ci None ldb money_log InnoDB 10 Compact 659037 144 95551488 0 72564736 174063616 2399885 2012-03-08 08:08:14 None None latin1_swedish_ci None ldb nummary_log InnoDB 10 Compact 332669 82 27328512 0 28442624 208666624 3458457 2012-03-08 08:07:46 None None latin1_swedish_ci None ldb pet_level_up_log InnoDB 10 Compact 229747 141 32587776 0 42123264 111149056 1311541 2012-03-08 08:09:53 None None latin1_swedish_ci None ldb pet_log InnoDB 10 Compact 378913 1037 393232384 0 95715328 1098907648 2899053 2012-03-08 08:08:51 None None latin1_swedish_ci None ldb property_bind_log InnoDB 10 Compact 714777 1215 868614144 0 54738944 74448896 830305 2012-03-08 08:08:40 None None latin1_swedish_ci None ldb purchase_platform_log InnoDB 10 Compact 161405 283 45678592 0 24690688 5242880 160977 2012-03-08 08:08:14 None None latin1_swedish_ci None ldb safe_log InnoDB 10 Compact 332886 699 233013248 0 36225024 28311552 405505 2012-03-08 08:06:31 None None latin1_swedish_ci None ldb sale_log InnoDB 10 Compact 40585 103 4210688 0 6356992 8388608 483544 2012-03-08 08:09:53 None None latin1_swedish_ci None ldb stat InnoDB 10 Compact 42627 98 4210688 0 2113536 5242880 195505 2012-03-08 08:08:40 None None latin1_swedish_ci None ldb switch_log InnoDB 10 Compact 657658 244 160612352 0 54673408 314572800 7808502 2012-03-08 08:08:14 None None latin1_swedish_ci None ldb task_log InnoDB 10 Compact 3181905 208 662732800 0 315326464 560988160 8187393 2012-03-08 08:08:14 None None latin1_swedish_ci None ldb user_log InnoDB 10 Compact 3131691 391 1224753152 0 93503488 270532608 15998092 2012-03-08 08:07:46 None None latin1_swedish_ci None mdb acc_info InnoDB 10 Compact 0 0 16384 0 0 0 None 2012-03-08 08:11:55 None None latin1_swedish_ci None mdb account InnoDB 10 Compact 0 0 16384 0 0 0 None 2012-03-08 08:11:55 None None latin1_swedish_ci None mdb admin_log InnoDB 10 Compact 0 0 16384 0 0 0 1 2012-03-08 08:11:55 None None latin1_swedish_ci None mdb char_info InnoDB 10 Compact 2884564 432 1248854016 0 462422016 7340032 None 2012-03-08 16:40:42 None None latin1_swedish_ci None mdb child_info InnoDB 10 Compact 17619 3409 60063744 0 5111808 6291456 None 2012-03-08 08:11:31 None None latin1_swedish_ci None mdb csc_sign_info InnoDB 10 Compact 0 0 16384 0 0 0 None 2012-03-08 08:11:32 None None latin1_swedish_ci None mdb csl_dist_info InnoDB 10 Compact 0 0 16384 0 16384 0 None 2012-03-08 08:11:32 None None latin1_swedish_ci None mdb csl_match_info InnoDB 10 Compact 0 0 16384 0 49152 0 None 2012-03-08 08:11:55 None None latin1_swedish_ci None mdb csl_person_info InnoDB 10 Compact 0 0 16384 0 16384 0 None 2012-03-08 08:11:56 None None latin1_swedish_ci None mdb csw_sign_info InnoDB 10 Compact 0 0 16384 0 0 0 None 2012-03-08 08:11:31 None None latin1_swedish_ci None mdb draft_info InnoDB 10 Compact 0 0 16384 0 0 0 None 2012-03-08 08:11:55 None None latin1_swedish_ci None mdb item_info InnoDB 10 Compact 6533479 551 3600859136 0 1890320384 5242880 None 2012-03-08 08:11:29 None None latin1_swedish_ci None mdb login_info InnoDB 10 Compact 0 0 16384 0 0 0 None 2012-03-08 08:11:55 None None latin1_swedish_ci None mdb party_basic_info InnoDB 10 Compact 307 693 212992 0 0 0 None 2012-03-08 08:11:41 None None latin1_swedish_ci None mdb party_members_info InnoDB 10 Compact 31658 179 5668864 0 0 4194304 None 2012-03-08 08:11:32 None None latin1_swedish_ci None mdb party_skills_info InnoDB 10 Compact 3844 413 1589248 0 0 4194304 None 2012-03-08 08:11:31 None None latin1_swedish_ci None mdb pet_info InnoDB 10 Compact 273678 3422 936542208 0 103694336 6291456 None 2012-03-08 08:11:32 None None latin1_swedish_ci None mdb server_status InnoDB 10 Compact 10 1638 16384 0 0 0 None 2012-03-08 08:11:32 None None latin1_swedish_ci None mdb shop_info InnoDB 10 Compact 593 248 147456 0 0 0 None 2012-03-08 08:11:55 None None latin1_swedish_ci None mdb sync_info InnoDB 10 Compact 0 0 16384 0 0 0 None 2012-03-08 08:11:31 None None latin1_swedish_ci None mdb upgrade_char_info InnoDB 10 Compact 6378 413 2637824 0 1622016 4194304 None 2012-03-08 08:11:32 None None latin1_swedish_ci None test game_maintenance InnoDB 10 Compact 12 1365 16384 0 0 0 13 2012-03-08 08:11:57 None None latin1_swedish_ci None test ipdb InnoDB 10 Compact 39107 67 2637824 0 0 4194304 None 2012-03-08 08:11:57 None None latin1_swedish_ci None test pushaccount InnoDB 10 Compact 0 0 16384 0 0 0 None 2012-03-08 08:11:57 None None latin1_swedish_ci None
下面是脚本:
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<(inx-1)):
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:")
#print opts
for op, value in opts:
if op == "-h":
host=value
print value
elif op == "-u":
user=value
print value
elif op == "-P":
port=value
print value
elif op == "-d":
dbname=value
print value
elif op == "-p":
pwd=value
print value
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)
分享到：
发表评论
相关推荐
5.5. MySQL服务器关机进程 5.6. 一般安全问题 5.6.1. 通用安全指南 5.6.2. 使MySQL在攻击者面前保持安全 5.6.3. Mysqld安全相关启动选项 5.6.4. LOAD DATA LOCAL安全问题 5.7. MySQL访问权限系统 5.7.1. 权限系统的...
5.5. MySQL服务器关机进程 5.6. 一般安全问题 5.6.1. 通用安全指南 5.6.2. 使MySQL在攻击者面前保持安全 5.6.3. Mysqld安全相关启动选项 5.6.4. LOAD DATA LOCAL安全问题 5.7. MySQL访问权限系统 5.7.1. 权限...
5.5. MySQL服务器关机进程 5.6. 一般安全问题 5.6.1. 通用安全指南 5.6.2. 使MySQL在攻击者面前保持安全 5.6.3. Mysqld安全相关启动选项 5.6.4. LOAD DATA LOCAL安全问题 5.7. MySQL访问权限系统 5.7.1. 权限系统的...
5.5. MySQL服务器关机进程 5.6. 一般安全问题 5.6.1. 通用安全指南 5.6.2. 使MySQL在攻击者面前保持安全 5.6.3. Mysqld安全相关启动选项 5.6.4. LOAD DATA LOCAL安全问题 5.7. MySQL访问权限系统 5.7.1. 权限...
5.5. MySQL服务器关机进程 5.6. 一般安全问题 5.6.1. 通用安全指南 5.6.2. 使MySQL在攻击者面前保持安全 5.6.3. Mysqld安全相关启动选项 5.6.4. LOAD DATA LOCAL安全问题 5.7. MySQL访问权限系统 5.7.1. 权限系统的...
5.5. MySQL服务器关机进程 5.6. 一般安全问题 5.6.1. 通用安全指南 5.6.2. 使MySQL在攻击者面前保持安全 5.6.3. Mysqld安全相关启动选项 5.6.4. LOAD DATA LOCAL安全问题 5.7. MySQL访问权限系统 5.7.1. 权限系统的...
mysql_fix_privilege_tables：升级MySQL系统表 5.5. MySQL服务器关机进程 5.6. 一般安全问题 5.6.1. 通用安全指南 5.6.2. 使MySQL在攻击者面前保持安全 5.6.3. Mysqld安全相关启动选项 5.6.4. ...
5.5. MySQL服务器关机进程 5.6. 一般安全问题 5.6.1. 通用安全指南 5.6.2. 使MySQL在攻击者面前保持安全 5.6.3. Mysqld安全相关启动选项 5.6.4. LOAD DATA LOCAL安全问题 5.7. MySQL访问权限系统 5.7.1. 权限系统的...
5.5. MySQL服务器关机进程 5.6. 一般安全问题 5.6.1. 通用安全指南 5.6.2. 使MySQL在攻击者面前保持安全 5.6.3. Mysqld安全相关启动选项 5.6.4. LOAD DATA LOCAL安全问题 5.7. MySQL访问权限系统 5.7.1. 权限系统的...
1.8 性能测试：mysql5.5与mysql5.1 60 第2章 半同步复制 62 2.1 半同步复制简介 62 2.2 半同步复制安装配置 63 2.3 参数说明 63 2.4 功能测试 64 2.4.1 如何验证半同步复制是否正常工作 64 2.4.2 半同步复制...
2、MySQL默认用户名：root，密码：yimonserver3、MySQL数据库文件存放目录：Server\MySQL5.1\data或Server\MySQL5.5\data4、网站根目录wwwroot5、访问本机请用http//localhost/6、非默认端口，网址为http端口/或...
但是缺点是软件版本有点低，官方提供的两种方法安装后版本都比较低，在centos5.5里rpm包安装的php是5.2，mysql是5.1，这几个包是从网上找的，shell文件，可以自定义升级到php任意版本，和mysql任意版本，不过前提是...
5.5. MySQL服务器关机进程 5.6. 一般安全问题 5.6.1. 通用安全指南 5.6.2. 使MySQL在攻击者面前保持安全 5.6.3. Mysqld安全相关启动选项 5.6.4. LOAD DATA LOCAL安全问题 5.7. MySQL访问权限系统 5.7.1. 权限...
5.5. MySQL服务器关机进程 5.6. 一般安全问题 5.6.1. 通用安全指南 5.6.2. 使MySQL在攻击者面前保持安全 5.6.3. Mysqld安全相关启动选项 5.6.4. LOAD DATA LOCAL安全问题 5.7. MySQL访问权限系统 5.7.1. 权限系统的...
目录 推荐序 前言 第1章MySQL架构与历史1 1.1MySQL逻辑架构1 1.1.1连接管理与安全性...附录BMySQL服务器状态655 附录C大文件传输683 附录DEXPLAIN687 附录E锁的调试703 附录F在MySQL上使用Sphinx713 索引739
基于TP5.1的开发的小程序商城源码 ... 2、MySQL版本——5.5以上； 3、用Apache或Nginx； 4、服务器域名需要做https，微信小程序要求必须做sll证书； 推荐：使用Linux + Apache + PHP7.0 + MySQL5.6；
5.4.4 存储过程和触发器 5.4.5 外键(Foreign Keys) 5.4.5.1 不使用外键的理由 5.4.6 视图(Views) 5.4.7 '--'作为一个 注解的开始 5.5 MySQL 遵循什么标准？ 5.6 怎样处理没有提交...