http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> please help with this error ```ERRO...
# questions-and-troubleshooting
r
@Rocky please help with this error
Copy code
ERROR (AutoStatistic|37) [HyperQueryJob.executeStatisticsQuery():156] execute statistics query failed
...
com.starrocks.sql.analyzer.SemanticException: Getting analyzing error. Detail message: Statistics query fail | Error Message [[reason=INTERNAL_ERROR] [msg=exceed big query cpu limit: current is 120010418554ns but limit is 120000000000ns: BE:29058]]
r
The error 
exceed big query cpu limit: current is 120010418554ns but limit is 120000000000ns
indicates that an automatic statistics collection task (
AutoStatistic
) was terminated because it exceeded the CPU time limit configured for its resource group. In your case, the limit is set to 120 seconds (
120000000000ns
). When StarRocks collects statistics, it runs background queries (e.g., 
SELECT count(*)...
) to gather data for the optimizer. If the table is very large or the query is complex, it may hit this "Big Query" CPU threshold. How to Fix 1. Identify and Increase the Resource Group Limit You need to find the resource group that the statistics job is using and increase its 
big_query_cpu_second_limit
. Since these are background tasks, they typically run under the 
root
user or a specific database classifier. First, check your current resource groups and their limits:
Copy code
sql
SHOW RESOURCE GROUPS ALL;
Look for the group that has 
big_query_cpu_second_limit
set to 
120
. To increase it (e.g., to 300 seconds) or disable the limit (set to 0), use:
Copy code
sql
ALTER RESOURCE GROUP <group_name> WITH (
    'big_query_cpu_second_limit' = '300' 
);
Note: Setting it to 
0
removes the limit entirely. 2. Use Sampled Statistics If full statistics collection is too heavy for your table, you can configure the analyze job to use sampling. This significantly reduces the CPU time required. You can create a custom sampled analyze job for the problematic table:
Copy code
sql
CREATE ANALYZE SAMPLE TABLE <table_name> PROPERTIES (
    "statistic_sample_collect_rows" = "200000"
);
3. Adjust Statistics Collection Properties If you want to adjust the behavior of the automatic collection globally, you can check the FE configuration related to statistics: • `statistic_sample_collect_rows`: The threshold above which sampling is used. • `statistic_auto_collect_ratio`: The health threshold that triggers an automatic update. Why did this happen? StarRocks uses Resource Groups to prevent a single query (including background ones) from monopolizing cluster resources. The 
big_query_cpu_second_limit
is a safety mechanism. If you recently added large tables or enabled more granular statistics, the background queries may now require more than the default allotted 120 seconds of CPU time on a single BE. References • faq/resource_isolation_faq.md • administration/management/resource_management/resource_group.md • administration/management/monitor_manage_big_queries.md • administration/management/monitoring/metrics.md • faq/Others.md
r
@Rocky will it keep trying? i noticed a cluster of these errors at once and then they stopped. does that mean it since succeeded, or gave up?
r
The short answer is: Yes, it will keep trying, but not immediately. The "cluster of errors" you saw, followed by silence, is typical of how the StarRocks automatic statistics scheduler works. Here is a breakdown of why that happens and how to check if it eventually succeeded: 1. Why did the errors come in a cluster? StarRocks runs a background thread that checks the "health" of your tables every 10 minutes (defined by 
statistic_collect_interval_sec
, default 
600
). * The Cluster: When the 10-minute check triggers, the system identifies all tables/partitions that have changed enough to need new statistics. It then launches multiple background queries to gather that data. If several of these queries are complex or run on large tables, they may all hit your Resource Group's CPU limit simultaneously, causing a burst of error logs. * The Silence: Once the scheduler has finished attempting all the "unhealthy" tasks for that cycle, it stops and waits for the next 10-minute interval. 2. Does the silence mean it succeeded or gave up? It likely gave up for now, but it hasn't "abandoned" the table. * If the tasks failed, the statistics for those tables remain "unhealthy." * In the next 10-minute cycle, the scheduler will see that the statistics are still outdated and will try again. * If the errors stopped permanently, it might be because your cluster entered a period with less load (allowing the query to finish under the 120s limit) or the automatic collection window (
statistic_auto_analyze_start_time
) closed for the day. 3. How to verify the current status Don't guess—check the actual status of the background jobs using this command:
Copy code
sql
-- This shows the status of background/automatic collection tasks
SHOW ANALYZE STATUS;
Look for: * Status: If you see 
FAILED
, check the 
Reason
column. It will likely confirm the "exceed big query cpu limit" error. * EndTime: This will tell you when the last attempt was made. * Columns: You might notice that different columns or tables are failing, which explains the "cluster" of logs. 4. Recommendation If you see these errors repeating every few hours or days, the background job is stuck in a failure loop. You should: 1. Increase the limit: As mentioned previously, increase the 
big_query_cpu_second_limit
for the resource group used by the statistics user (usually the group associated with the 
root
user or the default group). 2. Switch to Sampling: For very large tables, force a sampled analysis so it doesn't need as much CPU:
Copy code
sql
    CREATE ANALYZE TABLE <your_table> PROPERTIES("statistic_sample_collect_rows" = "1000000");
Summary: The system didn't give up; it's waiting for its next scheduled window to try again. If you don't adjust the resource limits or the table's collection strategy, the errors will likely return. References • using_starrocks/Cost_based_optimizer.md
r
@Rocky the table size is 2039215588 rows
r
With a table of 2,039,215,588 rows (roughly 2 billion), a full scan to gather statistics is a very resource-intensive operation. The error occurs because StarRocks’ background statistics collector is executing a query (like 
SELECT COUNT(*)
, 
NDV()
, etc.) that is consuming more than 120 seconds of total CPU time on a single Backend (BE) node. This threshold is enforced by your Resource Group settings. Here is why this is happening and how to resolve it for a table of this scale: 1. Why 2 Billion Rows Hits the Limit Even though StarRocks is fast, calculating exact statistics for 2 billion rows requires scanning every column being analyzed. * CPU Limit Calculation: The 
big_query_cpu_second_limit
of 120s is the cumulative CPU time across all threads on a single BE for that specific query. If a query uses 16 threads for 8 seconds, it has already consumed 128 "CPU seconds." * Large Table Strategy: By default, StarRocks tries to perform a Full Collection if the partition size is under 100GB (
statistic_max_full_collect_data_size
). For 2 billion rows, your table might be right on the edge or slightly under this limit in terms of raw bytes, but the processing time is exceeding the 120s safety cap. 2. Solution A: Switch to Sampled Statistics (Recommended) For a 2-billion-row table, Sampled Collection is the standard practice. It provides ~99% accuracy for the optimizer while using a fraction of the resources. You can force the automatic scheduler to use sampling for this specific table by running:
Copy code
sql
CREATE ANALYZE TABLE <your_table_name> PROPERTIES (
    "statistic_sample_collect_rows" = "1000000"
);
* This tells StarRocks: "Whenever you analyze this table automatically, only sample 1 million rows instead of scanning all 2 billion." * This will finish in seconds and stop the CPU timeout errors. 3. Solution B: Increase the Resource Group Limit If you absolutely require Full (Exact) Statistics, you must increase the CPU limit for the resource group that the background tasks use. 1. Identify the Resource Group: Background statistics usually run under the 
root
user or the 
default_wg
group. Check your groups:
Copy code
sql
    SHOW RESOURCE GROUPS ALL;
Look for the group where 
big_query_cpu_second_limit
is 
120
. 2. Increase the Limit: Increase it to 600 seconds (10 minutes) or set it to 
0
(unlimited):
Copy code
sql
    ALTER RESOURCE GROUP <group_name> WITH (
        'big_query_cpu_second_limit' = '600'
    );
4. Solution C: Global Configuration Tuning If you have many such large tables, you can adjust the FE global configurations to be more aggressive about sampling: * Reduce the Full Collection Threshold: If you want the system to automatically switch to sampling for smaller data sizes (default is 100GB), lower this value:
Copy code
sql
    -- Example: Switch to sampling if partition size exceeds 20GB
    ADMIN SET CONFIG ("statistic_max_full_collect_data_size" = "21474836480");
How to check if it's fixed? After applying one of the solutions above, you can wait for the next 10-minute cycle or manually trigger a check:
Copy code
sql
-- Check the status of the background jobs
SHOW ANALYZE STATUS LIKE '%your_table_name%';
If the Status shows 
SUCCESS
, the optimizer now has the metadata it needs to generate efficient query plans for your 2-billion-row table. References • faq/resource_isolation_faq.md • administration/management/monitor_manage_big_queries.md • administration/management/monitoring/metrics.md • administration/management/resource_management/resource_group.md
r
@Rocky how can i see the current state of these settings? also how can i see the table size in GB?
r
To see the current state of your cluster configurations and the size of your tables, you can use the following commands: 1. View Statistics & FE Configurations To see the current values for the statistics collection settings (like the 100GB full collection threshold), use the 
ADMIN SHOW CONFIG
command:
Copy code
sql
-- See all configurations related to statistics
ADMIN SHOW CONFIG LIKE '%statistic%';
Key settings to look for in the output: * 
statistic_max_full_collect_data_size
: The 100GB limit (in bytes). If a table is larger than this, StarRocks switches to sampling. * 
statistic_collect_interval_sec
: How often the background thread checks for "unhealthy" tables (default is 600s/10m). 2. View Resource Group Limits (The CPU Limit) Since your error was specifically about a CPU limit, you should check your Resource Groups:
Copy code
sql
SHOW RESOURCE GROUPS ALL;
Look for the 
big_query_cpu_second_limit
column. This is the 120s limit that triggered your error. 3. View Table Size in GB There are two ways to see how much space your table is occupying: Option A: Human-Readable (Fastest) The 
SHOW DATA
command automatically converts bytes into MB, GB, or TB for you:
Copy code
sql
-- Show size of all tables in the current database
SHOW DATA;

-- Show size of a specific table
SHOW DATA FROM <database_name>.<table_name>;
Note: This includes all replicas. If you have a replication factor of 3, the "DataSize" will be 3x the actual unique data. Option B: Using Information Schema (For precise calculations) If you want to calculate the size yourself or use it in a script, query the 
information_schema.tables
table:
Copy code
sql
SELECT 
    table_name, 
    ROUND(data_length / 1024 / 1024 / 1024, 2) AS data_size_gb,
    table_rows
FROM information_schema.tables 
WHERE table_schema = '<your_database_name>'
  AND table_name = '<your_table_name>';
Summary of your situation With 2 billion rows, if your table size is approaching or exceeding 100GB, the statistics job is likely attempting a "Full Scan" which is timing out at the 120s CPU limit. Recommendation: If 
SHOW DATA
shows your table is large, run this to force it into sampling mode and avoid the error:
Copy code
sql
CREATE ANALYZE TABLE <table_name> PROPERTIES("statistic_sample_collect_rows" = "1000000");
References • administration/management/FE_configuration.md • sql-reference/sql-statements/Database/SHOW_DATA.md • faq/Sql_faq.md • administration/management/resource_management/resource_group.md • faq/Others.md ``` ## References - [faq/Sql_faq.md](https://docs.starrocks.io/docs/faq/Sql_faq) - [faq/Others.md](https://docs.starrocks.io/docs/faq/Others) - [knowledge/admin_and_operation/shared_data_compaction.md](https://docs.starrocks.io/docs/knowledge/admin_and_operation/shared_data_compaction)
r
@Rocky the table failing to collect statistics is 997GB
r
A table size of 997 GB (nearly 1 TB) with 2 billion rows explains exactly why you are hitting that 120-second CPU limit, even though StarRocks has a default "sampling" threshold of 100 GB. Why it's still failing (The "Predicate Column" Trap) By default, StarRocks switches from a Full Scan to Sampling if a table exceeds 100 GB (
statistic_max_full_collect_data_size
). However, there is a specialized feature enabled by default that is likely causing your error: 1. Full Collection for Predicate Columns: Even when a table is huge (like your 997 GB table), StarRocks wants to be very accurate for columns you actually use in 
WHERE
, 
JOIN
, or 
GROUP BY
clauses. 2. The Trigger: When the system hits the 100 GB threshold, it checks the "Predicate Columns" for that table. If it finds them, it ignores the sampling rule for those specific columns and tries to perform a Full Scan on just those columns. 3. The Failure: Scanning 2 billion rows for even just 2 or 3 columns across a 1 TB table can easily consume more than 120 seconds of CPU time, triggering the "Big Query" protection. ────────── How to Fix This for a 1 TB Table You have three main paths to stop these errors and get your statistics healthy: Path A: Disable "Full Scan for Predicates" (Recommended for Large Clusters) If you want the system to stop trying to full-scan these columns and just use sampling for everything on large tables, turn off this behavior globally:
Copy code
sql
ADMIN SET CONFIG ("statistic_auto_collect_use_full_predicate_column_for_sample" = "false");
Effect: This forces StarRocks to use pure sampling (much faster) for any table over 100 GB. Path B: Force Sampling for This Specific Table If you only want to change the behavior for this one specific 997 GB table:
Copy code
sql
-- This tells the auto-scheduler: "Always sample 2 million rows, never do a full scan."
CREATE ANALYZE TABLE <your_table_name> PROPERTIES (
    "statistic_sample_collect_rows" = "2000000"
);
Path C: Increase the Resource Limit If your cluster has the hardware to handle it and you want the most accurate stats possible, increase the CPU limit to allow the scan to finish:
Copy code
sql
-- Increase limit to 15 minutes (900s)
ALTER RESOURCE GROUP <your_resource_group> WITH (
    'big_query_cpu_second_limit' = '900'
);
────────── Useful Commands to Audit Your Table To see which columns are being treated as "Predicate Columns" (and thus causing the full scan), you can query this view:
Copy code
sql
SELECT * FROM information_schema.column_stats_usage 
WHERE table_name = '<your_table_name>';
To see the exact status of the last attempt:
Copy code
sql
SHOW ANALYZE STATUS LIKE '%your_table_name%'\G
Summary: Your 997 GB table is triggering a "smart" full-scan of specific columns. Because the table is so large, that "smart" scan is hitting your safety limits. Using Path B (creating a specific analyze task with sampling) is the most surgical and safest fix. References • using_starrocks/Cost_based_optimizer.md
r
@Rocky generally speaking, this issue aside it seems like there are a lot of configuration options here. how can we best figure out which of these we should tune for our speciifc cluster? we are running version 4.0.8 currently in shared-data mode. our biggest table is 997 GB, but we have others between 200 and 400 GB in total. our queries are generally not super complex, but we are doing a lot of updates
r
In a Shared-Data (Compute-Storage Separation) cluster running at this scale (1 TB table) with high update frequency, your performance and stability will depend almost entirely on how well the system manages Compaction and Primary Key Indexes. Since you are running version 4.0.x (which aligns with the latest v3.x architectural advancements), here is a curated guide on the "High Leverage" settings you should audit for your specific workload. 1. The Most Important: Compaction Tuning In shared-data mode, every update creates a new version of data in S3/Object Storage. If these aren't merged (compacted) quickly, your query performance will drop, and your storage costs will spike. * 
compact_threads
(CN/BE setting): * What it does: Controls how many threads on each Compute Node are dedicated to merging data. * Recommendation: Set this to 25% of your CPU cores. If you have 32-core nodes, set this to 8. * Command: 
UPDATE information_schema.be_configs SET VALUE = 8 WHERE name = "compact_threads";
* 
max_cumulative_compaction_num_singleton_deltas
(CN/BE setting): * What it does: Limits how many small files are merged in one go. * Recommendation: Set to 100 (default is often 500 or 1000). This makes compaction tasks finish much faster, which is better for high-frequency updates. * 
lake_compaction_max_tasks
(FE setting): * What it does: Total concurrent compaction tasks across the whole cluster. * Recommendation: Keep at 
-1
(auto-adaptive). If you see "Compaction Score" rising in Grafana, you may need to increase your CN node count. ────────── 2. Primary Key Performance (For Updates) For a 997 GB table, your Primary Key index is likely too large to fit entirely in memory. * 
enable_persistent_index
(Table property): * Why: For a 1 TB table, keeping the PK index in memory can cause OOM (Out of Memory) crashes. * Recommendation: Ensure this is 
true
. Since you are on a very new version, check if you can use 
persistent_index_type = "CLOUD_NATIVE"
, which stores the index on object storage for even better scalability. * 
transaction_publish_version_worker_count
(CN/BE setting): * Why: In shared-data mode, this thread pool handles the "commit" of updates. * Recommendation: If you see high latency during 
COMMIT
of your load jobs, increase this value to 16 or 32. ────────── 3. Query Efficiency (For "Not Super Complex" Queries) If your queries are simple but frequent, you should lean into StarRocks' caching layers to avoid hitting S3. * `query_cache_type`: * Recommendation: Set to 
1
(or 
DEMAND
). This caches the results of sub-expressions and is highly effective for simple queries on large datasets. * Data Cache (Disk Cache): * Compute nodes in shared-data mode use local NVMe/SSD to cache data from S3. * Audit: Check your 
storage_root
in 
be.conf
. Ensure it has enough space (e.g., 500GB+ per node) to cache the "hot" parts of your 1TB table. ────────── 4. How to monitor if your tuning is working Instead of guessing which setting to change, run these two diagnostic queries: 1. Check for Compaction Backlog:
Copy code
sql
    -- If MaxCS (Max Compaction Score) is consistently > 100,
    -- you need more 'compact_threads' or more CN nodes.
    SELECT DATABASE, TABLE, PARTITION, MaxCS, AvgCS
    FROM information_schema.partitions_meta
    ORDER BY MaxCS DESC LIMIT 10;
