桶数设置为6，12后，性能还比不上桶为10

Question

环境是：6个be，默认表的桶是10，唯一模型表，唯一键为gcjlwyid
分析发现有倾斜的情况，因此设置为6和12，并导入相同的数据量。但性能还比不上10个桶
语句为：

SELECT count(*) FROM (
SELECT DISTINCT gcjlwyid,gcsj,kkdwbh,jdchphm,hpzldm,hpysdm,clpp,clysdm,jdccllxdm,aqdzt,gbbm,yjtpdz,gcsj_int FROM dwd_gj_kkgc_rt_6 WHERE gcsj>='2025-06-21 00:00:00' AND gcsj<='2025-06-21 23:59:59') a;

SELECT count(*) FROM (
SELECT DISTINCT gcjlwyid,gcsj,kkdwbh,jdchphm,hpzldm,hpysdm,clpp,clysdm,jdccllxdm,aqdzt,gbbm,yjtpdz,gcsj_int FROM dwd_gj_kkgc_rt_10 WHERE gcsj>='2025-06-21 00:00:00' AND gcsj<='2025-06-21 23:59:59') a;

桶数为10的计划

Pipeline  :  3(instance_num=6):
                      LOCAL_EXCHANGE_SINK_OPERATOR  (PASSTHROUGH)  (id=-4):
                            -  CloseTime:  avg  0ns,  max  0ns,  min  0ns
                            -  ExecTime:  avg  15.693ms,  max  21.539ms,  min  9.194ms
                            -  InitTime:  avg  1.525us,  max  1.580us,  min  1.380us
                            -  InputRows:  sum  100.078818M  (100078818),  avg  16.679803M  (16679803),  max  20.021079M  (20021079),  min  10.00452M  (10004520)
                            -  MemoryUsage:  sum  ,  avg  ,  max  ,  min
                                -  PeakMemoryUsage:  sum  0.00  ,  avg  0.00  ,  max  0.00  ,  min  0.00
                            -  OpenTime:  avg  1.3us,  max  1.450us,  min  530ns
                            -  WaitForDependency[LOCAL_EXCHANGE_SINK_DEPENDENCY]Time:  avg  10s119ms,  max  13s235ms,  min  6s450ms
                          OLAP_SCAN_OPERATOR  (id=0.  table  name  =  dwd_gj_kkgc_rt_10(dwd_gj_kkgc_rt_10)):
                                -  BlocksProduced:  sum  24.677K  (24677),  avg  4.112K  (4112),  max  4.937K  (4937),  min  2.467K  (2467)
                                -  CloseTime:  avg  434.823us,  max  610.20us,  min  258.940us
                                -  ExecTime:  avg  174.862ms,  max  220.838ms,  min  136.477ms
                                -  InitTime:  avg  118.141us,  max  155.930us,  min  89.530us
                                -  MemoryUsage:  sum  ,  avg  ,  max  ,  min
                                    -  PeakMemoryUsage:  sum  0.00  ,  avg  0.00  ,  max  0.00  ,  min  0.00
                                -  OpenTime:  avg  1.109ms,  max  2.419ms,  min  570.10us
                                -  ProjectionTime:  avg  0ns,  max  0ns,  min  0ns
                                -  RowsProduced:  sum  100.078818M  (100078818),  avg  16.679803M  (16679803),  max  20.021079M  (20021079),  min  10.00452M  (10004520)
                                -  RuntimeFilterInfo:  sum  ,  avg  ,  max  ,  min
                                -  WaitForDependency[OLAP_SCAN_OPERATOR_DEPENDENCY]Time:  avg  135.700ms,  max  193.234ms,  min  92.39ms
                              VScanner:
                                    -  MemoryUsage:  sum  ,  avg  ,  max  ,  min
                                        -  FreeBlocks:  sum  0.00  ,  avg  0.00  ,  max  0.00  ,  min  0.00

桶数为6的计划

