@Rocky In Spark we give the driver/executor a memoryOverheadFactor, which adds an off-heap buffer on top of the Pod memory limit to avoid cgroup OOMKills. Is there a recommended way to give a similar buffer to StarRocks FE/CN?

While StarRocks does not have a single parameter named exactly like Spark's

memoryOverheadFactor

, it achieves the same goal—providing a buffer to avoid cgroup OOMKills—through the relationship between the internal process limits and the Kubernetes Pod memory limit. In a containerized environment, StarRocks detects the Pod's memory limit as the total available physical memory and applies its own internal thresholds relative to that limit. 1. For CN (Compute Node) and BE Compute Nodes are C++ based and use a parameter called

mem_limit

to control the process memory upper bound. * Default Buffer: By default,

mem_limit

is set to 90% of the container's memory limit. This effectively provides a 10% overhead buffer for thread stacks, system overhead, and untracked memory. * Recommendation: If you encounter frequent cgroup OOMKills (Exit Code 137) despite the default, you can manually decrease this in

cn.conf

or `be.conf`:

bash
    mem_limit = 80%  # Increases buffer to 20%

* Soft Limit: StarRocks also has a "soft" limit (typically 90% of the

mem_limit

) where it begins to aggressively prune caches and trigger GC-like behavior to stay under the hard limit. 2. For FE (Frontend) FE is a JVM process. The metadata is stored in the JVM heap, but the process also requires off-heap memory for direct buffers, thread stacks, and Metaspace. * Recommended Ratio: For FE, the recommended "overhead" is significantly larger than for CN because of JVM native memory needs. * Machines/Pods < 32GB: Set

-Xmx

to 70% of the Pod limit (30% buffer). * Machines/Pods > 32GB: Set

-Xmx

to 80% of the Pod limit (20% buffer). * Configuration: Adjust these in

fe.conf

under the

JAVA_OPTS

variable:

bash
    JAVA_OPTS="-Xmx24g -Xms24g ..." # For a 32GB Pod

Summary of Ratios Component | Setting Name | Recommended Internal Limit | Effective Overhead Factor CN / BE |

mem_limit

| 90% (Default) | ~1.11x FE (<32G) |

Xmx

| 70% | ~1.42x FE (>32G) |

Xmx

| 80% | ~1.25x Best Practices for Kubernetes 1. Set Requests = Limits: To ensure the Pod gets a "Guaranteed" Quality of Service (QoS) and is less likely to be evicted by the kubelet during node-level memory pressure, set your memory requests equal to your limits. 2. Avoid Swap: Ensure swap is disabled on your Kubernetes nodes, as StarRocks is designed to manage its own memory and swap can lead to severe performance degradation. 3. MALLOC_ARENA_MAX: For FE processes, setting

MALLOC_ARENA_MAX=1

in the environment variables can help reduce virtual memory fragmentation and off-heap bloat. References • deployment/sr_operator.md • administration/management/BE_configuration.md • administration/management/monitoring/metrics.md • faq/resource_isolation_faq.md

@Rocky how can I check query optimize time with explain or verbose

To check the query optimization (planning) time in StarRocks, you have a few options depending on whether you want a detailed breakdown or a quick summary. 1. The

TRACE

Command (Best for Detailed Breakdown) Starting from v3.2, StarRocks provides a

TRACE

command specifically for debugging internal modules. This is the most direct way to see how much time the Optimizer is spending on different stages (like rewriting, cost estimation, etc.).

sql
TRACE TIMES OPTIMIZER <your_query>;

The output will show a list of events and the time cost for each. Look for the

Total

or

Optimizer

entry at the top of the trace to see the total planning duration. 2.

EXPLAIN ANALYZE

(Best for Execution + Planning Summary) While a regular

EXPLAIN

shows the plan without executing it,

EXPLAIN ANALYZE

executes the query and returns a text-based profile summary.

sql
EXPLAIN ANALYZE <your_query>;

In the output, search for the Summary section. It typically includes a field like: * Query Plan Time: The total time spent by the FE to parse, analyze, and optimize the query. 3. Query Profile (Best for Historical Queries) If you have already run a query and want to check its optimization time after the fact, you can use the Query Profile. 1. Find the Query ID:

sql
    SHOW PROFILELIST;

2. Analyze the Profile:

sql
    ANALYZE PROFILE FROM 'your_query_id';

The summary section at the top of the profile will list the Query Plan Time. 4. FE Web UI If you have access to the Frontend (FE) HTTP interface (default port

8030

), you can navigate to the Queries or Profiles tab. Clicking on a specific query ID will show the execution profile, which includes the planning/optimization duration in the summary statistics. Summary Table Method | Command | Use Case Trace |

