多表关联join 导致be节点内存占用异常

struggle1121 · 2023年08月24日 02:39

为了更快的定位您的问题，请提供以下信息，谢谢
【详述】
三张表的关联操作，没有做物化视图，三张表的数据量分别是：1.5亿，3千万，50万。场景是先基于三张表做一些聚合，然后基于聚合结果做关联查询，由于关联的key比较多（11个），所以在聚合时候将关联的key做了concat做到生成一个新的字段row_code，后续基于row_code字段做关联条件，结果一直是 Memory of process exceed limit，在调整query_mem_limit 和 exec_mem_limit 参数到40G，仍然是相同的结果。

相同的场景相同的查询在单节点clickhouse中是没有问题的，并且观察clickhouse服务器的内存占用也没有到40G

后续尝试将row_code的逻辑去掉，即在关联查询时显示使用多个key，这种情况下相同的查询时没有问题的。

– row_code 的逻辑
MD5(ifnull(app_id,’’)||ifnull(time_zone,’’)||ifnull(os_id,’’)||ifnull(channel_id,’’)||ifnull(country_id_active,’’)||ifnull(app_version_active,’’)||ifnull(plat_id,’’)||ifnull(campaign_id,’’)||ifnull(material_id,’’)||ifnull(app_id_site,’’)||cast(active_date as String)) as row_code
– 基于 row_code关联：
from a
left join b
on a.row_code=b.row_code
left join c
on a.row_code=b.row_code;
– 多key的关联
from a
left join b
on ifnull(a.app_id,’’)=ifnull(b.app_id,’’)
and ifnull(a.time_zone,’’)=ifnull(b.time_zone,’’)
and ifnull(a.os_id,’’)=ifnull(b.os_id,’’)
and ifnull(a.channel_id,’’)=ifnull(b.channel_id,’’)
and ifnull(a.country_id,’’)=ifnull(b.country_id,’’)
and ifnull(a.app_version,’’)=ifnull(b.app_version,’’)
and ifnull(a.plat_id,’’)=ifnull(b.plat_id,’’)
and ifnull(a.campaign_id,’’)=ifnull(b.campaign_id,’’)
and ifnull(a.material_id, ‘’)=ifnull(b.material_id,’’)
and ifnull(a.app_id_site,’’)=ifnull(b.app_id_site,’’)
and a.date_time=b.date_time
left join c
on ifnull(a.app_id,’’)=ifnull(c.app_id,’’)
and ifnull(a.time_zone,’’)=ifnull(c.time_zone,’’)
and ifnull(a.os_id,’’)=ifnull(c.os_id,’’)
and ifnull(a.channel_id,’’)=ifnull(c.channel_id,’’)
and ifnull(a.country_id,’’)=ifnull(c.country_id,’’)
and ifnull(a.app_version,’’)=ifnull(c.app_version,’’)
and ifnull(a.plat_id,’’)=ifnull(c.plat_id,’’)
and ifnull(a.campaign_id,’’)=ifnull(c.campaign_id,’’)
and ifnull(a.material_id, ‘’)=ifnull(c.material_id,’’)
and ifnull(a.app_id_site,’’)=ifnull(c.app_id_site,’’)
and a.date_time=c.date_time

【背景】调整过单个查询的内存限制到40G
【业务影响】
【StarRocks版本】最初是3.0.4，之后又升级到了最新版本 3.1.1 ，两个版本的表现是一致的
【集群规模】3fe（1 follower+2observer）+3be（fe与be混部）
【机器信息】CPU虚拟核/内存/网卡，例如：8C/64G/千兆
【联系方式】社区群7-STRUGGLE
【附件】

