sql请求"ERROR 4013 (HY001): No memory or reach tenant memory limit"

[boysxw]

生产环境 oceanbase_ce 4.2.1.BP7 版本
【问题描述】使用oms迁移时全量校验时，会产生类似于如下SQL
mysql>select /+QUERY_TIMEOUT(600000000) END_OUTLINE_DATA/ TABLE_SCHEMA,TABLE_NAME,INDEX_NAME,NON_UNIQUE,SEQ_IN_INDEX,COLUMN_NAME,SUB_PART
from information_schema.STATISTICS where ((TABLE_SCHEMA=‘test27db’ AND TABLE_NAME in (‘test_table1’,‘test_table4’))
OR (TABLE_SCHEMA=‘test04db’ AND TABLE_NAME in (‘test_table4’,‘test_table3’))
OR (TABLE_SCHEMA=‘test48db’ AND TABLE_NAME in (‘test_table1’))
OR (TABLE_SCHEMA=‘test25db’ AND TABLE_NAME in (‘test_table1’,‘test_table4’))
OR (TABLE_SCHEMA=‘test06db’ AND TABLE_NAME in (‘test_table4’,‘test_table3’))
OR (TABLE_SCHEMA=‘test08db’ AND TABLE_NAME in (‘test_table3’,‘test_table4’))
OR (TABLE_SCHEMA=‘test29db’ AND TABLE_NAME in (‘test_table1’,‘test_table4’))
OR (TABLE_SCHEMA=‘test42db’ AND TABLE_NAME in (‘test_table1’))
OR (TABLE_SCHEMA=‘test63db’ AND TABLE_NAME in (‘test_table1’,‘test_table5’))
OR (TABLE_SCHEMA=‘test40db’ AND TABLE_NAME in (‘test_table1’))
OR (TABLE_SCHEMA=‘test61db’ AND TABLE_NAME in (‘test_table1’,‘test_table5’))
OR (TABLE_SCHEMA=‘test23db’ AND TABLE_NAME in (‘test_table1’,‘test_table4’))
OR (TABLE_SCHEMA=‘test46db’ AND TABLE_NAME in (‘test_table1’))
OR (TABLE_SCHEMA=‘test44db’ AND TABLE_NAME in (‘test_table1’))
OR (TABLE_SCHEMA=‘test21db’ AND TABLE_NAME in (‘test_table4’,‘test_table1’))
OR (TABLE_SCHEMA=‘test02db’ AND TABLE_NAME in (‘test_table3’,‘test_table4’))
OR (TABLE_SCHEMA=‘test14db’ AND TABLE_NAME in (‘test_table4’))
OR (TABLE_SCHEMA=‘test39db’ AND TABLE_NAME in (‘test_table1’))
OR (TABLE_SCHEMA=‘test37db’ AND TABLE_NAME in (‘test_table4’,‘test_table1’))
OR (TABLE_SCHEMA=‘test16db’ AND TABLE_NAME in (‘test_table4’))
OR (TABLE_SCHEMA=‘test58db’ AND TABLE_NAME in (‘test_table5’,‘test_table1’))
OR (TABLE_SCHEMA=‘test18db’ AND TABLE_NAME in (‘test_table4’))
OR (TABLE_SCHEMA=‘test31db’ AND TABLE_NAME in (‘test_table1’,‘test_table4’))
OR (TABLE_SCHEMA=‘test52db’ AND TABLE_NAME in (‘test_table1’))
OR (TABLE_SCHEMA=‘test50db’ AND TABLE_NAME in (‘test_table1’))
OR (TABLE_SCHEMA=‘test56db’ AND TABLE_NAME in (‘test_table1’))
OR (TABLE_SCHEMA=‘test10db’ AND TABLE_NAME in (‘test_table2’,‘test_table3’))
OR (TABLE_SCHEMA=‘test35db’ AND TABLE_NAME in (‘test_table1’,‘test_table2’))
OR (TABLE_SCHEMA=‘test54db’ AND TABLE_NAME in (‘test_table1’))
OR (TABLE_SCHEMA=‘test12db’ AND TABLE_NAME in (‘test_table2’))
OR (TABLE_SCHEMA=‘test33db’ AND TABLE_NAME in (‘test_table1’,‘test_table2’))
OR (TABLE_SCHEMA=‘test03db’ AND TABLE_NAME in (‘test_table3’,‘test_table2’))
OR (TABLE_SCHEMA=‘test28db’ AND TABLE_NAME in (‘test_table1’,‘test_table2’))
OR (TABLE_SCHEMA=‘test49db’ AND TABLE_NAME in (‘test_table1’))
OR (TABLE_SCHEMA=‘test26db’ AND TABLE_NAME in (‘test_table2’,‘test_table1’))
OR (TABLE_SCHEMA=‘test05db’ AND TABLE_NAME in (‘test_table3’,‘test_table2’))
OR (TABLE_SCHEMA=‘test47db’ AND TABLE_NAME in (‘test_table1’))
OR (TABLE_SCHEMA=‘test07db’ AND TABLE_NAME in (‘test_table3’,‘test_table2’))
OR (TABLE_SCHEMA=‘test09db’ AND TABLE_NAME in (‘test_table3’,‘test_table2’))
OR (TABLE_SCHEMA=‘test41db’ AND TABLE_NAME in (‘test_table1’))
OR (TABLE_SCHEMA=‘test20db’ AND TABLE_NAME in (‘test_table1’,‘test_table2’))
OR (TABLE_SCHEMA=‘test62db’ AND TABLE_NAME in (‘test_table5’,‘test_table1’))
OR (TABLE_SCHEMA=‘test45db’ AND TABLE_NAME in (‘test_table1’))
OR (TABLE_SCHEMA=‘test24db’ AND TABLE_NAME in (‘test_table1’,‘test_table2’))
OR (TABLE_SCHEMA=‘test43db’ AND TABLE_NAME in (‘test_table1’))
OR (TABLE_SCHEMA=‘test01db’ AND TABLE_NAME in (‘test_table3’,‘test_table2’))
OR (TABLE_SCHEMA=‘test64db’ AND TABLE_NAME in (‘test_table5’,‘test_table1’))
OR (TABLE_SCHEMA=‘test22db’ AND TABLE_NAME in (‘test_table1’,‘test_table2’))
OR (TABLE_SCHEMA=‘test60db’ AND TABLE_NAME in (‘test_table5’,‘test_table1’))
OR (TABLE_SCHEMA=‘test15db’ AND TABLE_NAME in (‘test_table2’))
OR (TABLE_SCHEMA=‘test38db’ AND TABLE_NAME in (‘test_table1’))
OR (TABLE_SCHEMA=‘test17db’ AND TABLE_NAME in (‘test_table2’))
OR (TABLE_SCHEMA=‘test36db’ AND TABLE_NAME in (‘test_table1’,‘test_table2’))
OR (TABLE_SCHEMA=‘test59db’ AND TABLE_NAME in (‘test_table1’,‘test_table5’))
OR (TABLE_SCHEMA=‘test19db’ AND TABLE_NAME in (‘test_table2’))
OR (TABLE_SCHEMA=‘test53db’ AND TABLE_NAME in (‘test_table1’))
OR (TABLE_SCHEMA=‘test30db’ AND TABLE_NAME in (‘test_table2’,‘test_table1’))
OR (TABLE_SCHEMA=‘test51db’ AND TABLE_NAME in (‘test_table1’))
OR (TABLE_SCHEMA=‘test57db’ AND TABLE_NAME in (‘test_table1’))
OR (TABLE_SCHEMA=‘test11db’ AND TABLE_NAME in (‘test_table2’,‘test_table3’))
OR (TABLE_SCHEMA=‘test34db’ AND TABLE_NAME in (‘test_table2’,‘test_table1’))
OR (TABLE_SCHEMA=‘test13db’ AND TABLE_NAME in (‘test_table2’))
OR (TABLE_SCHEMA=‘test55db’ AND TABLE_NAME in (‘test_table1’))
OR (TABLE_SCHEMA=‘test32db’ AND TABLE_NAME in (‘test_table1’,‘test_table2’))
)
order by TABLE_SCHEMA,TABLE_NAME,INDEX_NAME, SEQ_IN_INDEX asc;

