@Rocky Tell me relational effect between CN pod memory usage and pipieline_dop,(shared data mode)

The relationship between CN pod memory usage and

pipeline_dop

(Degree of Parallelism) in StarRocks shared-data mode is one of direct positive correlation. Increasing

pipeline_dop

increases the memory footprint of individual queries on a CN node. 1. Execution Memory Expansion

pipeline_dop

determines the number of parallel pipeline instances (execution threads) for a query fragment. * Operator State: Memory-intensive operators like Hash Joins, Aggregates, and Sorts maintain internal state (such as hash tables and sort buffers). While some structures are shared, many require per-thread buffers or partitioned structures that scale with the DOP. * Buffer Usage: Higher parallelism increases the number of intermediate buffers used to transfer data between pipeline drivers, leading to higher transient memory consumption. 2. Impact on Shared-Data Features In shared-data mode, CN nodes are responsible for scanning data from remote storage (S3/HDFS) and managing the Data Cache. * Scan Parallelism: A higher

pipeline_dop

increases the number of concurrent scan tasks. Each task requires memory for pre-fetching and buffering data blocks from remote storage before they are processed or written to the local cache. * Cache Metadata: While the Data Cache size itself is often a fixed quota (

storage_page_cache_limit

), the metadata required to manage high-concurrency access to these blocks can grow, contributing to the overall memory usage of the CN process. 3. Resource Contention and OOM * CN Pod Constraints: CN pods in Kubernetes often have strict memory limits. If

pipeline_dop

is set too high (e.g., manually forced to the number of CPU cores), a single query may exhaust the available memory, triggering the Linux OOM killer or StarRocks' internal memory protection. * Concurrency vs. Parallelism: In high-concurrency scenarios (many users querying at once), a high

pipeline_dop

per query multiplies the memory pressure. Reducing

pipeline_dop