2. Check Memory Usage for PK Tables:
Copy code
sql
    -- See if the 'update' (PK index) memory usage is spiking
    -- You can view this via the BE web UI: http://<be_ip>:<be_http_port>/mem_tracker?type=update
Summary Recommendation for Your 1TB Table: 1. Switch to Sampled Stats (as discussed before) to stop the CPU timeout errors. 2. Increase 
compact_threads
 to 25% of your CPU to handle the update volume.
3. Ensure 
enable_persistent_index
 is on for the 1TB table to save memory. 4. Verify Tablet Count: For a 1TB table, you should have roughly 100-150 buckets (tablets). If you only have 10-20, a few threads are doing too much work; if you have 1000+, you have too many small files. References • best_practices/primarykey_table.md • table_design/table_types/primary_key_table.md
r
@Rocky how can i see the current settings for all of these
r
To see the current settings for your Shared-Data cluster (v4.0.8), you can use the following categories of commands. Since you are in shared-data mode, remember that your "BE" settings are actually applied to your Compute Nodes (CN). 1. View Global Backend/CN Configs (Compaction & Threads) To check the 
compact_threads
or 
max_cumulative_compaction_num_singleton_deltas
across all nodes:
Copy code
sql
SELECT * FROM information_schema.be_configs 
WHERE NAME IN ('compact_threads', 'max_cumulative_compaction_num_singleton_deltas', 'transaction_publish_version_worker_count');
2. View Table-Specific Settings (Persistent Index) To see if your 997 GB table has 
enable_persistent_index
or 
persistent_index_type
set correctly:
Copy code
sql
SHOW CREATE TABLE <your_table_name>;
Look for these in the 
PROPERTIES
section at the bottom of the output. 3. View Global Frontend Configs (Compaction Limits & Stats) To check the overall cluster compaction limit (
lake_compaction_max_tasks
) or the statistics thresholds:
Copy code
sql
ADMIN SHOW FRONTEND CONFIG LIKE '%lake_compaction%';
ADMIN SHOW FRONTEND CONFIG LIKE '%statistic%';
4. View Session/System Variables (Query Cache) To see if 
query_cache
or the 
datacache
(Disk Cache) is enabled for your queries:
Copy code
sql
SHOW VARIABLES LIKE '%query_cache%';
SHOW VARIABLES LIKE '%enable_scan_datacache%';
5. View Real-time Compaction Health Since you mentioned high update volume, check your "Compaction Score" to see if the system is keeping up. If 
MaxCS
is consistently above 100, your settings need tuning:
Copy code
sql
SELECT 
    DB_NAME, 
    TABLE_NAME, 
    PARTITION_NAME, 
    AVG_CS, 
    MAX_CS 
