开启spill=force, 再查询两张表union 还是报内存不足, 加工逻辑很简单

💬 StarRocks 用户问答 功能使用相关
#1

为了更快的定位您的问题，请提供以下信息，谢谢
【详述】问题详细描述

CREATE TABLE `ods_api_jstqm_sale_order_info_f_include_archive` (
  `__id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT "__id",
  `is_cod` varchar(1048576) NULL COMMENT "是否货到付款",
  `l_id` varchar(1048576) NULL COMMENT "快递单号",
  `send_date` varchar(1048576) NULL COMMENT "发货日期",
  `pay_date` varchar(1048576) NULL COMMENT "支付时间",
  `freight` varchar(1048576) NULL COMMENT "运费",
  `receiver_address` varchar(1048576) NULL COMMENT "收货地址",
  `receiver_district` varchar(1048576) NULL COMMENT "区",
  `wms_co_id` varchar(1048576) NULL COMMENT "发货仓编号",
  `logistics_company` varchar(1048576) NULL COMMENT "快递公司",
  `as_id` varchar(1048576) NULL COMMENT "补发换货单对应的售后单号",
  `free_amount` varchar(1048576) NULL COMMENT "抵扣金额",
  `shop_name` varchar(1048576) NULL COMMENT "店铺名称",
  `question_type` varchar(1048576) NULL COMMENT "问题类型",
  `outer_pay_id` varchar(1048576) NULL COMMENT "外部支付单号",
  `so_id` varchar(1048576) NULL COMMENT "线上订单号",
  `type` varchar(1048576) NULL COMMENT "订单类型",
  `order_from` varchar(1048576) NULL COMMENT "订单来源",
  `status` varchar(1048576) NULL COMMENT "聚水潭订单状态",
  `pay_amount` varchar(1048576) NULL COMMENT "应付金额",
  `shop_buyer_id` varchar(1048576) NULL COMMENT "买家昵称",
  `open_id` varchar(1048576) NULL COMMENT "平台买家唯一值",
  `shop_status` varchar(1048576) NULL COMMENT "平台订单状态",
  `receiver_mobile` varchar(1048576) NULL COMMENT "手机",
  `receiver_phone` varchar(1048576) NULL COMMENT "电话",
  `order_date` varchar(1048576) NULL COMMENT "订单日期",
  `question_desc` varchar(1048576) NULL COMMENT "问题描述",
  `receiver_city` varchar(1048576) NULL COMMENT "收件信息-市",
  `receiver_state` varchar(1048576) NULL COMMENT "收件信息-省",
  `receiver_name` varchar(1048576) NULL COMMENT "收件信息-收件人",
  `o_id` varchar(1048576) NULL COMMENT "ERP内部订单号",
  `shop_id` varchar(1048576) NULL COMMENT "店铺编号",
  `co_id` varchar(1048576) NULL COMMENT "公司编号",
  `remark` varchar(1048576) NULL COMMENT "订单备注",
  `drp_co_id_from` varchar(1048576) NULL COMMENT "分销商编号",
  `modified` varchar(1048576) NULL COMMENT "修改时间",
  `labels` varchar(1048576) NULL COMMENT "多标签",
  `paid_amount` varchar(1048576) NULL COMMENT "实际支付金额",
  `currency` varchar(1048576) NULL COMMENT "币种",
  `buyer_message` varchar(1048576) NULL COMMENT "买家留言",
  `lc_id` varchar(1048576) NULL COMMENT "物流公司编码",
  `invoice_title` varchar(1048576) NULL COMMENT "发票抬头",
  `invoice_type` varchar(1048576) NULL COMMENT "发票类型",
  `buyer_tax_no` varchar(1048576) NULL COMMENT "发票税号",
  `creator_name` varchar(1048576) NULL COMMENT "订单业务员",
  `plan_delivery_date` varchar(1048576) NULL COMMENT "计划发货时间",
  `node` varchar(1048576) NULL COMMENT "线下备注",
  `receiver_town` varchar(1048576) NULL COMMENT "收件信息-街道",
  `drp_co_id_to` varchar(1048576) NULL COMMENT "供销商编号",
  `shop_site` varchar(1048576) NULL COMMENT "店铺站点信息",
  `un_lid` varchar(1048576) NULL COMMENT "国际物流单号",
  `end_time` varchar(1048576) NULL COMMENT "确认收货时间",
  `receiver_country` varchar(1048576) NULL COMMENT "国家代码",
  `receiver_zip` varchar(1048576) NULL COMMENT "邮编",
  `seller_flag` varchar(1048576) NULL COMMENT "旗帜",
  `receiver_email` varchar(1048576) NULL COMMENT "收货邮箱",
  `referrer_id` varchar(1048576) NULL COMMENT "主播id",
  `referrer_name` varchar(1048576) NULL COMMENT "主播名称",
  `created` varchar(1048576) NULL COMMENT "订单创建时间",
  `pays` varchar(1048576) NULL COMMENT "支付信息",
  `items` varchar(1048576) NULL COMMENT "商品信息",
  `skus` varchar(1048576) NULL COMMENT "商品",
  `f_weight` varchar(1048576) NULL COMMENT "实称重量",
  `weight` varchar(1048576) NULL COMMENT "重量",
  `ts` varchar(1048576) NULL COMMENT "数据库行版本号",
  `buyer_id` varchar(1048576) NULL COMMENT "买家ID",
  `buyer_paid_amount` varchar(1048576) NULL COMMENT "买家实付",
  `seller_income_amount` varchar(1048576) NULL COMMENT "卖家实收",
  `chosen_channel` varchar(1048576) NULL COMMENT "实发快递渠道",
  `link_o_id` varchar(1048576) NULL COMMENT "被合并被拆分的订单内部单号",
  `merge_so_id` varchar(1048576) NULL COMMENT "合并线上订单号",
  `shipment` varchar(1048576) NULL COMMENT "买家指定物流",
  `sign_time` varchar(1048576) NULL COMMENT "预计送达时间",
  `cb_finances` varchar(1048576) NULL COMMENT "跨境线下订单财务数据",
  `outer_so_id` varchar(1048576) NULL COMMENT "outer_so_id",
  `f_freight` varchar(1048576) NULL COMMENT "f_freight",
  `glasses` varchar(1048576) NULL COMMENT "glasses",
  `oaid` varchar(1048576) NULL COMMENT "淘系订单oaid",
  `origin_data` varchar(1048576) NULL COMMENT "原始数据",
  `dt` varchar(1048576) NULL COMMENT "日分区",
  `yuce_cube_shop_id` varchar(1048576) NULL COMMENT "魔方店铺标识"
) ENGINE=OLAP 
PRIMARY KEY(`__id`)
COMMENT "ODS_销售订单信息表含归档(聚水潭奇门API)"
DISTRIBUTED BY HASH(`__id`)
PROPERTIES (
"compression" = "LZ4",
"enable_persistent_index" = "true",
"fast_schema_evolution" = "true",
"replicated_storage" = "true",
"replication_num" = "1"
);

CREATE TABLE `ods_api_jstqm_sale_order_info_f` (
  `__id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT "__id",
  `is_cod` varchar(1048576) NULL COMMENT "是否货到付款",
  `l_id` varchar(1048576) NULL COMMENT "快递单号",
  `send_date` varchar(1048576) NULL COMMENT "发货日期",
  `pay_date` varchar(1048576) NULL COMMENT "支付时间",
  `freight` varchar(1048576) NULL COMMENT "运费",
  `receiver_address` varchar(1048576) NULL COMMENT "收货地址",
  `receiver_district` varchar(1048576) NULL COMMENT "区",
  `wms_co_id` varchar(1048576) NULL COMMENT "发货仓编号",
  `logistics_company` varchar(1048576) NULL COMMENT "快递公司",
  `as_id` varchar(1048576) NULL COMMENT "补发换货单对应的售后单号",
  `free_amount` varchar(1048576) NULL COMMENT "抵扣金额",
  `shop_name` varchar(1048576) NULL COMMENT "店铺名称",
  `question_type` varchar(1048576) NULL COMMENT "问题类型",
  `outer_pay_id` varchar(1048576) NULL COMMENT "外部支付单号",
  `so_id` varchar(1048576) NULL COMMENT "线上订单号",
  `type` varchar(1048576) NULL COMMENT "订单类型",
  `order_from` varchar(1048576) NULL COMMENT "订单来源",
  `status` varchar(1048576) NULL COMMENT "聚水潭订单状态",
  `pay_amount` varchar(1048576) NULL COMMENT "应付金额",
  `shop_buyer_id` varchar(1048576) NULL COMMENT "买家昵称",
  `open_id` varchar(1048576) NULL COMMENT "平台买家唯一值",
  `shop_status` varchar(1048576) NULL COMMENT "平台订单状态",
  `receiver_mobile` varchar(1048576) NULL COMMENT "手机",
  `receiver_phone` varchar(1048576) NULL COMMENT "电话",
  `order_date` varchar(1048576) NULL COMMENT "订单日期",
  `question_desc` varchar(1048576) NULL COMMENT "问题描述",
  `receiver_city` varchar(1048576) NULL COMMENT "收件信息-市",
  `receiver_state` varchar(1048576) NULL COMMENT "收件信息-省",
  `receiver_name` varchar(1048576) NULL COMMENT "收件信息-收件人",
  `o_id` varchar(1048576) NULL COMMENT "ERP内部订单号",
  `shop_id` varchar(1048576) NULL COMMENT "店铺编号",
  `co_id` varchar(1048576) NULL COMMENT "公司编号",
  `remark` varchar(1048576) NULL COMMENT "订单备注",
  `drp_co_id_from` varchar(1048576) NULL COMMENT "分销商编号",
  `modified` varchar(1048576) NULL COMMENT "修改时间",
  `labels` varchar(1048576) NULL COMMENT "多标签",
  `paid_amount` varchar(1048576) NULL COMMENT "实际支付金额",
  `currency` varchar(1048576) NULL COMMENT "币种",
  `buyer_message` varchar(1048576) NULL COMMENT "买家留言",
  `lc_id` varchar(1048576) NULL COMMENT "物流公司编码",
  `invoice_title` varchar(1048576) NULL COMMENT "发票抬头",
  `invoice_type` varchar(1048576) NULL COMMENT "发票类型",
  `buyer_tax_no` varchar(1048576) NULL COMMENT "发票税号",
  `creator_name` varchar(1048576) NULL COMMENT "订单业务员",
  `plan_delivery_date` varchar(1048576) NULL COMMENT "计划发货时间",
  `node` varchar(1048576) NULL COMMENT "线下备注",
  `receiver_town` varchar(1048576) NULL COMMENT "收件信息-街道",
  `drp_co_id_to` varchar(1048576) NULL COMMENT "供销商编号",
  `shop_site` varchar(1048576) NULL COMMENT "店铺站点信息",
  `un_lid` varchar(1048576) NULL COMMENT "国际物流单号",
  `end_time` varchar(1048576) NULL COMMENT "确认收货时间",
  `receiver_country` varchar(1048576) NULL COMMENT "国家代码",
  `receiver_zip` varchar(1048576) NULL COMMENT "邮编",
  `seller_flag` varchar(1048576) NULL COMMENT "旗帜",
  `receiver_email` varchar(1048576) NULL COMMENT "收货邮箱",
  `referrer_id` varchar(1048576) NULL COMMENT "主播id",
  `referrer_name` varchar(1048576) NULL COMMENT "主播名称",
  `created` varchar(1048576) NULL COMMENT "订单创建时间",
  `pays` varchar(1048576) NULL COMMENT "支付信息",
  `items` varchar(1048576) NULL COMMENT "商品信息",
  `skus` varchar(1048576) NULL COMMENT "商品",
  `f_weight` varchar(1048576) NULL COMMENT "实称重量",
  `weight` varchar(1048576) NULL COMMENT "重量",
  `ts` varchar(1048576) NULL COMMENT "数据库行版本号",
  `buyer_id` varchar(1048576) NULL COMMENT "买家ID",
  `buyer_paid_amount` varchar(1048576) NULL COMMENT "买家实付",
  `seller_income_amount` varchar(1048576) NULL COMMENT "卖家实收",
  `chosen_channel` varchar(1048576) NULL COMMENT "实发快递渠道",
  `link_o_id` varchar(1048576) NULL COMMENT "被合并被拆分的订单内部单号",
  `merge_so_id` varchar(1048576) NULL COMMENT "合并线上订单号",
  `shipment` varchar(1048576) NULL COMMENT "买家指定物流",
  `sign_time` varchar(1048576) NULL COMMENT "预计送达时间",
  `cb_finances` varchar(1048576) NULL COMMENT "跨境线下订单财务数据",
  `outer_so_id` varchar(1048576) NULL COMMENT "outer_so_id",
  `f_freight` varchar(1048576) NULL COMMENT "f_freight",
  `glasses` varchar(1048576) NULL COMMENT "glasses",
  `oaid` varchar(1048576) NULL COMMENT "淘系订单oaid",
  `origin_data` varchar(1048576) NULL COMMENT "原始数据",
  `dt` varchar(1048576) NULL COMMENT "日分区",
  `yuce_cube_shop_id` varchar(1048576) NULL COMMENT "魔方店铺标识"
) ENGINE=OLAP 
PRIMARY KEY(`__id`)
COMMENT "ODS_销售订单信息表(聚水潭奇门API)"
DISTRIBUTED BY HASH(`__id`)
PROPERTIES (
"compression" = "LZ4",
"enable_persistent_index" = "true",
"fast_schema_evolution" = "true",
"replicated_storage" = "true",
"replication_num" = "1"
); 
CREATE TABLE `ods_api_jstqm_archive_sale_order_info_f` (
  `__id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT "__id",
  `is_cod` varchar(1048576) NULL COMMENT "是否货到付款",
  `l_id` varchar(1048576) NULL COMMENT "快递单号",
  `send_date` varchar(1048576) NULL COMMENT "发货日期",
  `pay_date` varchar(1048576) NULL COMMENT "支付时间",
  `freight` varchar(1048576) NULL COMMENT "运费",
  `receiver_address` varchar(1048576) NULL COMMENT "收货地址",
  `receiver_district` varchar(1048576) NULL COMMENT "区",
  `wms_co_id` varchar(1048576) NULL COMMENT "发货仓编号",
  `logistics_company` varchar(1048576) NULL COMMENT "快递公司",
  `as_id` varchar(1048576) NULL COMMENT "补发换货单对应的售后单号",
  `free_amount` varchar(1048576) NULL COMMENT "抵扣金额",
  `shop_name` varchar(1048576) NULL COMMENT "店铺名称",
  `question_type` varchar(1048576) NULL COMMENT "问题类型",
  `outer_pay_id` varchar(1048576) NULL COMMENT "外部支付单号",
  `so_id` varchar(1048576) NULL COMMENT "线上订单号",
  `type` varchar(1048576) NULL COMMENT "订单类型",
  `order_from` varchar(1048576) NULL COMMENT "订单来源",
  `status` varchar(1048576) NULL COMMENT "聚水潭订单状态",
  `pay_amount` varchar(1048576) NULL COMMENT "应付金额",
  `shop_buyer_id` varchar(1048576) NULL COMMENT "买家昵称",
  `open_id` varchar(1048576) NULL COMMENT "平台买家唯一值",
  `shop_status` varchar(1048576) NULL COMMENT "平台订单状态",
  `receiver_mobile` varchar(1048576) NULL COMMENT "手机",
  `receiver_phone` varchar(1048576) NULL COMMENT "电话",
  `order_date` varchar(1048576) NULL COMMENT "订单日期",
  `question_desc` varchar(1048576) NULL COMMENT "问题描述",
  `receiver_city` varchar(1048576) NULL COMMENT "收件信息-市",
  `receiver_state` varchar(1048576) NULL COMMENT "收件信息-省",
  `receiver_name` varchar(1048576) NULL COMMENT "收件信息-收件人",
  `o_id` varchar(1048576) NULL COMMENT "ERP内部订单号",
  `shop_id` varchar(1048576) NULL COMMENT "店铺编号",
  `co_id` varchar(1048576) NULL COMMENT "公司编号",
  `remark` varchar(1048576) NULL COMMENT "订单备注",
  `drp_co_id_from` varchar(1048576) NULL COMMENT "分销商编号",
  `modified` varchar(1048576) NULL COMMENT "修改时间",
  `labels` varchar(1048576) NULL COMMENT "多标签",
  `paid_amount` varchar(1048576) NULL COMMENT "实际支付金额",
  `currency` varchar(1048576) NULL COMMENT "币种",
  `buyer_message` varchar(1048576) NULL COMMENT "买家留言",
  `lc_id` varchar(1048576) NULL COMMENT "物流公司编码",
  `invoice_title` varchar(1048576) NULL COMMENT "发票抬头",
  `invoice_type` varchar(1048576) NULL COMMENT "发票类型",
  `buyer_tax_no` varchar(1048576) NULL COMMENT "发票税号",
  `creator_name` varchar(1048576) NULL COMMENT "订单业务员",
  `plan_delivery_date` varchar(1048576) NULL COMMENT "计划发货时间",
  `node` varchar(1048576) NULL COMMENT "线下备注",
  `receiver_town` varchar(1048576) NULL COMMENT "收件信息-街道",
  `drp_co_id_to` varchar(1048576) NULL COMMENT "供销商编号",
  `shop_site` varchar(1048576) NULL COMMENT "店铺站点信息",
  `un_lid` varchar(1048576) NULL COMMENT "国际物流单号",
  `end_time` varchar(1048576) NULL COMMENT "确认收货时间",
  `receiver_country` varchar(1048576) NULL COMMENT "国家代码",
  `receiver_zip` varchar(1048576) NULL COMMENT "邮编",
  `seller_flag` varchar(1048576) NULL COMMENT "旗帜",
  `receiver_email` varchar(1048576) NULL COMMENT "收货邮箱",
  `referrer_id` varchar(1048576) NULL COMMENT "主播id",
  `referrer_name` varchar(1048576) NULL COMMENT "主播名称",
  `created` varchar(1048576) NULL COMMENT "订单创建时间",
  `pays` varchar(1048576) NULL COMMENT "支付信息",
  `items` varchar(1048576) NULL COMMENT "商品信息",
  `skus` varchar(1048576) NULL COMMENT "商品",
  `f_weight` varchar(1048576) NULL COMMENT "实称重量",
  `weight` varchar(1048576) NULL COMMENT "重量",
  `ts` varchar(1048576) NULL COMMENT "数据库行版本号",
  `buyer_id` varchar(1048576) NULL COMMENT "买家ID",
  `buyer_paid_amount` varchar(1048576) NULL COMMENT "买家实付",
  `seller_income_amount` varchar(1048576) NULL COMMENT "卖家实收",
  `chosen_channel` varchar(1048576) NULL COMMENT "实发快递渠道",
  `link_o_id` varchar(1048576) NULL COMMENT "被合并被拆分的订单内部单号",
  `merge_so_id` varchar(1048576) NULL COMMENT "合并线上订单号",
  `shipment` varchar(1048576) NULL COMMENT "买家指定物流",
  `sign_time` varchar(1048576) NULL COMMENT "预计送达时间",
  `cb_finances` varchar(1048576) NULL COMMENT "跨境线下订单财务数据",
  `outer_so_id` varchar(1048576) NULL COMMENT "outer_so_id",
  `f_freight` varchar(1048576) NULL COMMENT "f_freight",
  `glasses` varchar(1048576) NULL COMMENT "glasses",
  `oaid` varchar(1048576) NULL COMMENT "淘系订单oaid",
  `origin_data` varchar(1048576) NULL COMMENT "原始数据",
  `dt` varchar(1048576) NULL COMMENT "日分区",
  `yuce_cube_shop_id` varchar(1048576) NULL COMMENT "魔方店铺标识"
) ENGINE=OLAP 
PRIMARY KEY(`__id`)
COMMENT "ODS_销售订单明细归档信息表(聚水潭奇门API)"
DISTRIBUTED BY HASH(`__id`)
PROPERTIES (
"compression" = "LZ4",
"enable_persistent_index" = "true",
"fast_schema_evolution" = "true",
"replicated_storage" = "true",
"replication_num" = "1"
);

insert overwrite ods_api_jstqm_sale_order_info_f_include_archive
select is_cod,l_id,send_date,pay_date,freight,receiver_address,receiver_district,wms_co_id,logistics_company,as_id,free_amount,shop_name,question_type,outer_pay_id,so_id,type,order_from,status,pay_amount,shop_buyer_id,open_id,shop_status,receiver_mobile,receiver_phone,order_date,question_desc,receiver_city,receiver_state,receiver_name,o_id,shop_id,co_id,remark,drp_co_id_from,modified,labels,paid_amount,currency,buyer_message,lc_id,invoice_title,invoice_type,buyer_tax_no,creator_name,plan_delivery_date,node,receiver_town,drp_co_id_to,shop_site,un_lid,end_time,receiver_country,receiver_zip,seller_flag,receiver_email,referrer_id,referrer_name,created,pays,items,skus,f_weight,weight,ts,buyer_id,buyer_paid_amount,seller_income_amount,chosen_channel,link_o_id,merge_so_id,shipment,sign_time,cb_finances,outer_so_id,f_freight,glasses,oaid,origin_data,dt,yuce_cube_shop_id from ods_api_jstqm_sale_order_info_f
union 
select is_cod,l_id,send_date,pay_date,freight,receiver_address,receiver_district,wms_co_id,logistics_company,as_id,free_amount,shop_name,question_type,outer_pay_id,so_id,type,order_from,status,pay_amount,shop_buyer_id,open_id,shop_status,receiver_mobile,receiver_phone,order_date,question_desc,receiver_city,receiver_state,receiver_name,o_id,shop_id,co_id,remark,drp_co_id_from,modified,labels,paid_amount,currency,buyer_message,lc_id,invoice_title,invoice_type,buyer_tax_no,creator_name,plan_delivery_date,node,receiver_town,drp_co_id_to,shop_site,un_lid,end_time,receiver_country,receiver_zip,seller_flag,receiver_email,referrer_id,referrer_name,created,pays,items,skus,f_weight,weight,ts,buyer_id,buyer_paid_amount,seller_income_amount,chosen_channel,link_o_id,merge_so_id,shipment,sign_time,cb_finances,outer_so_id,f_freight,glasses,oaid,origin_data,dt,yuce_cube_shop_id from ods_api_jstqm_archive_sale_order_info_f

mysql> select count(*) from ods_api_jstqm_sale_order_info_f;
+----------+
| count(*) |
+----------+
|  5114766 |
+----------+

mysql> select count(*) from ods_api_jstqm_archive_sale_order_info_f;
+----------+
| count(*) |
+----------+
|  1690505 |
+----------+

两张表数据量也不大, 只跑一个sql没有并发, 报错:
Used: 27229086104, Limit: 25684239974. Mem usage has exceed the limit of the resource group [load_resource_group]. You can change the limit by modifying [mem_limit] of this group

【背景】做过哪些操作？
【业务影响】
【是否存算分离】
【StarRocks版本】例如：3.3.0-rc1
【集群规模】例如：3fe（3 follower）+3be（fe与be混部）
【机器信息】CPU虚拟核/内存/网卡，16C/64G/万兆
【联系方式】为了在解决问题过程中能及时联系到您获取一些日志信息，请补充下您的联系方式，例如：社区群15-可乐鸡或者邮箱，谢谢
【附件】

  • fe.log/beINFO/相应截图
  • 慢查询：
    • Profile信息
      profile.txt (107.2 KB)
    • 并行度：show variables like ‘%parallel_fragment_exec_instance_num%’;
      | parallel_fragment_exec_instance_num | 1 |
    • pipeline是否开启：show variables like ‘%pipeline%’;
      | enable_pipeline_engine | true |
      | enable_pipeline_level_multi_partitioned_rf | false |
      | max_pipeline_dop | 64 |
      | pipeline_dop | 0 |
      | pipeline_profile_level | 1 |
      | pipeline_sink_dop | 0 |
    • be节点cpu和内存使用率截图
  • 查询报错：
  • be crash
    • be.out
  • 外表查询报错
    • be.out和fe.warn.log
#2

set pipeline_dop=4; 调低点

京ICP备2022026421号-1