http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> what is recommended tablet size and...
# questions-and-troubleshooting
a
@Rocky what is recommended tablet size and count in starrocks 4.1, i saw that the maximum size of a tablet is changed to 100 GB
r
Yes, you are correct. Starting from StarRocks 4.1, the recommended maximum size for a tablet (specifically for shared-data/cloud-native clusters) has been increased to 100 GB. This is a significant shift from previous versions where the recommendation was typically 1 GB to 10 GB per tablet. Key Changes in StarRocks 4.1 * Target Size: The new target capacity for a single tablet is 100 GB. * Architecture Focus: This large-capacity tablet support is primarily designed for shared-data clusters (Cloud Native). * Parallel Processing: To handle such large tablets without performance degradation, StarRocks 4.1 introduced: * Parallel Compaction: Multiple threads can perform compaction within a single tablet. * Parallel MemTable Finalization: Reduces overhead during data ingestion. * Tablet Internal Parallelism: Allows multiple threads to scan a single tablet in parallel during queries. Recommended Tablet Count Because individual tablets can now be much larger, the total number of tablets in your cluster can be significantly reduced. * Goal: You should aim to reduce the total tablet count to lower the metadata pressure on the Frontend (FE). * General Guideline: While there is no "hard" number, reducing the tablet count helps with: * FE Stability: Less memory and CPU usage for metadata management. * Ingestion Efficiency: Fewer tablets mean fewer MemTables and less memory overhead during high-concurrency ingestion (like CDC). * Faster Recovery: Metadata-related operations and cluster restarts become faster with fewer tablets. When to use 100 GB vs. 10 GB? * Large Datasets (PB Scale): Use the 100 GB target to keep the total tablet count manageable. * Small/Medium Datasets: You can still stick to the 1–10 GB range. If your partition size is small (e.g., only a few GBs), don't force it into one massive tablet if it naturally splits into a few smaller ones for better distribution across BE nodes. * Data Skew: If you have severe data skew, smaller tablets (and more buckets) may still be useful to distribute the load across more BE/CN nodes, though 
RANDOM
bucketing and parallel scanning in 4.1 mitigate this. Summary Recommendation for 4.1: If you are on a shared-data cluster with large volumes of data, plan your buckets so that each tablet holds between 10 GB and 100 GB. This will improve ingestion stability and reduce FE overhead. References • faq/Deploy_faq.md • deployment/plan_cluster.md • table_design/data_distribution/Data_distribution.md • faq/Others.md • knowledge/usage/best-practice.md • best_practices/partitioning.md
a
@Rocky what about shared-nothing?
r
For shared-nothing clusters, the recommendation remains different than for shared-data. While StarRocks 4.1 introduces the 100 GB target for shared-data, shared-nothing clusters should still target approximately 10 GB per tablet. Here is the breakdown of why shared-nothing keeps a smaller tablet size: 1. Recommended Tablet Size: 10 GB In modern StarRocks versions (v2.4 and later, including 4.1), the recommended target for shared-nothing is 10 GB of raw data per tablet. * Previously: The recommendation was 1 GB. * Now: Because of Parallel Scan (
enable_tablet_internal_parallel = true
), a single tablet can be scanned by multiple threads. This removed the "one thread per tablet" bottleneck, allowing the size to grow to 10 GB to reduce metadata overhead. 2. Why not 100 GB for Shared-Nothing? While the 100 GB optimization is the headline for shared-data in 4.1, it is not currently the standard for shared-nothing for several reasons: * Data Rebalancing: In shared-nothing, the tablet is the basic unit of data movement. If you have 100 GB tablets, adding a new BE node would require moving massive files across the network to rebalance. 10 GB tablets provide a more "granular" and manageable unit for background balancing. * Replica Management: Shared-nothing manages 3 replicas of every tablet. Repairing or migrating a 100 GB tablet after a node failure is much more resource-intensive and takes longer than a 10 GB tablet. * Phase 1 Focus: The StarRocks 4.1 "Large-Capacity Tablet" feature (Phase 1) is explicitly optimized for Lake tables (shared-data). The internal parallelism for compaction and ingestion was built to solve the high latency of object storage; shared-nothing's local SSDs already handle smaller files very efficiently. 3. Recommended Tablet Count For shared-nothing, the goal is to balance performance with metadata stability: * Avoid "Small Tablet" Problem: Having millions of 100 MB tablets will overwhelm the Frontend (FE) metadata memory and cause high CPU usage during compaction. * General Rule: Aim for a total tablet count that keeps your FE memory usage healthy (typically under 1–2 million tablets for a standard 32GB/64GB FE). * Bucketing Strategy: Use 
DISTRIBUTED BY RANDOM BUCKETS AUTO
if possible, or calculate buckets such that: 
Number of Buckets = (Estimated Partition Data Size) / 10 GB
Summary Table for StarRocks 4.1 Architecture | Recommended Tablet Size | Key Reason Shared-Data (Lake) | 100 GB | Reduces object storage metadata calls and FE pressure. Shared-Nothing (Local) | 10 GB | Balances query speed with data rebalancing/repair agility. Note: If you have an exceptionally large shared-nothing cluster (PB scale) and your FE is struggling with metadata, you can cautiously push tablets toward 20–30 GB, but 100 GB is currently not recommended for local storage due to the rebalancing and repair risks mentioned above. References • introduction/Architecture.md • faq/Deploy_faq.md • administration/management/resource_management/Replica.md • deployment/shared_data/feature-support-shared-data.md
a
@Rocky is it possible to collocate async mv and table?
r
Yes, it is possible to collocate an asynchronous materialized view (MV) with its base table (or any other table) starting from StarRocks v3.0. By putting an MV and its base table into the same Colocation Group, you can ensure that their data is distributed across the same set of BE nodes based on the same bucketing keys. This enables Colocate Joins between the MV and the base table, significantly improving query performance by avoiding data shuffling over the network. How to set it up You can specify the 
colocate_with
property when creating the MV:
Copy code
sql
CREATE MATERIALIZED VIEW my_mv
DISTRIBUTED BY HASH(user_id) BUCKETS 10
REFRESH ASYNC EVERY(INTERVAL 1 DAY)
PROPERTIES (
    "colocate_with" = "group_name"
)
AS 
SELECT user_id, SUM(price) 
FROM base_table 
GROUP BY user_id;
If the base table is already in a colocation group, simply use the same group name. If not, you can also alter the base table to join the group:
Copy code
sql
ALTER TABLE base_table SET ("colocate_with" = "group_name");
Requirements for Colocation For the colocation to be valid and stable, the following must match exactly between the MV and the base table: 1. Bucketing Columns: The distribution keys must have the same number of columns, the same data types (in the same order), and the same bucketing logic (Hash). 2. Bucket Count: They must have the exact same number of buckets. 3. Replica Count: They must have the same number of replicas (replication factor). Key Benefits * Colocate Join Performance: If you frequently join the MV with the base table (or other tables in the same group), StarRocks can perform a local join on each BE node without moving data. * Query Rewrite: If a query on the base table is transparently rewritten to use the MV, and that MV needs to be joined with another table in the same colocation group, the join remains a Colocate Join. Note: If you are using a version earlier than 3.0, the 
colocate_with
property is only supported for standard tables and synchronous MVs (Rollup), not asynchronous MVs. References • using_starrocks/async_mv/feature-support-asynchronous-materialized-views.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • using_starrocks/Colocate_join.md • administration/management/FE_configuration.md • using_starrocks/async_mv/Materialized_view.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md References • using_starrocks/async_mv/feature-support-asynchronous-materialized-views.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md
a
@Rocky i am using starrocks version 4.1.1 and getting error
Copy code
Colocate tables distribution columns must have the same data type with group 12283.233767, current col: id, should be: VARCHAR(36), current info type: HASH; distribution columns: [id,]; bucket num: 16;