FROM information_schema.partitions_meta 
ORDER BY MAX_CS DESC 
LIMIT 10;
6. View Data Cache (S3-to-Local Cache) Path & Size To see where your CN nodes are caching S3 data and how much space they are allowed to use:
Copy code
sql
SELECT * FROM information_schema.be_configs 
WHERE NAME LIKE '%storage_root_path%' 
   OR NAME LIKE '%datacache_disk_size%';
Summary Cheat Sheet What you want to see | Command CN Threads/Buffers | 
SELECT * FROM information_schema.be_configs
FE Global Policy | 
ADMIN SHOW FRONTEND CONFIG
Table Properties | 
SHOW CREATE TABLE <name>
Query Behavior | 
SHOW VARIABLES
Compaction Health | 
SELECT * FROM information_schema.partitions_meta
References • administration/management/BE_configuration.md • sql-reference/information_schema/be_configs.md • administration/management/compaction.md • administration/management/FE_configuration.md • knowledge/admin_and_operation/shared_data_compaction.md • using_starrocks/caching/query_cache.md • using_starrocks/caching/block_cache.md • sql-reference/System_variable.md • table_design/table_types/primary_key_table.md • best_practices/primarykey_table.md • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • table_design/StarRocks_table_design.md References • administration/management/compaction.md
r
@Rocky also the large table is large because of a varient type field. the index itself may not be too big - how can i check that size to determine if we should enable 
enable_persistent_index
r
You can check the specific storage breakdown of your table and the memory footprint of your index using two different methods. Since your table is 997 GB with 2 billion rows, it is highly likely that your index alone is consuming tens of gigabytes of RAM if it is not persisted. 1. Check the size of the 
VARIANT
column vs. others To confirm if the 
VARIANT
field is indeed the "space hog," use the 
column_compressed_size
function with the 
[_META_]
hint. This scans the metadata of the files rather than the data itself, so it is very fast even on a 1 TB table.
Copy code
sql
-- Replace 'your_variant_column' and 'your_table'
SELECT 
    sum(column_compressed_size(your_variant_column)) / 1024 / 1024 / 1024 AS variant_size_gb,
    sum(column_compressed_size(your_pk_column)) / 1024 / 1024 / 1024 AS pk_column_size_gb
