OceanBase-CE v3.1.3 诊断调优实践

原创杰克跑跑 2022-04-26

1753

本文是一篇实践文章，将会介绍如何使用 OceanBase 提供的视图、outline 功能和生态工具发现问题 SQL，并进行调优。阅读本文，可以了解如何在 OceanBase 中查找烂 SQL，如何查询 SQL 走的执行计划，如何指定 SQL 走某个索引以及如何给 SQL 进行限流。文章涉及到的知识点和软件均可以在 OceanBase 官网找到。

1 环境准备

本次测试涉及到的软件版本分别为：

OceanBase-CE v3.1.3
OCP-CE v3.1.1
ODC-CE v3.2.2
OB-SQL-Diagnoser v1.0.0

创建测试表

在数据库 test下创建测试表 test, 其表结构为：

插入数据

使用 ODC 的模拟数据工具可以很方面地制造测试数据，如图所示，为该表模拟100万行数据，其中id为 0-100万间的随机数据，age为 0-120 间的随机数据，name 为随机文本。

经过 ODC 模拟数据后，测试表内有100万行数据，各列数据特征如下：

2 执行 SQL

在 test 数据库下执行下面的SQL：

3 发现烂 SQL

前面执行了烂 SQL，那么如何找到系统中执行过的烂 SQL 呢？

黑屏找到烂 SQL

我们可以从视图 v$sql_audit 或者 gv$sql_audit 视图里找到SQL执行信息。v/gv$sql_audit 记录 SQL 每一次的执行审计信息，v$sql_audit只是gv$sql_audit过滤当前主机数据后的视图。执行下面的 SQL 查询 gv$sql_audit 捞出全表扫描且响应时间超过100ms 的SQL，可以发现上面执行的SQL。（注意：为避免查询出过多结果， ODC 会自动在 SQL 后加上limit 1000，所以查询出的SQL与原始 SQL 不太一样时不要惊讶。）

找到 SQL 的执行计划

有时候我们想分析 SQL 执行时走的执行计划，可以使用 gv$sql_audit 里查询到的 plan_id, tenant_id, svr_Ip, svr_port 在 gv$plan_cache_plan_explain里找到执行计划详细信息，从 gv$plan_cache_plan_explain 里查询时 plan_id, tenant_id, svr_Ip, svr_port 都需要带上。

白屏发现烂 SQL

从 Oceanbase-CE v3.1.3 开始, 提供了敏捷 SQL 诊断工具，能够快速发现集群中存在常见问题的 SQL，如图所示，使用该诊断工具，扫描执行 SQL 的时间区间，可以发现出该烂 SQL。除了内置的诊断项外，该工具还可以自定义诊断项。

4 调优

当我们发现系统中存在烂 SQL 时，需要进行调优，解决性能问题。Oceanbase outline 功能为我们提供了调优接口，通过 outline 功能，无需修改业务 SQL，就能干预执行计划，达到调优目的。针对上述 SQL，我们可以知道，该 SQL 全表扫描了，而且表非常大。需要建立合适的索引。由于 id 列的 NDV 比较大，可以在该列上建个索引：alter table test add index idx_id(`id`);

绑定索引

建完索引后，一般来说OB执行上述SQL时可以自动走上该索引，保险起见，我们可以使用outline功能固定计划，保证 SQL 走上索引。主要步骤：

从 gv$sql_audit 里找出该 SQL 的sql_id 为 '5A85834ABC0D646029D7A877CDB9FAFC'
使用语法 create outline $outlineName on $sqlId using hint $hintText 创建 outline

执行成功后，可以查看 outline 信息，记住该outline_id，在下面的验证环节会用到。

验证

创建完后，我们可以执行一下该 SQL，验证下是否成功。执行 SQL 后，找到 SQL 执行时使用的执行计划信息，可以看到该执行计划使用了 outline_id 为 1103909674288114 的 outline，该 outline_id与前面创建的outline的outline_id一样。

Explain 验证

另外，可以 explain SQL，查看执行计划是否走了索引。

5 应急

SQL 限流

如果发现某烂SQL在业务上并非重要的 SQL，想临时限制一下该 SQL 的执行频率，降低其对数据库系统的资源消耗，可以使用 outline 功能对该 SQL 进行限流，限流值是执行该 SQL 时单机的并发度。如图所示，使用 max_concurrent hint进行绑定，可以对 SQL 进行限流。创建完限流 outline 后，重复执行该 SQL，会发现 SQL 执行出错，且提示：reach max concurrent num 的信息。