【慢查询】物化视图的预聚合效果未达预期

💬 StarRocks 用户问答
#1

【详述】
似乎starrocks的物化视图的聚合是以tablet为粒度?
在对某个查询建立物化视图后,物化视图的结果集还是过大,SCAN的数据量和二次聚合依然会消耗大量时间,加速效果不明显 (对比人工导入的聚合物理表),在相关BI看板发送多个类似语句时,这些查询基本都会超时(300s)
【StarRocks版本】例如：1.19.1
【集群规模】例如：2fe（1 follower+1observer）+6be
【机器信息】40C/万兆
【附件】

#2

可以用这个SQL试试？

select /*+SET_VAR(new_planner_agg_stage=2)*/
product_code,
propertycode,
process_center_name,
p_platform_name,
dev_manager_name,
product_status_name,
distribution_available_stock,
purchase_qty_approve,
purchase_qty_unapprove,
shift_in_num,
unshift_num_approve,
weight,
labelname,
costprice,
product_name,
round(1.0 * SUM(sale_num_30), 6)
from
test
where
department_lv2 in ('电子平台')
group by
product_code,
propertycode,
process_center_name,
p_platform_name,
dev_manager_name,
product_status_name,
distribution_available_stock,
purchase_qty_approve,
purchase_qty_unapprove,
shift_in_num,
unshift_num_approve,
weight,
labelname,
costprice,
product_name
limit 30

另外，可以执行下面的sql看看有没有数据，没有的话可以analyze table test 一下

select * from table_statistic_v1 where table_name like "%test%";

或者也可以贴一下 explain costs + 你的sql的结果

#3

使用 /+SET_VAR(new_planner_agg_stage=2)/ 后慢了1倍

select * from table_statistic_v1 where table_name like “%test%”; 有数据

explain-cost.md (14.6 KB)

#4

使用 / +SET_VAR(new_planner_agg_stage=2) / 后的profile也方便贴一下么？
还有parallel_fragment_exec_instance_num=4可以调高一点，可以试试设置到parallel_fragment_exec_instance_num=16这些

#5

QueryProfile_stage2.txt (221.4 KB)

设置成16在仅进行单个查询时会有缩短查询时间数秒的提升,但是一个看板里有多个透视view会同时查询,总体性能还是会下降

#6

这个查询group by的列多，做聚合以及shuffle成本就很高。而且从profile里看，每台机器本地只有一半的聚合效果，所以/ +SET_VAR(new_planner_agg_stage=2) / 比不开还慢，应该还是建表的分桶列选得比较规律，每台机器上这堆group by的列，大部分都是一样的数据。
目前starrocks还不支持物化视图重新选分桶列，如果可以的话，可以尝试一下用其他列做分桶，或者只用其中一个列做分桶

#7

1.经过测试我发现物化视图聚合的粒度似乎是以bucket为单位做局部预聚合?当分区数越多或bucket数越多时,物化视图的rowCount变得非常的大
2.分桶列比较规律的意思是比较离散吗,当前的分桶字段是product_code, propertycode都是随机离散的值,我现在用department_lv2这个字段作为分桶列应该能保证聚合的数据在一个bucket中,但是因为是分区表,这些bucket还是会分布在各个BE里,似乎这个问题还是没办法解决
3.目前我在重做这张表,看看改变分桶列对性能影响

#8
  1. 是的，当前物化视图的分区分桶会和base表保持一致
  2. 是的，但是只是对于这个SQL，现在的分桶分桶导致数据分布得很规律，表现出来就是你打开 / +SET_VAR(new_planner_agg_stage=2) / 以后，本地的聚合效果只能聚合一半的数据，但是shuffle完聚合最终的结果才200条。也就说，在每个节点，这个SQL只有一半的数据是可以聚合的，但是在各个节点之间数据都是相同的。

不过我重新看了下profile，之前忽略了一个指标，你可以用/*+SET_VAR(new_planner_agg_stage=2, streaming_preaggregation_mode='force_preaggregation')*/ 这个试试

#9

1.更改分桶字段为department_lv2,局部预聚合效果会稍好(同一个分区下相同department_lv2值会在同个bucket中),但也会导致查询时触发bucket扫描更少,并发度下降使得查询性能下降
2.streaming_preaggregation_mode=‘force_preaggregation’ 会使得查询变慢,且内存溢出无法完成查询

京ICP备2022026421号-1