主键模型关联报错

💬 StarRocks 用户问答
#1

主键模型关联引起：Memory of process exceed limit. try consume:2147483648 Used: 153236659352, Limit: 154618822656. Mem usage has exceed the limit of BE，
三张表关联，先是2个主键表关联，然后和另外一张更新表关联再插入结果表如下所示：
insert into table
select
xxx
from table_a a
left join table_b b on a.id=b.id
left join table_c c on a.id=c.id
where xxxx
其中table_a和table_b是主键模型，table_c是更新模型。执行上面的sql就报错：Memory of process exceed limit. try consume:2147483648 Used: 153236659352, Limit: 154618822656. Mem usage has exceed the limit of BE
把table_b换成更新模型就不会报错。主键模型执行sql对内存要求很高吗？不是执行导入的时候才会耗内存吗？集群内存是充足的。请问这是怎么回事?
版本：2.3.8
规模：10be+3fe 192内存，40c，不是混部，
|enable_pipeline_engine|true|
|enable_pipeline_query_statistic|true|
|pipeline_dop|4|
|pipeline_profile_level|1|

#2

这几个表数据多大，可以看下执行计划

#3

table_a 80亿，table_b不到2千万，table_c 10亿，不太理解的是table_b不用主键用更新模型就不会报错，可以正常跑出来，一换成主键模型跑的时候，内存蹭蹭往上涨，然后就报错，这是啥情况，主键模型内存占用高是指导入的时候对吧？

#4

发下更新模型和主键模型分别的explain costs

#5

table_b为更新模型的explain_costs.txt (22.6 KB) table_b为主键模型explain_costs.txt (10.1 KB)

#6

发下更新和主键模型的表建表

image
image1740×1097 649 KB

#7

主键表建表语句：
CREATE TABLE xxx (
month date NOT NULL COMMENT “”,
platform_id int(11) NOT NULL COMMENT “”,
id varchar(2000) NOT NULL COMMENT “”,
local_content varchar(65533) NULL COMMENT “”,
first_image_content varchar(65533) NULL COMMENT “”,
baidu_content varchar(65533) NULL COMMENT “”,
access_date date NOT NULL COMMENT “”,
etl_load_dt datetime NULL COMMENT “”
) ENGINE=OLAP
PRIMARY KEY(month, platform_id, id)
COMMENT “OLAP”
PARTITION BY RANGE(month)
(PARTITION p202201 VALUES [(‘2022-01-01’), (‘2022-02-01’)),
PARTITION p202202 VALUES [(‘2022-02-01’), (‘2022-03-01’)),
PARTITION p202203 VALUES [(‘2022-03-01’), (‘2022-04-01’)),
PARTITION p202204 VALUES [(‘2022-04-01’), (‘2022-05-01’)),
PARTITION p202205 VALUES [(‘2022-05-01’), (‘2022-06-01’)),
PARTITION p202206 VALUES [(‘2022-06-01’), (‘2022-07-01’)),
PARTITION p202207 VALUES [(‘2022-07-01’), (‘2022-08-01’)),
PARTITION p202208 VALUES [(‘2022-08-01’), (‘2022-09-01’)),
PARTITION p202209 VALUES [(‘2022-09-01’), (‘2022-10-01’)),
PARTITION p202210 VALUES [(‘2022-10-01’), (‘2022-11-01’)),
PARTITION p202211 VALUES [(‘2022-11-01’), (‘2022-12-01’)),
PARTITION p202212 VALUES [(‘2022-12-01’), (‘2023-01-01’)),
PARTITION p202301 VALUES [(‘2023-01-01’), (‘2023-02-01’)),
PARTITION p202302 VALUES [(‘2023-02-01’), (‘2023-03-01’)),
PARTITION p202303 VALUES [(‘2023-03-01’), (‘2023-04-01’)),
PARTITION p202304 VALUES [(‘2023-04-01’), (‘2023-05-01’)),
PARTITION p202305 VALUES [(‘2023-05-01’), (‘2023-06-01’)),
PARTITION p202306 VALUES [(‘2023-06-01’), (‘2023-07-01’)),
PARTITION p202307 VALUES [(‘2023-07-01’), (‘2023-08-01’)),
PARTITION p202308 VALUES [(‘2023-08-01’), (‘2023-09-01’)),
PARTITION p202309 VALUES [(‘2023-09-01’), (‘2023-10-01’)))
DISTRIBUTED BY HASH(id) BUCKETS 32
PROPERTIES (
“replication_num” = “3”,
“dynamic_partition.enable” = “true”,
“dynamic_partition.time_unit” = “MONTH”,
“dynamic_partition.time_zone” = “Asia/Shanghai”,
“dynamic_partition.start” = “-2147483648”,
“dynamic_partition.end” = “1”,
“dynamic_partition.prefix” = “p”,
“dynamic_partition.buckets” = “32”,
“dynamic_partition.start_day_of_month” = “1”,
“in_memory” = “false”,
“storage_format” = “DEFAULT”,
“enable_persistent_index” = “false”
);

