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

Viewed 9

环境是: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

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

0 Answers