agg sql 一直卡住

[fzh_starrocks]

【详述】TPCH Q1的变体运行一直卡住，跑不出来
【背景】导入 TPCH SF100 的数据
【业务影响】
【StarRocks版本】2.2
【集群规模】1fe+3be，分布在 4 台不同的机器上
【机器信息】CPU虚拟核/内存/网卡，例如：20C/164G/万兆
【附件】

• fe.warn.log/be.warn.log/相应截图
• 慢查询：
  • Profile信息
  • 并行度：20
  • cbo是否开启：show variables like ‘%cbo%’;
    mysql> show variables like ‘%cbo%’;
    ±------------------------------------±------+
    | Variable_name | Value |
    ±------------------------------------±------+
    | cbo_cte_reuse | false |
    | cbo_enable_dp_join_reorder | true |
    | cbo_enable_greedy_join_reorder | true |
    | cbo_enable_low_cardinality_optimize | true |
    | cbo_enable_replicated_join | true |
    | cbo_max_reorder_node_use_dp | 10 |
    | cbo_max_reorder_node_use_exhaustive | 4 |
    | cbo_use_correlated_join_estimate | true |
    ±------------------------------------±------+
    8 rows in set (0.06 sec)
  • be节点cpu和内存使用率截图

select l_suppkey, l_returnflag, sum(l_quantity) as sum_qty, sum(l_extendedprice) as sum_base_price, sum(l_extendedprice * (1 - l_discount)) as sum_disc_price, sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge, avg(l_quantity) as avg_qty, avg(l_extendedprice) as avg_price, avg(l_discount) as avg_disc, count(*) as count_order from lineitem where l_shipdate <= date_sub(‘1998-12-01’, interval 108 day) group by l_suppkey, l_returnflag;
ERROR 1064 (HY000): query timeout

[shemplle]

把 query_timeout 设置时间长点重跑下看下？

[fzh_starrocks]

@U_1652776274898_8390

select l_suppkey, l_returnflag, sum(l_quantity) as sum_qty, sum(l_extendedprice) as sum_base_price, sum(l_extendedprice * (1 - l_discount)) as sum_disc_price, sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge, avg(l_quantity) as avg_qty, avg(l_extendedprice) as avg_price, avg(l_discount) as avg_disc, count(*) as count_order from lineitem where l_shipdate <= date_sub(‘1998-12-01’, interval 108 day) group by l_suppkey, l_returnflag;

运行出来了，时间太慢了
3000000 rows in set (3 min 31.57 sec)

[shemplle]

parallel_fragment_exec_instance_num
把这个参数设置为cpu核数的一半重跑下看下

[fzh_starrocks]

已经是这个配置了，还是很慢。
对于高基数 agg，也是这种情况。

mysql> SELECT 0.2 * avg(l_quantity), sum(l_extendedprice) FROM lineitem GROUP BY l_partkey;
20000000 rows in set (7 min 39.98 sec)
只有前几秒，CPU用的很满，然后就没啥动静。

配置：
enable_pipeline_engine=false/true 都一样
parallel_fragment_exec_instance_num=20
pipeline_dop=20

[许秀不许秀]

profile 提供一下