Starrocks 查询 3.1.15版本与最新版3.3.9性能对比

💬 StarRocks 用户问答性能相关

第一次看海 2025年01月15日 10:48 #1

为了更快的定位您的问题，请提供以下信息，谢谢
【详述】最近在做技术选型，在稳定版与最新版做ssb性能测试，
【背景】执行ssb测试使用的sql语句，并开启enable_profile 记录执行时间
【业务影响】测试发现3.3.9版本的ssb测试执行时间比3.1.15的时间长，两个不同版本安装环境一致
【是否存算分离】否
【StarRocks版本】3.1.15 与 3.3.9
【集群规模】例如：1fe+3be（fe与be混部）
【机器信息】CPU虚拟核/内存/网卡，18C/35G/虚拟机内部网络
【联系方式】dengxtabc@163.com
【附件】

Profile信息，如何获取profile
并行度：show variables like ‘%parallel_fragment_exec_instance_num%’;
show variables like ‘%pipeline_dop%’;
pipeline是否开启：show variables like ‘%pipeline%’;
±-------------------------------------------±------+
| Variable_name | Value |
±-------------------------------------------±------+
| enable_pipeline_engine | true |
| enable_pipeline_level_multi_partitioned_rf | false |
| max_pipeline_dop | 64 |
| pipeline_dop | 0 |
| pipeline_profile_level | 1 |
| pipeline_sink_dop | 0 |
±-------------------------------------------±------+
执行计划：explain costs + sql
be节点cpu和内存使用率截图

1赞

新版本3.3升级完查询性能不如2.5版本，同一个查询不同时长返回，结果也不一样

第一次看海 2025年01月15日 10:48 #2