Pipeline  :  3(instance_num=6):
                      LOCAL_EXCHANGE_SINK_OPERATOR  (PASSTHROUGH)  (id=-4):
                            -  CloseTime:  avg  0ns,  max  0ns,  min  0ns
                            -  ExecTime:  avg  14.319ms,  max  19.212ms,  min  11.35ms
                            -  InitTime:  avg  1.598us,  max  1.930us,  min  1.330us
                            -  InputRows:  sum  100.078818M  (100078818),  avg  16.679803M  (16679803),  max  16.685957M  (16685957),  min  16.672224M  (16672224)
                            -  MemoryUsage:  sum  ,  avg  ,  max  ,  min
                                -  PeakMemoryUsage:  sum  0.00  ,  avg  0.00  ,  max  0.00  ,  min  0.00
                            -  OpenTime:  avg  945ns,  max  1.510us,  min  410ns
                            -  WaitForDependency[LOCAL_EXCHANGE_SINK_DEPENDENCY]Time:  avg  32s622ms,  max  36s79ms,  min  28s314ms
                          OLAP_SCAN_OPERATOR  (id=0.  table  name  =  dwd_gj_kkgc_rt_6(dwd_gj_kkgc_rt_6)):
                                -  BlocksProduced:  sum  24.67K  (24670),  avg  4.111K  (4111),  max  4.113K  (4113),  min  4.11K  (4110)
                                -  CloseTime:  avg  396.728us,  max  520.210us,  min  348.330us
                                -  ExecTime:  avg  138.184ms,  max  156.765ms,  min  121.676ms
                                -  InitTime:  avg  125.310us,  max  172.910us,  min  107.450us
                                -  MemoryUsage:  sum  ,  avg  ,  max  ,  min
                                    -  PeakMemoryUsage:  sum  0.00  ,  avg  0.00  ,  max  0.00  ,  min  0.00
                                -  OpenTime:  avg  715.28us,  max  1.79ms,  min  515.460us
                                -  ProjectionTime:  avg  0ns,  max  0ns,  min  0ns
                                -  RowsProduced:  sum  100.078818M  (100078818),  avg  16.679803M  (16679803),  max  16.685957M  (16685957),  min  16.672224M  (16672224)
                                -  RuntimeFilterInfo:  sum  ,  avg  ,  max  ,  min
                                -  WaitForDependency[OLAP_SCAN_OPERATOR_DEPENDENCY]Time:  avg  101.838ms,  max  116.826ms,  min  86.756ms
                              VScanner:
                                    -  MemoryUsage:  sum  ,  avg  ,  max  ,  min
                                        -  FreeBlocks:  sum  0.00  ,  avg  0.00  ,  max  0.00  ,  min  0.00

帮忙看看是什么原因引起的

cosion · Answer

12个tong桶的情况：