both tables have id as VARCHAR(36)
r
The error you are seeing occurs because StarRocks requires exact matching of the distribution column's metadata for a colocation group. Even if both columns are named 
id
and are conceptually 
VARCHAR(36)
, they must match in nullability and character set/encoding. In StarRocks 4.1.1, this check is strict. The most common reasons for this mismatch when using Materialized Views (MVs) are: 1. Nullability Mismatch (Most Likely) StarRocks treats 
VARCHAR(36) NOT NULL
and 
VARCHAR(36) NULL
as different types for colocation. * Base Table: Often has 
id VARCHAR(36) NOT NULL
. * Async MV: When you create an MV, StarRocks infers the schema from the 
SELECT
statement. In many cases (especially if the query involves Joins or certain expressions), the optimizer marks the output columns as nullable (
NULL
) by default to be safe. How to verify: Run the following for both the base table and the MV:
Copy code
sql
DESC your_base_table_name;
DESC your_mv_name;
Check the Null column. If one says 
YES
and the other says 
NO
, the colocation will fail. How to fix: If the MV column is inferred as nullable, you can try to force it to be 
NOT NULL
by using a 
CAST
or ensure the query logic guarantees it, though StarRocks inference can be stubborn. Alternatively, you can 
ALTER
the base table to be nullable (if business logic allows) so they match. 2. Inferred Length Mismatch If your MV's 
SELECT
statement performs any transformation on the 
id
column, StarRocks might infer a different length (e.g., 
VARCHAR(65533)
or 
VARCHAR(1048576)
) instead of 
36
. * How to fix: Explicitly cast the column in the MV definition:
Copy code
sql
    CREATE MATERIALIZED VIEW my_mv
    DISTRIBUTED BY HASH(id)
    ...
    AS
    SELECT CAST(id AS VARCHAR(36)) AS id, ... FROM base_table;
