多表 简单union（50+）时，oom或者执行很慢

【 使用环境 】生产环境 or 测试环境 测试
【 OB or 其他组件 】 ob
【 使用版本 】4.3.5.1
【问题描述】typeorm库连接ob，会基于information_schema生成很多union all的sql，直接执行会oom，code: ‘ER_CANT_USE_ANONYMOUS_TO_GTID_WITH_GTID_MODE_NOT_ON’,
errno: 4013,
sqlState: ‘HY001’,
sqlMessage: ‘No memory or reach tenant memory limit’, 租户内存8g，ob_sql_work_area_percentage = 75

image1457×1179 87.6 KB

image1943×381 28.3 KB

【复现路径】问题出现前后相关操作
【附件及日志】推荐使用OceanBase敏捷诊断工具obdiag收集诊断信息,详情参见链接（右键跳转查看）：

【SOP系列 22 】——故障诊断第一步(自助诊断和诊断信息收集)

【备注】基于 LLM 和开源文档 RAG 的论坛小助手已开放测试，在发帖时输入 [@论坛小助手] 即可召唤小助手，欢迎试用！

根因分析场景: SQL性能问题
根因分析命令: obdiag rca run --scene=sql_performance_issue
日志采集场景: SQL性能问题
日志采集命令: obdiag gather scene run --scene=observer.perf_sql --env “{db_connect=’-h127.0.0.1 -P2881 -utest@test -p****** -Dtest’, trace_id=‘Yxx’}”

推荐使用 obdiag 工具进行根因分析和日志采集，以便确认问题所在。上述命令将帮助您收集与 SQL 性能相关的诊断信息，并对执行 UNION ALL 查询时出现的 OOM 或性能瓶颈进行分析。

麻烦您提供一些信息以便我们更好地帮助您：（下面提出几个问题）

1. 执行该查询时是否启用了 trace_id？如果有，请提供具体的 trace_id。
2. 查询涉及的 50+ 表是否有共同的特点（如大小、分区情况）？
3. 是否可以提供执行计划 (EXPLAIN PLAN) 的输出结果？
4. 调整 range_optimizer_max_mem_size 参数后，查询的执行时间是多少？是否仍然存在明显的性能瓶颈？
5. 数据库当前的内存配置（如 total_memory_percentage 和 memstore_limit_percentage）是多少？
6. 是否可以提供查询语句的具体内容以便进一步分析？如果涉及敏感信息，可以对表名或字段名进行脱敏处理。

附上敏捷诊断工具 obdiag 使用帮助链接

发一个完整的可以运行的 SQL 看看？

SELECT
kcu.TABLE_SCHEMA,
kcu.TABLE_NAME,
kcu.CONSTRAINT_NAME,
kcu.COLUMN_NAME,
kcu.REFERENCED_TABLE_SCHEMA,
kcu.REFERENCED_TABLE_NAME,
kcu.REFERENCED_COLUMN_NAME
FROM
(
SELECT
*
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
WHERE
kcu.TABLE_SCHEMA = ‘luru_pre’
AND kcu.TABLE_NAME = ‘xxx’ UNION
SELECT
*
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
WHERE
kcu.TABLE_SCHEMA = ‘luru_pre’
AND kcu.TABLE_NAME = ‘xxx2’ – 随便找几十张表union
) kcu

image1926×846 96.1 KB

image1936×695 75.2 KB

根据您提供的信息，问题的核心在于执行包含大量 UNION ALL 的查询时，OceanBase 数据库的性能显著低于 MySQL。以下是分析和可能的解决方案：

问题分析

1. SQL 结构复杂性：

   • 您的 SQL 查询通过多次 UNION ALL 将多个子查询结果合并。这种结构在 OceanBase 中可能会导致优化器生成较复杂的执行计划，尤其是在涉及 INFORMATION_SCHEMA 表时。
   • INFORMATION_SCHEMA 是一个虚拟表，其数据来源于系统元数据。访问这些表时，OceanBase 需要动态生成和解析元数据，这可能导致额外的开销。

