不一样的SQL监控，使用perfomance schema填补slow log的空白

作者：VEPHP 时间 2017-09-18

《不一样的SQL监控，使用perfomance schema填补slow log的空白》要点：
本文介绍了不一样的SQL监控，使用perfomance schema填补slow log的空白，希望对您有用。如果有疑问，可以联系我们。

作者介绍

刘世勇,曾就职于华为、网易,2015年初加入链家网,负责链家网数据库的运维、数据库架构设计、DB自动化运维平台的构建等工作.

1.引言

在MySQL中,通过slow log,我们可以发现线上的慢查询,并且结合监控和pt-toolkit,我们能够比较快速地发现慢查询,并且根据总响应时间、执行次数、平均响应时间等多个维度去分析和统计慢查询.可是如果我们的DB是经过了大量的SQL和业务优化,在当前的配置下,slow log中几乎已经没有慢查询了,可是整个库的负载还是非常高,或者是某些项目刚上线,经常会因为缓存策略不合理导致大量的请求穿透到DB,可是这些请求又不是慢查,很难排查这种抖动的问题.这个时候怎么办呢?我们能够以什么为切入点更深一步地做一些调优呢?

一种方法就是将slow log的阈值尽可能的减小,或者打开general log,可是这样对性能的影响是非常大的,因为会新增很多的IO操作.当然可以间歇性地调整,可是这样运维成本和复杂度就高了,一两个集群还勉强,集群多了之后,问题就会凸显出来了.而且从另一方面来讲,这种计划性的调整很难应对线上一些偶发性的问题.

还有没有别的办法呢?此时我忍不住怀念起Oracle的AWR.使用过Oracle的同学都知道,Oracle是一个功能强大的性能分析工具,看懂AWR报告也是Oracle DBA的基本功之一.在AWR里面,有个SQL stat的功能,实际上就是对某段时间内,整个DB执行过的SQL统计和分析报告.区别于MySQL slow log的是,这种统计报告是全量的,而slow log仅仅只是慢查,遇到一些高频的、快速的查询,slow log就没有了参考价值.有前辈高人参考Oracle的AWR写过一个myawr(https://github.com/noodba/myawr),但是这个工具也仅仅支持了slow log的统计结果.那有没有办法能够像Oracle AWR一样,能够统计和分析全量的SQL执行情况呢?有,那就是perfomance schema.当然仅仅是perfomance schema还不够,还需要将其中的SQL执行的统计数据拿出来作分析和展示.分析数据和展示数据都是借助myawr来做的.

本文内容主要分析如何开启PS来获得SQL执行的统计信息、怎么将全量的SQL统计信息录入myawr、怎么分析和展示SQL执行的统计数据.

2.开启PS

在使用perfomance schema之前,必须先开启它.开始PS其实比较简单,只要在配置文件中添加下面一行配置就可以：

performance_schema=ON

但是,仅仅打开PS还不够,因为默认PS开启的功能比较少.因为我们需要做全量的SQL统计,所以需要依赖PS中events_statements_summary_by_digest这个表的数据.这就需要在PS的setup_consumers中,将name和statements相关的值设置为YES：

update performance_schema.setup_consumers

set ENABLED = ‘YES’

where NAME like ‘events_statements%’;

同时,还需要将setup_instruments表中,name和statements相关的值设置为YES：

update performance_schema.setup_instruments

set ENABLED = ‘YES’, TIMED = ‘YES’

where NAME like ‘statement%’;

3.PS数据接入myawr

打开之后,接下来就需要将SQL统计数据接入myawr中.由于myawr最初没有对PS的支持,所以我们需要从头开始改造myawr.

第一步是创建表,用于存储统计数据.设计表的时候,为了和myawr原有的表的表结构保持一致,前三个字段还是分别为id、snap_id和host_id,snap_id是快照ID,和一个具体的snapshot对应,这个在生成最后的myawr报告时,非常有用,host_id是MySQL实例的ID.剩下的字段,都是从performance_schema.events_statements_summary_by_digest这个表中,根据实际的需求摘取出来的.

我们先看看这个表的结构：

SCHEMA_NAMESQL执行时的default database
DIGEST一个hash值,代表结构相同的一类SQL
DIGEST_TEXT通过正则过滤后的SQL文本,没有具体参数值,代表结构相同的一类SQL
COUNT_STAR代表这一类SQL一共执行了多少次,这是一个累积值,只有实例重启才会重置
SUM_TIMER_WAIT ~ SUM_NO_GOOD_INDEX_USED这一系列的字段,都是从不同维度对SQL进行的统计,可以根据自己关注的侧重点,按需查询
FIRST_SEENSQL第一次执行的时间
LAST_SEENSQL最近一次执行的时间,这个字段在收集SQL统计数据和最终生成myawr报告时都会用到

为了设计上的简单,我把performance_schema.events_statements_summary_by_digest所有的字段都包含进去,命名为myawr_snapshot_events_statements_summary_by_digest,最终的myawr中的表结构如下：

myawr

创建好表之后,接下来就是将PS的数据录入这个表.录入时,需要做一些简单的筛选,因为可能有一些SQL很长时间都没有被执行过了,这样的SQL的统计数据就不用重复的接入到myawr的数据库中.这时候过滤就需要用上上面说的LAST_SEEN这个字段,我们目前的设计是只录入最近24小时被执行过的SQL的统计信息,具体数据查询的SQL为select * from performance_schema.events_statements_summary_by_digest where LAST_SEEN > date_sub(now(), interval 24 hour).确定数据采集方式之后,只需要在myawr的数据采集脚本中,将这部分功能逻辑添加进去即可.

一个需要注意的地方是数据量,因为SQL执行的全量统计信息是非常大的.一方面需要考虑表的设计,在建表时就将表按照时间分区,避免后面数据写入成为瓶颈.另外,可以在部署数据采集任务时,适当地调整采样周期,这直接决定了数据写入的频率.

4.数据展示

数据录入到myawr的数据库之后,接下来的工作就是分析和展示了.实际上就是从各个不同的维度去出分析报告,最终在报告里面展示的数据是一样的,只是不同的分析维度的排序规则不一样.下面,从总执行时间、总执行次数、总扫描记录数、总返回记录数、总排序记录数5个维度去分析如何生成myawr报告.分析维度的选取,是根据日常运维的需求而定的,大家可以根据自己的实际需求,从myawr_snapshot_events_statements_summary_by_digest中选取其他的一些维度.

总执行时间

总执行时间是在整个DB性能分析时非常有用的信息,可以据此分析出当前整个DB的资源消耗的分布情况.总执行时间对应myawr_snapshot_events_statements_summary_by_digest这个表的SUM_TIMER_WAIT字段,生成报告的查询SQL如下：

SQL

$tidhost_id,即对哪个MySQL实例生成myawr报告
$start_snap_id为myawr报告的起始快照ID
$start_snap_time为$start_snap_id对应的时间点
$end_snap_id为myawr报告的终止快照ID
$end_snap_time为$end_snap_id对应的时间点

最终在myawr报告中展示为：

myawr

总执行次数

总执行次数在分析某一固定时间段的故障时比较有参考价值.总执行次数对应myawr_snapshot_events_statements_summary_by_digest这个表的COUNT_STAR字段,生成报告的查询SQL如下：