MySQL 5.5实时监控基于CentOS

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: tudou@b2c.xiaomi.com 
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) < 2: continue  
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();