http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> is delete async?
# questions-and-troubleshooting
b
@Rocky is delete async?
r
In StarRocks, the answer to whether a 
DELETE
operation is asynchronous depends on whether you are referring to the execution of the SQL command or the physical removal of data. 1. SQL Execution (Synchronous) From the perspective of a user running a 
DELETE
statement, the operation is synchronous. * When you execute 
DELETE FROM table WHERE ...
, the client will block and wait for a response from the StarRocks cluster. * The transaction is committed during this time. If the operation succeeds, the deleted data will no longer be visible to subsequent queries immediately. * You can control the timeout for these operations using the session variable 
insert_timeout
. 2. Physical Data Removal (Asynchronous) While the SQL command is synchronous, the physical deletion of data from the disk is asynchronous. * Logical Delete: When a 
DELETE
is executed, StarRocks marks the data as deleted (using "delete predicates" in Duplicate/Unique/Aggregate tables or "DelVectors" in Primary Key tables). * Asynchronous Compaction: The actual rows are not immediately wiped from the storage files (Segments). They are physically removed later during the compaction process, which is a background system task that merges data versions. * Disk Space: Because the physical removal is deferred, you may not see an immediate reduction in disk usage right after a 
DELETE
command finishes. 3. Deleting Tables (
DROP TABLE
) If you are referring to deleting an entire table: * 
DROP TABLE
is asynchronous by default. It moves the table metadata to a "trash" folder (recycle bin) where it stays for a period (controlled by 
catalog_trash_expire_second
) before being permanently deleted. * 
DROP TABLE ... FORCE
can be used to bypass the recycle bin and trigger a more immediate (though still background-finalized) deletion. Summary Phase | Mode | Description SQL Command | Synchronous | The client waits for the "OK" and the data disappears from query results immediately. Physical Cleanup | Asynchronous | Background worker threads (compaction) eventually remove the data from disk. Table Drop | Asynchronous | Metadata is moved to trash; actual data is cleaned up by background workers later. Rocky's Tip: Frequent 
DELETE
operations are not recommended in StarRocks because they create many data versions and overhead for the compaction process. For large-scale data removal, consider using Partitions and dropping old partitions instead. References • using_starrocks/async_mv/Materialized_view.md • release_notes/release-2.3.md • using_starrocks/async_mv/feature-support-asynchronous-materialized-views.md • faq/Others.md • data_source/feature-support-data-lake-analytics.md
b
@Rocky how do i refresh tablet datasize
r
In StarRocks, the "Data Size" you see in commands like 
SHOW DATA
or 
SHOW TABLET
is not updated in real-time. Instead, it is reported periodically by the Backends (BE) to the Frontend (FE). If you need to refresh or see more accurate data size/statistics, here is how you can handle it: 1. Wait for the Reporting Interval By default, StarRocks updates these statistics every 5 minutes (300 seconds). This is controlled by the following configuration parameters: * FE: 
tablet_stat_update_interval_second
(Default: 300) * BE: 
tablet_stat_cache_update_interval_second
(Default: 300) If you have just deleted a large amount of data or finished a load, the physical disk size won't reflect the change until the next report cycle. 2. Refresh Query Optimizer Statistics (
ANALYZE
) If you are looking to refresh row counts and column-level statistics (used by the Cost-Based Optimizer), you can manually trigger a collection task:
Copy code
sql
ANALYZE TABLE <table_name>;
You can check the status of this task with:
Copy code
sql
SHOW ANALYZE STATUS;
Note: This refreshes metadata used for query planning, but it may not immediately update the physical 
DataSize
reported in 
SHOW DATA
. 3. Trigger Manual Compaction (For Deleted Data) If you have executed a 
DELETE
and the 
DataSize
is still high even after 5 minutes, it is likely because the data has only been "logically" deleted. To physically remove the data and reduce the disk size, you can trigger a manual compaction:
Copy code
sql
-- For the whole table
ALTER TABLE <table_name> COMPACT;

