分布式数据库学习Note190：OceanBase社区版中，如何解决SQL 查询导致的异常？

欢迎访问OceanBase官网获取更多信息：https://www.oceanbase.com/

在生产实践过程中，除去硬件和基础设施的故障，在剩下的多种导致数据库服务异常的因素中，SQL 查询导致的异常可能是最为常见的场景（可能没有之一）。作为使用者重点关注的焦点问题，本节重点讨论 SQL 查询导致的异常在 OceanBase 中如何进行分析和处理。

场景描述

在数据库正常运行过程中，经常会碰到异常的 SQL 执行导致的 CPU 使用率飙升、IO 过高，或内存激增等情况。而 SQL 异常通常分为以下几种情况：

1. 业务应用没有新版本发布，也就是没有新增 SQL，这种情况又分为两类：

   1. 外部业务流量变化，SQL 执行次数突增或数据量激增。

   2. 原 SQL 执行计划异常。

2. 业务应用有新版本发布，出现了新的 SQL查询逻辑，其中包含了慢 SQL。

业务影响

SQL 问题容易导致 CPU 冲高，内存耗尽等现象，从而产生业务卡顿甚至崩溃等影响。

应急处理流程

下面分别介绍每种情况的快速定位和处理方法。

业务没有发布新版本，没有新增 SQL

情形一：SQL 执行次数或数据量变化

在数据库正常服务的情况下，由于业务的营销活动或者用户访问增长等原因导致数据库中原有某类 SQL 仅执行次数或者数据量增长，执行计划正常的情况下，往往优先选择的方案是在业务层面降级或限流。若业务无法直接限流或降级时，如果此时可以增加实例的 CPU/内存配置，则优先执行数据库的临时扩容。详情请参见 数据库基础管理 章节的 扩容和缩容 。

如果此时集群中没有多余的资源可以扩容。此时可以考虑通过在 OB 内核层面执行指定 SQL 限流。可以通过绑定 SQL 的执行计划加入 hint max_concurrent 来限制 SQL 并发，实现对 SQL 的限流。示例如下：

CREATE OUTLINE outline_name ON sql_id USING HINT /*+max_concurrent(1)*/;

关于绑定 outline 的操作，详情请参见 《SQL 参考（MySQL 模式）》 中的 CREATE OUTLINE 和 《SQL 参考（Oracle 模式）》 中的 CREATE OUTLINE 。

情形二：现有 SQL 执行计划异常

若业务没有发部新版本，但是数据库中原有的 SQL 执行计划异常，一般分为如下几种情况：

1. 执行计划选择错误，此时可以先执行清空 plan cache 的方式，强制让 SQL 重新走硬解析，以生成正确的计划。

   ALTER system flush plan cache [tenant_list] [global];
   

   说明

   • 其中 tenant_list 和 global 为可选字段，如果 tenant_list 没有指定，则清空所有租户的执行计划缓存，否则只清空指定租户的；如果 global 没有指定，则清空本机的执行计划缓存，否则清空该租户所在的所有 server 上的计划缓存，默认是最小范围。

   • tenant_list 示例如下：tenant = 'tenant1, tenant2, tenant3....'

2. 索引选择错误。

   经过排查后发现表中有更好的索引，执行计划没有走到正确的索引。如果清空执行计划缓存后没有解决，这时可以通过 outline 绑定的方式，告诉优化器正确的执行计划。

   索引绑定目前已经在 OCP 中支持图形化操作，请查看 OCP 用户指南执行对应操作。

   对于命令行执行绑定操作，可参考如下示例：将 SELECT * FROM t1 WHERE v1 = 3 这条 SQL 绑定走主键索引。

   1. 以业务租户身份登录到 OceanBase（注意 mysql 命令中要加上 -c 参数)。

   2. 创建outline。

      create outline bind_to_primary_key on SELECT/*+ index(t1 primary)*/ * FROM t1 WHERE v1 = 3;
      

   3. 确定 Outline 创建成功。

      SELECT * FROM oceanbase.gv$outline WHERE OUTLINE_NAME = 'bind_to_primary_key';
      

   4. 确定新的 SQL 通过绑定的 Outline 生成新的执行计划。

      当绑定 Outline 的 SQL 执行新的查询后，查询 gv$plan_cache_plan_stat 表中该 SQL 对应的计划信息中的 outline_id。如果 outline_id 与在 gv$outline 中查到的 outline_id 相同，则表示是按绑定的 Outline 生成的执行计划，否则不是。

      SELECT SQL_ID, PLAN_ID, STATEMENT, OUTLINE_ID, OUTLINE_DATA 
            FROM oceanbase.GV$OB_PLAN_CACHE_PLAN_STAT 
             WHERE STATEMENT LIKE '%SELECT * FROM t1 WHERE v1 = 3%';
      

   5. 确定生成的执行计划符合预期。

      确定是通过绑定的 Outline 生成的计划后，需要确定生成的计划是否符合预期，可以通过查询 gv$plan_cache_plan_stat 表查看 plan_cache 中缓存的执行计划形状。

      SELECT OPERATOR, NAME FROM oceanbase.V$OB_PLAN_CACHE_PLAN_STAT
            WHERE TENANT_ID = 业务租户ID AND IP = 'xxx.xxx.xxx.xxx' 
             AND PORT = xxxx AND PLAN_ID = 上一步中查出的 plan_id;
      