FROM your_table [_META_];
_Note: The 
[_META_]
hint is required to access these metadata functions._ ────────── 2. Check the actual Memory Usage of the Index To see how much RAM the Primary Key index is currently consuming on your Compute Nodes (CN), you can check the memory tracker. This is the most accurate way to see the real-time impact. 1. Via SQL (Information Schema):
Copy code
sql
    SELECT * FROM information_schema.be_metrics
    WHERE NAME LIKE '%update_primary_index_bytes_total%';
2. Via Browser: Go to 
http://<CN_IP>:<CN_HTTP_PORT>/mem_tracker?type=update
. * Look for the "update" category. This tracks the primary key index, delete vectors, and related structures. ────────── 3. Calculating the "Should I?" for Persistent Index For 2 billion rows, even a small Primary Key (like a single 
BIGINT
) uses a lot of memory. Here is the math for an in-memory index: * Formula: 
(PK_Column_Size + 9 bytes overhead) _ 2,000,000,000 rows _ 1.5 (Hash table overhead)
* If PK is BIGINT (8 bytes): 
(8 + 9) _ 2bn _ 1.5
= 51 GB of RAM per table replica. * If PK is a String/VARCHAR (e.g., 20 bytes): 
(20 + 9) _ 2bn _ 1.5
= 87 GB of RAM. Recommendation for your 997 GB Table: Because you are in Shared-Data mode, you should absolutely enable the persistent index. In fact, on your version (v4.0.8), you should use the Cloud Native version:
Copy code
sql
ALTER TABLE <your_table_name> SET (
    "enable_persistent_index" = "true",
    "persistent_index_type" = "CLOUD_NATIVE"
);
Why this is better for you: 1. Memory Savings: It will drop your RAM usage from ~50-80 GB down to just a few GB (only the "hot" parts of the index are kept in RAM). 2. S3 Integration: 
CLOUD_NATIVE
stores the index on S3 and uses the local disk of your CN nodes as a cache. This is specifically designed for the Shared-Data architecture you are running. 3. Update Performance: Since you have "a lot of updates," a persistent index prevents the CN nodes from hitting OOM (Out of Memory) during heavy write bursts. Summary * Use the 
[_META_]
query to prove the 
VARIANT
field is the source of the 1 TB size. * Check 
mem_tracker
to see the current RAM cost of the index. * *Enable 
CLOUD_NATIVE
persistent index*—for a 2-billion-row table, the memory overhead of an in-memory index is almost always too high for a standard cluster. References • best_practices/primarykey_table.md • table_design/table_types/primary_key_table.md • using_starrocks/caching/block_cache.md
r
@Rocky i see this trying to change the config
Copy code
mysql> ADMIN SET CONFIG ("statistic_auto_collect_use_full_predicate_column_for_sample" = "false");
ERROR 1064 (HY000): Getting syntax error at line 1, column 10. Detail message: No viable statement for input 'ADMIN SET CONFIG'.
r
The error you're seeing is because the command requires the keyword 
FRONTEND
. In StarRocks, configuration changes are split between the Frontend (FE) and Backend (BE/CN), and the syntax reflects that. For the specific setting you are trying to change, use this syntax:
Copy code
sql
-- Use FRONTEND and ensure both the key and value are in quotes
ADMIN SET FRONTEND CONFIG ("statistic_auto_collect_use_full_predicate_column_for_sample" = "false");
Important Notes for this Configuration: 1. Persistence: Changes made via 
ADMIN SET FRONTEND CONFIG
are not persistent. If you restart your FE nodes, they will revert to the values in your 
fe.conf
file. Once you verify the setting works for your cluster, you should add it to the 
fe.conf
on all FE nodes:
Copy code
properties
    # Add to fe.conf
    statistic_auto_collect_use_full_predicate_column_for_sample = false
2. Verify the Change: You can confirm the setting was applied correctly by running:
Copy code
sql
    ADMIN SHOW FRONTEND CONFIG LIKE '%statistic_auto_collect_use_full_predicate_column_for_sample%';
Check the 
Value
column to see the new state and the 
IsMutable
column to ensure it is a setting that can be changed dynamically (most statistics settings are 
true
). Why this is helpful for your 1TB table: By setting this to 
false
, you are telling StarRocks: "When a large table needs sampled statistics, do not try to flip back to a full scan just because there are predicate columns." For your 997 GB table with 2 billion rows, a "full scan" for predicate columns can be extremely resource-intensive during high update volumes. Forcing the sampled strategy will keep your FE CPU usage stable and prevent the "Analyze timeout" or "Analyze error" issues often seen on massive tables. References • sql-reference/sql-statements/cluster-management/config_vars/ADMIN_SET_CONFIG.md • using_starrocks/Cost_based_optimizer.md • administration/management/FE_configuration.md References • using_starrocks/Cost_based_optimizer.md
r
@Rocky i set statistic_auto_collect_use_full_predicate_column_for_sample = false ; the next auto-analyze kicked off and its still running FULL
r
The reason it is still running a 
FULL
 analyze is likely because the global auto-collection policy is still set to favor full scans by default, or your table size hasn't crossed the hard "fallback" limit yet. The setting you changed (
statistic_auto_collect_use_full_predicate_column_for_sample
) only prevents a Sampled job from "upgrading" itself to a full scan for specific columns; it doesn't stop a job that was already scheduled as Full. To force the system to stop doing full scans on your 1TB table, follow these steps: 1. Disable Global Auto-Full-Collection By default, StarRocks tries to do a full collection for everything. Turn this off to force the background scheduler to use the 
SAMPLE
strategy for all periodic tasks:
Copy code
sql
ADMIN SET FRONTEND CONFIG ("enable_collect_full_statistic" = "false");
2. Verify the "Large Table" Threshold Even if the setting above is 
true
, StarRocks should theoretically skip a 997 GB table if it exceeds 
statistic_max_full_collect_data_size
. Check what yours is set to (default is 100GB):
Copy code
sql
ADMIN SHOW FRONTEND CONFIG LIKE 'statistic_max_full_collect_data_size';
If your table is 997 GB but the system thinks the relevant partitions are smaller than this threshold, it will still try a 
FULL
scan. Setting 
enable_collect_full_statistic = false
is the more reliable hammer. 3. Check for "Stuck" or Manually Created Jobs If you previously ran a 
CREATE ANALYZE
statement (not just a one-off 
ANALYZE TABLE
), that job might be hardcoded to 
FULL
.
Copy code
sql
-- See all persistent analyze jobs
SHOW ANALYZE JOB;

