oceanbase mysql租户表数量超过20000张时查询information_schema.columns视图很慢，执行计划走了全表扫描

OceanBase 技术问题
#1

【 使用环境 】测试环境
【 OB or 其他组件 】
【 使用版本 】4.3.5.1

【问题描述】oceanbase mysql租户表数量超过20000张，查询information_schema.columns视图很慢，执行计划全表扫描。同样的sql查询底层系统表ceanbase.__ALL_VIRTUAL_INFORMATION_COLUMNS 就很快

【复现路径】

  1. 查询information_schema.columns视图很慢，执行计划扫走了全表扫描

    image
    image1185×411 46.9 KB

  2. 查询底层系统表ceanbase.__ALL_VIRTUAL_INFORMATION_COLUMN很快，执行计划走了TABLE GET算子

    image
    image1284×429 56.5 KB

#3

image
image1627×409 21.5 KB
，我们用的4.3.5.1版本查元数据上行为和4.2版本应该是有些不一致

select 1 from Information_schema.columns limit 0,1 语句执行45秒
#4
  1. SET ob_enable_trace_log = 1; --新版本 set ob_enable_show_trace=1;
  2. 执行SQL
  3. SHOW TRACE; --出现各个阶段信息

看看这两条sql 的trace，到底卡在哪里了

#5
  1. obdiag SQL性能问题信息收集
    –根据时间和执行语句查询trace_id
    select query_sql,svr_ip,TRACE_ID,client_ip,TENANT_NAME,user_name,DB_NAME,ELAPSED_TIME,RET_CODE,FROM_UNIXTIME(ROUND(REQUEST_TIME/1000/1000),’%Y-%m-%d %H:%i:%S’) from GV$OB_SQL_AUDIT
    WHERE REQUEST_TIME>=‘2024-04-05 14:34:00’ and lower(query_sql) like ‘%select%’;
    obdiag gather scene run --scene=observer.perf_sql --env “{db_connect=’-h127.0.0.1 -P2881 -utest@test -p****** -Dtest’, trace_id=‘Yxx’}”
  2. 全链路诊断中的Show Trace功能，也能轻松发现性能瓶颈并进行进一步的分析和调优。
  3. 以上两种方式可能并不是万能的，接下来可以按以下步骤尝试自查分析。
    a. 通过查看gv$ob_sql_audit审计视图确认影响执行耗时的等待事件。
    b. 再获取 SQL 执行计划EXPLAIN EXTENDED，查看执行计划并分析。
    c. 获取 sql plan monitor 信息，全过程可以参看如下操作。

1.登录sys租户设置 sql_plan_monitor 参数

确认 sql_plan_monitor 已经打开

show parameters like ‘enable_sql_audit’;

如果 enable_sql_audit = False 则将其打开

alter system enable_sql_audit = true;

  1. 登录业务租户，获取 sql 的执行计划
    EXPLAIN EXTENDED sql语句;

  2. 设置临时 trace 获取
    SET ob_enable_show_trace=‘ON’;

  3. 再次执行需要采集的 sql 语句

  4. 获取上一步执行的 sql 的 trace_id 信息
    select last_trace_id();

  5. 临时关闭 plan monitor 数据，防止信息被覆盖
    alter system enable_sql_audit = false;

  6. 获取 plan monitor 的 sql ，将 xxxxx 替换为第 6 步返回的 trace_id ，以获取每个算子的吐行信息
    select plan_line_id, concat(lpad(’ ', plan_depth, ’ '), plan_operation) op, sum(output_rows) rowss, sum(STARTS) rescan, min(first_refresh_time) open_time, max(last_refresh_time) close_time, min(first_change_time) first_row_time, max(last_change_time) last_row_eof_time, count(1) threads from gv$sql_plan_monitor where trace_id = ‘YC3500BA2DAC4-0006198CC947196A-0-0’ group by plan_line_id, plan_operation, plan_depth order by 1;
    8 恢复 sql_audit 参数
    alter system enable_sql_audit = true;

1 个赞
#6

看起是没走到联合索引table_schema,table_name

obclient(root@xxx)[(none)]> SELECT column_name, column_comment FROM information_schema.columns  WHERE table_schema = 'xxxx' AND table_name = 'xxx';
Empty set, 2 warnings (1.457 sec)

obclient(root@xxx)[(none)]> SHOW TRACE;
+-------------------------------------------+----------------------------+-------------+
| Operation                                 | StartTime                  | ElapseTime  |
+-------------------------------------------+----------------------------+-------------+
| com_query_process                         | 2025-04-22 17:00:48.015926 | 1455.755 ms |
| └── mpquery_single_stmt                   | 2025-04-22 17:00:48.015928 | 1455.744 ms |
|     ├── sql_compile                       | 2025-04-22 17:00:48.015932 | 0.047 ms    |
|     │   └── pc_get_plan                   | 2025-04-22 17:00:48.015934 | 0.022 ms    |
|     └── sql_execute                       | 2025-04-22 17:00:48.015987 | 1455.670 ms |
|         ├── open                          | 2025-04-22 17:00:48.015988 | 0.006 ms    |
|         ├── response_result               | 2025-04-22 17:00:48.015998 | 1455.013 ms |
|         │   └── do_local_das_task         | 2025-04-22 17:00:48.016003 | 0.030 ms    |
|         └── close                         | 2025-04-22 17:00:49.471027 | 0.616 ms    |
|             ├── close_das_task            | 2025-04-22 17:00:49.471029 | 0.597 ms    |
|             └── end_transaction           | 2025-04-22 17:00:49.471635 | 0.001 ms    |
+-------------------------------------------+----------------------------+-------------+
1 个赞
#7

看耗时主要在 sql_execute 的子 Span response_result，代表了 SQL 执行物理执行计划阶段耗时高。

你可以按照我楼上发的收集一下 信息 单独发个帖子 好跟踪 你是业务租户下查询么？

1 个赞
#8

试试

#9

该问题和研发那边确认了，符合当前的预期。是已知问题，后期会优化。如果要在ob4351版本上走到pk，可以在创建租户的时候将 lower_case_table_names 变量设置为1，但是这个变量在租户创建好后就不能修改了。
https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000002016015

1 个赞