mysql套件_mysql 性能分析套件

1 #!/usr/local/python3.5/bin/python3.5

2 #!coding:utf-8

3 ####################################4 #目地：用于诊断mysql性能问题5 #作者：蒋乐兴6 #时间：2016-07-02

7 #create user moniter@'127.0.0.1' identified by 'moniter@2048';8 #9 ####################################10

11 import mysql.connector asconnector12 import argparse13 import psutil14 import json15 import sys16 import os17

18 show_global_status_56="select variable_name,variable_value from information_schema.global_status where variable_name= %s"

19 show_global_variables_56="select variable_name,variable_value from information_schema.global_variables where variable_name= %s"

20 show_global_status_57="select variable_name,variable_value from performance_schema.global_status where variable_name= %s"

21 show_global_variables_57="select variable_name,variable_value from performance_schema.global_variables where variable_name= %s"

22

23 class AnalyseBase(object):24 def __init__(self,cursor,args):25 self.cursor=cursor26 self.args=args27 self.result={}28

29 def Analyse(self):30 "执行分析函数"

31 pass32 def Print(self):33 print(json.dumps(analyst.result,sort_keys=True,indent=4,ensure_ascii=False))34

35 classAnalyseInnodb(AnalyseBase):36 def innodb_log_waits(self):37 "status:innodb_log_waits innodb 等待刷新redo log 的次，如果它不是0，说明innodb_log_buffer_size 过小"

38 self.cursor.execute(args.show_global_status,('innodb_log_waits',))39 name,value=self.cursor.fetchone()40 comment=None41 if int(value)==0:42 comment='正常'

43 else:44 comment='innodb_log_waits > 0 应该适当增加innodb_log_buffer_size的大小'

45 self.result['innodb_log_waits']={'name':'innodb_log_waits','value':value,'comment':comment}46

47 def innodb_flush_log_at_trx_commit(self):48 ("variables:innodb_flush_log_at_trx_commit 0:事务提交时并不把redo log 写入日志文件，而是等待主线程每秒的刷新。"

49 "1:commit 时同步的方式刷新redo log 到日志文件"

50 "2:commit 时异步的方式刷新redo log 到日志文件")51 self.cursor.execute(args.show_global_variables,('innodb_flush_log_at_trx_commit',))52 name,value=self.cursor.fetchone()53 comment=None54 if int(value)==1:55 comment='正常、由于每个事务完成后都要同步的刷新日志，所以性能不是最好'

56 else:57 comment='注意、有安全隐患;0:事务提交时并不把redo log 写入日志文件，而是等待主线程每秒的刷新;2:commit 时异步的方式刷新redo log 到日志文件。'

58 self.result['innodb_flush_log_at_trx_commit']={'name':'innodb_flush_log_at_trx_commit','value':value,'comment':comment}59

60 def innodb_buffer_pool_size(self):61 self.cursor.execute(args.show_global_variables,('innodb_buffer_pool_size',))62 name,value=self.cursor.fetchone()63 memory_object=psutil.virtual_memory();64 total_memory=memory_object.total65 rate=float(value)/float(total_memory)66 comment=None67 if rate <=0.75:68 comment="注意、innodb_buffer_pool_size 过小;total_memory:{0}{1} / innodb_buffer_pool_size:{2}{3} = {4}%"

69 elif rate<=0.85:70 comment="正常、innodb_buffer_pool_size 合适;total_memory:{0}{1} / innodb_buffer_pool_size:{2}{3} = {4}%"

71 else:72 comment="注意、innodb_buffer_pool_size 过大;total_memory:{0}{1} / innodb_buffer_pool_size:{2}{3} = {4}%"

73 sign=args.memoryunit['sign']74 unit=int(args.memoryunit['unit'])75 value=int(value)76 comment=comment.format(value/unit,sign,total_memory/unit,sign,rate*100)77 self.result['innodb_buffer_pool_size']={'name':'innodb_buffer_pool_size','value':"{0}{1}".format(value/unit,sign),'comment':comment}78

79

80 def innodb_file_per_table(self):81 "variables:innodb_file_per_table 不做成单独表空间的话管理不方便"

