β

用python写的一段小脚本zabbix调用监控mysql的QPS和TPS

运维者 1484 阅读

写这段脚本不是为了别的,只是自己熟练掌握类的操作 另外还熟释一些函数,那么zabbix监控mysql,其中涉及到了一些计算,在配置文件不能进行计算,我觉得不太可能,所以写了这么一段脚本,功能得实现,因为知识面有限,后面遇到再继续优化吧,贴上代码如下:

#!/usr/bin/env python
#coding=utf-8

import sys
import os
import commands

class 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.TPS

class 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 QPS
UserParameter=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"

# /usr/local/zabbix/bin/zabbix_get -s 127.0.0.1 -p 10050 -k"mysql.QPS"    

# /usr/local/zabbix/bin/zabbix_get -s 127.0.0.1 -p 10050 -k"mysql.TPS"

优化版:

#!/usr/bin/env python
#coding=utf-8

import sys
import os
import time
import commands
from db_init import InitDb

class 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 last

class 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 Qps

class 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 result

class 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) / times

if __name__ == '__main__':
    main_obj = Main()
    main_obj.main()

用python写的一段小脚本zabbix调用监控mysql的QPS和TPS,首发于运维者

作者:运维者
临渊羡鱼,不如退而织网