查询结果不稳定 #6484
Unanswered
qzsee
asked this question in
A - General / Q&A
Replies: 2 comments 2 replies
-
|
0.13 is an old release version. I am not sure this bug has been fixed or not in the new version. |
Beta Was this translation helpful? Give feedback.
0 replies
-
还有一点是，可以把增加 shuffle 和不加 shuffle 声明的 plan 贴出来。先看一下规划层面的 diff |
Beta Was this translation helpful? Give feedback.
2 replies
-
|
我正尝试在每个涉及到的exec node上打写日志，查看一下。 以下是explain 不加shuffle-------------------------+
| Explain String |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| PLAN FRAGMENT 0 |
| OUTPUT EXPRS:<slot 30> `t1`.`date_scale` | <slot 31> `t1`.`time_tag` | <slot 32> `t2`.`region_id` | <slot 33> `t2`.`region_name` | <slot 34> `t1`.`cat_id` | <slot 35> sum(`t1`.`arranged_amt_3p`) |
| PARTITION: RANDOM |
| |
| RESULT SINK |
| |
| 7:AGGREGATE (update finalize) |
| | output: sum(`t1`.`arranged_amt_3p`) |
| | group by: `t1`.`date_scale`, `t1`.`time_tag`, if(TupleIsNull(2), NULL, <slot 4> `region_id`), if(TupleIsNull(2), NULL, <slot 5> max(`region_name`)), `t1`.`cat_id` |
| | |
| 6:HASH JOIN |
| | join op: INNER JOIN (BROADCAST) |
| | hash predicates: |
| | colocate: false, reason: Node type not match |
| | equal join conjunct: if(TupleIsNull(2), NULL, <slot 4> `region_id`) = <slot 19> `region_id` |
| | |
| |----9:EXCHANGE |
| | |
| 3:HASH JOIN |
| | join op: LEFT OUTER JOIN (BROADCAST) |
| | hash predicates: |
| | colocate: false, reason: Node type not match |
| | equal join conjunct: `t1`.`customer_bu_id` = <slot 3> `bu_id` |
| | |
| |----8:EXCHANGE |
| | |
| 0:OlapScanNode |
| TABLE: x |
| PREAGGREGATION: OFF. Reason: Aggregate Operator not match: SUM <--> REPLACE |
| PREDICATES: `t1`.`date_scale` = '202107', `t1`.`time_tag` = 2, `t1`.`customer_bu_id` != -1, `t1`.`cat_id` = -1 |
| partitions=1/1 |
| rollup: x |
| tabletRatio=1/10 |
| tabletList=884032 |
| cardinality=5753 |
| avgRowSize=55.481663 |
| numNodes=3 |
| |
| PLAN FRAGMENT 1 |
| OUTPUT EXPRS: |
| PARTITION: RANDOM |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 09 |
| UNPARTITIONED |
| |
| 5:AGGREGATE (update finalize) |
| | output: multi_distinct_count(DISTINCT `bu_id`), multi_distinct_count(DISTINCT if(1 = 1, `bu_id`, NULL)) |
| | group by: `region_id` |
| | having: <slot 20> multi_distinct_count(DISTINCT `bu_id`) = <slot 21> multi_distinct_count(DISTINCT if(`hp` = 1, `bu_id`, NULL)) |
| | |
| 4:OlapScanNode |
| TABLE: x |
| PREAGGREGATION: OFF. Reason: conjunct on `region_id` which is StorageEngine value column |
| PREDICATES: NOT `region_name` LIKE '%KA%' |
| partitions=1/1 |
| rollup: x |
| tabletRatio=1/1 |
| tabletList=884017 |
| cardinality=111 |
| avgRowSize=73.03603 |
| numNodes=3 |
| |
| PLAN FRAGMENT 2 |
| OUTPUT EXPRS: |
| PARTITION: RANDOM |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 08 |
| UNPARTITIONED |
| |
| 2:AGGREGATE (update finalize) |
| | output: max(`region_name`) |
| | group by: `bu_id`, `region_id` |
| | |
| 1:OlapScanNode |
| TABLE: x |
| PREAGGREGATION: ON |
| partitions=1/1 |
| rollup: x |
| tabletRatio=1/1 |
| tabletList=884017 |
| cardinality=111 |
| avgRowSize=73.03603 |
| numNodes=3 |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
shuffle-------------------------+
| Explain String |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| PLAN FRAGMENT 0 |
| OUTPUT EXPRS:<slot 30> `t1`.`date_scale` | <slot 31> `t1`.`time_tag` | <slot 32> `t2`.`region_id` | <slot 33> `t2`.`region_name` | <slot 34> `t1`.`cat_id` | <slot 35> sum(`t1`.`arranged_amt_3p`) |
| PARTITION: HASH_PARTITIONED: if(TupleIsNull(2), NULL, <slot 4> `region_id`) |
| |
| RESULT SINK |
| |
| 7:AGGREGATE (update finalize) |
| | output: sum(`t1`.`arranged_amt_3p`) |
| | group by: `t1`.`date_scale`, `t1`.`time_tag`, if(TupleIsNull(2), NULL, <slot 4> `region_id`), if(TupleIsNull(2), NULL, <slot 5> max(`region_name`)), `t1`.`cat_id` |
| | |
| 6:HASH JOIN |
| | join op: INNER JOIN (PARTITIONED) |
| | hash predicates: |
| | colocate: false, reason: Has join hint |
| | equal join conjunct: if(TupleIsNull(2), NULL, <slot 4> `region_id`) = <slot 19> `region_id` |
| | |
| |----10:EXCHANGE |
| | |
| 9:EXCHANGE |
| |
| PLAN FRAGMENT 1 |
| OUTPUT EXPRS: |
| PARTITION: RANDOM |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 10 |
| HASH_PARTITIONED: <slot 19> `region_id` |
| |
| 5:AGGREGATE (update finalize) |
| | output: multi_distinct_count(DISTINCT `bu_id`), multi_distinct_count(DISTINCT if(1 = 1, `bu_id`, NULL)) |
| | group by: `region_id` |
| | having: <slot 20> multi_distinct_count(DISTINCT `bu_id`) = <slot 21> multi_distinct_count(DISTINCT if(`hp` = 1, `bu_id`, NULL)) |
| | |
| 4:OlapScanNode |
| TABLE: x |
| PREAGGREGATION: OFF. Reason: conjunct on `region_id` which is StorageEngine value column |
| PREDICATES: NOT `region_name` LIKE '%KA%' |
| partitions=1/1 |
| rollup: x |
| tabletRatio=1/1 |
| tabletList=884017 |
| cardinality=111 |
| avgRowSize=73.03603 |
| numNodes=3 |
| |
| PLAN FRAGMENT 2 |
| OUTPUT EXPRS: |
| PARTITION: RANDOM |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 09 |
| HASH_PARTITIONED: if(TupleIsNull(2), NULL, <slot 4> `region_id`) |
| |
| 3:HASH JOIN |
| | join op: LEFT OUTER JOIN (BROADCAST) |
| | hash predicates: |
| | colocate: false, reason: Node type not match |
| | equal join conjunct: `t1`.`customer_bu_id` = <slot 3> `bu_id` |
| | |
| |----8:EXCHANGE |
| | |
| 0:OlapScanNode |
| TABLE: x |
| PREAGGREGATION: OFF. Reason: Aggregate Operator not match: SUM <--> REPLACE |
| PREDICATES: `t1`.`date_scale` = '202107', `t1`.`time_tag` = 2, `t1`.`customer_bu_id` != -1, `t1`.`cat_id` = -1 |
| partitions=1/1 |
| rollup: x |
| tabletRatio=1/10 |
| tabletList=884032 |
| cardinality=5753 |
| avgRowSize=55.481663 |
| numNodes=3 |
| |
| PLAN FRAGMENT 3 |
| OUTPUT EXPRS: |
| PARTITION: RANDOM |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 08 |
| UNPARTITIONED |
| |
| 2:AGGREGATE (update finalize) |
| | output: max(`region_name`) |
| | group by: `bu_id`, `region_id` |
| | |
| 1:OlapScanNode |
| TABLE: x |
| PREAGGREGATION: ON |
| partitions=1/1 |
| rollup: x |
| tabletRatio=1/1 |
| tabletList=884017 |
| cardinality=111 |
| avgRowSize=73.03603 |
| numNodes=3 |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |
Beta Was this translation helpful? Give feedback.
-
|
根据这个描述，exchange id = 8 的下层节点，和上层单个节点。在shuffle 和非shuffle 之间并不存在diff。反而区别在于更上层的节点。
|
Beta Was this translation helpful? Give feedback.
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Uh oh!
There was an error while loading. Please reload this page.
-
一、查询SQL
二、查询结果
+------------+----------+-----------+--------------+--------+-----------------------------+
| date_scale | time_tag | region_id | region_name | cat_id | sum(
t1.arranged_amt_3p) |+------------+----------+-----------+--------------+--------+-----------------------------+
| 202107 | 2 | 22304 | A | -1 | 106839459.9 |
| 202107 | 2 | 22303 | B | -1 | 131133774.81 |
| 202107 | 2 | 22306 | C | -1 | 66322643.32 |
| 202107 | 2 | 22307 | D | -1 | 77013996.36 |
| 202107 | 2 | 22302 | E | -1 | 56652868.22 |
+------------+----------+-----------+--------------+--------+-----------------------------+
每次的查询结果中，sum列的值都会变。profile
Query: Summary: - Query ID: a5c68792068a454d-8119d8839ac19b6d - Start Time: 2021-08-17 12:38:05 - End Time: 2021-08-17 12:38:06 - Total: 34ms - Query Type: Query - Query State: EOF - Doris Version: 0.13.11 - User: root - Default Db: xx:xx - Sql Statement: with hpr as ( select region_id from ( select region_id, count(distinct bu_id) as all_bu, count(distinct if(hp=1,bu_id,null)) hp_bu from ( select region_id, bu_id, 1 as hp from A.b where region_name not like '%KA%' ) tt group by 1 ) t3 where t3.all_bu=t3.hp_bu) select t1.date_scale, t1.time_tag, t2.region_id, t2.region_name, t1.cat_id, sum(t1.arranged_amt_3p) from A.c t1 left join ( select bu_id, region_id, max(region_name) as region_name from A.b group by 1, 2 ) t2 on t1.customer_bu_id=t2.bu_id join hpr t3 on t2.region_id=t3.region_id where t1.date_scale='202107' and t1.time_tag=2 and t1.customer_bu_id<>-1 and t1.cat_id=-1 group by t1.date_scale, t1.time_tag, t2.region_id, t2.region_name, t1.cat_id - Is Cached: No Execution Profile a5c68792068a454d-8119d8839ac19b6d:(Active: 33.573ms, % non-child: 100.00%) Fragment 0: Instance a5c68792068a454d-8119d8839ac19b6e (host=TNetworkAddress(hostname:x, port:x)):(Active: 13.855ms, % non-child: 0.00%) - AverageThreadTokens: 0.00 - FragmentCpuTime: 966.233us - MemoryLimit: 2.00 GB - PeakMemoryUsage: 34.46 MB - PeakReservation: 34.00 MB - PeakUsedReservation: 0.00 - RowsProduced: 5 BlockMgr: - BlockWritesOutstanding: 0 - BlocksCreated: 0 - BlocksRecycled: 0 - BufferedPins: 0 - BytesWritten: 0.00 - MaxBlockSize: 8.00 MB - TotalBufferWaitTime: 0ns - TotalEncryptionTime: 0ns - TotalIntegrityCheckTime: 0ns - TotalReadBlockTime: 0ns DataBufferSender (dst_fragment_instance_id=a5c68792068a454d-8119d8839ac19b6e): - AppendBatchTime: 38.620us - ResultRendTime: 25.779us - TupleConvertTime: 10.789us - NumSentRows: 5 AGGREGATION_NODE (id=7):(Active: 14.139ms, % non-child: 3.09%) - Probe Method: HashTable Linear Probing - BuildTime: 59.521us - GetResultsTime: 4.307us - HTResize: 0 - HTResizeTime: 839ns - HashBuckets: 5.12K (5120) - HashCollisions: 0 - HashFailedProbe: 0 - HashFilledBuckets: 5 - HashProbe: 90 - HashTravelLength: 0 - LargestPartitionPercent: 1 - MaxPartitionLevel: 0 - NumRepartitions: 0 - PartitionsCreated: 16 - PeakMemoryUsage: 34.28 MB - RowsProcessed: 90 - RowsRepartitioned: 0 - RowsReturned: 5 - RowsReturnedRate: 353 - SpilledPartitions: 0 HASH_JOIN_NODE (id=6):(Active: 13.100ms, % non-child: 0.24%) - ExecOption: Hash Table Built Asynchronously - BuildBuckets: 1.024K (1024) - BuildRows: 5 - BuildTime: 3.951us - LoadFactor: 4572279521687896100.00 - PeakMemoryUsage: 260.00 KB - ProbeRows: 94 - ProbeTime: 10.226us - PushDownComputeTime: 4.401us - PushDownTime: 3.415us - RowsReturned: 90 - RowsReturnedRate: 6.869K /sec EXCHANGE_NODE (id=9):(Active: 7.343ms, % non-child: 21.87%) - BytesReceived: 175.00 B - ConvertRowBatchTime: 3.139us - DataArrivalWaitTime: 7.282ms - DeserializeRowBatchTimer: 21.454us - FirstBatchArrivalWaitTime: 6.939ms - PeakMemoryUsage: 4.02 KB - RowsReturned: 5 - RowsReturnedRate: 680 - SendersBlockedTotalTimer(*): 0ns HASH_JOIN_NODE (id=3):(Active: 5.675ms, % non-child: 0.17%) - ExecOption: Hash Table Built Asynchronously - BuildBuckets: 1.024K (1024) - BuildRows: 90 - BuildTime: 6.252us - LoadFactor: 4590997607639154700.00 - PeakMemoryUsage: 296.00 KB - ProbeRows: 94 - ProbeTime: 12.606us - PushDownComputeTime: 0ns - PushDownTime: 0ns - RowsReturned: 94 - RowsReturnedRate: 16.563K /sec EXCHANGE_NODE (id=8):(Active: 4.391ms, % non-child: 13.08%) - BytesReceived: 2.60 KB - ConvertRowBatchTime: 20.755us - DataArrivalWaitTime: 4.256ms - DeserializeRowBatchTimer: 77.311us - FirstBatchArrivalWaitTime: 2.941ms - PeakMemoryUsage: 4.16 KB - RowsReturned: 90 - RowsReturnedRate: 20.494K /sec - SendersBlockedTotalTimer(*): 0ns OLAP_SCAN_NODE (id=0):(Active: 1.227ms, % non-child: 3.66%) - BlockLookupCacheTime: 13.626us - BlockPutCacheTime: 0ns - BytesRead: 17.96 KB - GetNextTime: 1.226ms - MaxWaitScanTime: 104.742us - NumDiskAccess: 1 - NumScanners: 1 - PeakMemoryUsage: 0.00 - RowsRead: 95 - RowsReturned: 94 - RowsReturnedRate: 76.574K /sec - RowsetNum: 2 - RowsetReaderInitTime: 799.633us - ScanCpuTime: 39.826us - ScannerBlockPutTimer: 4.74us - ScannerMaxPendingTimer: 36.2us - SegmentNum: 1 - StartScanTime: 98.378us - TabletCount : 1 - TotalReadThroughput: 0.0 /sec OlapScanner: - BlockConvertTime: 13.594us - BlockFetchTime: 658.724us - ReaderInitTime: 891.181us - RowsDelFiltered: 0 - RowsPushedCondFiltered: 0 - ScanTime: 40.139us - ShowHintsTime_V1: 0ns SegmentIterator: - BitmapIndexFilterTimer: 575ns - BlockLoadTime: 641.638us - BlockSeekCount: 118 - BlockSeekTime: 488.368us - BlocksLoad: 1 - CachedPagesNum: 10 - CompressedBytesRead: 0.00 - DecompressorTimer: 0ns - IOTimer: 0ns - IndexLoadTime_V1: 0ns - NumSegmentFiltered: 0 - NumSegmentTotal: 1 - RawRowsRead: 380 - RowsBitmapIndexFiltered: 0 - RowsBloomFilterFiltered: 0 - RowsConditionsFiltered: 0 - RowsKeyRangeFiltered: 5.373K (5373) - RowsStatsFiltered: 0 - RowsVectorPredFiltered: 285 - TotalPagesNum: 10 - UncompressedBytesRead: 0.00 - VectorPredEvalTime: 4.847us Buffer pool: - AllocTime: 10.853us - CumulativeAllocationBytes: 12.00 MB - CumulativeAllocations: 6 - PeakReservation: 34.00 MB - PeakUnpinnedBytes: 0.00 - PeakUsedReservation: 12.00 MB - ReservationLimit: 80.00 GB Fragment 1: Instance a5c68792068a454d-8119d8839ac19b70 (host=TNetworkAddress(hostname:x, port:x)):(Active: 2.551ms, % non-child: 0.00%) - AverageThreadTokens: 0.00 - FragmentCpuTime: 1.541ms - MemoryLimit: 2.00 GB - PeakMemoryUsage: 36.10 MB - PeakReservation: 36.00 MB - PeakUsedReservation: 0.00 - RowsProduced: 5 BlockMgr: - BlockWritesOutstanding: 0 - BlocksCreated: 0 - BlocksRecycled: 0 - BufferedPins: 0 - BytesWritten: 0.00 - MaxBlockSize: 8.00 MB - TotalBufferWaitTime: 0ns - TotalEncryptionTime: 0ns - TotalIntegrityCheckTime: 0ns - TotalReadBlockTime: 0ns DataStreamSender (dst_id=9, dst_fragments=[a5c68792068a454d-8119d8839ac19b6e]):(Active: 267.83us, % non-child: 0.80%) - BytesSent: 175.00 B - IgnoreRows: 0 - OverallThroughput: 639.8701171875 KB/sec - PeakMemoryUsage: 4.00 KB - SerializeBatchTime: 8.915us - UncompressedRowBatchSize: 200.00 B AGGREGATION_NODE (id=5):(Active: 2.350ms, % non-child: 4.24%) - Probe Method: HashTable Linear Probing - BuildTime: 127.62us - GetResultsTime: 45.955us - HTResize: 0 - HTResizeTime: 2.53us - HashBuckets: 5.12K (5120) - HashCollisions: 0 - HashFailedProbe: 0 - HashFilledBuckets: 5 - HashProbe: 106 - HashTravelLength: 0 - LargestPartitionPercent: 0 - MaxPartitionLevel: 0 - NumRepartitions: 0 - PartitionsCreated: 16 - PeakMemoryUsage: 36.09 MB - RowsProcessed: 106 - RowsRepartitioned: 0 - RowsReturned: 5 - RowsReturnedRate: 2.126K /sec - SpilledPartitions: 0 OLAP_SCAN_NODE (id=4):(Active: 927.462us, % non-child: 2.76%) - BlockLookupCacheTime: 29.201us - BlockPutCacheTime: 0ns - BytesRead: 16.69 KB - GetNextTime: 877.187us - MaxWaitScanTime: 125.161us - NumDiskAccess: 1 - NumScanners: 1 - PeakMemoryUsage: 0.00 - RowsRead: 111 - RowsReturned: 106 - RowsReturnedRate: 114.29K /sec - RowsetNum: 2 - RowsetReaderInitTime: 496.477us - ScanCpuTime: 44.392us - ScannerBlockPutTimer: 5.401us - ScannerMaxPendingTimer: 30.9us - SegmentNum: 1 - StartScanTime: 110.477us - TabletCount : 1 - TotalReadThroughput: 0.0 /sec OlapScanner: - BlockConvertTime: 17.770us - BlockFetchTime: 356.404us - ReaderInitTime: 536.59us - RowsDelFiltered: 0 - RowsPushedCondFiltered: 0 - ScanTime: 44.572us - ShowHintsTime_V1: 0ns SegmentIterator: - BitmapIndexFilterTimer: 323ns - BlockLoadTime: 334.951us - BlockSeekCount: 7 - BlockSeekTime: 237.424us - BlocksLoad: 1 - CachedPagesNum: 19 - CompressedBytesRead: 0.00 - DecompressorTimer: 0ns - IOTimer: 0ns - IndexLoadTime_V1: 0ns - NumSegmentFiltered: 0 - NumSegmentTotal: 1 - RawRowsRead: 111 - RowsBitmapIndexFiltered: 0 - RowsBloomFilterFiltered: 0 - RowsConditionsFiltered: 0 - RowsKeyRangeFiltered: 0 - RowsStatsFiltered: 0 - RowsVectorPredFiltered: 0 - TotalPagesNum: 19 - UncompressedBytesRead: 0.00 - VectorPredEvalTime: 0ns Buffer pool: - AllocTime: 18.548us - CumulativeAllocationBytes: 12.00 MB - CumulativeAllocations: 6 - PeakReservation: 36.00 MB - PeakUnpinnedBytes: 0.00 - PeakUsedReservation: 12.00 MB - ReservationLimit: 80.00 GB Fragment 2: Instance a5c68792068a454d-8119d8839ac19b6f (host=TNetworkAddress(hostname:x, port:x)):(Active: 4.132ms, % non-child: 0.00%) - AverageThreadTokens: 0.00 - FragmentCpuTime: 1.664ms - MemoryLimit: 2.00 GB - PeakMemoryUsage: 36.17 MB - PeakReservation: 36.00 MB - PeakUsedReservation: 0.00 - RowsProduced: 96 BlockMgr: - BlockWritesOutstanding: 0 - BlocksCreated: 0 - BlocksRecycled: 0 - BufferedPins: 0 - BytesWritten: 0.00 - MaxBlockSize: 8.00 MB - TotalBufferWaitTime: 0ns - TotalEncryptionTime: 0ns - TotalIntegrityCheckTime: 0ns - TotalReadBlockTime: 0ns DataStreamSender (dst_id=8, dst_fragments=[a5c68792068a454d-8119d8839ac19b6e]):(Active: 1.167ms, % non-child: 3.48%) - BytesSent: 2.60 KB - IgnoreRows: 0 - OverallThroughput: 2.1729326248168945 MB/sec - PeakMemoryUsage: 3.20 KB - SerializeBatchTime: 41.800us - UncompressedRowBatchSize: 5.29 KB AGGREGATION_NODE (id=2):(Active: 2.983ms, % non-child: 3.95%) - Probe Method: HashTable Linear Probing - BuildTime: 171.693us - GetResultsTime: 59.979us - HTResize: 0 - HTResizeTime: 2.312us - HashBuckets: 16.384K (16384) - HashCollisions: 0 - HashFailedProbe: 0 - HashFilledBuckets: 96 - HashProbe: 96 - HashTravelLength: 0 - LargestPartitionPercent: 16 - MaxPartitionLevel: 0 - NumRepartitions: 0 - PartitionsCreated: 16 - PeakMemoryUsage: 36.16 MB - RowsProcessed: 96 - RowsRepartitioned: 0 - RowsReturned: 96 - RowsReturnedRate: 32.177K /sec - SpilledPartitions: 0 OLAP_SCAN_NODE (id=1):(Active: 1.658ms, % non-child: 4.94%) - BlockLookupCacheTime: 6.598us - BlockPutCacheTime: 0ns - BytesRead: 3.56 KB - GetNextTime: 1.658ms - MaxWaitScanTime: 1.222ms - NumDiskAccess: 1 - NumScanners: 1 - PeakMemoryUsage: 0.00 - RowsRead: 96 - RowsReturned: 96 - RowsReturnedRate: 57.884K /sec - RowsetNum: 2 - RowsetReaderInitTime: 222.778us - ScanCpuTime: 17.800us - ScannerBlockPutTimer: 5.386us - ScannerMaxPendingTimer: 1.157ms - SegmentNum: 1 - StartScanTime: 93.784us - TabletCount : 1 - TotalReadThroughput: 0.0 /sec OlapScanner: - BlockConvertTime: 4.96us - BlockFetchTime: 127.50us - ReaderInitTime: 265.144us - RowsDelFiltered: 0 - RowsPushedCondFiltered: 0 - ScanTime: 18.200us - ShowHintsTime_V1: 0ns SegmentIterator: - BitmapIndexFilterTimer: 214ns - BlockLoadTime: 119.527us - BlockSeekCount: 7 - BlockSeekTime: 60.580us - BlocksLoad: 1 - CachedPagesNum: 4 - CompressedBytesRead: 0.00 - DecompressorTimer: 0ns - IOTimer: 0ns - IndexLoadTime_V1: 0ns - NumSegmentFiltered: 0 - NumSegmentTotal: 1 - RawRowsRead: 111 - RowsBitmapIndexFiltered: 0 - RowsBloomFilterFiltered: 0 - RowsConditionsFiltered: 0 - RowsKeyRangeFiltered: 0 - RowsStatsFiltered: 0 - RowsVectorPredFiltered: 0 - TotalPagesNum: 4 - UncompressedBytesRead: 0.00 - VectorPredEvalTime: 0ns Buffer pool: - AllocTime: 35.89us - CumulativeAllocationBytes: 34.00 MB - CumulativeAllocations: 17 - PeakReservation: 36.00 MB - PeakUnpinnedBytes: 0.00 - PeakUsedReservation: 34.00 MB - ReservationLimit: 80.00 GB三、强制shuffle
我们 对 hpr 的表变量 强制shuffle join 结果就稳定了。profile
Query: Summary: - Query ID: 21ae6d249284eb0-82f18110a8be71de - Start Time: 2021-08-17 12:41:20 - End Time: 2021-08-17 12:41:20 - Total: 33ms - Query Type: Query - Query State: EOF - Doris Version: 0.13.11 - User: root - Default Db: xx:xx - Sql Statement: with hpr as ( select region_id from ( select region_id, count(distinct bu_id) as all_bu, count(distinct if(hp=1,bu_id,null)) hp_bu from ( select region_id, bu_id, 1 as hp from A.b where region_name not like '%KA%' ) tt group by 1 ) t3 where t3.all_bu=t3.hp_bu) select t1.date_scale, t1.time_tag, t2.region_id, t2.region_name, t1.cat_id, sum(t1.arranged_amt_3p) from A.c t1 left join ( select bu_id, region_id, max(region_name) as region_name from A.b group by 1, 2 ) t2 on t1.customer_bu_id=t2.bu_id join [shuffle] hpr t3 on t2.region_id=t3.region_id where t1.date_scale='202107' and t1.time_tag=2 and t1.customer_bu_id<>-1 and t1.cat_id=-1 group by t1.date_scale, t1.time_tag, t2.region_id, t2.region_name, t1.cat_id - Is Cached: No Execution Profile 21ae6d249284eb0-82f18110a8be71de:(Active: 32.667ms, % non-child: 100.00%) Fragment 0: Instance 21ae6d249284eb0-82f18110a8be71e2 (host=TNetworkAddress(hostname:x, port:x)):(Active: 15.198ms, % non-child: 0.00%) - AverageThreadTokens: 0.00 - FragmentCpuTime: 911.310us - MemoryLimit: 2.00 GB - PeakMemoryUsage: 34.18 MB - PeakReservation: 34.00 MB - PeakUsedReservation: 0.00 - RowsProduced: 5 BlockMgr: - BlockWritesOutstanding: 0 - BlocksCreated: 0 - BlocksRecycled: 0 - BufferedPins: 0 - BytesWritten: 0.00 - MaxBlockSize: 8.00 MB - TotalBufferWaitTime: 0ns - TotalEncryptionTime: 0ns - TotalIntegrityCheckTime: 0ns - TotalReadBlockTime: 0ns DataBufferSender (dst_fragment_instance_id=21ae6d249284eb0-82f18110a8be71e2): - AppendBatchTime: 39.736us - ResultRendTime: 26.843us - TupleConvertTime: 10.837us - NumSentRows: 5 AGGREGATION_NODE (id=7):(Active: 15.348ms, % non-child: 3.10%) - Probe Method: HashTable Linear Probing - BuildTime: 45.565us - GetResultsTime: 4.192us - HTResize: 0 - HTResizeTime: 980ns - HashBuckets: 5.12K (5120) - HashCollisions: 0 - HashFailedProbe: 0 - HashFilledBuckets: 5 - HashProbe: 91 - HashTravelLength: 0 - LargestPartitionPercent: 1 - MaxPartitionLevel: 0 - NumRepartitions: 0 - PartitionsCreated: 16 - PeakMemoryUsage: 34.11 MB - RowsProcessed: 91 - RowsRepartitioned: 0 - RowsReturned: 5 - RowsReturnedRate: 325 - SpilledPartitions: 0 HASH_JOIN_NODE (id=6):(Active: 14.335ms, % non-child: 0.00%) - ExecOption: Hash Table Built Asynchronously - BuildBuckets: 1.024K (1024) - BuildRows: 5 - BuildTime: 4.320us - LoadFactor: 4572279521687896100.00 - PeakMemoryUsage: 84.00 KB - ProbeRows: 94 - ProbeTime: 10.312us - PushDownComputeTime: 0ns - PushDownTime: 0ns - RowsReturned: 91 - RowsReturnedRate: 6.347K /sec EXCHANGE_NODE (id=10):(Active: 8.50ms, % non-child: 24.64%) - BytesReceived: 175.00 B - ConvertRowBatchTime: 3.417us - DataArrivalWaitTime: 8.15ms - DeserializeRowBatchTimer: 29.337us - FirstBatchArrivalWaitTime: 7.452ms - PeakMemoryUsage: 4.02 KB - RowsReturned: 5 - RowsReturnedRate: 621 - SendersBlockedTotalTimer(*): 0ns EXCHANGE_NODE (id=9):(Active: 14.302ms, % non-child: 43.78%) - BytesReceived: 4.00 KB - ConvertRowBatchTime: 2.606us - DataArrivalWaitTime: 14.294ms - DeserializeRowBatchTimer: 14.978us - FirstBatchArrivalWaitTime: 14.256ms - PeakMemoryUsage: 17.47 KB - RowsReturned: 94 - RowsReturnedRate: 6.572K /sec - SendersBlockedTotalTimer(*): 0ns Buffer pool: - AllocTime: 7.496us - CumulativeAllocationBytes: 12.00 MB - CumulativeAllocations: 6 - PeakReservation: 34.00 MB - PeakUnpinnedBytes: 0.00 - PeakUsedReservation: 12.00 MB - ReservationLimit: 80.00 GB Fragment 1: Instance 21ae6d249284eb0-82f18110a8be71e1 (host=TNetworkAddress(hostname:x, port:x)):(Active: 3.93ms, % non-child: 0.00%) - AverageThreadTokens: 0.00 - FragmentCpuTime: 1.517ms - MemoryLimit: 2.00 GB - PeakMemoryUsage: 36.10 MB - PeakReservation: 36.00 MB - PeakUsedReservation: 0.00 - RowsProduced: 5 BlockMgr: - BlockWritesOutstanding: 0 - BlocksCreated: 0 - BlocksRecycled: 0 - BufferedPins: 0 - BytesWritten: 0.00 - MaxBlockSize: 8.00 MB - TotalBufferWaitTime: 0ns - TotalEncryptionTime: 0ns - TotalIntegrityCheckTime: 0ns - TotalReadBlockTime: 0ns DataStreamSender (dst_id=10, dst_fragments=[021ae6d249284eb0-82f18110a8be71e2]):(Active: 503.809us, % non-child: 1.54%) - BytesSent: 175.00 B - IgnoreRows: 0 - OverallThroughput: 339.2119140625 KB/sec - PeakMemoryUsage: 4.00 KB - SerializeBatchTime: 11.693us - UncompressedRowBatchSize: 200.00 B AGGREGATION_NODE (id=5):(Active: 2.609ms, % non-child: 4.09%) - Probe Method: HashTable Linear Probing - BuildTime: 124.869us - GetResultsTime: 25.393us - HTResize: 0 - HTResizeTime: 2.595us - HashBuckets: 5.12K (5120) - HashCollisions: 0 - HashFailedProbe: 0 - HashFilledBuckets: 5 - HashProbe: 106 - HashTravelLength: 0 - LargestPartitionPercent: 0 - MaxPartitionLevel: 0 - NumRepartitions: 0 - PartitionsCreated: 16 - PeakMemoryUsage: 36.09 MB - RowsProcessed: 106 - RowsRepartitioned: 0 - RowsReturned: 5 - RowsReturnedRate: 1.915K /sec - SpilledPartitions: 0 OLAP_SCAN_NODE (id=4):(Active: 1.273ms, % non-child: 3.90%) - BlockLookupCacheTime: 19.967us - BlockPutCacheTime: 0ns - BytesRead: 16.69 KB - GetNextTime: 1.237ms - MaxWaitScanTime: 133.203us - NumDiskAccess: 1 - NumScanners: 1 - PeakMemoryUsage: 0.00 - RowsRead: 111 - RowsReturned: 106 - RowsReturnedRate: 83.253K /sec - RowsetNum: 2 - RowsetReaderInitTime: 511.152us - ScanCpuTime: 43.662us - ScannerBlockPutTimer: 2.672us - ScannerMaxPendingTimer: 16.203us - SegmentNum: 1 - StartScanTime: 140.619us - TabletCount : 1 - TotalReadThroughput: 0.0 /sec OlapScanner: - BlockConvertTime: 19.728us - BlockFetchTime: 339.846us - ReaderInitTime: 544.846us - RowsDelFiltered: 0 - RowsPushedCondFiltered: 0 - ScanTime: 43.799us - ShowHintsTime_V1: 0ns SegmentIterator: - BitmapIndexFilterTimer: 220ns - BlockLoadTime: 315.982us - BlockSeekCount: 7 - BlockSeekTime: 206.823us - BlocksLoad: 1 - CachedPagesNum: 19 - CompressedBytesRead: 0.00 - DecompressorTimer: 0ns - IOTimer: 0ns - IndexLoadTime_V1: 0ns - NumSegmentFiltered: 0 - NumSegmentTotal: 1 - RawRowsRead: 111 - RowsBitmapIndexFiltered: 0 - RowsBloomFilterFiltered: 0 - RowsConditionsFiltered: 0 - RowsKeyRangeFiltered: 0 - RowsStatsFiltered: 0 - RowsVectorPredFiltered: 0 - TotalPagesNum: 19 - UncompressedBytesRead: 0.00 - VectorPredEvalTime: 0ns Buffer pool: - AllocTime: 17.313us - CumulativeAllocationBytes: 12.00 MB - CumulativeAllocations: 6 - PeakReservation: 36.00 MB - PeakUnpinnedBytes: 0.00 - PeakUsedReservation: 12.00 MB - ReservationLimit: 80.00 GB Fragment 2: Instance 21ae6d249284eb0-82f18110a8be71df (host=TNetworkAddress(hostname:x, port:x)):(Active: 6.564ms, % non-child: 0.29%) - AverageThreadTokens: 0.00 - FragmentCpuTime: 380.688us - MemoryLimit: 2.00 GB - PeakMemoryUsage: 314.45 KB - PeakReservation: 0.00 - PeakUsedReservation: 0.00 - RowsProduced: 94 BlockMgr: - BlockWritesOutstanding: 0 - BlocksCreated: 0 - BlocksRecycled: 0 - BufferedPins: 0 - BytesWritten: 0.00 - MaxBlockSize: 8.00 MB - TotalBufferWaitTime: 0ns - TotalEncryptionTime: 0ns - TotalIntegrityCheckTime: 0ns - TotalReadBlockTime: 0ns DataStreamSender (dst_id=9, dst_fragments=[021ae6d249284eb0-82f18110a8be71e2]):(Active: 152.197us, % non-child: 0.47%) - BytesSent: 4.00 KB - IgnoreRows: 0 - OverallThroughput: 25.690813064575195 MB/sec - PeakMemoryUsage: 2.45 KB - SerializeBatchTime: 96.654us - UncompressedRowBatchSize: 11.93 KB HASH_JOIN_NODE (id=3):(Active: 6.316ms, % non-child: 0.22%) - ExecOption: Hash Table Built Asynchronously - BuildBuckets: 1.024K (1024) - BuildRows: 96 - BuildTime: 11.722us - LoadFactor: 4591419820104220700.00 - PeakMemoryUsage: 296.00 KB - ProbeRows: 94 - ProbeTime: 20.819us - PushDownComputeTime: 0ns - PushDownTime: 0ns - RowsReturned: 94 - RowsReturnedRate: 14.881K /sec EXCHANGE_NODE (id=8):(Active: 5.120ms, % non-child: 15.67%) - BytesReceived: 2.60 KB - ConvertRowBatchTime: 9.205us - DataArrivalWaitTime: 5.23ms - DeserializeRowBatchTimer: 100.752us - FirstBatchArrivalWaitTime: 4.94ms - PeakMemoryUsage: 4.16 KB - RowsReturned: 96 - RowsReturnedRate: 18.747K /sec - SendersBlockedTotalTimer(*): 0ns OLAP_SCAN_NODE (id=0):(Active: 1.124ms, % non-child: 3.44%) - BlockLookupCacheTime: 14.329us - BlockPutCacheTime: 0ns - BytesRead: 17.96 KB - GetNextTime: 1.123ms - MaxWaitScanTime: 86.953us - NumDiskAccess: 1 - NumScanners: 1 - PeakMemoryUsage: 0.00 - RowsRead: 95 - RowsReturned: 94 - RowsReturnedRate: 83.572K /sec - RowsetNum: 2 - RowsetReaderInitTime: 721.83us - ScanCpuTime: 40.610us - ScannerBlockPutTimer: 4.488us - ScannerMaxPendingTimer: 13.28us - SegmentNum: 1 - StartScanTime: 166.708us - TabletCount : 1 - TotalReadThroughput: 0.0 /sec OlapScanner: - BlockConvertTime: 11.840us - BlockFetchTime: 601.202us - ReaderInitTime: 788.363us - RowsDelFiltered: 0 - RowsPushedCondFiltered: 0 - ScanTime: 41.22us - ShowHintsTime_V1: 0ns SegmentIterator: - BitmapIndexFilterTimer: 397ns - BlockLoadTime: 586.104us - BlockSeekCount: 118 - BlockSeekTime: 433.873us - BlocksLoad: 1 - CachedPagesNum: 10 - CompressedBytesRead: 0.00 - DecompressorTimer: 0ns - IOTimer: 0ns - IndexLoadTime_V1: 0ns - NumSegmentFiltered: 0 - NumSegmentTotal: 1 - RawRowsRead: 380 - RowsBitmapIndexFiltered: 0 - RowsBloomFilterFiltered: 0 - RowsConditionsFiltered: 0 - RowsKeyRangeFiltered: 5.373K (5373) - RowsStatsFiltered: 0 - RowsVectorPredFiltered: 285 - TotalPagesNum: 10 - UncompressedBytesRead: 0.00 - VectorPredEvalTime: 3.434us Fragment 3: Instance 21ae6d249284eb0-82f18110a8be71e0 (host=TNetworkAddress(hostname:x, port:x)):(Active: 2.469ms, % non-child: 0.00%) - AverageThreadTokens: 0.00 - FragmentCpuTime: 1.760ms - MemoryLimit: 2.00 GB - PeakMemoryUsage: 36.17 MB - PeakReservation: 36.00 MB - PeakUsedReservation: 0.00 - RowsProduced: 96 BlockMgr: - BlockWritesOutstanding: 0 - BlocksCreated: 0 - BlocksRecycled: 0 - BufferedPins: 0 - BytesWritten: 0.00 - MaxBlockSize: 8.00 MB - TotalBufferWaitTime: 0ns - TotalEncryptionTime: 0ns - TotalIntegrityCheckTime: 0ns - TotalReadBlockTime: 0ns DataStreamSender (dst_id=8, dst_fragments=[021ae6d249284eb0-82f18110a8be71df]):(Active: 791.445us, % non-child: 2.42%) - BytesSent: 2.60 KB - IgnoreRows: 0 - OverallThroughput: 3.2064476013183594 MB/sec - PeakMemoryUsage: 3.20 KB - SerializeBatchTime: 49.708us - UncompressedRowBatchSize: 5.29 KB AGGREGATION_NODE (id=2):(Active: 1.772ms, % non-child: 4.08%) - Probe Method: HashTable Linear Probing - BuildTime: 161.192us - GetResultsTime: 65.809us - HTResize: 0 - HTResizeTime: 2.265us - HashBuckets: 16.384K (16384) - HashCollisions: 0 - HashFailedProbe: 0 - HashFilledBuckets: 96 - HashProbe: 96 - HashTravelLength: 0 - LargestPartitionPercent: 16 - MaxPartitionLevel: 0 - NumRepartitions: 0 - PartitionsCreated: 16 - PeakMemoryUsage: 36.16 MB - RowsProcessed: 96 - RowsRepartitioned: 0 - RowsReturned: 96 - RowsReturnedRate: 54.172K /sec - SpilledPartitions: 0 OLAP_SCAN_NODE (id=1):(Active: 440.772us, % non-child: 1.35%) - BlockLookupCacheTime: 6.303us - BlockPutCacheTime: 0ns - BytesRead: 3.56 KB - GetNextTime: 440.674us - MaxWaitScanTime: 93.655us - NumDiskAccess: 1 - NumScanners: 1 - PeakMemoryUsage: 0.00 - RowsRead: 96 - RowsReturned: 96 - RowsReturnedRate: 217.799K /sec - RowsetNum: 2 - RowsetReaderInitTime: 193.987us - ScanCpuTime: 15.877us - ScannerBlockPutTimer: 7.832us - ScannerMaxPendingTimer: 16.477us - SegmentNum: 1 - StartScanTime: 118.399us - TabletCount : 1 - TotalReadThroughput: 0.0 /sec OlapScanner: - BlockConvertTime: 4.844us - BlockFetchTime: 121.667us - ReaderInitTime: 218.745us - RowsDelFiltered: 0 - RowsPushedCondFiltered: 0 - ScanTime: 16.285us - ShowHintsTime_V1: 0ns SegmentIterator: - BitmapIndexFilterTimer: 183ns - BlockLoadTime: 113.567us - BlockSeekCount: 7 - BlockSeekTime: 70.163us - BlocksLoad: 1 - CachedPagesNum: 4 - CompressedBytesRead: 0.00 - DecompressorTimer: 0ns - IOTimer: 0ns - IndexLoadTime_V1: 0ns - NumSegmentFiltered: 0 - NumSegmentTotal: 1 - RawRowsRead: 111 - RowsBitmapIndexFiltered: 0 - RowsBloomFilterFiltered: 0 - RowsConditionsFiltered: 0 - RowsKeyRangeFiltered: 0 - RowsStatsFiltered: 0 - RowsVectorPredFiltered: 0 - TotalPagesNum: 4 - UncompressedBytesRead: 0.00 - VectorPredEvalTime: 0ns Buffer pool: - AllocTime: 25.710us - CumulativeAllocationBytes: 34.00 MB - CumulativeAllocations: 17 - PeakReservation: 36.00 MB - PeakUnpinnedBytes: 0.00 - PeakUsedReservation: 34.00 MB - ReservationLimit: 80.00 GB四、疑问
doris版本：0.13.11
不加shuffle hint，都是走的broadcast，结果是异常的。
异常能稳定复现，即使我将数据导入到测试进去进行测试，异常依旧稳定复现。
我应该从什么角度去排查这个问题呢？
目前从profile看出，不加shuffle hint 的 profile 在往exchange node id=8 中发送数据的条数貌似被过滤掉了一部分。
加shuffle hint的profile，exchange node id=8中返回的数据条数和接收条数一致，没有被过滤。
不知道原因是否在这个地方。
Beta Was this translation helpful? Give feedback.
All reactions