2. 内存限制与参数调整：

   • 调整 range_optimizer_max_mem_size 参数后，虽然解决了 OOM 问题，但性能仍然较差。这表明当前的执行计划可能不够高效。
   • OceanBase 的默认内存分配策略可能不适合此类复杂查询，尤其是当租户内存有限时。

3. MySQL 与 OceanBase 的差异：

   • MySQL 在处理类似查询时可能采用了更高效的执行计划或缓存机制，而 OceanBase 的实现方式可能存在差异。

解决方案建议

1. 优化 SQL 结构

• 减少 UNION ALL 的使用：将多个子查询合并为一个查询，通过 WHERE 条件筛选不同的表名。例如：

  SELECT
    `kcu`.`TABLE_SCHEMA`,
    `kcu`.`TABLE_NAME`,
    `kcu`.`CONSTRAINT_NAME`,
    `kcu`.`COLUMN_NAME`,
    `kcu`.`REFERENCED_TABLE_SCHEMA`,
    `kcu`.`REFERENCED_TABLE_NAME`,
    `kcu`.`REFERENCED_COLUMN_NAME`
  FROM
    `INFORMATION_SCHEMA`.`KEY_COLUMN_USAGE` `kcu`
  WHERE
    `kcu`.`TABLE_SCHEMA` = 'luru_pre'
    AND `kcu`.`TABLE_NAME` IN ('xxx', 'xxx2', ...); -- 替换为实际表名
  

  这种方式可以减少查询的复杂度，并让优化器更容易生成高效的执行计划。

2. 调整系统参数

• 增加内存分配：
  • 确保租户的内存配置足够支持复杂查询。可以通过以下命令查看和调整相关参数：

    SHOW PARAMETERS LIKE 'ob_sql_work_area_percentage';
    SET GLOBAL ob_sql_work_area_percentage = 80; -- 根据需要调整
    

  • 同时，确保 total_memory_percentage 和 memstore_limit_percentage 的配置合理。
• 调整优化器参数：
  • 如果查询仍然较慢，可以尝试调整以下参数以优化执行计划：

    SET GLOBAL range_optimizer_max_mem_size = 512M;
    SET GLOBAL optimizer_search_depth = 64; -- 默认值为 31，适当增加搜索深度
    

3. 使用 EXPLAIN 分析执行计划

• 执行以下命令查看查询的执行计划：

  EXPLAIN SELECT
    `kcu`.`TABLE_SCHEMA`,
    `kcu`.`TABLE_NAME`,
    `kcu`.`CONSTRAINT_NAME`,
    `kcu`.`COLUMN_NAME`,
    `kcu`.`REFERENCED_TABLE_SCHEMA`,
    `kcu`.`REFERENCED_TABLE_NAME`,
    `kcu`.`REFERENCED_COLUMN_NAME`
  FROM
    (
      SELECT
        *
      FROM
        `INFORMATION_SCHEMA`.`KEY_COLUMN_USAGE` `kcu`
      WHERE
        `kcu`.`TABLE_SCHEMA` = 'luru_pre'
        AND `kcu`.`TABLE_NAME` = 'xxx' UNION
      SELECT
        *
      FROM
        `INFORMATION_SCHEMA`.`KEY_COLUMN_USAGE` `kcu`
      WHERE
        `kcu`.`TABLE_SCHEMA` = 'luru_pre'
        AND `kcu`.`TABLE_NAME` = 'xxx2'
    ) kcu;
  

• 检查执行计划中是否存在不必要的全表扫描或排序操作。如果发现低效的执行计划，可以通过索引优化或改写 SQL 来改善。

4. 缓存查询结果