##执行会报 ERROR 4013 (HY001): No memory or reach tenant memory limit

2、observer中日志
observer.log.20240705124326394:[2024-07-05 12:43:17.315273] WARN common_alloc (ob_tenant_ctx_allocator.cpp:457) [245496][T1002_ArcTimer][T1002][YB420A6DD963-00061BB79540BD31-0-0] [lt=0][errcode=-4013] No memory or reach tenant memory limit([OOPS]=“alloc failed reason”, msg=tenant memory has reached the upper limit(tenant_id: 1002, tenant_hold: 144954884096, tenant_limit: 144955146240, alloc_size: 2121728))
observer.log.20240705124326394:[2024-07-05 12:43:17.406793] WARN common_alloc (ob_tenant_ctx_allocator.cpp:457) [150041][TimezoneMgr][T1002][YB420A6DD963-00061BB791981954-0-0] [lt=0][errcode=-4013] No memory or reach tenant memory limit([OOPS]=“alloc failed reason”, msg=tenant memory has reached the upper limit(tenant_id: 1002, tenant_hold: 144954884096, tenant_limit: 144955146240, alloc_size: 2097152))
observer.log.20240705124326394:[2024-07-05 12:43:17.446865] WARN common_alloc (ob_tenant_ctx_allocator.cpp:457) [245735][T1002_L0_G0][T1002][YB420A6DD963-00061BB7F0C858BE-0-0] [lt=0][errcode=-4013] No memory or reach tenant memory limit([OOPS]=“alloc failed reason”, msg=tenant memory has reached the upper limit(tenant_id: 1002, tenant_hold: 144954884096, tenant_limit: 144955146240, alloc_size: 2097152))
observer.log.20240705125020032:[2024-07-05 12:44:39.672132] WARN common_alloc (ob_tenant_ctx_allocator.cpp:457) [245486][T1002_TenantWea][T1002][Y0-0000000000000000-0-0] [lt=5][errcode=-4013] No memory or reach tenant memory limit([OOPS]=“alloc failed reason”, msg=tenant memory has reached the upper limit(tenant_id: 1002, tenant_hold: 144954195968, tenant_limit: 144955146240, alloc_size: 2097152))
observer.log.20240705125020032:[2024-07-05 12:44:39.737063] WARN common_alloc (ob_tenant_ctx_allocator.cpp:457) [245503][T1002_TxLoopWor][T1002][Y0-0000000000000000-0-0] [lt=0][errcode=-4013] No memory or reach tenant memory limit([OOPS]=“alloc failed reason”, msg=tenant memory has reached the upper limit(tenant_id: 1002, tenant_hold: 144954195968, tenant_limit: 144955146240, alloc_size: 2097152))
observer.log.20240705125020032:[2024-07-05 12:44:39.755771] WARN common_alloc (ob_tenant_ctx_allocator.cpp:457) [245678][T1002_L0_G0][T1002][YB420A6DD963-00061BB7ED5AA15D-0-0] [lt=0][errcode=-4013] No memory or reach tenant memory limit([OOPS]=“alloc failed reason”, msg=tenant memory has reached the upper limit(tenant_id: 1002, tenant_hold: 144954195968, tenant_limit: 144955146240, alloc_size: 2097152))
observer.log.20240705125020032:[2024-07-05 12:44:39.755917] WARN common_alloc (ob_tenant_ctx_allocator.cpp:457) [149421][pnio1][T0][YB420A3AEBAA-00061BB9B96F4E31-0-0] [lt=3][errcode=-4013] No memory or reach tenant memory limit([OOPS]=“alloc failed reason”, msg=tenant memory has reached the upper limit(tenant_id: 1002, tenant_hold: 144954195968, tenant_limit: 144955146240, alloc_size: 2097152))
observer.log.20240705125020032:[2024-07-05 12:44:39.756491] WARN common_alloc (ob_tenant_ctx_allocator.cpp:457) [149419][pnio1][T0][YB420A3AEBAA-00061BB9B96F4E2F-0-0] [lt=0][errcode=-4013] No memory or reach tenant memory limit([OOPS]=“alloc failed reason”, msg=tenant memory has reached the upper limit(tenant_id: 1002, tenant_hold: 144954195968, tenant_limit: 144955146240, alloc_size: 2097152))
observer.log.20240705125020032:[2024-07-05 12:44:39.759431] WARN common_alloc (ob_tenant_ctx_allocator.cpp:457) [149423][pnio1][T0][YB420A19F8F3-00061C4D7BCE7A88-0-0] [lt=0][errcode=-4013] No memory or reach tenant memory limit([OOPS]=“alloc failed reason”, msg=tenant memory has reached the upper limit(tenant_id: 1002, tenant_hold: 144954195968, tenant_limit: 144955146240, alloc_size: 2097152))
observer.log.20240705125020032:[2024-07-05 12:44:39.760103] WARN common_alloc (ob_tenant_ctx_allocator.cpp:457) [149417][pnio1][T0][YB420A19F8F3-00061C4D7BCE7A89-0-0] [lt=0][errcode=-4013] No memory or reach tenant memory limit([OOPS]=“alloc failed reason”, msg=tenant memory has reached the upper limit(tenant_id: 1002, tenant_hold: 144954195968, tenant_limit: 144955146240, alloc_size: 2097152))

