含有bitmap字段sql查询很慢

💬 StarRocks 用户问答 功能使用相关
#1

【详述】含有bitmap字段sql查询很慢,数据量不到500条,通过profile得知QueryMemCache较高,不带条件查询耗时在6s+,带条件通常2s左右,有的会快点不到1s
【背景】刚开始怀疑是数据倾斜,重新建表hash分桶优化,但是没啥效果,后来怀疑是数据没有充分压缩,更改配置后,效果也不明显
【业务影响】由于查询超时,图表相关数据显示不出来
【StarRocks版本】2.5.4
【集群规模】3FE+5BE
【机器信息】FE:4C16G,BE:16C32G
【联系方式】lianweijunlee@126.com
【附件】

  • fe.log/beINFO/相应截图

  • 慢查询：
    Query:

    Summary:

    • Query ID: 30760d79-5115-11ee-84bd-00163e0e6dbb

    • Start Time: 2023-09-12 10:36:34

    • End Time: 2023-09-12 10:36:38

    • Total: 4s384ms

    • Query Type: Query

    • Query State: EOF

    • StarRocks Version: 2.5.4-1021a92

    • User: root

    • Default Db: portrait

    • Sql Statement: select * from biz_user_exec_rel limit 100;

    • QueryCpuCost: 15s3ms

    • QueryMemCost: 3.132GB

    • Variables: parallel_fragment_exec_instance_num=1,max_parallel_scan_instance_num=-1,pipeline_dop=0,enable_adaptive_sink_dop=true

    • Collect Profile Time: 1s643ms

    Planner:

    • Analyzer: 0ms / 1

    • CoordDeliverExec: 8ms / 1

    • CoordPrepareExec: 0ms / 1

    • ExecPlanBuild: 0ms / 1

    • Optimizer: 0ms / 1

    • Total: 0ms / 1

    Optimizer:

     - CostBaseOptimize: 0ms / 1

 - PhysicalRewrite: 0ms / 1

 - RuleBaseOptimize: 0ms / 1

 - preprocessMvs: 0ms / 1

    Execution Profile 30760d79-5115-11ee-84bd-00163e0e6dbb:

    • ExecutionTotalTime: 9s458ms

    Fragment 0:

     - BackendAddresses: 172.17.10.167:9060

 - BackendNum: 1

 - FragmentInstancePrepareTime: 233.851us

   - prepare-fragment-ctx: 41.543us

   - prepare-pipeline-driver: 93.133us

   - prepare-query-ctx: 4.632us

   - prepare-runtime-state: 93.133us

 - InstanceNum: 1

 - PeakMemoryUsage: 1.27 GB

 - QueryMemoryLimit: 16.00 GB

