【详述】查询以前的分区 有时候查询200多ms，有时候20多s，查询不稳定。
【业务影响】
【StarRocks版本】例如：2.4.2
【集群规模】例如：2fe +6be（fe与be混部）
- 慢查询：
- Profile信息
慢查询.txt (56.9 KB)
- Profile信息
Query:
Summary:
- Query ID: a310d35f-bd10-11ef-a127-02fdd9c332a9
- Start Time: 2024-12-18 07:21:02
- End Time: 2024-12-18 07:21:06
- Total: 4s414ms
- Query Type: Query
- Query State: Finished
- StarRocks Version: 3.1.3-384ba23
- User: root
- Default Db
- Sql Statement: /* ApplicationName=DBeaver 24.1.0 - SQLEditor <Script-48.sql< */ select * from ads.ads_indexbuy_market_symbol_target_rt
where exchange_id =1667048073731874835
and wsname =‘0xaec085e5a5ce8d96a7bdd3eb3a62445d4f6ce703’
LIMIT 0, 200
- Variables: parallel_fragment_exec_instance_num=1,max_parallel_scan_instance_num=-1,pipeline_dop=0,enable_adaptive_sink_dop=true,enable_runtime_adaptive_dop=false,runtime_profile_report_interval=10
- NonDefaultSessionVariables: {“sql_mode_v2”:{“defaultValue”:32,“actualValue”:2097184},“character_set_results”:{“defaultValue”:“utf8”,“actualValue”:“NULL”},“prefer_compute_node”:{“defaultValue”:false,“actualValue”:true},“enable_query_cache”:{“defaultValue”:false,“actualValue”:true},“max_pipeline_dop”:{“defaultValue”:64,“actualValue”:1000},“enable_adaptive_sink_dop”:{“defaultValue”:false,“actualValue”:true},“enable_profile”:{“defaultValue”:false,“actualValue”:true}}
- Collect Profile Time: 0
Planner:
- Analyzer: 0ms / 1
- CTEUniqueChecker: 0ms / 1
- CoordDeliverExec: 2ms / 1
- CoordPrepareExec: 0ms / 1
- ExecPlanBuild: 0ms / 1
- InputDependenciesChecker: 0ms / 1
- Optimizer: 1ms / 1
- Total: 1ms / 1
- Transformer: 0ms / 1
- TypeChecker: 0ms / 1
Optimizer:
- CostBaseOptimize: 0ms / 1
- PhysicalRewrite: 0ms / 1
- PlanValidate: 0ms / 1
- RuleBaseOptimize: 0ms / 1
- preprocessMvs: 0ms / 1
Execution:
- Topology: {“rootId”:1,“nodes”:[{“id”:1,“name”:“EXCHANGE”,“properties”:{“sinkIds”:[],“displayMem”:true},“children”:[0]},{“id”:0,“name”:“OLAP_SCAN”,“properties”:{“sinkIds”:[1],“displayMem”:true},“children”:[]}]}
- FrontendProfileMergeTime: 2.462ms
- QueryAllocatedMemoryUsage: 9.318 MB
- QueryCumulativeCpuTime: 714.002us
- QueryCumulativeNetworkTime: 1.620ms
- QueryCumulativeOperatorTime: 3.987ms
- QueryCumulativeScanTime: 965.639us
- QueryDeallocatedMemoryUsage: 9.285 MB
- QueryExecutionWallTime: 9.681ms
- QueryPeakMemoryUsage: 7.302 MB
- QueryPeakScheduleTime: 2.660ms
- QuerySpillBytes: 0.000 B
- ResultDeliverTime: 0ns
Fragment 0:
- BackendAddresses: xxxxx:9060
- InstanceIds: null
- BackendNum: 1
- BackendProfileMergeTime: 609.785us
- FragmentInstancePrepareTime: 235.987us
- prepare-fragment-ctx: 24.772us
- prepare-pipeline-driver: 59.204us
- prepare-query-ctx: 8.751us
- prepare-runtime-state: 59.204us
- InitialProcessDriverCount: 64
- InitialProcessMem: 15.232 GB
- InstanceAllocatedMemoryUsage: 33.000 KB
- InstanceDeallocatedMemoryUsage: 24.156 KB
- InstanceNum: 1
- InstancePeakMemoryUsage: 17.844 KB
- QueryMemoryLimit: 8.000 GB
Pipeline (id=0):
- ActiveTime: 35.180us
- __MAX_OF_ActiveTime: 126.579us
- __MIN_OF_ActiveTime: 2.851us
- BlockByInputEmpty: 0
- BlockByOutputFull: 0
- BlockByPrecondition: 0
- DegreeOfParallelism: 4
- DriverPrepareTime: 60.446us
- __MAX_OF_DriverPrepareTime: 92.217us
- __MIN_OF_DriverPrepareTime: 47.263us
- DriverTotalTime: 8.529ms
- __MAX_OF_DriverTotalTime: 8.571ms
- __MIN_OF_DriverTotalTime: 8.501ms
- OverheadTime: 4.160us
- __MAX_OF_OverheadTime: 16.642us
- __MIN_OF_OverheadTime: 0ns
- PeakDriverQueueSize: 6
- __MAX_OF_PeakDriverQueueSize: 3
- __MIN_OF_PeakDriverQueueSize: 0
- PendingTime: 7.527ms
- __MAX_OF_PendingTime: 7.529ms
- __MIN_OF_PendingTime: 7.526ms
- InputEmptyTime: 7.527ms
- __MAX_OF_InputEmptyTime: 7.529ms
- __MIN_OF_InputEmptyTime: 7.526ms
- FirstInputEmptyTime: 7.527ms
- __MAX_OF_FirstInputEmptyTime: 7.529ms
- __MIN_OF_FirstInputEmptyTime: 7.526ms
- FollowupInputEmptyTime: 0ns
- OutputFullTime: 0ns
- PendingFinishTime: 0ns
- PreconditionBlockTime: 0ns
- ScheduleCount: 4
- __MAX_OF_ScheduleCount: 1
- __MIN_OF_ScheduleCount: 1
- ScheduleTime: 966.655us
- __MAX_OF_ScheduleTime: 1.034ms
- __MIN_OF_ScheduleTime: 848.212us
- TotalDegreeOfParallelism: 4
- YieldByLocalWait: 0
- YieldByPreempt: 0
- YieldByTimeLimit: 0
RESULT_SINK (plan_node_id=-1):
CommonMetrics:
- IsFinalSink
- CloseTime: 2.780us
- __MAX_OF_CloseTime: 8.901us
- __MIN_OF_CloseTime: 490ns
- OperatorAllocatedMemoryUsage: 7.695 KB
- __MAX_OF_OperatorAllocatedMemoryUsage: 7.305 KB
- __MIN_OF_OperatorAllocatedMemoryUsage: 0.000 B
- OperatorDeallocatedMemoryUsage: 8.031 KB
- __MAX_OF_OperatorDeallocatedMemoryUsage: 7.859 KB
- __MIN_OF_OperatorDeallocatedMemoryUsage: 0.000 B
- OperatorPeakMemoryUsage: 56.000 B
- __MAX_OF_OperatorPeakMemoryUsage: 224.000 B
- __MIN_OF_OperatorPeakMemoryUsage: 0.000 B
- OperatorTotalTime: 13.569us
- __MAX_OF_OperatorTotalTime: 43.823us
- __MIN_OF_OperatorTotalTime: 552ns
- PrepareTime: 14.356us
- __MAX_OF_PrepareTime: 20.862us
- __MIN_OF_PrepareTime: 10.610us
- PullChunkNum: 0
- PullRowNum: 0
- PullTotalTime: 0ns
- PushChunkNum: 1
- __MAX_OF_PushChunkNum: 1
- __MIN_OF_PushChunkNum: 0
- PushRowNum: 1
- __MAX_OF_PushRowNum: 1
- __MIN_OF_PushRowNum: 0
- PushTotalTime: 10.688us
- __MAX_OF_PushTotalTime: 42.752us
- __MIN_OF_PushTotalTime: 0ns
- SetFinishedTime: 25ns
- __MAX_OF_SetFinishedTime: 31ns
- __MIN_OF_SetFinishedTime: 20ns
- SetFinishingTime: 75ns
- __MAX_OF_SetFinishingTime: 200ns
- __MIN_OF_SetFinishingTime: 30ns
UniqueMetrics:
LIMIT (plan_node_id=1):
CommonMetrics:
- CloseTime: 329ns
- __MAX_OF_CloseTime: 410ns
- __MIN_OF_CloseTime: 229ns
- OperatorAllocatedMemoryUsage: 0.000 B
- OperatorDeallocatedMemoryUsage: 8.000 B
- __MAX_OF_OperatorDeallocatedMemoryUsage: 8.000 B
- __MIN_OF_OperatorDeallocatedMemoryUsage: 0.000 B
- OperatorPeakMemoryUsage: 0.000 B
- OperatorTotalTime: 675ns
- __MAX_OF_OperatorTotalTime: 1.339us
- __MIN_OF_OperatorTotalTime: 401ns
- PrepareTime: 10.366us
- __MAX_OF_PrepareTime: 11.441us
- __MIN_OF_PrepareTime: 9.271us
- PullChunkNum: 1
- __MAX_OF_PullChunkNum: 1
- __MIN_OF_PullChunkNum: 0
- PullRowNum: 1
- __MAX_OF_PullRowNum: 1
- __MIN_OF_PullRowNum: 0
- PullTotalTime: 50ns
- __MAX_OF_PullTotalTime: 200ns
- __MIN_OF_PullTotalTime: 0ns
- PushChunkNum: 1
- __MAX_OF_PushChunkNum: 1
- __MIN_OF_PushChunkNum: 0
- PushRowNum: 1
- __MAX_OF_PushRowNum: 1
- __MIN_OF_PushRowNum: 0
- PushTotalTime: 202ns
- __MAX_OF_PushTotalTime: 810ns
- __MIN_OF_PushTotalTime: 0ns
- SetFinishedTime: 30ns
- __MAX_OF_SetFinishedTime: 40ns
- __MIN_OF_SetFinishedTime: 20ns
- SetFinishingTime: 62ns
- __MAX_OF_SetFinishingTime: 80ns
- __MIN_OF_SetFinishingTime: 40ns
UniqueMetrics:
EXCHANGE_SOURCE (plan_node_id=1):
CommonMetrics:
- CloseTime: 3.020us
- __MAX_OF_CloseTime: 3.980us
- __MIN_OF_CloseTime: 260ns
- JoinRuntimeFilterEvaluate: 0
- JoinRuntimeFilterHashTime: 0ns
- JoinRuntimeFilterInputRows: 0
- JoinRuntimeFilterOutputRows: 0
- JoinRuntimeFilterTime: 0ns
- OperatorAllocatedMemoryUsage: 16.047 KB
- __MAX_OF_OperatorAllocatedMemoryUsage: 14.172 KB
- __MIN_OF_OperatorAllocatedMemoryUsage: 640.000 B
- OperatorDeallocatedMemoryUsage: 3.516 KB
- __MAX_OF_OperatorDeallocatedMemoryUsage: 3.219 KB
- __MIN_OF_OperatorDeallocatedMemoryUsage: 64.000 B
- OperatorPeakMemoryUsage: 3.133 KB
- __MAX_OF_OperatorPeakMemoryUsage: 10.953 KB
- __MIN_OF_OperatorPeakMemoryUsage: 464.000 B
- OperatorTotalTime: 20.272us
- __MAX_OF_OperatorTotalTime: 64.775us
- __MIN_OF_OperatorTotalTime: 3.970us
- PrepareTime: 16.948us
- __MAX_OF_PrepareTime: 35.772us
- __MIN_OF_PrepareTime: 9.201us
- PullChunkNum: 1
- __MAX_OF_PullChunkNum: 1
- __MIN_OF_PullChunkNum: 0
- PullRowNum: 1
- __MAX_OF_PullRowNum: 1
- __MIN_OF_PullRowNum: 0
- PullTotalTime: 16.063us
- __MAX_OF_PullTotalTime: 64.255us
- __MIN_OF_PullTotalTime: 0ns
- PushChunkNum: 0
- PushRowNum: 0
- PushTotalTime: 0ns
- RuntimeBloomFilterNum: 0
- RuntimeInFilterNum: 0
- SetFinishedTime: 37ns
- __MAX_OF_SetFinishedTime: 80ns
- __MIN_OF_SetFinishedTime: 20ns
- SetFinishingTime: 1.150us
- __MAX_OF_SetFinishingTime: 4.221us
- __MIN_OF_SetFinishingTime: 70ns
UniqueMetrics:
- BufferUnplugCount: 0
- BytesPassThrough: 0.000 B
- BytesReceived: 893.000 B
- ClosureBlockCount: 0
- ClosureBlockTime: 0ns
- DecompressChunkTime: 0ns
- DeserializeChunkTime: 63.134us
- PeakBufferMemoryBytes: 893.000 B
- ReceiverProcessTotalTime: 27.972us
- RequestReceived: 1
- SenderTotalTime: 27.802us
- SenderWaitLockTime: 60ns
Fragment 1:
- BackendAddresses: xxxxx:9060
- InstanceIds: null
- BackendNum: 1
- BackendProfileMergeTime: 1.364ms
- FragmentInstancePrepareTime: 234.464us
- prepare-fragment-ctx: 13.350us
- prepare-pipeline-driver: 120.042us
- prepare-query-ctx: 6.750us
- prepare-runtime-state: 120.042us
- InitialProcessDriverCount: 77
- InitialProcessMem: 13.633 GB
- InstanceAllocatedMemoryUsage: 9.286 MB
- InstanceDeallocatedMemoryUsage: 9.262 MB
- InstanceNum: 1
- InstancePeakMemoryUsage: 7.293 MB
- QueryMemoryLimit: 8.000 GB
Pipeline (id=1):
- ActiveTime: 599.742us
- BlockByInputEmpty: 2
- BlockByOutputFull: 0
- BlockByPrecondition: 0
- DegreeOfParallelism: 1
- DriverPrepareTime: 138.212us
- DriverTotalTime: 5.953ms
- OverheadTime: 0ns
- PeakDriverQueueSize: 2
- PendingTime: 2.693ms
- InputEmptyTime: 1.057ms
- FirstInputEmptyTime: 154.103us
- FollowupInputEmptyTime: 903.747us
- OutputFullTime: 0ns
- PendingFinishTime: 1.638ms
- PreconditionBlockTime: 0ns
- ScheduleCount: 3
- ScheduleTime: 2.660ms
- TotalDegreeOfParallelism: 1
- YieldByLocalWait: 0
- YieldByPreempt: 0
- YieldByTimeLimit: 0
EXCHANGE_SINK (plan_node_id=1):
CommonMetrics:
- CloseTime: 14.400us
- OperatorAllocatedMemoryUsage: 11.266 KB
- OperatorDeallocatedMemoryUsage: 6.953 KB
- OperatorPeakMemoryUsage: 4.313 KB
- OperatorTotalTime: 91.622us
- PrepareTime: 56.361us
- PullChunkNum: 0
- PullRowNum: 0
- PullTotalTime: 0ns
- PushChunkNum: 1
- PushRowNum: 1
- PushTotalTime: 27.570us
- SetFinishedTime: 51ns
- SetFinishingTime: 49.601us
UniqueMetrics:
- ChannelNum: 1
- DestFragments: a310d35fbd1011ef-a12702fdd9c332ab
- DestID: 1
- PartType: UNPARTITIONED
- BytesPassThrough: 0.000 B
- BytesSent: 893.000 B
- CompressTime: 0ns
- NetworkBandwidth: 538.065 KB/sec
- NetworkTime: 1.620ms
- OverallThroughput: 523.215 KB/sec
- OverallTime: 1.666ms
- RequestSent: 1
- RpcAvgTime: 810.374us
- RpcCount: 2
- SerializeChunkTime: 23.690us
- ShuffleHashTime: 0ns
- UncompressedBytes: 893.000 B
- WaitTime: 2.733ms
CHUNK_ACCUMULATE (plan_node_id=0):
CommonMetrics:
- IsSubordinate
- CloseTime: 400ns
- OperatorAllocatedMemoryUsage: 0.000 B
- OperatorDeallocatedMemoryUsage: 8.000 B
- OperatorPeakMemoryUsage: 0.000 B
- OperatorTotalTime: 3.060us
- PrepareTime: 14.360us
- PullChunkNum: 1
- PullRowNum: 1
- PullTotalTime: 120ns
- PushChunkNum: 1
- PushRowNum: 1
- PushTotalTime: 2.110us
- SetFinishedTime: 140ns
- SetFinishingTime: 290ns
UniqueMetrics:
LIMIT (plan_node_id=0):
CommonMetrics:
- CloseTime: 469ns
- OperatorAllocatedMemoryUsage: 0.000 B
- OperatorDeallocatedMemoryUsage: 8.000 B
- OperatorPeakMemoryUsage: 0.000 B
- OperatorTotalTime: 1.170us
- PrepareTime: 14.391us
- PullChunkNum: 1
- PullRowNum: 1
- PullTotalTime: 220ns
- PushChunkNum: 1
- PushRowNum: 1
- PushTotalTime: 370ns
- SetFinishedTime: 51ns
- SetFinishingTime: 60ns
UniqueMetrics:
OLAP_SCAN (plan_node_id=0):
CommonMetrics:
- CloseTime: 614.721us
- JoinRuntimeFilterEvaluate: 0
- JoinRuntimeFilterHashTime: 0ns
- JoinRuntimeFilterInputRows: 0
- JoinRuntimeFilterOutputRows: 0
- JoinRuntimeFilterTime: 0ns
- OperatorAllocatedMemoryUsage: 9.403 MB
- OperatorDeallocatedMemoryUsage: 5.378 MB
- OperatorPeakMemoryUsage: 8.252 MB
- OperatorTotalTime: 1.071ms
- PrepareTime: 24.580us
- PullChunkNum: 1
- PullRowNum: 1
- PullTotalTime: 456.930us
- PushChunkNum: 0
- PushRowNum: 0
- PushTotalTime: 0ns
- RuntimeBloomFilterNum: 0
- RuntimeInFilterNum: 0
- SetFinishedTime: 40ns
- SetFinishingTime: 270ns
UniqueMetrics:
- BufferUnplugThreshold: 32
- MorselQueueType: fixed_morsel_queue
- Predicates: 1: exchange_id = 1667048073731874835, 2: wsname = ‘0xaec085e5a5ce8d96a7bdd3eb3a62445d4f6ce703’
- Rollup: ads_indexbuy_market_symbol_target_rt
- SharedScan: False
- Table: ads_indexbuy_market_symbol_target_rt
- BufferUnplugCount: 0
- BytesRead: 586.000 B
- CachedPagesNum: 80
- ChunkBufferCapacity: 64
- CompressedBytesRead: 0.000 B
- DefaultChunkBufferCapacity: 64
- IOTaskExecTime: 917.468us
- CreateSegmentIter: 97.932us
- DictDecode: 1.930us
- GetDelVec: 2.940us
- GetDeltaColumnGroup: 9.440us
- GetRowsets: 1.900us
- IOTime: 0ns
- LateMaterialize: 138.833us
- ReadPKIndex: 0ns
- SegmentInit: 275.745us
- BitmapIndexFilter: 0ns
- BitmapIndexFilterRows: 0
- BitmapIndexIteratorInit: 12.670us
- BloomFilterFilter: 440ns
- BloomFilterFilterRows: 0
- ColumnIteratorInit: 126.933us
- SegmentRuntimeZoneMapFilterRows: 0
- SegmentZoneMapFilterRows: 0
- ShortKeyFilter: 33.631us
- ShortKeyFilterRows: 0
- ZoneMapIndexFilterRows: 0
- ZoneMapIndexFiter: 7.370us
- SegmentRead: 10.820us
- BlockFetch: 9.681us
- BlockFetchCount: 1
- BlockSeek: 14.349us
- BlockSeekCount: 1
- ChunkCopy: 50ns
- DecompressT: 0ns
- DelVecFilterRows: 0
- PredFilter: 1.360us
- PredFilterRows: 0
- RowsetsReadCount: 12
- SegmentsReadCount: 1
- TotalColumnsDataPageCount: 29
- IOTaskWaitTime: 48.171us
- MorselsCount: 1
- PeakChunkBufferSize: 2
- PeakIOTasks: 1
- PeakScanTaskQueueSize: 0
- PushdownPredicates: 2
- RawRowsRead: 1
- ReadPagesNum: 80
- RowsRead: 1
- ScanTime: 965.639us
- SubmitTaskCount: 1
- TabletCount: 1
- UncompressedBytesRead: 0.000 B
Pipeline (id=0):
- ActiveTime: 124.782us
- BlockByInputEmpty: 0
- BlockByOutputFull: 0
- BlockByPrecondition: 0
- DegreeOfParallelism: 1
- DriverPrepareTime: 89.471us
- DriverTotalTime: 197.834us
- OverheadTime: 1.270us
- PeakDriverQueueSize: 0
- PendingTime: 0ns
- InputEmptyTime: 0ns
- FirstInputEmptyTime: 0ns
- FollowupInputEmptyTime: 0ns
- OutputFullTime: 0ns
- PendingFinishTime: 0ns
- PreconditionBlockTime: 0ns
- ScheduleCount: 1
- ScheduleTime: 73.052us
- TotalDegreeOfParallelism: 1
- YieldByLocalWait: 0
- YieldByPreempt: 0
- YieldByTimeLimit: 0
NOOP_SINK (plan_node_id=0):
CommonMetrics:
- IsSubordinate
- CloseTime: 60ns
- OperatorAllocatedMemoryUsage: 0.000 B
- OperatorDeallocatedMemoryUsage: 0.000 B
- OperatorPeakMemoryUsage: 0.000 B
- OperatorTotalTime: 150ns
- PrepareTime: 25.590us
- PullChunkNum: 0
- PullRowNum: 0
- PullTotalTime: 0ns
- PushChunkNum: 0
- PushRowNum: 0
- PushTotalTime: 0ns
- SetFinishedTime: 20ns
- SetFinishingTime: 70ns
UniqueMetrics:
OLAP_SCAN_PREPARE (plan_node_id=0):
CommonMetrics:
- IsSubordinate
- CloseTime: 4.900us
- OperatorAllocatedMemoryUsage: 14.945 KB
- OperatorDeallocatedMemoryUsage: 5.406 KB
- OperatorPeakMemoryUsage: 9.539 KB
- OperatorTotalTime: 123.362us
- PrepareTime: 27.220us
- PullChunkNum: 0
- PullRowNum: 0
- PullTotalTime: 118.362us
- PushChunkNum: 0
- PushRowNum: 0
- PushTotalTime: 0ns
- RuntimeBloomFilterNum: 0
- RuntimeInFilterNum: 0
- SetFinishedTime: 40ns
- SetFinishingTime: 60ns
UniqueMetrics:
@jingdan 老师， 大佬们，我们测试环境经常有一些表就几条记录，一会20s才返回，一会5ms就可以返回，生产没有这样情况，请问可以怎么排查吗？看了profile都没有超过1s的时间，但是耗时却是10s