2、内存状态正常

3、查询request_memory_used=125868913152，这么多内存

mysql> select 125868913152/1024/1024/1024 gb;
±-----------------+
| gb |
±-----------------+
| 117.224560260772 |
±-----------------+
疑问：
1、在3.1.4版本中，该SQL执行很快，在4.2.1.BP7可能是计划的原因，走的很慢.
2、该SQL执行gv$ob_sql_audit该请求，request_memory_used是指预估真的要117GB的内存？
谢谢

[淇铭]

你在omsweb上查看组件监控 看看组件的状态 贴一下

[boysxw]

oms全量校验报错日志是No memory or reach tenant。用这个请求在observer集群上执行，每次都报no memory。应该是集群上的问题。

[淇铭]

@boysxw

tenant memory has reached the upper limit

查看一下 截图看看或着贴个日志文档
grep ‘malloc_allocator.*tenant: 1002’ observer.log.20240705124326394 -A 20

[boysxw]

@淇铭

grep ‘malloc_allocator.*tenant: 1002’ observer.log.20240705124326394 -A 20

[boysxw]

@淇铭

着贴个日志文

observer.log.20240705144026502:[2024-07-05 14:39:08.178967] INFO [LIB] operator() (ob_malloc_allocator.cpp:567) [149355][MemDumpTimer][T1002][Y0-0000000000000000-0-0] [lt=4] [MEMORY] tenant: 1002, limit: 144,955,146,240 hold: 49,165,344,768 rpc_hold: 0 cache_hold: 34,001,125,376 cache_used: 34,001,125,376 cache_item_count: 16,213
observer.log.20240705144026502:[2024-07-05 14:39:18.181221] INFO [LIB] operator() (ob_malloc_allocator.cpp:567) [149355][MemDumpTimer][T1002][Y0-0000000000000000-0-0] [lt=4] [MEMORY] tenant: 1002, limit: 144,955,146,240 hold: 49,169,649,664 rpc_hold: 0 cache_hold: 34,009,513,984 cache_used: 34,009,513,984 cache_item_count: 16,217
observer.log.20240705144026502:[2024-07-05 14:39:28.183410] INFO [LIB] operator() (ob_malloc_allocator.cpp:567) [149355][MemDumpTimer][T1002][Y0-0000000000000000-0-0] [lt=4] [MEMORY] tenant: 1002, limit: 144,955,146,240 hold: 49,169,649,664 rpc_hold: 0 cache_hold: 34,009,513,984 cache_used: 34,009,513,984 cache_item_count: 16,217
observer.log.20240705144026502:[2024-07-05 14:39:38.185569] INFO [LIB] operator() (ob_malloc_allocator.cpp:567) [149355][MemDumpTimer][T1002][Y0-0000000000000000-0-0] [lt=5] [MEMORY] tenant: 1002, limit: 144,955,146,240 hold: 49,169,649,664 rpc_hold: 0 cache_hold: 34,009,513,984 cache_used: 34,009,513,984 cache_item_count: 16,217
observer.log.20240705144026502:[2024-07-05 14:39:48.187775] INFO [LIB] operator() (ob_malloc_allocator.cpp:567) [149355][MemDumpTimer][T1002][Y0-0000000000000000-0-0] [lt=5] [MEMORY] tenant: 1002, limit: 144,955,146,240 hold: 49,175,941,120 rpc_hold: 0 cache_hold: 34,015,805,440 cache_used: 34,015,805,440 cache_item_count: 16,220
observer.log.20240705144026502:[2024-07-05 14:39:58.190067] INFO [LIB] operator() (ob_malloc_allocator.cpp:567) [149355][MemDumpTimer][T1002][Y0-0000000000000000-0-0] [lt=5] [MEMORY] tenant: 1002, limit: 144,955,146,240 hold: 49,177,927,680 rpc_hold: 0 cache_hold: 34,015,805,440 cache_used: 34,015,805,440 cache_item_count: 16,220
observer.log.20240705144026502:[2024-07-05 14:40:08.192390] INFO [LIB] operator() (ob_malloc_allocator.cpp:567) [149355][MemDumpTimer][T1002][Y0-0000000000000000-0-0] [lt=5] [MEMORY] tenant: 1002, limit: 144,955,146,240 hold: 49,177,927,680 rpc_hold: 0 cache_hold: 34,015,805,440 cache_used: 34,015,805,440 cache_item_count: 16,220
observer.log.20240705144026502:[2024-07-05 14:40:18.194696] INFO [LIB] operator() (ob_malloc_allocator.cpp:567) [149355][MemDumpTimer][T1002][Y0-0000000000000000-0-0] [lt=5] [MEMORY] tenant: 1002, limit: 144,955,146,240 hold: 49,369,415,680 rpc_hold: 0 cache_hold: 34,164,703,232 cache_used: 34,164,703,232 cache_item_count: 16,291

