MySQL慢日志体系建设

慢查询日志是MySQL提供的一种日志记录，用来记录在MySQL中响应时间超过阈值的SQL语句，在很大程度上会影响数据库整体的性能，是MySQL优化的一个重要方向。在58的云DB平台建设中，慢SQL系统作为一个非常重要功能模块，不仅是DBA日常运维使用，我们也希望通过该功能可以协助开发人员更快速定位业务问题。

目前我们的慢日志系统主要包括两个核心功能：慢日志报表和实时慢SQL。

• 慢日志报表主要基于上一天的所有慢SQL进行汇总分析，最后获得慢SQL数量的变化趋势、执行次数、执行平均时长等统计结果;

• 实时慢SQL就是实时获取线上生产数据库产生的慢SQL语句，并提供给开发人员查阅。

慢日志分析工具

MySQL的慢日志分析工具主流有官方自带的mysqldumpslow和第三方的pt-query-digest,由于后者有更多的额外属性和附加能力，我们的慢日志分析工具选择使用pt-query-digest。

pt-query-digest有两个比较好用的功能：query review和query history。在使用review功能时，会将慢日志中的查询语句去参数化后进行分组统计，并可将结果直接保存到数据库中。使用history时，则会将慢SQL的查询度量（如查询时间、锁时间等）保存到数据库中，这些数据将来可用于趋势分析和查询性能分析。

慢日志报表

我们定义的慢查询阈值（long_query_time） 标准为0.1s，不过不同的集群可以个性化定制。为了获取数据库每天的慢日志报表，后台程序会根据云DB平台的cmdb信息，对所有的数据库集群每天凌晨进行切割，生成一个上一整天的慢查询日志文件,大体格式如下：

整个58集团有几千套数据库集群，为了整体统计所有数据库集群的慢SQL报表，我们内部开发了一套完善的慢SQL分析流程，主要包含SQL流水计算、全局分析、诊断优化、建议推送、跟踪反馈等各个功能模块。

通过收集模块收集所有集群的慢SQL文件，计算模块消费分析每天产生的慢日志文件，并将计算结果保存到MySQL中。筛选后的数据，由云DB平台展示，并根据慢SQL的数量排行，将topN的邮件推送给开发人员。之后开发人员可使用云DB平台，获取慢SQL调优建议，并可借助工单系统，自助修改表索引，达到优化的目的。

我们的处理流程的大体如下：

慢SQL分析计算流程：

• 根据数据库集群cmdb信息，获取需要分析慢日志的数据库列表并进行切割；

• 通过ansible将相应的慢日志文件拉取到慢日志分析服务器；

• 通过pt-query-digest分析慢日志并将结果写入慢SQL详情库；

• 在数据库管理平台展示相应数据；

• 调用SQLAdvisor，获取优化建议；

核心模块为调用pt-query-digest的分析，此处采用信息入库的方式，结果会保存到两个结果表tb_slowlog_review和tb_slowlog_review_history中，分析的具体命令如下：

经过流水计算后，慢SQL分析的结果信息存储在MySQL中，之后由数据库云平台开发相应的功能对外展示。目前我们主要抽象了如下几个功能：

慢SQL数量统计及优化建议

对每个集群的慢SQL总数量进行趋势图展示，并包括该集群下所有慢SQL的执行次数、执行平均时间等维度的排序，以及对应的慢SQL优化建议，如下图：

image

单条慢SQL详情追踪

针对产生的每一条慢SQL给出数量趋势统计，并会统计该慢SQL的第一次出现时间、最新一次出现时间、来源IP、所属业务集群、负责人、执行次数等详细信息，最大程度的协助用户快速定位该慢SQL的来源。具体见下图：

image

除了上面两个主要的模块外，我们还支持每日慢SQL报表邮件推送，新增慢SQL汇总统计，通过多种方式协助高效进行慢SQL的优化，提高数据库的访问性能。

实时慢SQL系统

慢SQL报表系统，用于分析前一日产生的慢查询日志文件，但一个非常大缺陷是不能分析当日实时产生的慢SQL，特别对于实时的线上调优效果，非常不方便。所以为解决该问题，我们实现支持了实时慢SQL功能，开发人员可在平台中实时查看生产环境产生的每一条慢SQL语句。我们这里引入了ELK和Kafka相关技术，实时慢SQL系统结构如下：

实时慢SQL的收集工作流程如下：

• 通过filebeat，实时采集慢查询日志的文件变化；

• 采集到的慢日志数据实时上报到kafka中；

• Logstash消费kafka中数据，并进行过滤、切割，存入ES；

• 云DB平台读取ES数据，进行数据展示

这样在用户端，开发人员即可所负责的MySQL集群中查看实时产生的慢SQL了：

总结

MySQL慢查询，作为影响性能关键因素，应被DBA、开发人员重视，并及时处理。云DB平台从总体报表、实时流水、定量分析等多个维度，打造MySQL慢查询体系建设，为DBA以及开发人员提供有力支持。