更新表建表语句
CREATE TABLE xxxx(
month date NULL COMMENT “”,
platform_id int(11) NULL COMMENT “”,
id varchar(65533) NULL COMMENT “”,
local_content varchar(65533) NULL COMMENT “”,
first_image_content varchar(65533) NULL COMMENT “”,
baidu_content varchar(65533) NULL COMMENT “”,
access_date date NOT NULL COMMENT “”,
etl_load_dt datetime NULL COMMENT “”
) ENGINE=OLAP
UNIQUE KEY(month, platform_id, id)
COMMENT “OLAP”
PARTITION BY RANGE(month)
(PARTITION p202201 VALUES [(‘2022-01-01’), (‘2022-02-01’)),
PARTITION p202202 VALUES [(‘2022-02-01’), (‘2022-03-01’)),
PARTITION p202203 VALUES [(‘2022-03-01’), (‘2022-04-01’)),
PARTITION p202204 VALUES [(‘2022-04-01’), (‘2022-05-01’)),
PARTITION p202205 VALUES [(‘2022-05-01’), (‘2022-06-01’)),
PARTITION p202206 VALUES [(‘2022-06-01’), (‘2022-07-01’)),
PARTITION p202207 VALUES [(‘2022-07-01’), (‘2022-08-01’)),
PARTITION p202208 VALUES [(‘2022-08-01’), (‘2022-09-01’)),
PARTITION p202209 VALUES [(‘2022-09-01’), (‘2022-10-01’)),
PARTITION p202210 VALUES [(‘2022-10-01’), (‘2022-11-01’)),
PARTITION p202211 VALUES [(‘2022-11-01’), (‘2022-12-01’)),
PARTITION p202212 VALUES [(‘2022-12-01’), (‘2023-01-01’)),
PARTITION p202301 VALUES [(‘2023-01-01’), (‘2023-02-01’)),
PARTITION p202302 VALUES [(‘2023-02-01’), (‘2023-03-01’)),
PARTITION p202303 VALUES [(‘2023-03-01’), (‘2023-04-01’)),
PARTITION p202304 VALUES [(‘2023-04-01’), (‘2023-05-01’)),
PARTITION p202305 VALUES [(‘2023-05-01’), (‘2023-06-01’)),
PARTITION p202306 VALUES [(‘2023-06-01’), (‘2023-07-01’)),
PARTITION p202307 VALUES [(‘2023-07-01’), (‘2023-08-01’)),
PARTITION p202308 VALUES [(‘2023-08-01’), (‘2023-09-01’)),
PARTITION p202309 VALUES [(‘2023-09-01’), (‘2023-10-01’)))
DISTRIBUTED BY HASH(id) BUCKETS 32
PROPERTIES (
“replication_num” = “3”,
“dynamic_partition.enable” = “true”,
“dynamic_partition.time_unit” = “MONTH”,
“dynamic_partition.time_zone” = “Asia/Shanghai”,
“dynamic_partition.start” = “-2147483648”,
“dynamic_partition.end” = “1”,
“dynamic_partition.prefix” = “p”,
“dynamic_partition.buckets” = “32”,
“dynamic_partition.start_day_of_month” = “1”,
“in_memory” = “false”,
“storage_format” = “DEFAULT”,
“enable_persistent_index” = “false”
);

#8

也遇到了这个报错，请问如何解决呢？扩容be可以解决吗
Memory of process exceed limit. try consume:2147483648 Used: 153236659352, Limit: 154618822656. Mem usage has exceed the limit of BE

京ICP备2022026421号-1