[秃蛙]

1. 3.x和4.x执行计划和表结构，数据量信息麻烦提供下，需要确认下计划差异是否符合预期。

2）request_memory_used是这个租户某短时间内使用内存的大小。
#查看memstore内存使用情况
sys租户可以看下select * from gv$ob_memstore;

#查看转储情况
SELECT * FROM oceanbase.GV$OB_TABLET_COMPACTION_PROGRESS WHERE TYPE=‘MINI_MERGE’\G

#查看转储阀值
SHOW PARAMETERS LIKE ‘freeze_trigger_percentage’

[秃蛙]

该问题已确认为内核缺陷。
当前规避方式： 将in 优化关闭: alter system set _enable_in_range_optimization = 0;
风险：有些大in的场景可能会内存开销增加。

或者升级4.2.2以上的版本使用新版query range可解决。

规划OB421bp10版本会进行修复。

[彤栩]

哈喽，boysxw~ 你这个问题被我们研发同学确认为内核缺陷，相应的积分已经退回至你的账户啦，可以查看下个人中心【积分记录】里的退回情况哈，有问题可以随时 cue 我~

[近墨者]

是哪块的内存模块使用过高呢，是memstore还是kvcache相关的？
我们在V3版本也有这种情况，关闭了in优化相关的参数。

[秃蛙]

这个问题是SqlExecutor优化器模块占用内存大。