< Rocky> StarRocks #questions-and-troubleshooting

Join Slack

<@U0A71G31CDV>

# questions-and-troubleshooting

Lý Thành

06/12/2026, 2:37 AM

@Rocky

Untitled

Rocky

06/12/2026, 2:38 AM

Hello, @Lý Thành! How can I assist you today?

Lý Thành

06/12/2026, 2:39 AM

@Rocky

Rocky

06/12/2026, 2:39 AM

Hello, @Lý Thành! How can I assist you today?

Lý Thành

06/12/2026, 2:39 AM

@Rocky

Copy code

Query:
  Summary:
     - Query ID: 019eb9a8-7b53-7289-8f38-bc3f2b520228
     - Start Time: 2026-06-12 09:28:07 (+07:00)
     - End Time: 2026-06-12 09:30:37 (+07:00)
     - Total: 2m29s
     - Query Type: Query
     - Query State: Finished
     - StarRocks Version: 4.0.10-5b36164
     - User: root
     - Default Db: lps
     - Sql Statement: /* ApplicationName=DBeaver 25.3.2 - SQLEditor <Script-288.sql> */ INSERT /*+set_var(dynamic_overwrite = true, spill_mode = 'auto')*/ OVERWRITE lps.NHANNT33_V_DAILY_H
WITH SelectedDates AS (
    SELECT d.SYS_RUN_DAY AS load_date
    FROM lps.date_dim d
    WHERE d.SYS_RUN_DAY >= DATE_SUB(CURRENT_DATE(), INTERVAL 3 DAY)
      AND d.SYS_RUN_DAY < CURRENT_DATE()
)
,TargetDates AS (
    SELECT DISTINCT SYS_RUN_DAY FROM (
        SELECT d.SYS_RUN_DAY
        FROM lps.date_dim d
        INNER JOIN SelectedDates s ON d.SYS_RUN_DAY = s.load_date
        WHERE d.IS_PRESE = 1
        
        UNION ALL
        
        SELECT d.BOM_base AS SYS_RUN_DAY
        FROM lps.date_dim d
        INNER JOIN SelectedDates s ON d.SYS_RUN_DAY = s.load_date
        WHERE d.is_rpt = 1 AND d.IS_PRESE = 1
        
        UNION ALL
        
        SELECT d.BOM_base AS SYS_RUN_DAY
        FROM lps.date_dim d
        INNER JOIN SelectedDates s ON d.SYS_RUN_DAY = s.load_date
        WHERE d.IS_EOM = 1 AND d.IS_PRESE = 1 AND d.IS_LASTEST = 0
    ) sub
)
,eom_ AS (
    SELECT a.*
    FROM DMAPP.DTM_CUSTOMER_LD_SUMMARY a
    INNER JOIN TargetDates t ON a.SYS_RUN_DATE = t.SYS_RUN_DAY
)
,eom_distinct_acc AS (
    SELECT DISTINCT ACCOUNT_NUMBER FROM eom_
)
,T AS (
    SELECT
        DATE_FORMAT(s.SCHEDULE_DUE_DATE, '%Y-%m') AS REPORT_MONTH,
        s.ACCOUNT_NUMBER, 
        s.SCHEDULE_NO, 
        s.SCHEDULE_ST_DATE, 
        s.SCHEDULE_DUE_DATE,
        EXTRACT(DAY FROM s.SCHEDULE_DUE_DATE) AS due_day,
        SUM(CASE WHEN s.COMPONENT_NAME = 'PRINCIPAL' THEN s.AMOUNT_DUE ELSE 0 END) AS PRI_AMT,
        SUM(CASE WHEN s.COMPONENT_NAME = 'MAIN_INT' THEN s.AMOUNT_DUE ELSE 0 END) AS INT_AMT,
        SUM(s.AMOUNT_SETTLED) AS REPAY_AMT,
        SUM(s.EMI_AMOUNT) AS EMI_AMOUNT
    FROM dmapp.stg_clzb_account_schedules s
    INNER JOIN eom_distinct_acc e ON s.ACCOUNT_NUMBER = e.ACCOUNT_NUMBER
    WHERE s.SCHEDULE_NO IS NOT NULL
    GROUP BY 1, 2, 3, 4, 5, 6
)
,due_ AS (
    SELECT
        t.REPORT_MONTH, t.ACCOUNT_NUMBER,
        t.SCHEDULE_NO AS PERIOD_NO, t.SCHEDULE_ST_DATE AS start_date, t.SCHEDULE_DUE_DATE AS due_date,
        t.DUE_DAY, t.PRI_AMT, t.INT_AMT, t.REPAY_AMT,
        CEIL((COALESCE(t.EMI_AMOUNT, (t.PRI_AMT + t.INT_AMT)) + 12000) / 1000) * 1000 AS EMI_AMOUNT,
        ROW_NUMBER() OVER(PARTITION BY t.ACCOUNT_NUMBER, t.REPORT_MONTH ORDER BY t.SCHEDULE_NO ASC) AS row_num
    FROM T
)
,b_grouped AS (
    SELECT
        CONTRACT_NO,
        MIN(MONTH_BEGIN) AS MONTH_BEGIN,
        COALESCE(MAX(MONTH_END) - INTERVAL 1 DAY, CURRENT_DATE()) AS MONTH_END
    FROM lps.DCS_CCN_QTDM
    GROUP BY CONTRACT_NO
)
,processed_base AS (
    SELECT
        a.SYS_RUN_DATE,
        DATE_FORMAT(a.SYS_RUN_DATE, '%Y-%m') AS REPORT_MONTH,
        CASE WHEN a.SYS_RUN_DATE = LAST_DAY(a.SYS_RUN_DATE) THEN 1 ELSE 0 END AS IS_LAST_DAY,
        DATE_FORMAT(a.SYS_RUN_DATE + INTERVAL 1 DAY, '%Y-%m') AS REPORT_MONTH_BOM,
        a.CONTRACT_NO, a.LMS_CIF_NO, a.LD_NO, a.LD_DI_DATE, a.ACCOUNT_NUMBER,
        a.DRAWDOWN_AMOUNT_BASE AS LOAN_AMT, a.DPD_LD,
        CASE WHEN a.CONTRACT_STATUS = 'Close' THEN -1 ELSE CEIL(GREATEST(COALESCE(a.DPD_LD, 0), 0) / 30) END AS BUCKET_SUM,
        CASE WHEN a.CONTRACT_STATUS = 'WO' THEN 'WO' WHEN a.CONTRACT_STATUS = 'Close' THEN 'C' WHEN a.CONTRACT_STATUS = 'Active' THEN 'A' END AS STT,
        a.FIRST_PAYMENT_DATE, a.LAST_PAYMENT_DATE, a.LAST_PAYMENT_AMOUNT, a.OVER_DUE_DAYS_LD AS DPD_LMS,
        a.OUTSTANDING, a.DUE_DATE_OF_MON, a.PAYMENT_AMOUNT_NET_MTD, a.PAYMENT_AMOUNT_MTD, a.AMT_MUST_COLLECTED,
        ABS(a.CA_ACCT_BALANCE) AS CA_ACCT_BALANCE,
        CASE
            WHEN a.SYS_RUN_DATE <= '2025-08-31' THEN COALESCE(a.PRI_PAST_DUE, 0) + COALESCE(a.INT_PAST_DUE, 0) + COALESCE(a.OD_PAST_DUE, 0) + COALESCE(a.OD_UNBILL, 0)
            ELSE COALESCE(a.PRI_PAST_DUE, 0) + COALESCE(a.INT_PAST_DUE, 0) + COALESCE(a.OD_POSTED, 0)
        END AS TOTAL_OVER_DUE_AMOUNT,
        a.EMI_AMOUNT, a.TOTAL_EMI_AMOUNT_DCS AS EMI, a.MIN_MUST_PAYMENT, a.MIN_MUST_PAYMENT_DCS, a.TOTAL_MIN_MUST_PAYMENT_DCS,
        CASE WHEN b.CONTRACT_NO IS NOT NULL THEN 1 ELSE 0 END AS CCN_STT,
        CASE WHEN a.OD_PAST_DUE_MIN = 0 THEN a.PRI_PAST_DUE + a.INT_PAST_DUE_MIN ELSE a.PRI_PAST_DUE + a.INT_PAST_DUE + a.OD_PAST_DUE_MIN END AS MIN_MUST_PAYMENT_CIR06,
        CASE
            WHEN a.SYS_RUN_DATE <= '2025-08-31' AND a.LD_DI_DATE < '2023-09-01' THEN COALESCE(a.PRI_PAST_DUE_MIN, 0) + COALESCE(a.INT_PAST_DUE_MIN, 0) + COALESCE(a.OD_PAST_DUE_MIN, 0)
            WHEN a.SYS_RUN_DATE <= '2025-08-31' AND a.LD_DI_DATE >= '2023-09-01' AND COALESCE(a.OD_PAST_DUE_MIN, 0) = 0 THEN COALESCE(a.PRI_PAST_DUE, 0) + COALESCE(a.INT_PAST_DUE_MIN, 0)
            WHEN a.SYS_RUN_DATE <= '2025-08-31' AND a.LD_DI_DATE >= '2023-09-01' AND COALESCE(a.OD_PAST_DUE_MIN, 0) <> 0 THEN COALESCE(a.PRI_PAST_DUE, 0) + COALESCE(a.INT_PAST_DUE, 0) + COALESCE(a.OD_PAST_DUE_MIN, 0)
            WHEN a.SYS_RUN_DATE >= '2025-09-01' AND a.LD_DI_DATE < '2023-09-01' THEN COALESCE(a.PRI_PAST_DUE_MIN, 0) + COALESCE(a.INT_PAST_DUE_MIN, 0)
            ELSE COALESCE(a.PRI_PAST_DUE, 0) + COALESCE(a.INT_PAST_DUE_MIN, 0)
        END AS AMT_COLLECTED_TO_RESOLVE,
        a.PRI_REC_ALLOCATED_TO_DATE, a.INT_REC_ALLOCATED_TO_DATE, a.OD_REC_ALLOCATED_TO_DATE
    FROM eom_ a
    LEFT JOIN b_grouped b
        ON b.CONTRACT_NO = a.CONTRACT_NO AND b.MONTH_BEGIN <= a.SYS_RUN_DATE AND b.MONTH_END >= a.SYS_RUN_DATE
)
,ra AS (
    SELECT
        EOM.REPORT_MONTH, EOM.SYS_RUN_DATE,
        BOM.DPD_LD AS DPD_BOM, EOM.DPD_LD AS DPD_EOM,
        BOM.BUCKET_SUM AS WAS_BUCKET, LEAST(BOM.BUCKET_SUM, 7) AS WAS_BUCKET_SUM,
        EOM.BUCKET_SUM, LEAST(EOM.BUCKET_SUM, 7) AS BUCKET_SUM_,
        BOM.STT AS WAS_STT, EOM.STT, EOM.CONTRACT_NO, EOM.LD_DI_DATE, EOM.LD_NO,
        BOM.OUTSTANDING AS POS_BOM, EOM.OUTSTANDING AS POS_EOM, EOM.LAST_PAYMENT_DATE, EOM.LAST_PAYMENT_AMOUNT,
        CASE
            WHEN BOM.STT = 'C' OR BOM.STT IS NULL THEN NULL
            WHEN BOM.FIRST_PAYMENT_DATE > EOM.SYS_RUN_DATE THEN 0
            WHEN BOM.STT != 'C' AND EOM.STT = 'C' THEN 0
            WHEN BOM.BUCKET_SUM = 0 AND EOM.DPD_LD > 0 THEN BOM.OUTSTANDING
            WHEN BOM.BUCKET_SUM > 0 AND EOM.DPD_LD >= (BOM.DPD_LD + DATEDIFF(EOM.SYS_RUN_DATE, DATE_TRUNC('month', EOM.SYS_RUN_DATE))) THEN BOM.OUTSTANDING
            WHEN BOM.BUCKET_SUM > 0 AND EOM.BUCKET_SUM > BOM.BUCKET_SUM THEN BOM.OUTSTANDING
        END AS ROLL_POS,
        CASE
            WHEN BOM.STT = 'C' OR BOM.STT IS NULL THEN NULL
            WHEN EOM.FIRST_PAYMENT_DATE > EOM.SYS_RUN_DATE THEN BOM.OUTSTANDING
            WHEN BOM.STT != 'C' AND EOM.STT = 'C' THEN 0
            WHEN BOM.BUCKET_SUM = 0 AND EOM.DPD_LD <= 0 THEN BOM.OUTSTANDING
            WHEN BOM.BUCKET_SUM > 0 AND EOM.DPD_LD <= (BOM.DPD_LD + DATEDIFF(EOM.SYS_RUN_DATE, DATE_TRUNC('month', EOM.SYS_RUN_DATE))) AND EOM.BUCKET_SUM = BOM.BUCKET_SUM THEN BOM.OUTSTANDING
        END AS STAY_POS,
        CASE
            WHEN BOM.STT = 'C' OR BOM.STT IS NULL THEN NULL
            WHEN EOM.FIRST_PAYMENT_DATE > EOM.SYS_RUN_DATE THEN 0
            WHEN BOM.STT != 'C' AND EOM.STT = 'C' THEN BOM.OUTSTANDING
            WHEN BOM.BUCKET_SUM = 0 AND EOM.STT = 'C' THEN BOM.OUTSTANDING
            WHEN BOM.BUCKET_SUM > 0 AND EOM.DPD_LD <= 0 THEN BOM.OUTSTANDING
            WHEN BOM.BUCKET_SUM > 0 AND EOM.BUCKET_SUM < BOM.BUCKET_SUM THEN BOM.OUTSTANDING
        END AS BACK_POS,
        EOM.PAYMENT_AMOUNT_NET_MTD AS cash_nofee, EOM.PAYMENT_AMOUNT_MTD AS total_cash,
        CASE
            WHEN BOM.STT = 'C' THEN 0
            WHEN DATE_TRUNC('month', EOM.FIRST_PAYMENT_DATE) > DATE_TRUNC('month', EOM.SYS_RUN_DATE) THEN 0
            WHEN BOM.CCN_STT = 1 THEN (EOM.EMI_AMOUNT - EOM.EMI + 12000)
            WHEN BOM.BUCKET_SUM = 0 THEN (BOM.EMI_AMOUNT + BOM.TOTAL_OVER_DUE_AMOUNT)
            ELSE BOM.TOTAL_OVER_DUE_AMOUNT
        END AS AMT_MUST_COLLECTED,
        EXTRACT(DAY FROM EOM.SYS_RUN_DATE) AS data_day,
        due.due_day, due.due_date, due.PRI_AMT, due.INT_AMT, due.REPAY_AMT, due.EMI_AMOUNT,
        due1.due_day AS due_day_2, due1.due_date AS due_date_2, due1.PRI_AMT AS PRI_AMT_2, due1.INT_AMT AS INT_AMT_2, due1.REPAY_AMT AS REPAY_AMT_2, due1.EMI_AMOUNT AS EMI_AMOUNT_2,
        BOM.MIN_MUST_PAYMENT AS MIN_MUST_PAYMENT_BOM, EOM.MIN_MUST_PAYMENT AS MIN_MUST_PAYMENT_EOM,
        BOM.MIN_MUST_PAYMENT_CIR06 AS MIN_MUST_PAYMENT_CIR06_BOM, EOM.MIN_MUST_PAYMENT_CIR06 AS MIN_MUST_PAYMENT_CIR06_EOM, EOM.MIN_MUST_PAYMENT_DCS AS MIN_MUST_PAYMENT_DCS_EOM,
        CASE WHEN BOM.LD_DI_DATE >= '2023-09-01' THEN 1 ELSE 0 END AS CIR06_flag,
        CASE
            WHEN BOM.LD_DI_DATE >= '2023-09-01' AND BOM.BUCKET_SUM > 0 AND due.due_day = 1 THEN (BOM.MIN_MUST_PAYMENT_CIR06 + due.PRI_AMT) - EOM.PAYMENT_AMOUNT_NET_MTD
            WHEN BOM.LD_DI_DATE >= '2023-09-01' AND BOM.BUCKET_SUM > 0 THEN BOM.MIN_MUST_PAYMENT_CIR06 - EOM.PAYMENT_AMOUNT_NET_MTD
            ELSE BOM.MIN_MUST_PAYMENT - EOM.PAYMENT_AMOUNT_NET_MTD
        END AS REMAIN_AMT,
        BOM.AMT_COLLECTED_TO_RESOLVE AS MIN_MUST_PAYMENT_RS_BOM, EOM.AMT_COLLECTED_TO_RESOLVE AS MIN_MUST_PAYMENT_RS_EOM,
        EOM.PRI_REC_ALLOCATED_TO_DATE, EOM.INT_REC_ALLOCATED_TO_DATE, EOM.OD_REC_ALLOCATED_TO_DATE
    FROM processed_base EOM
    LEFT JOIN processed_base BOM
        ON EOM.LD_NO = BOM.LD_NO AND EOM.REPORT_MONTH = BOM.REPORT_MONTH_BOM AND BOM.IS_LAST_DAY = 1
    LEFT JOIN due_ due
        ON EOM.ACCOUNT_NUMBER = due.ACCOUNT_NUMBER AND EOM.REPORT_MONTH = due.REPORT_MONTH AND due.row_num = 1
    LEFT JOIN due_ due1
        ON EOM.ACCOUNT_NUMBER = due1.ACCOUNT_NUMBER AND EOM.REPORT_MONTH = due1.REPORT_MONTH AND due1.row_num = 2
)
,fi AS (
    SELECT
        ra.*, ALLOC.GROUP_ADJUST, ALLOC.USER_CURRENT, ALLOC.TEAM,
        CASE WHEN DPD_BOM > 360 THEN 1 ELSE 0 END AS DPD_GROUP,
        CASE WHEN POS_BOM < 7000000 THEN '<7tr' WHEN POS_BOM >= 7000000 AND POS_BOM <= 30000000 THEN '<=30tr' ELSE '>30tr' END AS pos_group,
        CASE WHEN ra.STAY_POS > 0 THEN 1 ELSE 0 END AS case_stay,
        CASE WHEN ra.BACK_POS > 0 THEN 1 ELSE 0 END AS case_back,
        CASE WHEN ra.ROLL_POS > 0 THEN 1 ELSE 0 END AS case_roll,
        CASE
            WHEN COALESCE(ra.ROLL_POS, 0) > 0 THEN 'RF'
            WHEN COALESCE(ra.STAY_POS, 0) > 0 THEN 'RT'
            WHEN COALESCE(ra.BACK_POS, 0) > 0 THEN 'RB'
        END AS DEBT_STATUS,
        CASE WHEN COALESCE(ra.ROLL_POS, 0) > 0 AND REMAIN_AMT <= 100000 AND REMAIN_AMT > 0 AND cash_nofee > 0 THEN 1 ELSE 0 END AS tolerance,
        CASE
            WHEN COALESCE(ra.ROLL_POS, 0) > 0 AND REMAIN_AMT <= 100000 AND REMAIN_AMT > 0 AND cash_nofee > 0 THEN
                CASE WHEN REMAIN_AMT <= 12000 THEN '<=12K' WHEN REMAIN_AMT <= 50000 THEN '<=50k' ELSE '<=100k' END
            ELSE 'NO_TOLERANCE'
        END AS tolerance_band,
        CEIL(CASE WHEN COALESCE(ra.ROLL_POS, 0) > 0 AND REMAIN_AMT <= 100000 AND REMAIN_AMT > 0 AND cash_nofee > 0 THEN REMAIN_AMT ELSE 0 END / 1000) * 1000 AS tolerance_amt_round
    FROM ra
    LEFT JOIN lps.ALLOCATE_JOIN ALLOC
        ON ALLOC.CONTRACT_NO = ra.CONTRACT_NO AND ALLOC.REPORT_MONTH = ra.REPORT_MONTH
    INNER JOIN SelectedDates p ON ra.SYS_RUN_DATE = p.load_date
)
SELECT * FROM fi

