@Rocky what is the best alternative AWS instance class to use for CNs when you CPU utilization seems to be out main bottleneck in performance right now at c5.9xlarge?

If your StarRocks Compute Nodes (CNs) are CPU-bound on

c5.9xlarge

instances, the best immediate upgrade path within AWS is to move to the newer generation Intel (C6i or C7i) or AMD (C7a) instances. Recommended AWS Alternatives 1. C7i Instances (Intel Sapphire Rapids) - Top Recommendation * Why: These are the direct successors to the C5/C6i lines. They feature DDR5 memory (providing significantly higher memory bandwidth) and 4th Gen Intel Xeon Scalable processors. * Performance: StarRocks relies heavily on AVX2 and AVX-512 instruction sets for its vectorized execution engine. The C7i offers superior SIMD performance and higher IPC (instructions per clock) compared to the C5. * Instance Match:

c7i.12xlarge

or

c7i.16xlarge

(if you need to scale up beyond the 36 vCPUs of the

c5.9xlarge

). 2. C6i Instances (Intel Ice Lake) * Why: If C7i is not available in your region, C6i is a massive jump over C5. It uses 3rd Gen Intel Xeon processors and offers up to 15-20% better compute performance and higher memory bandwidth than C5. * Instance Match:

c6i.12xlarge

. 3. C7a Instances (AMD EPYC Genoa) * Why: These often provide the best raw compute performance per dollar. AMD's latest architecture handles vectorized workloads extremely well. * Note: Ensure you are running a recent version of StarRocks (v3.x+) to ensure optimal compatibility with the latest AMD instruction sets. Why move away from C5? * Vectorization: StarRocks' performance is tied to how fast the CPU can process batches of data using SIMD. The C5 family uses older Skylake/Cascade Lake architectures. Newer generations (C6i/C7i) have improved execution units that handle these instructions much more efficiently. * Memory Bandwidth: CPU bottlenecks in OLAP engines are often actually "data starvation" issues. The move from DDR4 (C5/C6i) to DDR5 (C7i/C7a) significantly increases the throughput available to the CPU cores during intensive scans and joins. What about Graviton (C7g)? While StarRocks supports ARM-based Graviton instances and they offer great price/performance, the official StarRocks documentation highly recommends x86 architecture CPUs (like C7i) for production environments to "fully unleash its vectorization capability" via AVX2/AVX-512. If your bottleneck is purely raw CPU throughput for complex queries, the latest Intel/AMD chips usually provide the highest ceiling. Summary Recommendation If budget allows, switch to

c7i.12xlarge

. It provides a modern architecture, DDR5 memory, and better support for the vectorized operations that StarRocks excels at. References • deployment/plan_cluster.md • knowledge/usage/load/shared_data_batch_ingestion.md • deployment/deployment_prerequisites.md • deployment/shared_data/feature-support-shared-data.md • introduction/Architecture.md

@Rocky If you had one 1 CN at

c7i.12xlarge

vs 3 CNs at

c7i.4xlarge

would you expect the very same QPS performance? If not which one would be better?

If you are comparing one

c7i.12xlarge

(48 vCPU, 96 GiB RAM) against three

c7i.4xlarge

(16 vCPU, 32 GiB RAM each), you would not expect the very same performance. In most StarRocks scenarios, the one large CN node (

c7i.12xlarge

) will generally provide better performance and higher QPS, particularly for complex analytical queries. Here is the breakdown of why one larger node is often superior for performance, while multiple nodes are better for availability: 1. The Cost of "Shuffle" (Network Overhead) StarRocks uses an MPP (Massively Parallel Processing) architecture. For many queries (especially those involving Joins or Aggregations), data must be repartitioned and "shuffled" between nodes. * On 1 large node: This "shuffle" happens via memory and local pointers within the same process. It is extremely fast. * On 3 small nodes: This shuffle must travel over the network (vNIC). Even with AWS's fast networking, this adds latency and consumes CPU cycles for serialization/deserialization. 2. Query Coordination & Tail Latency For every query, the Frontend (FE) must coordinate with all participating nodes. * In a 3-node cluster, a query is only as fast as the slowest node (the "straggler" effect). * With 1 large node, the overhead of distributing query fragments and gathering results is minimized, which typically results in lower "p99" latencies for high-QPS workloads. 3. Resource Utilization * Memory Efficiency: A single large node has a unified 96GB memory pool. This is more flexible for memory-intensive queries. In a 3-node setup, if one node hits its 32GB limit during a specific shuffle or join, the query may fail or spill to disk, even if the other two nodes have plenty of memory available. * Concurrency: StarRocks is highly optimized for multi-core scaling. A 48-core machine can handle many concurrent threads very efficiently without the inter-node communication bottleneck. When would the 3x

