- 浏览: 11429073 次
文章分类
最新评论
-
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定位
MySQL5.5 real time monitor at linux(centos)——MySQL5.5实时监控基于centos
原文链接:http://blog.csdn.net/ylqmf/article/details/7878498
利用值班时间用python写了个mysql 实时监控脚本,使用前要确认安装python和mysqldb:
yum install python MySQL-python -y
直接执行脚本就可以了 python monitor.py
下面是执行后的结果,硬盘和网卡监控尚未加入:
''' Created on 2012-8-16 MySQL real time status @author: tudou@b2c.xiaomi.com ''' import MySQLdb,os,time from decimal import Decimal mysql_host='localhost' unix_socket='/tmp/mysql.sock' mysql_user='root' mysql_pwd='123456' mysql_db='test' disk_list=['sda'] sleep_time=2 class mysqlrealtimestatus(object): def __init__(self,conf): self.conf=conf self.db=db(conf) self.previoustatus=None self.nextstatus=None self.previousdisk=None self.nextdisk=None def run(self): while 1: i=os.system('clear') self.getstatus() time.sleep(self.conf['sleep']); def getstatus(self): self.previoustatus = self.nextstatus sql = "show global status;" self.nextstatus = dict(self.db.execute(sql)) #print self.nextstatus sql="show full processlist;" set = self.db.execute(sql,'dict') self.now = time.strftime('%H:%M:%S',time.localtime(time.time())) if self.previoustatus!=None and long(self.nextstatus['Uptime_since_flush_status'])>long(self.previoustatus['Uptime_since_flush_status']): self.computer(); print('==========================slow sql==========================') #mysqlrealtimestatus.printl(('id','user','host','db','command','time','state','info'),8) for process in set: if str(process['Command'])=='Query' and int(process['Time'])>2: print('Id:'+str(process['Id'])+'\t'+ 'User:'+str(process['User'])+'\t'+ 'Host:'+str(process['Host'])+'\t'+ 'db:'+str(process['db'])+'\t'+ 'Command:'+str(process['Command'])+'\t'+ 'Time:'+str(process['Time'])+'\t'+ 'State:'+str(process['State'])) print('Info:'+str(process['Info'])) print('---------------------------------------------------------------------------------') def computer(self): ops=Decimal(self.relcount('Questions'))/Decimal(self.relcount('Uptime_since_flush_status')) tps=(Decimal(self.relcount('Com_commit'))+Decimal(self.relcount('Com_rollback')))/Decimal(self.relcount('Uptime_since_flush_status')) sps=Decimal(self.relcount('Com_select')+self.relcount('Qcache_hits'))/Decimal(self.relcount('Uptime_since_flush_status')) ips=Decimal(self.relcount('Com_insert')+self.relcount('Com_insert_select'))/Decimal(self.relcount('Uptime_since_flush_status')) ups=Decimal(self.relcount('Com_update')+self.relcount('Com_update_multi'))/Decimal(self.relcount('Uptime_since_flush_status')) dps=Decimal(self.relcount('Com_delete')+self.relcount('Com_delete_multi'))/Decimal(self.relcount('Uptime_since_flush_status')) rps=Decimal(self.relcount('Com_replace')+self.relcount('Com_replace_select'))/Decimal(self.relcount('Uptime_since_flush_status')) bsent_ps=Decimal(self.relcount('Bytes_sent'))/Decimal(self.relcount('Uptime_since_flush_status')) if(bsent_ps<0): bsent_ps=Decimal(self.status['Bytes_sent'])/Decimal(self.status['Uptime_since_flush_status']) breceived_ps=Decimal(self.relcount('Bytes_received'))/Decimal(self.relcount('Uptime_since_flush_status')) if(breceived_ps<0): breceived_ps=Decimal(self.status['Bytes_received'])/Decimal(self.status['Uptime_since_flush_status']) if Decimal(self.relcount('Innodb_buffer_pool_read_requests'))>0: ib_read_hits=1-Decimal(self.relcount('Innodb_buffer_pool_reads')+self.relcount('Innodb_buffer_pool_read_ahead'))/Decimal(self.relcount('Innodb_buffer_pool_read_requests')) else: ib_read_hits=1 ib_used_percent=1-Decimal(self.nextstatus['Innodb_buffer_pool_pages_free'])/Decimal(self.nextstatus['Innodb_buffer_pool_pages_total']) ib_dirty_page_percent=Decimal(self.nextstatus['Innodb_buffer_pool_pages_dirty'])/Decimal(self.nextstatus['Innodb_buffer_pool_pages_total']) if(self.nextstatus.has_key('Innodb_row_lock_waits')): ir_lock_waits_ps=Decimal(self.relcount('Innodb_row_lock_waits'))/Decimal(self.relcount('Uptime_since_flush_status')) else: ir_lock_waits_ps=0 if(self.relcount('Questions')>0): sq_percent=Decimal(self.relcount('Slow_queries'))/Decimal(self.relcount('Questions')) else: sq_percent=0 sq_ps=Decimal(self.relcount('Slow_queries'))/Decimal(self.relcount('Uptime_since_flush_status')) if(self.relcount('Created_tmp_tables')>0): td_percent=Decimal(self.relcount('Created_tmp_disk_tables'))/Decimal(self.relcount('Created_tmp_tables')) else: td_percent=0 opened_tables_ps=Decimal(self.relcount('Opened_tables'))/Decimal(self.relcount('Uptime_since_flush_status')) if(self.nextstatus.has_key('Opened_files')): opened_files_ps=Decimal(self.relcount('Opened_files'))/Decimal(self.relcount('Uptime_since_flush_status')) else: opened_files_ps=0 if(self.relcount('Connections')>0): thread_cache_hits=1-Decimal(self.relcount('Threads_created'))/Decimal(self.relcount('Connections')) else: thread_cache_hits=1 mysqlrealtimestatus.printl(('time','ops','tps','sps','ips','ups','dps','rps','bsps','brps','%ihpct','%upct','%dpct','ilwps','%sqpct','%tdpct','ofps','%tcpct')) mysqlrealtimestatus.println((self.now, mysqlrealtimestatus.dFormat(ops), mysqlrealtimestatus.dFormat(tps), mysqlrealtimestatus.dFormat(sps), mysqlrealtimestatus.dFormat(ips), mysqlrealtimestatus.dFormat(ups), mysqlrealtimestatus.dFormat(dps), mysqlrealtimestatus.dFormat(rps), mysqlrealtimestatus.dFormat(bsent_ps), mysqlrealtimestatus.dFormat(breceived_ps), mysqlrealtimestatus.perF(ib_read_hits), mysqlrealtimestatus.perF(ib_used_percent), mysqlrealtimestatus.perF(ib_dirty_page_percent), mysqlrealtimestatus.dFormat(ir_lock_waits_ps), mysqlrealtimestatus.perF(sq_percent), mysqlrealtimestatus.perF(td_percent), mysqlrealtimestatus.dFormat(opened_files_ps), mysqlrealtimestatus.perF(thread_cache_hits) )) #i=os.system('dstat -cglmpdy --tcp') loadavg=self.load_stat() mem=self.memory_stat() swap=self.swap_stat() self.previousdisk=self.nextdisk self.nextdisk=self.disk_stat() mysqlrealtimestatus.printl(('time','lavg1','lavg5','lavg15','mTotal','mUsed','Buffer','Cached','mFree','swapt','swapu',),8) mysqlrealtimestatus.println((self.now, mysqlrealtimestatus.dFormat(loadavg['lavg_1']), mysqlrealtimestatus.dFormat(loadavg['lavg_5']), mysqlrealtimestatus.dFormat(loadavg['lavg_15']), mysqlrealtimestatus.dFormat(Decimal(str(mem['MemTotal']))), mysqlrealtimestatus.dFormat(Decimal(str(mem['MemUsed']))), mysqlrealtimestatus.dFormat(Decimal(str(mem['Buffers']))), mysqlrealtimestatus.dFormat(Decimal(str(mem['Cached']))), mysqlrealtimestatus.dFormat(Decimal(str(mem['MemFree']))), mysqlrealtimestatus.dFormat(Decimal(str(swap['swapt']))*1024), mysqlrealtimestatus.dFormat(Decimal(str(swap['swapu']))*1024) ),8) #print #!/usr/bin/env python def load_stat(self): loadavg = {} f = open("/proc/loadavg") con = f.read().split() f.close() loadavg['lavg_1']=Decimal(con[0]) loadavg['lavg_5']=Decimal(con[1]) loadavg['lavg_15']=Decimal(con[2]) return loadavg #!/usr/bin/env python def memory_stat(self): mem = {} f = open("/proc/meminfo") lines = f.readlines() f.close() for line in lines: if len(line) < 2: continue name = line.split(':')[0] var = line.split(':')[1].split()[0] mem[name] = long(var) * 1024.0 mem['MemUsed'] = mem['MemTotal'] - mem['MemFree'] - mem['Buffers'] - mem['Cached'] return mem def disk_stat(self): disk=[] f = open("/proc/diskstats") lines = f.readlines() f.close() for disk_name in disk_list: for row in lines: if str(row).find(' '+disk_name+' ')>0: con=str(row).split(' ') disk.append({'disk_name':disk_name,'rcount':con[2],'rrcount':con[3],'rdcount':con[3],'rtime':con[4],'wcount':con[5],'rwcount':con[6],'wdcount':con[7],'wtime':con[8],}) break return disk def swap_stat(self): swap={} f = open("/proc/swaps") l = f.readlines() f.close() con=str(l[1]).split('\t') swap['swapt']=con[1] swap['swapu']=con[2] return swap #!/usr/bin/env python def net_stat(self): net = [] f = open("/proc/net/dev") lines = f.readlines() f.close() for line in lines[2:]: con = line.split() intf = {} intf['interface'] = con[0].lstrip(":") intf['ReceiveBytes'] = int(con[1]) intf['ReceivePackets'] = int(con[2]) intf['ReceiveErrs'] = int(con[3]) intf['ReceiveDrop'] = int(con[4]) intf['ReceiveFifo'] = int(con[5]) intf['ReceiveFrames'] = int(con[6]) intf['ReceiveCompressed'] = int(con[7]) intf['ReceiveMulticast'] = int(con[8]) intf['TransmitBytes'] = int(con[9]) intf['TransmitPackets'] = int(con[10]) intf['TransmitErrs'] = int(con[11]) intf['TransmitDrop'] = int(con[12]) intf['TransmitFifo'] = int(con[13]) intf['TransmitFrames'] = int(con[14]) intf['TransmitCompressed'] = int(con[15]) #intf['TransmitMulticast'] = int(con[16]) """ intf = dict( zip( ( 'interface','ReceiveBytes','ReceivePackets', 'ReceiveErrs','ReceiveDrop','ReceiveFifo', 'ReceiveFrames','ReceiveCompressed','ReceiveMulticast', 'TransmitBytes','TransmitPackets','TransmitErrs', 'TransmitDrop', 'TransmitFifo','TransmitFrames', 'TransmitCompressed','TransmitMulticast' ), ( con[0].rstrip(":"),int(con[1]),int(con[2]), int(con[3]),int(con[4]),int(con[5]), int(con[6]),int(con[7]),int(con[8]), int(con[9]),int(con[10]),int(con[11]), int(con[12]),int(con[13]),int(con[14]), int(con[15]),int(con[16])) ) ) """ net.append(intf) return net def relcount(self,param): return Decimal(self.nextstatus[param])-Decimal(self.previoustatus[param]) @staticmethod def println(param,s=7): p="" for i in param: if type(i)==type(""): p+=i+" " else: p+=str(i[0]).ljust(s) print p @staticmethod def printl(param,s=7): p="" for i in param: if str(i)=='time': p+=str(i)+" " else: p+=str(i).ljust(s) print p @staticmethod def perF(param): return mysqlrealtimestatus.dFormat(param*100) @staticmethod def dFormat(val): k=1024 m=k*k g=k*m t=k*g p=k*t dp=0 dm="" if(val!=0): if(val>p): dp=p dm="P" elif(val>t): dp=t dm="T" elif(val>g): dp=g dm="G" elif(val>m): dp=m dm="M" elif(val>k): dp=k dm="k" else: dp=1 return ["%2.2f" % (Decimal(val)/Decimal(dp)) +dm] else: return ["%2.2f" % 0] ''' ''' class MySQLHelper(object): @staticmethod def getConn(conf): pot = 3306 if(conf.has_key('port')): pot=conf['port'] dbname='test' if(conf.has_key('db')): dbname=conf['db'] if(conf.has_key('socket')): return MySQLdb.connect(host=conf['host'],unix_socket=conf['socket'],user=conf['user'],passwd=conf['pwd'],db=dbname) else: return MySQLdb.connect(host=conf['host'],port=pot,user=conf['user'],passwd=conf['pwd'],db=dbname) ''' ''' class db (object): def __init__(self,conf): self.conn=None self.conn=MySQLHelper.getConn(conf) def execute(self,sql,mod=''): if(mod=='dict'): cursor=self.conn.cursor(MySQLdb.cursors.DictCursor) else: cursor=self.conn.cursor() cursor.execute(sql) set=cursor.fetchall() return set def executeNoQuery(self,sql,param={}): cursor=self.conn.cursor() try: if(param=={}): rownum=cursor.execute(sql) else: rownum=cursor.executemany(sql,param) self.conn.commit() return rownum finally: cursor.close() def __del__(self): if (self.conn!=None): self.conn.close() if __name__ == '__main__': conf={'host':mysql_host,'socket':unix_socket,'user':mysql_user,'pwd':mysql_pwd,'db':mysql_db,'sleep':sleep_time} status=mysqlrealtimestatus(conf); status.run();
原文链接:http://blog.csdn.net/ylqmf/article/details/7878498
相关推荐
MySQL-server-5.5.25a-1.linux2.6.x86_64.rpm mysql5.5 sever 64位版本在centos上安装的rpm包
Linux CentOS5.5 web配置
集合网上资料和自身实际安装整理的centos5.5安装mysql5.5,文档中有5.5和5.6的下载地载,如果安装5.6还需要找一点资料。
centos 6.5配置mysql5.5
Centos5.5下安装LAMP完整版Centos5.5下安装LAMP完整版Centos5.5下安装LAMP完整版Centos5.5下安装LAMP完整版Centos5.5下安装LAMP完整版Centos5.5下安装LAMP完整版Centos5.5下安装LAMP完整版Centos5.5下安装LAMP完整版
linux centos 5.5 安装说明 linux centos 5.5 安装说明 linux centos 5.5 安装说明
打开终端 切换到根目录 [shell@localhost ~]# su -安装Mysql5.5之前先卸载CentOS自带的Mysql5.0。 [root@localhost ~]# yum remove mysql 安装cmake 下载cmake源码包cmake-2.8.5.tar.gz [root@localhost ~]# wget ...
在CentOS/Red Hat Linux上安装MySQL 5.5,转载请保留文档全部信息。
mysql5.5升级的所有包。 1、安装MySQL 5.5.x的yum源: rpm -Uvh http://repo.webtatic.com/yum/centos/5/latest.rpm 2、安装MySQL客户端的支持包: yum install libmysqlclient15 --enablerepo=webtatic 3、卸载...
码头工人MySQL 基于CentOS和MySQL 5.5的Docker MySQL映像
MySQL5.5启动多实例详细配置文件,多实例安装方法可参考博文:Centos6.10采用cmake编译安装MySQL5.5多实例安装
centos安装mysql5.5.txt
Linux复习资料——CentOS7下安装MySQL5.7.22安装包
Linux 安装 MySQL 5.5 (CentOS 6.5)-附件资源
CentOS 5.5使用yum安装Apache+PHP+MySQL 一、安装centos5.5操作系统 二、系统安装 三、安装Apache+php+Mysql 四、 配置防火墙 五、安装phpMyAdmin 六、安装php的扩展 七、安装apache扩展 八、如果想升级PHP的话
Centos7安装MySQL5.5-5.7.docx
其实在Centos 5.5中安装nginx+php+mysql是非常容易的。只需yum安装环境包和nginx、编译安装php、配置php-fpm文件和nginx.conf文件即可
Linux CentOS5.5 samba服务配置非常详细
不过自从被sun收购之后,就没用集成在centos这些开源Linux系统中了,那么如果想用的话就需要自己安装了,在安装过程中肯定会用到些命令,下面通过本篇文章给大家介绍centos 7安装mysql5.5和安装 mariadb使用的命令