• 如果查询结果不频繁变化，可以考虑将结果缓存到物理表中，避免每次查询都从 INFORMATION_SCHEMA 动态生成数据。例如：

  CREATE TABLE IF NOT EXISTS key_column_usage_cache AS
  SELECT
    `kcu`.`TABLE_SCHEMA`,
    `kcu`.`TABLE_NAME`,
    `kcu`.`CONSTRAINT_NAME`,
    `kcu`.`COLUMN_NAME`,
    `kcu`.`REFERENCED_TABLE_SCHEMA`,
    `kcu`.`REFERENCED_TABLE_NAME`,
    `kcu`.`REFERENCED_COLUMN_NAME`
  FROM
    `INFORMATION_SCHEMA`.`KEY_COLUMN_USAGE` `kcu`
  WHERE
    `kcu`.`TABLE_SCHEMA` = 'luru_pre';
  

  然后直接查询缓存表：

  SELECT * FROM key_column_usage_cache WHERE TABLE_NAME IN ('xxx', 'xxx2', ...);
  

5. 对比测试

• 在相同环境下对比 OceanBase 和 MySQL 的执行计划和性能表现，找出具体差异点。可以通过以下工具收集诊断信息：

  obdiag rca run --scene=sql_performance_issue
  obdiag gather scene run --scene=observer.perf_sql
  

总结

• 当前问题的主要原因是查询结构复杂性和系统参数配置不足。
• 建议优先优化 SQL 结构，减少 UNION ALL 的使用，并通过 EXPLAIN 分析执行计划。
• 如果问题仍未解决，可以进一步调整系统参数或缓存查询结果以提升性能。

如果您仍有疑问或需要进一步帮助，请提供更多诊断信息（如执行计划、系统参数配置等），我们将继续为您解答。

具体信息可参考以下文档:

1. 创建分区表
2. oceanbase.CDB_OB_RECOVER_TABLE_JOB_HISTORY

(小助手的答复已结束，如未能解决您的问题，请继续提问并等待其他同学的回复，谢谢！)

111

sql发在上面了

这应该是个老毛病了。你试试425bp1版本是否可以正常执行，这个问题在425版本进行了优化

union本身的资源消耗应该就很大吧,毕竟需要做排序

最简单的sql，相当于把字段in拆成 union，而且mysql 执行很快

现在版本是5.7.25-OceanBase_CE-v4.3.5.1

换个425.2的测试试试。

image3208×637 92.2 KB

image925×321 13.2 KB

sql就是typeorm批量生成的

image1023×585 40 KB

麻烦通过ocp的sql诊断拿一下trace id，然后通过gv$ob_sql_audit查询下详细信息发出来一下。