c7i.4xlarge

be better? While the 12xlarge wins on raw performance, the 3-node setup wins on Availability: * Fault Tolerance: If your single

12xlarge

instance fails or undergoes AWS maintenance, your entire compute layer is down. With three

4xlarge

nodes, if one fails, the other two can continue to process queries (though at reduced capacity). * Scaling Granularity: It is cheaper to scale from 3 nodes to 4 nodes (adding 16 cores) than it is to jump from a

12xlarge

to the next size up. Summary Recommendation * For Performance/QPS: Go with the single

c7i.12xlarge

. It will be more efficient and faster due to zero network shuffle overhead. * For Production Stability: Go with the three

c7i.4xlarge

nodes. In production, High Availability (HA) is usually more important than the ~15-20% performance gain you might get from consolidating into one node. Rocky's Tip: If you choose the 3-node setup, ensure your network bandwidth is sufficient and that you are using the same Availability Zone for all nodes to keep shuffle latency as low as possible! References • deployment/plan_cluster.md • faq/Deploy_faq.md • faq/Others.md • introduction/Architecture.md

@Rocky by this logic scaling up to 16 nodes is bad idea for performance.

Scaling to 16 nodes (scaling out) is generally a very positive move for performance in StarRocks because of its MPP (Massively Parallel Processing) architecture, but there is a "logic" where it could be counterproductive if certain configurations aren't adjusted. Here is the breakdown of why scaling to 16 nodes might be perceived as a "bad idea" versus the reality of how to do it correctly: 1. The "Bucket Count" Trap If your tables were created with a low number of buckets (e.g., 3 or 6 buckets) to match a smaller cluster, scaling to 16 nodes will result in idle resources. * The Logic: StarRocks scans tablets (buckets) in parallel. If you have 8 buckets but 16 nodes, at most 8 nodes can participate in the scan for that specific table. The other 8 nodes stay idle during the scan phase. * The Fix: When scaling out, you should ensure your bucket count is at least equal to (or a multiple of) your node count. You can use Automatic Bucketing (available since v2.5+) to let StarRocks handle this for you. 2. Network Shuffle Overhead ($N \times N$) As the number of nodes increases, the complexity of data shuffling increases. * The Logic: In a 16-node cluster, a "Shuffle Join" or "Shuffle Aggregation" potentially requires every node to talk to every other node ($16 \times 16 = 256$ potential connections). For very small datasets, the time spent managing network buffers and RPC calls can outweigh the time saved by parallel computation. * The Reality: For large datasets (TB+), the benefit of dividing the CPU/Memory load across 16 machines far outweighs the network overhead. StarRocks uses a highly optimized Pipeline execution engine to stream these shuffles efficiently. 3. Small Query Latency If your workload consists of thousands of "point lookups" (queries that return in <10ms), adding more nodes can slightly increase tail latency (P99). * The Logic: The FE (Frontend) node must schedule query fragments to 16 nodes instead of 3. Each node must report back. The probability of one node being slightly slower (due to GC or OS jitter) increases with the node count. * The Fix: For high-concurrency small queries, you can use Short Circuit Execution or Query Cache to bypass the full MPP overhead. 4. Broadcast Join Costs * The Logic: If you perform a Join where one table is small, StarRocks "broadcasts" that small table to every node. On a 16-node cluster, that small table is copied 16 times across the network. * The Reality: This is only "bad" if the "small" table is actually quite large (e.g., several GBs). For typical dimension tables, this is still extremely fast. When 16 Nodes IS a Good Idea: * Large Data Volumes: If you are hitting memory limits or disk I/O bottlenecks. * Complex Analytics: Multi-table joins and high-cardinality aggregations scale linearly with more nodes. * High Concurrency: More nodes provide more CPU cores to handle many users querying simultaneously. Conclusion: Scaling to 16 nodes is only a bad idea if your data is too small to justify the overhead or if you forget to re-evaluate your bucketing strategy. For production-grade workloads, 16 nodes is often a "sweet spot" for performance and cost. References • deployment/shared_data/feature-support-shared-data.md • best_practices/query_tuning/query_profile_operator_metrics.md • knowledge/usage/best-practice.md • deployment/plan_cluster.md

