如何通过参数调整确认慢 SQL

对于 OceanBase 数据库，您可以使用 OCP 监控或 gv$sql_audit 来定位慢 SQL，还可以使用系统参数来控制日志捕获执行时间超过多长的 SQL。本文主要介绍如何通过参数调整确认慢 SQL。

背景信息

trace_log_slow_query_watermark 参数用于设置查询的执行时间阈值，如果查询的执行时间超过该阈值，则被认为是慢查询，慢查询的追踪日志会被打印到系统日志中。

trace_log_slow_query_watermark 参数默认为 100 ms，表示执行时间超过 100 ms 的查询会被认为是慢 SQL，并记录在 observer.log 日志中。

有关该参数的详细信息，请参见《OceanBase 数据库 参考指南》中的 系统 配置项 章节。

操作步骤

1. 使用 ALTER SYSTEM SET 命令调整 trace_log_slow_query_watermark 参数的大小。

如下示例所示，将大于 1 秒的查询视为长查询。

<main id="notice" type='notice'>
  <h4>注意</h4>
  <p> <code>trace_log_slow_query_watermark</code> 参数设置得越小，产生的日志量会越大，对整个集群性能会有一定影响，需要根据情况来决定是否调整。</p>
</main>

```shell
obclient> ALTER SYSTEM SET trace_log_slow_query_watermark='1s';
```

2. 使用以下 SQL 确定租户 leader 所在的物理机。

   obclient> SELECT /*+READ_CONSISTENCY(WEAK),query_timeout(100000000)*/
     b.tenant_name,
     a.svr_ip
   FROM
     __all_virtual_meta_table a, __all_tenant b
   WHERE
     a.ROLE = 1
     AND instr(a.member_list, a.svr_ip)>0
     AND a.tenant_id = b.tenant_id
   group by b.tenant_name, a.svr_ip
   ORDER BY b.tenant_name, a.svr_ip;
   

3. 根据查询结果的 IP 地址，登录对应的 OBServer 物理机搜索 observer.log 日志。

   [root@hostname /]# cd /home/admin/oceanbase/log
   [root@hostname log]# fgrep "slow query" observer.log| sed "s/|\[/\n\]/g"
   

   日志样例如下图所示。

   

   从上图中，需要关注以下信息。

   • [optimize_end]：每个方框号（[]）中的文字表示 SQL 执行经过的每一个内部模块。

   • trace_id：语句的 trace_id，您可以通过该 ID 从日志中搜索语句的执行信息。与 gv$sql_audit 里的 trace_id 字段对应。

   • stmt：执行的 SQL 语句。

   • u：代表每一步消耗的时间，单位是微秒。

   • total_timeu：是指整个过程消耗的总时间，这里是 31ms。