{

"SVR_IP": "xxx",

"SVR_PORT": 2882,

"REQUEST_ID": 3021545,

"SQL_EXEC_ID": 32549910,

"TRACE_ID": "YB4273AC22B9-000630EF42AF7E4E-0-0",

"SID": 3221721703,

"CLIENT_IP": "xxx",

"CLIENT_PORT": 61787,

"TENANT_ID": 1004,

"TENANT_NAME": "shy",

"EFFECTIVE_TENANT_ID": 1004,

"USER_ID": 500003,

"USER_NAME": "xxx",

"USER_GROUP": 0,

"USER_CLIENT_IP": "xxx",

"DB_ID": 500002,

"DB_NAME": "luru_pre",

"SQL_ID": "212E849647024F6B3BEBA558657735B0",

"QUERY_SQL": "xxx",

"PLAN_ID": 9320,

"AFFECTED_ROWS": 0,

"RETURN_ROWS": 0,

"PARTITION_CNT": 2208,

"RET_CODE": 0,

"QC_ID": 0,

"DFO_ID": 0,

"SQC_ID": 0,

"WORKER_ID": 0,

"EVENT": "",

"P1TEXT": "",

"P1": 0,

"P2TEXT": "",

"P2": 0,

"P3TEXT": "",

"P3": 0,

"LEVEL": 0,

"WAIT_CLASS_ID": 100,

"WAIT_CLASS#": 0,

"WAIT_CLASS": "OTHER",

"STATE": "MAX_WAIT TIME ZERO",

"WAIT_TIME_MICRO": 0,

"TOTAL_WAIT_TIME_MICRO": 0,

"TOTAL_WAITS": 0,

"RPC_COUNT": 0,

"PLAN_TYPE": 1,

"IS_INNER_SQL": 0,

"IS_EXECUTOR_RPC": 0,

"IS_HIT_PLAN": 1,

"REQUEST_TIME": 1742803635367483,

"ELAPSED_TIME": 200146,

"NET_TIME": 0,

"NET_WAIT_TIME": 2,

"QUEUE_TIME": 19,

"DECODE_TIME": 0,

"GET_PLAN_TIME": 15470,

"EXECUTE_TIME": 184619,

"APPLICATION_WAIT_TIME": 0,

"CONCURRENCY_WAIT_TIME": 0,

"USER_IO_WAIT_TIME": 0,

"SCHEDULE_TIME": 0,

"ROW_CACHE_HIT": 225,

"BLOOM_FILTER_CACHE_HIT": 0,

"BLOCK_CACHE_HIT": 0,

"DISK_READS": 0,

"RETRY_CNT": 0,

"TABLE_SCAN": 1,

"CONSISTENCY_LEVEL": 3,

"MEMSTORE_READ_ROW_COUNT": 0,

"SSSTORE_READ_ROW_COUNT": 70401,

"DATA_BLOCK_READ_CNT": 0,

"DATA_BLOCK_CACHE_HIT": 851,

"INDEX_BLOCK_READ_CNT": 0,

"INDEX_BLOCK_CACHE_HIT": 712,

"BLOCKSCAN_BLOCK_CNT": 0,

"BLOCKSCAN_ROW_CNT": 0,

"PUSHDOWN_STORAGE_FILTER_ROW_CNT": 0,

"REQUEST_MEMORY_USED": 1128101836,

"EXPECTED_WORKER_COUNT": 0,

"USED_WORKER_COUNT": 0,

"SCHED_INFO": null,

"FUSE_ROW_CACHE_HIT": 368,

"PS_CLIENT_STMT_ID": -1,

"PS_INNER_STMT_ID": -1,

"TX_ID": 7475595,

"SNAPSHOT_VERSION": 1742803635083569000,

"REQUEST_TYPE": 2,

"IS_BATCHED_MULTI_STMT": 0,

"OB_TRACE_INFO": null,

"PLAN_HASH": 3752308579848672248,

"LOCK_FOR_READ_TIME": 0,

"PARAMS_VALUE": "",

"RULE_NAME": "",

"PARTITION_HIT": 1,

"TX_INTERNAL_ROUTING": 0,

"TX_STATE_VERSION": 0,

"FLT_TRACE_ID": "",

"PL_TRACE_ID": null,

"PLSQL_EXEC_TIME": 0,

"FORMAT_SQL_ID": "EDD8AF801FA44F178C037487B1B25882",

"STMT_TYPE": "SELECT",

"TOTAL_MEMSTORE_READ_ROW_COUNT": 0,

"TOTAL_SSSTORE_READ_ROW_COUNT": 70401,

"PROXY_USER": "",

"SEQ_NUM": 1742803635370739,

"NETWORK_WAIT_TIME": 0,

"PLSQL_COMPILE_TIME": 0

}

上面获取结果的sql是第一次执行么

提供一下原sql语句
1.查看下租户的内存分布 select * from GV$OB_MEMORY;
2.通过关键词 ‘malloc_allocator.*tenant: 1008’ 可以获取租户的内存元信息（limit、hold、cache_hold），-A可以显示更多的内存元信息的细节
grep 'malloc_allocator.tenant: XXXX’ | observer.log -A 20

3.通过关键词 ‘1008 ctx_id= DEFAULT_CTX_ID’ 可以获取1008租户的DEFAULT_CTX_ID的内存元信息
以DEFAULT_CTX_ID为例子：
grep ‘XXXX ctx_id= DEFAULT_CTX_ID’ | observer.log* -A 20

内存分布

后两个无结果

image902×239 28.9 KB