mysql 性能分析套件

!/usr/local/python3.5/bin/python3.5 #!coding:utf-8 #################################### #目地：用于诊断mysql性能问题 #作者：蒋乐兴 #时间：2016-07-02 #create user moniter@‘127.0.0.1‘ identified by ‘moniter@2048‘; # #################################### import mysql.connector as connector import argparse import psutil import json import sys import os show_global_status_56="select variable_name,variable_value from information_schema.global_status where variable_name= %s" show_global_variables_56="select variable_name,variable_value from information_schema.global_variables where variable_name= %s" show_global_status_57="select variable_name,variable_value from performance_schema.global_status where variable_name= %s" show_global_variables_57="select variable_name,variable_value from performance_schema.global_variables where variable_name= %s" class AnalyseBase(object): def __init__(self,cursor,args): self.cursor=cursor self.args=args self.result={} def Analyse(self): "执行分析函数" pass def Print(self): print(json.dumps(analyst.result,sort_keys=True,indent=4,ensure_ascii=False)) class AnalyseInnodb(AnalyseBase): def innodb_log_waits(self): "status:innodb_log_waits innodb 等待刷新redo log 的次，如果它不是0，说明innodb_log_buffer_size 过小" self.cursor.execute(args.show_global_status,(‘innodb_log_waits‘,)) name,value=self.cursor.fetchone() comment=None if int(value)==0: comment=‘正常‘ else: comment=‘innodb_log_waits > 0 应该适当增加innodb_log_buffer_size的大小‘ self.result[‘innodb_log_waits‘]={‘name‘:‘innodb_log_waits‘,‘value‘:value,‘comment‘:comment} def innodb_flush_log_at_trx_commit(self): ("variables:innodb_flush_log_at_trx_commit 0:事务提交时并不把redo log 写入日志文件，而是等待主线程每秒的刷新。" "1:commit 时同步的方式刷新redo log 到日志文件" "2:commit 时异步的方式刷新redo log 到日志文件") self.cursor.execute(args.show_global_variables,(‘innodb_flush_log_at_trx_commit‘,)) name,value=self.cursor.fetchone() comment=None if int(value)==1: comment=‘正常、由于每个事务完成后都要同步的刷新日志，所以性能不是最好‘ else: comment=‘注意、有安全隐患;0:事务提交时并不把redo log 写入日志文件，而是等待主线程每秒的刷新;2:commit 时异步的方式刷新redo log 到日志文件。‘ self.result[‘innodb_flush_log_at_trx_commit‘]={‘name‘:‘innodb_flush_log_at_trx_commit‘,‘value‘:value,‘comment‘:comment} def innodb_file_per_table(self): "variables:innodb_file_per_table 不做成单独表空间的话管理不方便" self.cursor.execute(args.show_global_variables,(‘innodb_file_per_table‘,)) name,value=self.cursor.fetchone() comment=None if comment==‘ON‘: comment=‘正常‘ else: comment=‘注意、建议开启innodb_file_per_table，以方式管理innodb表空间文件‘ self.result[‘innodb_file_per_table‘]={‘name‘:‘innodb_file_per_table‘,‘value‘:value,‘comment‘:comment} def innodb_io_capacity(self): "1:在合并插入缓冲时，合并插入缓冲数量为innodb_io_capacity的5%; 2:在从缓冲区刷新脏页时，刷新脏页的数量为innodb_io_capacity页。" self.cursor.execute(args.show_global_variables,(‘innodb_io_capacity‘,)) name,value=self.cursor.fetchone() comment=("注意、无法确认最优值，请核对磁盘IO能力。在合并插入缓冲时，合并插入缓冲数量为innodb_io_capacity的5%;" "在从缓冲区刷新脏页时，刷新脏页的数量为innodb_io_capacity页。") self.result[‘innodb_io_capacity‘]={‘name‘:‘innodb_io_capacity‘,‘value‘:value,‘comment‘:comment} def innodb_max_dirty_pages_pct(self): "innodb 在每秒刷新缓冲池时会去判断这个值，如果大于innodb_max_dirty_pages_pct,才会去刷新100个脏页" self.cursor.execute(args.show_global_variables,(‘innodb_max_dirty_pages_pct‘,)) name,value=self.cursor.fetchone() comment=None if int(value) <=74: comment=("注意、innodb_max_dirty_pages_pct 过小；这会增加磁盘的IO负载，请适当增加，推荐值75~80") elif int(value) <=80: comment=‘正常‘ else: comment=‘注意、innodb_max_dirty_pages_pct 过大；脏面数量过大，这会影响服务宕机后，重启的用时‘ self.result[‘innodb_max_dirty_pages_pct‘]={‘name‘:‘innodb_max_dirty_pages_pct‘,‘value‘:value,‘comment‘:comment} def Analyse(self): self.innodb_log_waits() self.innodb_file_per_table() self.innodb_flush_log_at_trx_commit() self.innodb_io_capacity() self.innodb_max_dirty_pages_pct() if __name__=="__main__": parser=argparse.ArgumentParser() parser.add_argument(‘--host‘,default=‘127.0.0.1‘,help=‘ip address of mysql server.....‘) parser.add_argument(‘--port‘,default=3306,type=int,help=‘port number of mysql server....‘) parser.add_argument(‘--user‘,default=‘moniter‘,help=‘mysql user name................‘) parser.add_argument(‘--password‘,default=‘moniter@2048‘,help=‘password of mysql user.........‘) parser.add_argument(‘--mysqlversion‘,default=5.6,choices=[‘5.6‘,‘5.7‘],help=‘version of mysql server........‘) parser.add_argument(‘target‘,default=‘innodb‘,choices=[‘innodb‘,‘binlog‘,‘all‘],help=‘the part of mysql that you want to tuning‘) args=parser.parse_args() #隔离不同版本mysql数据库的差异 if args.mysqlversion==5.6: args.show_global_status=show_global_status_56 args.show_global_variables=show_global_variables_56 elif args.mysqlversion==5.7: args.show_global_status=show_global_status_57 args.show_global_variables=show_global_variables_57 cnx=None cursor=None connection_config={ ‘host‘:args.host, ‘port‘:args.port, ‘user‘:args.user, ‘password‘:args.password } try: cnx=connector.connect(**connection_config) cursor=cnx.cursor() analyst=AnalyseInnodb(cursor,args) analyst.Analyse() analyst.Print() except Exception as err: print(err) finally: if cnx != None: cnx.close() cursor.close()

 

mysql 性能分析套件

标签：