【已解决】一个profile，希望可以分析一下性能瓶颈与相应的优化

Viewed 205
performance 1.2

doris版本1.1.3
其中的一个instance：

        Instance  c624c4e5014542b6-9e60f8362e3e6c7f  (host=TNetworkAddress(hostname:172.18.197.152,  port:9060)):(Active:  52s780ms,  %  non-child:  0.01%)
              -  FragmentCpuTime:  25s73ms
              -  MemoryLimit:  8.00  GB
              -  PeakMemoryUsage:  2.83  GB
              -  RowsProduced:  933.498K  (933498)
            BlockMgr:
                  -  BlockWritesOutstanding:  0
                  -  BlocksCreated:  362
                  -  BlocksRecycled:  357
                  -  BufferedPins:  0
                  -  BytesWritten:  0.00  
                  -  MaxBlockSize:  8.00  MB
                  -  TotalBufferWaitTime:  0ns
                  -  TotalEncryptionTime:  0ns
                  -  TotalIntegrityCheckTime:  0ns
                  -  TotalReadBlockTime:  0ns
            OlapTableSink:(Active:  27s560ms,  %  non-child:  39.93%)
                  -  CloseWaitTime:  2s37ms
                  -  ConvertBatchTime:  2s661ms
                  -  MaxAddBatchExecTime:  988.16ms
                  -  NonBlockingSendTime:  31s253ms
                      -  NonBlockingSendWorkTime:  30s889ms
                          -  SerializeBatchTime:  26s547ms
                  -  NumberBatchAdded:  2.742K  (2742)
                  -  NumberNodeChannels:  16
                  -  OpenTime:  1.654ms
                  -  RowsFiltered:  0
                  -  RowsRead:  933.498K  (933498)
                  -  RowsReturned:  933.498K  (933498)
                  -  SendDataTime:  22s232ms
                      -  WaitMemLimitTime:  17s40ms
                  -  TotalAddBatchExecTime:  30s266ms
                  -  ValidateDataTime:  370.127ms
            HASH_JOIN_NODE  (id=11):(Active:  25s213ms,  %  non-child:  0.28%)
                  -  ExecOption:  Hash  Table  Built  Asynchronously
                  -  BuildBuckets:  4.096K  (4096)
                  -  BuildRows:  2.924K  (2924)
                  -  BuildTime:  1.555ms
                  -  HashTableMaxList:  5
                  -  HashTableMinList:  1
                  -  LoadFactor:  4602949299033079800.00
                  -  PeakMemoryUsage:  8.74  MB
                  -  ProbeRows:  933.498K  (933498)
                  -  ProbeTime:  196.216ms
                  -  PushDownComputeTime:  0ns
                  -  PushDownTime:  0ns
                  -  RowsReturned:  933.498K  (933498)
                  -  RowsReturnedRate:  37.024K  /sec
                EXCHANGE_NODE  (id=15):(Active:  1.984ms,  %  non-child:  0.00%)
                      -  BytesReceived:  51.81  KB
                      -  ConvertRowBatchTime:  40.867us
                      -  DataArrivalWaitTime:  1.814ms
                      -  DeserializeRowBatchTimer:  163.262us
                      -  FirstBatchArrivalWaitTime:  728.19us
                      -  PeakMemoryUsage:  41.92  KB
                      -  RowsReturned:  2.924K  (2924)
                      -  RowsReturnedRate:  1.473278M  /sec
                      -  SendersBlockedTotalTimer(*):  0ns
                HASH_JOIN_NODE  (id=9):(Active:  25s14ms,  %  non-child:  0.37%)
                      -  ExecOption:  Hash  Table  Built  Asynchronously
                      -  BuildBuckets:  4.096K  (4096)
                      -  BuildRows:  2.924K  (2924)
                      -  BuildTime:  11.45ms
                      -  HashTableMaxList:  5
                      -  HashTableMinList:  1
                      -  LoadFactor:  4602949299033079800.00
                      -  PeakMemoryUsage:  8.73  MB
                      -  ProbeRows:  933.498K  (933498)
                      -  ProbeTime:  267.56ms
                      -  PushDownComputeTime:  0ns
                      -  PushDownTime:  0ns
                      -  RowsReturned:  933.498K  (933498)
                      -  RowsReturnedRate:  37.318K  /sec
                    EXCHANGE_NODE  (id=14):(Active:  11.345ms,  %  non-child:  0.02%)
                          -  BytesReceived:  51.81  KB
                          -  ConvertRowBatchTime:  39.484us
                          -  DataArrivalWaitTime:  11.185ms
                          -  DeserializeRowBatchTimer:  182.675us
                          -  FirstBatchArrivalWaitTime:  688.334us
                          -  PeakMemoryUsage:  38.65  KB
                          -  RowsReturned:  2.924K  (2924)
                          -  RowsReturnedRate:  257.717K  /sec
                          -  SendersBlockedTotalTimer(*):  0ns
                    HASH_JOIN_NODE  (id=7):(Active:  24s746ms,  %  non-child:  31.54%)
                          -  ExecOption:  Hash  Table  Built  Asynchronously
                          -  BuildBuckets:  2.048K  (2048)
                          -  BuildRows:  32
                          -  BuildTime:  309.955us
                          -  HashTableMaxList:  1
                          -  HashTableMinList:  1
                          -  LoadFactor:  4580160821035794400.00
                          -  PeakMemoryUsage:  7.09  MB
                          -  ProbeRows:  933.498K  (933498)
                          -  ProbeTime:  161.364ms
                          -  PushDownComputeTime:  0ns
                          -  PushDownTime:  0ns
                          -  RowsReturned:  933.498K  (933498)
                          -  RowsReturnedRate:  37.723K  /sec
                        EXCHANGE_NODE  (id=13):(Active:  1.88ms,  %  non-child:  0.00%)
                              -  BytesReceived:  1.14  KB
                              -  ConvertRowBatchTime:  1.793us
                              -  DataArrivalWaitTime:  1.71ms
                              -  DeserializeRowBatchTimer:  22.578us
                              -  FirstBatchArrivalWaitTime:  780.417us
                              -  PeakMemoryUsage:  4.25  KB
                              -  RowsReturned:  32
                              -  RowsReturnedRate:  29.41K  /sec
                              -  SendersBlockedTotalTimer(*):  0ns
                        SELECT_NODE  (id=5):(Active:  2s979ms,  %  non-child:  0.00%)
                              -  PeakMemoryUsage:  6.51  MB
                              -  RowsReturned:  933.498K  (933498)
                              -  RowsReturnedRate:  313.342K  /sec
                            ANALYTIC_EVAL_NODE  (id=4):(Active:  24s476ms,  %  non-child:  4.15%)
                                  -  EvaluationTime:  2s861ms
                                  -  GetNewBlockTime:  620.117us
                                  -  PeakMemoryUsage:  21.08  MB
                                  -  PinTime:  0ns
                                  -  RowsReturned:  978.742K  (978742)
                                  -  RowsReturnedRate:  39.986K  /sec
                                  -  UnpinTime:  237.245us
                                SORT_NODE  (id=3):(Active:  21s610ms,  %  non-child:  5.21%)
                                      -  InMemorySortTime:  1s263ms
                                      -  InitialRunsCreated:  1
                                      -  PeakMemoryUsage:  1.45  GB
                                      -  RowsReturned:  978.742K  (978742)
                                      -  RowsReturnedRate:  45.29K  /sec
                                      -  SortDataSize:  1.43  GB
                                      -  TotalMergesPerformed:  0
                                    ANALYTIC_EVAL_NODE  (id=2):(Active:  18s15ms,  %  non-child:  4.47%)
                                          -  EvaluationTime:  3s80ms
                                          -  GetNewBlockTime:  1.515ms
                                          -  PeakMemoryUsage:  20.54  MB
                                          -  PinTime:  0ns
                                          -  RowsReturned:  978.742K  (978742)
                                          -  RowsReturnedRate:  54.328K  /sec
                                          -  UnpinTime:  165.140us
                                        SORT_NODE  (id=1):(Active:  14s933ms,  %  non-child:  9.75%)
                                              -  InMemorySortTime:  2s989ms
                                              -  InitialRunsCreated:  1
                                              -  PeakMemoryUsage:  1.38  GB
                                              -  RowsReturned:  978.742K  (978742)
                                              -  RowsReturnedRate:  65.542K  /sec
                                              -  SortDataSize:  1.36  GB
                                              -  TotalMergesPerformed:  0
                                            EXCHANGE_NODE  (id=12):(Active:  8s206ms,  %  non-child:  11.89%)
                                                  -  BytesReceived:  573.40  MB
                                                  -  ConvertRowBatchTime:  37.835ms
                                                  -  DataArrivalWaitTime:  7s671ms
                                                  -  DeserializeRowBatchTimer:  2s218ms
                                                  -  FirstBatchArrivalWaitTime:  145.420ms
                                                  -  PeakMemoryUsage:  6.22  MB
                                                  -  RowsReturned:  978.742K  (978742)
                                                  -  RowsReturnedRate:  119.265K  /sec
                                                  -  SendersBlockedTotalTimer(*):  0ns