82 self.cursor.execute(args.show_global_variables,('innodb_file_per_table',))83 name,value=self.cursor.fetchone()84 comment=None85 if comment=='ON':86 comment='正常'

87 else:88 comment='注意、建议开启innodb_file_per_table，以方式管理innodb表空间文件'

89 self.result['innodb_file_per_table']={'name':'innodb_file_per_table','value':value,'comment':comment}90

91 def innodb_io_capacity(self):92 "1:在合并插入缓冲时，合并插入缓冲数量为innodb_io_capacity的5%; 2:在从缓冲区刷新脏页时，刷新脏页的数量为innodb_io_capacity页。"

93 self.cursor.execute(args.show_global_variables,('innodb_io_capacity',))94 name,value=self.cursor.fetchone()95 comment=("注意、无法确认最优值，请核对磁盘IO能力。在合并插入缓冲时，合并插入缓冲数量为innodb_io_capacity的5%;"

96 "在从缓冲区刷新脏页时，刷新脏页的数量为innodb_io_capacity页。")97 self.result['innodb_io_capacity']={'name':'innodb_io_capacity','value':value,'comment':comment}98

99 def innodb_max_dirty_pages_pct(self):100 "innodb 在每秒刷新缓冲池时会去判断这个值，如果大于innodb_max_dirty_pages_pct,才会去刷新100个脏页"

101 self.cursor.execute(args.show_global_variables,('innodb_max_dirty_pages_pct',))102 name,value=self.cursor.fetchone()103 comment=None104 if int(value) <=74:105 comment=("注意、innodb_max_dirty_pages_pct 过小；这会增加磁盘的IO负载，请适当增加，推荐值75~80")106 elif int(value) <=80:107 comment='正常'

108 else:109 comment='注意、innodb_max_dirty_pages_pct 过大；脏面数量过大，这会影响服务宕机后，重启的用时'

110 self.result['innodb_max_dirty_pages_pct']={'name':'innodb_max_dirty_pages_pct','value':value,'comment':comment}111

112 def Analyse(self):113 self.innodb_log_waits()114 self.innodb_file_per_table()115 self.innodb_flush_log_at_trx_commit()116 self.innodb_io_capacity()117 self.innodb_max_dirty_pages_pct()118 self.innodb_buffer_pool_size()119

120 if __name__=="__main__":121 parser=argparse.ArgumentParser()122 parser.add_argument('--host',default='127.0.0.1',help='ip address of mysql server.....')123 parser.add_argument('--port',default=3306,type=int,help='port number of mysql server....')124 parser.add_argument('--user',default='moniter',help='mysql user name................')125 parser.add_argument('--password',default='moniter@2048',help='password of mysql user.........')126 parser.add_argument('--mysqlversion',default=5.6,choices=['5.6','5.7'],help='version of mysql server........')127 parser.add_argument('--memoryunit',default='MB',choices=['G','GB','M','MB','K','KB'])128 parser.add_argument('target',default='innodb',choices=['innodb','binlog','all'],help='the part of mysql that you want to tuning')129 args=parser.parse_args()130 #隔离不同版本mysql数据库的差异131 if args.mysqlversion==5.6:132 args.show_global_status=show_global_status_56133 args.show_global_variables=show_global_variables_56134 elif args.mysqlversion==5.7:135 args.show_global_status=show_global_status_57136 args.show_global_variables=show_global_variables_57137 #调整内存单位138 unit=1024*1024

139 if args.memoryunit in('G','GB'):140 unit=1024*1024*1024

141 elif args.memoryunit in ('M','MB'):142 unit=1024*1024

143 elif args.memoryunit in ('K','KB'):144 unit=1024

145 args.memoryunit={'sign':args.memoryunit,'unit':unit}146 cnx=None147 cursor=None148 connection_config={149 'host':args.host,150 'port':args.port,151 'user':args.user,152 'password':args.password153 }154 try:155 cnx=connector.connect(**connection_config)156 cursor=cnx.cursor()157 analyst=AnalyseInnodb(cursor,args)158 analyst.Analyse()159 analyst.Print()160 except Exception aserr:161 print(err)162 finally:163 if cnx !=None:164 cnx.close()165 cursor.close()