pigdance opened a new issue #5696:
URL: https://github.com/apache/incubator-doris/issues/5696
3个FE，12个BE，Aggregate
模型明细数据，全表1亿记录，字段数20个，按pday天分区，每个分区500万条，查询一条记录竟然10~30秒,请问有什么优化的建议？
select * from trade where pday='2021-04-22' and
trade_no='M21042213530381263000'
profile信息如下
Query:
Summary:
- Query ID: 6ed4c95535034386-afbacddbc4e959a8
- Start Time: 2021-04-23 07:41:07
- End Time: 2021-04-23 07:41:33
- Total: 26s128ms
- Query Type: Query
- Query State: EOF
- Doris Version: 0.13.0-rc03
- User: root
- Default Db: default_cluster:test
- Sql Statement: select * from trade where pday='2021-04-22' and
trade_no='M21042213530381263000'
- Is Cached: No
Execution Profile 6ed4c95535034386-afbacddbc4e959a8:(Active: 26s128ms, %
non-child: 100.00%)
Fragment 0:
Instance 6ed4c95535034386-afbacddbc4e959af
(host=TNetworkAddress(hostname:x.x.x.223, port:19060)):(Active: 25s961ms, %
non-child: 0.00%)
- AverageThreadTokens: 4607182418800017400.00
- MemoryLimit: 2.00 GB
- PeakMemoryUsage: 12.01 KB
- PeakReservation: 0.00
- PeakUsedReservation: 0.00
- RowsProduced: 1
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=6ed4c95535034386-afbacddbc4e959af):
- AppendBatchTime: 138.460us
- ResultRendTime: 90.332us
- TupleConvertTime: 44.561us
- NumSentRows: 1
EXCHANGE_NODE (id=1):(Active: 25s960ms, % non-child: 99.36%)
- BytesReceived: 354.00 B
- ConvertRowBatchTime: 1.995us
- DataArrivalWaitTime: 25s960ms
- DeserializeRowBatchTimer: 17.204us
- FirstBatchArrivalWaitTime: 3s889ms
- PeakMemoryUsage: 4.01 KB
- RowsReturned: 1
- RowsReturnedRate: 0
- SendersBlockedTotalTimer(*): 0ns
Fragment 1:
Instance 6ed4c95535034386-afbacddbc4e959a9
(host=TNetworkAddress(hostname:x.x.x.228, port:19060)):(Active: 5s218ms, %
non-child: 0.00%)
- AverageThreadTokens: 4611686018427387900.00
- MemoryLimit: 2.00 GB
- PeakMemoryUsage: 8.22 KB
- PeakReservation: 0.00
- PeakUsedReservation: 0.00
- RowsProduced: 0
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=1,
dst_fragments=[6ed4c95535034386-afbacddbc4e959af]):(Active: 23.378us, %
non-child: 0.00%)
- BytesSent: 0.00
- IgnoreRows: 0
- OverallThroughput: 0.0 /sec
- PeakMemoryUsage: 224.00 B
- SerializeBatchTime: 0ns
- UncompressedRowBatchSize: 0.00
OLAP_SCAN_NODE (id=0):(Active: 5s217ms, % non-child: 19.97%)
- BitmapIndexFilterTimer: 3.450us
- BlockConvertTime: 2s804ms
- BlockFetchTime: 4s372ms
- BlockLoadTime: 1s561ms
- BlockSeekCount: 1.352K (1352)
- BlockSeekTime: 408.884ms
- BlocksLoad: 1.314K (1314)
- BytesRead: 716.87 MB
- CachedPagesNum: 9.909K (9909)
- CompressedBytesRead: 0.00
- DecompressorTimer: 0ns
- IOTimer: 0ns
- IndexLoadTime: 0ns
- NumDiskAccess: 1
- NumScanners: 2
- NumSegmentFiltered: 0
- NumSegmentTotal: 4
- PeakMemoryUsage: 0.00
- PerReadThreadRawHdfsThroughput: 0.0 /sec
- RawRowsRead: 1.343801M (1343801)
- ReaderInitTime: 46.621ms
- RowsBitmapIndexFiltered: 0
- RowsBloomFilterFiltered: 0
- RowsDelFiltered: 0
- RowsKeyRangeFiltered: 0
- RowsPushedCondFiltered: 0
- RowsRead: 1.342958M (1342958)
- RowsReturned: 0
- RowsReturnedRate: 0
- RowsStatsFiltered: 0
- RowsVectorPredFiltered: 0
- ScanTime: 10s321ms
- ScannerThreadsInvoluntaryContextSwitches: 0
- ScannerThreadsTotalWallClockTime: 0ns
- MaterializeTupleTime(*): 0ns
- ScannerThreadsSysTime: 0ns
- ScannerThreadsUserTime: 0ns
- ScannerThreadsVoluntaryContextSwitches: 0
- ShowHintsTime: 58.809us
- TabletCount : 2
- TotalPagesNum: 9.909K (9909)
- TotalRawReadTime(*): 0ns
- TotalReadThroughput: 0.0 /sec
- UncompressedBytesRead: 0.00
- VectorPredEvalTime: 11.204ms
Instance 6ed4c95535034386-afbacddbc4e959aa
(host=TNetworkAddress(hostname:x.x.x.224, port:19060)):(Active: 25s957ms, %
non-child: 0.00%)
- AverageThreadTokens: 4611686018427387900.00
- MemoryLimit: 2.00 GB
- PeakMemoryUsage: 8.22 KB
- PeakReservation: 0.00
- PeakUsedReservation: 0.00
- RowsProduced: 0
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=1,
dst_fragments=[6ed4c95535034386-afbacddbc4e959af]):(Active: 20.331us, %
non-child: 0.00%)
- BytesSent: 0.00
- IgnoreRows: 0
- OverallThroughput: 0.0 /sec
- PeakMemoryUsage: 224.00 B
- SerializeBatchTime: 0ns
- UncompressedRowBatchSize: 0.00
OLAP_SCAN_NODE (id=0):(Active: 25s957ms, % non-child: 99.35%)
- BitmapIndexFilterTimer: 2.801us
- BlockConvertTime: 881.529ms
- BlockFetchTime: 23s56ms
- BlockLoadTime: 22s172ms
- BlockSeekCount: 670
- BlockSeekTime: 6s786ms
- BlocksLoad: 658
- BytesRead: 358.42 MB
- CachedPagesNum: 4.845K (4845)
- CompressedBytesRead: 2.67 MB
- DecompressorTimer: 370.675ms
- IOTimer: 3s126ms
- IndexLoadTime: 0ns
- NumDiskAccess: 1
- NumScanners: 1
- NumSegmentFiltered: 0
- NumSegmentTotal: 2
- PeakMemoryUsage: 0.00
- PerReadThreadRawHdfsThroughput: 0.0 /sec
- RawRowsRead: 671.87K (671870)
- ReaderInitTime: 164.892ms
- RowsBitmapIndexFiltered: 0
- RowsBloomFilterFiltered: 0
- RowsDelFiltered: 0
- RowsKeyRangeFiltered: 0
- RowsPushedCondFiltered: 0
- RowsRead: 671.487K (671487)
- RowsReturned: 0
- RowsReturnedRate: 0
- RowsStatsFiltered: 0
- RowsVectorPredFiltered: 0
- ScanTime: 25s769ms
- ScannerThreadsInvoluntaryContextSwitches: 0
- ScannerThreadsTotalWallClockTime: 0ns
- MaterializeTupleTime(*): 0ns
- ScannerThreadsSysTime: 0ns
- ScannerThreadsUserTime: 0ns
- ScannerThreadsVoluntaryContextSwitches: 0
- ShowHintsTime: 47.147us
- TabletCount : 1
- TotalPagesNum: 4.957K (4957)
- TotalRawReadTime(*): 0ns
- TotalReadThroughput: 0.0 /sec
- UncompressedBytesRead: 5.51 MB
- VectorPredEvalTime: 6.236ms
Instance 6ed4c95535034386-afbacddbc4e959ab
(host=TNetworkAddress(hostname:x.x.x.226, port:19060)):(Active: 4s323ms, %
non-child: 0.00%)
- AverageThreadTokens: 4611686018427387900.00
- MemoryLimit: 2.00 GB
- PeakMemoryUsage: 8.22 KB
- PeakReservation: 0.00
- PeakUsedReservation: 0.00
- RowsProduced: 0
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=1,
dst_fragments=[6ed4c95535034386-afbacddbc4e959af]):(Active: 25.907us, %
non-child: 0.00%)
- BytesSent: 0.00
- IgnoreRows: 0
- OverallThroughput: 0.0 /sec
- PeakMemoryUsage: 224.00 B
- SerializeBatchTime: 0ns
- UncompressedRowBatchSize: 0.00
OLAP_SCAN_NODE (id=0):(Active: 4s323ms, % non-child: 16.55%)
- BitmapIndexFilterTimer: 3.830us
- BlockConvertTime: 1s781ms
- BlockFetchTime: 3s523ms
- BlockLoadTime: 1s736ms
- BlockSeekCount: 1.35K (1350)
- BlockSeekTime: 531.870ms
- BlocksLoad: 1.315K (1315)
- BytesRead: 717.11 MB
- CachedPagesNum: 9.918K (9918)
- CompressedBytesRead: 0.00
- DecompressorTimer: 0ns
- IOTimer: 0ns
- IndexLoadTime: 0ns
- NumDiskAccess: 1
- NumScanners: 2
- NumSegmentFiltered: 0
- NumSegmentTotal: 4
- PeakMemoryUsage: 0.00
- PerReadThreadRawHdfsThroughput: 0.0 /sec
- RawRowsRead: 1.34425M (1344250)
- ReaderInitTime: 160.742ms
- RowsBitmapIndexFiltered: 0
- RowsBloomFilterFiltered: 0
- RowsDelFiltered: 0
- RowsKeyRangeFiltered: 0
- RowsPushedCondFiltered: 0
- RowsRead: 1.343424M (1343424)
- RowsReturned: 0
- RowsReturnedRate: 0
- RowsStatsFiltered: 0
- RowsVectorPredFiltered: 0
- ScanTime: 8s370ms
- ScannerThreadsInvoluntaryContextSwitches: 0
- ScannerThreadsTotalWallClockTime: 0ns
- MaterializeTupleTime(*): 0ns
- ScannerThreadsSysTime: 0ns
- ScannerThreadsUserTime: 0ns
- ScannerThreadsVoluntaryContextSwitches: 0
- ShowHintsTime: 97.187us
- TabletCount : 2
- TotalPagesNum: 9.918K (9918)
- TotalRawReadTime(*): 0ns
- TotalReadThroughput: 0.0 /sec
- UncompressedBytesRead: 0.00
- VectorPredEvalTime: 10.905ms
Instance 6ed4c95535034386-afbacddbc4e959ae
(host=TNetworkAddress(hostname:x.x.x.222, port:19060)):(Active: 8s393ms, %
non-child: 0.00%)
- AverageThreadTokens: 4611686018427387900.00
- MemoryLimit: 2.00 GB
- PeakMemoryUsage: 8.22 KB
- PeakReservation: 0.00
- PeakUsedReservation: 0.00
- RowsProduced: 0
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=1,
dst_fragments=[6ed4c95535034386-afbacddbc4e959af]):(Active: 36.392us, %
non-child: 0.00%)
- BytesSent: 0.00
- IgnoreRows: 0
- OverallThroughput: 0.0 /sec
- PeakMemoryUsage: 224.00 B
- SerializeBatchTime: 0ns
- UncompressedRowBatchSize: 0.00
OLAP_SCAN_NODE (id=0):(Active: 8s392ms, % non-child: 32.12%)
- BitmapIndexFilterTimer: 2.456us
- BlockConvertTime: 824.752ms
- BlockFetchTime: 5s227ms
- BlockLoadTime: 4s399ms
- BlockSeekCount: 677
- BlockSeekTime: 1s374ms
- BlocksLoad: 657
- BytesRead: 358.60 MB
- CachedPagesNum: 2.985K (2985)
- CompressedBytesRead: 43.77 MB
- DecompressorTimer: 1s857ms
- IOTimer: 1s78ms
- IndexLoadTime: 0ns
- NumDiskAccess: 1
- NumScanners: 1
- NumSegmentFiltered: 0
- NumSegmentTotal: 2
- PeakMemoryUsage: 0.00
- PerReadThreadRawHdfsThroughput: 0.0 /sec
- RawRowsRead: 672.215K (672215)
- ReaderInitTime: 688.906ms
- RowsBitmapIndexFiltered: 0
- RowsBloomFilterFiltered: 0
- RowsDelFiltered: 0
- RowsKeyRangeFiltered: 0
- RowsPushedCondFiltered: 0
- RowsRead: 671.776K (671776)
- RowsReturned: 0
- RowsReturnedRate: 0
- RowsStatsFiltered: 0
- RowsVectorPredFiltered: 0
- ScanTime: 7s690ms
- ScannerThreadsInvoluntaryContextSwitches: 0
- ScannerThreadsTotalWallClockTime: 0ns
- MaterializeTupleTime(*): 0ns
- ScannerThreadsSysTime: 0ns
- ScannerThreadsUserTime: 0ns
- ScannerThreadsVoluntaryContextSwitches: 0
- ShowHintsTime: 69.464us
- TabletCount : 1
- TotalPagesNum: 4.963K (4963)
- TotalRawReadTime(*): 0ns
- TotalReadThroughput: 0.0 /sec
- UncompressedBytesRead: 88.58 MB
- VectorPredEvalTime: 6.970ms
Instance 6ed4c95535034386-afbacddbc4e959ac
(host=TNetworkAddress(hostname:x.x.x.225, port:19060)):(Active: 3s885ms, %
non-child: 0.00%)
- AverageThreadTokens: 4611686018427387900.00
- MemoryLimit: 2.00 GB
- PeakMemoryUsage: 1.01 MB
- PeakReservation: 0.00
- PeakUsedReservation: 0.00
- RowsProduced: 1
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=1,
dst_fragments=[6ed4c95535034386-afbacddbc4e959af]):(Active: 149.541us, %
non-child: 0.00%)
- BytesSent: 354.00 B
- IgnoreRows: 0
- OverallThroughput: 2.2575788497924805 MB/sec
- PeakMemoryUsage: 224.00 B
- SerializeBatchTime: 27.163us
- UncompressedRowBatchSize: 764.00 B
OLAP_SCAN_NODE (id=0):(Active: 3s884ms, % non-child: 14.87%)
- BitmapIndexFilterTimer: 1.938us
- BlockConvertTime: 692.681ms
- BlockFetchTime: 1s403ms
- BlockLoadTime: 708.148ms
- BlockSeekCount: 676
- BlockSeekTime: 213.345ms
- BlocksLoad: 657
- BytesRead: 358.43 MB
- CachedPagesNum: 4.954K (4954)
- CompressedBytesRead: 0.00
- DecompressorTimer: 0ns
- IOTimer: 0ns
- IndexLoadTime: 0ns
- NumDiskAccess: 1
- NumScanners: 1
- NumSegmentFiltered: 0
- NumSegmentTotal: 2
- PeakMemoryUsage: 0.00
- PerReadThreadRawHdfsThroughput: 0.0 /sec
- RawRowsRead: 671.894K (671894)
- ReaderInitTime: 35.774ms
- RowsBitmapIndexFiltered: 0
- RowsBloomFilterFiltered: 0
- RowsDelFiltered: 0
- RowsKeyRangeFiltered: 0
- RowsPushedCondFiltered: 0
- RowsRead: 671.494K (671494)
- RowsReturned: 1
- RowsReturnedRate: 0
- RowsStatsFiltered: 0
- RowsVectorPredFiltered: 0
- ScanTime: 3s839ms
- ScannerThreadsInvoluntaryContextSwitches: 0
- ScannerThreadsTotalWallClockTime: 0ns
- MaterializeTupleTime(*): 0ns
- ScannerThreadsSysTime: 0ns
- ScannerThreadsUserTime: 0ns
- ScannerThreadsVoluntaryContextSwitches: 0
- ShowHintsTime: 45.45us
- TabletCount : 1
- TotalPagesNum: 4.954K (4954)
- TotalRawReadTime(*): 0ns
- TotalReadThroughput: 0.0 /sec
- UncompressedBytesRead: 0.00
- VectorPredEvalTime: 5.274ms
Instance 6ed4c95535034386-afbacddbc4e959ad
(host=TNetworkAddress(hostname:x.x.x.220, port:19060)):(Active: 3s625ms, %
non-child: 0.00%)
- AverageThreadTokens: 4611686018427387900.00
- MemoryLimit: 2.00 GB
- PeakMemoryUsage: 8.22 KB
- PeakReservation: 0.00
- PeakUsedReservation: 0.00
- RowsProduced: 0
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=1,
dst_fragments=[6ed4c95535034386-afbacddbc4e959af]):(Active: 65.619us, %
non-child: 0.00%)
- BytesSent: 0.00
- IgnoreRows: 0
- OverallThroughput: 0.0 /sec
- PeakMemoryUsage: 224.00 B
- SerializeBatchTime: 0ns
- UncompressedRowBatchSize: 0.00
OLAP_SCAN_NODE (id=0):(Active: 3s625ms, % non-child: 13.87%)
- BitmapIndexFilterTimer: 1.661us
- BlockConvertTime: 645.460ms
- BlockFetchTime: 1s373ms
- BlockLoadTime: 726.55ms
- BlockSeekCount: 677
- BlockSeekTime: 277.301ms
- BlocksLoad: 657
- BytesRead: 358.48 MB
- CachedPagesNum: 4.961K (4961)
- CompressedBytesRead: 0.00
- DecompressorTimer: 0ns
- IOTimer: 0ns
- IndexLoadTime: 0ns
- NumDiskAccess: 1
- NumScanners: 1
- NumSegmentFiltered: 0
- NumSegmentTotal: 2
- PeakMemoryUsage: 0.00
- PerReadThreadRawHdfsThroughput: 0.0 /sec
- RawRowsRead: 671.982K (671982)
- ReaderInitTime: 67.714ms
- RowsBitmapIndexFiltered: 0
- RowsBloomFilterFiltered: 0
- RowsDelFiltered: 0
- RowsKeyRangeFiltered: 0
- RowsPushedCondFiltered: 0
- RowsRead: 671.615K (671615)
- RowsReturned: 0
- RowsReturnedRate: 0
- RowsStatsFiltered: 0
- RowsVectorPredFiltered: 0
- ScanTime: 3s544ms
- ScannerThreadsInvoluntaryContextSwitches: 0
- ScannerThreadsTotalWallClockTime: 0ns
- MaterializeTupleTime(*): 0ns
- ScannerThreadsSysTime: 0ns
- ScannerThreadsUserTime: 0ns
- ScannerThreadsVoluntaryContextSwitches: 0
- ShowHintsTime: 29.913us
- TabletCount : 1
- TotalPagesNum: 4.961K (4961)
- TotalRawReadTime(*): 0ns
- TotalReadThroughput: 0.0 /sec
- UncompressedBytesRead: 0.00
- VectorPredEvalTime: 5.40ms
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