Profile信息：由于任务没有执行成功，所以没有获取到
并行度：1
pipeline是否开启：是
执行SQL：
with a as(
select
app_id,
time_zone,
os_id,
channel_id,
country_id_active as country_id,
app_version_active as app_version,
plat_id,
campaign_id,
material_id,
app_id_site,
active_date as date_time,
md5(ifnull(app_id,’’)||ifnull(time_zone,’’)||ifnull(os_id,’’)||ifnull(channel_id,’’)||ifnull(country_id_active,’’)||ifnull(app_version_active,’’)||ifnull(plat_id,’’)||ifnull(campaign_id,’’)||ifnull(material_id,’’)||ifnull(app_id_site,’’)||cast(active_date as String)) as row_code,
count(distinct user_id) as day_active_user,
count(distinct (case when user_id_is_pay=true then user_id else null end)) as day_pay_active_user,
sum(start_up) as day_sessions,
sum(online_time) as day_sessionlength,
sum(case when user_id_is_pay=true then start_up else 0 end) as day_pay_sessions,
sum(case when user_id_is_pay=true then online_time else 0 end) as day_pay_sessionlength,
sum(case when install_date=active_date then start_up else 0 end) as day_new_sessions,
sum(case when install_date=active_date then online_time else 0 end) as day_new_sessionlength
from data_basic.basic_active_increase_new_hour
where active_date between ‘2023-06-01’ and ‘2023-06-20’
group by app_id,
time_zone,
os_id,
channel_id,
country_id_active ,
app_version_active,
plat_id,
campaign_id,
material_id,
app_id_site,
active_date
),
b as(
select
app_id,
time_zone,
os_id,
channel_id,
country_id_active as country_id,
app_version_active as app_version,
plat_id,
campaign_id,
material_id,
app_id_site,
active_date as date_time,
md5(ifnull(app_id,’’)||ifnull(time_zone,’’)||ifnull(os_id,’’)||ifnull(channel_id,’’)||ifnull(country_id_active,’’)||ifnull(app_version_active,’’)||ifnull(plat_id,’’)||ifnull(campaign_id,’’)||ifnull(material_id,’’)||ifnull(app_id_site,’’)||cast(active_date as String)) as row_code,
sum(adn_impression) as day_active_adn_impression,
sum(adn_income) as day_active_adn_income,
sum(case when position_type = ‘4’ then adn_impression else 0 end) as day_active_adn_reward_impression,
count(distinct case when position_type = ‘4’ and adn_impression > 0 then user_id else null end) as day_active_adn_reward_user,
sum(case when position_type = ‘0’ then adn_impression else 0 end) as day_active_adn_inter_impression,
count(distinct case when position_type = ‘0’ and adn_impression > 0 then user_id else null end) as day_active_adn_inter_user
from data_basic.basic_adn_increase_new_hour
where active_date between ‘2023-06-01’ and ‘2023-06-20’ and adn_impression>0
group by app_id,
time_zone,
os_id,
channel_id,
country_id_active ,
app_version_active ,
plat_id,
campaign_id,
material_id,
app_id_site,
active_date
),
c as(
select
app_id,
time_zone,
os_id,
channel_id,
country_id_active as country_id,
app_version_active as app_version,
plat_id,
campaign_id,
material_id,
app_id_site,
active_date as date_time,
md5(ifnull(app_id,’’)||ifnull(time_zone,’’)||ifnull(os_id,’’)||ifnull(channel_id,’’)||ifnull(country_id_active,’’)||ifnull(app_version_active,’’)||ifnull(plat_id,’’)||ifnull(campaign_id,’’)||ifnull(material_id,’’)||ifnull(app_id_site,’’)||cast(active_date as String)) as row_code,
sum(pay_times) as day_active_pay_times,
sum(pay_income) as day_active_pay_income,
count(distinct user_id) as day_active_pay_user,
count(distinct (case when active_date=first_pay_date then user_id else null end)) as day_pay_new_user
from data_basic.basic_pay_increase_new_hour
where active_date between ‘2023-06-01’ and ‘2023-06-20’
group by app_id,
time_zone,
os_id,
channel_id,
country_id_active ,
app_version_active ,
plat_id,
campaign_id,
material_id,
app_id_site,
active_date
)
select
a.date_time,
a.time_zone,
a.app_id,
a.os_id,
a.channel_id,
a.country_id,
a.app_version,
a.plat_id,
null as acount_id,
a.campaign_id,
null as ad_id,
a.material_id,
a.app_id_site,
a.day_active_user,
ifnull(c.day_active_pay_user,0) as day_active_pay_user,
ifnull(c.day_active_pay_times,0) as day_active_pay_times,
ifnull(c.day_active_pay_income,0) as day_active_pay_income,
ifnull(b.day_active_adn_income,0) as day_active_adn_income,
ifnull(b.day_active_adn_impression,0) as day_active_adn_impression,
ifnull(c.day_pay_new_user,0) as day_pay_new_user,
ifnull(a.day_pay_active_user,0) as day_pay_active_user,
a.day_sessions,
a.day_sessionlength,
a.day_pay_sessions,
a.day_pay_sessionlength,
a.day_new_sessions,
a.day_new_sessionlength,
ifnull(b.day_active_adn_reward_impression,0) as day_active_adn_reward_impression,
ifnull(b.day_active_adn_reward_user,0) as day_active_adn_reward_user,
ifnull(b.day_active_adn_inter_impression,0) as day_active_adn_inter_impression,
ifnull(b.day_active_adn_inter_user,0) as day_active_adn_inter_user

