@Rocky I can see some number in running queries in NumRunningQueries section in

show compute nodes

but in processlist, I don't see any running queries currently. what is actually running but not showing in processlist? in processlist, I can seee all sleep connections

The discrepancy you see between

SHOW COMPUTE NODES

and

SHOW PROCESSLIST

is common because they represent different layers of the StarRocks architecture. 1.

SHOW PROCESSLIST

is FE-specific

SHOW PROCESSLIST

shows the client connections and the SQL statements being managed by the Frontend (FE) you are currently connected to. • If you have multiple FEs, a query running on another FE will not appear in your current

SHOW PROCESSLIST

. • Even if a connection is in a

Sleep

state, the compute nodes (CNs) might still be finishing up the heavy lifting for the last query that was sent. 2.

NumRunningQueries

is CN-specific The

NumRunningQueries

column in

SHOW COMPUTE NODES

(or

SHOW BACKENDS

) represents the number of query fragments or tasks currently executing on that specific worker node. A single SQL query submitted to an FE is broken down into multiple "fragments" that are distributed across all CNs. Thus, one user query can trigger active "running queries" counts on every compute node in the cluster. What is actually running? If

SHOW PROCESSLIST

shows nothing but

NumRunningQueries

is non-zero, the "missing" tasks are likely: * Queries from other FEs: In a multi-FE cluster, other FEs are likely sending work to those compute nodes. * Internal & Background Tasks: StarRocks performs several background operations that consume compute resources but don't show up as a standard user connection: * Statistics Collection: Automatic

ANALYZE

tasks for gathering table statistics. * Materialized View (MV) Refreshes: Background jobs refreshing asynchronous MVs. * Loading Tasks: Routine Loads or internal

INSERT

tasks. * Schema Changes: Background tasks for table alterations. How to see everything To get a "global" view of what is truly running across the entire cluster, use these commands instead: * View all queries across all FEs (v3.4+):

sql
    SHOW PROC '/global_current_queries';

* View queries on the current FE:

sql
    SHOW PROC '/current_queries';

Note: This view includes a

QueryType

column which will tell you if the task is a

Query

,

Statistics

,

MV

, or

Internal

task. * View Query Queue status:

sql
    SHOW RUNNING QUERIES;

(Requires query queues to be enabled). References • sql-reference/sql-statements/cluster-management/nodes_processes/SHOW_RUNNING_QUERIES.md • using_starrocks/running_queries.md • sql-reference/sql-statements/cluster-management/nodes_processes/SHOW_BACKENDS.md

@Rocky is it better to keep differnt buckets/tablets for different partitions based on size of partition or keep a common tablets across all partitions of table?

In StarRocks, the best practice is to tune the bucket count so that each tablet (the unit of data in a bucket) is between 100MB and 1GB (compressed). Whether you should use a common count or vary it depends on your version and data distribution: 1. The Modern Recommendation:

BUCKETS auto

(v2.5.7+) If you are on version 2.5.7 or higher, use the

BUCKETS auto

property when creating a table. * How it works: StarRocks automatically determines the number of buckets for each partition. For the first few partitions, it uses a formula based on your BE node count. For subsequent partitions, it looks at the data volume in the last 5 partitions to adjust the bucket count for new ones. * Advantage: It solves the dilemma of having "too many small tablets" for small partitions and "too few giant tablets" for large partitions without manual intervention. 2. When to use a Common Bucket Count Use a fixed, common bucket count across all partitions if: * Colocate Joins: You are using the

colocate_with

property to join multiple tables locally. Tables in a colocate group must have the same number of buckets. * Predictable Data: Your data volume per partition is relatively stable (e.g., every daily partition is roughly the same size). 3. When to use Different Bucket Counts (Manual) If you are on an older version and don't have

auto

bucketing, you should manually specify different bucket counts if you have massive data skew between partitions (e.g., a "current day" partition with 1TB of data and "historical" partitions with only 10GB). * Small Partitions: Aim for fewer buckets to avoid "Metadata Explosion." Having hundreds of thousands of tiny tablets puts heavy pressure on the Frontend (FE) memory and slows down management tasks. * Large Partitions: Aim for more buckets to increase parallelism. If a tablet exceeds 10GB, you may encounter slow compaction and recovery times. Summary Comparison Strategy | Pros | Cons | Recommended for... Common Count | Simple; supports Colocate Joins. | Risk of "too many small" or "too few large" tablets. | Stable data sizes or Colocate Groups. Different Counts | Optimized tablet sizes (100MB-1GB). | Manual management; breaks Colocate Joins. | Extreme skew (Historical vs. Recent).

BUCKETS auto

| Hands-off; handles skew automatically. | Less control over exact parallelism. | Most use cases (v2.5.7+). How to check tablet sizes You can check if your current bucketing strategy is effective by looking at the actual size of your tablets:

sql
SHOW TABLET FROM <table_name>;

