http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> ```[0mSummary[0m [0mQueryId: 01...
# questions-and-troubleshooting
m
@Rocky
Copy code
[0mSummary[0m
    [0mQueryId: 019f1e96-edac-7453-8f94-ebc09f1c3026[0m
    [0mVersion: 4.0.7-b75f536[0m
    [0mState: Finished[0m
    [0mTotalTime: 4s186ms[0m
"        [0mExecutionTime: 4s174ms [Scan: 3s974ms (95.22%), Network: 14.429ms (0.35%), ResultDeliverTime: 0ns (0.00%), ScheduleTime: 4s87ms (97.92%)][0m"
        [0mCollectProfileTime: 5ms[0m
        [0mFrontendProfileMergeTime: 2.038ms[0m
"    [0mQueryPeakMemoryUsage: ?, QueryAllocatedMemoryUsage: 750.677 GB[0m"
    [0mTop Most Time-consuming Nodes:[0m
        [1m[31m1. OLAP_SCAN (id=0) : 4s19ms (91.23%)[0m
"        [0m2. TOP_N (id=2) [ROW_NUMBER, TOP-N]: 225.153ms (5.11%)[0m"
        [0m3. MERGE_EXCHANGE (id=3) [GATHER]: 105.998ms (2.41%)[0m
        [0m4. RESULT_SINK: 52.632ms (1.19%)[0m
        [0m5. PROJECT (id=4) : 2.154ms (0.05%)[0m
        [0m6. PROJECT (id=1) : 648.265us (0.01%)[0m
    [0mTop Most Memory-consuming Nodes:[0m
    [0mNonDefaultVariables:[0m
        [0mcharacter_set_results: utf8 -> utf8mb4[0m
        [0menable_adaptive_sink_dop: false -> true[0m
        [0menable_async_profile: true -> false[0m
        [0menable_profile: false -> true[0m
        [0mnet_write_timeout: 60 -> 600[0m
        [0msql_mode_v2: 32 -> 2097184[0m
[0mFragment 0[0m
│   [0mBackendNum: 1[0m
"│   [0mInstancePeakMemoryUsage: 45.501 MB, InstanceAllocatedMemoryUsage: 258.779 MB[0m"
│   [0mPrepareTime: ?[0m
└──[0mRESULT_SINK[0m
   │   [0mTotalTime: 52.632ms (1.19%) [CPUTime: 52.632ms][0m
   │   [0mOutputRows: 50.000K (50000)[0m
   │   [0mSinkType: MYSQL_PROTOCAL[0m
   └──[0mPROJECT (id=4) [0m
"      │   [0mEstimates: [row: ?, cpu: ?, memory: ?, network: ?, cost: ?][0m"
      │   [0mTotalTime: 2.154ms (0.05%) [CPUTime: 2.154ms][0m
      │   [0mOutputRows: 50.000K (50000)[0m
"      │   [0mExpression: [1: received, 2: auction_id, 3: imp_key, 25: app_bundle, 50: app_name, ...][0m"
      └──[0mMERGE_EXCHANGE (id=3) [GATHER][0m
"             [0mEstimates: [row: 50000, cpu: ?, memory: ?, network: ?, cost: 1.6216928022179553E11][0m"
"             [0mTotalTime: 105.998ms (2.41%) [CPUTime: 91.568ms, NetworkTime: 14.429ms][0m"
             [0mOutputRows: 50.000K (50000)[0m
"             [0mPeakMemory: ?, AllocatedMemory: ?[0m"
[0m
[0mFragment 1[0m
│   [0mBackendNum: 12[0m
"│   [0mInstancePeakMemoryUsage: 390.470 MB, InstanceAllocatedMemoryUsage: 750.424 GB[0m"
│   [0mPrepareTime: ?[0m
└──[0mDATA_STREAM_SINK (id=3)[0m
   │   [0mPartitionType: UNPARTITIONED[0m
"   └──[0mTOP_N (id=2) [ROW_NUMBER, TOP-N][0m"
"      │   [0mEstimates: [row: 50000, cpu: ?, memory: ?, network: ?, cost: 1.6213408022179553E11][0m"
      │   [0mTotalTime: 225.153ms (5.11%) [CPUTime: 225.153ms][0m
      │   [0mOutputRows: 600.000K (600000)[0m
"      │   [0mPeakMemory: ?, AllocatedMemory: ?[0m"
"      │   [0mOrderByExprs: [<slot 1> 1: received, <slot 2> 2: auction_id, <slot 3> 3: imp_key][0m"
      └──[0mPROJECT (id=1) [0m
"         │   [0mEstimates: [row: ?, cpu: ?, memory: ?, network: ?, cost: ?][0m"
         │   [0mTotalTime: 648.265us (0.01%) [CPUTime: 648.265us][0m
         │   [0mOutputRows: 10.069M (10068877)[0m
"         │   [0mExpression: [1: received, 2: auction_id, 3: imp_key, 25: app_bundle, 50: app_name, ...][0m"
         └──[1m[31mOLAP_SCAN (id=0) [0m
"                [1m[31mEstimates: [row: 377016000, cpu: ?, memory: ?, network: ?, cost: 3.2423376044359104E10][0m"
"                [1m[31mTotalTime: 4s19ms (91.23%) [CPUTime: 44.528ms, ScanTime: 3s974ms][0m"
                [1m[31mOutputRows: 10.069M (10068877)[0m
                [1m[31mRuntimeFilter: 10.069M (10068877) -> 10.069M (10068877) (0.00%)[0m
                [1m[31mTable: : requests[0m
                [1m[31mSubordinateOperators: [0m
                    [1m[31mNOOP[0m
                    [1m[31mOLAP_SCAN_PREPARE[0m
                [1m[31mDetail Timers: [ScanTime = IOTaskExecTime + IOTaskWaitTime][0m
"                    [1m[31mIOTaskExecTime: 1s433ms [min=442.831ms, max=2s843ms][0m"
"                        [1m[31mLateMaterialize: 773.897ms [min=193.571ms, max=1s889ms][0m"
"                    [1m[31mIOTaskWaitTime: 1s488ms [min=282.270ms, max=2s974ms][0m"
[0m
Explain why for this query profile which uses keyset pagination, it actually scanned more data (hence more I/O contention) that a previous one that used offset pagination.
r
Based on the query profile provided, the reason your keyset pagination query scanned more data (and experienced higher I/O contention) than the offset-based version likely stems from a Prefix Index (Short-key Index) mismatch or inefficient predicate pushdown for the multi-column keyset. Here is the breakdown of why this occurred: 1. High Scan Output (The Root Cause) In your profile, the 
OLAP_SCAN (id=0)
 node output 10.069M rows. * Ideally, keyset pagination should use the Prefix Index to jump directly to the first row of the page, scanning only the rows needed for that page (plus a small overhead). * Outputting 10 million rows indicates that the storage engine was unable to "seek" to the starting point. Instead, it performed a massive scan and applied the keyset filters (
received > ...
) in memory or during the vectorized scan process, rather than at the index level. 2. Prefix Index (Sort Key) Mismatch StarRocks speeds up range scans using a Prefix Index, which consists of the first 36 bytes of the table's Sort Key (defined by the 
ORDER BY
clause in the 
CREATE TABLE
statement). * The Issue: If the columns used for your keyset—`received`, 
auction_id
, and `imp_key`—are not the leading columns of the table's Sort Key, StarRocks cannot use the Prefix Index to skip data. * Result: The engine must scan all segments in the relevant tablets and manually evaluate the filter for every row. This explains the high 
IOTaskExecTime
(1.43s) and 
IOTaskWaitTime
(1.48s). 3. Complexity of Tuple Comparisons 
(a, b, c) > (v1, v2, v3)
 Keyset pagination typically involves comparing a tuple of columns. In SQL, 
(col1, col2) > (val1, val2)
is logically equivalent to: 
col1 > val1 OR (col1 = val1 AND col2 > val2)
* While StarRocks optimizes many predicates, complex 
OR
logic is often harder for the storage engine to translate into a single range seek on disk compared to a simple 
col1 > val1
. * If the optimizer expanded this into a complex tree, it might have defaulted to a broader scan, especially if the columns aren't perfectly aligned with the storage order. 4. Why Offset might have been "faster" or "less I/O" If your previous Offset query had a relatively small offset (e.g., 
LIMIT 50000 OFFSET 100000
), it only needed to find the first 150,000 rows. * If the Offset query was able to utilize Partition Pruning (e.g., if it filtered on a specific date) while the Keyset query lacked that filter or used a different column, the Offset query would naturally touch fewer tablets. _ In this Keyset profile, the 
RuntimeFilter
did zero work (
0.00%
efficiency), and the 
LateMaterialize
(773ms) suggests the engine was working hard to filter rows only _after* reading the filter columns from disk. Recommendations to Fix Performance: 1. Verify Sort Key: Check your table schema (
SHOW CREATE TABLE requests
). Ensure 
received
, 
auction_id
, and 
imp_key
are the first three columns in the 
DUPLICATE/UNIQUE/PRIMARY KEY
or 
ORDER BY
clause. 2. Use Partitioning: If the table is large, ensure you are still including a partition filter (like a date range) in your 
WHERE
clause alongside the keyset logic. Keyset pagination replaces 
OFFSET
, but it should not replace partition pruning. 3. Simplify the Keyset: If possible, test the performance using only the first column of the keyset (e.g., 
WHERE received > '...'
) to see if it hits the Prefix Index. If that is fast, the issue is the multi-column tuple optimization. 4. Check I/O Contention: Your 
IOTaskWaitTime
is nearly 1.5 seconds. This indicates the BE's I/O threads are saturated. Reducing the scan volume via the Sort Key will directly resolve this. References • best_practices/table_clustering.md • knowledge/usage/best-practice.md
m
@Rocky Only received and auction id are the sort keys. So will it work if i change the below query
Copy code
SELECT campaign_id,
         device_id_hashed,
         request_duid_hashed,
         UNIX_TIMESTAMP(received) AS received_ts,
         bid_price,
         min_bid_to_win,
         bid_floor,
         arrival_rate_score,
         lookalike_model_power,
         supply_chain_complete,
         app_name,
         app_bundle,
         received AS cursor_received,
         auction_id AS cursor_auction_id,
         imp_key AS cursor_imp_key
FROM requests.requests
WHERE bid_is_loss = 1
        AND bid_loss_reason = 102
        AND campaign_id IN ('2711716','3138150','3218300','3225884','3054358','3054359','2711715','3150914','3138151','3150889','3218298','3225883','3218299','3225882','3218301','3138152','2711717','3054360','3150910','2711713','3054361','3150917')
        AND received >= '2026-06-29 00:00:00'
        AND received <= '2026-06-30 23:59:59'
        AND received >= '2026-06-30 17:16:32'
        AND ( received > '2026-06-30 17:16:32'
        OR (received = '2026-06-30 17:16:32'
        AND (auction_id > '1-8163-178283979220187815117746-1'
        OR (auction_id = '1-8163-178283979220187815117746-1'
        AND imp_key > '1'))) )
ORDER BY  received, auction_id, imp_key LIMIT 50000
To remove the imp_key comparison? So received > last_received OR (received = last_received and auction_id > last_auction_id)
r
Yes, changing the query to remove the 
imp_key
comparison will significantly improve performance and reduce I/O contention. Why this change helps The 10M rows scanned in your profile indicates that StarRocks performed a Full Segment Scan instead of a Prefix Index Seek. Here is why: 1. Prefix Index Alignment: StarRocks builds a sparse "Prefix Index" (short-key index) using the first 36 bytes of your Sort Key (
received
and 
auction_id
). 2. The "Jump" Mechanism: For keyset pagination to be fast, the storage engine must "jump" (seek) to the specific data block where the last page ended. This only works if the columns in your 
WHERE
clause comparison exactly match the leading prefix of the Sort Key. 3. The 
imp_key
Problem: Because 
imp_key
is not in the Sort Key, your current complex nested 
OR
logic including 
imp_key
prevents the optimizer from treating the filter as a simple range seek. The engine likely decides it cannot safely use the index to find the starting point, so it scans all data within the broader time range and filters the 10 million rows in memory. Recommendation By simplifying the logic to only use the indexed columns, the storage engine can use the Prefix Index to skip directly to the first row of your next page. Optimized Query Pattern: Instead of the manual 
OR
expansion, you can use the Tuple Comparison syntax, which is cleaner and well-optimized in StarRocks:
Copy code
sql
WHERE received >= '2026-06-30 17:16:32' -- Broad range for partition/index pruning
  AND (received, auction_id) > ('2026-06-30 17:16:32', '1-8163-178283979220187815117746-1')
ORDER BY received, auction_id, imp_key 
LIMIT 50000
Important Considerations: * Uniqueness: Keyset pagination requires a unique key to prevent skipping or duplicating rows. If 
(received, auction_id)
is not 100% unique (i.e., multiple rows can have the same timestamp and same auction ID), you might occasionally skip a row that has the same prefix but a different 
imp_key
. * The Best Fix: If you need the precision of 
imp_key
, you should add it to the table's Sort Key. Since StarRocks v3.3+, you can modify the sort key of an existing table (Duplicate, Unique, or Aggregate key models) asynchronously:
Copy code
sql
    ALTER TABLE requests.requests ORDER BY (received, auction_id, imp_key);
Once this background task finishes, your original query (including 
imp_key
in the 
WHERE
clause) will become extremely fast because the Prefix Index will now cover all three columns. Summary of Benefits: * Scan Rows: Will drop from 10.069M to approximately 50,000 (the limit). * IOTaskExecTime: Will drop from 1.4s to milliseconds. * I/O Contention: Will virtually disappear as the engine no longer needs to read millions of unnecessary rows from disk. References • knowledge/usage/best-practice.md • table_design/indexes/Prefix_index_sort_key.md References • knowledge/usage/best-practice.md • table_design/indexes/Bitmap_index.md • using_starrocks/caching/query_cache.md
m
@Rocky It doesn't seem to work. I changed to use keyset pagination on received and auction id only but the scan bytes is worse. The keyset pagination queries scanned ~5x more data than offset pagination, but did use 3x less memory. My main concern is EBS I/O contention causing slower partial updates.
r
In a Primary Key table (implied by your use of partial updates), the reason keyset pagination is scanning more data than offset pagination—and causing I/O contention—is likely due to the selectivity of your non-sort-key filters (like 
campaign_id
, 
bid_is_loss
, etc.). 1. The "Sparse Match" Problem The profile shows that the 
OLAP_SCAN
node output 10.069M rows just to find your 50,000 results. This means only ~0.5% of the rows in that time range match your criteria. * Why Offset was "Faster": If your previous offset query was for an early page (e.g., 
OFFSET 0
), StarRocks started at the beginning of the segment and stopped as soon as it found the first 50,000 matches. If those matches happened to be near the start of the data, it scanned very little. * Why Keyset is "Worse": Keyset pagination forces the engine to start at a specific point in the Sort Key (e.g., 
2026-06-30 17:16:32
). If the specific 
campaign_ids
you are filtering for are sparse at that point in the table, the engine must scan millions of rows forward to find the next 50,000 matches. 2. Why this causes I/O Contention with Partial Updates Your high 
IOTaskWaitTime
(1.48s) confirms the BE is waiting on EBS throughput. This directly harms partial updates because: 1. Metadata Competition: Partial updates in PK tables require reading the Primary Key index and DelVectors (delete markers) from disk. These small, critical reads are getting queued behind your massive 10M-row scan. 2. Version Bloat: If you have high-frequency partial updates, you likely have many small rowsets. A 10M-row scan must check the DelVector for every rowset to ensure it's reading the latest version, multiplying the I/O load. 3. The Solution: Reduce "Rows Read" via Bitmap Indexes Since you are filtering on 
campaign_id
, 
bid_is_loss
, and 
bid_loss_reason
, and these are clearly very selective, you should add Bitmap Indexes to them. Why this fixes it: Without a Bitmap index, StarRocks must read the actual data for these columns from EBS to evaluate the 
WHERE
clause. With a Bitmap index, StarRocks can determine which rows to skip using only index metadata. Action Plan: 1. Add Bitmap Indexes:
Copy code
sql
    ALTER TABLE requests.requests ADD INDEX idx_campaign (campaign_id) USING BITMAP;
    ALTER TABLE requests.requests ADD INDEX idx_loss (bid_is_loss) USING BITMAP;
2. Check Compaction: If your "partial updates" are frequent, check your cumulative compaction score. High scores mean too many rowsets, which slows down both scans and updates.
Copy code
sql
    -- Check if any tablets have a high version count (>100)
    SHOW PROC '/statistic';
3. Refine the Logic: Ensure you are using the simplified tuple comparison to allow the Prefix Index to at least "seek" to the starting row quickly:
Copy code
sql
    WHERE (received, auction_id) > ('2026-06-30 17:16:32', '1-8163...')
By adding the indexes, the 
OLAP_SCAN
node should skip those 10M rows at the metadata level, drastically reducing EBS I/O and leaving the bandwidth available for your partial update "Apply" tasks. References • knowledge/usage/best-practice.md • table_design/indexes/Bitmap_index.md • table_design/table_types/primary_key_table.md • best_practices/primarykey_table.md table) References • knowledge/usage/best-practice.md • unloading/Spark_connector.md • table_design/indexes/Prefix_index_sort_key.md
m
@Rocky Isn't zone map indexes enough?
r
In many cases, Zone Maps (min/max indexes) are not enough, especially for columns that are not part of your Sort Key. Here is why Zone Maps are failing you in this specific scenario and why they are causing your EBS I/O contention: 1. Zone Maps vs. Data Distribution Zone Maps work at the Page level (typically 64KB - 1MB blocks). * The Sorting Problem: Your table is sorted by 
received
. This means the Zone Maps for 
received
are extremely effective—they allow StarRocks to skip entire segments and pages that are outside your time range. * The "Scattered" Data Problem: Columns like 
campaign_id
, 
bid_is_loss
, and 
bid_loss_reason
are likely not sorted. In any given data page, there is a high probability that multiple campaign IDs exist. * The Result: If a page's Zone Map says 
min_campaign_id = 100
and 
max_campaign_id = 5000000
, and your query asks for 
campaign_id = 2711716
, the engine must load that entire page from EBS to check the individual rows. Because your campaigns are scattered, the Zone Map "overlaps" with your filter on almost every page, forcing a "Scan" instead of a "Skip." 2. Why Keyset Pagination makes it worse than Offset * Offset Early Exit: In some cases, 
OFFSET
queries (especially small offsets) allow the engine to stop scanning as soon as it finds the first 
N
matching rows from the start of a segment. * Keyset Exhaustive Scan: Keyset pagination (
(received, auction) > (...)
) forces the engine to start deep within the sorted data. If the specific 
campaign_id
values you are looking for are sparse in that specific part of the table, StarRocks has to pull page after page from EBS, checking each one, until it finally collects 50,000 matches. 3. Why this triggers EBS Contention Because the Zone Maps aren't selective enough for your 
campaign_id
filter, StarRocks is performing heavy I/O: 1. It reads the 
received
and 
auction_id
columns to find the starting point. 2. It reads the 
campaign_id
, 
bid_is_loss
, and 
bid_loss_reason
columns for millions of rows to see if they match your 
WHERE
clause. 3. This massive "Read" volume saturates the EBS bandwidth, leaving no room for the small, high-priority I/O needed for Partial Updates (reading Primary Key indexes and Delete Vectors). How to fix it (Beyond Zone Maps) To reduce EBS I/O, you need an index that can filter rows without reading the data pages: 1. Bitmap Indexes: Ideal for 
bid_is_loss
or 
bid_loss_reason
(low cardinality). It allows the engine to know exactly which rows match before touching the data. 2. Bloom Filter Indexes: Excellent for 
campaign_id
(high cardinality). If a Bloom Filter says a campaign ID is not in a page, StarRocks skips the I/O for that page entirely. 3. Adjust the Sort Key: If 
campaign_id
is your most common filter, consider moving it earlier in the 
ORDER BY
clause of the table (e.g., 
ORDER BY (campaign_id, received, auction_id)
). This clusters the data by campaign, making Zone Maps highly effective again. Summary: Your current "Scan" is an I/O-bound process. Zone Maps are only helping you with the 
received
time range; they are likely providing 0% filtering for your campaign and loss-reason predicates, which is why you are seeing millions of rows scanned. References • table_design/indexes/Prefix_index_sort_key.md • table_design/indexes/Bitmap_index.md • table_design/indexes/Bloom_filter_index.md • knowledge/usage/best-practice.md
2 Views
Open in Slack
PreviousNext
Powered by