Pipeline (id=0):

   - ActiveTime: 3s269ms

     - __MAX_OF_ActiveTime: 3s787ms

     - __MIN_OF_ActiveTime: 2s686ms

   - BlockByInputEmpty: 8

     - __MAX_OF_BlockByInputEmpty: 2

     - __MIN_OF_BlockByInputEmpty: 2

   - BlockByOutputFull: 0

   - BlockByPrecondition: 0

   - DegreeOfParallelism: 4

   - DriverPrepareTime: 62.964us

     - __MAX_OF_DriverPrepareTime: 128.154us

     - __MIN_OF_DriverPrepareTime: 40.414us

   - DriverTotalTime: 3s877ms

     - __MAX_OF_DriverTotalTime: 4s381ms

     - __MIN_OF_DriverTotalTime: 3s326ms

   - OverheadTime: 944.298ms

     - __MAX_OF_OverheadTime: 1s607ms

     - __MIN_OF_OverheadTime: 644.193ms

   - PendingTime: 605.685ms

     - __MAX_OF_PendingTime: 639.548ms

     - __MIN_OF_PendingTime: 550.158ms

     - InputEmptyTime: 605.907ms

       - __MAX_OF_InputEmptyTime: 639.571ms

       - __MIN_OF_InputEmptyTime: 550.169ms

       - FirstInputEmptyTime: 117.291ms

         - __MAX_OF_FirstInputEmptyTime: 117.291ms

         - __MIN_OF_FirstInputEmptyTime: 117.290ms

       - FollowupInputEmptyTime: 488.616ms

         - __MAX_OF_FollowupInputEmptyTime: 522.279ms

         - __MIN_OF_FollowupInputEmptyTime: 432.878ms

     - OutputFullTime: 0ns

     - PendingFinishTime: 0ns

     - PreconditionBlockTime: 0ns

   - ScheduleCount: 47

     - __MAX_OF_ScheduleCount: 14

     - __MIN_OF_ScheduleCount: 10

   - ScheduleTime: 1.822ms

     - __MAX_OF_ScheduleTime: 5.644ms

     - __MIN_OF_ScheduleTime: 378.978us

   - TotalDegreeOfParallelism: 4

   - YieldByPreempt: 0

   - YieldByTimeLimit: 36

     - __MAX_OF_YieldByTimeLimit: 12

     - __MIN_OF_YieldByTimeLimit: 7

  RESULT_SINK:

    CommonMetrics:

       - CloseTime: 11.102us

         - __MAX_OF_CloseTime: 39.378us

         - __MIN_OF_CloseTime: 1.531us

       - OperatorTotalTime: 1.16ms

         - __MAX_OF_OperatorTotalTime: 1.716ms

         - __MIN_OF_OperatorTotalTime: 294.792us

       - PeakMemoryUsage: 0.00

       - PrepareTime: 11.689us

         - __MAX_OF_PrepareTime: 15.535us

         - __MIN_OF_PrepareTime: 9.862us

       - PullChunkNum: 0

       - PullRowNum: 0

       - PullTotalTime: 0ns

       - PushChunkNum: 19

         - __MAX_OF_PushChunkNum: 7

         - __MIN_OF_PushChunkNum: 4

       - PushRowNum: 100

         - __MAX_OF_PushRowNum: 33

         - __MIN_OF_PushRowNum: 18

       - PushTotalTime: 1.4ms

         - __MAX_OF_PushTotalTime: 1.714ms

         - __MIN_OF_PushTotalTime: 255.267us

       - SetFinishedTime: 65ns

         - __MAX_OF_SetFinishedTime: 107ns

         - __MIN_OF_SetFinishedTime: 27ns

       - SetFinishingTime: 261ns

         - __MAX_OF_SetFinishingTime: 605ns

         - __MIN_OF_SetFinishingTime: 40ns

    UniqueMetrics:

  LIMIT (plan_node_id=1):

    CommonMetrics:

       - CloseTime: 250ns

         - __MAX_OF_CloseTime: 552ns

         - __MIN_OF_CloseTime: 139ns

       - OperatorTotalTime: 263.536ms

         - __MAX_OF_OperatorTotalTime: 636.460ms

         - __MIN_OF_OperatorTotalTime: 3.702us

       - PeakMemoryUsage: 0.00

       - PrepareTime: 9.654us

         - __MAX_OF_PrepareTime: 12.115us

         - __MIN_OF_PrepareTime: 6.10us

       - PullChunkNum: 19

         - __MAX_OF_PullChunkNum: 7

         - __MIN_OF_PullChunkNum: 4

       - PullRowNum: 100

         - __MAX_OF_PullRowNum: 33

         - __MIN_OF_PullRowNum: 18

       - PullTotalTime: 1.285us

         - __MAX_OF_PullTotalTime: 1.903us

         - __MIN_OF_PullTotalTime: 747ns

       - PushChunkNum: 20

         - __MAX_OF_PushChunkNum: 7

         - __MIN_OF_PushChunkNum: 4

       - PushRowNum: 109

         - __MAX_OF_PushRowNum: 33

         - __MIN_OF_PushRowNum: 21

       - PushTotalTime: 263.534ms

         - __MAX_OF_PushTotalTime: 636.458ms

         - __MIN_OF_PushTotalTime: 2.474us

       - SetFinishedTime: 30ns

         - __MAX_OF_SetFinishedTime: 31ns

         - __MIN_OF_SetFinishedTime: 30ns

       - SetFinishingTime: 149ns

         - __MAX_OF_SetFinishingTime: 258ns

         - __MIN_OF_SetFinishingTime: 43ns

    UniqueMetrics:

  EXCHANGE_SOURCE (plan_node_id=1):

    CommonMetrics:

       - CloseTime: 1.121us

         - __MAX_OF_CloseTime: 1.641us

         - __MIN_OF_CloseTime: 750ns

       - JoinRuntimeFilterEvaluate: 0

       - JoinRuntimeFilterHashTime: 0ns

       - JoinRuntimeFilterInputRows: 0

       - JoinRuntimeFilterOutputRows: 0

       - JoinRuntimeFilterTime: 0ns

       - OperatorTotalTime: 2s60ms

         - __MAX_OF_OperatorTotalTime: 2s691ms

         - __MIN_OF_OperatorTotalTime: 1s550ms

       - PeakMemoryUsage: 0.00

       - PrepareTime: 27.687us

         - __MAX_OF_PrepareTime: 88.413us

         - __MIN_OF_PrepareTime: 6.829us

       - PullChunkNum: 20

         - __MAX_OF_PullChunkNum: 7

         - __MIN_OF_PullChunkNum: 4

       - PullRowNum: 109

         - __MAX_OF_PullRowNum: 33

         - __MIN_OF_PullRowNum: 21

       - PullTotalTime: 2s54ms

         - __MAX_OF_PullTotalTime: 2s667ms

         - __MIN_OF_PullTotalTime: 1s550ms

       - PushChunkNum: 0

       - PushRowNum: 0

       - PushTotalTime: 0ns

       - RuntimeBloomFilterNum: 0

       - RuntimeInFilterNum: 0

       - SetFinishedTime: 261ns

         - __MAX_OF_SetFinishedTime: 399ns

         - __MIN_OF_SetFinishedTime: 140ns

       - SetFinishingTime: 5.872ms

         - __MAX_OF_SetFinishingTime: 23.486ms

         - __MIN_OF_SetFinishingTime: 554ns

    UniqueMetrics:

       - BufferUnplugCount: 0

       - BytesPassThrough: 91.18 MB

       - BytesReceived: 1.01 GB

       - ClosureBlockCount: 18

       - ClosureBlockTime: 13s959ms

       - DecompressChunkTime: 0ns

       - DeserializeChunkTime: 6s603ms

       - ReceiverProcessTotalTime: 704.864ms

       - RequestReceived: 26

       - SenderTotalTime: 704.858ms

       - SenderWaitLockTime: 9.160us

    Fragment 1:

     - BackendAddresses: 172.17.10.164:9060,172.17.10.167:9060,172.17.10.168:9060,172.17.10.165:9060,172.17.10.166:9060

 - BackendNum: 5

 - FragmentInstancePrepareTime: 318.289us

   - __MAX_OF_FragmentInstancePrepareTime: 419.548us

   - __MIN_OF_FragmentInstancePrepareTime: 267.231us

   - prepare-fragment-ctx: 21.620us

   - prepare-pipeline-driver: 112.542us

   - prepare-query-ctx: 4.174us

   - prepare-runtime-state: 112.542us

 - InstanceNum: 5

 - PeakMemoryUsage: 11.37 GB

   - __MAX_OF_PeakMemoryUsage: 3.05 GB

   - __MIN_OF_PeakMemoryUsage: 1.30 GB

 - QueryMemoryLimit: 80.00 GB

   - __MAX_OF_QueryMemoryLimit: 16.00 GB

   - __MIN_OF_QueryMemoryLimit: 16.00 GB

