insert into select 超时

【详述】不少insert into select 超时300s,必现。sql中的单独执行select耗时3s且数据量只有3条,改用insert into values()执行会很快,表的数据量只有60多G。过多的insert超时引发的其他问题:正常很快的qurey有时会很慢;个别只需1s的insert也会超时300s
【背景】做过哪些操作?
【业务影响】
【StarRocks版本】例如:2.3.3
【集群规模】例如:3fe(3 follower)+5be(fe与be分开部)
【机器信息】CPU虚拟核/内存/网卡,例如:fe:4C/16G,be:8C/32G
【附件】
审计日志统计


-Grafana监控情况

建表语句:

CREATE TABLE `entity_42_label_summary_temp` (
  `type` int(11) NULL ,
  `id` int(11) NOT NULL ,
  `update_time` datetime NOT NULL ,
  `value` varchar(128) NULL ,
  `source_dt` datetime NULL,
  `conf_score` decimal64(10, 2) NULL,
  `act_score` decimal64(10, 2) NULL ,
  `bitmap` bitmap BITMAP_UNION NOT NULL 
) ENGINE=OLAP 
AGGREGATE KEY(`type`, `id`, `update_time`, `value`, `source_dt`, `conf_score`, `act_score`)
PARTITION BY RANGE(`update_time`)
( PARTITION p20221207 VALUES [('2022-12-07 00:00:00'), ('2022-12-08 00:00:00')),
PARTITION p20221208 VALUES [('2022-12-08 00:00:00'), ('2022-12-09 00:00:00')),
PARTITION p20221209 VALUES [('2022-12-09 00:00:00'), ('2022-12-10 00:00:00')),
PARTITION p20221210 VALUES [('2022-12-10 00:00:00'), ('2022-12-11 00:00:00')))
DISTRIBUTED BY HASH(`id`, `value`, `type`) BUCKETS 20 
PROPERTIES (
"replication_num" = "3",
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "DAY",
"dynamic_partition.time_zone" = "Asia/Shanghai",
"dynamic_partition.start" = "-2147483648",
"dynamic_partition.end" = "2",
"dynamic_partition.prefix" = "p",
"dynamic_partition.buckets" = "20",
"in_memory" = "false",
"storage_format" = "DEFAULT",
"enable_persistent_index" = "false"
);