-- If you see a job for your table with "Type: FULL", drop it
DROP ANALYZE JOB <job_id>;
4. Inspect the "Running" Job While the job is running, check the 
Properties
and 
Type
columns to see exactly why it chose 
FULL
.
Copy code
sql
SHOW ANALYZE STATUS LIKE 'your_table_name' \G
Look for: * Type: Is it 
FULL
or 
SAMPLE
? * Reason: Does it say "Auto" or "Manual"? * Properties: Does it show a 
statistic_sample_collect_rows
value? 5. Kill the current FULL scan Since a full scan on a 1TB table with updates is likely causing high resource usage, you should kill the current task after changing the configs:
Copy code
sql
-- Get the ID from SHOW ANALYZE STATUS
KILL ANALYZE <job_id>;
6. Manually trigger a Sampled Analyze (to "reset" health) After killing the full scan, trigger a sampled one manually. This will update the statistics and "satisfy" the auto-analyzer so it doesn't kick off again immediately.
Copy code
sql
ANALYZE SAMPLE TABLE your_table_name;
Summary Recommendation To ensure your massive table stays healthy without OOMing your FEs: 1. 
ADMIN SET FRONTEND CONFIG ("enable_collect_full_statistic" = "false");
 2. 
ADMIN SET FRONTEND CONFIG ("statistic_auto_collect_use_full_predicate_column_for_sample" = "false");
 3. Kill any existing full analyze tasks. 4. If the 
VARIANT
(or JSON) data makes the stats inaccurate, rely on 
SAMPLE
 with a higher row count:
Copy code
sql
    ANALYZE SAMPLE TABLE your_table PROPERTIES("statistic_sample_collect_rows" = "10000000");
References • using_starrocks/Cost_based_optimizer.md • sql-reference/sql-statements/cbo_stats/SHOW_ANALYZE_STATUS.md • administration/management/FE_configuration.md • sql-reference/sql-statements/cbo_stats/KILL_ANALYZE.md • sql-reference/sql-statements/cbo_stats/DROP_ANALYZE_JOB.md References • sql-reference/data-types/semi_structured/VARIANT.md
r
@Rocky statistic_max_full_collect_data_size is set to 107374182400; and whats the down side to setting enable_collect_full_statistic to false? will it cause other issues?
r
Setting 
enable_collect_full_statistic = false
is a global switch. While it will stop the heavy full scans on your 1TB table, you should be aware of the trade-offs. 1. Why it was still running "FULL" despite the 100GB limit The configuration 
statistic_max_full_collect_data_size
(100GB) applies to the size of the specific partitions being collected, not the total table size. * Because StarRocks collects statistics incrementally, if you only updated 50GB of data across a few partitions, the analyzer sees "50GB < 100GB" and triggers a 
FULL
scan for those specific partitions. _ By setting 
enable_collect_full_statistic = false
, you are telling the system: _"I don't care how small the change is; never use the FULL strategy automatically."* 2. The Downsides of Disabling Full Statistics The main risk is Plan Instability, particularly for queries involving joins or complex filters. * Inaccurate Cardinality (NDV): Sampled statistics estimate the number of unique values (NDV). On a table with 2 billion rows, a sample might miss "long tail" values. If the CBO (Optimizer) thinks a column has 1 million unique values when it actually has 100 million, it might choose a Broadcast Join instead of a Shuffle Join, which can lead to Out-of-Memory (OOM) errors on your Compute Nodes. * Data Skew Blindness: Sampled collection is notorious for missing data skew. If 90% of your rows have the same value for a join key, a sample might not catch this. The optimizer will then distribute the work unevenly, causing one or two nodes to do all the work while the others sit idle. * Impact on Small Tables: This setting is global. Even your small "dimension" tables (e.g., 10MB lookup tables) will now use sampled statistics. While the impact is usually negligible for tiny tables, it technically reduces the "perfection" of the execution plans for your entire cluster. 3. Will it cause "Other Issues"? Beyond performance, there are two specific behavioral changes to watch for: * Stale Stats on Column "Long Tails": If your 
VARIANT
data has keys that only appear in 0.01% of rows, the 
SAMPLE
scan might miss them entirely. If you then run a query filtering for that rare key, the optimizer will have no stats for it and will use a default "guess," which can be inefficient. * Manual Intervention Required: If you notice a specific query starts performing poorly after this change, you will have to manually "fix" it by running a one-off full analyze:
Copy code
sql
    -- Manually force full stats for a specific critical table
    ANALYZE FULL TABLE critical_small_table;
Recommendation for your 1TB Variant Table For a table of your size (1TB, 2B rows) with high update volume, the downsides of 
FULL
scans (system instability, FE CPU spikes, IO saturation) far outweigh the downsides of 
SAMPLE
inaccuracies. To mitigate the downsides of sampling: 1. Increase Sample Size: Instead of the default, tell the system to look at more rows during its sample:
Copy code
sql
    ADMIN SET FRONTEND CONFIG ("statistic_sample_collect_rows" = "10000000");
    -- Defaults to 200k; 10M is much safer for 2B rows.
2. Monitor for "Bad Joins": If a query that used to take 1 second now takes 10 seconds, check the 
EXPLAIN
plan. If you see a 
NESTED LOOP JOIN
or a 
BROADCAST
join on a huge table, you know the sampled stats are misleading the optimizer. Summary * Is it safe? Yes, it is a standard practice for clusters with multi-terabyte tables. * Will it cause issues? It might cause a slight decrease in query plan "perfection," but it will significantly increase the stability of your cluster by preventing massive background IO/CPU spikes. References • using_starrocks/Cost_based_optimizer.md • administration/management/FE_configuration.md
r
@Rocky is there a hybrid option to just disable full for the one table?
r
Yes, there is a "hybrid" approach. Instead of changing the global configuration for the entire cluster, you can create a Custom Analyze Job specifically for that table. Starting from StarRocks versions 3.2.12 and 3.3.4, user-created analyze jobs automatically override the default system behavior for the specified table. The Hybrid Solution: Create a custom SAMPLE job By creating a persistent 
SAMPLE
job for your large table, you tell StarRocks: "Whenever this table needs an update, use these specific sampled settings instead of your default logic." Run this command:
Copy code
sql
CREATE ANALYZE SAMPLE TABLE your_table_name 
PROPERTIES (
    "statistic_sample_collect_rows" = "10000000",
    "statistic_auto_collect_ratio" = "0.8"
);
Why this works: 1. Overrides the System: Because you explicitly defined a 
SAMPLE
job for this table, the background analyzer will use your job definition instead of the system-generated "Auto Full" task. 2. Cluster-Wide Safety: Other small tables in your database will still benefit from the default 
FULL
statistics (since you didn't disable the global 
enable_collect_full_statistic
). 3. Scalable Sampling: I included 
statistic_sample_collect_rows = 10,000,000
. For a table with 2 billion rows, the default sample of 200,000 is often too small for accurate plans; 10 million is a much safer "sweet spot" for a 1TB table. Important Version Note: * If you are on 3.2.12+ or 3.3.4+: Just run the command above. It will work immediately. * If you are on an older version: Custom jobs cannot override the system jobs unless you set the global flag 
enable_collect_full_statistic = false
. In older versions, the system job always takes priority. How to verify it's working: After you create the job, you can monitor it with:
Copy code
sql
-- See your custom job configuration
SHOW ANALYZE JOB; 