- Warehouse

Rocky

06/12/2026, 2:39 AM

It looks like you’ve shared a query profile summary for a successful

INSERT OVERWRITE

operation in StarRocks v4.0.10. The query finished in 2 minutes and 29 seconds. Based on the SQL and the hints provided, here are a few observations: Key Features Used * dynamic_overwrite = true
: This is enabled via the hint. It ensures that only the partitions present in the source data are overwritten in the target table

lps.NHANNT33_V_DAILY_H

, rather than overwriting the entire table. * spill_mode = 'auto'
: You’ve set the spill mode to auto. * Note: For this hint to take effect, the system variable

enable_spill

must also be set to

true

. If it isn't set globally or in your session, you should add it to your hint:

/_+set_var(dynamic_overwrite = true, enable_spill = true, spill_mode = 'auto')_/

. * Spilling is useful for this query given the large number of joins,

GROUP BY

operations, and window functions (like

ROW_NUMBER() OVER...

), as it prevents Out-of-Memory (OOM) errors by using disk space when memory is tight. Query Structure The query is a complex ETL process involving: 1. Date Filtering: Identifying specific dates from

lps.date_dim

. 2. Aggregation: Computing repayment and interest totals in CTE

. 3. Window Functions: Using

ROW_NUMBER

in CTE

due_

to rank account schedules. 4. Complex Joins: Multiple joins between

processed_base

due_

, and

ALLOCATE_JOIN

to calculate debt status, buckets, and "roll/stay/back" logic. How can I help you further with this query? Are you looking to: * Optimize performance? (e.g., it's running slower than expected). * Debug the logic? (e.g., results aren't what you expect). * Understand the execution plan? (you can provide the output of

EXPLAIN ANALYZE <sql>

for deeper insight). References • sql-reference/System_variable.md • administration/management/resource_management/spill_to_disk.md

Lý Thành

06/12/2026, 2:41 AM

@Rocky Optimize performance

Rocky

06/12/2026, 2:41 AM

To optimize the performance of your StarRocks query, which involves complex CTEs, window functions, and multiple joins, consider the following targeted optimizations: 1. Optimize the Non-Equi Join In the

processed_base

CTE, you have a non-equi join:

Copy code

sql
LEFT JOIN b_grouped b ON b.CONTRACT_NO = a.CONTRACT_NO 
   AND b.MONTH_BEGIN <= a.SYS_RUN_DATE 
   AND b.MONTH_END >= a.SYS_RUN_DATE

Non-equi joins (using

<=

>=

, etc.) are significantly slower than equi-joins (

) in StarRocks because they often trigger a nested loop join. * Optimization: If your data allows, try to join on a discrete key first (like

REPORT_MONTH

) and then apply the date range as a filter. If the dates always fall within the same month, adding

AND a.REPORT_MONTH = b.REPORT_MONTH

(after adding

REPORT_MONTH

b_grouped

) will allow StarRocks to use a Hash Join. 2. Consolidate Redundant Date Logic The

TargetDates

CTE performs three separate scans on

lps.date_dim

combined with

UNION ALL

. * Optimization: Simplify this into a single scan using

OR

logic. This reduces the overhead of multiple joins and unions:

Copy code

sql
    ,TargetDates AS (
        SELECT DISTINCT CASE
            WHEN d.IS_PRESE = 1 AND (d.is_rpt = 1 OR (d.IS_EOM = 1 AND d.IS_LASTEST = 0))
            THEN d.BOM_base
            ELSE d.SYS_RUN_DAY
        END AS SYS_RUN_DAY
        FROM lps.date_dim d
        INNER JOIN SelectedDates s ON d.SYS_RUN_DAY = s.load_date
        WHERE d.IS_PRESE = 1
    )

3. Replace the Self-Join with Window Functions The CTE

ra

joins

processed_base

with itself to compare the current day (

EOM

) with the "Beginning of Month" (

BOM

). * Optimization: If the

BOM

record is consistently the record from the previous month's end for that

LD_NO

, you can use the

LAG()

window function. Window functions are generally more memory-efficient than self-joins because they avoid scanning the table twice. _ _Note*: This only works if your

eom_

data includes both the current dates and the month-end dates needed for comparison. 4. Improve Join Performance (Colocate Joins) Since you are joining large tables like

DMAPP.DTM_CUSTOMER_LD_SUMMARY

stg_clzb_account_schedules

, and

lps.ALLOCATE_JOIN

on keys like

ACCOUNT_NUMBER

