order by 查询慢

💬 StarRocks 用户问答
#1

【详述】我们有一个大宽表，里面有时间字段，还有其他字段，当我们做一个大查询的时候，加上时间order by 会比不加时间排序要快很多，order by在15秒左右，没有排序的话200ms左右，
【背景】尝试过以时间字段为主键，增加索引，添加物化视图，对于已有的查询优化基本为零
【业务影响】很大
【StarRocks版本】例如：1.19.1
【集群规模】例如：3fe（3 follower）+3be（fe与be混部）
【机器信息】CPU虚拟核/内存/网卡，28C/256G/百兆
【附件】
加上order by查询时长

图片
图片970×55 7.66 KB

不加order by查询时长
图片
图片1000×102 9.51 KB

  • 慢查询：
    SELECT
    obb.ROWID ,obb.BID_TITLE,obb.BID_TYPE,obb.BID_INDUSTRY_TYPE1,obb.PROVINCE,obb.CITY,obb.PUBLISH_TM,
    obb.BID_BUDGET_AMT_SUM as BID_BUDGET_AMT,obb.WIN_AMT_SUM as WIN_AMT,obb.BID_STRUCT_TEXT,
    obb.ATTACH_1,obb.ATTACH_1_COS,obb.ATTACH_1_NAME,
    obb.ATTACH_2,obb.ATTACH_2_COS,obb.ATTACH_2_NAME,
    obb.ATTACH_3,obb.ATTACH_3_COS,obb.ATTACH_3_NAME,
    obb.ATTACH_4,obb.ATTACH_4_COS,obb.ATTACH_4_NAME,
    obb.ATTACH_5,obb.ATTACH_5_COS,obb.ATTACH_5_NAME,
    obb.ATTACH_6,obb.ATTACH_6_COS,obb.ATTACH_6_NAME,
    obb.ATTACH_7,obb.ATTACH_7_COS,obb.ATTACH_7_NAME,
    obb.ATTACH_8,obb.ATTACH_8_COS,obb.ATTACH_8_NAME,
    obb.ATTACH_9,obb.ATTACH_9_COS,obb.ATTACH_9_NAME,
    obb.ATTACH_10,obb.ATTACH_10_COS,obb.ATTACH_10_NAME
    FROM ods_bid_list obb
    WHERE obb.BID_TITLE LIKE “%计算机%”
    AND obb.BID_TYPE NOT IN (‘100’,‘117’)
    AND obb.PUBLISH_TM IS NOT NULL
    ORDER BY obb.PUBLISH_TM desc LIMIT 50
#2

数据量在一个亿左右，sql里涉及到的所有字段做了一个物化视图，排序字段也做了主键和索引

#3