±----------------------------------------------------------------------------------------------------------------------------+
| Explain String |
±----------------------------------------------------------------------------------------------------------------------------+
| PLAN COST |
| CPU: 2.2601538501917675E10 |
| Memory: 3837506.792822812 |
| |
| PLAN FRAGMENT 0(F08) |
| Output Exprs:51: sum | 22: d_year | 39: p_brand |
| Input Partition: UNPARTITIONED |
| RESULT SINK |
| |
| 19:MERGING-EXCHANGE |
| distribution type: GATHER |
| cardinality: 3945 |
| column statistics: |
| * d_year–>[1992.0, 1998.0, 0.0, 4.0, 7.0] ESTIMATE |
| * p_brand–>[-Infinity, Infinity, 0.0, 8.775096428571429, 1002.0] ESTIMATE |
| * sum–>[83082.0, 1.028495E7, 0.0, 8.0, 3945.375] ESTIMATE |
| |
| PLAN FRAGMENT 1(F07) |
| |
| Input Partition: HASH_PARTITIONED: 22: d_year, 39: p_brand |
| OutPut Partition: UNPARTITIONED |
| OutPut Exchange Id: 19 |
| |
| 18:SORT |
| | order by: [39, VARCHAR, false] ASC |
| | offset: 0 |
| | cardinality: 3945 |
| | column statistics: |
| | * d_year–>[1992.0, 1998.0, 0.0, 4.0, 7.0] ESTIMATE |
| | * p_brand–>[-Infinity, Infinity, 0.0, 8.775096428571429, 1002.0] ESTIMATE |
| | * sum–>[83082.0, 1.028495E7, 0.0, 8.0, 3945.375] ESTIMATE |
| | |
| 17:AGGREGATE (merge finalize) |
| | aggregate: sum[([51: sum, BIGINT, true]); args: INT; result: BIGINT; args nullable: true; result nullable: true] |
| | group by: [22: d_year, INT, false], [39: p_brand, VARCHAR, false] |
| | cardinality: 3945 |
| | column statistics: |
| | * d_year–>[1992.0, 1998.0, 0.0, 4.0, 7.0] ESTIMATE |
| | * p_brand–>[-Infinity, Infinity, 0.0, 8.775096428571429, 1002.0] ESTIMATE |
| | * sum–>[83082.0, 1.028495E7, 0.0, 8.0, 3945.375] ESTIMATE |
| | |
| 16:EXCHANGE |
| distribution type: SHUFFLE |
| partition exprs: [22: d_year, INT, false], [39: p_brand, VARCHAR, false] |
| cardinality: 3945 |
| |
| PLAN FRAGMENT 2(F00) |
| |
| Input Partition: RANDOM |
| OutPut Partition: HASH_PARTITIONED: 22: d_year, 39: p_brand |
| OutPut Exchange Id: 16 |
| |
| 15:AGGREGATE (update serialize) |
| | STREAMING |
| | aggregate: sum[([13: lo_revenue, INT, false]); args: INT; result: BIGINT; args nullable: false; result nullable: true] |
| | group by: [22: d_year, INT, false], [39: p_brand, VARCHAR, false] |
| | cardinality: 3945 |
| | column statistics: |
| | * d_year–>[1992.0, 1998.0, 0.0, 4.0, 7.0] ESTIMATE |
| | * p_brand–>[-Infinity, Infinity, 0.0, 8.775096428571429, 1002.0] ESTIMATE |
| | * sum–>[83082.0, 1.028495E7, 0.0, 8.0, 3945.375] ESTIMATE |
| | |
| 14:Project |
| | output columns: |
| | 13 <-> [13: lo_revenue, INT, false] |
| | 22 <-> [22: d_year, INT, false] |
| | 39 <-> [39: p_brand, VARCHAR, false] |
| | cardinality: 7025564 |
| | column statistics: |
| | * lo_revenue–>[83082.0, 1.028495E7, 0.0, 4.0, 4175844.0] ESTIMATE |
| | * d_year–>[1992.0, 1998.0, 0.0, 4.0, 7.0] ESTIMATE |
| | * p_brand–>[-Infinity, Infinity, 0.0, 8.775096428571429, 1002.0] ESTIMATE |
| | |
| 13:HASH JOIN |
| | join op: INNER JOIN (BROADCAST) |
| | equal join conjunct: [6: lo_orderdate, INT, false] = [18: d_datekey, INT, false] |
| | build runtime filters: |
| | - filter_id = 2, build_expr = (18: d_datekey), remote = false |
| | output columns: 13, 22, 39 |
| | can local shuffle: true |
| | cardinality: 7025564 |
| | column statistics: |
| | * lo_partkey–>[7.0, 1000000.0, 0.0, 4.0, 56000.0] ESTIMATE |
| | * lo_revenue–>[83082.0, 1.028495E7, 0.0, 4.0, 4175844.0] ESTIMATE |
| | * d_year–>[1992.0, 1998.0, 0.0, 4.0, 7.0] ESTIMATE |
| | * p_partkey–>[7.0, 1000000.0, 0.0, 4.0, 56000.0] ESTIMATE |
| | * p_brand–>[-Infinity, Infinity, 0.0, 8.775096428571429, 1002.0] ESTIMATE |
| | |
| |----12:EXCHANGE |
| | distribution type: BROADCAST |
| | cardinality: 2556 |
| | |
| 10:Project |
| | output columns: |
| | 6 <-> [6: lo_orderdate, INT, false] |
| | 13 <-> [13: lo_revenue, INT, false] |
| | 39 <-> [39: p_brand, VARCHAR, false] |
| | cardinality: 7036559 |
| | column statistics: |
| | * lo_orderdate–>[-2.147483648E9, 1.9990101E7, 0.0, 4.0, 2192.0] ESTIMATE |
| | * lo_revenue–>[83082.0, 1.028495E7, 0.0, 4.0, 4175844.0] ESTIMATE |
| | * p_brand–>[-Infinity, Infinity, 0.0, 8.775096428571429, 1002.0] ESTIMATE |
| | |
| 9:HASH JOIN |
| | join op: INNER JOIN (BROADCAST) |
| | equal join conjunct: [5: lo_suppkey, INT, false] = [44: s_suppkey, INT, false] |
| | build runtime filters: |
| | - filter_id = 1, build_expr = (44: s_suppkey), remote = false |
| | output columns: 6, 13, 39 |
| | can local shuffle: false |
| | cardinality: 7036559 |
| | column statistics: |
| | * lo_partkey–>[7.0, 1000000.0, 0.0, 4.0, 56000.0] ESTIMATE |
| | * lo_orderdate–>[-2.147483648E9, 1.9990101E7, 0.0, 4.0, 2192.0] ESTIMATE |
| | * lo_revenue–>[83082.0, 1.028495E7, 0.0, 4.0, 4175844.0] ESTIMATE |
| | * p_partkey–>[7.0, 1000000.0, 0.0, 4.0, 56000.0] ESTIMATE |
| | * p_brand–>[-Infinity, Infinity, 0.0, 8.775096428571429, 1002.0] ESTIMATE |
| | |
| |----8:EXCHANGE |
| | distribution type: BROADCAST |
| | cardinality: 40000 |
| | |
| 5:Project |
| | output columns: |
| | 5 <-> [5: lo_suppkey, INT, false] |
| | 6 <-> [6: lo_orderdate, INT, false] |
| | 13 <-> [13: lo_revenue, INT, false] |
| | 39 <-> [39: p_brand, VARCHAR, false] |
| | cardinality: 35320534 |
| | column statistics: |
| | * lo_suppkey–>[1.0, 199998.0, 0.0, 4.0, 200783.0] ESTIMATE |
| | * lo_orderdate–>[-2.147483648E9, 1.9990101E7, 0.0, 4.0, 2192.0] ESTIMATE |
| | * lo_revenue–>[83082.0, 1.028495E7, 0.0, 4.0, 4175844.0] ESTIMATE |
| | * p_brand–>[-Infinity, Infinity, 0.0, 8.775096428571429, 1002.0] ESTIMATE |
| | |
| 4:HASH JOIN |
| | join op: INNER JOIN (BROADCAST) |
| | equal join conjunct: [4: lo_partkey, INT, false] = [35: p_partkey, INT, false] |
| | build runtime filters: |
| | - filter_id = 0, build_expr = (35: p_partkey), remote = false |
| | output columns: 5, 6, 13, 39 |
| | can local shuffle: false |
| | cardinality: 35320534 |
| | column statistics: |
| | * lo_partkey–>[7.0, 1000000.0, 0.0, 4.0, 56000.0] ESTIMATE |
| | * lo_suppkey–>[1.0, 199998.0, 0.0, 4.0, 200783.0] ESTIMATE |
| | * lo_orderdate–>[-2.147483648E9, 1.9990101E7, 0.0, 4.0, 2192.0] ESTIMATE |
| | * lo_revenue–>[83082.0, 1.028495E7, 0.0, 4.0, 4175844.0] ESTIMATE |
| | * p_partkey–>[7.0, 1000000.0, 0.0, 4.0, 56000.0] ESTIMATE |
| | * p_brand–>[-Infinity, Infinity, 0.0, 8.775096428571429, 1002.0] ESTIMATE |
| | |
| |----3:EXCHANGE |
| | distribution type: BROADCAST |
| | cardinality: 56000 |
| | |
| 0:OlapScanNode |
| table: lineorder, rollup: lineorder |
| preAggregation: on |
| partitionsRatio=7/7, tabletsRatio=336/336 |
| tabletList=14036,14040,14044,14048,14052,14056,14060,14064,14068,14072 … |
| actualRows=636317704, avgRowSize=16.0 |
| cardinality: 636317704 |
| probe runtime filters: |
| - filter_id = 0, probe_expr = (4: lo_partkey) |
| - filter_id = 1, probe_expr = (5: lo_suppkey) |
| - filter_id = 2, probe_expr = (6: lo_orderdate) |
| column statistics: |
| * lo_partkey–>[7.0, 1000000.0, 0.0, 4.0, 1008869.0] ESTIMATE |
| * lo_suppkey–>[1.0, 199998.0, 0.0, 4.0, 200783.0] ESTIMATE |
| * lo_orderdate–>[-2.147483648E9, 1.9990101E7, 0.0, 4.0, 2192.0] ESTIMATE |
| * lo_revenue–>[83082.0, 1.028495E7, 0.0, 4.0, 4175844.0] ESTIMATE |
| |
| PLAN FRAGMENT 3(F05) |
| |
| Input Partition: RANDOM |
| OutPut Partition: UNPARTITIONED |
| OutPut Exchange Id: 12 |
| |
| 11:OlapScanNode |
| table: dates, rollup: dates |
| preAggregation: on |
| partitionsRatio=1/1, tabletsRatio=1/1 |
| tabletList=11862 |
| actualRows=2556, avgRowSize=8.0 |
| cardinality: 2556 |
| column statistics: |
| * d_datekey–>[1.9920101E7, 1.998123E7, 0.0, 4.0, 2560.0] ESTIMATE |
| * d_year–>[1992.0, 1998.0, 0.0, 4.0, 7.0] ESTIMATE |
| |
| PLAN FRAGMENT 4(F03) |
| |
| Input Partition: RANDOM |
| OutPut Partition: UNPARTITIONED |
| OutPut Exchange Id: 08 |
| |
| 7:Project |
| | output columns: |
| | 44 <-> [44: s_suppkey, INT, false] |
| | cardinality: 40000 |
| | column statistics: |
| | * s_suppkey–>[1.0, 200000.0, 0.0, 4.0, 40000.0] ESTIMATE |
| | |
| 6:OlapScanNode |
| table: supplier, rollup: supplier |
| preAggregation: on |
| Predicates: DictDecode(53: s_region, [ = ‘AMERICA’]) |
| dict_col=s_region |
| partitionsRatio=1/1, tabletsRatio=12/12 |
| tabletList=11787,11791,11795,11799,11803,11807,11811,11815,11819,11823 … |
| actualRows=200000, avgRowSize=10.801685 |
| cardinality: 40000 |
| column statistics: |
| * s_suppkey–>[1.0, 200000.0, 0.0, 4.0, 40000.0] ESTIMATE |
| * s_region–>[-Infinity, Infinity, 0.0, 6.801685, 5.0] ESTIMATE |
| |
| PLAN FRAGMENT 5(F01) |
| |
| Input Partition: RANDOM |
| OutPut Partition: UNPARTITIONED |
| OutPut Exchange Id: 03 |
| |
| 2:Project |
| | output columns: |
| | 35 <-> [35: p_partkey, INT, false] |
| | 39 <-> [39: p_brand, VARCHAR, false] |
| | cardinality: 56000 |
| | column statistics: |
| | * p_partkey–>[1.0, 1400000.0, 0.0, 4.0, 56000.0] ESTIMATE |
| | * p_brand–>[-Infinity, Infinity, 0.0, 8.775096428571429, 1002.0] ESTIMATE |
| | |
| 1:OlapScanNode |
| table: part, rollup: part |
| preAggregation: on |
| Predicates: DictDecode(52: p_category, [ = ‘MFGR#12’]) |
| dict_col=p_category |
| partitionsRatio=1/1, tabletsRatio=12/12 |
| tabletList=11736,11740,11744,11748,11752,11756,11760,11764,11768,11772 … |
| actualRows=1400000, avgRowSize=19.775097 |
| cardinality: 56000 |
| column statistics: |
| * p_partkey–>[1.0, 1400000.0, 0.0, 4.0, 56000.0] ESTIMATE |
| * p_category–>[-Infinity, Infinity, 0.0, 7.0, 25.0] ESTIMATE |
| * p_brand–>[-Infinity, Infinity, 0.0, 8.775096428571429, 1002.0] ESTIMATE |
±----------------------------------------------------------------------------------------------------------------------------+

第一次看海 2025年01月15日 11:19 #4

企业微信截图_17369399389888

第一次看海 2025年01月15日 11:20 #5

测试两个版本，记录10次执行sql的执行时间，和平均时间，两个版本都是默认安装，没有优化操作

persistperson 2025年01月20日 01:29 #6

难用的一匹，我的insert时间都翻了好几倍，也不知道最近两个小版本是谁优化的，反向优化了。

wangrui 2025年01月21日 07:02 #7

您好，这个性能退化我没有复现出来，我看您发的 plan 中 lineorder表的基数是 cardinality: 636317704，这个是不对的，正确应该是 600037902，您可以先对所有表收集统计信息，再试一下。如果还有退化，麻烦找几个退化明显的SQL，提供一下2个版本的profile，例如 q04，q11

mark 2025年01月21日 10:11 #8

第一次看海 2025年01月22日 09:03 #9

好的，年后发哦，明天就放假了

京ICP备2022026421号-1