or `CONTRACT_NO`: * Colocate Join: Ensure these tables are distributed (using

DISTRIBUTED BY HASH(...)

) on the same join keys. This allows StarRocks to perform joins locally on each BE without shuffling data over the network, providing a massive speed boost. * Analyze Tables: Ensure statistics are up to date so the optimizer chooses the best join order:

Copy code

sql
    ANALYZE TABLE dmapp.stg_clzb_account_schedules;
    ANALYZE TABLE DMAPP.DTM_CUSTOMER_LD_SUMMARY;

5. Tune System Variables For large ETL queries like this, adjust these session variables: * enable_spill = true
: You’ve set

spill_mode = 'auto'

, but it requires

enable_spill

to be

true

to actually use disk when memory is low. * parallel_fragment_exec_instance_num
: In older versions, or

pipeline_dop

in v3.x+, increasing this can improve parallelism if your cluster has idle CPU resources. 6. Materialized Views If the logic in CTEs like

(aggregating

stg_clzb_account_schedules

) or

b_grouped

is reused across multiple reports, consider creating an Asynchronous Materialized View. This would pre-calculate the aggregations, turning a 2-minute query into a few seconds. Recommended Action: Run

EXPLAIN ANALYZE <your_query>

and look for the "Top Costs" section. If a specific

JOIN

AGGREGATE

is consuming 80%+ of the time, focus your optimization specifically on that step. References • sql-reference/sql-statements/table_bucket_part_index/SELECT.md • using_starrocks/accelerate-cardinality-preserving-joins.md

Lý Thành

06/12/2026, 2:50 AM

@Rocky -- dmapp.stg_clzb_account_schedules definition CREATE TABLE

stg_clzb_account_schedules

(

account_number

varchar(35) NULL COMMENT "",

branch_code

varchar(35) NULL COMMENT "",

component_name

varchar(20) NULL COMMENT "",

formula_name

varchar(27) NULL COMMENT "",

schedule_type

varchar(1) NULL COMMENT "",

schedule_st_date

date NULL COMMENT "",

schedule_due_date

date NULL COMMENT "",

grace_days

int(11) NULL COMMENT "",

orig_amount_due

decimal(18, 0) NULL COMMENT "",

amount_due

decimal(18, 0) NULL COMMENT "",

adj_amount

decimal(18, 0) NULL COMMENT "",

amount_settled

decimal(18, 0) NULL COMMENT "",

amount_overdue

decimal(18, 0) NULL COMMENT "",

accrued_amount

decimal(18, 0) NULL COMMENT "",

settlement_ccy

varchar(3) NULL COMMENT "",

lcy_equivalent

decimal(18, 0) NULL COMMENT "",

dly_avg_amt

decimal(18, 0) NULL COMMENT "",

emi_amount

decimal(18, 0) NULL COMMENT "",

schedule_flag

varchar(1) NULL COMMENT "",

waiver_flag

varchar(1) NULL COMMENT "",

event_seq_no

int(11) NULL COMMENT "",

schedule_linkage

date NULL COMMENT "",

capitalized

varchar(1) NULL COMMENT "",

process_no

int(11) NULL COMMENT "",

amount_readjusted

decimal(18, 0) NULL COMMENT "",

adj_settled

decimal(18, 0) NULL COMMENT "",

sch_status

varchar(4) NULL COMMENT "",

account_gl

varchar(20) NULL COMMENT "",

last_pmnt_value_date

date NULL COMMENT "",

retry_start_date

date NULL COMMENT "",

mora_int

decimal(18, 0) NULL COMMENT "",

schedule_no

int(11) NULL COMMENT "",

writeoff_amt

decimal(18, 0) NULL COMMENT "",

readj_settled

decimal(18, 0) NULL COMMENT "",

last_readj_xrate

decimal(18, 0) NULL COMMENT "",

susp_amt_due

decimal(18, 0) NULL COMMENT "",

susp_amt_settled

decimal(18, 0) NULL COMMENT "",

susp_amt_lcy

decimal(18, 0) NULL COMMENT "",

susp_read_amt

decimal(18, 0) NULL COMMENT "",

susp_read_settled

decimal(18, 0) NULL COMMENT "",

last_susp_xrate

decimal(18, 0) NULL COMMENT "",

amount_waived

decimal(18, 0) NULL COMMENT "",

irr_applicable

char(1) NULL COMMENT "",

list_days

varchar(65533) NULL COMMENT "",

list_avg_amt

varchar(65533) NULL COMMENT "",

pay_by_date

date NULL COMMENT "",

grace_int

decimal(18, 0) NULL COMMENT "",

recovery_on

date NULL COMMENT "",

curr_unadv_amt

decimal(18, 4) NULL COMMENT "" ) ENGINE=OLAP DUPLICATE KEY(

account_number

) DISTRIBUTED BY RANDOM ORDER BY(

account_number

component_name

schedule_st_date

) PROPERTIES ( "bucket_size" = "1073741824", "compression" = "LZ4", "fast_schema_evolution" = "true", "replicated_storage" = "true", "replication_num" = "1" );

Rocky

06/12/2026, 2:50 AM

The table definition you provided reveals a significant performance bottleneck for the query you are running. Currently,

stg_clzb_account_schedules

is set to DISTRIBUTED BY RANDOM
. This is likely the primary reason for high latency in your

INSERT OVERWRITE

operation. The Problem: Shuffle Joins In your SQL, CTE

performs a join between

stg_clzb_account_schedules

and

eom_distinct_acc

on the

ACCOUNT_NUMBER

column. * Because the table is distributed randomly, StarRocks cannot guarantee where any specific

ACCOUNT_NUMBER

resides. * To perform the join, StarRocks must shuffle (redistribute) the data across the network between all Backend (BE) nodes during execution. For large tables, this network I/O and CPU overhead for serialization is very expensive. The Optimization: HASH Distribution & Colocate Joins You should change the distribution to HASH(account_number)
. If you do the same for your other large tables (like

DMAPP.DTM_CUSTOMER_LD_SUMMARY

and

lps.ALLOCATE_JOIN

), StarRocks can perform a Colocate Join. This allows the join to happen locally on each node without any data moving across the network. Recommended Table DDL Changes:

Copy code

sql
CREATE TABLE `stg_clzb_account_schedules` (
  -- ... columns remain same ...
  `account_number` varchar(35) NULL COMMENT ""
  -- ...
) ENGINE=OLAP
DUPLICATE KEY(`account_number`)
-- CHANGE: Use HASH distribution on the join key
DISTRIBUTED BY HASH(`account_number`) 
ORDER BY(`account_number`, `component_name`, `schedule_st_date`)
PROPERTIES (
  "replication_num" = "1", -- Consider "3" for production
  "compression" = "LZ4",
  "fast_schema_evolution" = "true"
);

Why this helps your specific query: 1. CTE
T
Join: The join

ON s.ACCOUNT_NUMBER = e.ACCOUNT_NUMBER

becomes a local join if

(the source table for

eom_

) is also hashed on its account/contract key. 2. CTE
T
Grouping: You are grouping by

ACCOUNT_NUMBER

. With HASH distribution, all rows for the same account are already on the same node, making the

GROUP BY

significantly faster as it avoids another shuffle. 3. CTE
due_
Window Function: You use

PARTITION BY t.ACCOUNT_NUMBER

. Window functions are extremely sensitive to distribution; keeping the partition key as the distribution key eliminates the need to reshuffle the data before calculating the

ROW_NUMBER()

. Next Steps: 1. Check Other Tables: Ensure

DMAPP.DTM_CUSTOMER_LD_SUMMARY

and

lps.ALLOCATE_JOIN

are also distributed by

HASH

on their respective join keys (

LD_NO

CONTRACT_NO

). 2. Verify with EXPLAIN: Run

EXPLAIN <your_sql>

. * If you see

CROSS-EXCHANGE

SHUFFLE

, data is moving over the network. * If you see

LOCAL-EXCHANGE

or no exchange at all for the join, the optimization is working. 3. Replication: You currently have

"replication_num" = "1"

. This is fine for staging but offers no high availability. For production performance and safety,

Lý Thành

06/12/2026, 2:59 AM

DTM_CUSTOMER_LD_SUMMARY table:

....

MIN_AMT_MUST_COLLECTED_BOM

decimal(38, 20) NULL COMMENT "",

OD_TO_EOM_AMT

decimal(38, 20) NULL COMMENT "",

paid_tern

