功能更新｜DAS推出全局Workload优化功能，实现SQL自动诊断

背景

日常的数据库优化中 在数据库的表上创建合适的索引是解决慢SQL查询问题的一种非常重要且常用的方案。在处理过程中 DBA或者开发人员通常会根据实例上的慢SQL信息进行优化 DAS自动SQL优化功能已经实现了根据慢SQL进行自动诊断 并创建合适的索引。但该方案会面临如下几个挑战

数据采集问题 一些业务SQL并没有达到慢SQL采集的阈值 比如1s 而这些SQL查询本身没有很好的利用索引 查询效率不高 仍然有很大的优化空间。在并发量增大或者表数据增多的情况下 这些查询很容易造成实例性能突然恶化而引起故障。写入代价问题 在创建索引时通常更注重提高数据读取的效率 而忽略索引维护对写入性能的影响和空间占用的成本 对于写多读少的表 创建太多索引反而会影响系统吞吐。workload变化问题 索引一旦创建 通常情况下很少变化 而业务却一直在动态变化中。随着业务不断迭代变化 一些索引可能不再有SQL使用 或者使用频率很低 此时需要引入更优的索引设计来提升数据库的处理性能。

为了解决上述问题 DAS推出了全局Workload优化功能 它可以及时检测到数据库的负载变化 识别到新增SQL、执行变化的SQL以及性能不佳的SQL 并综合考虑SQL的执行频率和相关SQL信息 给出优化建议。

解决方案介绍

? ? ?全局Workload优化 主要由三部分组成。

? ? ?Workload检测 根据数据库实例上和Workload相关的性能指标(如RT CPU等)以及全量SQL相关指标(执行次数、执行耗时、扫描行数等) 训练数据模型 实时检测Workload的SQL执行情况 从而识别新增SQL、执行变化的SQL 以及整个负载变化的周期。

? ? ? 如下图所示 全量SQL执行状况指标在period1和period2呈周期性状态 至period3 执行状况发生变化。全局Workload优化 根据数据训练模型 轻松实现识别负载变化的时间区间。

? ? 全局诊断 全局诊断优化则根据数据库在某一时间范围内的全部SQL执行情况 综合考虑SQL的查询和写入性能以及空间占用情况 推荐最优索引组合 从而从SQL角度最大限度提高数据库的性能 降低数据库导致的问题的概率。

智能压测 智能压测可以回放实例上某个时间段内的全部SQL 该功能会在相关文章中详细解读 将全局诊断和智能压测结合后 系统可以在测试实例上根据诊断建议自动创建索引 回放历史流量并对比采纳建议前后的SQL执行情况 生成测试报告。

具体实现触发时机

全局workload诊断支持用户自定义触发和系统自动检测触发两种模式 用户触发可以根据业务需求制定时间区间 触发全局诊断获取优化建议 自动检测会实时监测实例的负载信息 检测到数据库有异常SQL出现 或者发现Workload整体趋势变化 及时触发全局workload诊断。其中异常SQL包括 (1) 新增SQL (2) 执行次数占比浮动20%以上SQL (3) 执行平均RT浮动20%以上SQL等。

通过自动检测机制 可以帮助用户及时发现结构设计落后于业务变化的场景 减少故障发生的概率以及资源浪费。

数据来源

全局workload诊断的数据来源是SQL审计 包括SQL类型、SQL模版、执行次数以及SQL性能信息等。SQL审计会记录诊断时间内执行的所有SQL 因此可以发现不是慢SQL但性能欠佳的SQL问题。

关联SQL分析

通过解析SQL模版和元数据 可以分析出SQL、表、列之间的访问关系 从而得到可能相互影响的SQL集合。通过关联性分析 可以有效地减少后续求解问题的复杂度 同时为索引上线后的性能跟踪服务提供基础的数据支持。

候选索引生成及代价评估

该模块和后面的优化求解是全局workload优化的核心模块。在单SQL的索引推荐中可以根据一些规则或者经验来推荐索引 也能取得一定的效果 但基于全局workload的优化基于规则的方法就几乎无效了 必须能够将代价进行量化。我们基于DAS实现的外置优化器 可以做到快速准确的解析语法树、采样收集统计信息、生成候选索引以及计算使用某个索引的代价。

优化求解

在确定候选索引集以及索引代价的情况下 选择最有索引集合的过程可以等价为一个背包问题的变种。选择某个索引的收益等价为放入背包物品的价值 由于创建一个索引既可以给查询带来正收益也会对写入和空间成本带来副收益 因此价值可以是正数也可以是负数。背包的容量是一个表上最多建立索引的阈值 用户设置或系统默认 并非数据库存储约束 。我们的目标是使得背包中物品价值最大。另外需要注意的是 当选择一个索引后 它会对其他索引的价值产生影响 因此在每次迭代选择物品时需要根据已经存在索引的情况 更新剩余待选索引的价值。

索引I代价 执行次数 * a*读收益 - b*写代价 - c*空间占用

效果验证

为了保证优化建议的有效性 我们和智能压测功能整合到一起 提供快速方便的验证方案。智能压测系统会自动搭建测试实例上并同步真实数据 然后在测试上自动采纳优化建议 回放诊断时间段内的全量SQL并采集SQL执行的性能数据 最后对比生成测试报告。这种方案的好处是既保证了测试场景和线上业务的一致性 又不会对线上运行业务造成影响 同时还能预估采纳建议后产生的影响。

示例

比如表1中存在6条SQL 如果独立的看每一条SQL 得到的优化索引可能为表2中的4条索引 而从workload维度来看 索引可以合并为表3的两条索引。两种结果对比 整体RT下降14.45% 索引空间节省50%。

SQL2 ?idx_is_deleted_gmt_modified (is_deleted, gmt_modified)

SQL4 : ?idx_name(name) ?

SQL5: ? idx_name_id_birth_date (name, id, birth_date) ??

SQL6: ? idx_name_nick_name name, nick_name

idx_is_deleted_gmt_modified (is_deleted, gmt_modified)

idx_name_id_birth_date (name, id, birth_date)?

未来计划

? ? 全局Workload优化未来会打造自动优化的闭环 包括workload异常检测、全局workload诊断、智能压测效果评估 自动采纳建议、效果跟踪及异常处理。另外 目前全局workload优化考虑了SQL执行频率 SQL查询和写入的影响 但没有考虑固定参数或者参数倾斜等问题 后面可以进一步将这些业务属性纳入到考虑因素当中。

本文转自网络，原文链接：https://developer.aliyun.com/article/785130
本站部分内容转载于网络，版权归原作者所有，转载之目的在于传播更多优秀技术内容，如有侵权请联系QQ/微信：153890879删除，谢谢！