bin412113
2 Answers
徐振超@SelectDB6266

语句：

select modify_time, order_uuid, t2.city_id, t2.city_name, t2.big_area, t2.latlong, t2.order_group, t2.confirm_bill_time, t2.order_datetime, t2.user_id, t2.client_type, t2.remark, t2.order_vehicle_id, t2.order_vehicle_name, t2.is_user_favorite, t2.order_fleet_status, t2.ep_id, t2.order_status, t2.order_subset, t2.order_type, t2.business_type, t2.total_price_fen, t2.amount_fen, t2.paid_amount_fen, t2.pay_type, t2.pay_status, t2.pay_time, t2.cancelled_by, t2.cancel_time, t2.complete_time, t2.order_status_2_time, t2.follower_num, t2.to_pay_info, t2.is_immediate, t2.advance_payment_status, t2.advance_payment_fen, t2.has_insurance, t2.has_extra_request, t2.freight_type, t2.order_service_type, t2.version, t2.create_time, t2.order_id, t2.order_display_id, t2.is_bus_ib_sc, t2.is_bus_ep, t2.is_bus_ib, t2.is_bus_lc, t2.is_bus_ltl, t2.is_bus_move_easy, t2.is_bus_move_sc, t2.is_bus_sc, t2.is_move_wf_api, t2.start_city_id, t2.start_zone_id, t2.start_zone, t2.start_address, t2.start_lon, t2.start_lat, t2.end_city_id, t2.end_zone_id, t2.end_zone, t2.end_address, t2.end_lon, t2.end_lat, t2.total_distance, t2.market_id, t2.goods_type_id, d.goods_type as goods_type_name, t2.standard_order_vehicle_id, t2.standard_order_vehicle_name, t2.order_vehicle_lv1_type, t2.order_vehicle_lv2_type, t2.order_vehicle_lv3_type, t2.vehicle_attr, t2.package_amount, t2.discount_amount, t2.driver_perquisite_price_amount, t2.start_h3_6, t2.end_h3_6, t2.start_rowkey, t2.end_rowkey, t2.is_cross_city, t2.start_region_id, t2.end_region_id, e.city_code as start_city_code, f.city_code as end_city_code, e.city_code as o_index_2, f.city_code as d_index_2, t2.start_zone_id as o_index_3, t2.end_zone_id as d_index_3, t2.o_index_6, t2.d_index_6, t2.o_index_7, t2.d_index_7, t2.start_region_id as o_index_10, t2.end_region_id as d_index_10 from( select first_modify_time as modify_time, order_uuid, city_id, city_name, big_area, latlong, order_group, confirm_bill_time, order_datetime, user_id, client_type, remark, order_vehicle_id, order_vehicle_name, is_user_favorite, order_fleet_status, ep_id, order_status, order_subset, order_type, business_type, total_price_fen, amount_fen, paid_amount_fen, pay_type, pay_status, pay_time, cancelled_by, cancel_time, complete_time, order_status_2_time, follower_num, to_pay_info, is_immediate, advance_payment_status, advance_payment_fen, has_insurance, has_extra_request, freight_type, order_service_type, version, create_time, order_id, order_display_id, is_bus_ib_sc, is_bus_ep, is_bus_ib, is_bus_lc, is_bus_ltl, is_bus_move_easy, is_bus_move_sc, is_bus_sc, is_move_wf_api, start_city_id, start_zone_id, start_zone, start_address, start_lon, start_lat, end_city_id, end_zone_id, end_zone, end_address, end_lon, end_lat, total_distance, market_id, goods_type_id, standard_order_vehicle_id, standard_order_vehicle_name, order_vehicle_lv1_type, order_vehicle_lv2_type, order_vehicle_lv3_type, vehicle_attr, package_amount, discount_amount, driver_perquisite_price_amount, start_h3_6, end_h3_6, start_rowkey, end_rowkey, is_cross_city, start_region_id, end_region_id, o_index_6, d_index_6, o_index_7, d_index_7 from(select
          t.*,
          first_value(modify_time) over( partition by order_uuid, order_status order by version,modify_time ) as first_modify_time,
          row_number() over( partition by order_uuid, order_status order by version desc,modify_time desc ) rn
        from
          hll_dwd.rtc_dwd_order_request_test t
        where
          dt = date_format('2024-04-10 23:25:00', 'yyyy-MM-dd')
          and modify_time < concat( substr( from_unixtime( unix_timestamp('2024-04-10 23:25:00') + 60 * 10, 'yyyy-MM-dd HH:mm:ss'), 1, 15 ), '0:00')
          and date(modify_time) = date(order_datetime)
      ) t1
    where
      t1.rn = 1
  ) t2
  left join hll_dim.dim_goods_type d on t2.goods_type_id = d.goods_type_id
  left join hll_dim.dim_city_districts e on t2.start_zone_id = e.area_code
  left join hll_dim.dim_city_districts f on t2.end_zone_id = f.area_code