3. Check the Colocation Group Schema To see exactly what the existing colocation group expects, run:
Copy code
sql
SHOW PROC '/colocation_group';
Find your group ID (e.g., 
12283.233767
) and look at the 
DistCols
column. This will show the exact type (e.g., 
varchar(36)
) and whether it is nullable that the "Parent" table of that group established. 4. Character Set / Collation In StarRocks 3.2+, different collations (like 
utf8_general_ci
vs 
binary
) can cause a type mismatch. Ensure both the table and the MV are using the same session collation during creation. Summary Checklist: 1. Buckets: Ensure both have exactly 
BUCKETS 16
. 2. Replicas: Ensure both have the same 
replication_num
. 3. Nullability: Check if one is 
NOT NULL
and the other is 
NULL
. 4. Length: Ensure the MV schema didn't expand the 
VARCHAR
to a larger size. If you find that the MV inferred the wrong type, the best solution is to re-create the MV with an explicit 
CAST(id AS VARCHAR(36))
 in the 
SELECT
clause to match the base table exactly. References • using_starrocks/Colocate_join.md • best_practices/query_tuning/schema_tuning.md
a
@Rocky Checked everything in this list Summary Checklist: 1. Buckets: Ensure both have exactly 
BUCKETS 16
. 2. Replicas: Ensure both have the same 
replication_num
. 3. Nullability: Check if one is 
NOT NULL
and the other is 
NULL
. 4. Length: Ensure the MV schema didn't expand the 
VARCHAR
to a larger size. cast to *`VARCHAR(36)`*didn't help same error
r
In StarRocks 4.1.1, the "same data type" requirement for colocation is extremely strict and covers metadata that might not be obvious in a simple 
DESC
output. Since you have already verified the length and bucket counts, the issue is almost certainly one of the following two hidden metadata mismatches: 1. Nullability (Most Common) Even if both columns are 
VARCHAR(36)
, they must have the same "Null" property. * Base Table: Likely defined as 
id VARCHAR(36) NOT NULL
. * MV: The optimizer often infers MV columns as nullable (
NULL
) by default, even if you use a 
CAST
. A 
CAST(id AS VARCHAR(36))
does not change the nullability of the column in the MV's resulting schema. The Fix: Force the MV column to be 
NOT NULL
by using 
coalesce
. StarRocks' optimizer can often recognize that a 
coalesce
against a non-null literal (like an empty string) results in a 
NOT NULL
column:
Copy code
sql
CREATE MATERIALIZED VIEW my_mv
DISTRIBUTED BY HASH(id) BUCKETS 16
PROPERTIES ("colocate_with" = "your_group")
AS 
SELECT 
    CAST(coalesce(id, '') AS VARCHAR(36)) AS id, -- Force NOT NULL and Type
    ...