-- For a specific partition
ALTER TABLE <table_name> COMPACT PARTITION <partition_name>;
After compaction finishes, the next heartbeat (within 5 minutes) will report the reduced physical size to the FE. 4. Refreshing External Tables If you are using External Catalogs (e.g., Hive, Iceberg, Hudi) and the data has changed in the source system, you can force a metadata refresh immediately:
Copy code
sql
REFRESH EXTERNAL TABLE <table_name>;
Summary of Commands Goal | Command Refresh Row Counts/CBO Stats | 
ANALYZE TABLE <table_name>;
Check Current Stats Status | 
SHOW TABLE STATUS;
or 
SHOW DATA;
Physically Reclaim Disk Space | 
ALTER TABLE <table_name> COMPACT;
Refresh Data Lake Metadata | 
REFRESH EXTERNAL TABLE <table_name>;
Rocky's Tip: If you are developing and need to see size changes faster, you can temporarily reduce 
tablet_stat_update_interval_second
in your FE configuration, but avoid setting this too low in production as it increases the heartbeat overhead on the Leader FE.
b
@Rocky what determines the max memory in the datacachemetrics output that i'm seeing
r
The "max memory" value you see in the 
datacachemetrics
(often viewed via the BE API 
http://<BE_IP>:<BE_HTTP_PORT>/api/datacache/stat
) is primarily determined by a specific Backend (BE) configuration parameter. Here is the breakdown of what determines that limit: 1. 
datacache_mem_size
 This is the most direct control. It defines the maximum memory quota for the Data Cache (specifically the Page Cache component). * Location: Found in your 
be.conf
file or can be modified dynamically. * Format: It can be set as a physical limit (e.g., 
10G
, 
20GB
) or a percentage (e.g., 
10%
). * Dynamic Update: You can change it without a restart using:
Copy code
sql
    -- Example: Set to 20GB for all BEs
    UPDATE be_configs SET VALUE="20G" WHERE NAME="datacache_mem_size";
2. Default Values (If not set) If 
datacache_mem_size
is not explicitly configured (or set to 
0
), the system behavior depends on your version: * In many modern versions, if left at 
0
, the Data Cache might not allocate a dedicated memory quota for data pages, focusing instead on disk-based caching (Block Cache). * However, if you see a non-zero "max memory" or "quota" in your metrics and you haven't set it, the system may be using a default percentage of the total machine memory. 3. Block Cache Metadata While 
datacache_mem_size
controls the memory used for actual data blocks, the Block Cache (which primarily uses disk) also consumes memory for its metadata. * The memory used for metadata is reported as 
block_cache_meta_used_bytes
. * This is not a fixed "max" but grows based on how much disk cache you have configured (
datacache_disk_size
). Generally, for every 1TB of disk cache, you can expect a few GBs of memory to be consumed for metadata index. 4. Shared-Data Clusters (StarOS) If you are running in a shared-data (compute-storage separation) mode, the data cache is often managed by the "Starlet" (StarOS) agent. In these cases: * The memory limit is influenced by 
starlet_use_star_cache
. * In some versions, it may also respect 
starlet_star_cache_mem_size
(if available) or automatically scale based on the available memory on the CN/BE node. How to verify your current setting: To see exactly what the cluster thinks its "max" is and where it's coming from, run:
Copy code
sql
ADMIN SHOW FRONTEND CONFIG LIKE '%datacache_mem_size%';
-- or for BE specific config
ADMIN SHOW BACKEND CONFIG LIKE '%datacache_mem_size%';
Rocky's Tip: If you are seeing high memory usage but the "max memory" in 
datacachemetrics
is low, remember that StarRocks has other caches (like the Result Cache or Consistency Cache) and general query execution memory that are managed separately from the Data Cache. References • administration/management/BE_configuration.md • data_source/data_cache.md • knowledge/usage/best-practice.md • using_starrocks/caching/block_cache.md
b
@Rocky how do i limit the number of concurrent tasks that starrocks will run
r
To limit the number of concurrent tasks in StarRocks, you need to configure different parameters depending on the type of task (queries, loads, or background exports). StarRocks provides both global settings and fine-grained resource isolation. 1. Limiting Concurrent Queries (SQL) You can control how many queries run simultaneously to prevent system overload. * Global Limit (Query Queues): From version 2.5+, you can enable a global queue that holds queries once a limit is reached.
Copy code
sql
    -- Set the max concurrent queries per BE (0 = unlimited)
    SET GLOBAL query_queue_concurrency_limit = 50;

    -- You can also limit based on CPU/Memory usage
    SET GLOBAL query_queue_mem_used_pct_limit = 0.8; -- 80% memory
