查询性能分析

自己看了订单机器用的是万兆网卡,为什么还有网络问题,哪里有相关学习资料,感觉都不全
e[0mSummarye[0m
e[0mQueryId: 5542a60a-3ea2-11ef-a243-6c92bfb68cd6e[0m
e[0mVersion: 3.1.8-55c4c35e[0m
e[0mState: Finishede[0m
e[0mTotalTime: 2s447mse[0m
e[0mExecutionTime: 2s293ms [Scan: 238.288ms (10.39%), Network: 2s899ms (126.43%), ResultDeliverTime: 0ns (0.00%), ScheduleTime: 45.934ms (2.00%)]e[0m
e[0mCollectProfileTime: 30mse[0m
e[0mFrontendProfileMergeTime: 45.137mse[0m
e[0mQueryPeakMemoryUsage: 672.456 MB, QueryAllocatedMemoryUsage: 9.901 GBe[0m
e[0mTop Most Time-consuming Nodes:e[0m
e[1me[31m1. EXCHANGE (id=12) [SHUFFLE]: 1s749ms (48.86%)e[0m
e[0m2. EXCHANGE (id=41) [SHUFFLE]: 210.193ms (5.87%)e[0m
e[0m3. OLAP_SCAN (id=0) : 193.432ms (5.40%)e[0m
e[0m4. EXCHANGE (id=31) [BROADCAST]: 179.741ms (5.02%)e[0m
e[0m5. EXCHANGE (id=8) [BROADCAST]: 162.804ms (4.55%)e[0m
e[0m6. EXCHANGE (id=27) [BROADCAST]: 160.700ms (4.49%)e[0m
e[0m7. EXCHANGE (id=4) [BROADCAST]: 150.138ms (4.19%)e[0m
e[0m8. EXCHANGE (id=19) [BROADCAST]: 142.891ms (3.99%)e[0m
e[0m9. EXCHANGE (id=35) [SHUFFLE]: 142.578ms (3.98%)e[0m
e[0m10. EXCHANGE (id=23) [BROADCAST]: 133.579ms (3.73%)e[0m
e[0mTop Most Memory-consuming Nodes:e[0m
e[0m1. EXCHANGE (id=12) [SHUFFLE]: 927.516 MBe[0m
e[0m2. EXCHANGE (id=35) [SHUFFLE]: 179.959 MBe[0m
e[0m3. EXCHANGE (id=41) [SHUFFLE]: 76.706 MBe[0m
e[0m4. HASH_JOIN (id=32) [BROADCAST, LEFT OUTER JOIN]: 65.873 MBe[0m
e[0m5. HASH_JOIN (id=5) [BROADCAST, INNER JOIN]: 56.879 MBe[0m
e[0m6. HASH_JOIN (id=38) [BUCKET_SHUFFLE(S), RIGHT OUTER JOIN]: 37.176 MBe[0m
e[0m7. AGGREGATION (id=11) [serialize, update]: 31.896 MBe[0m
e[0m8. AGGREGATION (id=34) [serialize, update]: 27.294 MBe[0m
e[0m9. HASH_JOIN (id=9) [BROADCAST, INNER JOIN]: 14.285 MBe[0m
e[0m10. HASH_JOIN (id=28) [BROADCAST, INNER JOIN]: 13.821 MBe[0m
e[0mNonDefaultVariables:e[0m
e[0menable_adaptive_sink_dop: false -> truee[0m
e[0menable_persistent_index_by_default: false -> truee[0m
e[0menable_profile: false -> truee[0m
e[0mstreaming_preaggregation_mode: auto -> force_streaminge[0m
e[0mFragment 0e[0m
│ e[0mBackendNum: 1e[0m
│ e[0mInstancePeakMemoryUsage: 872.211 KB, InstanceAllocatedMemoryUsage: 1.510 MBe[0m
│ e[0mPrepareTime: 1.635mse[0m
└──e[0mRESULT_SINKe[0m
│ e[0mTotalTime: 93.341us (0.00%) [CPUTime: 93.341us]e[0m
│ e[0mOutputRows: 1e[0m
│ e[0mSinkType: MYSQL_PROTOCALe[0m
└──e[0mPROJECT (id=47) e[0m
│ e[0mEstimates: [row: ?, cpu: ?, memory: ?, network: ?, cost: ?]e[0m
│ e[0mTotalTime: 61.061us (0.00%) [CPUTime: 61.061us]e[0m
│ e[0mOutputRows: 1e[0m
│ e[0mExpression: [149: max, coalesce(144: sum, 0), round(coalesce(145: sum, 0.0), 2), …]e[0m
└──e[0mAGGREGATION (id=46) [finalize, merge]e[0m
│ e[0mEstimates: [row: 1, cpu: 0.00, memory: 0.00, network: 0.00, cost: 1382361520413.64]e[0m
│ e[0mTotalTime: 3.481ms (0.10%) [CPUTime: 3.481ms]e[0m
│ e[0mOutputRows: 1e[0m
│ e[0mPeakMemory: 1.500 KB, AllocatedMemory: 26.390 KBe[0m
│ e[0mAggExprs: [sum(145: sum), sum(146: sum), sum(147: sum), sum(148: sum), …]e[0m
│ e[0mSubordinateOperators: e[0m
│ e[0mLOCAL_EXCHANGE [Passthrough]e[0m
└──e[0mEXCHANGE (id=45) [GATHER]e[0m
e[0mEstimates: [row: 1, cpu: 88.00, memory: 0.00, network: 88.00, cost: 1382361520033.64]e[0m
e[0mTotalTime: 8.523ms (0.24%) [CPUTime: 3.717ms, NetworkTime: 4.806ms]e[0m
e[0mOutputRows: 144e[0m
e[0mPeakMemory: 592.311 KB, AllocatedMemory: 2.184 MBe[0m
e[0m
e[0mFragment 1e[0m
│ e[0mBackendNum: 3e[0m
│ e[0mInstancePeakMemoryUsage: 43.701 MB, InstanceAllocatedMemoryUsage: 223.050 MBe[0m
│ e[0mPrepareTime: 3.676mse[0m
└──e[0mDATA_STREAM_SINK (id=45)e[0m
│ e[0mPartitionType: UNPARTITIONEDe[0m
└──e[0mAGGREGATION (id=44) [serialize, update]e[0m
│ e[0mEstimates: [row: 1, cpu: 5388594.00, memory: 88.00, network: 0.00, cost: 1382361519857.64]e[0m
│ e[0mTotalTime: 923.240us (0.03%) [CPUTime: 923.240us]e[0m
│ e[0mOutputRows: 144e[0m
│ e[0mPeakMemory: 3.380 MB, AllocatedMemory: 9.910 MBe[0m
│ e[0mAggExprs: [sum(coalesce(130: sum, 0.0)), sum(coalesce(131: sum, 0.0)), …]e[0m
└──e[0mPROJECT (id=43) e[0m
│ e[0mEstimates: [row: ?, cpu: ?, memory: ?, network: ?, cost: ?]e[0m
│ e[0mTotalTime: 88.918us (0.00%) [CPUTime: 88.918us]e[0m
│ e[0mOutputRows: 51.341K (51341)e[0m
│ e[0mExpression: [129: sum, 130: sum, 131: sum, 132: sum, 133: sum, 134: sum, 135: sum, 136: max]e[0m
└──e[0mAGGREGATION (id=42) [finalize, merge]e[0m
│ e[0mEstimates: [row: 30273, cpu: 2966754.00, memory: 5388594.00, network: 0.00, cost: 1382358825384.64]e[0m
│ e[0mTotalTime: 5.605ms (0.16%) [CPUTime: 5.605ms]e[0m
│ e[0mOutputRows: 51.341K (51341)e[0m
│ e[0mPeakMemory: 3.688 KB, AllocatedMemory: 22.108 MBe[0m
│ e[0mAggExprs: [sum(129: sum), sum(130: sum), sum(131: sum), sum(132: sum), …]e[0m
│ e[0mGroupingExprs: [11: ItemId]e[0m
└──e[0mEXCHANGE (id=41) [SHUFFLE]e[0m
e[0mEstimates: [row: 30273, cpu: 2966754.00, memory: 0.00, network: 2966754.00, cost: 1382346564819.64]e[0m
e[0mTotalTime: 210.193ms (5.87%) [CPUTime: 15.886ms, NetworkTime: 194.307ms]e[0m
e[0mOutputRows: 120.654K (120654)e[0m
e[0mPeakMemory: 76.706 MB, AllocatedMemory: 406.934 MBe[0m
e[0m
e[0mFragment 2e[0m
│ e[0mBackendNum: 3e[0m
│ e[0mInstancePeakMemoryUsage: 148.970 MB, InstanceAllocatedMemoryUsage: 2.864 GBe[0m
│ e[0mPrepareTime: 8.353mse[0m
└──e[0mDATA_STREAM_SINK (id=41)e[0m
│ e[0mPartitionType: HASH_PARTITIONEDe[0m
│ e[0mPartitionExprs: [11: ItemId]e[0m
└──e[0mAGGREGATION (id=40) [serialize, update]e[0m
│ e[0mEstimates: [row: 30273, cpu: 1345264062321.79, memory: 2966754.00, network: 0.00, cost: 1382340631311.64]e[0m
│ e[0mTotalTime: 257.058us (0.01%) [CPUTime: 257.058us]e[0m
│ e[0mOutputRows: 120.654K (120654)e[0m
│ e[0mPeakMemory: 7.379 MB, AllocatedMemory: 23.055 MBe[0m
│ e[0mAggExprs: [sum(86: coalesce), sum(87: round), sum(88: round), sum(127: coalesce), …]e[0m
│ e[0mGroupingExprs: [11: ItemId]e[0m
└──e[0mPROJECT (id=39) e[0m
│ e[0mEstimates: [row: ?, cpu: ?, memory: ?, network: ?, cost: ?]e[0m
│ e[0mTotalTime: 42.914us (0.00%) [CPUTime: 42.914us]e[0m
│ e[0mOutputRows: 120.654K (120654)e[0m
│ e[0mExpression: [11: ItemId, 85: max, 86: coalesce, 87: round, 88: round, 89: coalesce, …]e[0m
└──e[0mHASH_JOIN (id=38) [BUCKET_SHUFFLE(S), RIGHT OUTER JOIN]e[0m
│ e[0mEstimates: [row: 10851288593, cpu: 1348634702687.16, memory: 1000983081.39, network: 0.00, cost: 709702666642.75]e[0m
│ e[0mTotalTime: 15.620ms (0.44%) [CPUTime: 15.620ms]e[0m
│ e[0mOutputRows: 120.654K (120654)e[0m
│ e[0mPeakMemory: 37.176 MB, AllocatedMemory: 138.373 MBe[0m
│ e[0mBuildTime: 14.844mse[0m
│ e[0mProbeTime: 591.170use[0m
│ e[0mEqJoinConjuncts: [98: ItemId <=> 11: ItemId, 94: AccountId = 3: AccountId, …]e[0m
│ e[0mSubordinateOperators: e[0m
│ e[0mCHUNK_ACCUMULATEe[0m
├──e[0m PROJECT (id=14) e[0m
│ │ e[0mEstimates: [row: ?, cpu: ?, memory: ?, network: ?, cost: ?]e[0m
│ │ e[0mTotalTime: 302.636us (0.01%) [CPUTime: 302.636us]e[0m
│ │ e[0mOutputRows: 125.177K (125177)e[0m
│ │ e[0mExpression: [94: AccountId, 95: SiteId, 98: ItemId, …]e[0m
│ └──e[0mAGGREGATION (id=13) [finalize, merge]e[0m
│ │ e[0mEstimates: [row: 56457044, cpu: 2369657283.98, memory: 3724626353.51, network: 0.00, cost: 22865664116.92]e[0m
│ │ e[0mTotalTime: 9.277ms (0.26%) [CPUTime: 9.277ms]e[0m
│ │ e[0mOutputRows: 125.177K (125177)e[0m
│ │ e[0mPeakMemory: 3.532 MB, AllocatedMemory: 43.083 MBe[0m
│ │ e[0mAggExprs: [sum(125: sum), sum(126: sum)]e[0m
│ │ e[0mGroupingExprs: [98: ItemId, 94: AccountId, 95: SiteId]e[0m
│ └──e[1me[31mEXCHANGE (id=12) [SHUFFLE]e[0m
│ e[1me[31mEstimates: [row: 56457044, cpu: 2369657283.98, memory: 0.00, network: 2369657283.98, cost: 14231582767.92]e[0m
│ e[1me[31mTotalTime: 1s749ms (48.86%) [CPUTime: 78.124ms, NetworkTime: 1s671ms]e[0m
│ e[1me[31mOutputRows: 8.836M (8835579)e[0m
│ e[1me[31mPeakMemory: 927.516 MB, AllocatedMemory: 6.700 GBe[0m
│ e[1me[31mRuntimeFilter: 8.836M (8835579) -> 8.836M (8835579) (0.00%)e[0m
│ e[1me[31mSubordinateOperators: e[0m
│ e[1me[31mCHUNK_ACCUMULATEe[0m
│ e[1me[31mDetail Timers: e[0m
│ e[1me[31mClosureBlockTime: 145.845ms [min=0ns, max=4s708ms]e[0m
│ e[1me[31mOverallTime: 684.910ms [min=53.597ms, max=1s833ms]e[0m
│ e[1me[31mWaitTime: 658.833ms [min=20.327ms, max=1s807ms]e[0m
└──e[0m PROJECT (id=37) e[0m
│ e[0mEstimates: [row: ?, cpu: ?, memory: ?, network: ?, cost: ?]e[0m
│ e[0mTotalTime: 665.790us (0.02%) [CPUTime: 665.790us]e[0m
│ e[0mOutputRows: 120.654K (120654)e[0m
│ e[0mExpression: [3: AccountId, 4: SiteId, 11: ItemId, 85: max, coalesce(80: sum, 0), …]e[0m
└──e[0mAGGREGATION (id=36) [finalize, merge]e[0m
│ e[0mEstimates: [row: 12207110, cpu: 1000983081.39, memory: 1684581283.32, network: 0.00, cost: 11007749653.06]e[0m
│ e[0mTotalTime: 8.833ms (0.25%) [CPUTime: 8.833ms]e[0m
│ e[0mOutputRows: 120.654K (120654)e[0m
│ e[0mPeakMemory: 640.717 KB, AllocatedMemory: 44.131 MBe[0m
│ e[0mAggExprs: [sum(81: sum), sum(82: sum), sum(83: sum), sum(84: sum), max(85: max), …]e[0m
│ e[0mGroupingExprs: [11: ItemId, 3: AccountId, 4: SiteId]e[0m
└──e[0mEXCHANGE (id=35) [SHUFFLE]e[0m
e[0mEstimates: [row: 12207110, cpu: 1000983081.39, memory: 0.00, network: 1000983081.39, cost: 7138095545.72]e[0m
e[0mTotalTime: 142.578ms (3.98%) [CPUTime: 30.843ms, NetworkTime: 111.735ms]e[0m
e[0mOutputRows: 1.109M (1109174)e[0m
e[0mPeakMemory: 179.959 MB, AllocatedMemory: 1.194 GBe[0m
e[0m
e[0mFragment 3e[0m
│ e[0mBackendNum: 3e[0m
│ e[0mInstancePeakMemoryUsage: 151.139 MB, InstanceAllocatedMemoryUsage: 1.523 GBe[0m
│ e[0mPrepareTime: 14.804mse[0m
└──e[0mDATA_STREAM_SINK (id=35)e[0m
│ e[0mPartitionType: HASH_PARTITIONEDe[0m
│ e[0mPartitionExprs: [11: ItemId, 3: AccountId, 4: SiteId]e[0m
└──e[0mAGGREGATION (id=34) [serialize, update]e[0m
│ e[0mEstimates: [row: 12207110, cpu: 1428231222.30, memory: 1000983081.39, network: 0.00, cost: 5136129382.93]e[0m
│ e[0mTotalTime: 12.401ms (0.35%) [CPUTime: 12.401ms]e[0m
│ e[0mOutputRows: 1.109M (1109174)e[0m
│ e[0mPeakMemory: 27.294 MB, AllocatedMemory: 149.002 MBe[0m
│ e[0mAggExprs: [sum(76: expr), sum(77: expr), sum(78: case), sum(79: case), …]e[0m
│ e[0mGroupingExprs: [11: ItemId, 3: AccountId, 4: SiteId]e[0m
│ e[0mSubordinateOperators: e[0m
│ e[0mLOCAL_EXCHANGE [Passthrough]e[0m
└──e[0mPROJECT (id=33) e[0m
│ e[0mEstimates: [row: ?, cpu: ?, memory: ?, network: ?, cost: ?]e[0m
│ e[0mTotalTime: 14.465ms (0.40%) [CPUTime: 14.465ms]e[0m
│ e[0mOutputRows: 1.109M (1109174)e[0m
│ e[0mExpression: [3: AccountId, 4: SiteId, 11: ItemId, 15: RefundQty, 22: UpdateTime, …]e[0m
│ e[0mCommonExpression: [CAST(47: ExchangeRate AS DECIMAL128(18,6)), …]e[0m
└──e[0mHASH_JOIN (id=32) [BROADCAST, LEFT OUTER JOIN]e[0m
│ e[0mEstimates: [row: 12207110, cpu: 2279758736.66, memory: 9237608.00, network: 0.00, cost: 2420047608.99]e[0m
│ e[0mTotalTime: 113.685ms (3.18%) [CPUTime: 113.685ms]e[0m
│ e[0mOutputRows: 1.109M (1109174)e[0m
│ e[0mPeakMemory: 65.873 MB, AllocatedMemory: 376.021 MBe[0m
│ e[0mBuildTime: 107.801mse[0m
│ e[0mProbeTime: 4.932mse[0m
│ e[0mEqJoinConjuncts: [18: ReturnSummaryId = 62: Id]e[0m
│ e[0mSubordinateOperators: e[0m
│ e[0mLOCAL_EXCHANGE [Passthrough]e[0m
├──e[0m PROJECT (id=29) e[0m
│ │ e[0mEstimates: [row: ?, cpu: ?, memory: ?, network: ?, cost: ?]e[0m
│ │ e[0mTotalTime: 96.148us (0.00%) [CPUTime: 96.148us]e[0m
│ │ e[0mOutputRows: 1.109M (1109174)e[0m