2017-07-27 DBA日记,使用Python解释mysql 5.6 slowquery.log

来源:互联网 发布:c语言socket编程指南 编辑:程序博客网 时间:2024/06/16 04:44
目的(Why)
在mysql 5.6环境下,提高slow_query.log的可读性,快速定位慢查询SQL
使用人群(Who)
MySql DBA
功能描述(What)
1. 数据采集:开启mysql slow query log,方法如下:
set global slow_query_log='ON'
set global slow_query_log_file='/u01/log/slow_query.log'
set global slow_launch_time=2
2. ETL:使用python代码,解析slow_query.log,执行ETL转换后,存储在MYSQL数据库中。
3. 聚合统计,按执行次数,按执行时间排序。按不同维度的优化先级找出最需要进行优化的SQL语句。
什么时候做(When)
1)每天早上9-10点期间分析昨天晚上至凌晨的慢SQL
2)每天下午17点期,分析白天的慢SQL
执行地点(Where)
目前处于demo阶段,都是通过ssh SFTP等工具,把日志拉回到桌面机上进行分析。
各位可以根据实际需要,在python代码中封装下载文件或远程打开的接口,也可以把python程序放了在MYSQL主机上自动运行,我们在后台数据库查询,这个就靠各位脑补了。
实现代码(HOW):
1) 先决条件:
a)安装mysql connector ptyhon包,网址如下:
https://dev.mysql.com/downloads/connector/python/
b) python 运行环境
c) MYSQL客户端工具,TOAD,NAVICAT
2) 存放slow query 表设计代码:
CREATE TABLE `slow_query` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `start_time` varchar(50) CHARACTER SET gbk COLLATE gbk_bin DEFAULT NULL,  `username` varchar(50) CHARACTER SET gbk COLLATE gbk_bin DEFAULT NULL,  `duration` decimal(10,2) DEFAULT NULL,  `sqltext` text CHARACTER SET latin1,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
3)Python代码
#!/usr/bin/python
#-*-coding:utf-8 -*-'''开辟一个数组,存放三个字段,执行时长,执行用户,SQL前50个字符, SQL完整字符功能:1) 找出执行时间最长的sql语句2) 重复的SQL语句合并,并显示执行时间最长的SQL语句步骤:1)LOG日志结构化2) 合并去重3) 统计'''import mysql.connector as mctry:conn = mc.connect(user='username', password='pwd', host='192.168.0.1', database='qqt')cur=conn.cursor()Inssql='''insert into qqt.slow_query (  start_time  ,username  ,duration  ,sqltext) VALUES (%s,%s,%s,%s)'''print 'importing slow sql to mysql.......'except Exception as e:print eslog=open('.//slowlog-17.log','r')lines=slog.readlines()print len(lines)print "\#"status=Falsea=0Sql=[]Sqlset=[]Sqltext=''#格式化slow_query.log ,  算法,连续3行是以“#”之后的,不是以#开头的为一个分段;#当遇到“#”开头的,但计数器由0开始统计,显示大于第3行时重,所有变量重置,重新计数#列表加入到新列表,并重新赋值时,不影响新列表各列表元素的值。for line in lines[4:]:if line[0]=="#" and a>=3:Sqlset.append(Sql)Sql=[]Sql.append(line)#print Sqlseta=0a=a+1elif line[0]!="#" and a<3:#print lineSql=[]a=0elif line[0]=="#" and a<3:field=line[1:].split(":")Sql.append(field[1].split()[0])a=a+1elif line[0]!="#" and a>=3:if line[0:3] in ('use','SET'):passelse:Sql.append(line)a=a+1#print Sqlset[1]for sql1 in Sqlset:#print sql1[0][8:],sql1[1],sql1[2],sql1[3:]try:#print sql1[1].split('[')[0],sql1[2]start_time=sql1[0][8:27]username=sql1[1].split('[')[0]duration=round(float(sql1[2]),2)Sqltext=''for text in sql1[3:]:Sqltext=Sqltext+textprint '#',cur.execute(Inssql,(start_time,username,duration,Sqltext))conn.commit()except Exception as e:print e'''聚合去重+统计,以下是可选部份,完全可以通过mysql客户端完成''''''秉承将事交给更专业的工具完成原则,将slow query log结构化后,导入到数据库,并通过sql语句的group by方法快速完成聚合和统计. '''SelSQL='''select substring(sqltext,1,50) text,(select sqltext from qqt.slow_query where substring(sqltext,1,50)=text limit 1) sql_fulltext,count(*),max(duration)from qqt.slow_querygroup by substring(sqltext,1,50)order by 3 desclimit 30'''



阅读全文
0 0
原创粉丝点击