* Resource Group Limit (Recommended): You can create "Resource Groups" to limit concurrency for specific users or workloads.
Copy code
sql
    CREATE RESOURCE GROUP 'analytics_group'
    PROPERTIES (
        "concurrency_limit" = "10",
        "cpu_core_limit" = "5"
    );
    -- Bind a user to this group
    ALTER USER 'reporter' SET PROPERTIES ("resource_group" = "analytics_group");
2. Limiting Data Loading (Ingestion) Different load types have different concurrency controls: * Broker Load: Controlled by the FE configuration 
max_broker_load_job_concurrency
(Default: 5). This limits the number of active Broker Load jobs cluster-wide. * Routine Load (Kafka): * `max_routine_load_task_num_per_be`: Limits tasks per Backend (Default: 16). * `max_routine_load_job_num`: Total number of routine load jobs allowed in the FE (Default: 100). * `desired_concurrent_number`: Set this per job during 
CREATE ROUTINE LOAD
to limit its specific parallelism. * Transaction Limit: `max_running_txn_num_per_db`: Limits total concurrent transactions (Stream Load, Broker Load, etc.) per database (Default: 1000). 3. Limiting Export Tasks If you are using the 
EXPORT
command to dump data to S3/HDFS: * `export_running_job_num_limit`: Maximum concurrent export jobs (Default: 5). * `export_task_pool_size`: Maximum internal tasks in the thread pool for exports (Default: 5). 4. How to Apply These Changes Most of these are FE configurations. You can check and update them without restarting the cluster: To view current settings:
Copy code
sql
ADMIN SHOW FRONTEND CONFIG LIKE '%concurrency%';
ADMIN SHOW FRONTEND CONFIG LIKE '%limit%';
To update a setting (e.g., Broker Load):
Copy code
sql
ADMIN SET FRONTEND CONFIG ("max_broker_load_job_concurrency" = "10");
Summary Table Task Type | Key Parameter | Scope SQL Queries | 
query_queue_concurrency_limit
| Global/BE User/Group Queries | 
concurrency_limit
| Resource Group Broker Load | 
max_broker_load_job_concurrency
| Cluster Routine Load | 
max_routine_load_task_num_per_be
| BE Export Tasks | 
export_running_job_num_limit
| Cluster Rocky's Tip: If you see "Too many tasks" or "Too many transactions" errors, it's often better to check if your application is sending too many small batches. Increasing these limits too high can lead to high CPU usage and memory pressure! References • faq/loading/Routine_load_faq.md • administration/management/FE_configuration.md • loading/RoutineLoad.md • unloading/Export.md
b
@Rocky how do i limit the number of concurrent tasks created with submit task
r
To limit the number of concurrent tasks executed via the 
SUBMIT TASK
framework (asynchronous ETL tasks like 
INSERT
or 
CTAS
), you need to adjust specific Frontend (FE) configuration parameters. 1. The Primary Setting: 
task_runs_concurrency
 This is the global limit for the number of tasks that can run in parallel across the cluster. * Default Value: 
4
* Description: This caps the parallel execution of 
TaskRun
instances. When the number of running tasks reaches this limit, the 
TaskRunScheduler
will wait for a task to complete before starting a new one. 2. The Queue Setting: 
task_runs_queue_length
 If you submit more tasks than the concurrency limit allows, they enter a "pending" queue. * Default Value: 
500
* Description: This defines the maximum number of tasks allowed in the queue. If you exceed this number, new task submissions will be rejected or suspended. ────────── How to Change These Limits Since these are FE configuration items, you can modify them dynamically using the 
ADMIN SET FRONTEND CONFIG
command without restarting your cluster. To view your current settings:
Copy code
sql
ADMIN SHOW FRONTEND CONFIG LIKE '%task_runs%';
To increase or decrease the concurrency:
Copy code
sql
-- Example: Allow up to 10 concurrent tasks
ADMIN SET FRONTEND CONFIG ("task_runs_concurrency" = "10");

