字节客增慢SQL治理体系

小鋺豆

动手点关注干货不迷路项目概览背景慢 SQL 即执行时间超过 long_query_time 设定阈值的SQL语句，可通过 select @@long_query_time 查看数据库具体的慢查询阈值。另外慢 SQL 不仅仅包括 select 语句，也包括 delete,insert 等 DML 语句。慢查询 SQL 的危害包括:性能: 慢 SQL 的执行时间过长，则会导致用户的等待时间过长，直接影响用户体验；稳定性: 当 db 出现慢查询，一旦有其他的 DDL 操作，可能会造成整个数据库的等待;另一方面，慢 SQL 会拖垮数据库，导致正常执行的 SQL 也会变成慢 SQL。在字节的线上事故管理平台搜索慢 SQL 关键字可以看到很多由于慢 SQL 导致的事故，危害性较大。成果发布慢 SQL 月报，整理最佳实践，头部泳道推动改进等取得了慢 SQL 数下降了近 50%，慢 SQL周运行次数下降了一个数量级的成效；慢 SQL 配置&告警订阅持续配置率从 18% 提升到 70% 左右，持续优化中。名词解释RDS：Relational Database Service,即字节关系型数据库服务。提供的关系数据库服务，使用的数据库产品主要以开源 MySQL 数据库为主。字节云关系型数据库服务（RDS）专注于为业务提供稳定可靠，弹性伸缩的在线数据库服务。Mars：客增性能平台名称。风神 Aeolus：字节自研敏捷 BI 平台，提供灵活易用的数据查询，高效美观的报表制作，与丰富多元的数据内容。设计方案1. 架构图2. 核心功能2.1 全面的慢 SQL 度量看板以字节 RDS 平台数据库的慢 SQL 数据为依据，量化管理客增每日/每周/每月的慢 SQL 数量&运行次数。按照度量看板数据推动大家及时改进存量的慢 SQL，降低数据库质量风险。例如周维度的运行次数 & 慢 SQL 条数趋势图如下所示：2.2 慢 SQL 治理体系2.2.1 rds 慢 SQL 阈值配置自动化管理字节关系型数据库平台-RDS 提供慢 SQL 阈值配置的功能：当 SQL 执行时间超过该阈值后，会被自动 kill 终止运行，相当于慢 SQL 的容灾配置（如果一条 SQL执行了 3 个月还在运行，结果不敢想象）慢 SQL 阈值配置自动化管理是解决业务关联的数据库全部配置了慢 SQL 阈值信息。该部分通过线上定时巡检来实现，流程如下：2.2.2 Mars-慢 SQL 治理平台在客增质量工作台搭建 Mars-客增慢 SQL 治理 Web 页面，展示相关业务的慢 SQL现状以及排期跟进修复情况，目的是让业务同学更清晰快速了解当前业务相关，提供问题修复效率，方案如下：慢 SQL 跟进页面:2.2.3 慢 SQL 风险评估模型-慢 SQL 分当业务线存在较多慢 SQL 时，如何精准且合理的分析出哪些慢 SQL 风险最高？我们基于关系型数据库的 Quert_time,Lock_time,Rows_sent,Rows_affected,Bytes_sent 等维度建立客增的慢 SQL 风险评估模型，给每条慢 SQL& 每个数据库打分，按照慢 SQL 分来排序，分数最高的慢 SQL风险最高。慢 SQL 模型如下:2.3 慢 SQL-CI 流水线准入/准出卡口建设基于 ByteCycle（ByteCycle 字节统一能效中台）开发慢 SQL 原子节点，提供慢 SQL 相关的卡点能力。bytecycle 基于 psm 维度来构建持续集成流水线，通过提供慢 SQL 原子节点，可以方便用户插拔式使用。CI 卡点能够提供大家对慢 SQL 的重视程度以及提高慢 SQL 的改进效率。2.4 慢 SQL 监控&告警订阅目前提供慢 SQL 月报，每日慢 SQL相关问题修复提醒，sqll kill lark 告警卡片等维度的信息展示和触发。相关样式如下:慢 SQL 月报每日慢 SQL 问题修复提醒配置 db 慢查询阈值后，如果超过该阈值则该语句会被 db 自动 kill，订阅后会自动将获取到的 kill 信息发送到对应群中3. Code 方案RDS 元信息获取实现方案数据表设计createtablecg_rds_external(idintunsignedauto_incrementprimarykeycomment'id',db_namevarchar(100)default''nullcomment'db名字',ownersvarchar(100)default''notnullcomment'dbowners',regionvarchar(100)default''notnullcomment'db部署的region',proxy_port_mastervarchar(100)default''notnullcomment'master节点的port',proxy_port_slavevarchar(100)default''notnullcomment'slave节点的port',sync_timedatetimedefaultCURRENT_TIMESTAMPnotnullonupdateCURRENT_TIMESTAMPcomment'数据同步时间')ENGINE=InnoDBDEFAULTCHARSET=utf8mb4comment'rdsdb额外信息';createtablecg_rds_slow_query_config(idintunsignedauto_incrementprimarykeycomment'id',config_idintnullcomment'慢查询配置id',db_namevarchar(255)default''nullcomment'db名字',regionvarchar(100)default''notnullcomment'db部署的region',portvarchar(100)default''notnullcomment'规则中的端口',db_rolevarchar(100)default''notnullcomment'masterorslave',max_query_timeintnullcomment'超时阈值，单位是秒',creatorvarchar(100)default''nullcomment'规则创建人',create_timevarchar(100)default''nullcomment'规则创建时间',sync_timedatetimedefaultCURRENT_TIMESTAMPnotnullonupdateCURRENT_TIMESTAMPcomment'数据同步时间')ENGINE=InnoDBDEFAULTCHARSET=utf8mb4comment'rds慢查询规则配置信息';createtablecg_rds_db_alarm_config(idintunsignedauto_incrementprimarykeycomment'id',regionvarchar(100)default''notnullcomment'db部署的region',alarm_idintnullcomment'alarm规则id',db_namevarchar(255)default''nullcomment'db名字',typevarchar(100)default''notnullcomment'alarmtype,例如lark',group_idvarchar(100)default''notnullcomment'larkid',create_timevarchar(100)default''notnullcomment'规则创建/更新时间',ownervarchar(100)default''notnullcomment'alarm创建人',sync_timedatetimedefaultCURRENT_TIMESTAMPnotnullonupdateCURRENT_TIMESTAMPcomment'数据同步时间')ENGINE=InnoDBDEFAULTCHARSET=utf8mb4comment'rdsalarm配置表';慢 SQL 查询详情落库数据表createtablecg_slow_query_detail_info(idintunsignedauto_incrementprimarykeycomment'id',db_namevarchar(255)default''nullcomment'db名',db_regionvarchar(255)default''nullcomment'db的region',fingerprint_md5varchar(255)default''nullcomment'慢sql标识',begin_timedatetimeDEFAULTCURRENT_TIMESTAMPnullcomment'慢sql的开始执行时间',max_run_timevarchar(255)default''nullcomment'sql执行的最大耗时',run_countintdefault0nullcomment'sql执行次数',psm_namevarchar(255)default''nullcomment'发起sql的psm',avg_query_timevarchar(255)default''nullcomment'平均耗时',rds_addressvarchar(255)default''nullcomment'执行sql的rds主机ip:port',psm_hostvarchar(255)default''nullcomment'发起查询请求的主机ip',sync_timedatetimeNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMPCOMMENT'数据同步时间')ENGINE=InnoDBDEFAULTCHARSET=utf8mb4comment'客增慢sql记录';慢 SQL 被 kill 的详情信息获取方案数据表createtablecg_kill_sql_detail_info(idintunsignedauto_incrementprimarykeycomment'id',db_namevarchar(255)default''nullcomment'db名',db_regionvarchar(255)default''nullcomment'db的region',db_rolevarchar(255)default''nullcomment'db节点:masterslave',begin_timedatetimeDEFAULTCURRENT_TIMESTAMPnullcomment'被kill的sql执行开始时间',psm_namevarchar(255)default''nullcomment'发起sql的psm',sql_detailvarchar(2000)default''nullcomment'sql详情',db_table_namevarchar(255)default''nullcomment'该sql的表名,如果多个表，只取第一个',sync_timedatetimeNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMPCOMMENT'数据同步时间')ENGINE=InnoDBDEFAULTCHARSET=utf8mb4comment'rds被kill的慢sql数据统计';Metrics 监控规则rds 报警订阅的监控只能发现 rds 上执行的 SQL 数据，不能实时发现慢接口。故推荐使用 dbatman 的 metrics 打点来完成慢 SQL 的监控告警工作。$key="max:toutiao.ttds.dbatman.latency.max{db=sales_manage,port=*,host=*,dc=*}"$value=max(q($key,"3m","1m"))/1000warn=$value>50runEvery=14. 慢 SQL 治理最佳实践与标准制定慢 SQL 治理优化基本可分为如下 3 类:优化 shcema优化索引，尽可能构建三星索引优化查询，合理的设计查询相关细则如下所示:参考https://dbaplus.cn/news-11-4069-1.html小结虽然慢 SQL 专项取得了一定的进展，但后续的持续优化仍然有很长的路要走，如果你也对慢 SQL 优化、数据库质量感兴趣，欢迎加入我们！除了慢 SQL 专项，SRE、EP 等方向的建设同样欢迎大家加入。可以邮件联系 fangchang.fc@bytedance.om 咨询相关信息或者直接发送简历内推。欢迎来聊，hc 多多，等待你的加入！