【详述】不少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)