FROM base_table;
2. Collation Mismatch (Newer Versions) StarRocks 3.3+ and 4.x support column collations. If your base table was created with one collation (e.g., 
binary
) and your current session or the MV's default is different (e.g., 
utf8mb4_general_ci
), the types are considered different. * Check: Run 
SHOW CREATE TABLE <base_table>
and look for any 
COLLATE
keywords. * Check: Run 
SHOW CREATE MATERIALIZED VIEW <mv_name>
(if it partially created) or check your session variable 
select @@collation_database
. 3. Case Sensitivity in Column Names While StarRocks column names are generally case-insensitive in queries, the Colocation Group registration is sometimes sensitive to the case used in the 
DISTRIBUTED BY HASH(...)
clause during the initial creation of the group. * If the base table (the "Parent" of the group) was created with 
DISTRIBUTED BY HASH(ID)
, but your MV uses 
DISTRIBUTED BY HASH(id)
, try matching the case exactly. Recommended Troubleshooting Steps: 1. Inspect the Group Metadata: Run this command to see exactly what the group expects:
Copy code
sql
    SHOW PROC '/colocation_group';
Find the entry for your group ID. Look at the 
DistCols
column. It will show the exact type string (e.g., 
varchar(36)
) and potentially other flags. 2. Compare DESC outputs side-by-side:
Copy code
sql
    DESC <base_table>;
    DESC <mv_name>;
Pay special attention to the Null column. If the base table says 
NO
and the MV says 
YES
, they cannot be colocated. If the nullability is the issue, the 
coalesce
trick above is the most reliable way to align them without altering your base table. References • using_starrocks/Colocate_join.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md • table_design/table_types/Materialized_view.md • best_practices/query_tuning/schema_tuning.md • using_starrocks/async_mv/feature-support-asynchronous-materialized-views.md References • best_practices/query_tuning/schema_tuning.md • faq/operation_maintenance_faq.md
a
@Rocky base tables all ready in the same collocation group, i just trying to add mv to the same group
r
This error persists because StarRocks 4.1.1 is extremely strict about the internal metadata of distribution columns. Even if both columns appear as 
VARCHAR(36)
in a 
DESC
output, they may differ in "widening" behavior, nullability, or collation. Since you've already tried a basic 
CAST
, the issue is likely one of the following two hidden behaviors: 1. The "VARCHAR Widening" Behavior (Most Likely) In StarRocks 4.x, a new feature automatically converts 
VARCHAR(N)
to the 
STRING
type (which is internally 
VARCHAR(1048576)
) during MV creation to avoid truncation. This causes a mismatch if your base table is strictly 
VARCHAR(36)
. The Fix: Set the following session variable to 
false
before running your 
CREATE MATERIALIZED VIEW
statement:
Copy code
sql
SET transform_type_prefer_string_for_varchar = false;
This forces the MV to respect the explicit length in your 
CAST(id AS VARCHAR(36))
instead of widening it to the maximum length. 2. Hidden Nullability Mismatch StarRocks considers 
VARCHAR(36) NULL
and 
VARCHAR(36) NOT NULL
as different types for colocation. Even if you 
CAST
a column, the optimizer usually marks the result as Nullable by default if the source table or logic allows nulls. How to verify: 1. Run 
DESC <base_table>;
and 
DESC <mv_name>;
(if you can create it without the colocation property first). 2. Check the Null column. If the base table is 
NO
and the MV is 
YES
, they cannot be colocated. The Fix: Force the column to be 
NOT NULL
by wrapping it in a 
COALESCE
or 
IFNULL
with a constant. The optimizer will then recognize the output as `NOT NULL`:
Copy code
sql
CREATE MATERIALIZED VIEW my_mv
DISTRIBUTED BY HASH(id) BUCKETS 16
PROPERTIES ("colocate_with" = "group_name")
AS 
SELECT 
    CAST(COALESCE(id, '') AS VARCHAR(36)) AS id,
    ...