``` Look for the

DataSize

column. If you see many tablets under 10MB or over 10GB, you should reconsider your bucket count. ## References - [knowledge/usage/best-practice.md](https://docs.starrocks.io/docs/knowledge/usage/best-practice) - [best_practices/bucketing.md](https://docs.starrocks.io/docs/best_practices/bucketing) - [best_practices/query_tuning/schema_tuning.md](https://docs.starrocks.io/docs/best_practices/query_tuning/schema_tuning) - [best_practices/partitioning.md](https://docs.starrocks.io/docs/best_practices/partitioning) - [table_design/data_distribution/Data_distribution.md](https://docs.starrocks.io/docs/table_design/data_distribution/Data_distribution)

@Rocky I'm in 4.0.8 version using shared data, and historical data is very small compared to recent data, lot of data skew will be there.

For your version (4.0.8) and Shared-data architecture, it is definitely better to have different bucket counts for different partitions to handle your data skew. In StarRocks, the "one-size-fits-all" bucket count is legacy. Having a common count across a table with significant skew leads to two problems: 1. Historical Partitions: "Metadata Explosion" where too many tiny tablets (e.g., 1MB each) slow down the Frontend (FE) and increase management overhead. 2. Recent Partitions: "Giant Tablets" (e.g., >100GB each) that limit query parallelism and make data compaction/recovery very slow. Here are the recommended strategies for your specific setup: 1. The Best Choice: Automatic Bucketing (

BUCKETS auto

) Since v2.5.7, you can simply omit the

BUCKETS

keyword. StarRocks will automatically calculate the bucket count for each partition. * How it handles skew: For new partitions, StarRocks looks at the data volume of the last 5 partitions. If your recent data is huge, it will automatically increase the bucket count for the next day's partition. If you ingest smaller data later, it will scale down. * Recommendation: Use this as your default. It provides "Different buckets for different partitions" without manual work. 2. For

Duplicate Key

Tables: Random Bucketing +

bucket_size

If your table is a

Duplicate Key

model (common for logs/historical data), version 4.0.x supports an even more advanced feature:

sql
DISTRIBUTED BY RANDOM
PROPERTIES ("bucket_size" = "1073741824") -- 1GB per bucket

* Why this is great for skew: Instead of guessing the number of buckets, StarRocks will dynamically split tablets as you load data. * Result: Small historical partitions might only have 1 bucket (1 tablet), while massive recent partitions will automatically grow to hundreds of buckets to keep each tablet around 1GB. 3. Manual Overrides (If needed) If you know a specific partition will be massive or tiny, you can manually specify the distribution when adding that partition:

sql
ALTER TABLE my_table ADD PARTITION p_massive 
    VALUES LESS THAN ("2024-01-01")
    DISTRIBUTED BY HASH(user_id) BUCKETS 128; -- Higher for recent data

Key Metrics to Follow In Shared-data mode, your target should be: * Ideal Tablet Size: 100MB – 1GB (compressed). * Max Tablet Size: Try not to exceed 10GB per tablet. * Minimum Tablet Size: Avoid having thousands of tablets smaller than 10MB. To verify your current skew and tablet sizes, run:

sql
-- View all partitions, their bucket counts, and data sizes
SELECT 
    TABLE_NAME, 
    PARTITION_NAME, 
    BUCKETS, 
    DATA_SIZE 
FROM information_schema.partitions_meta 
WHERE TABLE_NAME = 'your_table_name';

Summary for v4.0.8 Shared-data: * Avoid a fixed common bucket count if skew is large. * Use

BUCKETS auto

for most cases (Hash distribution). * Use

RANDOM

+

bucket_size

for Duplicate Key tables to get the best automatic scaling. References • knowledge/usage/best-practice.md • best_practices/bucketing.md • table_design/data_distribution/Data_distribution.md

@Rocky where is it mentioned in official doc? if looks at last 5partitions to determine the tablet count for next partition?

The logic regarding the "last 5 partitions" is part of StarRocks' internal Automatic Bucketing algorithm, which is officially described in the Data Distribution and Best Practices documentation. In version 4.0.8, when you use

BUCKETS auto

(or omit the

BUCKETS

clause), StarRocks uses two different methods to decide the bucket count: 1. Initial State (Fewer than 5 partitions) If the table is new or has fewer than 5 existing partitions, StarRocks cannot predict the data volume yet. It uses a formula based on your BE node count (X) to ensure basic parallelism: * X ≤ 12:

2 * X

buckets * X ≤ 24:

1.5 * X

buckets * X ≤ 36:

36

buckets * X > 36:

min(X, 48)

buckets 2. Stable State (5+ partitions) Once you have 5 or more partitions, StarRocks analyzes the actual data volume of the last 5 partitions. * It calculates an average or trend from these partitions to determine if the next partition needs more or fewer buckets to stay within the recommended 100MB – 1GB per tablet range. * This is why it's perfect for your "skewed" scenario: as your daily data volume grows, StarRocks will see the increase in the recent partitions and automatically scale up the bucket count for tomorrow's partition. Where to find this in the docs * Documentation Page: Data distribution - Automatically set the number of buckets * Best Practices: The detailed calculation logic (the "last 5 partitions" rule and the BE-based formula) is documented in the Best Practices and Data Model Design sections of the StarRocks knowledge base/GitHub docs. Important Note for your 4.0.8 Cluster Since you are on 4.0.8, you have an even better option for skewed data if you are using

Duplicate Key

tables: Random Bucketing with

bucket_size

. Instead of the "last 5 partitions" logic, you can set:

sql
PROPERTIES("bucket_size" = "1073741824") -- 1GB

This allows the bucket count to increase dynamically during the load itself. If a single partition suddenly gets 10x more data than usual, StarRocks will just keep adding buckets until the data is finished, ensuring you never end up with massive tablets. References • table_design/data_distribution/Data_distribution.md • knowledge/usage/best-practice.md • best_practices/bucketing.md References • table_design/data_distribution/Data_distribution.md • knowledge/usage/best-practice.md