decimal(38, 20) NULL COMMENT "" ) ENGINE=OLAP PRIMARY KEY(

sys_run_date

ld_no

) COMMENT "Table luu so lieu chi tiet hop dong vay theo ngay" PARTITION BY RANGE(

sys_run_date

) (PARTITION p201806 VALUES [("2018-06-01"), ("2018-07-01")), PARTITION p201807 VALUES [("2018-07-01"), ("2018-08-01")), PARTITION p201808 VALUES [("2018-08-01"), ("2018-09-01")), PARTITION p201809 VALUES [("2018-09-01"), ("2018-10-01")), PARTITION p201810 VALUES [("2018-10-01"), ("2018-11-01")), PARTITION p201811 VALUES [("2018-11-01"), ("2018-12-01")), PARTITION p201812 VALUES [("2018-12-01"), ("2019-01-01")), PARTITION p201901 VALUES [("2019-01-01"), ("2019-02-01")), PARTITION p201902 VALUES [("2019-02-01"), ("2019-03-01")), PARTITION p201903 VALUES [("2019-03-01"), ("2019-04-01")), PARTITION p201904 VALUES [("2019-04-01"), ("2019-05-01")), PARTITION p201905 VALUES [("2019-05-01"), ("2019-06-01")), PARTITION p201906 VALUES [("2019-06-01"), ("2019-07-01")), PARTITION p201907 VALUES [("2019-07-01"), ("2019-08-01")), PARTITION p201908 VALUES [("2019-08-01"), ("2019-09-01")), PARTITION p201909 VALUES [("2019-09-01"), ("2019-10-01")), PARTITION p201910 VALUES [("2019-10-01"), ("2019-11-01")), PARTITION p201911 VALUES [("2019-11-01"), ("2019-12-01")), PARTITION p201912 VALUES [("2019-12-01"), ("2020-01-01")), PARTITION p202001 VALUES [("2020-01-01"), ("2020-02-01")), PARTITION p202002 VALUES [("2020-02-01"), ("2020-03-01")), PARTITION p202003 VALUES [("2020-03-01"), ("2020-04-01")), PARTITION p202004 VALUES [("2020-04-01"), ("2020-05-01")), PARTITION p202005 VALUES [("2020-05-01"), ("2020-06-01")), PARTITION p202006 VALUES [("2020-06-01"), ("2020-07-01")), PARTITION p202007 VALUES [("2020-07-01"), ("2020-08-01")), PARTITION p202008 VALUES [("2020-08-01"), ("2020-09-01")), PARTITION p202009 VALUES [("2020-09-01"), ("2020-10-01")), PARTITION p202010 VALUES [("2020-10-01"), ("2020-11-01")), PARTITION p202011 VALUES [("2020-11-01"), ("2020-12-01")), PARTITION p202012 VALUES [("2020-12-01"), ("2021-01-01")), PARTITION p202101 VALUES [("2021-01-01"), ("2021-02-01")), PARTITION p202102 VALUES [("2021-02-01"), ("2021-03-01")), PARTITION p202103 VALUES [("2021-03-01"), ("2021-04-01")), PARTITION p202104 VALUES [("2021-04-01"), ("2021-05-01")), PARTITION p202105 VALUES [("2021-05-01"), ("2021-06-01")), PARTITION p202106 VALUES [("2021-06-01"), ("2021-07-01")), PARTITION p202107 VALUES [("2021-07-01"), ("2021-08-01")), PARTITION p202108 VALUES [("2021-08-01"), ("2021-09-01")), PARTITION p202109 VALUES [("2021-09-01"), ("2021-10-01")), PARTITION p202110 VALUES [("2021-10-01"), ("2021-11-01")), PARTITION p202111 VALUES [("2021-11-01"), ("2021-12-01")), PARTITION p202112 VALUES [("2021-12-01"), ("2022-01-01")), PARTITION p202201 VALUES [("2022-01-01"), ("2022-02-01")), PARTITION p202202 VALUES [("2022-02-01"), ("2022-03-01")), PARTITION p202203 VALUES [("2022-03-01"), ("2022-04-01")), PARTITION p202204 VALUES [("2022-04-01"), ("2022-05-01")), PARTITION p202205 VALUES [("2022-05-01"), ("2022-06-01")), PARTITION p202206 VALUES [("2022-06-01"), ("2022-07-01")), PARTITION p202207 VALUES [("2022-07-01"), ("2022-08-01")), PARTITION p202208 VALUES [("2022-08-01"), ("2022-09-01")), PARTITION p202209 VALUES [("2022-09-01"), ("2022-10-01")), PARTITION p202210 VALUES [("2022-10-01"), ("2022-11-01")), PARTITION p202211 VALUES [("2022-11-01"), ("2022-12-01")), PARTITION p202212 VALUES [("2022-12-01"), ("2023-01-01")), PARTITION p202301 VALUES [("2023-01-01"), ("2023-02-01")), PARTITION p202302 VALUES [("2023-02-01"), ("2023-03-01")), PARTITION p202303 VALUES [("2023-03-01"), ("2023-04-01")), PARTITION p202304 VALUES [("2023-04-01"), ("2023-05-01")), PARTITION p202305 VALUES [("2023-05-01"), ("2023-06-01")), PARTITION p202306 VALUES [("2023-06-01"), ("2023-07-01")), PARTITION p202307 VALUES [("2023-07-01"), ("2023-08-01")), PARTITION p202308 VALUES [("2023-08-01"), ("2023-09-01")), PARTITION p202309 VALUES [("2023-09-01"), ("2023-10-01")), PARTITION p202310 VALUES [("2023-10-01"), ("2023-11-01")), PARTITION p202311 VALUES [("2023-11-01"), ("2023-12-01")), PARTITION p202312 VALUES [("2023-12-01"), ("2024-01-01")), PARTITION p202401 VALUES [("2024-01-01"), ("2024-02-01")), PARTITION p202402 VALUES [("2024-02-01"), ("2024-03-01")), PARTITION p202403 VALUES [("2024-03-01"), ("2024-04-01")), PARTITION p202404 VALUES [("2024-04-01"), ("2024-05-01")), PARTITION p202405 VALUES [("2024-05-01"), ("2024-06-01")), PARTITION p202406 VALUES [("2024-06-01"), ("2024-07-01")), PARTITION p202407 VALUES [("2024-07-01"), ("2024-08-01")), PARTITION p202408 VALUES [("2024-08-01"), ("2024-09-01")), PARTITION p202409 VALUES [("2024-09-01"), ("2024-10-01")), PARTITION p202410 VALUES [("2024-10-01"), ("2024-11-01")), PARTITION p202411 VALUES [("2024-11-01"), ("2024-12-01")), PARTITION p202412 VALUES [("2024-12-01"), ("2025-01-01")), PARTITION p202501 VALUES [("2025-01-01"), ("2025-02-01")), PARTITION p202502 VALUES [("2025-02-01"), ("2025-03-01")), PARTITION p202503 VALUES [("2025-03-01"), ("2025-04-01")), PARTITION p202504 VALUES [("2025-04-01"), ("2025-05-01")), PARTITION p202505 VALUES [("2025-05-01"), ("2025-06-01")), PARTITION p202506 VALUES [("2025-06-01"), ("2025-07-01")), PARTITION p202507 VALUES [("2025-07-01"), ("2025-08-01")), PARTITION p202508 VALUES [("2025-08-01"), ("2025-09-01")), PARTITION p202509 VALUES [("2025-09-01"), ("2025-10-01")), PARTITION p202510 VALUES [("2025-10-01"), ("2025-11-01")), PARTITION p202511 VALUES [("2025-11-01"), ("2025-12-01")), PARTITION p202512 VALUES [("2025-12-01"), ("2026-01-01")), PARTITION p202601 VALUES [("2026-01-01"), ("2026-02-01")), PARTITION p202602 VALUES [("2026-02-01"), ("2026-03-01")), PARTITION p202603 VALUES [("2026-03-01"), ("2026-04-01")), PARTITION p202604 VALUES [("2026-04-01"), ("2026-05-01")), PARTITION p202605 VALUES [("2026-05-01"), ("2026-06-01")), PARTITION p202606 VALUES [("2026-06-01"), ("2026-07-01")), PARTITION p202607 VALUES [("2026-07-01"), ("2026-08-01")), PARTITION p202608 VALUES [("2026-08-01"), ("2026-09-01")), PARTITION p202609 VALUES [("2026-09-01"), ("2026-10-01")), PARTITION p202610 VALUES [("2026-10-01"), ("2026-11-01")), PARTITION p202611 VALUES [("2026-11-01"), ("2026-12-01")), PARTITION p202612 VALUES [("2026-12-01"), ("2027-01-01"))) DISTRIBUTED BY HASH(

ld_no

) BUCKETS 32 PROPERTIES ( "compression" = "LZ4", "dynamic_partition.buckets" = "32", "dynamic_partition.enable" = "true", "dynamic_partition.end" = "6", "dynamic_partition.history_partition_num" = "72", "dynamic_partition.prefix" = "p", "dynamic_partition.replication_num" = "2", "dynamic_partition.start" = "-96", "dynamic_partition.start_day_of_month" = "1", "dynamic_partition.time_unit" = "MONTH", "dynamic_partition.time_zone" = "Asia/Ho_Chi_Minh", "enable_persistent_index" = "true", "fast_schema_evolution" = "true", "replicated_storage" = "true", "replication_num" = "2" );

Lý Thành

06/12/2026, 2:59 AM

@Rocky

DTM_CUSTOMER_LD_SUMMARY table:

`....

MIN_AMT_MUST_COLLECTED_BOM

decimal(38, 20) NULL COMMENT "",`

OD_TO_EOM_AMT

decimal(38, 20) NULL COMMENT "",

paid_tern