-- Example: Increase the pending queue length to 1000
ADMIN SET FRONTEND CONFIG ("task_runs_queue_length" = "1000");
Important Considerations * Resource Impact: Increasing 
task_runs_concurrency
allows more ETL jobs to run at once, which increases the CPU and Memory load on your Backends (BEs). Monitor your cluster performance if you raise this value significantly. * Version Note: The 
task_runs_concurrency
parameter was formally introduced as a mutable global config in version 3.2.0, though the task framework itself exists in earlier versions. * Monitoring: You can track the status of your tasks and see if they are being queued by querying the metadata:
Copy code
sql
    SELECT * FROM information_schema.task_runs WHERE state = 'PENDING';
Rocky's Tip: If you are trying to limit the concurrency of Materialized View refreshes, StarRocks also uses the Task framework for these in newer versions. However, for MVs, you might also want to look at 
partition_refresh_number
within the MV definition to control how many partitions refresh at once are processed at once. References • sql-reference/sql-statements/loading_unloading/ETL/SUBMIT_TASK.md • administration/management/FE_configuration.md References • sql-reference/sql-statements/loading_unloading/ETL/SUBMIT_TASK.md • administration/management/FE_configuration.md • integrations/airflow.md
b
@Rocky how much memory should i allocate to data cache
r
Allocating memory for the Data Cache depends on whether you are optimizing for raw speed (Page Cache) or storage capacity (Block Cache). In StarRocks, "Data Cache" is actually composed of two different layers that consume memory differently. 1. Memory for Page Cache (
datacache_mem_size
) The Page Cache stores decompressed data pages directly in RAM. This is the fastest way to access data. * Recommended Allocation: 10% to 20% of your total system memory. * When to increase it: If you have frequent queries on the same "hot" data and your Backend (BE) or Compute Node (CN) has significant free RAM. * When to decrease it: If you are running complex queries with large Joins or Shuffles that are hitting memory limits (OOM), as query execution memory is more critical. * Configuration:
Copy code
sql
    -- Set to 10% of total RAM
    UPDATE be_configs SET VALUE="10%" WHERE NAME="datacache_mem_size";
2. Memory for Block Cache (Metadata) The Block Cache stores data on disk (SSD/NVMe). However, it requires a portion of RAM to store the metadata/index of those blocks so it knows where they are on disk. * Rule of Thumb: Every 1 TB of disk cache consumes roughly 1 GB to 2 GB of RAM for metadata. * Impact: If you allocate a 10 TB disk cache (
datacache_disk_size
), ensure your node has at least 15–20 GB of "extra" RAM available to handle the index, otherwise, you may face stability issues or high "hidden" memory usage. * Monitoring: Look at the metric 
fslib_star_cache_meta_memory_size
in your monitoring dashboard to see exactly how much RAM the metadata is using. 3. The "Hidden" Cache: OS Page Cache StarRocks is designed to work alongside the Operating System. If you leave a significant portion of RAM unallocated (i.e., not assigned to 
datacache_mem_size
or query limits), the Linux kernel will automatically use that RAM to cache the most frequently accessed files from your 
datacache_disk_size
. * Rocky’s Recommendation: It is often better to allocate a modest 10% to Page Cache and let the OS Page Cache manage the rest of the free RAM. This provides a "best of both worlds" approach where the OS dynamically balances memory between query execution and data caching. Summary Checklist: Component | Medium | Recommendation | Config Parameter Page Cache | RAM | 10% - 20% of RAM | 
datacache_mem_size
Block Cache | Disk | 80% of Disk | 
datacache_disk_size
Metadata | RAM | ~1-2GB per 1TB of Disk | (Automatic) Pro Tip: If you are using Shared-Data (Cloud-Native) mode with CN nodes, you can afford to be more aggressive with cache memory (up to 40%) because those nodes don't have to manage local data storage (SSTables), leaving more room for caching. References • data_source/data_cache.md • using_starrocks/caching/block_cache.md • administration/management/BE_configuration.md • knowledge/usage/best-practice.md References • using_starrocks/caching/block_cache.md • data_source/data_cache.md
Open in Slack
PreviousNext
Powered by