@Rocky But won't Shuffle Overhead be worse during scale out events from 4 to 16 CNs?

The concern about shuffle overhead during a scale-out event (e.g., from 4 to 16 CNs) is a valid one in distributed systems. In a shuffle operation, every sender node potentially talks to every receiver node, meaning the number of network "channels" increases quadratically ($N$ senders $\times M$ receivers). However, in StarRocks, the performance usually improves rather than degrades during such a scale-out. Here is a breakdown of why the shuffle overhead is managed effectively and what actually happens: 1. Increased Aggregate Bandwidth While the number of network connections increases, so does the total available bandwidth and CPU power for serialization/deserialization. * With 4 nodes: You have 4 NICs and 4 sets of CPUs handling the shuffle. * With 16 nodes: You have 16 NICs and 16 sets of CPUs. Even though there are more "pieces" of data moving, each node is responsible for a much smaller slice of the total data, and the cluster has 4x the throughput capacity to move it. 2. Pipeline Execution Engine StarRocks uses a Pipeline Execution Engine designed to handle high-concurrency and asynchronous RPCs. * Non-blocking I/O: The exchange operators don't "stop and wait" for a single RPC to finish. They use a task-based model where many small data chunks (morsels) are pushed through the network concurrently. * Adaptive Buffer Management: StarRocks manages the memory buffers for these channels dynamically to prevent memory exhaustion as the number of nodes (and thus channels) grows. 3. Shared-Data Advantage (CNs) Since you specifically mentioned CNs (Compute Nodes), you are likely using StarRocks' Shared-data architecture. * Zero Data Rebalancing: Unlike "Shared-nothing" architectures where adding nodes triggers a massive background "shuffle" to rebalance data on disks (which can crush performance for hours), StarRocks CNs are stateless. * Immediate Scaling: When you go from 4 to 16 CNs, there is no disk-level shuffle. The new nodes simply start pulling data from the shared storage (S3/HDFS) and participating in new query shuffles immediately. 4. Optimizer Intelligence (CBO) The StarRocks Cost-Based Optimizer (CBO) is aware of the cluster size. When you scale to 16 nodes: * The CBO might shift from Shuffle Joins to Broadcast Joins if it determines that broadcasting a small table to 16 nodes is cheaper than shuffling two large tables. * It utilizes Local Shuffle optimizations: If data can be processed on the same node (intra-node parallelism), StarRocks will perform a local shuffle which bypasses the network stack entirely. 5. When could it actually be "worse"? There is one specific scenario where overhead might feel "worse": Small Queries. If you are running very small queries (processing only a few MBs), the fixed overhead of setting up 16x16 network channels might take longer than the actual data processing. However, for the large-scale analytical workloads StarRocks is designed for, the gain in parallel processing power significantly outweighs the RPC coordination overhead. Pro-Tip for 16+ Nodes: If you notice network bottlenecks after scaling, you can tune the following: *

rpc_compress_ratio_threshold

: Controls when StarRocks compresses shuffle data. *

enable_exchange_pass_through

: Ensure this is

true

(default) so that data destined for the same node doesn't hit the network. * Data Locality: In shared-data mode, ensure your Data Cache is warm on the new 12 nodes to minimize the "storage-to-CN" shuffle. References • best_practices/query_tuning/query_profile_operator_metrics.md • best_practices/query_tuning/query_planning.md • administration/management/BE_configuration.md

@Rocky If you had one 4 CN at

c7i.12xlarge

vs 16 CNs at

c7i.4xlarge

would you expect the very same QPS performance? If not which one would be better?