建表语句：

CREATE TABLE rtc_dwd_order_request_test( modify_time datetime NULL COMMENT "", order_uuid varchar(36) NULL COMMENT "", city_id bigint(20) NULL COMMENT "", city_name varchar(100) NULL COMMENT "", big_area varchar(36) NULL COMMENT "", latlong varchar(1024) NULL COMMENT "", order_group int(11) NULL COMMENT "", confirm_bill_time varchar(36) NULL COMMENT "", order_datetime varchar(36) NULL COMMENT "", user_id bigint(20) NULL COMMENT "", client_type smallint(6) NULL COMMENT "", remark varchar(65533) NULL COMMENT "", order_vehicle_id smallint(6) NULL COMMENT "", order_vehicle_name varchar(100) NULL COMMENT "", is_user_favorite tinyint(4) NULL COMMENT "", order_fleet_status int(11) NULL COMMENT "", ep_id bigint(20) NULL COMMENT "", order_status tinyint(4) NULL COMMENT "", order_subset tinyint(4) NULL COMMENT "", order_type tinyint(4) NULL COMMENT "", business_type tinyint(4) NULL COMMENT "", total_price_fen int(11) NULL COMMENT "", amount_fen int(11) NULL COMMENT "", paid_amount_fen int(11) NULL COMMENT "", pay_type tinyint(4) NULL COMMENT "", pay_status tinyint(4) NULL COMMENT "", pay_time varchar(36) NULL COMMENT "", cancelled_by bigint(20) NULL COMMENT "", cancel_time varchar(36) NULL COMMENT "", complete_time varchar(36) NULL COMMENT "", order_status_2_time varchar(36) NULL COMMENT "", follower_num tinyint(4) NULL COMMENT "", to_pay_info varchar(512) NULL COMMENT "", is_immediate tinyint(4) NULL COMMENT "", advance_payment_status tinyint(4) NULL COMMENT "", advance_payment_fen int(11) NULL COMMENT "", has_insurance tinyint(4) NULL COMMENT "", has_extra_request tinyint(4) NULL COMMENT "", freight_type varchar(100) NULL COMMENT "", order_service_type tinyint(4) NULL COMMENT "", version int(11) NULL COMMENT "", create_time datetime NULL COMMENT "", order_id largeint(40) NULL COMMENT "", order_display_id largeint(40) NULL COMMENT "", is_bus_ib_sc tinyint(4) NULL COMMENT "", is_bus_ep tinyint(4) NULL COMMENT "", is_bus_ib tinyint(4) NULL COMMENT "", is_bus_lc tinyint(4) NULL COMMENT "", is_bus_ltl tinyint(4) NULL COMMENT "", is_bus_move_easy tinyint(4) NULL COMMENT "", is_bus_move_sc tinyint(4) NULL COMMENT "", is_bus_sc tinyint(4) NULL COMMENT "", is_move_wf_api tinyint(4) NULL COMMENT "", start_city_id bigint(20) NULL COMMENT "", start_zone_id bigint(20) NULL COMMENT "", start_zone varchar(100) NULL COMMENT "", start_address varchar(65533) NULL COMMENT "", start_lon varchar(100) NULL COMMENT "", start_lat varchar(100) NULL COMMENT "", end_city_id bigint(20) NULL COMMENT "", end_zone_id bigint(20) NULL COMMENT "", end_zone varchar(100) NULL COMMENT "", end_address varchar(65533) NULL COMMENT "", end_lon varchar(100) NULL COMMENT "", end_lat varchar(100) NULL COMMENT "", total_distance bigint(20) NULL COMMENT "", market_id bigint(20) NULL COMMENT "", goods_type_id bigint(20) NULL COMMENT "", standard_order_vehicle_id bigint(20) NULL COMMENT "", standard_order_vehicle_name varchar(100) NULL COMMENT "", order_vehicle_lv1_type varchar(100) NULL COMMENT "", order_vehicle_lv2_type varchar(100) NULL COMMENT "", order_vehicle_lv3_type varchar(100) NULL COMMENT "", vehicle_attr tinyint(4) NULL COMMENT "", package_amount double NULL COMMENT "", discount_amount double NULL COMMENT "", driver_perquisite_price_amount double NULL COMMENT "", start_h3_6 varchar(64) NULL COMMENT "", end_h3_6 varchar(64) NULL COMMENT "", start_rowkey varchar(64) NULL COMMENT "", end_rowkey varchar(64) NULL COMMENT "", is_cross_city bigint(20) NULL COMMENT "", start_region_id varchar(64) NULL COMMENT "", end_region_id varchar(64) NULL COMMENT "", dt date NULL COMMENT "", o_index_10 text NULL COMMENT "起点10级h3网格", d_index_10 text NULL COMMENT "终点10级h3网格", o_index_7 text NULL COMMENT "起点7级h3网格", d_index_7 text NULL COMMENT "终点7级h3网格", o_index_6 text NULL COMMENT "起点6级h3网格", d_index_6 text NULL COMMENT "终点6级h3网格") ENGINE = OLAP DUPLICATE KEY(modify_time) COMMENT "OLAP" PARTITION BY RANGE(dt) () DISTRIBUTED BY HASH(order_uuid) BUCKETS 10 PROPERTIES ( "replication_allocation" = "tag.location.default: 3", "dynamic_partition.enable" = "true", "dynamic_partition.time_unit" = "DAY", "dynamic_partition.time_zone" = "Asia/Shanghai", "dynamic_partition.start" = "-90", "dynamic_partition.end" = "1", "dynamic_partition.prefix" = "p", "dynamic_partition.replication_allocation" = "tag.location.default: 3", "dynamic_partition.buckets" = "10", "dynamic_partition.create_history_partition" = "false", "dynamic_partition.history_partition_num" = "-1", "dynamic_partition.hot_partition_num" = "0", "dynamic_partition.reserved_history_periods" = "NULL", "in_memory" = "false", "storage_format" = "V2" );

bin412113