【详述】一个查询，导致be全挂
【背景】无
【业务影响】 导致服务不可用
【StarRocks版本】2.3.4
【集群规模】3fe（3 follower）+7be
【机器信息】CPU虚拟核/内存/网卡，：56C/256G/万兆
【附件】
- fe.log/beINFO/相应截图
查询sql
：SELECT level_first,count(1),sum(gmv) FROM furion.consume_member_section_label_wide_test a ,furion.tag_user_bitmap_test_1 b
WHERE enddate=‘2022-12-12’ AND consume_type=‘集团新客首消项目’ AND tag_id=1111
AND bitmap_contains(b.users,mapid)
GROUP BY level_first
建表语句1：
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| consume_member_section_label_wide_test | CREATE TABLE consume_member_section_label_wide_test (
mapid bigint(20) NOT NULL COMMENT “mapid”,
enddate date NOT NULL COMMENT “截止日期”,
consume_type varchar(65533) NOT NULL COMMENT “消费类型”,
product_name varchar(65533) NULL COMMENT “项目名称”,
orders bigint(20) NULL COMMENT “订单数”,
revenue_before decimal64(18, 2) NULL COMMENT “促前营收”,
revenue_after decimal64(18, 2) NULL COMMENT “促后营收”,
gmv decimal64(18, 2) NULL COMMENT “GMV”,
consume_diff int(11) NULL COMMENT “消费间隔”,
coupon_percent decimal64(18, 4) NULL COMMENT “营收占比”,
level_first varchar(65533) NULL COMMENT “一级分类”,
consume_diff_label varchar(65533) NULL COMMENT “消费间隔标签”,
coupon_percent_label varchar(65533) NULL COMMENT “促销占比标签”,
resident_city_name varchar(65533) NULL COMMENT “常驻城市”,
flow_date_diff int(11) NULL COMMENT “访问流失间隔”,
flow_date_diff_label varchar(65533) NULL COMMENT “访问流失标签”,
marketing_counts int(11) NULL COMMENT “营销频次”,
marketing_counts_label varchar(65533) NULL COMMENT “营销频次标签”,
INDEX bi_consume_type (consume_type) USING BITMAP COMMENT ‘’,
INDEX bi_product_name (product_name) USING BITMAP COMMENT ‘’,
INDEX bi_consume_diff_label (consume_diff_label) USING BITMAP COMMENT ‘’,
INDEX bi_level_first (level_first) USING BITMAP COMMENT ‘’,
INDEX bi_coupon_percent_label (coupon_percent_label) USING BITMAP COMMENT ‘’,
INDEX bi_resident_city_name (resident_city_name) USING BITMAP COMMENT ‘’,
INDEX bi_flow_date_diff_label (flow_date_diff_label) USING BITMAP COMMENT ‘’,
INDEX bi_marketing_counts_label (marketing_counts_label) USING BITMAP COMMENT ‘’
) ENGINE=OLAP
PRIMARY KEY(mapid, enddate, consume_type)
COMMENT “OLAP”
PARTITION BY RANGE(enddate)
(PARTITION p20221209 VALUES [(‘2022-12-09’), (‘2022-12-10’)),
PARTITION p20221210 VALUES [(‘2022-12-10’), (‘2022-12-11’)),
PARTITION p20221211 VALUES [(‘2022-12-11’), (‘2022-12-12’)),
PARTITION p20221212 VALUES [(‘2022-12-12’), (‘2022-12-13’)),
PARTITION p20221213 VALUES [(‘2022-12-13’), (‘2022-12-14’)),
PARTITION p20221214 VALUES [(‘2022-12-14’), (‘2022-12-15’)),
PARTITION p20221215 VALUES [(‘2022-12-15’), (‘2022-12-16’)),
PARTITION p20221216 VALUES [(‘2022-12-16’), (‘2022-12-17’)),
PARTITION p20221217 VALUES [(‘2022-12-17’), (‘2022-12-18’)),
PARTITION p20221218 VALUES [(‘2022-12-18’), (‘2022-12-19’)))
DISTRIBUTED BY HASH(mapid) BUCKETS 10
PROPERTIES (
“replication_num” = “3”,
“colocate_with” = “group_furion_comsume_member_section”,
“dynamic_partition.enable” = “true”,
“dynamic_partition.time_unit” = “DAY”,
“dynamic_partition.time_zone” = “Asia/Shanghai”,
“dynamic_partition.start” = “-365”,
“dynamic_partition.end” = “3”,
“dynamic_partition.prefix” = “p”,
“dynamic_partition.buckets” = “10”,
“in_memory” = “false”,
“storage_format” = “DEFAULT”,
“enable_persistent_index” = “false”
);
建表语句2：
| tag_user_bitmap_test_1 | CREATE TABLE tag_user_bitmap_test_1 (
tag_id int(11) NULL COMMENT “”,
input_date date NULL COMMENT “”,
tag_value bigint(20) NULL COMMENT “”,
users bitmap BITMAP_UNION NOT NULL COMMENT “访问用户id”
) ENGINE=OLAP
AGGREGATE KEY(tag_id, input_date, tag_value)
COMMENT “OLAP”
PARTITION BY RANGE(input_date)
(PARTITION p20221209 VALUES [(‘2022-12-09’), (‘2022-12-10’)),
PARTITION p20221210 VALUES [(‘2022-12-10’), (‘2022-12-11’)),
PARTITION p20221211 VALUES [(‘2022-12-11’), (‘2022-12-12’)),
PARTITION p20221212 VALUES [(‘2022-12-12’), (‘2022-12-13’)),
PARTITION p20221213 VALUES [(‘2022-12-13’), (‘2022-12-14’)),
PARTITION p20221214 VALUES [(‘2022-12-14’), (‘2022-12-15’)),
PARTITION p20221215 VALUES [(‘2022-12-15’), (‘2022-12-16’)),
PARTITION p20221216 VALUES [(‘2022-12-16’), (‘2022-12-17’)),
PARTITION p20221217 VALUES [(‘2022-12-17’), (‘2022-12-18’)),
PARTITION p20221218 VALUES [(‘2022-12-18’), (‘2022-12-19’)))
DISTRIBUTED BY HASH(tag_id) BUCKETS 10
PROPERTIES (
“replication_num” = “3”,
“dynamic_partition.enable” = “true”,
“dynamic_partition.time_unit” = “DAY”,
“dynamic_partition.time_zone” = “Asia/Shanghai”,
“dynamic_partition.start” = “-365”,
“dynamic_partition.end” = “3”,
“dynamic_partition.prefix” = “p”,
“dynamic_partition.buckets” = “10”,
“in_memory” = “false”,
“storage_format” = “DEFAULT”,
“enable_persistent_index” = “false”
);
:supervise_thread()