Doris版本：2.0.3
在一张聚合表上查询的时候，如果查询一个季度数据，数据量几十万条，几十毫秒就返回了。查询本月数据，大概50条，反而30秒才返回。
建表语句：
CREATE TABLE dw_job_fee_consumption_details (
START_TIME datetime(6) NULL COMMENT '',
QUEUE_TIME datetime(6) NULL COMMENT '',
CLUSTER_ID DECIMAL NULL COMMENT '',
JOB_ID varchar(384) NULL COMMENT '',
CHARGE_MODE varchar(96) REPLACE_IF_NOT_NULL NULL COMMENT '',
CREATE_TIME datetime(6) MAX NULL COMMENT '',
ID bigint(20) MAX NULL COMMENT '',
ACCT_ID bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT '',
USER_ID bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT '',
USER_NAME varchar(300) REPLACE_IF_NOT_NULL NULL COMMENT '',
...聚合数据...
COUNT int(11) REPLACE_IF_NOT_NULL NULL DEFAULT "1" COMMENT ''
) ENGINE=OLAP
AGGREGATE KEY(START_TIME, QUEUE_TIME, CLUSTER_ID, JOB_ID)
COMMENT 'OLAP'
PARTITION BY RANGE(START_TIME)()
DISTRIBUTED BY HASH(START_TIME, QUEUE_TIME, CLUSTER_ID, JOB_ID) BUCKETS 1
PROPERTIES (
"replication_allocation" = "tag.location.default: 3",
"is_being_synced" = "false",
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "MONTH",
"dynamic_partition.time_zone" = "Asia/Shanghai",
"dynamic_partition.start" = "-82",
"dynamic_partition.end" = "3",
"dynamic_partition.prefix" = "p",
"dynamic_partition.replication_allocation" = "tag.location.default: 3",
"dynamic_partition.buckets" = "1",
"dynamic_partition.create_history_partition" = "true",
"dynamic_partition.history_partition_num" = "-1",
"dynamic_partition.hot_partition_num" = "0",
"dynamic_partition.reserved_history_periods" = "NULL",
"dynamic_partition.storage_policy" = "",
"dynamic_partition.storage_medium" = "HDD",
"dynamic_partition.start_day_of_month" = "1",
"storage_format" = "V2",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false"
);
查询sql：
SELECT
fee.CLUSTER_ID AS clusterId,
fee.JOB_ID AS jobId,
fee.QUEUE_TIME AS queueTime,
fee.START_TIME AS startTime,
.....
FROM
dw_job_fee_consumption_details fee
WHERE
create_time >= '2024-11-01 00:00:00'
AND create_time <= '2024-11-05 23:59:59'
AND fee.charge_mode IN ( '', '' )
ORDER BY
START_TIME DESC
LIMIT 0,
20;
慢SQL的Profile:
Query:
Summary:
- Profile ID: f0ec9e0c63934c46-9ee02228c090b799
- Task Type: QUERY
- Start Time: 2024-11-06 16:37:27
- End Time: 2024-11-06 16:37:57
- Total: 30s325ms
- Task State: EOF
- User: root
- Default Db: default_cluster:sacp
- Sql Statement:
Execution Summary:
- Workload Group:
- Analysis Time: 4ms
- Plan Time: 23ms
- JoinReorder Time: N/A
- CreateSingleNode Time: N/A
- QueryDistributed Time: N/A
- Init Scan Node Time: N/A
- Finalize Scan Node Time: N/A
- Get Splits Time: N/A
- Get PARTITIONS Time: N/A
- Get PARTITION FILES Time: N/A
- Create Scan Range Time: N/A
- Schedule Time: 81ms
- Fetch Result Time: 30s216ms
- Write Result Time: 0ms
- Wait and Fetch Result Time: 30s217ms
- Doris Version: doris-2.0.3-rc06-37d31a5
- Is Nereids: Yes
- Is Pipeline: Yes
- Is Cached: No
- Total Instances Num: 9
- Instances Num Per BE: 10.0.16.15:8060:4,10.0.16.38:8060:5
- Parallel Fragment Exec Instance Num: 4
- Trace ID:
Execution Profile f0ec9e0c63934c46-9ee02228c090b799:(Active: 30s326ms, % non-child: 0.00%)
Fragments:
Fragment 0:
Instance f0ec9e0c63934c46-9ee02228c090b7a2 (host=TNetworkAddress(hostname:10.0.16.38, port:9060)):
VDataBufferSender (dst_fragment_instance_id=-f1361f39c6cb3ba--611fddd73f6f485e):
- AppendBatchTime: 0ns
- CopyBufferTime: 0ns
- ResultSendTime: 0ns
- TupleConvertTime: 0ns
- BytesSent: 0.00
- NumSentRows: 0
VSORT_NODE (id=1334):(Active: 194.860us, % non-child: 0.00%)
- TOP-N: true
- Spilled: false
- ChildGetResultTime: 0ns
- GetResultTime: 7.590us
- MemoryUsage:
- PeakMemoryUsage: 0.00
- SortBlocks: 0.00
- MergeBlockTime: 0ns
- PartialSortTime: 0ns
- PartialSortTotalTime: 0ns
- ProjectionTime: 0ns
- RowsReturned: 0
- RowsReturnedRate: 0
BlockSpill:
- BlockBytes: 0.00
- BlockCount: 0
VEXCHANGE_NODE (id=1331):(Active: 3.310us, % non-child: 0.00%)
- BlocksProduced: 0
- BytesReceived: 0.00
- DataArrivalWaitTime: 0ns
- DecompressBytes: 0.00
- DecompressTime: 0ns
- DeserializeRowBatchTimer: 0ns
- FirstBatchArrivalWaitTime: 0ns
- LocalBytesReceived: 0.00
- MemoryUsage:
- Blocks: 0.00
- PeakMemoryUsage: 3.35 KB
- ProjectionTime: 0ns
- RowsProduced: 0
- RowsReturned: 0
- RowsReturnedRate: 0
- SendersBlockedTotalTimer(*): 0ns
PipelineContext:(Active: 30s278ms, % non-child: 0.00%)
- PrepareTime: 1.551ms
- StartTime: 134.470us
Pipeline (pipeline id=0):
PipelineTask (index=0):(Active: 556.680us, % non-child: 0.00%)
- Sink: ResultSinkOperator(dst_id=10000)
- OperatorIds(source2root): [SortSourceOperator(node_id=1334)]
- CoreChangeTimes: 2
- ExecuteTime: 93.370us
- CloseTime: 384.250us
- FinalizeTime: 220ns
- GetBlockTime: 32.540us
- OpenTime: 23.450us
- PrepareTime: 17.350us
- SinkTime: 13.110us
- GetBlockCounter: 1
- NumBlockedBySinkTimes: 0
- NumBlockedBySrcTimes: 1
- NumBlockedTimes: 3
- NumScheduleTimes: 3
- NumYieldTimes: 0
- Task1BeginExecuteTime: 60.856ms
- Task2EosTime: 30s277ms
- Task3SrcPendingFinishOverTime: 30s277ms
- Task4DstPendingFinishOverTime: 30s277ms
- Task5TotalTime: 30s277ms
- Task6ClosePipelineTime: 30s277ms
- TaskCpuTime: 157.550us
- WaitBfTime: 0ns
- WaitSinkTime: 0ns
- WaitSourceTime: 30s216ms
- WaitWorkerTime: 501.400us
Pipeline (pipeline id=1):
PipelineTask (index=1):(Active: 154.210us, % non-child: 0.00%)
- Sink: SortSinkOperator(dst_id=1334)
- OperatorIds(source2root): [ExchangeSourceOperator(node_id=1331)]
- CoreChangeTimes: 2
- ExecuteTime: 71.710us
- CloseTime: 11.260us
- FinalizeTime: 70ns
- GetBlockTime: 13.10us
- OpenTime: 22.570us
- PrepareTime: 29.730us
- SinkTime: 6.570us
- GetBlockCounter: 1
- NumBlockedBySinkTimes: 0
- NumBlockedBySrcTimes: 1
- NumBlockedTimes: 3
- NumScheduleTimes: 3
- NumYieldTimes: 0
- Task1BeginExecuteTime: 60.855ms
- Task2EosTime: 30s277ms
- Task3SrcPendingFinishOverTime: 30s277ms
- Task4DstPendingFinishOverTime: 30s277ms
- Task5TotalTime: 30s277ms
- Task6ClosePipelineTime: 30s277ms
- TaskCpuTime: 134.120us
- WaitBfTime: 0ns
- WaitSinkTime: 0ns
- WaitSourceTime: 30s213ms
- WaitWorkerTime: 3.395ms
Fragment 1:
Instance f0ec9e0c63934c46-9ee02228c090b79a (host=TNetworkAddress(hostname:10.0.16.15, port:9060)):
VDataStreamSender (dst_id=1331, dst_fragments=[{"f0ec9e0c63934c46-9ee02228c090b7a2"}]):(Active: 88.310us, % non-child: 0.00%)
- BlocksSent: 1
- BrpcSendTime: 0ns
- BrpcSendTime.Wait: 0ns
- BytesSent: 0.00
- CompressTime: 0ns
- IgnoreRows: 0
- LocalBytesSent: 0.00
- LocalSendTime: 0ns
- LocalSentRows: 0
- MemoryUsage:
- PeakMemoryUsage: 5.14 KB
- OverallThroughput: 0.0 /sec
- RpcAvgTime: 0ns
- RpcCount: 1
- RpcMaxTime: 0ns
- RpcMinTime: 2562047h47m
- RpcSumTime: 0ns
- SerializeBatchTime: 0ns
- SplitBlockDistributeByChannelTime: 0ns
- SplitBlockHashComputeTime: 0ns
- UncompressedRowBatchSize: 0.00
VNewOlapScanNode(dw_job_fee_consumption_details) (id=1319):(Active: 4.990us, % non-child: 0.00%)
.....Fragement1中耗时不多就省略了.....
对比expalin:
发现可能是执行计划不一样，但是这种不知道怎么优化，求助各位大佬！！！
版本升级到2.0.15后，执行仍然比较慢。不过执行计划都一致了。
耗时的instance:
Instance cf63309b67934b47-a5d21ac487451095 (host=TNetworkAddress(hostname:10.0.16.38, port:9060)):
VDataStreamSender (dst_id=1310, dst_fragments=[{"cf63309b67934b47-a5d21ac48745109b"}]):(Active: 171.960us, % non-child: 0.00%)
- BlocksSent: 0
- BrpcSendTime: 0ns
- BrpcSendTime.Wait: 0ns
- BytesSent: 0.00
- CompressTime: 0ns
- IgnoreRows: 0
- LocalBytesSent: 0.00
- LocalSendTime: 0ns
- LocalSentRows: 0
- MemoryUsage:
- PeakMemoryUsage: 3.81 KB
- OverallThroughput: 0.0 /sec
- RpcAvgTime: 0ns
- RpcCount: 0
- RpcMaxTime: 0ns
- RpcMinTime: 0ns
- RpcSumTime: 0ns
- SerializeBatchTime: 0ns
- SplitBlockDistributeByChannelTime: 0ns
- SplitBlockHashComputeTime: 0ns
- UncompressedRowBatchSize: 0.00
VSORT_NODE (id=1307):(Active: 921.10us, % non-child: 0.00%)
- TOP-N: true
- Spilled: false
- ChildGetResultTime: 0ns
- GetResultTime: 4.670us
- MemoryUsage:
- PeakMemoryUsage: 0.00
- SortBlocks: 0.00
- MergeBlockTime: 0ns
- PartialSortTime: 0ns
- PartialSortTotalTime: 0ns
- ProjectionTime: 0ns
- RowsReturned: 0
- RowsReturnedRate: 0
BlockSpill:
- BlockBytes: 0.00
- BlockCount: 0
VNewOlapScanNode(dw_job_fee_consumption_details) (id=1296):(Active: 2.510us, % non-child: 0.00%)
- RuntimeFilters: :
- PushDownPredicates: [{CREATE_TIME >= [2024-11-01 00:00:00]}, {CREATE_TIME <= [2024-11-05 23:59:59]}, {CHARGE_MODE IN [AsRequired, Trial]}]
- KeyRanges: ScanKeys:ScanKey=[null(0000-01-01 00:00:00) : 9999-12-31 23:59:59.999999]
- TabletIds: [464846, 464821]
- RemainedPredicates: VectorizedFn[VectorizedFnCall[ge](arguments=CREATE_TIME, DateTimeV2,return=Nullable(UInt8))]{
SlotRef(slot_id=5 type=DATETIMEV2),
VLiteral (name = DateTimeV2, type = DateTimeV2, value = (2024-11-01 00:00:00.000000))}, VectorizedFn[VectorizedFnCall[le](arguments=CREATE_TIME, DateTimeV2,return=Nullable(UInt8))]{
SlotRef(slot_id=5 type=DATETIMEV2),
VLiteral (name = DateTimeV2, type = DateTimeV2, value = (2024-11-05 23:59:59.000000))}, InPredicate(SlotRef(slot_id=4 type=VARCHAR) 0,[VLiteral (name = String, type = String, value = (AsRequired)) VLiteral (name = String, type = String, value = (Trial))])
- UseSpecificThreadToken: False
- AcquireRuntimeFilterTime: 1.40us
- AllocateResourceTime: 798.210us
- GetNextTime: 3.820us
- MaxScannerThreadNum: 2
- MemoryUsage:
- PeakMemoryUsage: 0.00
- NumScanners: 2
- OpenTime: 0ns
- ProcessConjunctTime: 370.970us
- ProjectionTime: 0ns
- RowsReturned: 0
- RowsReturnedRate: 0
- ScanByteRead: 2.72 GB
- ScanRowsRead: 11.255045M (11255045)
- ScannerWorkerWaitTime: 360.770us
- TabletNum: 2
- TotalReadThroughput: 0
VScanner:
- ReadColumns: 此处省略
- PerScannerRunningTime: [172.150us, 54s614ms, ]
- PerScannerRowsRead: [3.25K, 11.25M, ]
- PerScannerWaitTime: [152.340us, 208.430us, ]
- BlockConvertTime: 0ns
- BlockFetchTime: 36s232ms
- DeleteBitmapGetAggTime: 0ns
- MemoryUsage:
- FreeBlocks: 1.00 MB
- QueuedBlocks: 0.00
- NewlyCreateFreeBlocksNum: 1
- ReaderInitTime: 0ns
- RowsDelFiltered: 0
- ScannerBatchWaitTime: 0ns
- ScannerConvertBlockTime: 0ns
- ScannerCpuTime: 54s756ms
- ScannerCtxSchedCount: 3
- ScannerCtxSchedTime: 185.320us
- ScannerFilterTime: 291.437ms
- ScannerGetBlockTime: 54s318ms
- ScannerInitTime: 226.110us
- ScannerPrefilterTime: 0ns
- ScannerSchedCount: 2
SegmentIterator:
- BitmapIndexFilterTimer: 11.970us
- BlockConditionsFilteredBloomFilterTime: 0ns
- BlockConditionsFilteredDictTime: 0ns
- BlockConditionsFilteredTime: 103.850us
- BlockConditionsFilteredZonemapRuntimePredicateTime: 0ns
- BlockConditionsFilteredZonemapTime: 0ns
- BlockInitSeekCount: 149
- BlockInitSeekTime: 4.414ms
- BlockInitTime: 6.130ms
- BlockLoadTime: 5s872ms
- BlocksLoad: 2.791K (2791)
- CachedPagesNum: 40.679K (40679)
- CompressedBytesRead: 115.57 MB
- DecompressorTimer: 584.592ms
- ExprFilterEvalTime: 0ns
- FirstReadSeekCount: 58.191K (58191)
- FirstReadSeekTime: 4s311ms
- FirstReadTime: 5s817ms
- IOTimer: 208.276ms
- InvertedIndexFilterTime: 27.360us
- InvertedIndexQueryBitmapCopyTime: 0ns
- InvertedIndexQueryBitmapOpTime: 0ns
- InvertedIndexQueryCacheHit: 0
- InvertedIndexQueryCacheMiss: 0
- InvertedIndexQueryNullBitmapTime: 0ns
- InvertedIndexQueryTime: 0ns
- InvertedIndexSearcherCacheHit: 0
- InvertedIndexSearcherCacheMiss: 0
- InvertedIndexSearcherOpenTime: 0ns
- InvertedIndexSearcherSearchTime: 0ns
- LazyReadSeekCount: 0
- LazyReadSeekTime: 0ns
- LazyReadTime: 0ns
- NumSegmentFiltered: 0
- NumSegmentTotal: 20
- OutputColumnTime: 7.229ms
- OutputIndexResultColumnTimer: 4.439ms
- RawRowsRead: 11.255045M (11255045)
- RowsBitmapIndexFiltered: 0
- RowsBloomFilterFiltered: 0
- RowsConditionsFiltered: 0
- RowsDictFiltered: 0
- RowsInvertedIndexFiltered: 0
- RowsKeyRangeFiltered: 0
- RowsShortCircuitPredFiltered: 0
- RowsShortCircuitPredInput: 0
- RowsVectorPredFiltered: 0
- RowsVectorPredInput: 0
- RowsZonemapFiltered: 0
- RowsZonemapRuntimePredicateFiltered: 0
- SecondReadTime: 0ns
- ShortPredEvalTime: 0ns
- TotalPagesNum: 63.776K (63776)
- UncompressedBytesRead: 268.47 MB
- VectorPredEvalTime: 0ns
这里是不是有什么策略，引发扫码全表了！！！
