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 as connector
12 import argparse
13 import psutil
14 import json
15 import sys
16 import os
17
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=cursor
26 self.args=args
27 self.result={}
28
29 def Analyse(self):
30 "执行分析函数"
31 pass
32 def Print(self):
33 print(json.dumps(analyst.result,sort_keys=True,indent=4,ensure_ascii=False))
34
35 class AnalyseInnodb(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=None
41 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=None
54 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.total
65 rate=float(value)/float(total_memory)
66 comment=None
67 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=None
85 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=None
104 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_56
133 args.show_global_variables=show_global_variables_56
134 elif args.mysqlversion==5.7:
135 args.show_global_status=show_global_status_57
136 args.show_global_variables=show_global_variables_57
137 #调整内存单位
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=None
147 cursor=None
148 connection_config={
149 'host':args.host,
150 'port':args.port,
151 'user':args.user,
152 'password':args.password
153 }
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 as err:
161 print(err)
162 finally:
163 if cnx != None:
164 cnx.close()
165 cursor.close()