TRACE TIMES OPTIMIZER <sql>

| Deep dive into why optimization is slow. Explain Analyze |

EXPLAIN ANALYZE <sql>

| Quick check of planning vs. execution time. Profile |

ANALYZE PROFILE FROM '<id>'

| Checking optimization time for past queries. Note: If you find that "Query Plan Time" is high (e.g., > 500ms), it is often due to a large number of partitions, complex JOINs, or large amounts of metadata being loaded for-rewrite metadata in-list filters. References • developers/trace-tools/query_trace_profile.md • best_practices/query_tuning/query_planning.md

@Rocky give me examples for analyze profile from query_id

To use

ANALYZE PROFILE

, you first need a valid

query_id

. You can find this ID by listing recent queries or by capturing the ID of the query you just ran. Step 1: Enable Profile Collection If you haven't already, ensure your session is capturing profiles:

sql
SET enable_profile = true;

Step 2: Get the Query ID You can find the ID of the last query you ran:

sql
SELECT last_query_id();
-- Example Output: 'a40456b2-8428-11ee-8d02-6a32f8c68848'

Or view a list of recent queries:

sql
SHOW PROFILELIST;

Step 3: Use ANALYZE PROFILE Once you have the ID, use the following commands to see the timing and optimization data. Example A: Basic Summary (Tree View) This is the most common usage. It shows the execution tree and the Summary section, which includes the Query Plan Time (optimization time).

sql
ANALYZE PROFILE FROM 'a40456b2-8428-11ee-8d02-6a32f8c68848';

Example B: Detailed Node Metrics If you identify a specific node (e.g., Node ID 0) that is taking a long time, you can specify that ID to see its detailed counters (rows processed, peak memory, etc.):

sql
-- Returns summary + detailed metrics for node 0
ANALYZE PROFILE FROM 'a40456b2-8428-11ee-8d02-6a32f8c68848', 0;

What to Look For (Query Optimization Time) In the output of

ANALYZE PROFILE

, look for the Summary block at the very top. It will look similar to this:

text
Summary
 - QueryId: a40456b2-8428-11ee-8d02-6a32f8c68848
 - State: Finished
 - Total: 150ms
 - Query Plan Time: 45ms       <-- THIS is your optimization/planning time
 - Execution Time: 105ms
 ...

Pro Tip:

get_query_profile

If you want the raw, full-text profile (which is very long but contains every single metric), you can use:

sql
SELECT get_query_profile('a40456b2-8428-11ee-8d02-6a32f8c68848')\G

(Note: Use

\G

in the MySQL client to make the long string readable.) References • best_practices/query_tuning/query_profile_text_based_analysis.md • sql-reference/sql-statements/cluster-management/plan_profile/ANALYZE_PROFILE.md • best_practices/query_tuning/query_profile_overview.md References • best_practices/query_tuning/query_profile_text_based_analysis.md • sql-reference/sql-statements/cluster-management/plan_profile/ANALYZE_PROFILE.md • best_practices/query_tuning/query_profile_overview.md

@Rocky explain me what specific case create Starrocks error: Memory of deafult_wg exceed limit. pipline backend : starrocks.cn.svc // mem usage has exceed the limit of query pool: BE: 10002. adjust mem_limit would be fix that issue?

This error occurs when the total memory used by all active queries on a specific Backend (BE) exceeds the memory limit allocated to the Query Pool. In StarRocks, memory is managed in a hierarchy. The "Query Pool" is a subset of the total BE process memory dedicated strictly to query execution (to prevent queries from starving essential system tasks like metadata management or heartbeats). Specific Cases that trigger this error: 1. High Concurrency: Many small or medium queries running simultaneously, where their combined memory usage hits the limit of the

default_wg

(which, by default, is allowed to use 100% of the query pool). 2. Memory-Intensive "Big" Queries: One or more queries performing large JOINs, aggregations, or window functions that require significant intermediate buffering. 3. Data Skew: If your data is not distributed evenly, one specific BE (in your case, ID

10002

) might be processing significantly more data than others, causing it to hit its local memory limit while other BEs remain idle. 4. Resource Group Competition: If you have created other resource groups, they share the total query pool. Even though

default_wg

is set to

100%

, it represents 100% of the available pool after other groups have taken their shares. ────────── Would adjusting

mem_limit

fix the issue? Yes, but with caveats. There are two different

mem_limit

settings to consider: 1. Global BE

mem_limit

(in

be.conf

) This is the total memory the StarRocks process is allowed to use from the OS/Pod. * Effect: Increasing this (e.g., from

90%

to

95%

or adding more RAM to the Pod) increases the "absolute size" of the query pool. * Risk: If you are already at 90% and you increase it, you risk a cgroup OOMKill (the Pod crashing) because there is less buffer for the OS and non-tracked C++ memory. 2. Resource Group