from a
left join b
on a.row_code=b.row_code
left join c
on a.row_code=b.row_code;

执行计划：
PLAN FRAGMENT 0
OUTPUT EXPRS:4: active_date | 2: time_zone | 1: app_id | 6: os_id | 7: channel_id | 10: country_id_active | 12: app_version_active | 13: plat_id | 110: expr | 14: campaign_id | 110: expr | 15: material_id | 16: app_id_site | 28: count | 111: ifnull | 112: ifnull | 113: ifnull | 114: ifnull | 115: ifnull | 116: ifnull | 117: ifnull | 30: sum | 31: sum | 32: sum | 33: sum | 34: sum | 35: sum | 118: ifnull | 119: ifnull | 120: ifnull | 121: ifnull
PARTITION: UNPARTITIONED

RESULT SINK

24:EXCHANGE

PLAN FRAGMENT 1
OUTPUT EXPRS:
PARTITION: HASH_PARTITIONED: 1: app_id, 2: time_zone, 6: os_id, 7: channel_id, 10: country_id_active, 12: app_version_active, 13: plat_id, 14: campaign_id, 15: material_id, 16: app_id_site, 4: active_date

STREAM DATA SINK
EXCHANGE ID: 24
UNPARTITIONED

PLAN FRAGMENT 2
OUTPUT EXPRS:
PARTITION: HASH_PARTITIONED: 37: app_id, 38: time_zone, 42: os_id, 43: channel_id, 46: country_id_active, 48: app_version_active, 49: plat_id, 50: campaign_id, 51: material_id, 52: app_id_site, 41: active_date

STREAM DATA SINK
EXCHANGE ID: 21
UNPARTITIONED

20:Project
| <slot 71> : 71: sum
| <slot 72> : 72: sum
| <slot 73> : 73: sum
| <slot 74> : 74: count
| <slot 75> : 75: sum
| <slot 76> : 76: count
| <slot 77> : md5(CAST(((((((((((CAST(ifnull(37: app_id, ‘’) AS BOOLEAN)) OR (CAST(ifnull(38: time_zone, ‘’) AS BOOLEAN))) OR (CAST(ifnull(42: os_id, ‘’) AS BOOLEAN))) OR (CAST(ifnull(43: channel_id, ‘’) AS BOOLEAN))) OR (CAST(ifnull(46: country_id_active, ‘’) AS BOOLEAN))) OR (CAST(ifnull(48: app_version_active, ‘’) AS BOOLEAN))) OR (CAST(ifnull(49: plat_id, ‘’) AS BOOLEAN))) OR (CAST(ifnull(50: campaign_id, ‘’) AS BOOLEAN))) OR (CAST(ifnull(51: material_id, ‘’) AS BOOLEAN))) OR (CAST(ifnull(52: app_id_site, ‘’) AS BOOLEAN))) OR (CAST(41: active_date AS BOOLEAN)) AS VARCHAR))
|
19:AGGREGATE (merge finalize)
| output: sum(71: sum), sum(72: sum), sum(73: sum), multi_distinct_count(74: count), sum(75: sum), multi_distinct_count(76: count)
| group by: 37: app_id, 38: time_zone, 42: os_id, 43: channel_id, 46: country_id_active, 48: app_version_active, 49: plat_id, 50: campaign_id, 51: material_id, 52: app_id_site, 41: active_date
|
18:EXCHANGE

PLAN FRAGMENT 3
OUTPUT EXPRS:
PARTITION: RANDOM

STREAM DATA SINK
EXCHANGE ID: 18
HASH_PARTITIONED: 37: app_id, 38: time_zone, 42: os_id, 43: channel_id, 46: country_id_active, 48: app_version_active, 49: plat_id, 50: campaign_id, 51: material_id, 52: app_id_site, 41: active_date