Query:
Summary:
- Query ID: 77be7fe1-8d86-11ec-9540-a0b3cc1d1370
- Start Time: 2022-02-14 19:08:41
- End Time: 2022-02-14 19:08:51
- Total: 9s991ms
- Query Type: Query
- Query State: EOF
- StarRocks Version: 1.19.1
- User: root
- Default Db: default_cluster:bxb_list
- Sql Statement: /* ApplicationName=DataGrip 2020.1 / SELECT
obb.ROWID ,obb.BID_TITLE,obb.BID_TYPE,obb.BID_INDUSTRY_TYPE1,obb.PROVINCE,obb.CITY,obb.PUBLISH_TM,
obb.BID_BUDGET_AMT_SUM as BID_BUDGET_AMT,obb.WIN_AMT_SUM as WIN_AMT,obb.BID_STRUCT_TEXT,
obb.ATTACH_1,obb.ATTACH_1_COS,obb.ATTACH_1_NAME,
obb.ATTACH_2,obb.ATTACH_2_COS,obb.ATTACH_2_NAME,
obb.ATTACH_3,obb.ATTACH_3_COS,obb.ATTACH_3_NAME,
obb.ATTACH_4,obb.ATTACH_4_COS,obb.ATTACH_4_NAME,
obb.ATTACH_5,obb.ATTACH_5_COS,obb.ATTACH_5_NAME,
obb.ATTACH_6,obb.ATTACH_6_COS,obb.ATTACH_6_NAME,
obb.ATTACH_7,obb.ATTACH_7_COS,obb.ATTACH_7_NAME,
obb.ATTACH_8,obb.ATTACH_8_COS,obb.ATTACH_8_NAME,
obb.ATTACH_9,obb.ATTACH_9_COS,obb.ATTACH_9_NAME,
obb.ATTACH_10,obb.ATTACH_10_COS,obb.ATTACH_10_NAME
FROM ods_bid_list obb
WHERE obb.BID_TITLE LIKE “%计算机%”
AND obb.BID_TYPE NOT IN (‘100’,‘117’)
AND obb.PUBLISH_TM IS NOT NULL
ORDER BY obb.PUBLISH_TM desc LIMIT 50
Execution Profile 77be7fe1-8d86-11ec-9540-a0b3cc1d1370:(Active: 9s990ms[9990774196ns], % non-child: 100.00%)
Fragment 0:
Instance 77be7fe1-8d86-11ec-9540-a0b3cc1d137b (host=TNetworkAddress(hostname:172.16.42.42, port:9060)):(Active: 9s984ms[9984282687ns], % non-child: 0.01%)
- AverageThreadTokens: 4607182418800017400.00
- MemoryLimit: 4.00 GB
- PeakMemoryUsage: 0.00
- PeakReservation: 0.00
- PeakUsedReservation: 0.00
- RowsProduced: 50
BlockMgr:
- BlockWritesOutstanding: 0
- BlocksCreated: 0
- BlocksRecycled: 0
- BufferedPins: 0
- BytesWritten: 0.00
- MaxBlockSize: 8.00 MB
- TotalBufferWaitTime: 0ns
- TotalReadBlockTime: 0ns
DataBufferSender (dst_fragment_instance_id=77be7fe1-8d86-11ec-9540-a0b3cc1d137b):
- AppendBatchTime: 119.555us
- ResultRendTime: 130.938us
- TupleConvertTime: 109.241us
- NumSentRows: 50
EXCHANGE_NODE (id=2):(Active: 9s983ms[9983754116ns], % non-child: 99.93%)
- BytesReceived: 50.52 KB
- ConvertRowBatchTime: 0ns
- DataArrivalWaitTime: 0ns
- DecompressRowBatchTimer: 201.769us
- DeserializeChunkMetaTimer: 0ns
- DeserializeRowBatchTimer: 1.481ms
- FirstBatchArrivalWaitTime: 0ns
- MergeSortedChunks: 2.72ms
- PeakMemoryUsage: 0.00
- RequestReceived: 10.00 B
- RowsReturned: 50
- RowsReturnedRate: 5
- SenderTotalTime: 1.880ms
- SenderWaitLockTime: 1.853us
- SendersBlockedTotalTimer(): 0ns
Fragment 1:
Instance 77be7fe1-8d86-11ec-9540-a0b3cc1d1375 (host=TNetworkAddress(hostname:172.16.42.42, port:9060)):(Active: 9s978ms[9978193923ns], % non-child: 0.00%)
- AverageThreadTokens: 4607182418800017400.00
- MemoryLimit: 4.00 GB
- PeakMemoryUsage: 315.35 MB
- PeakReservation: 0.00
- PeakUsedReservation: 0.00
- RowsProduced: 50
BlockMgr:
- BlockWritesOutstanding: 0
- BlocksCreated: 0
- BlocksRecycled: 0
- BufferedPins: 0
- BytesWritten: 0.00
- MaxBlockSize: 8.00 MB
- TotalBufferWaitTime: 0ns
- TotalReadBlockTime: 0ns
DataStreamSender (dst_id=2, dst_fragments=[77be7fe18d8611ec-9540a0b3cc1d137b]):(Active: 454.777us[454777ns], % non-child: 0.00%)
- PartType: UNPARTITIONED
- BytesSent: 4.74 KB
- CompressTime: 48.790us
- IgnoreRows: 0
- OverallThroughput: 10.168426513671875 MB/sec
- PeakMemoryUsage: 192.00 B
- SendRequestTime: 40.442us
- SerializeBatchTime: 26.470us
- ShuffleDispatchTime: 0ns
- ShuffleHashTime: 0ns
- UncompressedBytes: 38.12 KB
- WaitResponseTime: 271.859us
SORT_NODE (id=1):(Active: 9s977ms[9977951906ns], % non-child: 1.48%)
- SortKeys: 23: PUBLISH_TM DESC
- SortType: TopN
- ChunksSorter: 146.233ms
- 1-BuildingTime: 275.174us
- 2-SortingTime: 718.306us
- 3-MergingTime: 1.96ms
- 4-OutputTime: 56.91us
- PeakMemoryUsage: 193.02 MB
- RowsReturned: 50
- RowsReturnedRate: 5
OLAP_SCAN_NODE (id=0):(Active: 9s830ms[9830476019ns], % non-child: 3.45%)
- Table: ods_bid_list
- Rollup: list_test
- Predicates: 12: BID_TITLE LIKE ‘%计算机%’, 16: BID_TYPE NOT IN (‘100’, ‘117’), 23: PUBLISH_TM IS NOT NULL
- BytesRead: 6.01 GB
- ExprFilterTime: 431.709ms
- NumDiskAccess: 0
- PeakMemoryUsage: 156.20 MB
- PerReadThreadRawHdfsThroughput: 0.0 /sec
- RowsRead: 0
- RowsReturned: 34.764K (34764)
- RowsReturnedRate: 3.536K /sec
- ScanTime: 9s941ms
- ScannerThreadsInvoluntaryContextSwitches: 0
- ScannerThreadsTotalWallClockTime: 0ns
- MaterializeTupleTime(): 0ns
- ScannerThreadsSysTime: 0ns
- ScannerThreadsUserTime: 0ns
- ScannerThreadsVoluntaryContextSwitches: 0
- TabletCount : 1
- TotalRawReadTime(): 0ns
- TotalReadThroughput: 0.0 /sec
SCAN:(Active: 9s486ms[9486208294ns], % non-child: 94.95%)
- CachedPagesNum: 0
- CompressedBytesRead: 631.21 MB
- CreateSegmentIter: 1.848ms
- DictDecode: 2s605ms
- IOTime: 1s334ms
- LateMaterialize: 647.295ms
- PushdownPredicates: 1
- RawRowsRead: 7.244689M (7244689)
- SegmentInit: 146.988ms
- BitmapIndexFilter: 23.200us
- BitmapIndexFilterRows: 0
- BloomFilterFilterRows: 0
- ShortKeyFilterRows: 0
- ZoneMapIndexFilterRows: 0
- SegmentRead: 6s33ms
- BlockFetch: 6s5ms
- BlockFetchCount: 1.777K (1777)
- BlockSeek: 1.20ms
- BlockSeekCount: 20
- ChunkCopy: 65.753us
- DecompressT: 1s383ms
- DelVecFilterRows: 0
- IndexLoad: 0ns
- PredFilter: 1.740ms
- PredFilterRows: 0
- TotalPagesNum: 68.09K (68090)
- UncompressedBytesRead: 3.28 GB
Instance 77be7fe1-8d86-11ec-9540-a0b3cc1d1377 (host=TNetworkAddress(hostname:172.16.42.42, port:9060)):(Active: 9s959ms[9959068804ns], % non-child: 0.00%)
- AverageThreadTokens: 4607182418800017400.00
- MemoryLimit: 4.00 GB
- PeakMemoryUsage: 296.52 MB
- PeakReservation: 0.00
- PeakUsedReservation: 0.00
- RowsProduced: 50
BlockMgr:
- BlockWritesOutstanding: 0
- BlocksCreated: 0
- BlocksRecycled: 0
- BufferedPins: 0
- BytesWritten: 0.00
- MaxBlockSize: 8.00 MB
- TotalBufferWaitTime: 0ns
- TotalReadBlockTime: 0ns
DataStreamSender (dst_id=2, dst_fragments=[77be7fe18d8611ec-9540a0b3cc1d137b]):(Active: 453.862us[453862ns], % non-child: 0.00%)
- PartType: UNPARTITIONED
- BytesSent: 4.26 KB
- CompressTime: 41.987us
- IgnoreRows: 0
- OverallThroughput: 9.176127433776855 MB/sec
- PeakMemoryUsage: 192.00 B
- SendRequestTime: 41.776us
- SerializeBatchTime: 17.825us
- ShuffleDispatchTime: 0ns
- ShuffleHashTime: 0ns
- UncompressedBytes: 36.92 KB
- WaitResponseTime: 304.485us
SORT_NODE (id=1):(Active: 9s958ms[9958747934ns], % non-child: 1.28%)
- SortKeys: 23: PUBLISH_TM DESC
- SortType: TopN
- ChunksSorter: 127.53ms
- 1-BuildingTime: 191.137us
- 2-SortingTime: 759.741us
- 3-MergingTime: 1.154ms
- 4-OutputTime: 47.770us
- PeakMemoryUsage: 180.72 MB
- RowsReturned: 50
- RowsReturnedRate: 5
OLAP_SCAN_NODE (id=0):(Active: 9s830ms[9830570688ns], % non-child: 3.59%)
- Table: ods_bid_list
- Rollup: list_test
- Predicates: 12: BID_TITLE LIKE ‘%计算机%’, 16: BID_TYPE NOT IN (‘100’, ‘117’), 23: PUBLISH_TM IS NOT NULL
- BytesRead: 6.00 GB
- ExprFilterTime: 433.41ms
- NumDiskAccess: 0
- PeakMemoryUsage: 155.25 MB
- PerReadThreadRawHdfsThroughput: 0.0 /sec
- RowsRead: 0
- RowsReturned: 34.814K (34814)
- RowsReturnedRate: 3.541K /sec
- ScanTime: 9s928ms
- ScannerThreadsInvoluntaryContextSwitches: 0
- ScannerThreadsTotalWallClockTime: 0ns
- MaterializeTupleTime(): 0ns
- ScannerThreadsSysTime: 0ns
- ScannerThreadsUserTime: 0ns
- ScannerThreadsVoluntaryContextSwitches: 0
- TabletCount : 1
- TotalRawReadTime(): 0ns
- TotalReadThroughput: 0.0 /sec
SCAN:(Active: 9s472ms[9472385491ns], % non-child: 94.81%)
- CachedPagesNum: 0
- CompressedBytesRead: 629.03 MB
- CreateSegmentIter: 1.445ms
- DictDecode: 2s633ms
- IOTime: 1s187ms
- LateMaterialize: 589.81ms
- PushdownPredicates: 1
- RawRowsRead: 7.237255M (7237255)
- SegmentInit: 155.342ms
- BitmapIndexFilter: 22.867us
- BitmapIndexFilterRows: 0
- BloomFilterFilterRows: 0
- ShortKeyFilterRows: 0
- ZoneMapIndexFilterRows: 0
- SegmentRead: 6s46ms
- BlockFetch: 6s19ms
- BlockFetchCount: 1.777K (1777)
- BlockSeek: 1.56ms
- BlockSeekCount: 20
- ChunkCopy: 74.149us
- DecompressT: 1s409ms
- DelVecFilterRows: 0
- IndexLoad: 0ns
- PredFilter: 1.694ms
- PredFilterRows: 0
- TotalPagesNum: 67.889K (67889)
- UncompressedBytesRead: 3.26 GB
Instance 77be7fe1-8d86-11ec-9540-a0b3cc1d1379 (host=TNetworkAddress(hostname:172.16.42.43, port:9060)):(Active: 9s815ms[9815793844ns], % non-child: 0.00%)
- AverageThreadTokens: 4607182418800017400.00
- MemoryLimit: 4.00 GB
- PeakMemoryUsage: 276.03 MB
- PeakReservation: 0.00
- PeakUsedReservation: 0.00
- RowsProduced: 50
BlockMgr:
- BlockWritesOutstanding: 0
- BlocksCreated: 0
- BlocksRecycled: 0
- BufferedPins: 0
- BytesWritten: 0.00
- MaxBlockSize: 8.00 MB
- TotalBufferWaitTime: 0ns
- TotalReadBlockTime: 0ns
DataStreamSender (dst_id=2, dst_fragments=[77be7fe18d8611ec-9540a0b3cc1d137b]):(Active: 624.78us[624078ns], % non-child: 0.01%)
- PartType: UNPARTITIONED
- BytesSent: 5.60 KB
- CompressTime: 45.816us
- IgnoreRows: 0
- OverallThroughput: 8.7699556350708 MB/sec
- PeakMemoryUsage: 192.00 B
- SendRequestTime: 69.283us
- SerializeBatchTime: 36.574us
- ShuffleDispatchTime: 0ns
- ShuffleHashTime: 0ns
- UncompressedBytes: 44.81 KB
- WaitResponseTime: 430.715us
SORT_NODE (id=1):(Active: 9s815ms[9815308487ns], % non-child: 1.29%)
- SortKeys: 23: PUBLISH_TM DESC
- SortType: TopN
- ChunksSorter: 128.123ms
- 1-BuildingTime: 174.536us
- 2-SortingTime: 711.621us
- 3-MergingTime: 1.87ms
- 4-OutputTime: 56.300us
- PeakMemoryUsage: 133.98 MB
- RowsReturned: 50
- RowsReturnedRate: 5
OLAP_SCAN_NODE (id=0):(Active: 9s686ms[9686034795ns], % non-child: 3.41%)
- Table: ods_bid_list
- Rollup: list_test
- Predicates: 12: BID_TITLE LIKE ‘%计算机%’, 16: BID_TYPE NOT IN (‘100’, ‘117’), 23: PUBLISH_TM IS NOT NULL
- BytesRead: 6.01 GB
- ExprFilterTime: 428.816ms
- NumDiskAccess: 0
- PeakMemoryUsage: 153.43 MB
- PerReadThreadRawHdfsThroughput: 0.0 /sec
- RowsRead: 0
- RowsReturned: 34.764K (34764)
- RowsReturnedRate: 3.589K /sec
- ScanTime: 9s799ms
- ScannerThreadsInvoluntaryContextSwitches: 0
- ScannerThreadsTotalWallClockTime: 0ns
- MaterializeTupleTime(): 0ns
- ScannerThreadsSysTime: 0ns
- ScannerThreadsUserTime: 0ns
- ScannerThreadsVoluntaryContextSwitches: 0
- TabletCount : 1
- TotalRawReadTime(): 0ns
- TotalReadThroughput: 0.0 /sec
SCAN:(Active: 9s345ms[9345216633ns], % non-child: 93.54%)
- CachedPagesNum: 0
- CompressedBytesRead: 685.08 MB
- CreateSegmentIter: 1.396ms
- DictDecode: 2s589ms
- IOTime: 1s519ms
- LateMaterialize: 761.160ms
- PushdownPredicates: 1
- RawRowsRead: 7.253587M (7253587)
- SegmentInit: 105.926ms
- BitmapIndexFilter: 23.314us
- BitmapIndexFilterRows: 0
- BloomFilterFilterRows: 0
- ShortKeyFilterRows: 0
- ZoneMapIndexFilterRows: 0
- SegmentRead: 5s834ms
- BlockFetch: 5s806ms
- BlockFetchCount: 1.78K (1780)
- BlockSeek: 1.305ms
- BlockSeekCount: 22
- ChunkCopy: 73.443us
- DecompressT: 1s303ms
- DelVecFilterRows: 0
- IndexLoad: 0ns
- PredFilter: 1.686ms
- PredFilterRows: 0
- TotalPagesNum: 68.303K (68303)
- UncompressedBytesRead: 3.29 GB
Instance 77be7fe1-8d86-11ec-9540-a0b3cc1d137a (host=TNetworkAddress(hostname:172.16.42.43, port:9060)):(Active: 9s704ms[9704675869ns], % non-child: 0.00%)
- AverageThreadTokens: 4607182418800017400.00
- MemoryLimit: 4.00 GB
- PeakMemoryUsage: 304.65 MB
- PeakReservation: 0.00
- PeakUsedReservation: 0.00
- RowsProduced: 50
BlockMgr:
- BlockWritesOutstanding: 0
- BlocksCreated: 0
- BlocksRecycled: 0
- BufferedPins: 0
- BytesWritten: 0.00
- MaxBlockSize: 8.00 MB
- TotalBufferWaitTime: 0ns
- TotalReadBlockTime: 0ns
DataStreamSender (dst_id=2, dst_fragments=[77be7fe18d8611ec-9540a0b3cc1d137b]):(Active: 533.901us[533901ns], % non-child: 0.01%)
- PartType: UNPARTITIONED
- BytesSent: 4.91 KB
- CompressTime: 53.258us
- IgnoreRows: 0
- OverallThroughput: 8.986563682556152 MB/sec
- PeakMemoryUsage: 192.00 B
- SendRequestTime: 64.153us
- SerializeBatchTime: 17.955us
- ShuffleDispatchTime: 0ns
- ShuffleHashTime: 0ns
- UncompressedBytes: 40.09 KB
- WaitResponseTime: 357.124us
SORT_NODE (id=1):(Active: 9s704ms[9704253474ns], % non-child: 1.22%)
- SortKeys: 23: PUBLISH_TM DESC
- SortType: TopN
- ChunksSorter: 121.81ms
- 1-BuildingTime: 189.329us
- 2-SortingTime: 704.750us
- 3-MergingTime: 893.640us
- 4-OutputTime: 38.719us
- PeakMemoryUsage: 162.07 MB
- RowsReturned: 50
- RowsReturnedRate: 5
OLAP_SCAN_NODE (id=0):(Active: 9s582ms[9582198325ns], % non-child: 3.48%)
- Table: ods_bid_list
- Rollup: list_test
- Predicates: 12: BID_TITLE LIKE ‘%计算机%’, 16: BID_TYPE NOT IN (‘100’, ‘117’), 23: PUBLISH_TM IS NOT NULL
- BytesRead: 6.03 GB
- ExprFilterTime: 432.100ms
- NumDiskAccess: 0
- PeakMemoryUsage: 167.65 MB
- PerReadThreadRawHdfsThroughput: 0.0 /sec
- RowsRead: 0
- RowsReturned: 34.199K (34199)
- RowsReturnedRate: 3.569K /sec
- ScanTime: 9s689ms
- ScannerThreadsInvoluntaryContextSwitches: 0
- ScannerThreadsTotalWallClockTime: 0ns
- MaterializeTupleTime(): 0ns
- ScannerThreadsSysTime: 0ns
- ScannerThreadsUserTime: 0ns
- ScannerThreadsVoluntaryContextSwitches: 0
- TabletCount : 1
- TotalRawReadTime(): 0ns
- TotalReadThroughput: 0.0 /sec
SCAN:(Active: 9s234ms[9234390500ns], % non-child: 92.43%)
- CachedPagesNum: 0
- CompressedBytesRead: 631.77 MB
- CreateSegmentIter: 1.343ms
- DictDecode: 2s629ms
- IOTime: 1s285ms
- LateMaterialize: 677.401ms
- PushdownPredicates: 1
- RawRowsRead: 7.27234M (7272340)
- SegmentInit: 159.444ms
- BitmapIndexFilter: 23.244us
- BitmapIndexFilterRows: 0
- BloomFilterFilterRows: 0
- ShortKeyFilterRows: 0
- ZoneMapIndexFilterRows: 0
- SegmentRead: 5s711ms
- BlockFetch: 5s683ms
- BlockFetchCount: 1.787K (1787)
- BlockSeek: 1.25ms
- BlockSeekCount: 20
- ChunkCopy: 73.579us
- DecompressT: 1s267ms
- DelVecFilterRows: 0
- IndexLoad: 0ns
- PredFilter: 1.704ms
- PredFilterRows: 0
- TotalPagesNum: 68.379K (68379)
- UncompressedBytesRead: 3.29 GB
Instance 77be7fe1-8d86-11ec-9540-a0b3cc1d1372 (host=TNetworkAddress(hostname:172.16.42.41, port:9060)):(Active: 9s700ms[9700468718ns], % non-child: 0.00%)
- AverageThreadTokens: 4607182418800017400.00
- MemoryLimit: 4.00 GB
- PeakMemoryUsage: 297.66 MB
- PeakReservation: 0.00
- PeakUsedReservation: 0.00
- RowsProduced: 50
BlockMgr:
- BlockWritesOutstanding: 0
- BlocksCreated: 0
- BlocksRecycled: 0
- BufferedPins: 0
- BytesWritten: 0.00
- MaxBlockSize: 8.00 MB
- TotalBufferWaitTime: 0ns
- TotalReadBlockTime: 0ns
DataStreamSender (dst_id=2, dst_fragments=[77be7fe18d8611ec-9540a0b3cc1d137b]):(Active: 486.0us[486000ns], % non-child: 0.00%)
- PartType: UNPARTITIONED
- BytesSent: 4.80 KB
- CompressTime: 48.763us
- IgnoreRows: 0
- OverallThroughput: 9.646631240844727 MB/sec
- PeakMemoryUsage: 192.00 B
- SendRequestTime: 53.48us
- SerializeBatchTime: 31.12us
- ShuffleDispatchTime: 0ns
- ShuffleHashTime: 0ns
- UncompressedBytes: 43.23 KB
- WaitResponseTime: 288.956us
SORT_NODE (id=1):(Active: 9s700ms[9700114468ns], % non-child: 1.36%)
- SortKeys: 23: PUBLISH_TM DESC
- SortType: TopN
- ChunksSorter: 135.126ms
- 1-BuildingTime: 187.896us
- 2-SortingTime: 769.581us
- 3-MergingTime: 1.84ms
- 4-OutputTime: 65.895us
- PeakMemoryUsage: 166.61 MB
- RowsReturned: 50
- RowsReturnedRate: 5
OLAP_SCAN_NODE (id=0):(Active: 9s563ms[9563820186ns], % non-child: 3.49%)
- Table: ods_bid_list
- Rollup: list_test
- Predicates: 12: BID_TITLE LIKE ‘%计算机%’, 16: BID_TYPE NOT IN (‘100’, ‘117’), 23: PUBLISH_TM IS NOT NULL
- BytesRead: 6.03 GB
- ExprFilterTime: 440.762ms
- NumDiskAccess: 0
- PeakMemoryUsage: 174.29 MB
- PerReadThreadRawHdfsThroughput: 0.0 /sec
- RowsRead: 0
- RowsReturned: 36.325K (36325)
- RowsReturnedRate: 3.798K /sec
- ScanTime: 9s682ms
- ScannerThreadsInvoluntaryContextSwitches: 0
- ScannerThreadsTotalWallClockTime: 0ns
- MaterializeTupleTime(): 0ns
- ScannerThreadsSysTime: 0ns
- ScannerThreadsUserTime: 0ns
- ScannerThreadsVoluntaryContextSwitches: 0
- TabletCount : 1
- TotalRawReadTime(): 0ns
- TotalReadThroughput: 0.0 /sec
SCAN:(Active: 9s215ms[9215044684ns], % non-child: 92.24%)
- CachedPagesNum: 0
- CompressedBytesRead: 686.40 MB
- CreateSegmentIter: 1.316ms
- DictDecode: 2s630ms
- IOTime: 1s177ms
- LateMaterialize: 579.84ms
- PushdownPredicates: 1
- RawRowsRead: 7.266971M (7266971)
- SegmentInit: 125.877ms
- BitmapIndexFilter: 25.664us
- BitmapIndexFilterRows: 0
- BloomFilterFilterRows: 0
- ShortKeyFilterRows: 0
- ZoneMapIndexFilterRows: 0
- SegmentRead: 5s828ms
- BlockFetch: 5s798ms
- BlockFetchCount: 1.783K (1783)
- BlockSeek: 1.208ms
- BlockSeekCount: 22
- ChunkCopy: 75.848us
- DecompressT: 1s357ms
- DelVecFilterRows: 0
- IndexLoad: 0ns
- PredFilter: 1.722ms
- PredFilterRows: 0
- TotalPagesNum: 68.486K (68486)
- UncompressedBytesRead: 3.30 GB
Instance 77be7fe1-8d86-11ec-9540-a0b3cc1d1376 (host=TNetworkAddress(hostname:172.16.42.42, port:9060)):(Active: 9s698ms[9698346043ns], % non-child: 0.00%)
- AverageThreadTokens: 4607182418800017400.00
- MemoryLimit: 4.00 GB
- PeakMemoryUsage: 290.25 MB
- PeakReservation: 0.00
- PeakUsedReservation: 0.00
- RowsProduced: 50
BlockMgr:
- BlockWritesOutstanding: 0
- BlocksCreated: 0
- BlocksRecycled: 0
- BufferedPins: 0
- BytesWritten: 0.00
- MaxBlockSize: 8.00 MB
- TotalBufferWaitTime: 0ns
- TotalReadBlockTime: 0ns
DataStreamSender (dst_id=2, dst_fragments=[77be7fe18d8611ec-9540a0b3cc1d137b]):(Active: 582.733us[582733ns], % non-child: 0.01%)
- PartType: UNPARTITIONED
- BytesSent: 5.46 KB
- CompressTime: 46.295us
- IgnoreRows: 0
- OverallThroughput: 9.156521797180176 MB/sec
- PeakMemoryUsage: 192.00 B
- SendRequestTime: 71.468us
- SerializeBatchTime: 44.80us
- ShuffleDispatchTime: 0ns
- ShuffleHashTime: 0ns
- UncompressedBytes: 45.28 KB
- WaitResponseTime: 354.938us
SORT_NODE (id=1):(Active: 9s697ms[9697955318ns], % non-child: 1.34%)
- SortKeys: 23: PUBLISH_TM DESC
- SortType: TopN
- ChunksSorter: 132.915ms
- 1-BuildingTime: 157.305us
- 2-SortingTime: 754.802us
- 3-MergingTime: 1.357ms
- 4-OutputTime: 64.755us
- PeakMemoryUsage: 185.84 MB
- RowsReturned: 50
- RowsReturnedRate: 5
OLAP_SCAN_NODE (id=0):(Active: 9s563ms[9563760753ns], % non-child: 3.99%)
- Table: ods_bid_list
- Rollup: list_test
- Predicates: 12: BID_TITLE LIKE ‘%计算机%’, 16: BID_TYPE NOT IN (‘100’, ‘117’), 23: PUBLISH_TM IS NOT NULL
- BytesRead: 6.03 GB
- ExprFilterTime: 434.495ms
- NumDiskAccess: 0
- PeakMemoryUsage: 157.75 MB
- PerReadThreadRawHdfsThroughput: 0.0 /sec
- RowsRead: 0
- RowsReturned: 34.994K (34994)
- RowsReturnedRate: 3.659K /sec
- ScanTime: 9s623ms
- ScannerThreadsInvoluntaryContextSwitches: 0
- ScannerThreadsTotalWallClockTime: 0ns
- MaterializeTupleTime(): 0ns
- ScannerThreadsSysTime: 0ns
- ScannerThreadsUserTime: 0ns
- ScannerThreadsVoluntaryContextSwitches: 0
- TabletCount : 1
- TotalRawReadTime(): 0ns
- TotalReadThroughput: 0.0 /sec
SCAN:(Active: 9s165ms[9165373160ns], % non-child: 91.74%)
- CachedPagesNum: 0
- CompressedBytesRead: 630.33 MB
- CreateSegmentIter: 1.734ms
- DictDecode: 2s618ms
- IOTime: 1s79ms
- LateMaterialize: 563.391ms
- PushdownPredicates: 1
- RawRowsRead: 7.268107M (7268107)
- SegmentInit: 140.775ms
- BitmapIndexFilter: 27.558us
- BitmapIndexFilterRows: 0
- BloomFilterFilterRows: 0
- ShortKeyFilterRows: 0
- ZoneMapIndexFilterRows: 0
- SegmentRead: 5s790ms
- BlockFetch: 5s762ms
- BlockFetchCount: 1.782K (1782)
- BlockSeek: 1.30ms
- BlockSeekCount: 20
- ChunkCopy: 71.266us
- DecompressT: 1s367ms
- DelVecFilterRows: 0
- IndexLoad: 0ns
- PredFilter: 1.698ms
- PredFilterRows: 0
- TotalPagesNum: 68.135K (68135)
- UncompressedBytesRead: 3.27 GB

#4

Instance 77be7fe1-8d86-11ec-9540-a0b3cc1d1374 (host=TNetworkAddress(hostname:172.16.42.42, port:9060)):(Active: 9s635ms[9635742583ns], % non-child: 0.00%)
- AverageThreadTokens: 4607182418800017400.00
- MemoryLimit: 4.00 GB
- PeakMemoryUsage: 284.37 MB
- PeakReservation: 0.00
- PeakUsedReservation: 0.00
- RowsProduced: 50
BlockMgr:
- BlockWritesOutstanding: 0
- BlocksCreated: 0
- BlocksRecycled: 0
- BufferedPins: 0
- BytesWritten: 0.00
- MaxBlockSize: 8.00 MB
- TotalBufferWaitTime: 0ns
- TotalReadBlockTime: 0ns
DataStreamSender (dst_id=2, dst_fragments=[77be7fe18d8611ec-9540a0b3cc1d137b]):(Active: 529.598us[529598ns], % non-child: 0.01%)
- PartType: UNPARTITIONED
- BytesSent: 5.96 KB
- CompressTime: 56.39us
- IgnoreRows: 0
- OverallThroughput: 10.989985466003418 MB/sec
- PeakMemoryUsage: 192.00 B
- SendRequestTime: 70.487us
- SerializeBatchTime: 37.437us
- ShuffleDispatchTime: 0ns
- ShuffleHashTime: 0ns
- UncompressedBytes: 44.19 KB
- WaitResponseTime: 316.330us
SORT_NODE (id=1):(Active: 9s635ms[9635315628ns], % non-child: 1.36%)
- SortKeys: 23: PUBLISH_TM DESC
- SortType: TopN
- ChunksSorter: 134.888ms
- 1-BuildingTime: 179.323us
- 2-SortingTime: 746.395us
- 3-MergingTime: 1.86ms
- 4-OutputTime: 55.311us
- PeakMemoryUsage: 158.14 MB
- RowsReturned: 50
- RowsReturnedRate: 5
OLAP_SCAN_NODE (id=0):(Active: 9s499ms[9499436581ns], % non-child: 3.70%)
- Table: ods_bid_list
- Rollup: list_test
- Predicates: 12: BID_TITLE LIKE ‘%计算机%’, 16: BID_TYPE NOT IN (‘100’, ‘117’), 23: PUBLISH_TM IS NOT NULL
- BytesRead: 6.00 GB
- ExprFilterTime: 437.86ms
- NumDiskAccess: 0
- PeakMemoryUsage: 150.04 MB
- PerReadThreadRawHdfsThroughput: 0.0 /sec
- RowsRead: 0
- RowsReturned: 35.097K (35097)
- RowsReturnedRate: 3.694K /sec
- ScanTime: 9s591ms
- ScannerThreadsInvoluntaryContextSwitches: 0
- ScannerThreadsTotalWallClockTime: 0ns
- MaterializeTupleTime(): 0ns
- ScannerThreadsSysTime: 0ns
- ScannerThreadsUserTime: 0ns
- ScannerThreadsVoluntaryContextSwitches: 0
- TabletCount : 1
- TotalRawReadTime(): 0ns
- TotalReadThroughput: 0.0 /sec
SCAN:(Active: 9s129ms[9129972925ns], % non-child: 91.38%)
- CachedPagesNum: 0
- CompressedBytesRead: 629.01 MB
- CreateSegmentIter: 1.674ms
- DictDecode: 2s630ms
- IOTime: 985.632ms
- LateMaterialize: 536.541ms
- PushdownPredicates: 1
- RawRowsRead: 7.232777M (7232777)
- SegmentInit: 133.486ms
- BitmapIndexFilter: 23.724us
- BitmapIndexFilterRows: 0
- BloomFilterFilterRows: 0
- ShortKeyFilterRows: 0
- ZoneMapIndexFilterRows: 0
- SegmentRead: 5s776ms
- BlockFetch: 5s748ms
- BlockFetchCount: 1.777K (1777)
- BlockSeek: 1.36ms
- BlockSeekCount: 20
- ChunkCopy: 68.390us
- DecompressT: 1s374ms
- DelVecFilterRows: 0
- IndexLoad: 0ns
- PredFilter: 1.775ms
- PredFilterRows: 0
- TotalPagesNum: 67.874K (67874)
- UncompressedBytesRead: 3.26 GB
Instance 77be7fe1-8d86-11ec-9540-a0b3cc1d1378 (host=TNetworkAddress(hostname:172.16.42.43, port:9060)):(Active: 9s611ms[9611201272ns], % non-child: 0.00%)
- AverageThreadTokens: 4607182418800017400.00
- MemoryLimit: 4.00 GB
- PeakMemoryUsage: 309.57 MB
- PeakReservation: 0.00
- PeakUsedReservation: 0.00
- RowsProduced: 50
BlockMgr:
- BlockWritesOutstanding: 0
- BlocksCreated: 0
- BlocksRecycled: 0
- BufferedPins: 0
- BytesWritten: 0.00
- MaxBlockSize: 8.00 MB
- TotalBufferWaitTime: 0ns
- TotalReadBlockTime: 0ns
DataStreamSender (dst_id=2, dst_fragments=[77be7fe18d8611ec-9540a0b3cc1d137b]):(Active: 642.949us[642949ns], % non-child: 0.01%)
- PartType: UNPARTITIONED
- BytesSent: 5.31 KB
- CompressTime: 44.197us
- IgnoreRows: 0
- OverallThroughput: 8.066083908081055 MB/sec
- PeakMemoryUsage: 192.00 B
- SendRequestTime: 68.983us
- SerializeBatchTime: 19.897us
- ShuffleDispatchTime: 0ns
- ShuffleHashTime: 0ns
- UncompressedBytes: 38.53 KB
- WaitResponseTime: 449.953us
SORT_NODE (id=1):(Active: 9s610ms[9610690148ns], % non-child: 1.20%)
- SortKeys: 23: PUBLISH_TM DESC
- SortType: TopN
- ChunksSorter: 118.306ms
- 1-BuildingTime: 372.686us
- 2-SortingTime: 771.980us
- 3-MergingTime: 882.405us
- 4-OutputTime: 68.717us
- PeakMemoryUsage: 161.28 MB
- RowsReturned: 50
- RowsReturnedRate: 5
OLAP_SCAN_NODE (id=0):(Active: 9s491ms[9491267886ns], % non-child: 3.57%)
- Table: ods_bid_list
- Rollup: list_test
- Predicates: 12: BID_TITLE LIKE ‘%计算机%’, 16: BID_TYPE NOT IN (‘100’, ‘117’), 23: PUBLISH_TM IS NOT NULL
- BytesRead: 6.03 GB
- ExprFilterTime: 437.561ms
- NumDiskAccess: 0
- PeakMemoryUsage: 174.30 MB
- PerReadThreadRawHdfsThroughput: 0.0 /sec
- RowsRead: 0
- RowsReturned: 36.28K (36280)
- RowsReturnedRate: 3.822K /sec
- ScanTime: 9s594ms
- ScannerThreadsInvoluntaryContextSwitches: 0
- ScannerThreadsTotalWallClockTime: 0ns
- MaterializeTupleTime(): 0ns
- ScannerThreadsSysTime: 0ns
- ScannerThreadsUserTime: 0ns
- ScannerThreadsVoluntaryContextSwitches: 0
- TabletCount : 1
- TotalRawReadTime(): 0ns
- TotalReadThroughput: 0.0 /sec
SCAN:(Active: 9s135ms[9135062726ns], % non-child: 91.43%)
- CachedPagesNum: 0
- CompressedBytesRead: 631.30 MB
- CreateSegmentIter: 1.409ms
- DictDecode: 2s647ms
- IOTime: 1s176ms
- LateMaterialize: 561.12ms
- PushdownPredicates: 1
- RawRowsRead: 7.266534M (7266534)
- SegmentInit: 109.607ms
- BitmapIndexFilter: 20.489us
- BitmapIndexFilterRows: 0
- BloomFilterFilterRows: 0
- ShortKeyFilterRows: 0
- ZoneMapIndexFilterRows: 0
- SegmentRead: 5s767ms
- BlockFetch: 5s739ms
- BlockFetchCount: 1.784K (1784)
- BlockSeek: 1.110ms
- BlockSeekCount: 20
- ChunkCopy: 74.855us
- DecompressT: 1s286ms
- DelVecFilterRows: 0
- IndexLoad: 0ns
- PredFilter: 1.653ms
- PredFilterRows: 0
- TotalPagesNum: 68.174K (68174)
- UncompressedBytesRead: 3.28 GB
Instance 77be7fe1-8d86-11ec-9540-a0b3cc1d1371 (host=TNetworkAddress(hostname:172.16.42.41, port:9060)):(Active: 9s527ms[9527987099ns], % non-child: 0.00%)
- AverageThreadTokens: 4607182418800017400.00
- MemoryLimit: 4.00 GB
- PeakMemoryUsage: 285.05 MB
- PeakReservation: 0.00
- PeakUsedReservation: 0.00
- RowsProduced: 50
BlockMgr:
- BlockWritesOutstanding: 0
- BlocksCreated: 0
- BlocksRecycled: 0
- BufferedPins: 0
- BytesWritten: 0.00
- MaxBlockSize: 8.00 MB
- TotalBufferWaitTime: 0ns
- TotalReadBlockTime: 0ns
DataStreamSender (dst_id=2, dst_fragments=[77be7fe18d8611ec-9540a0b3cc1d137b]):(Active: 759.388us[759388ns], % non-child: 0.01%)
- PartType: UNPARTITIONED
- BytesSent: 4.67 KB
- CompressTime: 46.438us
- IgnoreRows: 0
- OverallThroughput: 6.005455017089844 MB/sec
- PeakMemoryUsage: 192.00 B
- SendRequestTime: 50.370us
- SerializeBatchTime: 18.263us
- ShuffleDispatchTime: 0ns
- ShuffleHashTime: 0ns
- UncompressedBytes: 37.00 KB
- WaitResponseTime: 575.23us
SORT_NODE (id=1):(Active: 9s527ms[9527394105ns], % non-child: 1.35%)
- SortKeys: 23: PUBLISH_TM DESC
- SortType: TopN
- ChunksSorter: 134.194ms
- 1-BuildingTime: 167.375us
- 2-SortingTime: 749.806us
- 3-MergingTime: 784.437us
- 4-OutputTime: 43.83us
- PeakMemoryUsage: 160.34 MB
- RowsReturned: 50
- RowsReturnedRate: 5
OLAP_SCAN_NODE (id=0):(Active: 9s392ms[9392137040ns], % non-child: 3.40%)
- Table: ods_bid_list
- Rollup: list_test
- Predicates: 12: BID_TITLE LIKE ‘%计算机%’, 16: BID_TYPE NOT IN (‘100’, ‘117’), 23: PUBLISH_TM IS NOT NULL
- BytesRead: 6.03 GB
- ExprFilterTime: 434.585ms
- NumDiskAccess: 0
- PeakMemoryUsage: 167.95 MB
- PerReadThreadRawHdfsThroughput: 0.0 /sec
- RowsRead: 0
- RowsReturned: 35.539K (35539)
- RowsReturnedRate: 3.783K /sec
- ScanTime: 9s510ms
- ScannerThreadsInvoluntaryContextSwitches: 0
- ScannerThreadsTotalWallClockTime: 0ns
- MaterializeTupleTime(): 0ns
- ScannerThreadsSysTime: 0ns
- ScannerThreadsUserTime: 0ns
- ScannerThreadsVoluntaryContextSwitches: 0
- TabletCount : 1
- TotalRawReadTime(): 0ns
- TotalReadThroughput: 0.0 /sec
SCAN:(Active: 9s52ms[9052182582ns], % non-child: 90.61%)
- CachedPagesNum: 0
- CompressedBytesRead: 631.93 MB
- CreateSegmentIter: 1.724ms
- DictDecode: 2s603ms
- IOTime: 1s136ms
- LateMaterialize: 600.888ms
- PushdownPredicates: 1
- RawRowsRead: 7.266996M (7266996)
- SegmentInit: 118.860ms
- BitmapIndexFilter: 25.971us
- BitmapIndexFilterRows: 0
- BloomFilterFilterRows: 0
- ShortKeyFilterRows: 0
- ZoneMapIndexFilterRows: 0
- SegmentRead: 5s678ms
- BlockFetch: 5s650ms
- BlockFetchCount: 1.782K (1782)
- BlockSeek: 1.104ms
- BlockSeekCount: 20
- ChunkCopy: 70.722us
- DecompressT: 1s296ms
- DelVecFilterRows: 0
- IndexLoad: 0ns
- PredFilter: 1.754ms
- PredFilterRows: 0
- TotalPagesNum: 68.336K (68336)
- UncompressedBytesRead: 3.29 GB
Instance 77be7fe1-8d86-11ec-9540-a0b3cc1d1373 (host=TNetworkAddress(hostname:172.16.42.41, port:9060)):(Active: 9s472ms[9472395102ns], % non-child: 0.00%)
- AverageThreadTokens: 4607182418800017400.00
- MemoryLimit: 4.00 GB
- PeakMemoryUsage: 289.55 MB
- PeakReservation: 0.00
- PeakUsedReservation: 0.00
- RowsProduced: 50
BlockMgr:
- BlockWritesOutstanding: 0
- BlocksCreated: 0
- BlocksRecycled: 0
- BufferedPins: 0
- BytesWritten: 0.00
- MaxBlockSize: 8.00 MB
- TotalBufferWaitTime: 0ns
- TotalReadBlockTime: 0ns
DataStreamSender (dst_id=2, dst_fragments=[77be7fe18d8611ec-9540a0b3cc1d137b]):(Active: 714.788us[714788ns], % non-child: 0.01%)
- PartType: UNPARTITIONED
- BytesSent: 4.80 KB
- CompressTime: 48.33us
- IgnoreRows: 0
- OverallThroughput: 6.552284240722656 MB/sec
- PeakMemoryUsage: 192.00 B
- SendRequestTime: 85.10us
- SerializeBatchTime: 29.325us
- ShuffleDispatchTime: 0ns
- ShuffleHashTime: 0ns
- UncompressedBytes: 38.59 KB
- WaitResponseTime: 492.668us
SORT_NODE (id=1):(Active: 9s471ms[9471789144ns], % non-child: 1.37%)
- SortKeys: 23: PUBLISH_TM DESC
- SortType: TopN
- ChunksSorter: 135.209ms
- 1-BuildingTime: 183.986us
- 2-SortingTime: 731.724us
- 3-MergingTime: 1.10ms
- 4-OutputTime: 58.596us
- PeakMemoryUsage: 157.48 MB
- RowsReturned: 50
- RowsReturnedRate: 5
OLAP_SCAN_NODE (id=0):(Active: 9s335ms[9335405042ns], % non-child: 3.37%)
- Table: ods_bid_list
- Rollup: list_test
- Predicates: 12: BID_TITLE LIKE ‘%计算机%’, 16: BID_TYPE NOT IN (‘100’, ‘117’), 23: PUBLISH_TM IS NOT NULL
- BytesRead: 6.02 GB
- ExprFilterTime: 431.564ms
- NumDiskAccess: 0
- PeakMemoryUsage: 157.65 MB
- PerReadThreadRawHdfsThroughput: 0.0 /sec
- RowsRead: 0
- RowsReturned: 35.107K (35107)
- RowsReturnedRate: 3.76K /sec
- ScanTime: 9s455ms
- ScannerThreadsInvoluntaryContextSwitches: 0
- ScannerThreadsTotalWallClockTime: 0ns
- MaterializeTupleTime(): 0ns
- ScannerThreadsSysTime: 0ns
- ScannerThreadsUserTime: 0ns
- ScannerThreadsVoluntaryContextSwitches: 0
- TabletCount : 1
- TotalRawReadTime(): 0ns
- TotalReadThroughput: 0.0 /sec
SCAN:(Active: 8s998ms[8998859777ns], % non-child: 90.07%)
- CachedPagesNum: 0
- CompressedBytesRead: 631.00 MB
- CreateSegmentIter: 1.348ms
- DictDecode: 2s602ms
- IOTime: 1s107ms
- LateMaterialize: 531.776ms
- PushdownPredicates: 1
- RawRowsRead: 7.261916M (7261916)
- SegmentInit: 110.141ms
- BitmapIndexFilter: 23.265us
- BitmapIndexFilterRows: 0
- BloomFilterFilterRows: 0
- ShortKeyFilterRows: 0
- ZoneMapIndexFilterRows: 0
- SegmentRead: 5s703ms
- BlockFetch: 5s675ms
- BlockFetchCount: 1.784K (1784)
- BlockSeek: 1.311ms
- BlockSeekCount: 20
- ChunkCopy: 71.714us
- DecompressT: 1s297ms
- DelVecFilterRows: 0
- IndexLoad: 0ns
- PredFilter: 1.687ms
- PredFilterRows: 0
- TotalPagesNum: 68.214K (68214)
- UncompressedBytesRead: 3.28 G B

#5

你好，目前看主要时间话费在scan上，辛苦将profile放到一个文件里上传下。另外您现在分桶设置的多少？并行度设置的多少？

#6

profile.txt (163.4 KB)
现在是新建了一个分桶设置了99个的建表语句后面是这样的

#7

ENGINE=OLAP
DUPLICATE KEY(PUBLISH_TM,ROWID)
COMMENT “OLAP”
DISTRIBUTED BY HASH(PUBLISH_TM) BUCKETS 99
PROPERTIES (
“replication_num” = “3”,
“in_memory” = “true”,
“storage_format” = “DEFAULT”
);

#8

里面有一千多万的测试数，查询效率还是很慢
11秒

#9

并行度是在哪里看

#10

show variables like ‘%para%’

#11

INSERT INTO MY_TABLE(Variable_name, Value) VALUES (‘parallel_exchange_instance_num’, ‘-1’);
INSERT INTO MY_TABLE(Variable_name, Value) VALUES (‘parallel_fragment_exec_instance_num’, ‘14’);

#12

parallel_exchange_instance_num’, ‘-1’
‘parallel_fragment_exec_instance_num’, ‘14’

#13

这个有数据倾斜，可以show tablet from $table看下每个tablet的数据量

image
image1884×380 29.7 KB

#14

Result_1.csv (71.2 KB)

#15

这个是查询出来的结果

#16

你有一个桶的数据量（datasize）为4017292224，其他都是几十万左右，这些差距太大了，导致这个桶的查询拉低了整体的时延。StarRocks查询scan性能分析 参考下这里数据倾斜的部分，这张表需要重新设计下，更改并选择合适的hash键，可以通过hash键分组count下，每组结果差距不大的话就是比较合理的。

#17

老哥这些信息在哪看的，我们最近在使用starrcosk也遇到了这个问题，使用order by的时候比mysql还慢的多

#18

获取Profile，通过Profile分析查询瓶颈 - StarRocks用户问答 / 常见 FAQ - StarRocks数据库论坛 开启profile,将慢查询的profile发一下，建议单独开个帖子

#19

命令行执行set is_report_success = true

#20

然后浏览器访问http:服务器ip：http端口（默认8030），然后执行慢SQL去刷新网页端能在query里找到你执行的SQL，后面profile

京ICP备2022026421号-1