mem_limit

You can check the current limits for the

default_wg

by running:

sql
SHOW RESOURCE GROUPS;

If you see that the

mem_limit

is set to a low value (e.g.,

0.5

or

50%

), you can increase it:

sql
ALTER RESOURCE GROUP default_wg WITH ('mem_limit' = '1.0');

_Note: If

default_wg

is already at

1.0

(100%), you cannot increase it further within the resource group settings; you must increase the BE's total hardware memory or global

mem_limit

._ Recommended Action Plan 1. Check for Skew: Run

EXPLAIN ANALYZE <query>

for your heaviest queries and check if the

ScanRange

or

RowsReturned

is significantly higher on BE

10002

than others. 2. Increase Query Memory: If a single query is failing, you might need to increase the individual query limit:

sql
    SET exec_mem_limit = 17179869184; -- Set to 16GB for this session

3. Monitor Memory Trackers: Visit the BE's debug page to see exactly what is consuming the query pool:

bash
    curl -XGET http://<BE_IP>:<BE_HTTP_PORT>/mem_tracker

4. Enable Spilling: If your queries are large, consider enabling intermediate result spilling to disk to trade performance for memory stability:

sql
    SET enable_spill = true;

References • faq/resource_isolation_faq.md • administration/management/resource_management/resource_group.md • faq/Sql_faq.md • administration/management/BE_configuration.md References • faq/resource_isolation_faq.md • faq/Sql_faq.md • knowledge/usage/best-practice.md • best_practices/audit_log_resource_group.md

@Rocky with shared_datamode (using s3) we save meta in fe pvc , and data saved on s3 object storage. i want to have a look data files in s3. how can i find path of that? what is path rules? and there is some data starrocks/{some}/{uuid}/{db....}/{.....}/{.....}/meta/ what is this?

In StarRocks Shared-data Mode (Compute-Storage Decoupled), while the FE maintains the global catalog metadata in its local PVC (the

meta/

directory on the FE), it also stores tablet-specific metadata and the actual data segments on S3. 1. How to find the S3 path of your data You can find the exact S3 location for any database or table using the

SHOW PROC

command. * To see paths for all tables in a database:

sql
    SHOW PROC '/dbs/<database_name>';

* To see paths for a specific table: Look at the

StoragePath

column in the output. It will show a URL similar to:

<s3://my-bucket/starrocks/uuid/db_123/table_456/>

2. Path Rules & Structure Starting from StarRocks v3.1.4, data is organized by Partition ID. The general directory structure on S3 looks like this:

text
s3://<bucket>/<prefix>/<cluster_uuid>/<db_id>/<table_id>/
└── <partition_id>/
    ├── data/    <-- Contains .shared_group or .seg files (Actual Data)
    └── meta/    <-- Contains .tablet_meta files (Tablet Metadata)

* Cluster UUID: A unique identifier for your StarRocks cluster to prevent path collisions if multiple clusters use the same bucket. * DB/Table/Partition IDs: These are internal StarRocks IDs (integers). * data/ folder: This is where the actual segment files are stored. These files contain the row/column data in StarRocks' internal format. * meta/ folder: This contains the Tablet Metadata. 3. What is the

meta/

folder in S3? Even though your FE has a local PVC for metadata, StarRocks stores Tablet Metadata on S3 for the following reasons: 1. Persistence: In shared-data mode, the FE's local metadata primarily tracks the catalog (schemas, table names). The details of which specific S3 files belong to which tablet (and their versions) are stored in these

.tablet_meta

files on S3. 2. Shared Access: Since compute nodes (CNs) are stateless, they need to read these metadata files from S3 to understand which data segments they need to pull and process for a specific query. 3. Recovery: If the FE's local storage is lost, the cluster can potentially reconstruct or maintain data integrity because the mapping of tablets to data files is safely persisted in the object store. Summary of Path Components If you see a path like `starrocks/some_string/uuid/db_id/table_id/partition_id/meta/`: *

starrocks/

: Your root prefix defined in your Storage Volume. *

uuid/

: The cluster's unique ID. *

db_id

/

table_id

: The internal IDs of your database and table. *

meta/

: Files describing the tablet versions and segment list. References • faq/shared_data_faq.md • deployment/shared_data/s3.md • deployment/shared_data/feature-support-shared-data.md References • deployment/shared_data/s3.md • faq/shared_data_faq.md • deployment/shared_data/minio.md • deployment/shared_data/hdfs.md • deployment/shared_data/gcs.md • deployment/shared_data/azure.md