如何检查大查询整体情况

OceanBase 数据库中耗时长的 SQL 称为大查询。本文介绍大查询的资源分配机制以及查询方法。

用户发给 observer 进程的 SQL 语句中，可以分为两类：一类 SQL 访问和操作的数据量小，所以执行很快；另一类 SQL 要访问大量的数据或者要写入大量数据，所有执行耗时长。耗时长的 SQL 称为大查询。

默认情况下，OceanBase 数据库的配置项 large_query_threshold 定义了以查询时长作为维度的大查询判别方式，默认为 100 ms，即查询时间超过 100 ms 的查询被认为是大查询。此外，large_query_worker_percentage 定义了大查询可以使用的工作线程数量，默认为 30%。有关以上配置项的详细信息，请参见《OceanBase 数据库参考指南》中的 系统配置项 章节。

适用版本

OceanBase 数据库所有版本。

大查询资源分配机制

当同时存在小查询和大查询时，OceanBase 数据库会把 30% 的 CPU 时间用于处理大查询。

对于 OceanBase 数据库 V2.X 版本，还不能精确控制大查询的 CPU 占用，observer 进程实际上是控制用来处理大查询的活跃线程数以限制 CPU 使用。大查询只能使用这个租户活跃线程的 30%。

observer 进程根据租户的 cpu_count * cpu_quota_concurrency 计算活跃线程数，这些线程在创建租户时就会被创建。例如，活跃线程 A 由于执行大查询被挂起，这时活跃线程就少了一个，作为补充，这个租户会额外再创建一个活跃线程。一个租户能创建的线程总数由 cpu_count * workers_per_cpu_quota 控制。当租户线程数到达上限后，新创建线程会失败，线程 A 不会被挂起，以保持活跃线程数始终不变。

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

通过 dump tenant 查询

在 OBServer 安装目录执行以下命令查询 observer.log 日志。

[admin@hostname ~]$ cd /home/admin/oceanbase/log
[admin@hostname log]$ grep 'dump tenant.*tenant={id:1002' observer.log| sed "s/,/\n/g"

示例如下。

[2020-12-16 15:29:24.640442] INFO  [SERVER.OMT] ob_multi_tenant.cpp:806 [31771][3104][Y0-0000000000000000] [lt=24] [dc=0] dump tenant info(tenant={id:1002
compat_mode:0
unit_min_cpu:"3.000000000000000000e+00"
unit_max_cpu:"3.000000000000000000e+00"
slice:"0.000000000000000000e+00"
slice_remain:"0.000000000000000000e+00"
token_cnt:12
ass_token_cnt:12
lq_tokens:3
used_lq_tokens:0
stopped:false
idle_us:1709164
recv_hp_rpc_cnt:2
recv_np_rpc_cnt:2
recv_lp_rpc_cnt:0
recv_mysql_cnt:314
recv_task_cnt:11
recv_large_req_cnt:0
tt_large_quries:12
actives:12
workers:12
nesting workers:7
lq waiting workers:0
req_queue:total_size=0 queue[0]=0 queue[1]=0 queue[2]=0 queue[3]=0 queue[4]=0 queue[5]=0
large queued:0
multi_level_queue:total_size=0 queue[0]=0 queue[1]=0 queue[2]=0 queue[3]=0 queue[4]=0 queue[5]=0 queue[6]=0 queue[7]=0
recv_level_rpc_cnt:cnt[0]=0 cnt[1]=0 cnt[2]=0 cnt[3]=0 cnt[4]=0 cnt[5]=0 cnt[6]=0 cnt[7]=0 })

关注以下两个字段的信息：

• tt_large_quries：表示 10s 内处理了多少个大查询 。

• lq waiting workers：表示有多少个线程由于大查询被挂起。

通过查询 SQL 执行时间检查

OceanBase 数据库的 MySQL 租户和 Oracle 租户都可以通过 gv$sql_audit 视图来查看SQL 执行时间的分布情况，从而判断大查询占用的资源