To answer your question directly: No, you should not expect the very same QPS performance, even though both configurations represent a significant amount of compute power. In your specific example, the 16 CNs at

c7i.4xlarge

actually have more aggregate raw resources than the 4 CNs at

c7i.12xlarge

: * 4x

c7i.12xlarge

: 192 vCPUs, 384 GiB RAM. * 16x

c7i.4xlarge

: 256 vCPUs, 512 GiB RAM. Despite having ~33% more CPU and RAM, the 16-node cluster may not necessarily provide 33% better performance, and in many scenarios, the 4-node cluster might actually be more efficient. Here is why: 1. Network Overhead and Shuffling StarRocks uses an MPP (Massively Parallel Processing) architecture. Many queries (especially joins and aggregations) require Data Shuffling between nodes. * 4 Nodes: There are only $4 \times 3 = 12$ possible network paths between nodes. Most data processing happens locally within the large 48-core nodes. * 16 Nodes: There are $16 \times 15 = 240$ possible network paths. The overhead of serializing, transmitting, and deserializing data across the network increases significantly with more nodes. * Result: For complex queries involving large joins, the 4 larger nodes often perform better because more work stays "local" to the node's memory and CPU. 2. AWS Instance Networking Limits AWS instances have different network performance profiles: * `c7i.4xlarge`: Up to 12.5 Gbps. * `c7i.12xlarge`: Up to 37.5 Gbps. While the 16 nodes have a higher aggregate theoretical bandwidth (16 * 12.5 = 200 Gbps), each individual node is more likely to hit its network throughput or Packets Per Second (PPS) limit during a heavy shuffle compared to the beefier

12xlarge

nodes. 3. FE Scheduling Overhead The Frontend (FE) must plan and schedule query fragments to every participating node. * Scheduling a query across 16 nodes is more complex than scheduling across 4. For very high QPS of "short" queries (simple lookups), the FE can become a bottleneck faster when managing a larger number of nodes. 4. Resource Utilization Efficiency * System Overhead: Every node runs its own OS, StarRocks background threads, and metadata caches. 16 nodes mean 16 sets of overhead, whereas 4 nodes mean only 4. * Data Cache: In StarRocks' shared-data architecture (which uses CNs), data is cached on local storage. Fewer, larger nodes typically manage a larger, more unified local cache more efficiently than many fragmented smaller caches. Summary Recommendation * Which is better for QPS? If your queries are very simple (point lookups, small scans) and the FE isn't the bottleneck, the 16-node cluster might win simply because it has 64 more vCPUs. * Which is better for Analytics/Complexity? The 4-node cluster is generally preferred. It will be more stable, have lower tail latency (P99) due to reduced network jitter, and handle complex joins much more efficiently. Rocky's Tip: In StarRocks, "Scale-Up" (fewer, larger nodes) is generally more efficient than "Scale-Out" (many small nodes) until you hit the limits of the largest available instance sizes or need more nodes for extreme high availability. For

c7i

instances, we typically recommend a minimum of 16 cores per node for production workloads. References • deployment/plan_cluster.md • faq/Deploy_faq.md • knowledge/usage/best-practice.md • faq/Others.md

@Rocky If you had one 4 CN at

c7i.12xlarge

vs 12 CNs at

c7i.4xlarge

would you expect the very same QPS performance? If not which one would be better?

No, you should not expect the exact same QPS performance. While the total number of vCPUs (192) and total memory (384 GiB) are identical in both configurations, the 4 CNs at

c7i.12xlarge

configuration will generally outperform the 12 CNs at

c7i.4xlarge

