写这段脚本不是为了别的,只是自己熟练掌握类的操作 另外还熟释一些函数,那么zabbix监控mysql,其中涉及到了一些计算,在配置文件不能进行计算,我觉得不太可能,所以写了这么一段脚本,功能得实现,因为知识面有限,后面遇到再继续优化吧,贴上代码如下:
#!/usr/bin/env python#coding=utf-8import sysimport osimport commandsclass QpsTps(object): def __init__(self): self.QPS = '' self.TPS = '' def getQps(self): (Queries,QPS_result) = commands.getstatusoutput("mysqladmin -uroot -p123456 extended-status | grep 'Queries' | cut -d'|' -f3") self.QPS = int(QPS_result) return self.QPS def getTps(self): (Com_commit,cm_result) = commands.getstatusoutput("mysqladmin -uroot -p123456 extended-status | grep 'Com_commit' | cut -d'|' -f3 ") (Com_rollback,rb_result) = commands.getstatusoutput("mysqladmin -uroot -p123456 extended-status | grep 'Com_rollback' | cut -d'|' -f3 | awk 'NR==1'") self.TPS = int(cm_result) + int(rb_result) return self.TPSclass error_out(object): def error_print(self): '''代入值少输,输出错误''' print print 'Usage : ' + sys.argv[0] + ' MysqlStatusKey ' print sys.exit(1)class Main(object): def main(self): if len(sys.argv) == 1: error = error_out() error.error_print() elif sys.argv[1] == 'QPS': a = QpsTps() print a.getQps() elif sys.argv[1] == 'TPS': a = QpsTps() print a.getTps()if __name__ == '__main__': main_obj = Main() main_obj.main()
将代码上传至系统,赋值权限,在zabbix的mysql配置文中加入:
UserParameter=mysql.QPS,python /usr/local/zabbix/scripts/get_qps_tps.py QPSUserParameter=mysql.TPS,python /usr/local/zabbix/scripts/get_qps_tps.py TPS
服务端取值测试:
# /usr/local/zabbix/bin/zabbix_get -s 10.16.1.68 -p 10050 -k"mysql.QPS"1783724# /usr/local/zabbix/bin/zabbix_get -s 127.0.0.1 -p 10050 -k"mysql.QPS" 3695982# /usr/local/zabbix/bin/zabbix_get -s 127.0.0.1 -p 10050 -k"mysql.TPS"278279
优化版:
#!/usr/bin/env python#coding=utf-8import sysimport osimport timeimport commandsfrom db_init import InitDbclass MysqlPeerStatus(object): def __init__(self): a = InitDb() a.readconfigfile() self.user = a.GetUser() self.passwd = a.GetPasswd() self.value = 0 def GetValue(self, key): (temp,last) = commands.getstatusoutput("mysqladmin -u%s -p%s extended-status | grep '%s>' | cut -d'|' -f3"%(self.user,self.passwd,key)) last = float(last) return lastclass MysqlQpsTps(object): def __init__(self): """init""" self.a = MysqlPeerStatus() for key in ('Com_insert','Com_update', 'Com_delete', 'Com_select'): if key == 'Com_insert': self.com_insert = self.a.GetValue(key) elif key == 'Com_update': self.com_update = self.a.GetValue(key) elif key == 'Com_delete': self.com_delete = self.a.GetValue(key) else: self.com_select = self.a.GetValue(key) def Tps(self): Tps = self.com_insert + self.com_update + self.com_delete return Tps def Qps(self): Qps = self.com_insert + self.com_update + self.com_delete + self.com_select return Qpsclass InnodbBufferStatus(object): def __init__(self): """init""" self.a = MysqlPeerStatus() for key in ('Innodb_buffer_pool_pages_total','Innodb_buffer_pool_read_requests','Innodb_buffer_pool_reads', 'Innodb_buffer_pool_pages_free','Innodb_buffer_pool_pages_dirty'): if key == 'Innodb_buffer_pool_pages_total': self.pages_total = self.a.GetValue(key) elif key == 'Innodb_buffer_pool_read_requests': self.cache_read = self.a.GetValue(key) elif key == 'Innodb_buffer_pool_reads': self.disk_read = self.a.GetValue(key) elif key == 'Innodb_buffer_pool_pages_free': self.free_pages = self.a.GetValue(key) else: self.pages_dirty = self.a.GetValue(key) def InnodbBufferReadHitRate(self): result = (1 - self.disk_read/self.cache_read) * 100 return result def InnodbBufferUsage(self): result = (1 - self.free_pages/self.pages_total) * 100 return result def InnodbBufferPoolDirtyPercentage(self): result = self.pages_dirty/self.pages_total * 100 return resultclass error_out(object): def error_print(self): '''输出错误信息''' print print 'Usage : ' + sys.argv[0] + ' time ' + ' MysqlStatusKey ' print 'MysqlStatusKey include (Qps, Tps, innodb_buffer_read_hit_ratio, innodb_buffer_usage, Queries Etc!)' print sys.exit(1)class Main(object): def main(self): if len(sys.argv) == 1: error = error_out() error.error_print() elif len(sys.argv) == 2: #times = float(sys.argv[1]) key = sys.argv[1] if key == 'innodb_buffer_read_hit_ratio': b = InnodbBufferStatus() print b.InnodbBufferReadHitRate() elif key == 'innodb_buffer_usage': b = InnodbBufferStatus() print b.InnodbBufferUsage() elif key == 'innodb_pages_dirty_percentage': b = InnodbBufferStatus() print b.InnodbBufferPoolDirtyPercentage() elif key == 'Qps': b = MysqlQpsTps() print b.Qps() elif key == 'Tps': b = MysqlQpsTps() print b.Tps() else: b = MysqlPeerStatus() print b.GetValue(key) #print last #time.sleep(times) #print (b.GetValue(key) - last) / timesif __name__ == '__main__': main_obj = Main() main_obj.main()
用python写的一段小脚本zabbix调用监控mysql的QPS和TPS,首发于运维者。
有时我们选择改变,并非经过深思熟虑,