FROM base_table;
3. Check the Exact Group Requirements To see exactly what the existing colocation group expects (including length and nullability), run:
Copy code
sql
SHOW PROC '/colocation_group';
Find your group ID (e.g., 
12283.233767
). The 
DistCols
column will show exactly what the "Parent" table of that group established. Your MV must match that string identically. Summary Recommendation Try creating the MV again with these settings:
Copy code
sql
SET transform_type_prefer_string_for_varchar = false;

CREATE MATERIALIZED VIEW your_mv
DISTRIBUTED BY HASH(id) BUCKETS 16
PROPERTIES ("colocate_with" = "your_group_name")
AS 
SELECT 
    CAST(COALESCE(id, '') AS VARCHAR(36)) AS id, 
    ...
FROM base_table;
Note: If the base table's 
id
column is actually nullable (
YES
in DESC), remove the 
COALESCE
and just use the 
CAST
while ensuring the session variable is set to 
false
. References • administration/management/FE_configuration.md • using_starrocks/Colocate_join.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • table_design/data_distribution/Colocate_join.md • using_starrocks/async_mv/feature-support-asynchronous-materialized-views.md • table_design/table_types/Materialized_view.md • sql-reference/data-types/string-type/VARCHAR.md References • administration/management/FE_configuration.md
a
@Rocky desc showing that id field is varchar(1048576), even if u cast it to VARCHAR(36)
r
In StarRocks 4.x (including your version 4.1.1), there is a new default behavior intended to prevent Materialized View (MV) refresh failures caused by character truncation. The system automatically "widens" 
CHAR
and 
VARCHAR
columns to the 
STRING
type (internally 
VARCHAR(1048576)
) when creating MVs or using CTAS. This widening causes a mismatch with your colocation group, which expects a strict 
VARCHAR(36)
. The Solution You must disable this "type widening" behavior in your session before running the 
CREATE MATERIALIZED VIEW
statement. 1. Set the session variable:
Copy code
sql
    SET transform_type_prefer_string_for_varchar = false;
_Note: If you want this to apply to all users/sessions, use 
SET GLOBAL transform_type_prefer_string_for_varchar = false;
(requires admin privileges)._ 2. Re-create the MV with an explicit CAST and Nullability check: Even with the variable set, StarRocks might infer the column as 
NULL
(nullable). If your base table's 
id
is 
NOT NULL
, the colocation will still fail. Use 
COALESCE
to guarantee the 
NOT NULL
status in the MV schema:
Copy code
sql
    -- Force the MV to respect the VARCHAR(36) length
    SET transform_type_prefer_string_for_varchar = false;

    CREATE MATERIALIZED VIEW your_mv_name
    DISTRIBUTED BY HASH(id) BUCKETS 16
    PROPERTIES ("colocate_with" = "your_group_name")
    AS
    SELECT
        -- CAST ensures the type/length, COALESCE ensures NOT NULL
        CAST(COALESCE(id, '') AS VARCHAR(36)) AS id,
        ...
    FROM base_table;
Why this happens in 4.x * Behavior Change: Starting in StarRocks 4.0, the system defaults 
transform_type_prefer_string_for_varchar
to 
true
. This is designed to help users avoid "String is too long" errors during asynchronous refreshes if the source data exceeds the inferred length. * Colocation Conflict: Because Colocate Join groups require a 1:1 metadata match (including the exact defined length), this automatic widening to 1,048,576 bytes breaks the colocation group logic. Verification After creating the MV, run:
Copy code
sql
DESC your_mv_name;
It should now show 
VARCHAR(36)
instead of 
VARCHAR(1048576)
. If it does, and the Null column matches your base table (usually 
No
), the table will successfully join the colocation group. References • administration/management/FE_configuration.md • release_notes/release-4.0.md • using_starrocks/Colocate_join.md • sql-reference/data-types/string-type/VARCHAR.md References • knowledge/usage/best-practice.md • sql-reference/data-types/string-type/VARCHAR.md • faq/table_design_faq.md • release_notes/release-2.4.md • administration/management/FE_configuration.md • release_notes/release-4.0.md
2 Views
Open in Slack
PreviousNext
Powered by