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

jackie-zzzz · 2025 年3 月 23 日 17:05

【使用环境】生产环境 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

然后基于 https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000000209941?back=kb 调整了 range_optimizer_max_mem_size 为256M 能正常执行了，但是sql执行的很慢

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

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

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

论坛小助手 · 2025 年3 月 23 日 17:05

根因分析场景: 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 或性能瓶颈进行分析。

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

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

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

obpilot · 2025 年3 月 23 日 17:54

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

jackie-zzzz · 2025 年3 月 23 日 20:04

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

这是30张表的执行时间

相同sql mysql 只用8ms

论坛小助手 · 2025 年3 月 23 日 20:19

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

问题分析

SQL 结构复杂性：
- 您的 SQL 查询通过多次 UNION ALL 将多个子查询结果合并。这种结构在 OceanBase 中可能会导致优化器生成较复杂的执行计划，尤其是在涉及 INFORMATION_SCHEMA 表时。
- INFORMATION_SCHEMA 是一个虚拟表，其数据来源于系统元数据。访问这些表时，OceanBase 需要动态生成和解析元数据，这可能导致额外的开销。
内存限制与参数调整：
- 调整 range_optimizer_max_mem_size 参数后，虽然解决了 OOM 问题，但性能仍然较差。这表明当前的执行计划可能不够高效。
- OceanBase 的默认内存分配策略可能不适合此类复杂查询，尤其是当租户内存有限时。
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 分析执行计划。
如果问题仍未解决，可以进一步调整系统参数或缓存查询结果以提升性能。

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

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

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

AntTech_RTXNYP · 2025 年3 月 23 日 21:47

111

jackie-zzzz · 2025 年3 月 24 日 09:35

sql发在上面了

辞霜 · 2025 年3 月 24 日 10:05

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

独善其身 · 2025 年3 月 24 日 10:10

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

jackie-zzzz · 2025 年3 月 24 日 11:01

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

jackie-zzzz · 2025 年3 月 24 日 11:02

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

辞霜 · 2025 年3 月 24 日 11:05

换个425.2的测试试试。

jackie-zzzz · 2025 年3 月 24 日 14:43

4.2.5.2 版本也是一样的，这几个慢sql都是多表union 慢，相同sql mysql正常

sql就是typeorm批量生成的

辞霜 · 2025 年3 月 24 日 15:58

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

jackie-zzzz · 2025 年3 月 24 日 16:16

{

"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

}

辞霜 · 2025 年3 月 24 日 16:31

上面获取结果的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

jackie-zzzz · 2025 年3 月 24 日 16:39

内存分布

CTX_NAME	MOD_NAME	COUNT	HOLD	USED
PLAN_CACHE_CTX_ID	SqlPhyPlan	16906	275458432	269369262
MEMSTORE_CTX_ID	Memstore	106	220561408	220460496
DEFAULT_CTX_ID	MysqlRequesReco	90	135921664	135620480
CO_STACK	CoStack	220	113541120	113329920
META_OBJ_CTX_ID	L_TabletPool	1227	90464256	80480157
DEFAULT_CTX_ID	LogGroupBuffer	2	83927040	83886080
KVSTORE_CACHE_ID	KvstorCacheMb	30	62914560	0
PLAN_CACHE_CTX_ID	SqlPlanCache	249	42629888	42358560
DEFAULT_CTX_ID	MrgMemPoolChk	2	33595392	33554672
DEFAULT_CTX_ID	IoControl	9	32296960	32233824
DEFAULT_CTX_ID	ServerObjecPool	3	28585984	28524544
DEFAULT_CTX_ID	CACHE_MAP_NODE	12	24969216	24956928
DEFAULT_CTX_ID	DiagnosticInfos	16	16908288	16777728
DEFAULT_CTX_ID	BloomFilter	1	16797696	16777248
DEFAULT_CTX_ID	OmtTenant	111	16232928	16067217

后两个无结果

AntTech_QWWMD2 · 2025 年3 月 24 日 18:42

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