执行sql

    INSERT INTO
    	entity_42_label_summary_temp (id,update_time,value,bitmap,type) 
    WITH rule_hits_per_column AS (
    	SELECT
    		(
    		SELECT
    			bitmap_union(to_bitmap(seq_id)) bm
    		FROM
    			entity_42_seq_id_mapping ) AS rule_0_hits,
    		bitmap_andnot((bitmap_and(((
    		SELECT
    			ifnull(bitmap_union(seq_id_bitmap), bitmap_empty()) as seq_id_bitmap
    		FROM
    			entity_42_label_summary_temp
    		WHERE
    			label_id = 574
    			AND label_type = 1
    			AND update_time = '2022-12-04 02:19:33'
    			AND (label_value IN ( '低活跃', '高活跃' )))), ((
    		SELECT
    			ifnull(bitmap_union(seq_id_bitmap), bitmap_empty()) as seq_id_bitmap
    		FROM
    			entity_42_label_summary_temp
    		WHERE
    			label_id = 572
    			AND label_type = 1
    			AND update_time = '2022-12-04 02:18:34'
    			AND (label_value IN ('是')))))), (
    		SELECT
    			bitmap_union(seq_id_bitmap) AS seq_id_bitmap
    		FROM
    			(
    			SELECT
    				bitmap_union(seq_id_bitmap) AS seq_id_bitmap
    			FROM
    				entity_blacklist_summary
    			WHERE
    				(( blacklist_id = 3
    					AND entity_id = 42
    					AND update_time = '2022-12-04T03:16:33'
    					AND source_type = 'ENTITY'))
    		union all (
    			select
    				bitmap_union(seq_id_bitmap) as seq_id_bitmap
    			from
    				entity_blacklist_summary s,
    				(
    				select
    					blacklist_id,
    					max(update_time) as update_time,
    					source_type,
    					entity_id
    				from
    					entity_blacklist_summary
    				where
    					source_type = 'ONEID'
    					and entity_id = 42
    				group by
    					blacklist_id,
    					source_type,
    					entity_id ) r
    			where
    				s.blacklist_id = r.blacklist_id
    				and s.update_time = r.update_time
    				and s.source_type = r.source_type
    				and s.entity_id = r.entity_id )
    	union all (
    		select
    			bitmap_empty() as seq_id_bitmap) ) b)) AS rule_1_hits ,
    		bitmap_andnot((bitmap_and(((
    		SELECT
    			ifnull(bitmap_union(seq_id_bitmap), bitmap_empty()) as seq_id_bitmap
    		FROM
    			entity_42_label_summary_temp
    		WHERE
    			label_id = 574
    			AND label_type = 1
    			AND update_time = '2022-12-04 02:19:33'
    			AND (label_value IN ( '未知', '中活跃', '不活跃' )))), ((
    		SELECT
    			ifnull(bitmap_union(seq_id_bitmap), bitmap_empty()) as seq_id_bitmap
    		FROM
    			entity_42_label_summary_temp
    		WHERE
    			label_id = 572
    			AND label_type = 1
    			AND update_time = '2022-12-04 02:18:34'
    			AND (label_value IN ('否')))))), (
    		SELECT
    			bitmap_union(seq_id_bitmap) AS seq_id_bitmap
    		FROM
    			(
    			SELECT
    				bitmap_union(seq_id_bitmap) AS seq_id_bitmap
    			FROM
    				entity_blacklist_summary
    			WHERE
    				(( blacklist_id = 3
    					AND entity_id = 42
    					AND update_time = '2022-12-04T03:16:33'
    					AND source_type = 'ENTITY'))
    		union all (
    			select
    				bitmap_union(seq_id_bitmap) as seq_id_bitmap
    			from
    				entity_blacklist_summary s,
    				(
    				select
    					blacklist_id,
    					max(update_time) as update_time,
    					source_type,
    					entity_id
    				from
    					entity_blacklist_summary
    				where
    					source_type = 'ONEID'
    					and entity_id = 42
    				group by
    					blacklist_id,
    					source_type,
    					entity_id ) r
    			where
    				s.blacklist_id = r.blacklist_id
    				and s.update_time = r.update_time
    				and s.source_type = r.source_type
    				and s.entity_id = r.entity_id )
    	union all (
    		select
    			bitmap_empty() as seq_id_bitmap) ) b)) AS rule_2_hits ),
    	layer_hits_per_column AS (
    	SELECT
    		bitmap_and( rule_0_hits ,
    		rule_1_hits) AS layer_1_hits ,
    		bitmap_and( bitmap_andnot ( rule_0_hits ,
    		rule_1_hits ) ,
    		rule_2_hits) AS layer_2_hits ,
    		bitmap_andnot ( bitmap_andnot ( rule_0_hits ,
    		rule_1_hits ) ,
    		rule_2_hits ) AS all_layers_remain
    	FROM
    		rule_hits_per_column ),
    	layer_name_and_hits AS (
    	SELECT
    		'自定义分层1' AS label_value,
    		layer_1_hits AS seq_id_bitmap
    	FROM
    		layer_hits_per_column
    UNION ALL
    	SELECT
    		'自定义分层2' AS label_value,
    		layer_2_hits AS seq_id_bitmap
    	FROM
    		layer_hits_per_column
    UNION ALL
    	SELECT
    		NULL AS label_value,
    		all_layers_remain AS seq_id_bitmap
    	FROM
    		layer_hits_per_column )
    SELECT
    	89 AS id,
    	'2022-12-04 14:20:37' AS update_time,
    	value,
    	bitmap,
    	2
    FROM
    	layer_name_and_hits;

insert profile分析:
insert_profile.txt (1.9 MB)

方向就错了,sr支持并发入单表性能不行,他的优势是大批量数据少数入库请求,建议修改sql聚合所有需要入的数据定期单线程执行。

上面出现的sql目前不存并发问题,是新建的一张临时表,把数据都导入了进来。线上没有使用这张表,只自己测试用。

麻烦执行下select current_verison() 发下版本信息. 另外执行慢的时请求状态再描述下, 是否有insert 和 select语句并发执行,并发状况是什么.