for several architectural reasons: Why 4 Large Nodes (

c7i.12xlarge

) are typically better: 1. Reduced Network Overhead (Shuffle): In a distributed query, data often needs to be "shuffled" (exchanged) between nodes. With 12 nodes, the network complexity increases significantly as data must be serialized, sent over the wire, and deserialized across 12 different endpoints. With 4 larger nodes, a much higher percentage of data processing happens locally within the same process or shared memory, which is significantly faster than network I/O. 2. Lower Scheduling Latency (FE Overhead): For every query, the Frontend (FE) must plan and schedule "fragments" to the available nodes. In a high-QPS scenario, scheduling tasks to 12 nodes instead of 4 creates 3x the scheduling overhead for the FE. This can become a bottleneck when trying to achieve maximum concurrency. 3. Better Resource Utilization (Pipeline Engine): StarRocks' Pipeline execution engine is designed to scale across many cores within a single node. Larger nodes allow for larger memory pools per process, which is more efficient for: * Large Hash Tables: Joins and aggregations can use a larger contiguous memory block without needing to spill to disk or partition data across more nodes. * Data Caching: If you use the Data Cache for shared-data architecture, fewer nodes mean less "cache fragmentation" and better cache hit rates for the same total cache size. 4. Lower OS Overhead: Running 12 instances means paying the "OS tax" 12 times (12 kernels, 12 sets of background processes, 12 StarRocks process overheads). 4 larger instances are more "efficient" in terms of pure compute power delivered to the query engine. When 12 Smaller Nodes (

c7i.4xlarge

) might be considered: * Fault Tolerance (Blast Radius): If one node fails, you lose ~8% of your capacity with 12 nodes, compared to 25% with 4 nodes. * Scaling Granularity: If you need to scale up later, adding one

4xlarge

(16 vCPUs) is cheaper and more granular than adding one

12xlarge

(48 vCPUs). Recommendation: For optimal QPS and lower latency, go with the 4 CNs at

c7i.12xlarge

. StarRocks generally prefers "thicker" nodes (larger instance types) to minimize the overhead of distributed coordination. Note on Bucketing: If you do choose 12 nodes, ensure your table bucketing (distribution) is a multiple of 12 (or significantly higher, like 24 or 48) to ensure all nodes are utilized. With 4 nodes, a bucket count of 16 or 20 would suffice to keep all cores busy. References • knowledge/usage/best-practice.md • deployment/shared_data/feature-support-shared-data.md • faq/Others.md • introduction/Architecture.md

@Rocky What is table bucketing (distribution)?

In StarRocks, bucketing (also known as distribution) is the second level of the data distribution strategy, following partitioning. While partitioning (optional) divides data by time or categories (e.g., by day), bucketing splits the data within each partition into smaller physical units called tablets. Tablets are the fundamental unit of data management in StarRocks for storage, replication, and parallel processing across Backend (BE) nodes. 1. Types of Bucketing StarRocks supports two primary bucketing methods: Hash Bucketing (Most Common) You specify one or more columns as bucketing keys. StarRocks applies a hash function to these keys to determine which bucket a row belongs to. * Key Advantage: Bucket Pruning. If your query filters on the bucketing key, StarRocks only scans the specific buckets that could contain that data, significantly reducing I/O. * Performance: Enables Colocated Joins and Bucket Shuffle Joins, which keep data local to nodes during joins and avoid expensive network shuffling. * Requirement: You must choose high-cardinality columns (e.g.,

user_id

,

order_id

) to ensure data is evenly distributed and avoid "data skew." Random Bucketing (Available since v3.1) Data is distributed across buckets in a round-robin fashion without needing a specific key. * Key Advantage: It is simple to use and guarantees no data skew because data is spread uniformly regardless of column values. * Automatic Scaling: Since v3.2, StarRocks can dynamically split tablets as the data grows if you use random bucketing. * Limitations: It is currently only supported for Duplicate Key tables and does not support bucket pruning or colocated joins. ────────── 2. How to Configure Bucketing You define the distribution method in the

CREATE TABLE

statement: Example: Hash Bucketing

sql
CREATE TABLE orders (
    order_id BIGINT,
    user_id BIGINT,
    order_date DATE
)
DUPLICATE KEY(order_id)
PARTITION BY RANGE(order_date) (...)
DISTRIBUTED BY HASH(order_id) BUCKETS 16; -- Use order_id as the hash key

Example: Random Bucketing

sql
CREATE TABLE logs (
    log_id BIGINT,
    message TEXT
)
DUPLICATE KEY(log_id)
DISTRIBUTED BY RANDOM BUCKETS AUTO; -- StarRocks automatically decides bucket count