decimal(38, 20) NULL COMMENT "" ) ENGINE=OLAP PRIMARY KEY(

sys_run_date

ld_no

) COMMENT "Table luu so lieu chi tiet hop dong vay theo ngay" PARTITION BY RANGE(

sys_run_date

ld_no

Rocky

06/12/2026, 2:59 AM

The DDL for

DTM_CUSTOMER_LD_SUMMARY

shows it is a Primary Key table partitioned by date and distributed by

HASH(ld_no)

. This is a solid foundation, but there is a distribution key mismatch that is likely causing data shuffling in your query. Here is how to optimize the performance based on this table structure: 1. Fix the Distribution Key Mismatch In your query, the join in CTE

is the most expensive part: * Join:

stg_clzb_account_schedules s

JOIN

eom_distinct_acc e

s.ACCOUNT_NUMBER = e.ACCOUNT_NUMBER

. * The Conflict: *

stg_clzb_account_schedules

was

RANDOM

(suggested to change to

HASH(account_number)

). *

DTM_CUSTOMER_LD_SUMMARY

HASH(ld_no)

. * The Result: Even if both tables are hashed, StarRocks cannot perform a Colocate Join because the columns are different (

account_number

ld_no

). This forces a Shuffle Join, moving millions of rows across the network. Optimization: If

ld_no

and

account_number

represent the same ID, ensure both tables use the exact same column for distribution. If they are different, check if

account_number

can be added to the Primary Key of

DTM_CUSTOMER_LD_SUMMARY

, allowing you to change its distribution to

HASH(account_number)

. 2. Optimize the "BOM" Self-Join Your query joins

processed_base

(EOM) to

processed_base

(BOM) on

ld_no

. * Since

DTM_CUSTOMER_LD_SUMMARY

is already distributed by

ld_no

, this specific join (

ra

CTE) is likely already performing well as a local join. * However, if you find this step is still slow, verify that both the "End of Month" and "Beginning of Month" records fall within the same partition or a small number of partitions. StarRocks 3.x+ is very efficient at "Bucket Shuffling," but scanning the same large table twice is still heavy. 3. Leverage Primary Key Table Benefits Since

DTM_CUSTOMER_LD_SUMMARY

is a Primary Key table with `enable_persistent_index = true`: * Query Pruning: Your query uses

WHERE d.SYS_RUN_DAY >= DATE_SUB(CURRENT_DATE(), INTERVAL 3 DAY)

. Because

sys_run_date

is part of the Primary Key and the Partition Key, StarRocks will prune almost all partitions and use the index to locate rows instantly. * Optimization: Ensure that the

TargetDates

CTE doesn't "break" this pruning. If the optimizer doesn't recognize the dates as constants, it might do a full scan. You can test this by replacing the CTE with hardcoded dates in a test run to see if performance jumps. 4. Adjust Bucket Count You are using

BUCKETS 32

. * For a table with many years of history (partitions from 2018 to 2027), 32 buckets per partition is generally healthy if each partition is roughly 1GB–10GB. * If your daily data is very large (e.g., >100GB per month), consider increasing the bucket count to

to increase parallelism across your BE nodes. 5. Check the Final Join (
ALLOCATE_JOIN
) The final CTE

fi

joins on `CONTRACT_NO`:

Copy code

sql
LEFT JOIN lps.ALLOCATE_JOIN ALLOC ON ALLOC.CONTRACT_NO = ra.CONTRACT_NO

To prevent a final massive shuffle before the `INSERT`: * Ensure

lps.ALLOCATE_JOIN

is also DISTRIBUTED BY HASH(contract_no)
. * If

CONTRACT_NO

is the same as

ld_no

, this will result in a Colocate Join, making the final assembly of the data nearly instantaneous. Summary Checklist: 1. Align distribution keys: Use

HASH

on the join key (

account_number

ld_no

) across all tables in the query. 2. Run
EXPLAIN ANALYZE
: Look at the join between

stg_clzb_account_schedules

and

eom_

. If it says

SHUFFLE_JOIN

, that is your target for optimization. 3. Update Stats: Since these are dynamic partitions, ensure you have periodic stats collection:

Copy code

sql
    ANALYZE TABLE DMAPP.DTM_CUSTOMER_LD_SUMMARY WITH ASYNC;

Lý Thành

06/12/2026, 3:06 AM

@Rocky how to update this big table

DTM_CUSTOMER_LD_SUMMARY as query join on account_number and contract_no

Rocky

06/12/2026, 3:07 AM

To update a large Primary Key table like

DTM_CUSTOMER_LD_SUMMARY

by joining it with other sources (on columns like

account_number

and

contract_no

), you have three main strategies depending on your StarRocks version and the scale of the update. 1. Multi-Table UPDATE Syntax (StarRocks 3.0+) If you are on version 3.0 or higher, you can use the

FROM

clause or a

CTE

directly in your

UPDATE

statement. This is the most readable way to join and update. Example using a join:

Copy code

sql
UPDATE DTM_CUSTOMER_LD_SUMMARY AS target
SET target.some_column = source.new_value,
    target.another_column = source.other_value
FROM other_table AS source
WHERE target.contract_no = source.contract_no 
  AND target.account_number = source.account_number
  AND target.sys_run_date = '2024-01-01'; -- Pruning by partition/PK is critical

Example using a CTE:

Copy code

sql
WITH updated_data AS (
    SELECT contract_no, account_number, MAX(amount) as total_amt
    FROM staging_table
    GROUP BY 1, 2
)
UPDATE DTM_CUSTOMER_LD_SUMMARY AS target
SET target.total_amount = src.total_amt
FROM updated_data AS src
WHERE target.contract_no = src.contract_no;

2. High-Performance "Column Mode" Update (StarRocks 3.1+) If you are updating a large number of rows but only a few columns, standard updates can be slow because they default to "Row Mode" (which rewrites the whole row). StarRocks 3.1 introduced Column Mode to make this significantly faster. To enable this, set the following session variable before running your `UPDATE`:

Copy code

sql
SET enable_update_column_with_join = true;
UPDATE DTM_CUSTOMER_LD_SUMMARY ... -- Syntax same as above

3. The "Upsert" Pattern (Most Efficient for Bulk Updates) For massive updates where you already have the data in a staging table or complex query, using

INSERT INTO ... SELECT

is often faster than an

UPDATE

statement. Since

DTM_CUSTOMER_LD_SUMMARY

is a Primary Key table, an

INSERT

with an existing key will automatically perform an UPSERT. Full Upsert (Overwrites all columns):

Copy code

sql
INSERT INTO DTM_CUSTOMER_LD_SUMMARY (sys_run_date, ld_no, column_to_update, ...)
SELECT sys_run_date, ld_no, new_value, ...
FROM source_query_or_table;

Partial Upsert (Overwrites only specific columns): If you only want to update 2 columns and leave the rest unchanged, use the

partial_update

session variable:

Copy code

sql
SET partial_update_mode = 'column'; -- Optimized for large row counts
INSERT INTO DTM_CUSTOMER_LD_SUMMARY (sys_run_date, ld_no, column_to_update)
SELECT sys_run_date, ld_no, new_value
FROM source_query;

Critical Performance Tips for your Table: 1. Join on the Primary Key: Your table's PK is

(sys_run_date, ld_no)

. If you join only on

account_number

contract_no

without including

sys_run_date

, StarRocks has to scan all partitions to find the matches. Always include
sys_run_date
in the
WHERE
or
JOIN
clause to trigger partition pruning. 2. Align Distribution: Ensure your source data is also distributed by

HASH(ld_no)

(the same as your target table) to enable a Colocate Join, which avoids expensive network shuffles. 3. Check Version: If you are on a version older than 3.0, you cannot use

FROM

CTE

in an

UPDATE

. You must use the

INSERT INTO ... SELECT

(Upsert) pattern described in step 3. Recommendation: Use Step 3 (Partial Upsert) with

partial_update_mode = 'column'

. It is generally the most robust and performant way to handle "Big Table" updates in StarRocks. References • sql-reference/sql-statements/table_bucket_part_index/UPDATE.md • loading/Load_to_Primary_Key_tables.md

Lý Thành

06/12/2026, 3:11 AM

@Rocky redesign may table as this big table

DTM_CUSTOMER_LD_SUMMARY as query join on account_number and contract_no

Rocky

06/12/2026, 3:11 AM

To redesign your tables for optimal performance when joining on

account_number

and

contract_no

, you need to align their Distribution Keys and use Colocation Groups. In StarRocks, if two tables have the same distribution key and are in the same Colocation Group, joins happen locally on each node, eliminating the network shuffle that is likely causing your current slowness. Step 1: Standardize the Distribution Key Your

DTM_CUSTOMER_LD_SUMMARY

table currently uses

ld_no

for distribution, but your joins use

account_number

. You should change the distribution key to the column you join on most frequently. Important: In a Primary Key table, the distribution column must be part of the Primary Key. Step 2: Redesigned DDL for
DTM_CUSTOMER_LD_SUMMARY
I have modified the Primary Key and Distribution to focus on

account_number

(assuming

account_number

and

ld_no

are the same or

account_number

is the join key).

Copy code

sql
CREATE TABLE `DTM_CUSTOMER_LD_SUMMARY` (
  `sys_run_date` DATE NOT NULL,
  `account_number` VARCHAR(35) NOT NULL, -- Use the Join Key here
  `ld_no` VARCHAR(35) NOT NULL,
  `contract_no` VARCHAR(35) NULL,
  -- ... other columns ...
  `paid_tern` decimal(38, 20) NULL
) ENGINE=OLAP
PRIMARY KEY(`sys_run_date`, `account_number`) -- account_number must be in PK
PARTITION BY RANGE(`sys_run_date`) (
    -- ... your existing partitions ...
)
DISTRIBUTED BY HASH(`account_number`) BUCKETS 32 -- Match distribution to join key
PROPERTIES (
    "colocate_with" = "group_loan_data", -- MUST match across tables
    "enable_persistent_index" = "true",
    "replication_num" = "3" 
);

Step 3: Redesigned DDL for
stg_clzb_account_schedules
To make the join local, this table must "match" the distribution of the big table.

Copy code

sql
CREATE TABLE `stg_clzb_account_schedules` (
  `account_number` varchar(35) NOT NULL,
  `component_name` varchar(20) NULL,
  -- ... other columns ...
) ENGINE=OLAP
DUPLICATE KEY(`account_number`)
DISTRIBUTED BY HASH(`account_number`) BUCKETS 32 -- Must match bucket count
ORDER BY(`account_number`, `component_name`)
PROPERTIES (
    "colocate_with" = "group_loan_data", -- MUST match the big table
    "replication_num" = "3"
);

Why this Redesign works: 1. Colocate Joins: By adding

"colocate_with" = "group_loan_data"

and using the same number of

BUCKETS (32)

, StarRocks ensures that

account_number = '123'

for both tables is stored on the exact same physical machine. The join is now "Local" (zero network traffic). 2. Partition Pruning: Since

sys_run_date

remains the partition key, your

WHERE

clauses on dates will still skip 99% of the data. 3. Primary Key Efficiency: By putting

account_number

in the Primary Key, the

INSERT OVERWRITE

(Upsert) and

UPDATE

operations will use the Persistent Index, making them extremely fast even on huge datasets. How to handle
contract_no
? If you frequently join on

contract_no

instead of

account_number

, and they are different values: * If they are 1:1: Use either one as the distribution key consistently across all tables. * If they are different: You can create a Secondary Index (Bitmap Index) on

contract_no

to speed up filtering, but for the fastest Joins, you must pick one "Master" distribution key. Final Verification After recreating the tables and running your query, run:

Copy code

sql
EXPLAIN <your_query>;

Look for Colocate Join
in the plan. If you see it, the redesign is successful and performance will be significantly better by 10x-100x. References • table_design/Colocation_join.md • table_design/Data_distribution.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md References

Rocky

06/12/2026, 3:11 AM

• administration/management/monitoring/Monitor_and_Alert.md • administration/management/monitoring/alert.md • administration/management/monitoring/metrics.md

Lý Thành

06/12/2026, 3:15 AM

@Rocky create a Secondary Index (Bitmap Index) on

contract_no

to speed up filtering

Rocky

06/12/2026, 3:15 AM

To create a Bitmap Index on the

contract_no

column of your

DTM_CUSTOMER_LD_SUMMARY

table, use the following SQL: 1. Create the Index

Copy code

sql
CREATE INDEX idx_contract_no ON DTM_CUSTOMER_LD_SUMMARY (contract_no) USING BITMAP;

2. Check Creation Progress Creating an index is an asynchronous process (it won't happen instantly on a big table). You can monitor the progress with this command:

Copy code

sql
SHOW ALTER TABLE COLUMN;

Look for the

State

column. Once it says FINISHED
, the index is active. 3. Verify the Index is Helping To see if StarRocks is actually using your new index to speed up queries, you can run a query and check the Query Profile. Look for the metric: * BitmapIndexFilterRows
: This tells you how many rows were skipped thanks to the Bitmap Index. ────────── Pro-Tips for "Big Table" Indexes: * Bitmap vs. Bloom Filter: * Bitmap Index (what you asked for) is great for

contract_no

if you perform equality (

IN

, and range queries (

). * Bloom Filter Index is an alternative if you only do equality (

) or

IN

checks on

contract_no

. It is often more space-efficient for very high-cardinality columns (columns with millions of unique values). To create one:

ALTER TABLE DTM_CUSTOMER_LD_SUMMARY SET ("bloom_filter_columns" = "contract_no");

* Cardinality: StarRocks' Bitmap Index is unique because it performs well even on high-cardinality columns (like a contract ID), as long as your query filters out a large percentage of the data (typically 99% or more). * Storage: Adding an index will increase the storage size of your table. On a very large table, ensure you have enough disk space before starting. Summary of Benefits By adding this index, any query that filters by

contract_no

(e.g.,

WHERE contract_no = 'ABC123'

) will no longer need to scan the entire partition. StarRocks will use the bitmap to jump directly to the relevant rows containing that specific contract. References • table_design/indexes/Bitmap_index.md • knowledge/usage/best-practice.md

Lý Thành

06/12/2026, 3:17 AM

@Rocky only do equality (

) or

IN

checks on

contract_no

Rocky

06/12/2026, 3:18 AM

If you only perform equality (

) and

IN

checks on

contract_no

, a Bloom Filter Index is often the most efficient choice. While a Bitmap Index is also powerful, a Bloom Filter is specifically designed to quickly verify if a value exists in a data block, making it ideal for high-cardinality strings like contract numbers. Option 1: Bloom Filter Index (Best for
=
and
IN
) This is set as a table property. It is very space-efficient for unique IDs. To add it to your table:

Copy code

sql
ALTER TABLE DTM_CUSTOMER_LD_SUMMARY 
SET ("bloom_filter_columns" = "contract_no");

Why choose this? * High Cardinality: It excels when

contract_no

has millions of unique values. * Efficiency: It helps the storage engine skip entire data blocks that definitely do not contain the contract you are looking for. * Limitation: It cannot speed up range queries (like

) or

IS NULL

checks. ────────── Option 2: Bitmap Index (Versatile) If there is even a small chance you might use range queries (e.g.,

contract_no > 'ABC'

) or

IS NULL

in the future, stick with the Bitmap Index. To create it:

Copy code

sql
CREATE INDEX idx_contract_no ON DTM_CUSTOMER_LD_SUMMARY (contract_no) USING BITMAP;

────────── How to verify which index is working? After running your query, check the Query Profile. You can look for these specific metrics to see which index "did the heavy lifting": 1. For Bloom Filter: Look for BloomFilterFilterRows
. This shows how many rows were skipped by the Bloom Filter. 2. For Bitmap: Look for BitmapIndexFilterRows
. This shows how many rows were skipped by the Bitmap Index. Rocky's Recommendation: Since you confirmed you only use

and

IN

, use the Bloom Filter. It is the standard industry practice for high-cardinality IDs in StarRocks to keep the storage overhead low while maximizing lookup speed. References • table_design/indexes/Bloomfilter_index.md • table_design/indexes/Bitmap_index.md • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • administration/Query_planning.mdspeed_planning) References • table_design/indexes/Bloomfilter_index.md • table_design/indexes/Bitmap_index.md

Lý Thành

06/12/2026, 3:19 AM

@Rocky add to contract_no and account_no also

Copy code

ALTER TABLE DTM_CUSTOMER_LD_SUMMARY 
SET ("bloom_filter_columns" = "contract_no");

Rocky

06/12/2026, 3:20 AM

To add both

contract_no

and

account_number

to the Bloom Filter index, you must provide them as a comma-separated list in a single command. Note: Based on your previous DDL, your column is named

account_number

. If you named it

account_no

, please adjust the snippet below:

Copy code

sql
ALTER TABLE DTM_CUSTOMER_LD_SUMMARY 
SET ("bloom_filter_columns" = "contract_no, account_number");

Important things to know: 1. Overwrite Behavior: This command overwrites the existing bloom filter property. If you had other columns indexed previously and you leave them out of this command, they will be removed. 2. Asynchronous Process: The index is not created instantly. You can monitor the progress by running:

Copy code

sql
    SHOW ALTER TABLE COLUMN;

Wait until the

State

shows FINISHED
before expecting a performance boost. 3. Check the Results: Once finished, you can verify it is working by running your query and checking the Query Profile for the metric

BloomFilterFilterRows

. References • table_design/indexes/Bloomfilter_index.md • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md References • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • table_design/indexes/Bloomfilter_index.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md • knowledge/usage/best-practice.md

Lý Thành

06/12/2026, 3:45 AM

@Rocky StreamLoad → http://10.0.27.71:8030/api/dmapp/losrep_application/_stream_load label=losrep_application_16_2026-06-12_1 rows=11423 bytes=14745993 báo nhiêu mb ?

Rocky

06/12/2026, 3:45 AM

Với dữ liệu

bytes=14745993

trong kết quả Stream Load của bạn, con số này tương đương khoảng 14.06 MB (nếu tính theo hệ nhị phân MiB) hoặc 14.75 MB (nếu tính theo hệ thập phân). Cách tính cụ thể: * Theo chuẩn máy tính (MiB): $14,745,993 / 1024 / 1024 \approx \mathbf{14.06 \, MB}$ * Theo chuẩn thập phân: $14,745,993 / 1,000,000 \approx \mathbf{14.75 \, MB}$ Thông thường trong quản trị hệ thống, chúng ta thường làm tròn là khoảng 14 MB.

Lý Thành

06/12/2026, 10:50 AM

@Rocky find top 50 biggest tables in starrocks

Rocky

06/12/2026, 10:51 AM

To find the top 50 biggest tables in StarRocks, you can query the

information_schema.tables

view. This view contains the

DATA_LENGTH

column, which represents the total storage size (including all replicas) in bytes. SQL Query The following query retrieves the top 50 tables by size, converting the result into Gigabytes (GB) for readability:

Copy code

sql
SELECT 
    TABLE_SCHEMA AS database_name, 
    TABLE_NAME AS table_name, 
    ROUND(DATA_LENGTH / 1024 / 1024 / 1024, 2) AS size_gb, 
    TABLE_ROWS AS row_count
FROM information_schema.tables 
WHERE TABLE_SCHEMA NOT IN ('information_schema', '_statistics_')
ORDER BY DATA_LENGTH DESC 
LIMIT 50;

Key details to keep in mind: * DATA_LENGTH
: This value reflects the physical storage used on the disks, meaning it includes the size of all replicas (e.g., if you have 3 replicas, this value will be roughly 3x the size of the unique data). * Real-time Accuracy: StarRocks updates these statistics periodically. For a real-time check of a specific database, you can also use the command

SHOW DATA;

SHOW DATA FROM <db_name>;

. * Catalog Scope: This query typically shows tables within the current catalog (usually

default_catalog

). If you are using external catalogs (like Hive or Iceberg), you may need to switch to that catalog first. Alternatives If you need more granular details (like size per partition), you can use the

information_schema.partitions_meta

view, though note that the

DATA_SIZE

column there is often a formatted string (e.g., "1.2GB") which is harder to sort numerically than the

DATA_LENGTH

(bytes) found in

information_schema.tables

. References • sql-reference/information_schema/tables.md • faq/Sql_faq.md • sql-reference/sql-statements/Database/SHOW_DATA.md References • sql-reference/information_schema/tables.md • faq/Sql_faq.md • administration/management/compaction.md

Lý Thành

06/14/2026, 4:30 AM

@Rocky start time: Tue Dec 16 025225 PM +07 2025, server uptime: 145225 up 4 days, 3:07, 4 users, load average: 0.15, 0.04, 0.01 Run with JEMALLOC_CONF: 'percpu_arenapercpu,oversize threshold0,muzzy_decay_ms5000,dirty decay ms5000,metadata_thpauto,background threadtrue,proftrue,prof activefalse' start time: Tue Mar 10 044609 PM +07 2026, server uptime: 164609 up 88 days, 5:00, 3 users, load average: 0.06, 0.59, 0.68 Run with JEMALLOC_CONF: 'percpu_arenapercpu,oversize threshold0,muzzy_decay_ms5000,dirty decay ms5000,metadata_thpauto,background threadtrue,proftrue,prof activefalse' start time: Mon Mar 16 095533 AM +07 2026, server uptime: 095533 up 93 days, 22:09, 4 users, load average: 0.48, 0.14, 0.04 Run with JEMALLOC_CONF: 'percpu_arenapercpu,oversize threshold0,muzzy_decay_ms5000,dirty decay ms5000,metadata_thpauto,background threadtrue,proftrue,prof activefalse' start time: Wed May 20 053612 PM +07 2026, server uptime: 173612 up 159 days, 5:50, 6 users, load average: 0.47, 2.26, 3.81 Run with JEMALLOC_CONF: 'percpu_arenapercpu,oversize threshold0,muzzy_decay_ms5000,dirty decay ms5000,metadata_thpauto,background threadtrue,proftrue,prof activefalse' start time: Mon Jun 8 024544 PM +07 2026, server uptime: 144544 up 178 days, 2:59, 5 users, load average: 0.04, 1.91, 6.38 Run with JEMALLOC_CONF: 'percpu_arenapercpu,oversize threshold0,muzzy_decay_ms5000,dirty decay ms5000,metadata_thpauto,background threadtrue,proftrue,prof activefalse' start time: Tue Jun 9 031124 PM +07 2026, server uptime: 151124 up 179 days, 3:25, 5 users, load average: 0.16, 4.01, 6.55 Run with JEMALLOC_CONF: 'percpu_arenapercpu,oversize threshold0,muzzy_decay_ms5000,dirty decay ms5000,metadata_thpauto,background threadtrue,proftrue,prof activefalse' start time: Wed Jun 10 020027 PM +07 2026, server uptime: 140027 up 180 days, 2:14, 5 users, load average: 0.38, 5.75, 7.49 Run with JEMALLOC_CONF: 'percpu_arenapercpu,oversize threshold0,muzzy_decay_ms5000,dirty decay ms5000,metadata_thpauto,background threadtrue,proftrue,prof activefalse' start time: Wed Jun 10 031555 PM +07 2026, server uptime: 151555 up 180 days, 3:28, 6 users, load average: 1.42, 8.17, 9.99 Run with JEMALLOC_CONF: 'percpu_arenapercpu,oversize threshold0,muzzy_decay_ms5000,dirty decay ms5000,metadata_thpauto,background threadtrue,proftrue,prof activefalse' 4.0.10 RELEASE (build 5b36164 distro centos arch x86_64) query_id:00000000-0000-0000-0000-000000000000, fragment_instance:00000000-0000-0000-0000-000000000000, plan_node_id:-1 * Aborted at 1781348142 (unix time) try "date -d @1781348142" if you are using GNU date * PC: @ 0x9014d9d starrocks:OlapScanNode:_could_tablet_internal_parallel(std:vector<starrocks:TScanRangeParams, std:allocatorstarrocks:TScanRangeParams > const&, int, unsigned long, starrocks:TTabletInternalParallelMode:type, long*, long*) const * SIGFPE (@0x9014d9d) received by PID 4041197 (TID 0x14749fee2640) LWP(4041732) from PID 151080349; stack trace: * @ 0x1474f848f218 __pthread_once_slow @ 0xdee2e94 google::(anonymous namespace)::FailureSignalHandler(int, siginfo_t*, void*) @ 0x1474f9ac76ce PosixSignals::chained_handler(int, siginfo_t*, void*) [clone .part.0] @ 0x1474f9ac81c6 JVM_handle_linux_signal @ 0x1474f843ebf0 (/usr/lib64/libc.so.6+0x3ebef) @ 0x9014d9d starrocks:OlapScanNode:_could_tablet_internal_parallel(std:vector<starrocks:TScanRangeParams, std:allocator<starrocks:TScanRangeParams> > const&, int, unsigned long, starrocks:TTabletInternalParallelMode:type, long*, long*) const @ 0x901a2ea starrocks:OlapScanNode:convert_scan_range_to_morsel_queue(std:vector<starrocks:TScanRangeParams, std:allocator<starrocks:TScanRangeParams> > const&, int, int, bool, starrocks:TTabletInternalParallelMode:type, unsigned long) @ 0xa2fe4c7 starrocks:ScanNode:convert_scan_range_to_morsel_queue_factory(std:vector<starrocks:TScanRangeParams, std:allocator<starrocks:TScanRangeParams> > const&, std::map<int, std:vector<starrocks:TScanRangeParams, std:allocator<starrocks:TScanRangeParams@ @ 0xa129369 starrocks:pipelineFragmentExecutor:_prepare_exec_plan(starrocks::ExecEnv*, starrocks:pipeline:UnifiedExecPlanFragmentParams const&) @ 0xa12ae6e starrocks:pipelineFragmentExecutor:prepare(starrocks::ExecEnv*, starrocks::TExecPlanFragmentParams const&, starrocks::TExecPlanFragmentParams const&) @ 0xa9c4123 starrocks:PInternalServiceImplBase<starrocksPInternalService>:_exec_plan_fragment_by_pipeline(starrocks::TExecPlanFragmentParams const&, starrocks::TExecPlanFragmentParams const&) @ 0xa9ca423 starrocks:PInternalServiceImplBase<starrocksPInternalService>:_exec_plan_fragment(brpc::Controller*, starrocks::PExecPlanFragmentRequest const*, starrocks::PExecPlanFragmentResult*) @ 0xa9d03dc starrocks:PInternalServiceImplBase<starrocksPInternalService>:_exec_plan_fragment(google:protobuf:RpcController*, starrocks::PExecPlanFragmentRequest const*, starrocks::PExecPlanFragmentResult*, google:protobuf:Closure*) @ 0x836fd38 starrocks:PriorityThreadPool:work_thread(int) @ 0xdea02c7 thread_proxy @ 0x1474f848a002 start_thread @ 0x1474f850f070 __clone3

Rocky

06/14/2026, 4:30 AM

The crash log you provided indicates a SIGFPE (Signal Floating Point Exception), which is almost always caused by a division by zero error in the StarRocks Backend (BE) code. Specifically, the crash occurs in the function:

starrocks::OlapScanNode::_could_tablet_internal_parallel

This function is responsible for determining whether a single data tablet can be scanned by multiple threads in parallel to speed up the query. The division by zero likely happens during the calculation of how many "morsels" (parallel work units) to create, possibly due to a specific configuration value being set to zero or a tablet having unusual metadata. Immediate Workaround To stop the crashes and restore service, you should disable the Tablet Internal Parallel feature. This will bypass the buggy code path: 1. Disable it globally (recommended):

Copy code

sql
    SET GLOBAL enable_tablet_internal_parallel = false;

2. Or disable it for specific sessions/queries:

Copy code

sql
    SET enable_tablet_internal_parallel = false;

Analysis & Recommendations 1. Check System Variables: Verify if any of the following variables have been manually set to `0`: *

tablet_internal_parallel_max_morsel_num

tablet_internal_parallel_min_scan_rows

pipeline_dop

(Degree of Parallelism) If any of these are

, try resetting them to their default values. 2. Version Information: Your log shows StarRocks 4.0.10. According to recent release notes, this version has known improvements and some edge-case bugs in the parallel scan logic. * Recommendation: Check if v4.0.11 or v4.1.0 is available for your environment, as these versions include specific "Enhancements of parallel processing capability" and bug fixes for the Primary Key and OLAP scan nodes. 3. High System Load: Your logs show a very high load average (

9.99

) just before the crash. While high load doesn't directly cause a

SIGFPE

, it often correlates with a high number of concurrent scan tasks, which may have triggered a race condition or an edge case in the parallel scan manager. 4. Identify the Query: If possible, check the FE (Frontend)

fe.audit.log

to identify the specific query that triggered the crash. It likely involves a large table with many tablets or a table using the Primary Key storage engine. Next Step: After disabling the feature, if the crashes stop, you should reach out to StarRocks support or the community with the full BE core dump and the problematic query to confirm if this is a fixed bug in a newer patch

4.0.11+

. References • sql-reference/System_variable.md • administration/management/BE_configuration.md • faq/operation_maintenance_faq.md References • sql-reference/System_variable.md • administration/management/BE_configuration.md • faq/operation_maintenance_faq.md • table_design/indexes/Bloomfilter_index.md References • sql-reference/System_variable.md • administration/management/FE_configuration.md • faq/operation_maintenance_faq.md • administration/management/BE_configuration.md

Lý Thành

06/14/2026, 5:02 AM

@Rocky 4.0.11 wangsimo0 released this 5 days ago · 3943 commits to main since this release 4.0.11 `9559176` Release Date: June 5, 2026 Behavior Changes • `get_json_string`and the other`get_json_*`functions now return the JSON parse error instead of NULL when implicit VARCHAR-to-JSON parsing fails under`ALLOW_THROW_EXCEPTION`. The default behavior (returning NULL when the mode is disabled) is unchanged.#73199 • `pipeline_enable_large_column_checker`is now enabled by default.#72798 Improvements • Lake write-path load spill files now use a flat, single-level directory layout with the transaction ID baked into each filename, and are reclaimed by a txn-id-based vacuum pass. This moves bulk deletes off the write hot path and lets vacuum clean up spill files leaked by BE crashes.#73064 • SHOW statements (such as`SHOW GRANTS`and`SHOW WAREHOUSES`) are now allowed inside an explicit transaction, so BI/JDBC clients that automatically issue SHOW no longer break the transaction flow.#72954 • Java UDAF and UDTF now support STRUCT arguments and return types.#72911 • Scalar Java UDF now supports STRUCT arguments.#72620 • Java UDF now supports DATE and DATETIME types.#72337 • Java UDF now supports nested ARRAY/MAP types.#72283 • Added the FE configuration`deploy_serialization_min_thread_pool_size`.#72274 • Skipped redundant partition key expression building when an`add_partition_value`deduplication hit occurs.#73156 • Avoided a redundant`latestSnapshot()`call in`PaimonMetadata#getTableVersionRange`.#72892 • Deduplicated commutative AND/OR expressions in scalar operator common subexpression elimination.#72823 Bug Fixes The following issues have been fixed: • A memory leak introduced by the UDAF cache.#74025 • An incorrect implementation in aggregate combined functions.#74169 • An issue in shared-data combined txn log mode where the per-partition coordinator claim was not re-recorded on every sender's open, which could drop txn logs.#73962 • A read failure on Iceberg tables that use a custom`LocationProvider`, fixed by lazily initializing the`LocationProvider`in`SerializableTable`.#73482 • A serialization failure caused by the`de.javakaffeeUnmodifiableCollectionsSerializer`, now replaced with a Java 17-compatible version.#73458 • `HdfsFsManager`copy error messages now include the underlying cause.#73414 • A concurrent`SegmentFlushTask`race in`DeltaWriter::commit()`.#73371 • Sort merge provider errors are now propagated to the fragment context instead of being lost.#73337 • An issue where Ranger row-filter/masking policies on Hive views were skipped, so policies on the view or its base tables were not applied.#73265 • Upgraded libthrift to 0.23.0 to address a security vulnerability (CVE).#73243 • An FE file-descriptor leak, fixed by reusing`HttpClient`instances.#73239 • Parquet broker load errors now include file/column/row context.#73236 • A slot lookup failure for output slots with an empty`col_name`in the Spark connector external scan.#73225 • A crash in`SinkBuffer`during graceful exit.#73202 • Query cache conflicts with local shuffle aggregation.#73194 • A use-after-free of the Hive partition descriptor across fragment teardown.#73176 • A thread-safety issue in lake vacuum, fixed by using`localtime_r`.#73088 • A race condition between`PipelineTimerTaskdoRun`and unscheduling during query context destruction.#73082 • Lock contention on read-only query-engine paths, reduced by relaxing DB locks.#73067 • An materialized view refresh failure with SQL Server tables in a JDBC catalog.#72962 • A JNI local-reference leak in`JDBCScanner::_init_jdbc_scanner`.#72913 • An issue where partition TopN could lose a child's output column.#72848 • An incorrect plan caused by not clearing`LambdaArgument.transformedOp`before INSERT OVERWRITE re-planning.#72832 • The coordinator lock was held during external resource cleanup.#72830 • `Locker`rollback is now exception-safe and the unlock order is fixed.#72789 • An incorrect byte order in`ColumnDict.merge`, now using unsigned byte order.#72778 • A stack-buffer-overflow when formatting into a temporary`std::string`.#72728 • The HAVING clause is now checked when disabling aggregation spill on a small LIMIT.#72705 • A hang caused by joining forwarded RPCs when draining the runtime_filter worker.#72626 • Incorrect lazy-materialization slot nullability for a materialized view over an outer join.#72621 • `merge_condition`was not preserved when applying a normal rowset commit.#72542 • Lock contention in`TabletScheduler`/`TabletSchedCtx`hot paths during clone, reduced by relaxing DB locks.#72475 • `Locker`did not roll back a partial intensive-lock acquisition.#72423 • A spillable hash join probe crash.#72397 • COALESCE children are now cast to a common type in the JOIN USING transformer.#72338 • DB READ lock was held too broadly for single-table proc directories, now relaxed to per-table.#72334 • A memory leak when caching the materialized view plan context.#72300 • FSE-v2 did not set the schema for shared-data sorted schema change.#72235 • `ConsistencyChecker`held a DB READ lock too broadly in periodic scans, now relaxed to per-table READ.#72218 • A BE crash when querying`information_schema.warehouse_queries`.#72019 • A trailing`\r`was not stripped before the closing enclose in CRLF CSV inputs.#71866 • Paimon primary key columns were incorrectly marked as non-nullable when querying an external catalog.#71660 • A redundant double slash was created when constructing the JDBC URL if the URI already ended with a trailing slash, breaking strict drivers such as ClickHouse.#70992

Rocky

06/14/2026, 5:03 AM

Dựa trên thông tin bạn cung cấp, hệ thống đang gặp lỗi SIGFPE (Signal Floating Point Exception) tại hàm

OlapScanNode::_could_tablet_internal_parallel

. Đây là lỗi chia cho 0 xảy ra trong cơ chế xử lý song song bên trong tablet (Tablet Internal Parallel). Mặc dù bạn đã trích dẫn release notes của bản 4.0.11, nhưng bản log crash của bạn vẫn đang chạy trên 4.0.10 RELEASE. Dưới đây là phân tích và hướng xử lý: 1. Nguyên nhân trực tiếp Lỗi này xảy ra khi StarRocks tính toán số lượng đơn vị công việc (morsels) để chia nhỏ một tablet cho nhiều thread xử lý. Nếu một trong các biến cấu hình sau bằng

, nó sẽ gây ra lỗi chia cho 0 và làm sập BE: *

tablet_internal_parallel_min_scan_rows

tablet_internal_parallel_max_morsel_num

2. Giải pháp khắc phục tạm thời (Workaround) Để ngăn chặn BE tiếp tục crash, bạn nên tắt tính năng Tablet Internal Parallel ngay lập tức. Việc này sẽ buộc StarRocks sử dụng cơ chế quét tablet truyền thống (một thread/một tablet), tránh được đoạn code lỗi:

Copy code

sql
-- Tắt trên toàn hệ thống
SET GLOBAL enable_tablet_internal_parallel = false;

-- Hoặc tắt cho session hiện tại để kiểm tra
SET enable_tablet_internal_parallel = false;

3. Kiểm tra cấu hình hệ thống Hãy kiểm tra xem có biến nào vô tình bị set về

hay không bằng lệnh:

Copy code

sql
SHOW VARIABLES LIKE '%tablet_internal_parallel%';

_Giá trị mặc định thông thường của

tablet_internal_parallel_min_scan_rows

là 131072. Nếu nó đang là 0, hãy set lại giá trị mặc định._ 4. Về bản cập nhật 4.0.11 Trong danh sách lỗi đã fix của 4.0.11 bạn gửi, không thấy nhắc đích danh hàm

_could_tablet_internal_parallel

. Tuy nhiên, bản 4.0.11 có nhiều bản vá quan trọng về bộ nhớ và race condition (như

#73202

#73082

). Khuyến nghị: 1. Áp dụng

SET GLOBAL enable_tablet_internal_parallel = false;

để ổn định hệ thống ngay. 2. Lên kế hoạch nâng cấp lên 4.0.11 vì bản 4.0.10 bạn đang dùng dường như gặp vấn đề với việc xử lý tải cao (load average của bạn đạt mức >9 trước khi crash). 3. Nếu sau khi tắt tính năng trên mà vẫn crash, hãy kiểm tra lại các truy vấn nặng thực hiện vào thời điểm

Wed Jun 10 03:15:55

trong file

fe.audit.log

. References • sql-reference/System_variable.md • administration/management/BE_configuration.md • faq/operation_maintenance_faq.md References • knowledge/trouble_shooting/fe_deadlock_problems.md • data_source/datalake_faq.md • release_notes/release-3.4.md

Lý Thành

06/15/2026, 2:39 AM

@Rocky [2026-06-14, 231829 UTC] {taskinstance.py:3313} ERROR - Task failed with exception Traceback (most recent call last): File "/home/airflow/.local/lib/python3.12/site-packages/airflow/models/taskinstance.py", line 768, in _execute_task *result = _execute_callable(context=context, *execute_callable_kwargs) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/home/airflow/.local/lib/python3.12/site-packages/airflow/models/taskinstance.py", line 734, in _execute_callable return ExecutionCallableRunner( ^^^^^^^^^^^^^^^^^^^^^^^^ File "/home/airflow/.local/lib/python3.12/site-packages/airflow/utils/operator_helpers.py", line 252, in run *return self.func(*args, *kwargs) ^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/home/airflow/.local/lib/python3.12/site-packages/airflow/models/baseoperator.py", line 424, in wrapper *return func(self, *args, *kwargs) ^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/home/airflow/.local/lib/python3.12/site-packages/airflow/operators/python.py", line 238, in execute return_value = self.execute_callable() ^^^^^^^^^^^^^^^^^^^^^^^ File "/home/airflow/.local/lib/python3.12/site-packages/airflow/operators/python.py", line 256, in execute_callable *return runner.run(*self.op_args, *self.op_kwargs) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/home/airflow/.local/lib/python3.12/site-packages/airflow/utils/operator_helpers.py", line 252, in run *return self.func(*args, *kwargs) ^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/opt/airflow/dags/bags/dwh-dmapp-starrocks/airflow_dags/extract_framework/extract_dag_creator.py", line 223, in _extract_table_to_target cur.execute(sql) File "/home/airflow/.local/lib/python3.12/site-packages/MySQLdb/cursors.py", line 179, in execute res = self._query(mogrified_query) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/home/airflow/.local/lib/python3.12/site-packages/MySQLdb/cursors.py", line 330, in _query db.query(q) File "/home/airflow/.local/lib/python3.12/site-packages/MySQLdb/connections.py", line 280, in query _mysql.connection.query(self, query) MySQLdb.ProgrammingError: (1064, 'Getting analyzing error. Detail message: Tablet lost replicas. Check if any backend is down or not. tablet_id: 2255643, replicas: 373184617107/ 1/17107/16907NORMAL:DEAD,. Check quorum number failed(OlapTableSink): BeReplicaSize:0, quorum:1.') [2026-06-14, 231829 UTC] {taskinstance.py:907} DEBUG - Task Duration set to 5.401184

Rocky

06/15/2026, 2:39 AM

Lỗi này xác nhận rằng các vụ sập Backend (BE) mà chúng ta đã phân tích ở bước trước (do lỗi

SIGFPE

) đã trực tiếp gây ra việc mất dữ liệu tạm thời trên các bản sao (replicas). Khi BE bị sập, Frontend (FE) không tìm thấy đủ số lượng bản sao hoạt động để thực hiện lệnh ghi dữ liệu (

OlapTableSink

). Thông báo

BeReplicaSize:0, quorum:1

nghĩa là hệ thống cần ít nhất 1 bản sao sống để ghi, nhưng hiện tại không có bản sao nào khả dụng cho tablet

. Các bước xử lý ngay lập tức: 1. Kiểm tra trạng thái các Backend (BE) Đầu tiên, bạn cần xác định có bao nhiêu BE đang bị dừng (Dead):

Copy code

sql
SHOW BACKENDS;

Nếu cột

Alive

hiển thị

false

, hãy khởi động lại các tiến trình BE trên server tương ứng. 2. Ngăn chặn sập BE tái diễn (Quan trọng) Dựa trên log crash trước đó, hãy chạy lệnh này ngay để chặn đứng nguyên nhân gây sập BE khi chạy các câu lệnh scan/extract:

Copy code

sql
SET GLOBAL enable_tablet_internal_parallel = false;

3. Kiểm tra các Tablet bị lỗi Kiểm tra chi tiết trạng thái của tablet

được nhắc đến trong lỗi Airflow:

Copy code

sql
SHOW TABLET 2255643;
-- Hoặc kiểm tra trạng thái replica của table chứa tablet đó
ADMIN SHOW REPLICA STATUS FROM <tên_table> WHERE TabletId = 2255643;

Nếu cột

Status

hiển thị

DEAD

hoặc

MISSING

, hệ thống sẽ tự động sửa chữa (clone) lại khi các BE đã

Alive

trở lại. 4. Kiểm tra tổng thể sức khỏe Cluster Để xem có bao nhiêu tablet đang không lành mạnh trên toàn bộ database:

Copy code

sql
SHOW PROC '/statistic';

Tìm hàng tương ứng với database của bạn, nếu cột

UnhealthyTabletNum

hoặc

InconsistentTabletNum

lớn hơn 0, hệ thống đang trong quá trình tự sửa chữa. Tại sao Airflow bị lỗi? Task Airflow của bạn thực hiện lệnh

extract_table_to_target

(có thể là một lệnh

INSERT INTO ... SELECT

hoặc tương tự). Khi câu lệnh này chạy, nó cần đọc từ tablet nguồn hoặc ghi vào tablet đích. Vì BE chứa tablet đó đã bị sập (do lỗi

SIGFPE

khi tính toán song song), câu lệnh bị hủy bỏ ngay lập tức với lỗi

. Tóm lại: 1. Khởi động lại BE. 2. Tắt

enable_tablet_internal_parallel

. 3. Chờ vài phút để StarRocks tự sửa chữa các bản sao bị lỗi (

Automatic replica repair

), sau đó chạy lại task Airflow. References • faq/operation_maintenance_faq.md • administration/management/resource_management/Replica.md • sql-reference/sql-statements/cluster-management/tablet_replica/ADMIN_SHOW_REPLICA_STATUS.md • sql-reference/System_variable.md -variable) References • faq/operation_maintenance_faq.md • administration/management/resource_management/Replica.md • sql-reference/sql-statements/cluster-management/tablet_replica/ADMIN_SHOW_REPLICA_STATUS.md • sql-reference/System_variable.md • sql-reference/sql-statements/cluster-management/nodes_processes/SHOW_BACKENDS.md • sql-reference/sql-statements/table_bucket_part_index/SHOW_TABLET.md References • administration/management/resource_management/Replica.md • faq/operation_maintenance_faq.md

Lý Thành

06/15/2026, 10:10 AM

@Rocky start time: Mon Jun 15 075057 AM +07 2026, server uptime: 075057 up 100 days, 14:16, 5 users, load average: 0.00, 0.01, 0.00 Run with JEMALLOC_CONF: 'percpu_arenapercpu,oversize threshold0,muzzy_decay_ms5000,dirty decay ms5000,metadata_thpauto,background threadtrue,proftrue,prof activefalse' 4.0.10 RELEASE (build 5b36164 distro centos arch x86_64) query_id:019ecab3-bb0b-7cad-b9a5-c1f9a7557c52, fragment_instance:019ecab3-bb0b-7cad-b9a5-c1f9a7557c56, plan_node_id:29 * Aborted at 1781517270 (unix time) try "date -d @1781517270" if you are using GNU date * PC: @ 0x14b186e8e5c4 ___pthread_mutex_lock * SIGSEGV (@0x2b8) received by PID 4057853 (TID 0x14b10a223640) LWP(4058217) from PID 696; stack trace: * @ 0x14b186e904f8 __pthread_once_slow @ 0xdee2e94 google::(anonymous namespace)::FailureSignalHandler(int, siginfo_t*, void*) @ 0x14b1884cf99e PosixSignals::chained_handler(int, siginfo_t*, void*) [clone .part.0] @ 0x14b1884d0496 JVM_handle_linux_signal @ 0x14b186e3fc30 (/usr/lib64/libc.so.6+0x3fc2f) @ 0x14b186e8e5c4 ___pthread_mutex_lock @ 0xa25fe8f starrocks:pipelineSpillableHashJoinProbeOperator:_status() const @ 0xa26654e std::_Function_handler<void (starrocks:workgroup:YieldContext&), starrocks:pipelineSpillableHashJoinProbeOperator:_load_all_partition_build_side(starrocks::RuntimeState*)::{lambda(auto:1&)#1}>::_M_invoke(std::_Any_data const&, starrocks:workgroup:Y@ @ 0xa2a87bf starrocks:workgroupScanExecutor:worker_thread() @ 0x8673f77 starrocks:ThreadPool:dispatch_thread() @ 0x866ac70 starrocks:🧵:supervise_thread(void*) @ 0x14b186e8b2ea start_thread @ 0x14b186f10500 __clone3 [1781517271.182][thread: 22750611781184] je_mallctl execute purge success [1781517271.182][thread: 22750611781184] je_mallctl execute dontdump success

Rocky

06/15/2026, 10:10 AM

Log mới này cho thấy một lỗi sập BE khác, lần này là SIGSEGV (Segmentation Fault), liên quan đến cơ chế Spill-to-disk (Tràn đĩa) của Hash Join. 1. Phân tích lỗi * Vị trí lỗi:

starrocks::pipeline::SpillableHashJoinProbeOperator::_status()

* Nguyên nhân: Lỗi phân đoạn (Null pointer dereference). Khi truy vấn chạy thiếu bộ nhớ và phải sử dụng cơ chế "Spill" (ghi dữ liệu tạm ra đĩa), một con trỏ trạng thái bên trong toán tử Hash Join đã bị truy cập sai. * Mối liên hệ: Trong danh sách các lỗi đã fix của bản 4.0.11 mà bạn đã cung cấp ở trên, có dòng: > _• A spillable hash join probe crash. https://github.com/StarRocks/starrocks/pull/72397_ Đây chính là lỗi bạn đang gặp phải. Bản 4.0.10 bạn đang dùng chứa bug nghiêm trọng này khi thực hiện các câu lệnh Join lớn cần tràn đĩa. 2. Giải pháp khắc phục Giải pháp 1: Tắt tính năng Spill (Khuyên dùng ngay lập tức) Vì hệ thống đang liên tục sập do cơ chế tràn đĩa khi bộ nhớ đầy, bạn nên tắt tính năng này để truy vấn thà báo lỗi "OOM" (Out of Memory) còn hơn là làm sập toàn bộ BE:

Copy code

sql
SET GLOBAL enable_spilling = false;

Lưu ý: Sau khi tắt, các truy vấn quá lớn có thể bị fail với lỗi "Memory limit exceeded", nhưng BE sẽ không bị crash. Giải pháp 2: Nâng cấp lên 4.0.11 Như đã xác nhận ở trên, bản 4.0.11 đã sửa chính xác lỗi

SpillableHashJoinProbeOperator crash

này. Đây là giải pháp triệt để nhất. 3. Tổng kết tình trạng hệ thống của bạn Hiện tại cluster StarRocks (v4.0.10) của bạn đang bị "tấn công" bởi 2 bug crash độc lập: 1. Lỗi 1 (SIGFPE): Xảy ra khi quét dữ liệu song song (

OlapScanNode

). _ _Cách xử lý:*

SET GLOBAL enable_tablet_internal_parallel = false;

2. Lỗi 2 (SIGSEGV): Xảy ra khi Join dữ liệu lớn và tràn đĩa (

SpillableHashJoinProbeOperator

). _ _Cách xử lý:*

SET GLOBAL enable_spilling = false;

Hành động khuyến nghị: Chạy cả 2 lệnh trên để ổn định hệ thống, sau đó thực hiện nâng cấp lên 4.0.11 càng sớm càng tốt để có thể bật lại các tính năng tối ưu hiệu năng này. References • sql-reference/System_variable.md • administration/management/BE_configuration.md • faq/operation_maintenance_faq.md • administration/management/resource_management/spill_to_disk.md References • sql-reference/System_variable.md • administration/management/BE_configuration.md • faq/operation_maintenance_faq.md • administration/management/resource_management/spill_to_disk.md

Lý Thành

06/15/2026, 10:16 AM

@Rocky SQL Error [1193] [HY000]: Getting analyzing error. Detail message: Unknown system variable 'enable_spilling', the most similar variables are {'enable_spill', 'enable_pipeline', 'enable_split_topn_agg'}. Getting analyzing error. Detail message: Unknown system variable 'enable_spilling', the most similar variables are {'enable_spill', 'enable_pipeline', 'enable_split_topn_agg'}.

SET GLOBAL enable_spilling = false;

Rocky

06/15/2026, 10:16 AM

Xin lỗi bạn, tôi đã nhầm lẫn tên biến. Trong StarRocks, tên chính xác của biến này là enable_spill
(không có đuôi "ing"). Dưới đây là các câu lệnh đúng để bạn thực thi nhằm ngăn chặn tình trạng sập BE ngay lập tức: 1. Tắt cơ chế Tràn đĩa (Spill-to-disk) Lệnh này sẽ khắc phục lỗi

SIGSEGV

tại `SpillableHashJoinProbeOperator`:

Copy code

sql
SET GLOBAL enable_spill = false;

2. Tắt cơ chế Quét song song nội bộ tablet Lệnh này sẽ khắc phục lỗi

SIGFPE

tại `OlapScanNode`:

Copy code

sql
SET GLOBAL enable_tablet_internal_parallel = false;

Giải thích thêm: * enable_spill
: Biến này kiểm soát việc StarRocks có được phép ghi dữ liệu trung gian ra đĩa khi bộ nhớ RAM bị đầy hay không. Do bản 4.0.10 bạn đang dùng gặp lỗi xử lý con trỏ khi thực hiện việc này, chúng ta cần tắt nó đi. * Tại sao lại bị lỗi 1193: Thông báo lỗi của StarRocks đã gợi ý đúng cho bạn:

the most similar variables are {'enable_spill', ...}

. Lời khuyên: Sau khi chạy 2 lệnh trên, hệ thống sẽ ổn định hơn (không bị crash bất ngờ). Tuy nhiên, một số truy vấn cực lớn có thể sẽ báo lỗi "Memory limit exceeded" thay vì tự động tràn ra đĩa. Đây là sự đánh đổi tạm thời trước khi bạn có thể nâng cấp lên bản 4.0.11 để sửa triệt để các lỗi này. References • sql-reference/System_variable.md • administration/management/resource_management/spill_to_disk.md References • sql-reference/System_variable.md

Lý Thành

06/17/2026, 7:02 AM

@Rocky should i truncate primary key table before reinsert data on daily job

Rocky

06/17/2026, 7:02 AM

Câu trả lời ngắn gọn là: Không nên sử dụng
TRUNCATE
trong các job chạy hàng ngày đối với bảng Primary Key (PK) của StarRocks. Thay vào đó, bạn nên tận dụng cơ chế tự động của bảng PK hoặc sử dụng lệnh

INSERT OVERWRITE

. Dưới đây là phân tích chi tiết dựa trên từng kịch bản: 1. Nếu bạn chỉ muốn Cập nhật (Update) và Thêm mới (Insert) Bảng Primary Key của StarRocks được thiết kế theo chiến lược Delete+Insert. Điều này có nghĩa là khi bạn thực hiện một lệnh

INSERT

thông thường: * Nếu Key đã tồn tại: StarRocks sẽ tự động đánh dấu dòng cũ là đã xóa và chèn dòng mới vào (Upsert). * Nếu Key chưa tồn tại: StarRocks sẽ chèn dòng mới. => Giải pháp: Chỉ cần chạy

INSERT INTO ... SELECT ...

hoặc các công cụ Load dữ liệu mà không cần xóa trước. StarRocks sẽ đảm bảo dữ liệu luôn là bản mới nhất dựa trên Primary Key. 2. Nếu bạn muốn Thay thế hoàn toàn (Full Refresh) dữ liệu Nếu quy trình hàng ngày của bạn yêu cầu xóa bỏ toàn bộ dữ liệu cũ và nạp lại từ đầu (ví dụ: bảng snapshot), hãy sử dụng:

Copy code

sql
INSERT OVERWRITE table_name SELECT ...;

Tại sao
INSERT OVERWRITE
tốt hơn
TRUNCATE
? * Tính nguyên tử (Atomicity):

INSERT OVERWRITE

sẽ chuẩn bị dữ liệu mới ở một vùng tạm thời, sau đó hoán đổi (swap) với dữ liệu cũ một cách tức thì. Người dùng vẫn có thể đọc dữ liệu cũ trong khi dữ liệu mới đang được chuẩn bị. * Không có downtime: Nếu bạn dùng

TRUNCATE

, bảng sẽ bị trống cho đến khi lệnh

INSERT

chạy xong. Nếu

INSERT

thất bại giữa chừng, bạn sẽ mất trắng dữ liệu.

INSERT OVERWRITE

không gặp lỗi này. 3. Nếu bạn muốn Thay thế một phần (Partition Refresh) Nếu dữ liệu của bạn được phân vùng (Partitioned) theo ngày (

dt

), bạn có thể chỉ ghi đè một ngày cụ thể:

Copy code

sql
INSERT OVERWRITE table_name PARTITION(dt='2026-06-14') SELECT ...;

INSERT INTO

nếu bạn chỉ muốn cập nhật dữ liệu dựa trên Key, hoặc

INSERT OVERWRITE

nếu bạn muốn làm mới toàn bộ dữ liệu để đảm bảo an toàn và tính sẵn sàng của hệ thống. References • table_design/table_types/primary_key_table.md • loading/InsertInto.md • loading/Load_to_Primary_Key_tables.md References • loading/InsertInto.md • loading/Load_to_Primary_Key_tables.md • table_design/table_types/primary_key_table.md

Open in Slack

Previous Next