一个profile,希望可以分析一下性能瓶颈与相应的优化

Viewed 146

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

2 Answers

语句:

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" );