SEARU.ORG
当前位置:SEARU.ORG > Linux 数据库 > 正文

MySQL 5.5实时监控基于CentOS

MySQL5.5 real time monitor at linux(CentOS)——MySQL5.5实时监控基于CentOS。


利用值班时间用python写了个mysql 实时监控脚本,使用前要确认安装python和mysqldb:


yum install python MySQL-python -y


直接执行脚本就可以了 python monitor.py


下面是执行后的结果,硬盘和网卡监控尚未加入:




  1. ”’ 

  2. Created on 2012-8-16 

  3. MySQL real time status 

  4. @author: [email protected] 

  5. ”’  

  6. import MySQLdb,os,time  

  7. from decimal import Decimal  

  8. mysql_host=‘localhost’  

  9. unix_socket=‘/tmp/mysql.sock’  

  10. mysql_user=‘root’  

  11. mysql_pwd=‘123456’  

  12. mysql_db=‘test’  

  13. disk_list=[‘sda’]  

  14. sleep_time=2  

  15.   

  16. class mysqlrealtimestatus(object):  

  17.       

  18.     def __init__(self,conf):  

  19.         self.conf=conf  

  20.         self.db=db(conf)  

  21.         self.previoustatus=None  

  22.         self.nextstatus=None  

  23.         self.previousdisk=None  

  24.         self.nextdisk=None  

  25.           

  26.           

  27.     def run(self):  

  28.         while 1:  

  29.             i=os.system(‘clear’)  

  30.             self.getstatus()  

  31.             time.sleep(self.conf[‘sleep’]);  

  32.               

  33.               

  34.     def getstatus(self):  

  35.         self.previoustatus = self.nextstatus  

  36.         sql = “show global status;”  

  37.         self.nextstatus = dict(self.db.execute(sql))  

  38.         #print self.nextstatus   

  39.         sql=“show full processlist;”  

  40.         set = self.db.execute(sql,‘dict’)  

  41.           

  42.         self.now = time.strftime(‘%H:%M:%S’,time.localtime(time.time()))  

  43.         if self.previoustatus!=None and long(self.nextstatus[‘Uptime_since_flush_status’])>long(self.previoustatus[‘Uptime_since_flush_status’]):  

  44.             self.computer();  

  45.         print(‘==========================slow sql==========================’)  

  46.         #mysqlrealtimestatus.printl((‘id’,’user’,’host’,’db’,’command’,’time’,’state’,’info’),8)   

  47.         for process in set:  

  48.             if str(process[‘Command’])==‘Query’ and int(process[‘Time’])>2:  

  49.                 print(‘Id:’+str(process[‘Id’])+‘\t’+  

  50.                 ‘User:’+str(process[‘User’])+‘\t’+  

  51.                 ‘Host:’+str(process[‘Host’])+‘\t’+  

  52.                 ‘db:’+str(process[‘db’])+‘\t’+  

  53.                 ‘Command:’+str(process[‘Command’])+‘\t’+  

  54.                 ‘Time:’+str(process[‘Time’])+‘\t’+  

  55.                 ‘State:’+str(process[‘State’]))  

  56.                 print(‘Info:’+str(process[‘Info’]))  

  57.                 print(‘———————————————————————————‘)  

  58.                   

  59.           

  60.     def computer(self):  

  61.         ops=Decimal(self.relcount(‘Questions’))/Decimal(self.relcount(‘Uptime_since_flush_status’))  

  62.         tps=(Decimal(self.relcount(‘Com_commit’))+Decimal(self.relcount(‘Com_rollback’)))/Decimal(self.relcount(‘Uptime_since_flush_status’))  

  63.         sps=Decimal(self.relcount(‘Com_select’)+self.relcount(‘Qcache_hits’))/Decimal(self.relcount(‘Uptime_since_flush_status’))  

  64.         ips=Decimal(self.relcount(‘Com_insert’)+self.relcount(‘Com_insert_select’))/Decimal(self.relcount(‘Uptime_since_flush_status’))  

  65.         ups=Decimal(self.relcount(‘Com_update’)+self.relcount(‘Com_update_multi’))/Decimal(self.relcount(‘Uptime_since_flush_status’))  

  66.         dps=Decimal(self.relcount(‘Com_delete’)+self.relcount(‘Com_delete_multi’))/Decimal(self.relcount(‘Uptime_since_flush_status’))  

  67.         rps=Decimal(self.relcount(‘Com_replace’)+self.relcount(‘Com_replace_select’))/Decimal(self.relcount(‘Uptime_since_flush_status’))  

  68.           

  69.         bsent_ps=Decimal(self.relcount(‘Bytes_sent’))/Decimal(self.relcount(‘Uptime_since_flush_status’))  

  70.         if(bsent_ps<0):  

  71.             bsent_ps=Decimal(self.status[‘Bytes_sent’])/Decimal(self.status[‘Uptime_since_flush_status’])  

  72.         breceived_ps=Decimal(self.relcount(‘Bytes_received’))/Decimal(self.relcount(‘Uptime_since_flush_status’))  

  73.         if(breceived_ps<0):  

  74.             breceived_ps=Decimal(self.status[‘Bytes_received’])/Decimal(self.status[‘Uptime_since_flush_status’])  

  75.         if Decimal(self.relcount(‘Innodb_buffer_pool_read_requests’))>0:  

  76.             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’))  

  77.         else:  

  78.             ib_read_hits=1  

  79.           

  80.         ib_used_percent=1-Decimal(self.nextstatus[‘Innodb_buffer_pool_pages_free’])/Decimal(self.nextstatus[‘Innodb_buffer_pool_pages_total’])  

  81.         ib_dirty_page_percent=Decimal(self.nextstatus[‘Innodb_buffer_pool_pages_dirty’])/Decimal(self.nextstatus[‘Innodb_buffer_pool_pages_total’])  

  82.           

  83.         if(self.nextstatus.has_key(‘Innodb_row_lock_waits’)):  

  84.             ir_lock_waits_ps=Decimal(self.relcount(‘Innodb_row_lock_waits’))/Decimal(self.relcount(‘Uptime_since_flush_status’))  

  85.         else:  

  86.             ir_lock_waits_ps=0  

  87.         if(self.relcount(‘Questions’)>0):  

  88.             sq_percent=Decimal(self.relcount(‘Slow_queries’))/Decimal(self.relcount(‘Questions’))  

  89.         else:  

  90.             sq_percent=0  

  91.         sq_ps=Decimal(self.relcount(‘Slow_queries’))/Decimal(self.relcount(‘Uptime_since_flush_status’))  

  92.         if(self.relcount(‘Created_tmp_tables’)>0):  

  93.             td_percent=Decimal(self.relcount(‘Created_tmp_disk_tables’))/Decimal(self.relcount(‘Created_tmp_tables’))  

  94.         else:  

  95.             td_percent=0  

  96.         opened_tables_ps=Decimal(self.relcount(‘Opened_tables’))/Decimal(self.relcount(‘Uptime_since_flush_status’))  

  97.         if(self.nextstatus.has_key(‘Opened_files’)):  

  98.             opened_files_ps=Decimal(self.relcount(‘Opened_files’))/Decimal(self.relcount(‘Uptime_since_flush_status’))  

  99.         else:  

  100.             opened_files_ps=0  

  101.         if(self.relcount(‘Connections’)>0):  

  102.             thread_cache_hits=1-Decimal(self.relcount(‘Threads_created’))/Decimal(self.relcount(‘Connections’))  

  103.         else:  

  104.             thread_cache_hits=1  

  105.           

  106.         mysqlrealtimestatus.printl((‘time’,‘ops’,‘tps’,‘sps’,‘ips’,‘ups’,‘dps’,‘rps’,‘bsps’,‘brps’,‘%ihpct’,‘%upct’,‘%dpct’,‘ilwps’,‘%sqpct’,‘%tdpct’,‘ofps’,‘%tcpct’))  

  107.         mysqlrealtimestatus.println((self.now,  

  108.                                      mysqlrealtimestatus.dFormat(ops),  

  109.                                      mysqlrealtimestatus.dFormat(tps),  

  110.                                      mysqlrealtimestatus.dFormat(sps),  

  111.                                      mysqlrealtimestatus.dFormat(ips),  

  112.                                      mysqlrealtimestatus.dFormat(ups),  

  113.                                      mysqlrealtimestatus.dFormat(dps),  

  114.                                      mysqlrealtimestatus.dFormat(rps),  

  115.                                      mysqlrealtimestatus.dFormat(bsent_ps),  

  116.                                      mysqlrealtimestatus.dFormat(breceived_ps),  

  117.                                      mysqlrealtimestatus.perF(ib_read_hits),  

  118.                                      mysqlrealtimestatus.perF(ib_used_percent),  

  119.                                      mysqlrealtimestatus.perF(ib_dirty_page_percent),  

  120.                                      mysqlrealtimestatus.dFormat(ir_lock_waits_ps),  

  121.                                      mysqlrealtimestatus.perF(sq_percent),  

  122.                                      mysqlrealtimestatus.perF(td_percent),  

  123.                                      mysqlrealtimestatus.dFormat(opened_files_ps),  

  124.                                      mysqlrealtimestatus.perF(thread_cache_hits)  

  125.                                      ))  

  126.         #i=os.system(‘dstat -cglmpdy –tcp’)   

  127.         loadavg=self.load_stat()  

  128.         mem=self.memory_stat()  

  129.         swap=self.swap_stat()  

  130.         self.previousdisk=self.nextdisk  

  131.         self.nextdisk=self.disk_stat()  

  132.         mysqlrealtimestatus.printl((‘time’,‘lavg1’,‘lavg5’,‘lavg15’,‘mTotal’,‘mUsed’,‘Buffer’,‘Cached’,‘mFree’,‘swapt’,‘swapu’,),8)  

  133.         mysqlrealtimestatus.println((self.now,  

  134.                                      mysqlrealtimestatus.dFormat(loadavg[‘lavg_1’]),  

  135.                                      mysqlrealtimestatus.dFormat(loadavg[‘lavg_5’]),  

  136.                                      mysqlrealtimestatus.dFormat(loadavg[‘lavg_15’]),  

  137.                                      mysqlrealtimestatus.dFormat(Decimal(str(mem[‘MemTotal’]))),  

  138.                                      mysqlrealtimestatus.dFormat(Decimal(str(mem[‘MemUsed’]))),  

  139.                                      mysqlrealtimestatus.dFormat(Decimal(str(mem[‘Buffers’]))),  

  140.                                      mysqlrealtimestatus.dFormat(Decimal(str(mem[‘Cached’]))),  

  141.                                      mysqlrealtimestatus.dFormat(Decimal(str(mem[‘MemFree’]))),  

  142.                                      mysqlrealtimestatus.dFormat(Decimal(str(swap[‘swapt’]))*1024),  

  143.                                      mysqlrealtimestatus.dFormat(Decimal(str(swap[‘swapu’]))*1024)  

  144.                                      ),8)  

  145.         #print    

  146.     #!/usr/bin/env python   

  147.     def load_stat(self):  

  148.         loadavg = {}  

  149.         f = open(“/proc/loadavg”)  

  150.         con = f.read().split()  

  151.         f.close()  

  152.         loadavg[‘lavg_1’]=Decimal(con[0])  

  153.         loadavg[‘lavg_5’]=Decimal(con[1])  

  154.         loadavg[‘lavg_15’]=Decimal(con[2])  

  155.         return loadavg  

  156.     #!/usr/bin/env python   

  157.     def memory_stat(self):  

  158.         mem = {}  

  159.         f = open(“/proc/meminfo”)  

  160.         lines = f.readlines()  

  161.         f.close()  

  162.         for line in lines:  

  163.             if len(line) < 2continue  

  164.             name = line.split(‘:’)[0]  

  165.             var = line.split(‘:’)[1].split()[0]  

  166.             mem[name] = long(var) * 1024.0  

  167.         mem[‘MemUsed’] = mem[‘MemTotal’] – mem[‘MemFree’] – mem[‘Buffers’] – mem[‘Cached’]  

  168.         return mem  

  169.     def disk_stat(self):  

  170.         disk=[]  

  171.         f = open(“/proc/diskstats”)  

  172.         lines = f.readlines()  

  173.         f.close()  

  174.         for disk_name in disk_list:  

  175.             for row in lines:  

  176.                 if str(row).find(‘ ‘+disk_name+‘ ‘)>0:  

  177.                     con=str(row).split(‘ ‘)  

  178.                     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],})  

  179.                     break  

  180.                   

  181.         return disk  

  182.     def swap_stat(self):  

  183.         swap={}  

  184.         f = open(“/proc/swaps”)  

  185.         l = f.readlines()  

  186.         f.close()  

  187.         con=str(l[1]).split(‘\t’)  

  188.         swap[‘swapt’]=con[1]  

  189.         swap[‘swapu’]=con[2]  

  190.         return swap  

  191.       

  192.     #!/usr/bin/env python   

  193.     def net_stat(self):  

  194.         net = []  

  195.         f = open(“/proc/net/dev”)  

  196.         lines = f.readlines()  

  197.         f.close()  

  198.         for line in lines[2:]:  

  199.             con = line.split()  

  200.               

  201.             intf = {}  

  202.             intf[‘interface’] = con[0].lstrip(“:”)  

  203.             intf[‘ReceiveBytes’] = int(con[1])  

  204.             intf[‘ReceivePackets’] = int(con[2])  

  205.             intf[‘ReceiveErrs’] = int(con[3])  

  206.             intf[‘ReceiveDrop’] = int(con[4])  

  207.             intf[‘ReceiveFifo’] = int(con[5])  

  208.             intf[‘ReceiveFrames’] = int(con[6])  

  209.             intf[‘ReceiveCompressed’] = int(con[7])  

  210.             intf[‘ReceiveMulticast’] = int(con[8])  

  211.             intf[‘TransmitBytes’] = int(con[9])  

  212.             intf[‘TransmitPackets’] = int(con[10])  

  213.             intf[‘TransmitErrs’] = int(con[11])  

  214.             intf[‘TransmitDrop’] = int(con[12])  

  215.             intf[‘TransmitFifo’] = int(con[13])  

  216.             intf[‘TransmitFrames’] = int(con[14])  

  217.             intf[‘TransmitCompressed’] = int(con[15])  

  218.             #intf[‘TransmitMulticast’] = int(con[16])   

  219.             “”” 

  220.             intf = dict( 

  221.                 zip( 

  222.                     ( ‘interface’,’ReceiveBytes’,’ReceivePackets’, 

  223.                       ‘ReceiveErrs’,’ReceiveDrop’,’ReceiveFifo’, 

  224.                       ‘ReceiveFrames’,’ReceiveCompressed’,’ReceiveMulticast’, 

  225.                       ‘TransmitBytes’,’TransmitPackets’,’TransmitErrs’, 

  226.                       ‘TransmitDrop’, ‘TransmitFifo’,’TransmitFrames’, 

  227.                       ‘TransmitCompressed’,’TransmitMulticast’ ), 

  228.                     ( con[0].rstrip(“:”),int(con[1]),int(con[2]), 

  229.                       int(con[3]),int(con[4]),int(con[5]), 

  230.                       int(con[6]),int(con[7]),int(con[8]), 

  231.                       int(con[9]),int(con[10]),int(con[11]), 

  232.                       int(con[12]),int(con[13]),int(con[14]), 

  233.                       int(con[15]),int(con[16])) 

  234.                 ) 

  235.             ) 

  236.             “””  

  237.             net.append(intf)  

  238.         return net  

  239.       

  240.     def relcount(self,param):  

  241.         return Decimal(self.nextstatus[param])-Decimal(self.previoustatus[param])  

  242.      

  243.     @staticmethod  

  244.     def println(param,s=7):  

  245.         p=””  

  246.         for i in param:  

  247.             if type(i)==type(“”):  

  248.                 p+=i+” “  

  249.             else:  

  250.                 p+=str(i[0]).ljust(s)  

  251.           

  252.         print p  

  253.     @staticmethod  

  254.     def printl(param,s=7):  

  255.         p=””  

  256.         for i in param:  

  257.             if str(i)==‘time’:  

  258.                 p+=str(i)+”     “  

  259.             else:  

  260.                 p+=str(i).ljust(s)  

  261.           

  262.         print p  

  263.          

  264.     @staticmethod  

  265.     def perF(param):  

  266.         return mysqlrealtimestatus.dFormat(param*100)  

  267.      

  268.     @staticmethod  

  269.     def dFormat(val):  

  270.         k=1024  

  271.         m=k*k  

  272.         g=k*m  

  273.         t=k*g  

  274.         p=k*t  

  275.         dp=0  

  276.         dm=””  

  277.         if(val!=0):  

  278.             if(val>p):  

  279.                 dp=p  

  280.                 dm=“P”  

  281.             elif(val>t):  

  282.                 dp=t  

  283.                 dm=“T”  

  284.             elif(val>g):  

  285.                 dp=g  

  286.                 dm=“G”  

  287.             elif(val>m):  

  288.                 dp=m  

  289.                 dm=“M”  

  290.             elif(val>k):  

  291.                 dp=k  

  292.                 dm=“k”  

  293.             else:  

  294.                 dp=1  

  295.             return [“%2.2f” % (Decimal(val)/Decimal(dp)) +dm]  

  296.         else:  

  297.             return [“%2.2f” % 0]  

  298. ”’ 

  299.  

  300. ”’  

  301. class MySQLHelper(object):  

  302.     @staticmethod  

  303.     def getConn(conf):  

  304.         pot = 3306  

  305.         if(conf.has_key(‘port’)):  

  306.             pot=conf[‘port’]  

  307.         dbname=‘test’  

  308.         if(conf.has_key(‘db’)):  

  309.             dbname=conf[‘db’]  

  310.           

  311.         if(conf.has_key(‘socket’)):  

  312.             return MySQLdb.connect(host=conf[‘host’],unix_socket=conf[‘socket’],user=conf[‘user’],passwd=conf[‘pwd’],db=dbname)  

  313.         else:  

  314.             return MySQLdb.connect(host=conf[‘host’],port=pot,user=conf[‘user’],passwd=conf[‘pwd’],db=dbname)  

  315. ”’ 

  316.  

  317. ”’  

  318. class db (object):  

  319.     def __init__(self,conf):  

  320.         self.conn=None  

  321.         self.conn=MySQLHelper.getConn(conf)  

  322.       

  323.     def execute(self,sql,mod=):  

  324.         if(mod==‘dict’):  

  325.             cursor=self.conn.cursor(MySQLdb.cursors.DictCursor)  

  326.         else:  

  327.             cursor=self.conn.cursor()  

  328.         cursor.execute(sql)  

  329.         set=cursor.fetchall()  

  330.         return set  

  331.       

  332.     def executeNoQuery(self,sql,param={}):  

  333.         cursor=self.conn.cursor()  

  334.         try:  

  335.             if(param=={}):  

  336.                 rownum=cursor.execute(sql)  

  337.             else:  

  338.                 rownum=cursor.executemany(sql,param)  

  339.             self.conn.commit()  

  340.             return rownum  

  341.         finally:  

  342.             cursor.close()  

  343.       

  344.     def __del__(self):  

  345.         if (self.conn!=None):  

  346.             self.conn.close()  

  347.   

  348. if __name__ == ‘__main__’:  

  349.     conf={‘host’:mysql_host,‘socket’:unix_socket,‘user’:mysql_user,‘pwd’:mysql_pwd,‘db’:mysql_db,‘sleep’:sleep_time}  

  350.     status=mysqlrealtimestatus(conf);  

  351.     status.run();  

未经允许不得转载:SEARU.ORG » MySQL 5.5实时监控基于CentOS

赞 (0)
分享到:更多 ()

评论 0