-- See the actual history of when it runs
SHOW ANALYZE STATUS LIKE 'your_table_name%';
In 
SHOW ANALYZE STATUS
, the 
Type
column should now consistently show 
SAMPLE
 for this table, even if the updated partition size is small. References • using_starrocks/Cost_based_optimizer.md • sql-reference/sql-statements/cbo_stats/CREATE_ANALYZE.md • sql-reference/sql-statements/cbo_stats/SHOW_ANALYZE_JOB.md References • using_starrocks/Cost_based_optimizer.md • sql-reference/sql-statements/cbo_stats/DROP_ANALYZE.md • sql-reference/sql-statements/cbo_stats/CREATE_ANALYZE.md
r
@Rocky is it safe to go higher? what about 
100,000,000
rows?
r
Technically, yes, it is "safe" to set 
statistic_sample_collect_rows
to 100,000,000 rows. StarRocks is designed to handle large-scale data, and its internal limit for a single analyze query (
statistic_collect_max_row_count_per_query
) defaults to 5 billion rows. However, for a 2 billion-row table, 100 million rows is likely overkill. Here is the breakdown of why you might want to stop at a lower number (like 10M–20M) and the risks of going to 100M. 1. The "Diminishing Returns" of Sampling Statistically, accuracy doesn't improve linearly with sample size. * 1 million rows: Excellent for most distributions. * 10 million rows: The "gold standard" for massive tables (billions of rows). This is the default limit for StarRocks histograms. * 100 million rows: You are scanning 5% of your 1TB table just to get metadata. The accuracy gain over 10 million rows is usually less than 1%, but the resource cost (IO and CPU) is 10x higher. 2. Risks of 100M Sample Rows If you decide to go with 100 million, monitor these three areas: * FE Memory Pressure: While BEs do the heavy lifting (calculating NDV via sketches), the FE must aggregate the results for every column. If your table has 200+ columns and you sample 100M rows, the metadata object created in FE memory can become quite large. * Background IO Impact: Even with a sampled scan (which uses 
TABLESAMPLE
), the system still has to read 100 million records from disk. If this kicks off during your peak business hours, it could compete with your user queries for disk throughput. * Task Timeouts: If the BEs cannot finish scanning 100M rows within the analyze timeout period, the job will fail and retry, leading to a loop of wasted resources. 3. Recommended "Sweet Spot" For a 1TB table with 2B rows, I recommend a "step-up" approach: 1. Start with 10,000,000 (10M): This is usually more than enough for the CBO to choose correct join types and handle 
VARIANT
data.
Copy code
sql
    CREATE ANALYZE SAMPLE TABLE your_table PROPERTIES("statistic_sample_collect_rows" = "10000000");
2. Verify accuracy: Run a query that performs a join or high-cardinality group-by. Check the 
EXPLAIN
plan. If the 
output rows
estimate is within the same order of magnitude as the actual rows, the stats are fine. 3. Go to 100M only if: You have extremely skewed data or "needle in a haystack" keys in your 
VARIANT
column that the 10M sample is consistently missing. 4. How to monitor the impact If you do set it to 100 million, run these commands to ensure the cluster is healthy: * Check FE Stats Memory:
Copy code
sql
    SELECT inspect_memory('Statistics');
* Check Scan Progress:
Copy code
sql
    SHOW ANALYZE STATUS LIKE 'your_table%';
    -- Look at the 'StartTime' and 'EndTime' to see how long 100M rows takes.