────────── 3. Best Practices for Choosing Bucketing Keys To get the best performance from your StarRocks cluster: 1. High Cardinality: Pick columns with many unique values (like IDs) to prevent one BE node from being overloaded while others are idle. Avoid columns like "gender" or "status" as bucketing keys. 2. Query Patterns: Choose columns that are frequently used in

WHERE

clauses (for pruning) or

JOIN

conditions (for local joins). 3. Tablet Size: Aim for each tablet to be between 100 MB and 10 GB. * Too many small tablets increase metadata overhead. * Too few large tablets limit parallelism. 4. Auto-Bucketing: If you are unsure how many buckets to use, StarRocks can automatically determine the bucket count based on your cluster size and historical data. Use

DISTRIBUTED BY HASH(...) BUCKETS AUTO

. By correctly setting up bucketing, you ensure that your data is balanced across the cluster and that queries remain lightning-fast by only scanning the necessary data! References • table_design/data_distribution/Data_distribution.md • table_design/data_distribution/feature-support-data-distribution.md • best_practices/bucketing.md

@Rocky What is table bucketing (distribution)? Does it apply to Shared-data mode?

Table bucketing (also known as Distribution) is the second level of StarRocks' two-tier data distribution strategy (the first level being Partitioning). While partitioning typically divides data by time or category, bucketing further subdivides the data within each partition into smaller units called tablets. What is Table Bucketing? Bucketing determines how data is physically and logically distributed across the cluster. Each tablet is the smallest unit of data management, including storage, replication, and query execution. StarRocks supports two bucketing methods: * Hash Bucketing (Recommended): Data is distributed based on the hash value of one or more "bucketing keys." This allows for tablet pruning (scanning only relevant buckets) and Colocated Joins (joining tables with the same distribution without shuffling data). * Random Bucketing: Data is distributed randomly across buckets. This is simpler as it doesn't require choosing a key, but it doesn't support tablet pruning or certain join optimizations. Does it apply to Shared-data mode? Yes, table bucketing applies to Shared-data mode. In StarRocks' shared-data architecture (where data is stored in object storage like S3 or HDFS), the concept of bucketing remains fundamental for several reasons: 1. Parallelism: Even though data resides in centralized object storage, StarRocks uses tablets to divide the workload among Compute Nodes (CNs). Each tablet can be processed by different threads or nodes in parallel. 2. Metadata Management: The system manages data at the tablet level. Each tablet has its own metadata and versioning. 3. Query Optimization: Just like in shared-nothing mode, hash bucketing in shared-data mode allows the query optimizer to prune tablets and perform efficient joins. 4. Data Cache: Shared-data mode uses local disks on compute nodes as a cache. The cache management and replacement policies are often tied to these tablet structures. Shared-data Specific Considerations: * Table Creation Latency: In shared-data mode, creating a table with an excessive number of buckets can be slower because the system must write a metadata file for each tablet to the object storage. * Automatic Bucketing: Since StarRocks v2.5.7+, it is generally recommended to let the system automatically determine the number of buckets. This simplifies configuration regardless of the storage mode. Summary: Bucketing is a core architectural component of StarRocks that persists in Shared-data mode to ensure high-performance parallel processing and efficient data access. References • table_design/data_distribution/Data_distribution.md • faq/shared_data_faq.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md

@Rocky example of creating table with bucket collumn?

In StarRocks, you define bucket columns using the

DISTRIBUTED BY

clause. You can choose between Hash Bucketing (most common) or Random Bucketing (v3.1+). 1. Hash Bucketing (Recommended) Hash bucketing distributes data across buckets based on the hash value of specific columns. This is ideal for improving query performance on those columns.

sql
CREATE TABLE table_example (
    user_id INT,
    event_date DATE,
    city VARCHAR(20),
    revenue DECIMAL(10, 2)
) 
DUPLICATE KEY(user_id, event_date)
-- Distributed by the 'user_id' column
DISTRIBUTED BY HASH(user_id);

2. Hash Bucketing with Multiple Columns You can specify multiple columns to ensure better data distribution if a single column doesn't have high enough cardinality.

