MySQL慢SQL采集方案分析
本篇内容主要讲解“MySQL慢SQL采集方案分析”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“MySQL慢SQL采集方案分析”吧!
前期准备作为一名MySQL DBA，首要的任务是维持数据库的可用性和稳定性，在生产中，有时候一条慢SQL会拖垮整个系统的响应和体验，所以慢SQL治理至关重要。
首先我们采用pt-query-digest来进行慢sql采集和分析
mysql>showglobalvariableswherevariable_namein('slow_query_log','long_query_time','slow_query_log_file','log_queries_not_using_indexes','log_throttle_queries_not_using_indexes','min_examined_row_limit','log_slow_admin_statements','log_slow_slave_statements','log_output');+----------------------------------------+----------------------------+|Variable_name|Value|+----------------------------------------+----------------------------+|log_output|FILE||log_queries_not_using_indexes|ON||log_slow_admin_statements|OFF||log_slow_slave_statements|ON||log_throttle_queries_not_using_indexes|100||long_query_time|0.500000||min_examined_row_limit|0||slow_query_log|ON||slow_query_log_file|slowquery_201908161156.log|+----------------------------------------+----------------------------+9rowsinset(0.01sec)
该mysql版本为percona5.7.21版本，控制slow log的参数主要为这几个：
log_output: 控制慢sql是记录在文件还是记录在table,FILE|TABLE可选择
log_queries_not_using_indexes：控制没有使用索引的sql也将被记录到慢查询日志中;
log_slow_admin_statements：管理语句执行时间大于阈值也将写入到慢查询日志中，管理语句包括alter table, check table等;
log_slow_slave_statements：从库应用binlog，如果binlog格式是statement，执行时间超过阈值时，将写入从库的慢查询日志， 对于ROW格式binlog，不管执行时间有没有超过阈值，都不会写入到从库的慢查询日志;
log_throttle_queries_not_using_indexes：如果log_queries_not_using_indexes打开，没有使用索引的sql将会写入到慢查询日志中，该参数将限制每分钟写入的sql数量；
long_query_time：慢查询阈值，单位秒，执行时间超过这个值的将被记录为慢查询日志中;
min_examined_row_limit：对于查询扫描行数小于此参数的sql，将不会记录到慢查询日志中；
slow_query_log：控制是否打开慢查询；
slow_query_log_file：慢查询日志记录文件；
这些参数我们目前主要使用log_output ,slow_query_log ,slow_query_log_file,min_examined_row_limit,log_throttle_queries_not_using_indexes这几个
mysql>setgloballog_output='FILE';QueryOK,0rowsaffected(0.00sec)mysql>setglobalslow_query_log=1;QueryOK,0rowsaffected(0.00sec)mysql>setglobalmin_examined_row_limit=0;QueryOK,0rowsaffected(0.00sec)mysql>setgloballog_throttle_queries_not_using_indexes=1;QueryOK,0rowsaffected(0.00sec)mysql>setglobalslow_query_log_file='slowquery_201908161203.log';QueryOK,0rowsaffected(0.00sec)
设置参数后开始安装pt工具
pt-query-digest工具简介安装和使用
我们这里直接安装个全家桶(percona-toolkit)吧，首先安装依赖包:
yuminstall-yperlperl-IO-Socket-SSLperl-DBD-MySQLperl-Time-HiResperl-Digest-MD5perl-ExtUtils-MakeMaker
然后下载percona-toolkit:
https://www.percona.com/downloads/percona-toolkit/LATEST/
这里是cenos7环境，如果是其他环境另行下载
我们直接安装 rpm -ivh percona-toolkit-3.0.13-1.el7.x86_64.rpm
pt-query-digest[OPTIONS][FILES][DSN]--create-review-table当使用--review参数把分析结果输出到表中时，如果没有表就自动创建。--create-history-table当使用--history参数把分析结果输出到表中时，如果没有表就自动创建。--filter对输入的慢查询按指定的字符串进行匹配过滤后再进行分析--limit限制输出结果百分比或数量，默认值是20,即将最慢的20条语句输出，如果是50%则按总响应时间占比从大到小排序，输出到总和达到50%位置截止。--hostmysql服务器地址--usermysql用户名--passwordmysql用户密码--history将分析结果保存到表中，分析结果比较详细，下次再使用--history时，如果存在相同的语句，且查询所在的时间区间和历史表中的不同，则会记录到数据表中，可以通过查询同一CHECKSUM来比较某类型查询的历史变化。--review将分析结果保存到表中，这个分析只是对查询条件进行参数化，一个类型的查询一条记录，比较简单。当下次使用--review时，如果存在相同的语句分析，就不会记录到数据表中。--output分析结果输出类型，值可以是report(标准分析报告)、slowlog(Mysqlslowlog)、json、json-anon，一般使用report，以便于阅读。--since从什么时间开始分析，值为字符串，可以是指定的某个”yyyy-mm-dd[hh:mm:ss]”格式的时间点，也可以是简单的一个时间值：s(秒)、h(小时)、m(分钟)、d(天)，如12h就表示从12小时前开始统计。--until截止时间，配合—since可以分析一段时间内的慢查询。pt-query-digest 一些用例
1、分析慢查询文件:
pt-query-digestslow-query.log>slow-query-report.log
2、分析最近1小时内的查询：
pt-query-digest--since=1hslow-query.log>slow-before_1h.log
3、分析指定时间范围内的查询：
pt-query-digestslow-query.log--since'2019-08-0810:30:00'--until'2019-08-0810:35:00'>slow-query-5min.log
4、针对某个用户的慢查询
pt-query-digest--filter'($event->{user}||"")=~m/^dba/i'slow-query.log>slow-query-fordbauser.log
5、查询所有所有的全表扫描或full join的慢查询
pt-query-digest--filter'(($event->{Full_scan}||"")eq"yes")||(($event->{Full_join}||"")eq"yes")'slow-query.log>slow_query-full.log
6、把查询保存到t_slowq_review表
pt-query-digest--user=dbms–password=admin--reviewh=localhost,D=test,t=t_slowq_review--create-review-tableslow-query.log
7、把查询保存到t_slowq_details表
pt-query-digest--user=dbms–password=admin--historyh=localhost,D=test,t=t_slowq_details--create-history-tableslow-query.log
8、分析指含有select语句的慢查询
pt-query-digest--filter'$event->{fingerprint}=~m/^select/i'slow-query.log>slow-query-justselect.log
9、通过tcpdump抓取mysql的tcp协议数据，然后再分析
tcpdump-s65535-x-nn-q-tttt-iany-c1000port3306>mysql.tcp3306.txtpt-query-digest--typetcpdumpmysql.tcp3306.txt>slow-query-3306.log
10、分析binlog
mysqlbinlogmysql-bin.000011>mysql-bin.000011.sqlpt-query-digest--type=binlogmysql-bin.000011.sql>mysql-bin.000011.log
11.分析general log
pt-query-digest--type=genloggeneral_3306.log>generallog_3306.log直接解析入库的方案：
这里采用直接入库的方式，然后会采用脚本去切割，可以使用时间间隔切割，然后删除，尝试过since util，发现文本越大，每次的解析时间都会很长，不太适合生产部署，可以采用切割日志，如果需要保留本地的话，可以拷贝走
参考解析命令pt-query-digest\--user=user--password=password--port=port\--reviewh=ip,D=dbname,t=t_slowq_review\--historyh=ip,D=dbname,t=t_slowq_details\--no-report--limit=100%--charset=utf8\--filter="\$event->{Bytes}=length(\$event->{arg})and\$event->{instanceid}=15and\$event->{hostname}='idc-mysql18'and\$event->{client}=\$event->{ip}"\slow-query.log
在生产中由于可能存在分库，所以sql的checksum值可能会一致，所以我们加了每个实例的id进去来标识不同的数据库，完全靠checksum值来标记可能混淆
参考表结构：CREATETABLE`t_slowq_details`(`instanceid_max`int(11)NOTNULL,`hostname_max`varchar(64)NOTNULL,`client_max`varchar(64)DEFAULTNULL,`user_max`varchar(64)NOTNULL,`db_max`varchar(64)DEFAULTNULL,`checksum`char(32)NOTNULL,`sample`longtextNOTNULL,`ts_min`timestampNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP,`ts_max`datetime(6)NOTNULL,`ts_cnt`floatDEFAULTNULL,`Query_time_sum`floatDEFAULTNULL,`Query_time_min`floatDEFAULTNULL,`Query_time_max`floatDEFAULTNULL,`Query_time_pct_95`floatDEFAULTNULL,`Query_time_stddev`floatDEFAULTNULL,`Query_time_median`floatDEFAULTNULL,`Lock_time_sum`floatDEFAULTNULL,`Lock_time_min`floatDEFAULTNULL,`Lock_time_max`floatDEFAULTNULL,`Lock_time_pct_95`floatDEFAULTNULL,`Lock_time_stddev`floatDEFAULTNULL,`Lock_time_median`floatDEFAULTNULL,`Rows_sent_sum`floatDEFAULTNULL,`Rows_sent_min`floatDEFAULTNULL,`Rows_sent_max`floatDEFAULTNULL,`Rows_sent_pct_95`floatDEFAULTNULL,`Rows_sent_stddev`floatDEFAULTNULL,`Rows_sent_median`floatDEFAULTNULL,`Rows_examined_sum`floatDEFAULTNULL,`Rows_examined_min`floatDEFAULTNULL,`Rows_examined_max`floatDEFAULTNULL,`Rows_examined_pct_95`floatDEFAULTNULL,`Rows_examined_stddev`floatDEFAULTNULL,`Rows_examined_median`floatDEFAULTNULL,`Rows_affected_sum`floatDEFAULTNULL,`Rows_affected_min`floatDEFAULTNULL,`Rows_affected_max`floatDEFAULTNULL,`Rows_affected_pct_95`floatDEFAULTNULL,`Rows_affected_stddev`floatDEFAULTNULL,`Rows_affected_median`floatDEFAULTNULL,`Rows_read_sum`floatDEFAULTNULL,`Rows_read_min`floatDEFAULTNULL,`Rows_read_max`floatDEFAULTNULL,`Rows_read_pct_95`floatDEFAULTNULL,`Rows_read_stddev`floatDEFAULTNULL,`Rows_read_median`floatDEFAULTNULL,`Merge_passes_sum`floatDEFAULTNULL,`Merge_passes_min`floatDEFAULTNULL,`Merge_passes_max`floatDEFAULTNULL,`Merge_passes_pct_95`floatDEFAULTNULL,`Merge_passes_stddev`floatDEFAULTNULL,`Merge_passes_median`floatDEFAULTNULL,`InnoDB_IO_r_ops_min`floatDEFAULTNULL,`InnoDB_IO_r_ops_max`floatDEFAULTNULL,`InnoDB_IO_r_ops_pct_95`floatDEFAULTNULL,`InnoDB_IO_r_ops_stddev`floatDEFAULTNULL,`InnoDB_IO_r_ops_median`floatDEFAULTNULL,`InnoDB_IO_r_bytes_min`floatDEFAULTNULL,`InnoDB_IO_r_bytes_max`floatDEFAULTNULL,`InnoDB_IO_r_bytes_pct_95`floatDEFAULTNULL,`InnoDB_IO_r_bytes_stddev`floatDEFAULTNULL,`InnoDB_IO_r_bytes_median`floatDEFAULTNULL,`InnoDB_IO_r_wait_min`floatDEFAULTNULL,`InnoDB_IO_r_wait_max`floatDEFAULTNULL,`InnoDB_IO_r_wait_pct_95`floatDEFAULTNULL,`InnoDB_IO_r_wait_stddev`floatDEFAULTNULL,`InnoDB_IO_r_wait_median`floatDEFAULTNULL,`InnoDB_rec_lock_wait_min`floatDEFAULTNULL,`InnoDB_rec_lock_wait_max`floatDEFAULTNULL,`InnoDB_rec_lock_wait_pct_95`floatDEFAULTNULL,`InnoDB_rec_lock_wait_stddev`floatDEFAULTNULL,`InnoDB_rec_lock_wait_median`floatDEFAULTNULL,`InnoDB_queue_wait_min`floatDEFAULTNULL,`InnoDB_queue_wait_max`floatDEFAULTNULL,`InnoDB_queue_wait_pct_95`floatDEFAULTNULL,`InnoDB_queue_wait_stddev`floatDEFAULTNULL,`InnoDB_queue_wait_median`floatDEFAULTNULL,`InnoDB_pages_distinct_min`floatDEFAULTNULL,`InnoDB_pages_distinct_max`floatDEFAULTNULL,`InnoDB_pages_distinct_pct_95`floatDEFAULTNULL,`InnoDB_pages_distinct_stddev`floatDEFAULTNULL,`InnoDB_pages_distinct_median`floatDEFAULTNULL,`QC_Hit_cnt`floatDEFAULTNULL,`QC_Hit_sum`floatDEFAULTNULL,`Full_scan_cnt`floatDEFAULTNULL,`Full_scan_sum`floatDEFAULTNULL,`Full_join_cnt`floatDEFAULTNULL,`Full_join_sum`floatDEFAULTNULL,`Tmp_table_cnt`floatDEFAULTNULL,`Tmp_table_sum`floatDEFAULTNULL,`Tmp_table_on_disk_cnt`floatDEFAULTNULL,`Tmp_table_on_disk_sum`floatDEFAULTNULL,`Filesort_cnt`floatDEFAULTNULL,`Filesort_sum`floatDEFAULTNULL,`Filesort_on_disk_cnt`floatDEFAULTNULL,`Filesort_on_disk_sum`floatDEFAULTNULL,`Bytes_sum`floatDEFAULTNULL,`Bytes_min`floatDEFAULTNULL,`Bytes_max`floatDEFAULTNULL,`Bytes_pct_95`floatDEFAULTNULL,`Bytes_stddev`floatDEFAULTNULL,`Bytes_median`floatDEFAULTNULL,PRIMARYKEY(`instanceid_max`,`checksum`,`ts_min`,`ts_max`)USINGBTREE,KEY`idx_hostname_max_ts_min`(`instanceid_max`,`ts_min`))ENGINE=InnoDBDEFAULTCHARSET=utf8/*!50100PARTITIONBYRANGE(UNIX_TIMESTAMP(ts_min))(PARTITIONp201908VALUESLESSTHAN(1567267200)ENGINE=InnoDB,PARTITIONp201909VALUESLESSTHAN(1569859200)ENGINE=InnoDB,PARTITIONp201910VALUESLESSTHAN(1572537600)ENGINE=InnoDB,PARTITIONp201911VALUESLESSTHAN(1575129600)ENGINE=InnoDB,PARTITIONp201912VALUESLESSTHAN(1577808000)ENGINE=InnoDB)*/CREATETABLE`t_slowq_review`(`checksum`char(32)NOTNULL,`fingerprint`longtextNOTNULL,`sample`longtextNOTNULL,`first_seen`timestampNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP,`last_seen`datetime(6)DEFAULTNULL,`reviewed_by`varchar(20)DEFAULTNULL,`reviewed_on`datetime(6)DEFAULTNULL,`comments`longtext,`reviewed_status`varchar(24)DEFAULTNULL,PRIMARYKEY(`checksum`)USINGBTREE,KEY`idx_last_seen`(`last_seen`)USINGBTREE)ENGINE=InnoDBDEFAULTCHARSET=utf8参考python 采集脚本
这个脚本只是提供一个思路，逻辑就是，会自动去切割slowlog，然后解析上传，切割后解析玩会删除前一个日志，如果需要保留本地日志可以把代码里面的删除改成拷贝走，其中我提出的instance_id是我们觉得生产中只有checksum没法和系统进行结合和日常使用，各位可以参考使用，并进行改造，其中有啥问题可以留言????
#!/usr/bin/envpython#-*-coding:utf-8-*-#create_time:2019-08-0811:20importsysimportosimportsocketimporttimefrommultiprocessingimportProcessimportsubprocessimportpymysqlasMySQLdbDBMS_HOST=""#存储数据库的ipDBMS_NAME=""#存储数据库的dbnameDBMS_PORT=#存储数据库的端口DBMS_PWD=""#存储数据库的账号密码DBMS_USER=""#存储数据库的账号DBA_HOST=""#被采集实例的ip，一般默认为127.0.0.1DBA_USER=""#被采集实例的用户DBA_PASSWORD=""#被采集实例用户密码#一般数据实例上建立一个dba管理账号来使用classMySQLConnection(object):def__init__(self,host,port,user,passwd,db=None,charset="utf8"):self.host=hostself.port=portself.user=userself.passwd=passwdself.db=dbself.charset=charsetself.error=""defSuccess(self):returnself.error==""defConnect(self):self.error=""try:self.conn=MySQLdb.connect(host=self.host,port=self.port,user=self.user,passwd=self.passwd,charset=self.charset)ifself.db:self.conn.select_db(self.db)self.cursor=self.conn.cursor()exceptExceptionase:self.error=str(e)defClose(self):self.error=""try:self.cursor.close()self.conn.close()exceptExceptionase:self.error=str(e)defFileds(self):res=[]try:forrinself.cursor.description:res.append(r[0])except:passreturnresdefQuery(self,sql,params=None,rownum=[0],fetch=0):self.error=""data=()try:ifparams:rownum[0]=self.cursor.execute(sql,params)else:rownum[0]=self.cursor.execute(sql)ifrownum[0]>0:iffetch==0:data=self.cursor.fetchall()eliffetch==1:data=self.cursor.fetchone()exceptExceptionase:self.error=str(e)returndatadefExecute(self,sql,params=None):self.error=""try:ifparams:self.cursor.execute(sql,params)else:self.cursor.execute(sql)self.conn.commit()exceptExceptionase:self.error=str(e)returnFalsereturnTruedefRunCommand(c):p=subprocess.Popen(c,stdout=subprocess.PIPE,stderr=subprocess.PIPE,shell=True)(stdout,stderr)=p.communicate()return(stdout.decode("utf-8"),stderr.decode("utf-8"))deflog_out(instance_id,start=time.time(),error=None):"""这里是用来记录慢sql解析情况的，会上报相关状态"""dbms_con=MySQLConnection(DBMS_HOST,DBMS_PORT,DBMS_USER,DBMS_PWD,DBMS_NAME)dbms_con.Connect()ifdbms_con.error:returnstart_time=time.strftime('%Y-%m-%d%T',time.localtime(start))dbms_con.Execute("replaceintot_slowq_message(instance_id,error,start_time)values(%s,%s,%s)",(instance_id,error,start_time))dbms_con.Close()defGetSlowlogfile(instanceid,port):"""slowlog=datadir+slowlog:paramport::return:"""local_conn=MySQLConnection(host=DBA_HOST,port=port,user=DBA_USER,passwd=DBA_PASSWORD)local_conn.Connect()ifnotlocal_conn.error:data=local_conn.Query("showglobalvariableswherevariable_namein('long_query_time','slow_query_log_file','datadir')")ifdata:res={data[0][0]:data[0][1],data[1][0]:data[1][1],data[2][0]:data[2][1],}local_conn.Close()returnreslocal_conn.Close()error="getslowlog_fileparemetersfailed"log_out(instance_id=instanceid,error=error)returnNoneelse:log_out(instance_id=instanceid,error=str(local_conn.error))local_conn.Close()returnNonedefSwitchSlowlogfile(port):local_conn=MySQLConnection(host=DBA_HOST,port=port,user=DBA_USER,passwd=DBA_PASSWORD)local_conn.Connect()iflocal_conn.error:returnFalsetmp_log="slowquery_%s.log"%(time.strftime('%Y%m%d%H%M',time.localtime(time.time())),)res=local_conn.Execute("setglobalslow_query_log_file=%s",tmp_log)returnTruedefCollectSlowlog(instanceid,port,hostname):res=GetSlowlogfile(instanceid,port)ifres:slowlog_time=eval(res["long_query_time"])slowlog_file=res["slow_query_log_file"]data_dir=res['datadir']slow_log=data_dir+slowlog_fileifSwitchSlowlogfile(port):try:pt_query_digest=RunCommand("whereispt-query-digest")[0].split()[1]except:pt_query_digest="/usr/bin/pt-query-digest"cmd="%s--user=%s--password=%s--port=%s--reviewh=%s,D=%s,t=%s--historyh=%s,D=%s,t=%s--no-report--limit=100%%--charset=utf8"\"--filter=\"\\$event->{Bytes}=length(\\$event->{arg})and\\$event->{instanceid}=%sand\\$event->{hostname}='%s'"\"and\$event->{client}=\$event->{ip}\"%s"%(pt_query_digest,DBMS_USER,DBMS_PWD,DBMS_PORT,DBMS_HOST,DBMS_NAME,"t_slowq_review",DBMS_HOST,DBMS_NAME,"t_slowq_details",instanceid,hostname,slow_log)out,error=RunCommand(cmd)ifnoterror:os.remove(slow_log)log_out(instance_id=instanceid)else:error="parseslowlog_filefailed"log_out(instance_id=instanceid,error=error)else:error="switchslowlog_filefailed"log_out(instance_id=instanceid,error=error)defMain():"""该部分可以自己进行改造，我这边的逻辑是我有一张记录实例的元数据库，来查询当前主机上面的实例，然后获取实例id和port，各位可以自行改造"""try:hostname=socket.gethostname()dbms_con=MySQLConnection(DBMS_HOST,DBMS_PORT,DBMS_USER,DBMS_PWD,DBMS_NAME)dbms_con.Connect()ifdbms_con.error:returnsql1="SELECTa.instance_id,a.portFROMt_info_instanceASaJOINt_info_machineASbONa.m_id=b.m_idWHEREb.hostname=%s"res1=dbms_con.Query(sql1,(hostname,))ifres1!=Noneandlen(res1)>0:forinstanceinres1:instance_id=instance[0]port=instance[1]p=Process(target=CollectSlowlog,args=(instance_id,port,hostname))p.start()dbms_con.Close()exceptExceptionase:print(e)if__name__=="__main__":Main()
其他
另一张状态表表结构：
CREATETABLE`t_slowq_message`(`instance_id`int(11)NOTNULL,`error`text,`start_time`datetimeNOTNULL,`end_time`timestampNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP,PRIMARYKEY(`instance_id`))ENGINE=InnoDBDEFAULTCHARSET=utf8
到此，相信大家对“MySQL慢SQL采集方案分析”有了更深的了解，不妨来实际操作一番吧！这里是亿速云网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！
声明：本站所有文章资源内容，如无特殊说明或标注，均为采集网络资源。如若本站内容侵犯了原著者的合法权益，可联系本站删除。