(e.g., setting it to

1

or relying on StarRocks v3.x's adaptive DOP) allows more queries to run concurrently within the same CN pod memory envelope. 4. Mitigation: Spill to Disk From StarRocks v3.0.1 onwards, if high

pipeline_dop

causes memory usage to approach the limit, the system can spill intermediate results to disk (for Join/Agg/Sort). This reduces the immediate memory pressure on the CN pod at the cost of query performance. Summary Recommendation * Default (0): Use the default

pipeline_dop = 0

(adaptive), which allows StarRocks to balance parallelism based on the query plan and available resources. * High Concurrency: If your CN pods are hitting memory limits under load, decrease

pipeline_dop

to reduce the per-query memory overhead. * Large Queries: If you have large, infrequent ETL-like queries and sufficient memory, increase

pipeline_dop

to improve speed, but monitor the CN pod's memory usage closely. References • sql-reference/System_variable.md • knowledge/usage/best-practice.md • using_starrocks/caching/query_cache.md • administration/management/resource_management/Memory_management.md • faq/resource_isolation_faq.md

@Rocky tell me about exec_mode for session variable. there is no docs/

The session variable

exec_mode

is not a standard or documented user-facing variable in StarRocks. If you are seeing it in a query profile or a technical discussion, it likely refers to one of the following concepts: 1.

ExecMode

in Query Profiles In StarRocks query profiles (especially in version 3.0+), you will see a field named

ExecMode

. This is not a variable you set directly, but rather a status indicator that shows which execution engine was used for that query: * Pipeline: The query was executed using the modern Pipeline Engine (default in v3.x). * Vectorized: The query was executed using the older non-pipeline vectorized engine. * How to control it: You don't set `exec_mode`; instead, you use the session variable

enable_pipeline_engine

(

true

or

false

). 2. Similar "Mode" Variables You might be looking for one of these documented session variables that control execution behavior: *

spill_mode

: Controls intermediate result spilling to disk. * Values:

auto

(default),

force

. *

partial_update_mode

: Controls how partial updates are handled in Primary Key tables. * Values:

auto

(default),

column

. *

streaming_preaggregation_mode

: Controls the pre-aggregation strategy for

GROUP BY

queries. * Values:

auto

,

force_preaggregation

,

force_streaming

. *

sql_mode

: A standard MySQL-compatible variable to adjust SQL syntax behavior (e.g.,

PIPES_AS_CONCAT

). 3. FE Configuration

run_mode

Do not confuse session variables with the

run_mode

configuration in

fe.conf

. This is a global setting determined at deployment time: * `shared_data`: For StarRocks shared-data clusters. * `shared_nothing`: For traditional shared-nothing clusters. Summary If you saw

exec_mode

in a profile and want to change it, use

set enable_pipeline_engine = true/false;

. If you are looking to control how queries handle high memory load, look into

spill_mode

. If you found this variable in a specific context (like a specific error message or a script), please provide that context so I can give a more precise answer. References • sql-reference/information_schema/session_variables.md • sql-reference/information_schema/verbose_session_variables.md • sql-reference/System_variable.md

@Rocky give me some guide for set up pipeline_dop size for sql type & cpu size.

Setting

pipeline_dop

(Degree of Parallelism) correctly is a balance between individual query speed and overall cluster throughput. Since StarRocks v3.0+, the system uses an adaptive mechanism by default, but manual tuning is often necessary for specific workloads. 1. General Rule by CPU Size The baseline for

pipeline_dop

is relative to the physical CPU cores available on your CN (Compute Node) pod or BE (Backend). * Default (

0

): StarRocks automatically sets the DOP to half the number of physical CPU cores (up to a max of 64). * Manual Setting: If you choose to set it manually, the recommended starting point is: $$\text{pipeline\_dop} = \frac{\text{Physical CPU Cores}}{2}$$ * Small Pods (e.g., 4–8 cores): Keep

pipeline_dop

at 2 or 4. Setting it higher leads to heavy context switching and overhead without performance gains. * Large Pods (e.g., 32+ cores): You can scale up to 16 or 32 for single large queries, but monitor memory usage closely. ────────── 2. Guide by SQL Workload Type SQL Type | Recommended

pipeline_dop

| Reasoning High Concurrency / Point Lookups | 1 | When hundreds of users query simultaneously, setting DOP to 1 maximizes throughput by ensuring each query uses minimal CPU/memory, preventing resource exhaustion. Complex OLAP / Heavy Joins | Half of CPU cores (e.g., 8, 16) | Large analytical queries benefit from parallel scans and joins. High DOP speeds up data processing but increases memory footprint per query. ETL /

INSERT INTO ... SELECT

| Half of CPU cores | This variable also controls the parallelism of loading jobs. High DOP improves write throughput for large batch inserts. External Catalog Queries (S3, HDFS, Iceberg) | 2 or 4 | External scans often involve high I/O latency. A moderate DOP prevents the CN from overwhelming the remote storage metadata service or the Data Cache. ────────── 3. Shared-Data Mode (CN Pod) Considerations In shared-data mode, the CN pods are often more sensitive to memory pressure because they handle the Data Cache. * Memory vs. DOP: If your CN pods are frequently hitting OOM (Out of Memory), the first step is to reduce

pipeline_dop

. Each additional DOP unit replicates execution structures (like Hash Join buffers) which can consume several GBs for large datasets. * Data Cache Contention: A very high

pipeline_dop

triggers many concurrent read requests to the remote storage. If your local cache isn't warmed up, this can lead to "thread-pileup" where many threads wait for I/O, driving up CN CPU usage without actual processing progress. 4. How to Apply You can set this at the session level or globally. It is best practice to use session-level hints for specific heavy queries rather than a global change.

sql
-- For a specific heavy analytical query
SELECT /*+ SET_VAR(pipeline_dop = 16) */ count(*) FROM large_table JOIN ...;

-- For high-concurrency dashboards (set at connection pool level)
SET pipeline_dop = 1;

-- To return to automatic management
SET pipeline_dop = 0;

Recommendation: For most production environments, leave it at 0 (adaptive). Only manually override to 1 for high-concurrency "short" query apps, or to a specific high number for "long" batch ETL processes that have the cluster to themselves. References • sql-reference/System_variable.md • knowledge/usage/best-practice.md • faq/Sql_faq.md • best_practices/query_tuning/query_planning.md • faq/resource_isolation_faq.md • quick_start/shared-data.md