sql
CREATE TABLE orders (
    order_id BIGINT,
    order_date DATE,
    customer_id INT,
    amount DECIMAL(10, 2)
)
DUPLICATE KEY(order_id)
-- Distributed by both 'order_id' and 'customer_id'
DISTRIBUTED BY HASH(order_id, customer_id);

3. Random Bucketing (Since v3.1) If you don't want to pick a specific column or your data is heavily skewed, you can use random bucketing. This is currently only supported for Duplicate Key tables.

sql
CREATE TABLE log_data (
    log_time DATETIME,
    log_level VARCHAR(10),
    message TEXT
)
DUPLICATE KEY(log_time)
-- StarRocks distributes data randomly
DISTRIBUTED BY RANDOM BUCKETS 10;

4. Manually Setting Bucket Count Since version 2.5.7, StarRocks automatically determines the number of buckets, but you can still set them manually for fine-tuning.

sql
CREATE TABLE manual_buckets (
    id INT,
    name VARCHAR(50)
)
DISTRIBUTED BY HASH(id) BUCKETS 20;

Tips for choosing Bucket Columns: * High Cardinality: Choose columns with many unique values (e.g.,

user_id

,

order_id

) to avoid data skew. * Query Patterns: Choose columns that are frequently used as filters (

WHERE

clause) or in

JOIN

conditions. * Immutability: The values of bucketing columns cannot be updated. * Automatic Bucketing: In most cases, it is best to let StarRocks handle the bucket count automatically (omit the

BUCKETS

keyword). References • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md • table_design/table_types/aggregate_table.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE_AS_SELECT.md

@Rocky I want to run warmup querie on a new CN before it registers with FEs to make sure its actually ready to receive traffic. Is that possible?

Standard StarRocks Compute Nodes (CN) cannot execute SQL queries until they are registered with the Frontends (FE). This is because the CN is an execution engine; the query parsing, optimization, and fragment scheduling all happen at the FE. Without registration, the FE is unaware of the CN and cannot send it tasks. However, there are a few ways to achieve a "warmup" or "validation" phase before exposing a new CN to production traffic: 1. The Multi-Warehouse Approach (Recommended for Shared-data 3.x+) If you are using StarRocks 3.0 or later in shared-data mode, you can use the Multi-Warehouse (Compute Cluster) feature to isolate the new node during its warmup phase. 1. Create a Temporary Warehouse: Create a dedicated compute cluster that is not used by your production applications.

sql
    CREATE WAREHOUSE warmup_warehouse;

2. Add the New CN to the Warmup Warehouse: Register the new CN specifically to this isolated warehouse.

sql
    ALTER WAREHOUSE warmup_warehouse ADD NODE "cn_ip:9050";

3. Run Warmup Queries: In your SQL session, point your queries to the warmup warehouse. This ensures only the new CN (and others in that warehouse) processes the data.

sql
    SET warehouse = 'warmup_warehouse';
    -- Run your validation or warmup queries here
    SELECT count(*) FROM large_table;

4. Move to Production: Once you are satisfied, you can move the node to your production warehouse or simply direct traffic to it. 2. Manual Data Cache Warmup (

CACHE SELECT

) If your primary concern is warming up the Data Cache (local disk cache for shared-data), StarRocks provides a specific syntax for this. Note that the node must be registered first, but you can run this immediately after adding it:

sql
CACHE SELECT * FROM your_table WHERE ...;

This command synchronously loads data from remote storage (S3/HDFS) into the local disk cache of the available CNs. 3. Check Health via HTTP API (Pre-registration) Before you run

ALTER SYSTEM ADD COMPUTE NODE

, you can verify the node is "ready" at a process level by hitting its HTTP heartbeat endpoint:

bash
curl http://<cn_ip>:<be_http_port>/api/health

If this returns a success status, the CN is properly started, the ports are open, and it is ready to receive heartbeats from the FE. Summary * Is it possible to run SQL before registration? No, because SQL requires the FE's coordination. * Best workaround: Use a separate Warehouse (in 3.x) to isolate the CN while you run test queries, or add it to the cluster and use the

CACHE SELECT

statement to prime the cache before directing user traffic to it. References • sql-reference/sql-statements/cluster-management/resource_group/CREATE_RESOURCE_GROUP.md • administration/stargo.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md