12个桶情况：
                         DISTINCT_STREAMING_AGGREGATION_OPERATOR  (id=1):
                                -  PlanInfo
                                      -  group  by:  gcjlwyid,  gcsj,  kkdwbh,  jdchphm,  hpzldm,  hpysdm,  clpp,  clysdm,  jdccllxdm,  aqdzt,  gbbm,  yjtpdz,  gcsj_int
                                      -  cardinality=100,078,818
                                      -  projections:  1
                                      -  project  output  tuple  id:  3
                                -  BlocksProduced:  sum  24.658K  (24658),  avg  684,  max  4.106K  (4106),  min  1
                                -  CloseTime:  avg  8.946us,  max  69.30us,  min  410ns
                                -  ExecTime:  avg  3s94ms,  max  18s533ms,  min  15.940us
                                -  InitTime:  avg  6.135us,  max  11.630us,  min  3.590us
                                -  MemoryUsage:  sum  ,  avg  ,  max  ,  min
                                    -  PeakMemoryUsage:  sum  0.00  ,  avg  0.00  ,  max  0.00  ,  min  0.00
                                -  OpenTime:  avg  47.16us,  max  139.570us,  min  8.980us
                                -  ProjectionTime:  avg  18.794ms,  max  113.22ms,  min  40ns
                                -  RowsProduced:  sum  100.078818M  (100078818),  avg  2.779967M  (2779967),  max  16.685957M  (16685957),  min  0
                              LOCAL_EXCHANGE_OPERATOR  (BUCKET_HASH_SHUFFLE)  (id=-3):
                                    -  BlocksProduced:  sum  24.616K  (24616),  avg  683,  max  4.104K  (4104),  min  0
                                    -  CloseTime:  avg  0ns,  max  0ns,  min  0ns
                                    -  ExecTime:  avg  554.507ms,  max  3s524ms,  min  1.440us
                                    -  GetBlockFailedTime:  sum  177,  avg  4,  max  86,  min  0
                                    -  InitTime:  avg  1.439us,  max  2.770us,  min  1.10us
                                    -  MemoryUsage:  sum  ,  avg  ,  max  ,  min
                                        -  PeakMemoryUsage:  sum  1.16  GB,  avg  32.91  MB,  max  139.88  MB,  min  0.00
                                    -  OpenTime:  avg  0ns,  max  0ns,  min  0ns
                                    -  ProjectionTime:  avg  0ns,  max  0ns,  min  0ns
                                    -  RowsProduced:  sum  100.078818M  (100078818),  avg  2.779967M  (2779967),  max  16.685957M  (16685957),  min  0

10个桶的情况

DISTINCT_STREAMING_AGGREGATION_OPERATOR  (id=1):
                                -  BlocksProduced:  sum  24.256K  (24256),  avg  808,  max  1.619K  (1619),  min  1
                                -  CloseTime:  avg  16.913us,  max  52.250us,  min  630ns
                                -  ExecTime:  avg  4s96ms,  max  9s267ms,  min  16.50us
                                -  InitTime:  avg  5.767us,  max  10.580us,  min  3.570us
                                -  MemoryUsage:  sum  ,  avg  ,  max  ,  min
                                    -  PeakMemoryUsage:  sum  0.00  ,  avg  0.00  ,  max  0.00  ,  min  0.00
                                -  OpenTime:  avg  42.315us,  max  626.370us,  min  7.720us
                                -  ProjectionTime:  avg  20.689ms,  max  55.317ms,  min  40ns
                                -  RowsProduced:  sum  100.078818M  (100078818),  avg  3.33596M  (3335960),  max  6.678084M  (6678084),  min  0
                              LOCAL_EXCHANGE_OPERATOR  (BUCKET_HASH_SHUFFLE)  (id=-3):
                                    -  BlocksProduced:  sum  23.24K  (23240),  avg  774,  max  1.615K  (1615),  min  0
                                    -  CloseTime:  avg  0ns,  max  0ns,  min  0ns
                                    -  ExecTime:  avg  1s75ms,  max  2s371ms,  min  1.360us
                                    -  GetBlockFailedTime:  sum  2.721K  (2721),  avg  90,  max  373,  min  0
                                    -  InitTime:  avg  1.537us,  max  2.920us,  min  1.90us
                                    -  MemoryUsage:  sum  ,  avg  ,  max  ,  min
                                        -  PeakMemoryUsage:  sum  2.14  GB,  avg  73.03  MB,  max  139.88  MB,  min  0.00
                                    -  OpenTime:  avg  0ns,  max  0ns,  min  0ns
                                    -  ProjectionTime:  avg  0ns,  max  0ns,  min  0ns
                                    -  RowsProduced:  sum  100.078818M  (100078818),  avg  3.33596M  (3335960),  max  6.678084M  (6678084),  min  0
                                    -  WaitForDependency[LOCAL_EXCHANGE_OPERATOR_DEPENDENCY]Time:  avg  4s597ms,  max  12s259ms,  min  30.78ms

也就是不管10个桶还是12个桶都存在倾斜情况。但12个桶的倾斜更严重

桶数设置为6，12后，性能还比不上桶为10

1 Answers