17:AGGREGATE (update serialize)
| STREAMING
| output: sum(61: adn_impression), sum(62: adn_income), sum(67: case), multi_distinct_count(68: case), sum(69: case), multi_distinct_count(70: case)
| group by: 37: app_id, 38: time_zone, 42: os_id, 43: channel_id, 46: country_id_active, 48: app_version_active, 49: plat_id, 50: campaign_id, 51: material_id, 52: app_id_site, 41: active_date
|
16:Project
| <slot 37> : 37: app_id
| <slot 38> : 38: time_zone
| <slot 41> : 41: active_date
| <slot 42> : 42: os_id
| <slot 43> : 43: channel_id
| <slot 46> : 46: country_id_active
| <slot 48> : 48: app_version_active
| <slot 49> : 49: plat_id
| <slot 50> : 50: campaign_id
| <slot 51> : 51: material_id
| <slot 52> : 52: app_id_site
| <slot 61> : 61: adn_impression
| <slot 62> : 62: adn_income
| <slot 67> : if(124: expr, 61: adn_impression, 0)
| <slot 68> : if((124: expr) AND (126: expr), 40: user_id, NULL)
| <slot 69> : if(125: expr, 61: adn_impression, 0)
| <slot 70> : if((125: expr) AND (126: expr), 40: user_id, NULL)
| common expressions:
| <slot 124> : 56: position_type = ‘4’
| <slot 125> : 56: position_type = ‘0’
| <slot 126> : 61: adn_impression > 0
|
15:OlapScanNode
TABLE: basic_adn_increase_new_hour
PREAGGREGATION: ON
PREDICATES: 41: active_date >= ‘2023-06-01’, 41: active_date <= ‘2023-06-20’, 61: adn_impression > 0
partitions=20/82
rollup: basic_adn_increase_new_hour
tabletRatio=20/20
tabletList=15240,15243,15247,15376,15252,15514,15258,15264,15270,15273 …
cardinality=1
avgRowSize=170.34755
numNodes=0

PLAN FRAGMENT 4
OUTPUT EXPRS:
PARTITION: HASH_PARTITIONED: 78: app_id, 79: time_zone, 83: os_id, 84: channel_id, 87: country_id_active, 89: app_version_active, 90: plat_id, 91: campaign_id, 92: material_id, 93: app_id_site, 82: active_date

STREAM DATA SINK
EXCHANGE ID: 12
UNPARTITIONED

11:Project
| <slot 104> : 104: sum
| <slot 105> : 105: sum
| <slot 106> : 106: count
| <slot 107> : 107: count
| <slot 108> : md5(CAST(((((((((((CAST(ifnull(78: app_id, ‘’) AS BOOLEAN)) OR (CAST(ifnull(79: time_zone, ‘’) AS BOOLEAN))) OR (CAST(ifnull(83: os_id, ‘’) AS BOOLEAN))) OR (CAST(ifnull(84: channel_id, ‘’) AS BOOLEAN))) OR (CAST(ifnull(87: country_id_active, ‘’) AS BOOLEAN))) OR (CAST(ifnull(89: app_version_active, ‘’) AS BOOLEAN))) OR (CAST(ifnull(90: plat_id, ‘’) AS BOOLEAN))) OR (CAST(ifnull(91: campaign_id, ‘’) AS BOOLEAN))) OR (CAST(ifnull(92: material_id, ‘’) AS BOOLEAN))) OR (CAST(ifnull(93: app_id_site, ‘’) AS BOOLEAN))) OR (CAST(82: active_date AS BOOLEAN)) AS VARCHAR))
|
10:AGGREGATE (merge finalize)
| output: sum(104: sum), sum(105: sum), multi_distinct_count(106: count), multi_distinct_count(107: count)
| group by: 78: app_id, 79: time_zone, 83: os_id, 84: channel_id, 87: country_id_active, 89: app_version_active, 90: plat_id, 91: campaign_id, 92: material_id, 93: app_id_site, 82: active_date
|
9:EXCHANGE

PLAN FRAGMENT 5
OUTPUT EXPRS:
PARTITION: RANDOM

