• administration/management/resource_management/Memory_management.md • administration/management/BE_configuration.md

The primary bottleneck in your current setup is the Compute Node (CN) ephemeral storage, which is critically undersized for a Primary Key table of this scale. 1. Fix Persistent Index Storage (Critical) In a Primary Key model with

enable_persistent_index = true

, the index (which maps primary keys to row locations) is stored on the local disk of your CN nodes. * The Math: For 450 million rows with a primary key of

md5(col1)

(32 bytes) +

col2

(let's assume 10 bytes), the index requires approximately 9GB to 12GB of disk space cluster-wide. * The Bottleneck: You have only 512Mi of ephemeral storage per CN. When StarRocks runs out of disk space for the persistent index, performance collapses as it struggles with I/O or fails to persist the index. * Solution A (Increase Disk): Increase your CN

ephemeral-storage

to at least 50GB - 100GB. This allows for the index and local data caching. * Solution B (Cloud Native Index): If you are on StarRocks v3.3.2+, use the Cloud Native persistent index which stores the index on S3 and uses local disk only as a cache:

sql
    PROPERTIES (
       "enable_persistent_index" = "true",
       "persistent_index_type" = "CLOUD_NATIVE"
    );

2. Increase Load Parallelism Your 3 CN nodes have 8 CPUs each (24 total), but the default Broker Load settings may only be using 3 concurrent instances (one per CN). * FE Configuration: Increase the number of parallel instances allowed per CN. Run this on your leader FE:

sql
    ADMIN SET FRONTEND CONFIG ("load_parallel_instance_num" = "4");

* Pipeline Parallelism: Ensure the pipeline engine can utilize all cores during the transformation phase (

md5

is CPU heavy):

sql
    SET GLOBAL pipeline_dop = 8;

3. Adjust Memory and Load Limits Your CN nodes have 16Gi RAM, but you limited the load to only 1Gi (

load_mem_limit

). * The Impact: A 1GB limit for 450 million rows forces the CN to flush data to S3 very frequently, creating many small files and increasing metadata overhead. * Recommendation: Increase the memory limit in your

LOAD

statement to allow larger buffers:

sql
    PROPERTIES (
        "timeout" = "7200",
        "load_mem_limit" = "8589934592" -- Increase to 8GB (8589934592 bytes)
    );

4. Optimize the Primary Key The

md5(col1)

transformation is problematic for two reasons: 1. CPU Cost: Calculating MD5 for 450M rows during ingestion is very expensive. If possible, pre-calculate this value or use a numeric hash if the raw

col1

is not unique. 2. Index Size: MD5 produces a 32-character string. Large string primary keys significantly increase the memory and disk footprint of the persistent index. 5. Check Bucket Count For a 450M row dataset on 3 CNs, ensure your table has an appropriate number of buckets. * Goal: Each tablet should be around 500MB - 1GB. * Recommendation: Use approximately 32 to 48 buckets (

DISTRIBUTED BY HASH(col1) BUCKETS 48

). Too few buckets will limit the parallelism of the load. Summary Checklist 1. Disk: Increase CN ephemeral storage to 50GB+ (or switch to

CLOUD_NATIVE

index). 2. Memory: Increase CN memory to 32Gi if possible; set

load_mem_limit

to 8GB. 3. Parallelism: Set

load_parallel_instance_num

to 4 in FE config. 4. Transformation: Avoid

md5

in the load's

SET

clause if it can be handled upstream. References • deployment/shared_data/hdfs.md • quick_start/shared-data.md • deployment/shared_data/azure.md • deployment/shared_data/s3.md • deployment/shared_data/minio.md • faq/Others.md

• introduction/Architecture.md • best_practices/primarykey_table.md