Pipeline (id=1):

   - ActiveTime: 887.8ms

     - __MAX_OF_ActiveTime: 2s157ms

     - __MIN_OF_ActiveTime: 286.378us

   - BlockByInputEmpty: 64

     - __MAX_OF_BlockByInputEmpty: 5

     - __MIN_OF_BlockByInputEmpty: 1

   - BlockByOutputFull: 0

   - BlockByPrecondition: 0

   - DegreeOfParallelism: 4

   - DriverPrepareTime: 73.553us

     - __MAX_OF_DriverPrepareTime: 148.659us

     - __MIN_OF_DriverPrepareTime: 52.87us

   - DriverTotalTime: 4s880ms

     - __MAX_OF_DriverTotalTime: 6s13ms

     - __MIN_OF_DriverTotalTime: 4s375ms

   - OverheadTime: 410.594ms

     - __MAX_OF_OverheadTime: 976.994ms

     - __MIN_OF_OverheadTime: 0ns

   - PendingTime: 3s975ms

     - __MAX_OF_PendingTime: 6s10ms

     - __MIN_OF_PendingTime: 2s714ms

     - InputEmptyTime: 2s233ms

       - __MAX_OF_InputEmptyTime: 4s377ms

       - __MIN_OF_InputEmptyTime: 101.407us

       - FirstInputEmptyTime: 48.175us

         - __MAX_OF_FirstInputEmptyTime: 75.208us

         - __MIN_OF_FirstInputEmptyTime: 30.200us

       - FollowupInputEmptyTime: 2s233ms

         - __MAX_OF_FollowupInputEmptyTime: 4s377ms

         - __MIN_OF_FollowupInputEmptyTime: 68.563us

     - OutputFullTime: 0ns

     - PendingFinishTime: 1s741ms

       - __MAX_OF_PendingFinishTime: 5s492ms

       - __MIN_OF_PendingFinishTime: 0ns

     - PreconditionBlockTime: 0ns

   - ScheduleCount: 96

     - __MAX_OF_ScheduleCount: 7

     - __MIN_OF_ScheduleCount: 2

   - ScheduleTime: 18.238ms

     - __MAX_OF_ScheduleTime: 345.206ms

     - __MIN_OF_ScheduleTime: 635.387us

   - TotalDegreeOfParallelism: 20

     - __MAX_OF_TotalDegreeOfParallelism: 4

     - __MIN_OF_TotalDegreeOfParallelism: 4

   - YieldByPreempt: 0

   - YieldByTimeLimit: 16

     - __MAX_OF_YieldByTimeLimit: 2

     - __MIN_OF_YieldByTimeLimit: 0

  EXCHANGE_SINK (plan_node_id=1):

    CommonMetrics:

       - CloseTime: 3.495us

         - __MAX_OF_CloseTime: 16.848us

         - __MIN_OF_CloseTime: 117ns

       - OperatorTotalTime: 470.978ms

         - __MAX_OF_OperatorTotalTime: 1s337ms

         - __MIN_OF_OperatorTotalTime: 8.570us

       - PeakMemoryUsage: 0.00

       - PrepareTime: 39.215us

         - __MAX_OF_PrepareTime: 105.221us

         - __MIN_OF_PrepareTime: 27.430us

       - PullChunkNum: 0

       - PullRowNum: 0

       - PullTotalTime: 0ns

       - PushChunkNum: 30

         - __MAX_OF_PushChunkNum: 4

         - __MIN_OF_PushChunkNum: 0

       - PushRowNum: 282

         - __MAX_OF_PushRowNum: 92

         - __MIN_OF_PushRowNum: 0

       - PushTotalTime: 470.955ms

         - __MAX_OF_PushTotalTime: 1s337ms

         - __MIN_OF_PushTotalTime: 0ns

       - SetFinishedTime: 30ns

         - __MAX_OF_SetFinishedTime: 95ns

         - __MIN_OF_SetFinishedTime: 18ns

       - SetFinishingTime: 19.948us

         - __MAX_OF_SetFinishingTime: 96.760us

         - __MIN_OF_SetFinishingTime: 1.704us

    UniqueMetrics:

       - DestID: 1

       - DestFragments: 30760d79511511ee-84bd00163e0e6dc1

       - PartType: UNPARTITIONED

       - ChannelNum: 1

       - BytesPassThrough: 250.39 MB

         - __MAX_OF_BytesPassThrough: 159.21 MB

         - __MIN_OF_BytesPassThrough: 0.00

       - BytesSent: 1.15 GB

         - __MAX_OF_BytesSent: 362.18 MB

         - __MIN_OF_BytesSent: 0.00

       - CompressTime: 0ns

       - NetworkBandwidth: 2.806769541464746 GB/sec

         - __MAX_OF_NetworkBandwidth: 850.4567403793335 MB/sec

         - __MIN_OF_NetworkBandwidth: 0.0 /sec

       - NetworkTime: 333.812ms

         - __MAX_OF_NetworkTime: 501.38ms

         - __MIN_OF_NetworkTime: 775.674us

       - OverallThroughput: 381.4152374267578 MB/sec

         - __MAX_OF_OverallThroughput: 139.5133876800537 MB/sec

         - __MIN_OF_OverallThroughput: 0.0 /sec

       - OverallTime: 3s944ms

         - __MAX_OF_OverallTime: 6s4ms

         - __MIN_OF_OverallTime: 1s546ms

       - RequestSent: 22

         - __MAX_OF_RequestSent: 7

         - __MIN_OF_RequestSent: 0

       - RpcAvgTime: 53.330ms

         - __MAX_OF_RpcAvgTime: 79.259ms

         - __MIN_OF_RpcAvgTime: 96.959us

       - RpcCount: 33

         - __MAX_OF_RpcCount: 8

         - __MIN_OF_RpcCount: 5

       - SerializeChunkTime: 307.340ms

         - __MAX_OF_SerializeChunkTime: 824.23ms

         - __MIN_OF_SerializeChunkTime: 0ns

       - ShuffleHashTime: 0ns

       - UncompressedBytes: 1.15 GB

         - __MAX_OF_UncompressedBytes: 158.25 MB

         - __MIN_OF_UncompressedBytes: 0.00

       - WaitTime: 171.676ms

         - __MAX_OF_WaitTime: 503.596ms

         - __MIN_OF_WaitTime: 534.319us

  LIMIT (plan_node_id=0):

    CommonMetrics:

       - CloseTime: 385ns

         - __MAX_OF_CloseTime: 705ns

         - __MIN_OF_CloseTime: 137ns

       - OperatorTotalTime: 4.402ms

         - __MAX_OF_OperatorTotalTime: 88.15ms

         - __MIN_OF_OperatorTotalTime: 547ns

       - PeakMemoryUsage: 0.00

       - PrepareTime: 6.629us

         - __MAX_OF_PrepareTime: 10.46us

         - __MIN_OF_PrepareTime: 4.958us

       - PullChunkNum: 30

         - __MAX_OF_PullChunkNum: 4

         - __MIN_OF_PullChunkNum: 0

       - PullRowNum: 282

         - __MAX_OF_PullRowNum: 92

         - __MIN_OF_PullRowNum: 0

       - PullTotalTime: 321ns

         - __MAX_OF_PullTotalTime: 706ns

         - __MIN_OF_PullTotalTime: 0ns

       - PushChunkNum: 30

         - __MAX_OF_PushChunkNum: 4

         - __MIN_OF_PushChunkNum: 0

       - PushRowNum: 296

         - __MAX_OF_PushRowNum: 106

         - __MIN_OF_PushRowNum: 0

       - PushTotalTime: 4.401ms

         - __MAX_OF_PushTotalTime: 88.14ms

         - __MIN_OF_PushTotalTime: 0ns

       - SetFinishedTime: 24ns

         - __MAX_OF_SetFinishedTime: 50ns

         - __MIN_OF_SetFinishedTime: 19ns

       - SetFinishingTime: 160ns

         - __MAX_OF_SetFinishingTime: 404ns

         - __MIN_OF_SetFinishingTime: 35ns

    UniqueMetrics:

  OLAP_SCAN (plan_node_id=0):

    CommonMetrics:

       - CloseTime: 17.810ms

         - __MAX_OF_CloseTime: 344.776ms

         - __MIN_OF_CloseTime: 450.197us

       - JoinRuntimeFilterEvaluate: 0

       - JoinRuntimeFilterHashTime: 0ns

       - JoinRuntimeFilterInputRows: 0

       - JoinRuntimeFilterOutputRows: 0

       - JoinRuntimeFilterTime: 0ns

       - OperatorTotalTime: 18.308ms

         - __MAX_OF_OperatorTotalTime: 345.58ms

         - __MIN_OF_OperatorTotalTime: 847.23us

       - PeakMemoryUsage: 0.00

       - PrepareTime: 12.288us

         - __MAX_OF_PrepareTime: 23.321us

         - __MIN_OF_PrepareTime: 7.522us

       - PullChunkNum: 30

         - __MAX_OF_PullChunkNum: 4

         - __MIN_OF_PullChunkNum: 0

       - PullRowNum: 296

         - __MAX_OF_PullRowNum: 106

         - __MIN_OF_PullRowNum: 0

       - PullTotalTime: 497.732us

         - __MAX_OF_PullTotalTime: 1.245ms

         - __MIN_OF_PullTotalTime: 242.70us

       - PushChunkNum: 0

       - PushRowNum: 0

       - PushTotalTime: 0ns

       - RuntimeBloomFilterNum: 0

       - RuntimeInFilterNum: 0

       - SetFinishedTime: 148ns

         - __MAX_OF_SetFinishedTime: 535ns

         - __MIN_OF_SetFinishedTime: 47ns

       - SetFinishingTime: 630ns

         - __MAX_OF_SetFinishingTime: 2.206us

         - __MIN_OF_SetFinishingTime: 297ns

    UniqueMetrics:

       - MorselQueueType: fixed_morsel_queue

       - BufferUnplugThreshold: 1

       - SharedScan: False

       - Rollup: biz_user_exec_rel

       - Table: biz_user_exec_rel

       - Aggr: 1s567ms

         - __MAX_OF_Aggr: 2s570ms

         - __MIN_OF_Aggr: 0ns

       - BufferUnplugCount: 4

         - __MAX_OF_BufferUnplugCount: 2

         - __MIN_OF_BufferUnplugCount: 0

       - BytesRead: 1.47 GB

         - __MAX_OF_BytesRead: 159.21 MB

         - __MIN_OF_BytesRead: 0.00

       - CachedPagesNum: 440

         - __MAX_OF_CachedPagesNum: 96

         - __MIN_OF_CachedPagesNum: 0

       - ChunkBufferCapacity: 256

       - CompressedBytesRead: 150.38 MB

         - __MAX_OF_CompressedBytesRead: 72.51 MB

         - __MIN_OF_CompressedBytesRead: 0.00

       - CreateSegmentIter: 10.113us

         - __MAX_OF_CreateSegmentIter: 55.562us

         - __MIN_OF_CreateSegmentIter: 0ns

       - DefaultChunkBufferCapacity: 1

       - GetDelVec: 40ns

         - __MAX_OF_GetDelVec: 292ns

         - __MIN_OF_GetDelVec: 0ns

       - GetRowsets: 215ns

         - __MAX_OF_GetRowsets: 649ns

         - __MIN_OF_GetRowsets: 0ns

       - IOTaskExecTime: 464.407ms

         - __MAX_OF_IOTaskExecTime: 3s848ms

         - __MIN_OF_IOTaskExecTime: 0ns

       - IOTaskWaitTime: 234.474us

         - __MAX_OF_IOTaskWaitTime: 8.836ms

         - __MIN_OF_IOTaskWaitTime: 8.380us

       - IOTime: 2.688ms

         - __MAX_OF_IOTime: 72.13ms

         - __MIN_OF_IOTime: 0ns

       - MorselsCount: 64

         - __MAX_OF_MorselsCount: 4

         - __MIN_OF_MorselsCount: 3

       - PeakChunkBufferSize: 10

       - PushdownPredicates: 0

       - RawRowsRead: 303

         - __MAX_OF_RawRowsRead: 100

         - __MIN_OF_RawRowsRead: 0

       - ReadPagesNum: 530

         - __MAX_OF_ReadPagesNum: 96

         - __MIN_OF_ReadPagesNum: 0

       - RowsRead: 303

         - __MAX_OF_RowsRead: 100

         - __MIN_OF_RowsRead: 0

       - ScanTime: 464.638ms

         - __MAX_OF_ScanTime: 3s857ms

         - __MIN_OF_ScanTime: 0ns

       - SegmentInit: 393.572us

         - __MAX_OF_SegmentInit: 11.709ms

         - __MIN_OF_SegmentInit: 0ns

         - BitmapIndexFilter: 0ns

         - BitmapIndexFilterRows: 0

         - BloomFilterFilterRows: 0

         - SegmentRuntimeZoneMapFilterRows: 0

         - SegmentZoneMapFilterRows: 0

         - ShortKeyFilterRows: 0

         - ZoneMapIndexFilterRows: 0

       - SegmentRead: 438.195ms

         - __MAX_OF_SegmentRead: 3s636ms

         - __MIN_OF_SegmentRead: 0ns

         - BlockFetch: 437.594ms

           - __MAX_OF_BlockFetch: 3s636ms

           - __MIN_OF_BlockFetch: 0ns

         - BlockFetchCount: 40

           - __MAX_OF_BlockFetchCount: 4

           - __MIN_OF_BlockFetchCount: 0

         - BlockSeek: 662.846us

           - __MAX_OF_BlockSeek: 34.238ms

           - __MIN_OF_BlockSeek: 0ns

         - BlockSeekCount: 251

           - __MAX_OF_BlockSeekCount: 90

           - __MIN_OF_BlockSeekCount: 0

         - ChunkCopy: 0ns

         - DecompressT: 3.4ms

           - __MAX_OF_DecompressT: 97.71ms

           - __MIN_OF_DecompressT: 0ns

         - DelVecFilterRows: 0

         - IndexLoad: 0ns

         - PredFilter: 0ns

         - PredFilterRows: 0

         - RowsetsReadCount: 230

           - __MAX_OF_RowsetsReadCount: 8

           - __MIN_OF_RowsetsReadCount: 0

         - SegmentsReadCount: 40

           - __MAX_OF_SegmentsReadCount: 4

           - __MIN_OF_SegmentsReadCount: 0

         - TotalColumnsDataPageCount: 609

           - __MAX_OF_TotalColumnsDataPageCount: 253

           - __MIN_OF_TotalColumnsDataPageCount: 0

       - Sort: 1s567ms

         - __MAX_OF_Sort: 2s570ms

         - __MIN_OF_Sort: 0ns

       - SubmitTaskCount: 80

         - __MAX_OF_SubmitTaskCount: 5

         - __MIN_OF_SubmitTaskCount: 3

       - TabletCount: 13

       - UncompressedBytesRead: 293.43 MB

         - __MAX_OF_UncompressedBytesRead: 142.72 MB

         - __MIN_OF_UncompressedBytesRead: 0.00

