云上口袋SR压测期间的参数调整

一、背景

y2云上sr集群压测。

目的：

1. 优化sql

2. 探索集群性能

集群信息 云上SR： 略

压测语句，有4个： 略

二、、获取执行计划步骤

1. 设置Session级

set enable_profile=true;

2. ## explain

explain SELECT
    national_dept_id AS dept_id,
    sum( salesAmount ) AS salesAmount,
    count( DISTINCT f.store_code ) actualStoreCount,
CASE
        
        WHEN count( DISTINCT f.store_code ) != 0 THEN
        round( sum( salesAmount )/ count( DISTINCT f.store_code ), 1 ) ELSE NULL 
    END AS avgSalesAmountByStore,
    sum( target ) AS salesAmountTarget,
    sum( tc ) AS tc,
CASE
        
        WHEN sum( tc )!= 0 THEN
        sum( salesAmount )* 1000 / sum( tc ) ELSE NULL 
    END AS actualSR,
CASE
        
        WHEN sum( target )!= 0 THEN
        sum( salesAmount )/ sum( target )* 100 ELSE NULL 
    END AS vsTarget 
FROM
    edw_report_kfc_db.edw_dim_bsc_t_dp_c_user_for_day u
    LEFT JOIN (
    SELECT
        store_code,
        sum( quantity ) AS salesAmount 
    FROM
        edw_report_kfc_db.edw_onebyone_sale_product_realtime_fact f
        JOIN ( SELECT DISTINCT itemCode AS item_code FROM edw_report_kfc_db.edw_item_golden_sr_mapping_info ) c ON f.item_code = c.item_code 
    WHERE
        f.part_dt = 20250106
        AND f.five_min_interval <= '2025-01-06 13:12:42' 
    GROUP BY
        store_code 
    ) f ON u.store_code = f.store_code
    LEFT JOIN (
    SELECT
        f.store_code,
        count( DISTINCT f.order_number ) AS tc 
    FROM
        edw_report_kfc_db.edw_kfc_onebyone_order_realtime_fact_module f 
    WHERE
        f.part_dt = 2025-01-06 
        AND f.order_number NOT IN ( SELECT order_number FROM edw_report_kfc_db.edw_kfc_onebyone_order_realtime_fact_module WHERE part_dt = '2025-01-06' AND order_type = '8' ) 
        AND f.fiveMinIntervalStr <= '2025-01-06 13:12:42' 
    GROUP BY
        f.store_code 
    ) m ON u.store_code = m.store_code
    LEFT JOIN (
    SELECT
        store_code,
        sum( target ) AS target 
    FROM
        edw_report_kfc_db.dim_glod_bucket_sr_target_config_t 
    WHERE
        del = '0' 
        AND tenant_id = 1
        AND role_date = '2025-01-06'
    GROUP BY
        store_code 
    ) t ON u.store_code = t.store_code 
WHERE
    u.brand_code = 1
    AND u.is_franchisee = 'N' 
    AND national_dept_id = 'N0000000'
GROUP BY
    national_dept_id;

3. ## analyze

 explain analyze SELECT
    national_dept_id AS dept_id,
    sum( salesAmount ) AS salesAmount,
    count( DISTINCT f.store_code ) actualStoreCount,
CASE
        
        WHEN count( DISTINCT f.store_code ) != 0 THEN
        round( sum( salesAmount )/ count( DISTINCT f.store_code ), 1 ) ELSE NULL 
    END AS avgSalesAmountByStore,
    sum( target ) AS salesAmountTarget,
    sum( tc ) AS tc,
CASE
        
        WHEN sum( tc )!= 0 THEN
        sum( salesAmount )* 1000 / sum( tc ) ELSE NULL 
    END AS actualSR,
CASE
        
        WHEN sum( target )!= 0 THEN
        sum( salesAmount )/ sum( target )* 100 ELSE NULL 
    END AS vsTarget 
FROM
    edw_report_kfc_db.edw_dim_bsc_t_dp_c_user_for_day u
    LEFT JOIN (
    SELECT
        store_code,
        sum( quantity ) AS salesAmount 
    FROM
        edw_report_kfc_db.edw_onebyone_sale_product_realtime_fact f
        JOIN ( SELECT DISTINCT itemCode AS item_code FROM edw_report_kfc_db.edw_item_golden_sr_mapping_info ) c ON f.item_code = c.item_code 
    WHERE
        f.part_dt = 20250106
        AND f.five_min_interval <= '2025-01-06 13:12:42' 
    GROUP BY
        store_code 
    ) f ON u.store_code = f.store_code
    LEFT JOIN (
    SELECT
        f.store_code,
        count( DISTINCT f.order_number ) AS tc 
    FROM
        edw_report_kfc_db.edw_kfc_onebyone_order_realtime_fact_module f 
    WHERE
        f.part_dt = 2025-01-06 
        AND f.order_number NOT IN ( SELECT order_number FROM edw_report_kfc_db.edw_kfc_onebyone_order_realtime_fact_module WHERE part_dt = '2025-01-06' AND order_type = '8' ) 
        AND f.fiveMinIntervalStr <= '2025-01-06 13:12:42' 
    GROUP BY
        f.store_code 
    ) m ON u.store_code = m.store_code
    LEFT JOIN (
    SELECT
        store_code,
        sum( target ) AS target 
    FROM
        edw_report_kfc_db.dim_glod_bucket_sr_target_config_t 
    WHERE
        del = '0' 
        AND tenant_id = 1
        AND role_date = '2025-01-06'
    GROUP BY
        store_code 
    ) t ON u.store_code = t.store_code 
WHERE
    u.brand_code = 1
    AND u.is_franchisee = 'N' 
    AND national_dept_id = 'N0000000'
GROUP BY
    national_dept_id;

三、开启query profile

1. 2.5版本的SR，开启query profile

set enable_profile = true;

2. 访问fe的8030的query

3. 执行sql

4. 查询

四、FE和BE参数调整（第二轮压测前的参数调整）

1. FE参数

# fe conf 
enable_query_cache=true 
# 随着enable_query_cache开启，默认是20%
storage_page_cache_limit=20%

enable_query_cache 参数属于动态参数，可以使用set global 方式修改

set global enable_query_cache = true;

2. ## BE参数

# be conf
disable_storage_page_cache=true

五、补充

1. 厂商提供的方式

2. 2.5版本的starrocks

不支持 explain analyze sql;

3. 开启query profile

参考： docs.starrocks.io/zh/docs/2.5…

4. Query cache 开启

# fe conf 
enable_query_cache=true

参考: docs.starrocks.io/zh/docs/2.5…

5. 额外调整的参数

cbo_cte_reuse        false
enable_adaptive_sink_dop        false
enable_filter_unused_columns_in_scan_stage        false
hash_join_push_down_right_table        false
pipeline_dop        1
new_planner_optimize_timeout        300000