3. 索引缺失。

   排查后发现当前 SQL 涉及到的表中缺乏最优索引，请根据需要创建对应索引。

   注意

   • 索引创建尽量包含全部所查询的列：包含的列越全越好，这样可以尽可能的减少回表的行数。

   • 等值条件永远放在复合索引包含列的最前面。

   • 过滤与排序数据量大的字段放在复合索引包含列的前面。

业务发布新版本，出现新的 SQL 逻辑

业务有新发布上线时，会有新 SQL 产生，在有些情况下可能存在一些未经 review 的慢 SQL 在生产上占用了过多资源，影响了核心业务。此时需要排查数据库中的慢 SQL 来定位问题所在，并进行后续优化处理。

1. 通过 OCP 查找慢 SQL。

   可以在 OCP 的 TOP SQL 页面查看最近一段时间耗时、执行次数最高的查询，从高到低进行排序，对于计划走错的 SQL 直接在线绑定，具体操作请参见 SQL 诊断；对于需要限流的 SQL，需要结合业务研发的确认信息，定位可以进行限流的SQL。

2. 在命令行查找慢 SQL。

   1. 查询特定租户下消耗 CPU 最多的 top sql。

      SELECT
       sql_id,
        avg(execute_time) avg_exec_time,
        count(*) cnt,
        avg(execute_time - TOTAL_WAIT_TIME_MICRO) cpu_time,
      RETRY_CNT,QUEUE_TIME,IS_HIT_PLAN
      FROM
       OCEANBASE.V$OB_SQL_AUDIT
      WHERE
        tenant_id = 1002 
      GROUP BY 
       1
      ORDER BY 
       (avg_exec_time * cnt) desc
      limit
       5;
      

      说明

      • 其中 EXECUTE_TIME 值，如果值过大，考虑存在等待事件或逻辑读次数异常多。

      • RETRY_CNT 字段即 retry 次数，如果次数很多，则可能有锁冲突或切主等情况。

      • QUEUE_TIME 过大，则表明 CPU 资源不够用。

      • GET_PLAN_TIME 为获取执行计划的时间，如果如果时间很长，一般会伴随 IS_HIT_PLAN＝0，表示没有命中 plan。

   2. 其他相关查询。

      OceanBase 提供两张虚拟表 V$OB_SQL_AUDIT，GV$OB_SQL_AUDIT 记录最近一段时间 SQL 执行历史，V$OB_SQL_AUDIT 存储本机的 SQL 执行历史，GV$OB_SQL_AUDIT 存储整个集群的 SQL 执行历史。可以根据需要进行相关查询。

      1. 查询 GV$OB_SQL_AUDIT 表，如查询某租户执行时间大于1s (1000000微秒)的 SQL。

         SELECT * FROM GV$OB_SQL_AUDIT WHERE tenant_id= <tenantid> AND elapsed_time> 1000000 limit 10;
         

      2. 查询 SQL 执行时间按秒分布的直方图。

         SELECT round(elapsed_time/1000000), count(*) FROM V$OB_SQL_AUDIT WHERE tenant_id = <tenant_id> GROUP BY  1;
         

3. 在 OceanBase 日志中查找慢 SQL。

   OceanBase 中 SQL 执行时间超过 trace_log_slow_query_watermark (系统参数)的，在 observer 日志中都会打印 slow query 消息，该参数默认值为 100ms。可以通过 observer 日志查找慢 SQL。这种方式没有上述两种方法高效直观，但在一些情况下也有助于一些问题的问题，例如当 sql_audit 中的缓存已经淘汰时。

   记录慢 SQL 日志的位置：/home/admin/oceanbase/log

   1. 看日志中所有的 slow query。

      fgrep '[slow query]' observer.log |sed -e 's/|/\n/g' | more

   2. 根据 trace_id 查询某个 slow query。

      fgrep "<trace_id>" observer.log |sed -e 's/|/\n/g' | more

SQL 排序工作区内存不足

OceanBase 中对于需要排序的中间结果集数据，是存放在一块称为工作区的内存区域的，当遇到一些大量排序的 SQL 请求时，可能遇到工作区内存不足导致的报错。通常在 OB 日志中出现类似于"Failed to allocate memory"的错误信息。此时一般可以通过增大目标业务租户的 ob_sql_work_area_percentage 变量（默认值为 5，即租户单个资源单元内存规格的 5%）解决。此变量可配置为 Session 级别或 Global 级别，建议值域为 [5,20]。

示例语句如下所示：

• Session 级别

  obclient> SET ob_sql_work_area_percentage = xxx;
  

• Global 级别

  obclient> SET GLOBAL ob_sql_work_area_percentage = xxx;

欢迎访问OceanBase官网获取更多信息：https://www.oceanbase.com/