python分析Mysql慢查询。通过Python调用开源分析工具
pt-query-digest
生成json结果,Python脚本解析json生成html报告。
0x00 直接上代码
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169
#!/usr/bin/env python# -*- coding: utf-8 -*-# @Time : 2018/10/12 下午3:00# @Author : Kionf# @Site : https://kionf.com# @Software: Sublimeimport subprocessimport jsonimport osimport reimport sysimport timefrom jinja2 import Environment, FileSystemLoader# reload(sys)# sys.setdefaultencoding('utf-8')LibToolkit = 'pt-query-digest'LibToolkit_url = 'https://github.com/kalivim/Mysql-SlowLog-analysis/raw/master/pt-query-digest'HtmlTemplate = 'template.html'HtmlTemplate_url = 'https://github.com/kalivim/Mysql-SlowLog-analysis/raw/master/template.html'#检测极赖os.system('rpm -q perl-Digest-MD5 || yum -y -q install perl-Digest-MD5')class RunAndCheckCommand:def __init__(self, commands, task_name, ret_code=0):self.commands = commandsself.task_name = task_nameself.ret_code = ret_codedef check_command_status_code(self):"""检测任务"""if self.exp_code == self.ret_code:print("\033[92m [INFO]>> %s \033[0m" % self.task_name)else:print("\033[91m [ERROR]>> %s \033[0m" % self.task_name)exit(1)def exec_command_stdout_res(self):"""执行命令实时返回命令输出:return:"""command_res = subprocess.Popen(self.commands, shell=True)while command_res.poll():line = command_res.stdout.readline()line.strip()if line:print(line)command_res.wait()self.exp_code = command_res.returncodeself.check_command_status_code()class AnalysisMysqlSlowLog:"""分析Mysql慢查询日志输出报告。调用第三方工具包percona-toolkit中pt-query-digest工具,默认输出slow.json文件Perl语言编写"""def __init__(self, slow_log_file, json_file, report_file):""":param slow_log_file: 需要分析的慢查询日志文件:param report_file: 生成报告文件名"""self.LibToolkit = LibToolkitself.json_file = json_fileself.report_file = report_fileself.slow_log_file = slow_log_fileself.query_digest = "perl %s %s --output json --progress time,1 > %s" % (self.LibToolkit, slow_log_file, self.json_file)def check_argv_options(self):get_toolkit = os.path.isfile(HtmlTemplate)get_template = os.path.isfile(LibToolkit)get_slow_log = os.path.isfile(self.slow_log_file)if not get_toolkit:res = RunAndCheckCommand('wget %s 2>/dev/null' % LibToolkit_url, '下载pt-query-digest工具')res.exec_command_stdout_res()if not get_template:res = RunAndCheckCommand('wget %s 2>/dev/null' % HtmlTemplate_url, '下载报告HTML模板')res.exec_command_stdout_res()if not get_slow_log:print("\033[91m [ERROR]>> 指定 %s 慢查询日志不存在 \033[0m" % self.slow_log_file)exit(1)def general_html_report(self, sql_info):env = Environment(loader=FileSystemLoader(os.path.dirname(__file__)))template = env.get_template(HtmlTemplate)html_content = template.render(sql_info=sql_info)with open(self.report_file, 'wa') as f:f.write(html_content.encode('utf-8'))def general_json_slow_log_report(self):"""调用第三方工具pt-query-digest生成json报告,并获取需要信息:return: digest slow_log format to json"""self.check_argv_options()RunCommandsOBJ = RunAndCheckCommand(self.query_digest, '生成Json报告')RunCommandsOBJ.exec_command_stdout_res()f = open(self.json_file, 'ra')format_dict_all_data = json.load(f)have_slow_query_tables = []all_sql_info = []all_slow_query_sql_info = format_dict_all_data['classes']global_sql_info = format_dict_all_data['global']for slow_query_sql in all_slow_query_sql_info:query_metrics = slow_query_sql['metrics']query_time = query_metrics['Query_time']query_tables = slow_query_sql['tables']for show_tables_sql in query_tables:get_table_name = show_tables_sql['create'].split('.')[1]table_name = re.match(r'`(\w*)`\\G', get_table_name).group(1)if table_name not in have_slow_query_tables:have_slow_query_tables.append(table_name)sql_info = {'ID': slow_query_sql['checksum'],'query_time_max': query_time['max'],'query_time_min': query_time['min'],'query_time_95': query_time['pct_95'],'query_time_median': query_time['median'],'query_row_send_95': query_metrics['Rows_sent']['pct_95'],'query_db': query_metrics['db']['value'],'slow_query_count': slow_query_sql['query_count'],'slow_query_tables': have_slow_query_tables,'sql': slow_query_sql['example']['query'],}all_sql_info.append(sql_info)all_sql_info = sorted(all_sql_info, key=lambda e: float(e['query_time_95']), reverse=True)return all_sql_infoclass SendReportForEmail:passdef help_msg():"""输出帮助信息"""msg = """Usage:./ slow-query-analysis.py 慢查询日志 生成json报告文件名 生成html报告文件名"""print(msg)if __name__ == "__main__":if len(sys.argv) == 4:slow_log_name = sys.argv[1]json_file_name = sys.argv[2]report_name = sys.argv[3]print('\033[92m ====开始分析慢查询日志==== \033[0m')obj = AnalysisMysqlSlowLog(slow_log_file=slow_log_name, json_file=json_file_name, report_file=report_name)res_json_report = obj.general_json_slow_log_report()obj.general_html_report(res_json_report)else:help_msg()
0x01 脚本使用方法
wget https://raw.githubusercontent.com/kalivim/Mysql-SlowLog-analysis/master/analysis-slow-log.pychmod +x analysis-slow-log.py./analysis-slow-log.py Mysql_SlowLog_file general_json_filename report_name
参数介绍
Mysql_SlowLog_file mysql慢查询日志的文件名general_json_filename 脚本生成的json报告文件名report_name 生成的Html报告名
生成结果:
0x02 pt-query-digest工具介绍
pt-query-digest
为percona
工具包中的一个perl脚本,用来分析Mysql慢查询日志,或者tcpdump的Mysql的数据包。生成结果有三种格式可以选择:json,report, Mysql
常用语句:
- 将结果和历史分析记录写入Mysql数据库(会自动创建表,需指定写入数据库)
1
./pt-query-digest --progress time,1 --review h=172.16.8.185,D=kionf_dev,t=query_review,u=root,p=passwd --history h=172.16.8.185,D=kionf_dev,t=query_history,u=root,p=passwd mysql-M-slow.log
- 生成json报告
./pt-query-digest --progress time,1 --output json mysql-slow.log
- 生成report
./pt-query-digest mysql-slow.log
选项介绍:
--progess time,1 显示慢查询分析进度,每1s刷新一次--review 将分析结果保存到表中,这个分析只是对查询条件进行参数化,一个类型的查询一条记录,比较简单。下次使用--review时,存在相同的语句分析,不会记录。--history 将分析结果保存到表中,分析结果比较详细,下次再使用--history时,如果存在相同的语句,且查询所在的时间区间和历史表中的不同,则会记录到数据表中--output 分析结果输出类型。 json report json-anon