Pipeline (id=0):

   - ActiveTime: 40.127us

     - __MAX_OF_ActiveTime: 77.557us

     - __MIN_OF_ActiveTime: 26.369us

   - BlockByInputEmpty: 0

   - BlockByOutputFull: 0

   - BlockByPrecondition: 0

   - DegreeOfParallelism: 4

   - DriverPrepareTime: 48.180us

     - __MAX_OF_DriverPrepareTime: 72.749us

     - __MIN_OF_DriverPrepareTime: 30.994us

   - DriverTotalTime: 459.376us

     - __MAX_OF_DriverTotalTime: 643.909us

     - __MIN_OF_DriverTotalTime: 331.630us

   - OverheadTime: 247ns

     - __MAX_OF_OverheadTime: 4.860us

     - __MIN_OF_OverheadTime: 0ns

   - PendingTime: 0ns

     - InputEmptyTime: 0ns

       - FirstInputEmptyTime: 0ns

       - FollowupInputEmptyTime: 0ns

     - OutputFullTime: 0ns

     - PendingFinishTime: 0ns

     - PreconditionBlockTime: 0ns

   - ScheduleCount: 20

     - __MAX_OF_ScheduleCount: 1

     - __MIN_OF_ScheduleCount: 1

   - ScheduleTime: 419.248us

     - __MAX_OF_ScheduleTime: 611.342us

     - __MIN_OF_ScheduleTime: 295.391us

   - TotalDegreeOfParallelism: 20

     - __MAX_OF_TotalDegreeOfParallelism: 4

     - __MIN_OF_TotalDegreeOfParallelism: 4

   - YieldByPreempt: 0

   - YieldByTimeLimit: 0

  NOOP_SINK (plan_node_id=0):

    CommonMetrics:

       - CloseTime: 307ns

         - __MAX_OF_CloseTime: 531ns

         - __MIN_OF_CloseTime: 110ns

       - OperatorTotalTime: 404ns

         - __MAX_OF_OperatorTotalTime: 679ns

         - __MIN_OF_OperatorTotalTime: 237ns

       - PeakMemoryUsage: 0.00

       - PrepareTime: 8.633us

         - __MAX_OF_PrepareTime: 15.544us

         - __MIN_OF_PrepareTime: 5.679us

       - PullChunkNum: 0

       - PullRowNum: 0

       - PullTotalTime: 0ns

       - PushChunkNum: 0

       - PushRowNum: 0

       - PushTotalTime: 0ns

       - SetFinishedTime: 27ns

         - __MAX_OF_SetFinishedTime: 42ns

         - __MIN_OF_SetFinishedTime: 18ns

       - SetFinishingTime: 68ns

         - __MAX_OF_SetFinishingTime: 116ns

         - __MIN_OF_SetFinishingTime: 40ns

    UniqueMetrics:

  OLAP_SCAN_PREPARE (plan_node_id=0):

    CommonMetrics:

       - CloseTime: 7.569us

         - __MAX_OF_CloseTime: 14.163us

         - __MIN_OF_CloseTime: 4.76us

       - OperatorTotalTime: 42.915us

         - __MAX_OF_OperatorTotalTime: 80.127us

         - __MIN_OF_OperatorTotalTime: 28.486us

       - PeakMemoryUsage: 0.00

       - PrepareTime: 22.281us

         - __MAX_OF_PrepareTime: 41.972us

         - __MIN_OF_PrepareTime: 13.860us

       - PullChunkNum: 0

       - PullRowNum: 0

       - PullTotalTime: 35.174us

         - __MAX_OF_PullTotalTime: 73.238us

         - __MIN_OF_PullTotalTime: 22.389us

       - PushChunkNum: 0

       - PushRowNum: 0

       - PushTotalTime: 0ns

       - RuntimeBloomFilterNum: 0

       - RuntimeInFilterNum: 0

       - SetFinishedTime: 53ns

         - __MAX_OF_SetFinishedTime: 65ns

         - __MIN_OF_SetFinishedTime: 40ns

       - SetFinishingTime: 117ns

         - __MAX_OF_SetFinishingTime: 221ns

         - __MIN_OF_SetFinishingTime: 57ns

    UniqueMetrics:

