为了更快的定位您的问题，请提供以下信息，谢谢
为了更快的定位您的问题，请提供以下信息，谢谢
【详述】执行insert into tableName select … 语句时报错 SQL 错误 [1064] [42000]: Memory of Fragment b2d0d622-89d3-11ef-bc77-52da3d732399 exceed limit. try consume:4326924288 Used: 6760575520, Limit: 21474836480. Mem usage has exceed the limit of single query, You can change the limit by set session variable exec_mem_limit or query_mem_limit.
Memory of Fragment b2d0d622-89d3-11ef-bc77-52da3d732399 exceed limit. try consume:4326924288 Used: 6760575520, Limit: 21474836480. Mem usage has exceed the limit of single query, You can change the limit by set session variable exec_mem_limit or query_mem_limit.
Memory of Fragment b2d0d622-89d3-11ef-bc77-52da3d732399 exceed limit. try consume:4326924288 Used: 6760575520, Limit: 21474836480. Mem usage has exceed the limit of single query, You can change the limit by set session variable exec_mem_limit or query_mem_limit.
【背景】做过哪些操作？
【业务影响】数据无法插入目标表，且执行sql会将be节点搞挂
【是否存算分离】否
【StarRocks版本】2.3.2
【集群规模】例如：2fe+3be
【机器信息】64C252G (混部有CDH但资源充足)
【联系方式】为了在解决问题过程中能及时联系到您获取一些日志信息，请补充下您的联系方式，例如：社区群17-林哥小子或者邮箱，谢谢
【附件】
【参考过的解决办法】fe和be的conf配置文件都设置过 mem_limit = 60G 单次执行该SQL查询时，设置参数set exec_mem_limit=20G;
(附：以前执行其它starrocks sql任务也出现过内存不够报错，设置参数set exec_mem_limit增大内存后能直接解决，本次一直不行)
建表语句
DROP TABLE IF EXISTS ads_tender_bid_applet;
CREATE TABLE ads_tender_bid_applet (
sort_number string comment ‘序号/id’,
tender_no string comment ‘招标编号’,
title string comment ‘标题’,
content string comment ‘正文’,
publish_time string comment ‘发布时间’,
tenderee string comment ‘招标人’,
province_code string comment ‘项目所在省编码’,
province string comment ‘项目所在省’,
city_code string comment ‘项目所在市编码’,
city string comment ‘项目所在市’,
target_type string comment ‘标的类型’,
announcement_type string comment ‘公告类型’,
procurement_method string comment ‘采购方式’,
purchasing_system string comment ‘采购方体系’,
url string comment ‘原文网址’,
tender_contact_person string comment ‘招标人联系人’,
tender_telephone string comment ‘招标人电话’,
tender_agent string comment ‘招标代理机构’,
tender_agent_contact_person string comment ‘招标代理机构联系人’,
tender_agent_telephone string comment ‘招标代理机构电话’,
bid_open_time string comment ‘投标截止时间’,
winning_bidder string comment ‘中标单位’,
winning_bid_amount string comment ‘中标金额’,
attachment_url string comment ‘附件网址’,
label string comment ‘标签’,
data_level string comment ‘数据等级’,
data_identification string comment ‘数据标识，TENDER 招标，BID 中标，PURCHASE 采购，PURCHASE_RESULT 采购结果’,
create_by string DEFAULT ‘system’ comment ‘创建人，默认system’,
create_time DATETIME DEFAULT current_timestamp comment ‘创建时间，默认表的写入时间，不存在更新，无更新时间’
)
ENGINE=OLAP
COMMENT ‘xxx’
DISTRIBUTED BY HASH(tender_no, purchasing_system,data_identification)
;
插入语句
SET enable_insert_strict = false;
set exec_mem_limit=20G;
truncate table ads_tender_bid_applet;
insert into ads_tender_bid_applet
select
row_number() over(order by t1.publish_time desc, t1.create_time desc) as sort_number
,t1.tender_no
,t1.title
,t1.content
,t1.publish_time
,t1.tenderee
,t2.prov_code as province_code
,t1.province
,t2.city_code as city_code
,t1.city
,t1.target_type
,t1.announcement_type
,t1.procurement_method
,t1.purchasing_system
,t1.url
,t1.tender_contact_person
,t1.tender_telephone
,t1.tender_agent
,t1.tender_agent_contact_person
,t1.tender_agent_telephone
,t1.bid_open_time
,t1.winning_bidder
,t1.winning_bid_amount
,t1.attachment_url
,concat_ws(’/’,t1.province,t1.city,t1.target_type,t1.announcement_type,t1.procurement_method) as label
,t1.data_level
,t1.data_identification
,t1.create_by
,t1.create_time
from (
select * from ads_tender_bid_applet_tmp1 where substr(publish_time,1,4) >= ‘2023’
union all
select * from ads_tender_bid_applet_tmp2 where substr(publish_time,1,4) >= ‘2023’
union all
select * from ads_tender_bid_applet_tmp3 where substr(publish_time,1,4) >= ‘2023’
union all
select * from ads_tender_bid_applet_tmp4 where substr(publish_time,1,4) >= ‘2023’
union all
select * from ads_tender_bid_applet_tmp5 where substr(publish_time,1,4) >= ‘2023’
union all
select * from ads_tender_bid_applet_tmp6 where substr(publish_time,1,4) >= ‘2023’
) t1
left join (
select prov_name,city_name,prov_code,city_code
from shengye_platform.uc_prov_city_reg
group by prov_name,city_name,prov_code,city_code
order by prov_name,city_name
) t2
on t1.province = t2.prov_name and t1.city = t2.city_name
;
【附件】
- fe.log/beINFO/相应截图
- 慢查询：
- Profile信息
- 并行度：show variables like ‘%parallel_fragment_exec_instance_num%’;
- pipeline是否开启：show variables like ‘%pipeline%’;
- be节点cpu和内存使用率截图
- 查询报错：
- query_dump，怎么获取query_dump文件
- be crash
- be.out
- 外表查询报错
- be.out和fe.warn.log