• Oracle 租户

  OBCLIENT> SELECT 
  ROUND(AVG(AVG_EXECUTE_TIME),0) VALUE_AVG,
  ROUND(MAX(AVG_EXECUTE_TIME),0) VALUE_MAX,
  ROUND(MIN(AVG_EXECUTE_TIME),0) VALUE_MIN,
  ROUND(CUME_DIST(100) WITHIN GROUP(ORDER BY AVG_EXECUTE_TIME DESC),0) VALUE_PCT_OCCURRENCE_ABOVE_100MS,
  ROUND(CUME_DIST(5000000) WITHIN GROUP(ORDER BY AVG_EXECUTE_TIME DESC),0) VALUE_PCT_OCCURRENCE_ABOVE_5S
  FROM 
  (
  SELECT SQL_ID,/*+READ_CONSISTENCY(WEAK), QUERY_TIMEOUT(100000000), PARALLEL(72)*/
  COUNT(1),
  ROUND(AVG(ELAPSED_TIME)) AVG_ELAPSED_TIME,
  ROUND(AVG(EXECUTE_TIME)) AVG_EXECUTE_TIME,
  ROUND(AVG(QUEUE_TIME)) AVG_QUEUE_TIME,
  ROUND(AVG(RETURN_ROWS)) AVG_RETURN_ROWS,
  ROUND(AVG(AFFECTED_ROWS)) AVG_AFFECTED_ROWS
  FROM GV$SQL_AUDIT
  WHERE TO_DATE('1970-01-01','YYYY-MM-DD') + (REQUEST_TIME / 1000000/86400)+TO_NUMBER(SUBSTR(TZ_OFFSET(SESSIONTIMEZONE),1,3))/24
  BETWEEN TO_DATE('2020-11-23 00:00:00','YYYY-MM-DD HH24:MI:SS') AND TO_DATE('2020-11-25 00:00:00','YYYY-MM-DD HH24:MI:SS')
  GROUP BY SQL_ID
  ORDER BY ROUND(AVG(EXECUTE_TIME)) DESC
  );
  

• MySQL 租户

  OBCLIENT> SELECT 
  ROUND(AVG(AVG_EXECUTE_TIME),0) VALUE_AVG,
  ROUND(MAX(AVG_EXECUTE_TIME),0) VALUE_MAX,
  ROUND(MIN(AVG_EXECUTE_TIME),0) VALUE_MIN,
  ROUND(CUME_DIST() OVER (ORDER BY AVG_EXECUTE_TIME DESC),0) VALUE_PCT_OCCURRENCE_ABOVE_100MS,
  ROUND(CUME_DIST() OVER (ORDER BY AVG_EXECUTE_TIME DESC),0) VALUE_PCT_OCCURRENCE_ABOVE_5S
  FROM 
  (
  SELECT SQL_ID,/*+READ_CONSISTENCY(WEAK), QUERY_TIMEOUT(100000000), PARALLEL(72)*/
  COUNT(1),
  ROUND(AVG(ELAPSED_TIME)) AVG_ELAPSED_TIME,
  ROUND(AVG(EXECUTE_TIME)) AVG_EXECUTE_TIME,
  ROUND(AVG(QUEUE_TIME)) AVG_QUEUE_TIME,
  ROUND(AVG(RETURN_ROWS)) AVG_RETURN_ROWS,
  ROUND(AVG(AFFECTED_ROWS)) AVG_AFFECTED_ROWS
  FROM GV$SQL_AUDIT
  GROUP BY SQL_ID
  ORDER BY ROUND(AVG(EXECUTE_TIME)) DESC
  );
  

示例如下，其中 value_pct_occurrence_above_100ms 表示超过 100 ms 的查询，value_pct_occurrence_above_5s 表示超过 5s 的查询。

+-----------+-----------+-----------+----------------------------------+-------------------------------+
| value_avg | value_max | value_min | value_pct_occurrence_above_100ms | value_pct_occurrence_above_5s |
+-----------+-----------+-----------+----------------------------------+-------------------------------+
|      6950 |    874275 |        17 |                                1 |                             1 |
+-----------+-----------+-----------+----------------------------------+-------------------------------+
1 row in set (0.63 sec)