STREAM DATA SINK
EXCHANGE ID: 09
HASH_PARTITIONED: 78: app_id, 79: time_zone, 83: os_id, 84: channel_id, 87: country_id_active, 89: app_version_active, 90: plat_id, 91: campaign_id, 92: material_id, 93: app_id_site, 82: active_date

8:AGGREGATE (update serialize)
| STREAMING
| output: sum(97: pay_times), sum(98: pay_income), multi_distinct_count(81: user_id), multi_distinct_count(103: case)
| group by: 78: app_id, 79: time_zone, 83: os_id, 84: channel_id, 87: country_id_active, 89: app_version_active, 90: plat_id, 91: campaign_id, 92: material_id, 93: app_id_site, 82: active_date
|
7:Project
| <slot 78> : 78: app_id
| <slot 79> : 79: time_zone
| <slot 81> : 81: user_id
| <slot 82> : 82: active_date
| <slot 83> : 83: os_id
| <slot 84> : 84: channel_id
| <slot 87> : 87: country_id_active
| <slot 89> : 89: app_version_active
| <slot 90> : 90: plat_id
| <slot 91> : 91: campaign_id
| <slot 92> : 92: material_id
| <slot 93> : 93: app_id_site
| <slot 97> : 97: pay_times
| <slot 98> : 98: pay_income
| <slot 103> : if(82: active_date = 102: first_pay_date, 81: user_id, NULL)
|
6:OlapScanNode
TABLE: basic_pay_increase_new_hour
PREAGGREGATION: ON
PREDICATES: 82: active_date >= ‘2023-06-01’, 82: active_date <= ‘2023-06-20’
partitions=20/51
rollup: basic_pay_increase_new_hour
tabletRatio=20/20
tabletList=15146,15149,15152,15155,15158,15161,15164,15167,15170,15173 …
cardinality=134590
avgRowSize=136.64626
numNodes=0

PLAN FRAGMENT 6
OUTPUT EXPRS:
PARTITION: RANDOM

STREAM DATA SINK
EXCHANGE ID: 03
HASH_PARTITIONED: 1: app_id, 2: time_zone, 6: os_id, 7: channel_id, 10: country_id_active, 12: app_version_active, 13: plat_id, 14: campaign_id, 15: material_id, 16: app_id_site, 4: active_date

2:AGGREGATE (update serialize)
| STREAMING
| output: sum(25: case), sum(26: case), sum(27: case), multi_distinct_count(5: user_id), multi_distinct_count(23: case), sum(19: start_up), sum(18: online_time), sum(24: case)
| group by: 1: app_id, 2: time_zone, 6: os_id, 7: channel_id, 10: country_id_active, 12: app_version_active, 13: plat_id, 14: campaign_id, 15: material_id, 16: app_id_site, 4: active_date
|
1:Project
| <slot 1> : 1: app_id
| <slot 2> : 2: time_zone
| <slot 4> : 4: active_date
| <slot 5> : 5: user_id
| <slot 6> : 6: os_id
| <slot 7> : 7: channel_id
| <slot 10> : 10: country_id_active
| <slot 12> : 12: app_version_active
| <slot 13> : 13: plat_id
| <slot 14> : 14: campaign_id
| <slot 15> : 15: material_id
| <slot 16> : 16: app_id_site
| <slot 18> : 18: online_time
| <slot 19> : 19: start_up
| <slot 23> : if(122: expr, 5: user_id, NULL)
| <slot 24> : if(122: expr, 19: start_up, 0)
| <slot 25> : if(122: expr, 18: online_time, 0)
| <slot 26> : if(123: expr, 19: start_up, 0)
| <slot 27> : if(123: expr, 18: online_time, 0)
| common expressions:
| <slot 122> : 17: user_id_is_pay = TRUE
| <slot 123> : 3: install_date = 4: active_date
|
0:OlapScanNode
TABLE: basic_active_increase_new_hour
PREAGGREGATION: ON
PREDICATES: 4: active_date >= ‘2023-06-01’, 4: active_date <= ‘2023-06-20’
partitions=20/62
rollup: basic_active_increase_new_hour
tabletRatio=20/20
tabletList=16067,15879,16009,15945,15955,15891,15965,16031,15840,15905 …
cardinality=7475220
avgRowSize=145.08133
numNodes=0

许秀不许秀 · 2023年09月8日 08:54

可以提供下profile