执行计划:
PLAN FRAGMENT 0
OUTPUT EXPRS:1: group_code | 2: object_id | 3: level_id | 4: level | 5: type | 6: mem_codes
PARTITION: UNPARTITIONED
RESULT SINK
1:EXCHANGE
PLAN FRAGMENT 1
OUTPUT EXPRS:
PARTITION: RANDOM
STREAM DATA SINK
EXCHANGE ID: 01
UNPARTITIONED
0:OlapScanNode
TABLE: biz_user_exec_rel
PREAGGREGATION: OFF. Reason: None aggregate function
partitions=1/1
rollup: biz_user_exec_rel
tabletRatio=64/64
tabletList=399506,399510,399514,399518,399522,399526,399530,399534,399538,399542 …
cardinality=508
avgRowSize=36.503937
numNodes=0

#2

您好，profile能发文本吗？这样看不方便

#3

不带条件Profile (24.6 KB)

#4

  • ScanTime: 464.638ms

  • __MAX_OF_ScanTime: 3s857ms

  • __MIN_OF_ScanTime: 0ns

表设计有问题，buckets设置得不好

#5

建表sql: CREATE TABLE biz_user_exec_rel (
group_code bigint(20) NOT NULL COMMENT “集团号”,
object_id bigint(20) NOT NULL COMMENT “业务主键”,
level_id bigint(20) NOT NULL COMMENT “层级主键”,
level varchar(255) NOT NULL COMMENT “层级名称”,
type varchar(255) NOT NULL COMMENT “业务类型”,
mem_codes bitmap BITMAP_UNION NULL COMMENT “用户集”
) ENGINE = OLAP AGGREGATE KEY(
group_code,
object_id,
level_id,
level,
type
) COMMENT “OLAP” DISTRIBUTED BY HASH(object_id) BUCKETS 64 PROPERTIES (
“replication_num” = “3”,
“in_memory” = “false”,
“storage_format” = “DEFAULT”,
“enable_persistent_index” = “false”,
“compression” = “LZ4”
);

#6

object_id 有多少个值，基于object_id 数据是均匀的吗

#7

image
image995×520 24.6 KB

#8

看图就知道object_id 倾斜了，object_id分布键，可以换成组合两个键，这样更均匀。

#9

image
image763×136 8.87 KB

试过,查询依然很慢

#10

查询其他objectId也比较慢

#11

这个表数据量很大吗？

#12

不大,500条左右

#13

500条数据 bucket先设置1吧

#14

未来数据会增加,不会太大

#15

先设置1测试一下性能，未来数据量大概多少万？

#16

至少1年内达不到1万

#17

我按照您的建议试一下

#18

这个数据量一个bucket就ok，就先按照hash key(object_id) 就好了

#19

还是很慢,

image
image1004×710 43.1 KB

#20

image 查询内存为啥这么高呢,bitmap对应的uid有600多万

京ICP备2022026421号-1