Summary: 100M rows is safe and won't crash StarRocks, but 10M-20M is almost always sufficient for a 1TB table and much kinder to your system resources. References • administration/management/FE_configuration.md • administration/management/BE_configuration.md
r
@Rocky another error we see a lot is
Copy code
E20260416 09:24:43.503404 139753758385728 vacuum.cpp:625] Internal error: starlet err grpc.GetShard(shardId=26045) error: Deadline Exceeded
be/src/storage/lake/tablet_manager.cpp:634 value_or_err_L634
be/src/storage/lake/tablet_manager.cpp:631 value_or_err_L631
be/src/storage/lake/vacuum.cpp:460 collect_files_to_vacuum(tablet_mgr, root_dir, tablet_info, grace_timestamp, min_retain_version, vacuum_version_range.get(), &datafile_deleter, &metafile_deleter, &bundle_file_deleter, vacuumed_file_size, &tablet_vacuumed_version, extra_file_size, tablet_retain_info)
be/src/storage/lake/vacuum.cpp:606 vacuum_tablet_metadata(tablet_mgr, root_loc, tablet_infos, min_retain_version, grace_timestamp, request.enable_file_bundling(), &vacuumed_files, &vacuumed_file_size, &vacuumed_version, &extra_file_size, retain_versions)
r
The error you are seeing is specific to StarRocks Shared-Data (Cloud Native) mode. It indicates a communication timeout between the Compute Node (BE/CN) and the Frontend (FE). What is happening? 1. Starlet & GetShard: In shared-data mode, the BE/CN uses a component called "Starlet" to communicate with the "Star Manager" service on the FE. The 
GetShard
call is used to retrieve metadata (locations, versions) for a specific tablet (shard) from the FE. 2. Vacuum Process: The stack trace shows this is happening during 
vacuum_tablet_metadata
. This is the background process where the BE/CN identifies and deletes old, unused data files from your object storage (S3/OSS/etc.) to save costs. 3. Deadline Exceeded: The gRPC request from the BE to the FE took longer than the configured timeout (default is 5 seconds). Because you have a 1TB table with billions of rows, your metadata volume is likely very high, causing these metadata lookups to occasionally exceed the 5s window, especially if the FE is busy. How to Fix It You can resolve this by increasing the gRPC timeout on the FE side. 1. Increase the FE Timeout Add or update this parameter in your 
fe.conf
and restart the FE (or use 
ADMIN SET FRONTEND CONFIG
if it is mutable in your version):
Copy code
properties
# Increase from default 5s to 20s
starmgr_grpc_timeout_seconds = 20
2. Check FE Health Since the FE is responsible for managing all Lake metadata, if it is under high CPU pressure or experiencing long JVM Garbage Collection (GC) pauses, it will miss these 5s deadlines. * Check the FE 
sys.log
for "GC" or "Pause" messages. * Monitor FE CPU usage during your high-load periods. 3. Reduce Vacuum Parallelism (Optional) If your FE is being "hammered" by too many vacuum requests at once, you can slow down the orchestration in `fe.conf`:
Copy code
properties
# Default is 8. Reducing this can lower the burst load on FE metadata lookups.
lake_autovacuum_parallel_partitions = 4
Is this serious? If you ignore this error: * Storage Costs: The "Vacuum" process is failing. This means old data versions and "garbage" files in S3/object storage are not being deleted. Over time, your cloud storage bill will increase because the system cannot clean up after compactions. * No Data Loss: This does not cause data corruption or loss; it just prevents the system from cleaning up the "trash." * Query Performance: It shouldn't directly affect query speed, but if the metadata backlog grows too large, the FE might become even slower. Summary of Recommendation 1. Set 
starmgr_grpc_timeout_seconds = 20
in 
fe.conf
. 2. Monitor your object storage usage (e.g., S3 bucket size). If it stops growing rapidly after this change, the vacuum process is working again. References • administration/management/FE_configuration.md • faq/shared_data_faq.md • knowledge/admin_and_operation/shared_data_garbage_cleanup.md References • administration/management/BE_configuration.md • administration/management/FE_configuration.md
r
@Rocky hmm heres another issue, 
show compute nodes
shows this for all nodes:
Copy code
Status: Normal, DiskUsage: 3.1TB/3.1TB, MemUsage: 18.3GB/18.3GB
r
In StarRocks Shared-Data (Lake) mode, seeing "100% usage" for Disk and Memory on Compute Nodes (CN) is actually a common and often normal state, though it looks alarming. 1. DiskUsage: 3.1TB / 3.1TB (100% Full) For a Compute Node, "Disk Usage" is almost always the Data Cache (Starlet Cache). * How it works: CNs do not store permanent data; they only download blocks of data from S3/object storage to local disks to speed up queries. * The "Full" Status: By design, the Data Cache will attempt to fill up 100% of its allocated space (defined by 
starlet_star_cache_disk_size_percent
, which defaults to 80% of the disk). Once full, it uses an LRU (Least Recently Used) strategy to evict old data to make room for new data. * Is it a problem? No. A 100% full cache means your CN is effectively utilizing its local storage to minimize expensive remote reads. As long as the 
Status
is 
Normal
and queries aren't failing with "No space left on device," this is the intended behavior. 2. MemUsage: 18.3GB / 18.3GB (100% Full) StarRocks is aggressive with memory to ensure high performance. * Memory Limit: The 
mem_limit
parameter (defaulting to 90% of the machine/container's RAM) tells the process how much it can take. * TCMalloc: StarRocks uses a memory allocator (TCMalloc) that often holds onto memory once it is allocated from the OS, rather than giving it back immediately. This can cause the "Used" metric to stay near the limit even if queries aren't currently running. * Is it a problem? It becomes a problem only if you see "Memory Limit Exceeded" errors in your query results. If the status is 
Normal
and queries are finishing, the node is just keeping its buffers warm. 3. The Connection to your "Deadline Exceeded" Error While "100% Disk" is fine for the cache, the "Deadline Exceeded" error you saw earlier suggests the FE (Frontend) or CN is struggling to keep up with metadata lookups. * Shared-Data pressure: Because your nodes are "full" (heavily utilized), the background Vacuum and Compaction processes are working hard. * Recommendation: Ensure your FE has enough CPU/RAM, as it has to manage the metadata for all 3.1TB of cached data and the 1TB of raw data on S3. If the FE is hitting 100% CPU, it will cause those 
GetShard
gRPC timeouts. Summary Checklist: 1. Don't panic about the 3.1TB/3.1TB; that's just a "warm" cache. 2. Check FE Health: Run 
top
or check monitoring for the FE node. If FE CPU is high, the 
GetShard
timeouts will continue. 3. Apply the Timeout Fix: (As mentioned previously) Increase 
starmgr_grpc_timeout_seconds = 20
in 
fe.conf
to give the system more breathing room during heavy metadata operations. References • deployment/shared_data/feature-support-shared-data.md • administration/management/BE_configuration.md • faq/shared_data_faq.md References • administration/management/monitoring/alert.md • knowledge/admin_and_operation/shared_data_compaction.md
r
@Rocky im still having trouble getting this analyze to succeed. i tried: • setting 'big_query_cpu_second_limit' = '600' for the resource group • setting these params: ◦ statistic_auto_collect_use_full_predicate_column_for_sample = false ◦ statistic_sample_collect_rows = 10000000 ◦ starmgr_grpc_timeout_seconds = 20 • running with "statistic_sample_collect_rows" = "5000000" should i exclude the variant column in the analyze? is there any downside to that? also what else should i try?
r
It looks like you've already applied several of the standard tuning steps. At this point, the bottleneck is likely the combination of shared-data metadata pressure and the sheer overhead of scanning a VARIANT column on a 1TB table. Here is the strategy to get the 
ANALYZE
to succeed: 1. Should you exclude the 
VARIANT
column? Yes, absolutely. This is the most likely culprit for your failures. * The Reason: 
VARIANT
columns can store up to 16MB per row. Even with a 5M row sample, the BEs might be reading and processing gigabytes of semi-structured data just to calculate basic statistics. In a Shared-Data (Lake) environment, this causes massive IO from S3 and can overwhelm the BE memory/CPU. * The Downside: The Cost-Based Optimizer (CBO) will lack the NDV (Number of Distinct Values) and Data Size for that specific column. * Impact: If you frequently join on a field extracted from that variant or use it as a high-selectivity filter, the optimizer might choose a sub-optimal join type (e.g., a Broadcast Join instead of a Shuffle Join). * Workaround: For the most important fields inside your JSON/Variant, consider materializing them as separate columns (using a generated column or during ETL). You can then analyze those specific columns easily. How to exclude it: You must explicitly list the columns you want to analyze.
Copy code
sql
ANALYZE SAMPLE TABLE your_table (col1, col2, col3) 
PROPERTIES("statistic_sample_collect_rows" = "5000000");
────────── 2. Increase the Resource Group Limit Further A 
big_query_cpu_second_limit
of 600 is only 10 minutes of CPU time. For a 2-billion-row table in Lake mode, especially if the data isn't fully cached (remember your disks were 100% full, so this scan is likely triggering S3 reads), 10 minutes is very tight. * Try: 
1800
(30 mins) or 
3600
(1 hour). ────────── 3. Use ASYNC Mode Manual 
ANALYZE
is synchronous by default. If your client or the FE-BE connection drops, the job might fail. Use ASYNC to let the FE manage it in the background.
Copy code
sql
ANALYZE SAMPLE TABLE your_table 
WITH ASYNC MODE
PROPERTIES("statistic_sample_collect_rows" = "5000000");
You can then monitor it with:
Copy code
sql
SHOW ANALYZE STATUS LIKE 'your_table%';
-- Look at the 'Reason' column if it fails.
────────── 4. Check for "Scan Timeouts" Even if the resource group allows it, the underlying query might hit a session 
query_timeout
. Before running the 
ANALYZE
command, bump your session limits:
Copy code
sql
SET query_timeout = 3600; -- 1 hour
SET query_mem_limit = 17179869184; -- 16GB (or higher depending on your CN RAM)
────────── 5. Why it’s failing (The Shared-Data factor) Because your Compute Nodes (CNs) showed DiskUsage: 3.1TB/3.1TB, every row the 
ANALYZE
job reads from S3 that isn't already in the cache will evict an existing data block. * This "Cache Churn" is extremely heavy on the system. * If the FE is also busy with the 
vacuum
errors you mentioned earlier, the 
ANALYZE
job (which needs to talk to the FE for metadata) is hitting the "Perfect Storm." Summary Recommendation: 1. Run 
SET query_timeout = 3600;
2. Run 
ANALYZE SAMPLE TABLE your_table (list_all_cols_except_variant) WITH ASYNC MODE PROPERTIES("statistic_sample_collect_rows" = "5000000");
3. If that succeeds, you've found the bottleneck. If you truly need stats on the Variant, increase the sample rows slowly (e.g., to 10M) only for the other columns first. References • sql-reference/data-types/semi_structured/VARIANT.md • faq/Sql_faq.md • table_design/indexes/Bitmap_index.md • sql-reference/System_variable.md
r
@Rocky we never join on fields in the JSON/Variant, just filter on them (though always in addition to other non-variant filters)
r
Given that you never join on the fields within the 
VARIANT
(or 
JSON
) column and you always include non-variant filters (like dates or IDs) in your queries, you should definitely exclude the variant column from your 
ANALYZE
command. Is there a downside to excluding it? In your specific use case: No, there is virtually no downside. Here is why: 1. JOINs are the main driver for Stats: The Cost-Based Optimizer (CBO) uses statistics primarily to determine the "Join Order" and "Join Type" (e.g., whether to broadcast a small table or shuffle a large one). Since you aren't joining on the variant data, the CBO doesn't need to know the distribution (NDV) of those values. 2. Compound Filtering: When you filter on 
non_variant_col = 'X'
AND 
variant_field = 'Y'
, the CBO will use the high-quality statistics from the standard column to determine how many rows/tablets to scan. The filter on the variant is then pushed down to the storage engine. 3. Storage-Level Optimizations: StarRocks' performance on semi-structured data (like Flat JSON or subfield pruning) happens at the storage layer. These optimizations do not require the results of an 
ANALYZE TABLE
command to work; they rely on internal metadata created during data ingestion. What else should you try? If the 
ANALYZE
still fails even after excluding the variant column, your table is likely so large (1TB/2B rows) that even a sample scan is timing out due to the Shared-Data overhead you are seeing in your logs. Try these three specific adjustments: 1. Use the "Predicate Column" Strategy Instead of trying to analyze every column (except the variant), only analyze the columns you actually use in 
WHERE
and 
JOIN
clauses. You can find these by running:
Copy code
sql
SELECT * FROM _statistics_.predicate_columns;
Then, only run 
ANALYZE
on those specific columns:
Copy code
sql
ANALYZE SAMPLE TABLE your_table (col_a, col_b) 
PROPERTIES("statistic_sample_collect_rows" = "1000000");
2. Lower the Sample Size (Temporarily) A sample of 5M or 10M rows on a 2B row table is fine for most CBO decisions. If it keeps failing, drop the sample to 1,000,000 just to get some statistics into the system. Partial statistics are significantly better for the optimizer than no statistics at all. 3. Check for "Deadlock" with Vacuum/Compaction The error logs you shared earlier showed the 
vacuum
process (garbage collection) was failing with 
Deadline Exceeded
. If your system is currently trying to "Vacuum" 1TB of data while you are also trying to "Analyze" it, they are competing for the same FE metadata locks. * Check Vacuum Status: 
SHOW TABLET STORAGE
(or check your object storage bucket size). * Workaround: Try running the 
ANALYZE
during a low-traffic window or after you've increased the 
starmgr_grpc_timeout_seconds
to 20s as discussed previously, which should allow the background tasks to clear out. Summary Recommendation Run this specific command to bypass the variant overhead and give the job the best chance to succeed:
Copy code
sql
-- List only your primary filter/join columns here
ANALYZE SAMPLE TABLE your_table (user_id, event_date, category_id) 
WITH ASYNC MODE
PROPERTIES(
    "statistic_sample_collect_rows" = "1000000"
);
Wait 5 minutes, then check status:
Copy code
sql
SHOW ANALYZE STATUS LIKE 'your_table%';
References • using_starrocks/Flat_json.md • sql-reference/data-types/semi_structured/JSON.md
r
@Rocky i ran a one-time analyze and its now worked. then i added an analyze job for this table to match that. the job shows pending and it looks like the table is still running FULL analyzes. why?
r
It sounds like you are seeing a conflict between the Custom Analyze Job you created and the Default Automatic Collector that StarRocks runs in the background. Here is why your job is "Pending" while "Full" analyzes are still appearing: 1. The Default "Auto-Collector" is faster than your Job StarRocks has a built-in background process that checks table health every 5 minutes (
statistic_collect_interval_sec
). * The Trigger: If the "health" of your table statistics falls below 0.8 (meaning >20% of the data has changed since the last analyze), the system automatically triggers an analyze task. * The "Full" Mystery: By default, StarRocks prefers 
FULL
statistics. Even for large tables, it may attempt a "Full" collection of just the Predicate Columns (the columns you actually use in 
WHERE
clauses). * The Conflict: Only one analyze task can run on a table at a time. If the Auto-Collector starts a task first, your custom job will stay in 
PENDING
until the auto-task finishes. 2. Why your table is hitting "FULL" instead of "SAMPLE" Even if you configured your job for 
SAMPLE
, the background system might be overriding it because: * Small Partition Logic: In Shared-Data mode, StarRocks often analyzes new or changed partitions. If the specific partitions that changed are small, it will use 
FULL
for those partitions regardless of total table size. * Predicate Column Promotion: There is a setting 
statistic_auto_collect_use_full_predicate_column_for_sample
(default 
true
). This tells StarRocks: "If I'm supposed to do a sample, but I only need stats for a few columns, just do a FULL scan of those specific columns instead." On a 1TB table, a FULL scan of 3 columns is often faster than a SAMPLE scan of 100 columns. 3. How to stop the "Full" Analyzes and prioritize your Job If the background "Full" analyzes are causing the same performance issues (timeouts/IO pressure) you had before, you should take control by disabling the default behavior: Step A: Disable the Default Full Collector Run this to stop the background system from automatically starting 
FULL
tasks:
Copy code
sql
ADMIN SET FRONTEND CONFIG ("enable_collect_full_statistic" = "false");
Step B: Lower the Auto-Collection Threshold If you want the system to be less "aggressive" about updating stats:
Copy code
sql
ADMIN SET FRONTEND CONFIG ("statistic_auto_collect_ratio" = "0.2"); 
-- Only auto-trigger when 80% of data has changed (vs default 20%)
Step C: Check why your job is "Pending" Run this command to see the specific reason:
Copy code
sql
SHOW ANALYZE JOB;
-- Check the 'Reason' column. It will often say "Already has a running task" 
-- or show the next scheduled run time.
Summary of what's happening: 1. Manual Analyze worked: This updated the stats "metadata." 2. Auto-Collector woke up: It saw the table was still "unhealthy" (perhaps because the sample size you used was too small to satisfy the 0.8 health ratio) and started its own Full task. 3. Your Job is waiting: It's in the queue (
PENDING
) waiting for the Auto-Collector to finish. Recommendation: Disable 
enable_collect_full_statistic
and let your custom 
SAMPLE
job be the primary way this table gets analyzed. This will stop the heavy background "Full" scans from competing with your nodes. References • using_starrocks/Cost_based_optimizer.md • sql-reference/sql-statements/cbo_stats/SHOW_ANALYZE_JOB.md • administration/management/FE_configuration.md • sql-reference/sql-statements/cbo_stats/CREATE_ANALYZE.md
Open in Slack
PreviousNext
Powered by