http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> explain below section of starrocks ...
# questions-and-troubleshooting
c
@Rocky explain below section of starrocks query profile
Copy code
SegmentInit: 30s975ms [min=16s280ms, max=38s894ms]                                                                         |
|                          ColumnIteratorInit: 25s750ms [min=13s639ms, max=33s367ms]
r
These metrics are found in the Scan Operator (typically 
OLAP_SCAN_NODE
) and represent the time spent preparing the storage layer to read data. In your case, 30 seconds for 
SegmentInit
is very high, suggesting a significant bottleneck before actual data reading even begins. 1. SegmentInit (30s 975ms) 
SegmentInit
is the total time spent initializing the segments (the physical files that store data in StarRocks) for the scan. * What it does: It involves opening segment files, reading the Segment Footer (metadata at the end of the file), and applying initial filters like Zone Maps (min/max indexes), Bloom Filters, or Bitmap Indexes to determine which data blocks need to be read. * Analysis: Your profile shows an average of ~30.9s, with a maximum of ~38.8s. This indicates that the scan threads are spending nearly 40 seconds just "getting ready" to read data. 2. ColumnIteratorInit (25s 750ms) This is a sub-metric of 
SegmentInit
. * What it does: It specifically tracks the time spent creating Iterators for each column required by your query. To do this, StarRocks must load the metadata for each column (such as dictionary encoding maps, page indexes, and null bitmap info). * Analysis: In your profile, 
ColumnIteratorInit
accounts for roughly 83% (25.7s out of 30.9s) of the total 
SegmentInit
time. This means the bottleneck is specifically in initializing the column-level readers. ────────── Why are these values so high? When these metrics reach the 30-second range, it usually points to one of the following issues: 1. High Metadata Latency (Cloud/Remote Storage): * If you are using Shared Data Mode (Compute-Storage separation) or reading from external storage (HDFS/S3), the system might be experiencing high latency while fetching segment footers and column metadata over the network. _ _Solution:* Check network latency or consider using local caching (Data Cache) if on shared storage. 2. Too Many Small Files (Segments): * If the table has many small segments (caused by frequent small imports or lack of compaction), the overhead of opening thousands of files and reading their footers adds up. _ _Solution:* Check the 
SegmentsReadCount
metric. If it's in the thousands, you likely need to trigger manual compaction or optimize your ingestion (larger batches). 3. Wide Tables with Many Columns: _ If you are performing a 
SELECT _
on a table with hundreds or thousands of columns, the system must initialize an iterator for every single one. _ _Solution:* Only select the columns you actually need. 4. Disk I/O Contention: * The disks may be saturated, causing the small, random reads required for segment footers and column metadata to queue up. _ _Solution:* Check disk I/O wait and utilization on the Backend (BE) nodes. Summary Your query is stuck in the initialization phase. Most of the time is being spent loading column-level metadata (
ColumnIteratorInit
). You should check if you are selecting too many columns or if your table has a "small file" problem (too many segments). References • best_practices/query_tuning/query_profile_operator_metrics.md • faq/Sql_faq.md • unloading/Spark_connector.md
c
@Rocky how to check 
SegmentsReadCount
metric suggested above?
r
To check the 
SegmentsReadCount
 metric, you need to examine the Query Profile for your specific query. This metric shows how many segments were actually opened and read by the 
OLAP_SCAN
operator during execution. Here are the steps to find it: 1. Enable Profile Collection Before running your query, ensure that profile reporting is enabled for your session:
Copy code
sql
SET enable_profile = true;
2. Run your Query Execute the SQL statement you want to analyze. 3. Retrieve the Profile You can view the profile using one of two main methods: Method A: Using the FE Web UI (Recommended) 1. Open your browser and go to 
http://<FE_IP>:<FE_HTTP_PORT>
(default port is 
8030
). 2. Click on the Queries tab in the top navigation bar. 3. Find your query in the list and click on its Profile link. 4. In the visual or text profile, look for the 
OLAP_SCAN
 node. 5. Expand the node and look for the 
SegmentRead
 section under 
UniqueMetrics
. You will see 
SegmentsReadCount
 there. Method B: Using SQL Command If you are using a terminal, you can get the profile text directly: 1. Get the Query ID of your last query:
Copy code
sql
   SELECT last_query_id();
2. View the profile (replace 
<query_id>
with the ID from the previous step):
Copy code
sql
   SHOW PROFILE FOR QUERY "<query_id>";
3. Search the output text for the string 
SegmentsReadCount
. What to look for: In the profile output, it will look something like this:
Copy code
text
OLAP_SCAN (plan_node_id=0):
  ...
  - SegmentRead: 2.559us
    - RowsetsReadCount: 7
    - SegmentsReadCount: 3   <-- This is the metric
      - __MAX_OF_SegmentsReadCount: 2
      - __MIN_OF_SegmentsReadCount: 0
Why this metric matters: * High `SegmentsReadCount`: If this number is very high relative to the data size, it usually indicates that your data is fragmented into too many small files. This often happens due to frequent small imports or a lack of compaction. * Performance Impact: Reading many small segments increases I/O overhead and metadata management costs, which slows down queries. If you see this, you might need to trigger manual compaction or adjust your ingestion frequency. References • administration/management/monitoring/metrics.md • data_source/data_cache.md • best_practices/query_tuning/query_profile_operator_metrics.md • unloading/Spark_connector.md
c
@Rocky how to check number of small files per table?
r
To check the number of files (segments or rowsets) per table in StarRocks, you can use the 
information_schema.be_tablets
system view. In StarRocks, data is stored in tablets, and each tablet consists of one or more rowsets, which in turn contain segments (physical files on disk). The best way to identify tables with many "small files" is to look at the segment count and the average segment size. 1. Querying segment count per table You can join 
be_tablets
(which contains tablet-level file metrics) with 
partitions_meta
(to get table and database names).
Copy code
sql
SELECT 
    pm.DB_NAME, 
    pm.TABLE_NAME, 
    SUM(bt.NUM_SEGMENT) AS total_segments,
    SUM(bt.NUM_ROWSET) AS total_rowsets,
    ROUND(SUM(bt.DATA_SIZE) / 1024 / 1024, 2) AS total_size_mb,
    ROUND(SUM(bt.DATA_SIZE) / SUM(bt.NUM_SEGMENT) / 1024 / 1024, 2) AS avg_segment_size_mb
FROM information_schema.be_tablets bt
JOIN (SELECT DISTINCT DB_NAME, TABLE_NAME, PARTITION_ID FROM information_schema.partitions_meta) pm 
  ON bt.PARTITION_ID = pm.PARTITION_ID
GROUP BY pm.DB_NAME, pm.TABLE_NAME
ORDER BY total_segments DESC;
* 
NUM_SEGMENT
: The actual number of data files on disk for that tablet. * 
NUM_ROWSET
: The number of versions (ingestion batches). Many rowsets usually indicate a need for compaction. * 
avg_segment_size_mb
: If this value is very low (e.g., less than 10MB), it confirms you have many small files. 2. Using the Compaction Score StarRocks provides a Compaction Score (CS), which is a direct indicator of how many un-compacted "small files" or versions exist in a partition. A high score (e.g., > 100) indicates that the table has too many small files that need to be merged. You can check this directly in `information_schema.partitions_meta`:
Copy code
sql
SELECT 
    DB_NAME, 
    TABLE_NAME, 
    PARTITION_NAME, 
    MAX_CS AS max_compaction_score, 
    AVG_CS AS avg_compaction_score
FROM information_schema.partitions_meta
ORDER BY MAX_CS DESC;
3. Using 
SHOW PROC
(Manual Inspection) If you want to look at a specific table's partitions and their file counts:
Copy code
sql
-- Replace 'db_name' and 'table_name' with your actual names
SHOW PROC '/dbs/db_name/table_name/partitions';
Look for columns like 
AvgCS
, 
MaxCS
(Compaction Score), and 
DataSize
. Why do small files matter? In StarRocks, having an excessive number of small files (segments) per table can lead to: * High Metadata Overhead: Increased memory usage on FEs and BEs. * Query Latency: The system has to open and read many file footers during a scan. * I/O Pressure: Compaction tasks will be triggered frequently to merge these files. Solution: If you find too many small files, consider increasing your data ingestion batch size or adjusting the 
cumulative_compaction_threshold
in the BE configuration to merge files more aggressively. References • sql-reference/information_schema/partitions_meta.md • sql-reference/information_schema/partitions.md • sql-reference/sql-statements/table_bucket_part_index/SHOW_PARTITIONS.md
c
@Rocky
Copy code
sql
SELECT 
    pm.DB_NAME, 
    pm.TABLE_NAME, 
    SUM(bt.NUM_SEGMENT) AS total_segments,
    SUM(bt.NUM_ROWSET) AS total_rowsets,
    ROUND(SUM(bt.DATA_SIZE) / 1024 / 1024, 2) AS total_size_mb,
    ROUND(SUM(bt.DATA_SIZE) / SUM(bt.NUM_SEGMENT) / 1024 / 1024, 2) AS avg_segment_size_mb
FROM information_schema.be_tablets bt
JOIN (SELECT DISTINCT DB_NAME, TABLE_NAME, PARTITION_ID FROM information_schema.partitions_meta) pm 
  ON bt.PARTITION_ID = pm.PARTITION_ID
GROUP BY pm.DB_NAME, pm.TABLE_NAME
ORDER BY total_segments DESC;
this query doesn't finish even for 2 hrs
r
Hello! I'm Rocky. That query is likely hanging because it's performing a very expensive join between two massive metadata tables: 
information_schema.be_tablets
and 
information_schema.partitions_meta
. Why it's slow: 1. BE Probing: 
information_schema.be_tablets
is a "virtual" table. When you query it, the FrontEnd (FE) must request metadata from every Backend (BE) node in your cluster for every single tablet replica. If you have millions of tablets, this collection alone can take minutes or even timeout. 2. Expensive Join: Your query joins millions of tablet rows with 
partitions_meta
(which can also be huge) using 
PARTITION_ID
. This creates a massive join operation in the FE memory. 3. Redundant Data: 
be_tablets
returns 1 row per replica. If you have a replication factor of 3, 
SUM(DATA_SIZE)
will give you the total physical size (3x the logical data size). Optimized Query To speed this up, you should pre-aggregate the tablet data by 
TABLE_ID
before joining it with a smaller table like 
tables_config
to get the names. Joining on 
TABLE_ID
is significantly faster than 
PARTITION_ID
.
Copy code
sql
SELECT 
    t.TABLE_SCHEMA, 
    t.TABLE_NAME, 
    bt.total_segments,
    bt.total_rowsets,
    bt.total_size_mb,
    bt.avg_segment_size_mb
FROM (
    -- Step 1: Aggregate tablet metrics first to reduce row count
    SELECT 
        TABLE_ID, 
        SUM(NUM_SEGMENT) AS total_segments,
        SUM(NUM_ROWSET) AS total_rowsets,
        ROUND(SUM(DATA_SIZE) / 1024 / 1024, 2) AS total_size_mb,
        ROUND(SUM(DATA_SIZE) / NULLIF(SUM(NUM_SEGMENT), 0) / 1024 / 1024, 2) AS avg_segment_size_mb
    FROM information_schema.be_tablets
    GROUP BY TABLE_ID
) bt
-- Step 2: Join with tables_config (much smaller than partitions_meta)
JOIN information_schema.tables_config t ON bt.TABLE_ID = t.TABLE_ID
-- Recommended: Filter for a specific database if possible
-- WHERE t.TABLE_SCHEMA = 'your_database_name'
ORDER BY total_segments DESC;
Additional Tips: * Filter Early: If you only suspect a few databases, add a 
WHERE
clause to 
tables_config
. Even though 
be_tablets
doesn't always push down filters perfectly, reducing the join set helps immensely. * Check Tablet Count: If this query still takes too long, you might have an extreme number of tablets (too many small partitions/buckets). Check your total tablet count with 
SHOW PROC '/statistic'
. * Physical vs Logical: Remember that 
SUM(bt.DATA_SIZE)
includes all replicas. To get the size of a single copy, divide the result by your replication factor. Let me know if you need help narrowing down a specific table! References • sql-reference/information_schema/be_tablets.md • sql-reference/sql-statements/table_bucket_part_index/SHOW_TABLET.md • sql-reference/information_schema/be_cloud_native_compactions.md • sql-reference/information_schema/fe_tablet_schedules.md
c
@Rocky above query also doesn't finish in 1 hrs.
r
It sounds like your query is hitting a significant bottleneck or perhaps getting stuck. To help you troubleshoot this, let's look into what’s happening under the hood. Here is a step-by-step guide to diagnosing a long-running query in StarRocks: 1. Check Current Status First, see if the query is actually running or if it's waiting in a queue.
Copy code
sql
-- Check all active queries
SHOW PROCESSLIST;

-- (v3.1+) More detailed view for running/queued queries
SHOW RUNNING QUERIES;
* IsPending: If 
true
, the query is stuck in a resource queue. * Time: Check how many seconds it has been running. * State: Look for states like 
Running
, 
Pending
, or 
Executing
. 2. Inspect the Real-Time Profile If you are on StarRocks v3.1 or later, you can analyze the profile of a query while it is still running. This is the best way to see which operator (Scan, Join, Aggregate) is consuming the hour.
Copy code
sql
-- 1. Find the QueryId
SHOW PROFILELIST;

-- 2. Analyze the profile for that specific QueryId
ANALYZE PROFILE FROM 'your_query_id';
Look for the Top Most Time-consuming Nodes. If a 
SCAN
is taking 99% of the time, you might have a data distribution or indexing issue. If it's a 
JOIN
, you might have a data skew or a massive broadcast join. 3. Check for Resource Bottlenecks The query might be slow because the backends (BEs) are overwhelmed. Check the health and resource usage of your nodes:
Copy code
sql
SHOW PROC '/backends';
Check 
CpuUsedPct
and 
MemUsedPct
. If one node is at 100% and others are at 5%, you likely have data skew on your distribution key. 4. Examine the Query Plan To see how StarRocks intends to run the query, use `EXPLAIN`:
Copy code
sql
EXPLAIN VERBOSE <your_query>;
Check for: * CROSS JOIN: Ensure you aren't accidentally doing a cartesian product. * Broadcast Joins: If a very large table is being broadcast to all nodes, it will be extremely slow. * Partition/Tablet Pruning: Ensure your 
WHERE
clause is actually hitting the partitions and buckets you expect. 5. Common Quick Fixes If you need to get the system moving again, you can kill the connection:
Copy code
sql
-- Use the Id from SHOW PROCESSLIST
KILL <connection_id>;
To help me give you more specific advice, could you share: 1. The SQL query itself. 2. The schema of the tables involved (output of 
SHOW CREATE TABLE ...
). 3. The output of 
EXPLAIN
for that query. I'm Rocky, here to help you get that query finished! References • faq/shared_data_faq.md • best_practices/query_tuning/query_planning.md • best_practices/query_tuning/query_plan_intro.md • loading/loading_introduction/troubleshooting_loading.md
c
@Rocky is it possible to cache information_schema.be_tablets table?
r
Yes, the information in 
information_schema.be_tablets
is internally cached by StarRocks, and you can control the refresh interval of this cache through configuration parameters. 1. Internal Caching Mechanism StarRocks does not use the standard Query Cache (Result Cache) for 
information_schema.be_tablets
because it is a system-generated view rather than a standard OLAP table. Instead, it relies on an internal Tablet Stat Cache mechanism: * BE Side: The Backend nodes cache tablet statistics (like row count and data size). This is controlled by the BE configuration: * `tablet_stat_cache_update_interval_second`: (Default: 
300
) The time interval at which the BE's internal Tablet Stat Cache updates. * FE Side: The Frontend node retrieves these statistics from the BEs periodically. This is controlled by the FE configuration: * `tablet_stat_update_interval_second`: (Default: 
300
) The time interval at which the FE retrieves tablet statistics from each BE. 2. How to "Cache" it Manually If you find that querying 
information_schema.be_tablets
is slow (which can happen in very large clusters with millions of tablets) and you need to query it frequently, a common practice is to create a regular StarRocks table and periodically materialize the results:
Copy code
sql
-- Create a table to store the snapshot
CREATE TABLE tablet_stats_snapshot (...) 
DISTRIBUTED BY HASH(tablet_id);

-- Periodically refresh
INSERT OVERWRITE tablet_stats_snapshot 
SELECT * FROM information_schema.be_tablets;
3. Checking Cache Status You can verify the current configuration for these intervals by running:
Copy code
sql
-- Check FE config
ADMIN SHOW FRONTEND CONFIG LIKE '%tablet_stat_update_interval_second%';

-- Check BE config
SELECT * FROM information_schema.be_configs 
WHERE name = 'tablet_stat_cache_update_interval_second';
Note: Decreasing these intervals will provide more real-time data but may increase the load on the FE and BE metadata reporting threads. Increasing them will reduce overhead but make the data in 
be_tablets
potentially "staler." References • administration/management/BE_configuration.md • administration/management/FE_configuration.md • using_starrocks/caching/query_cache.md • data_source/data_cache_observe.md
c
@Rocky can you share exact command for
Copy code
-- Create a table to store the snapshot
CREATE TABLE tablet_stats_snapshot (...) 
DISTRIBUTED BY HASH(tablet_id);
r
To create a table for snapshotting tablet statistics from the 
information_schema.be_tablets
system table, you can use the following 
CREATE TABLE
command. This schema includes all fields provided by 
be_tablets
plus a 
snapshot_time
column to track when the data was recorded.
Copy code
sql
CREATE TABLE tablet_stats_snapshot (
    snapshot_time  DATETIME NOT NULL COMMENT "Time when the snapshot was taken",
    be_id          BIGINT NOT NULL,
    table_id       BIGINT,
    partition_id   BIGINT,
    tablet_id      BIGINT NOT NULL,
    num_version    BIGINT,
    max_version    BIGINT,
    min_version    BIGINT,
    num_rowset     BIGINT,
    num_row        BIGINT,
    data_size      BIGINT,
    index_mem      BIGINT,
    create_time    BIGINT COMMENT "Creation time of the tablet (Unix timestamp)",
    state          VARCHAR(64),
    type           VARCHAR(64),
    data_dir       VARCHAR(500),
    shard_id       BIGINT,
    schema_hash    BIGINT,
    index_disk     BIGINT,
    medium_type    VARCHAR(64),
    num_segment    BIGINT
)
ENGINE = OLAP
DUPLICATE KEY(snapshot_time, be_id, table_id)
DISTRIBUTED BY HASH(tablet_id)
PROPERTIES (
    "replication_num" = "3"
);
How to populate the snapshot You can run the following command periodically (e.g., via a Routine Load, a Task, or an external script) to capture the current state:
Copy code
sql
INSERT INTO tablet_stats_snapshot 
SELECT 
    NOW(), 
    BE_ID, TABLE_ID, PARTITION_ID, TABLET_ID, 
    NUM_VERSION, MAX_VERSION, MIN_VERSION, 
    NUM_ROWSET, NUM_ROW, DATA_SIZE, INDEX_MEM, 
    CREATE_TIME, STATE, TYPE, DATA_DIR, 
    SHARD_ID, SCHEMA_HASH, INDEX_DISK, MEDIUM_TYPE, NUM_SEGMENT
FROM information_schema.be_tablets;
Key Considerations: 1. Duplicate Key: Since this is historical log-style data, 
DUPLICATE KEY
is the most efficient model. 2. Distribution: As requested, it is 
DISTRIBUTED BY HASH(tablet_id)
, which ensures that all historical snapshots for a single tablet are collocated, making it efficient to track a specific tablet's growth over time. 3. Storage: If you plan to keep these snapshots for a long time, consider adding 
PARTITION BY RANGE (snapshot_time)
so you can easily drop old data using 
DROP PARTITION
. References • sql-reference/information_schema/be_tablets.md • sql-reference/information_schema/be_txns.md • sql-reference/information_schema/be_logs.md • sql-reference/information_schema/be_cloud_native_compactions.md • sql-reference/information_schema/be_compactions.md • sql-reference/information_schema/be_metrics.md • sql-reference/sql-statements/table_bucket_part_index/SHOW_TABLET.md
c
@Rocky how to increase starrocks mysql shell idle connection timeout
r
To increase the idle connection timeout for the StarRocks MySQL shell (or any MySQL-compatible client), you need to adjust the 
wait_timeout
 system variable. 1. 
wait_timeout
 This variable defines the number of seconds StarRocks waits for activity on a connection before closing it. * Default: 28800 seconds (8 hours). * Unit: Seconds. How to change it: * For the current session only: If you want to increase the timeout for your current connection:
Copy code
sql
    SET wait_timeout = 86400; -- Sets timeout to 24 hours
* Globally (for all future connections): To change the default behavior for all new connections:
Copy code
sql
    SET GLOBAL wait_timeout = 86400;
Note: Changing the global variable does not affect currently active connections; it only applies to connections created after the change. 2. Other related timeouts While 
wait_timeout
is the primary setting for idle connections, you might also find these useful depending on your goal: * 
interactive_timeout
: In StarRocks, this variable exists primarily for MySQL compatibility and generally does not have a practical effect on idle connections compared to 
wait_timeout
. * 
query_timeout
: This controls how long a running query can execute before being killed (default is 300 seconds). If your connection is dropping because a query takes too long, increase this instead:
Copy code
sql
    SET query_timeout = 3600; -- 1 hour
* Load Balancer/Proxy Settings: If you are connecting to StarRocks through a load balancer (like F5, HAProxy, or an AWS ELB), the load balancer itself often has an idle timeout. If your connection drops earlier than the 
wait_timeout
value you set, check the timeout settings on your networking infrastructure. You can also enable TCP Keep-Alive in the StarRocks FE configuration (
mysql_service_nio_enable_keep_alive = true
) to help prevent proxies from closing idle connections. References • sql-reference/System_variable.md • sql-reference/sql-statements/loading_unloading/BROKER_LOAD.md • administration/management/FE_configuration.md • faq/loading/Loading_faq.md
c
@Rocky how to check current value for 
wait_timeout
?
r
To check the current value of the 
wait_timeout
system variable in StarRocks, you can use the following SQL commands: Method 1: Using 
SHOW VARIABLES
 This is the most common way to see the variable and its value:
Copy code
sql
SHOW VARIABLES LIKE 'wait_timeout';
Method 2: Using 
SELECT
 You can also query the variable directly using the 
@@
prefix:
Copy code
sql
SELECT @@wait_timeout;
Context: * Default value: 28800 seconds (8 hours). * Description: It defines how many seconds StarRocks waits for activity on a non-interactive connection before closing it. * Scope: It can be set at the session level (affects only the current connection) or the global level (affects new connections). If you want to check the global value specifically, you can use:
Copy code
sql
SHOW GLOBAL VARIABLES LIKE 'wait_timeout';
References • sql-reference/System_variable.md • faq/Sql_faq.md
c
@Rocky my starrocks mysql termial shell get disconnected within few mins even though wait_timeout is set 28800
r
Hi there! I'm Rocky. It sounds like you're running into a common issue where an external network component is dropping your connection before StarRocks does. Even though you have 
wait_timeout
set to 28800 (8 hours) in StarRocks, your connection is likely being terminated by a Load Balancer, Proxy (like Nginx, HAProxy, or ProxySQL), or a Firewall sitting between your MySQL client and the StarRocks Frontend (FE). These components often have their own idle connection timeouts that default to much shorter periods (e.g., 60 seconds or 5 minutes). Here are a few things to check and try: 1. Check your Load Balancer/Proxy settings If you are connecting via a Load Balancer (like AWS ELB, F5) or a proxy: * Check the Idle Timeout setting on that device/service. * Ensure it matches or exceeds the duration you need for your idle sessions. 2. Verify FE Keep-Alive Configuration StarRocks has a configuration to help keep connections alive through proxies by using TCP Keep-Alive. * Check your FE configuration (
fe.conf
) for: 
mysql_service_nio_enable_keep_alive = true
* This is 
true
by default, but it's worth verifying. It helps prevent intermediate network devices from silently dropping the connection. 3. Check 
interactive_timeout
 While StarRocks uses 
wait_timeout
for most sessions, for compatibility with some MySQL clients that identify as "interactive," you might also want to try setting the 
interactive_timeout
variable:
Copy code
sql
SET GLOBAL interactive_timeout = 28800;
-- And for your current session:
SET interactive_timeout = 28800;
4. Client-side Reconnect If you are using the 
mysql
command-line tool, you can use the 
--reconnect
flag. This won't stop the disconnection from happening, but it will automatically try to reconnect when you issue your next command:
Copy code
bash
mysql -h <FE_IP> -P 9030 -u root --reconnect
5. Check FE Logs If the disconnection is happening while a query is running (not just while idle), check the 
fe/log/fe.warn.log
or 
fe/log/fe.log
to see if there are any errors or if the FE is restarting, which would also cause a disconnect. Summary Recommendation: The most frequent cause for a "few minutes" disconnect is an intermediate proxy/load balancer idle timeout. Increasing the timeout on your proxy or enabling more aggressive TCP keep-alive on your client machine usually resolves this! References • administration/management/FE_configuration.md
c
@Rocky how to optimize
Copy code
OLAP_SCAN (id=0)                                                                                                                       |
|              Estimates: [row: 172148293, cpu: ?, memory: ?, network: ?, cost: 2.9006987496875E10]                                               |
|              TotalTime: 29s171ms (99.99%) [CPUTime: 16.543ms, ScanTime: 29s154ms]                                                               |
|              OutputRows: 3                                                                                                                      |
|              Table: : test_table1                                                                                                 |
|              SubordinateOperators:                                                                                                              |
|                  CHUNK_ACCUMULATE                                                                                                               |
|              Detail Timers: [ScanTime = IOTaskExecTime + IOTaskWaitTime]                                                                        |
|                  IOTaskExecTime: 15s967ms [min=3s201ms, max=25s664ms]                                                                           |
|                      IOStatistics:                                                                                                              |
|                          IOTimeRemote: 5s812ms [min=913.491ms, max=11s980ms]                                                                    |
|                          IOTimeTotal: 5s872ms [min=922.159ms, max=12s93ms]                                                                      |
|                      IOTime: 5s872ms [min=922.159ms, max=12s93ms]                                                                               |
|                      SegmentInit: 15s954ms [min=3s199ms, max=25s659ms]                                                                          |
|                          ColumnIteratorInit: 12s520ms [min=2s64ms, max=20s773ms]                                                                |
|                  IOTaskWaitTime: 6s414ms [min=108.611ms, max=18s72ms]
r
Based on the profile snippet you provided, the bottleneck is clearly in the Scan Initialization phase. Specifically, 
SegmentInit
 (15.9s) and 
ColumnIteratorInit
 (12.5s) account for nearly half of the total execution time (29s). This indicates that StarRocks is spending an excessive amount of time "opening" files and metadata rather than actually reading and processing data. Here are the primary causes and how to optimize them: 1. High Number of Small Files (Segments) When 
ColumnIteratorInit
is high, it usually means the table has too many segments or small files. This happens if you have many small data loads and background compaction hasn't caught up. * How to check: Run 
SHOW TABLET FROM test_table1;
and check the 
VersionCount
or look for the number of segments in the data directory. * Solution: * Increase Load Batch Size: Avoid frequent small imports (e.g., every few seconds). Consolidate imports into larger batches. * Manual Compaction: If the compaction score is high, you can trigger manual compaction or adjust BE configurations like 
cumulative_compaction_num_threads_per_disk
to speed it up. 2. Over-sharding (Too Many Tablets) If a table has too many tablets (buckets) relative to the amount of data, each query must initialize iterators for every single tablet, even if they contain very little data. * How to check: Run 
SHOW CREATE TABLE test_table1;
and check the 
DISTRIBUTED BY
clause. * Solution: For a table with ~172M rows, you should aim for tablets to be around 100MB to 1GB in size. If your tablets are only a few MBs each, reduce the bucket count in the table definition. 3. Missing Pruning (Poor Sort Key/Index) Your query returns only 3 rows out of 172 million, yet the engine is spending 29 seconds scanning. This means Storage Layer Pruning (Zone Map) is not working effectively. * The Issue: The engine is opening and "looking into" almost all segments because it cannot skip them based on metadata. * Solution: * Optimize Sort Key: Ensure the column used in your 
WHERE
clause is the first column in your 
DUPLICATE KEY
or 
PRIMARY KEY
(Sort Key). This allows StarRocks to skip segments entirely using the min/max index. * Bloom Filter Index: If the filter column has high cardinality (like a UUID or ID), add a Bloom Filter index: 
ALTER TABLE test_table1 SET ("bloom_filter_columns" = "your_filter_column");
* Bitmap Index: For low-cardinality columns, use a Bitmap index to speed up filtering. 4. Remote Storage Latency (Shared-Data Cluster) The profile shows 
IOTimeRemote: 5.8s
. This confirms you are likely using a shared-data (Cloud Native) architecture or reading from HDFS/S3. * The Issue: Accessing metadata for many small files over the network is significantly slower than local disk. * Solution: * Warm up the Cache: Ensure the data and metadata are cached on the CN/BE local disks. * Data Size: If using StarRocks 3.x, check if the 
query_cache
is enabled to avoid re-scanning the same data. 5. IO Thread Pool Saturation 
IOTaskWaitTime: 6s
suggests that the IO threads were waiting to be scheduled. This is a side effect of having too many segments/tablets, which creates a massive queue of small IO tasks. * Immediate Fix: You can try increasing the scanner thread count (session variable):
Copy code
sql
    SET pipeline_exec_thread_pool_size = 32; -- Adjust based on CPU cores
Summary Recommendation: 1. Check Compaction status (
show proc '/compactions'
). 2. Ensure your 
WHERE
clause column is part of the Sort Key. 3. Reduce the number of Buckets (Tablets) if they are too small. References • faq/Sql_faq.md • knowledge/admin_and_operation/shared_data_compaction.md
c
@Rocky is bitmap index efficient for point lookups?
r
Yes, bitmap indexes in StarRocks are efficient for point lookups, but their effectiveness depends on the cardinality of the column and the query conditions. Based on StarRocks' performance testing and documentation, here is how bitmap indexes behave in different point lookup scenarios: 1. High-Cardinality Point Lookups (Efficient) For a single column with high cardinality (many unique values, e.g., a specific 
ID
or 
Key
), a bitmap index is highly efficient. * Why: It can precisely locate the row numbers and filter out the vast majority of data pages, significantly reducing I/O. * Example: In StarRocks' internal tests, a point lookup on a high-cardinality column saw query times drop from 0.43s (without index) to 0.015s (with bitmap index). 2. Multiple Low-Cardinality Lookups (Efficient) If you are performing a "point lookup" based on a combination of several low-cardinality columns (e.g., 
WHERE color='red' AND size='XL' AND gender='male'
), a bitmap index is very effective. * Why: While a single low-cardinality filter might not skip many pages, the intersection of multiple bitmap indexes can narrow down the search to a very small number of rows/pages. 3. Single Low-Cardinality Lookups (Inefficient) For a point lookup on a single column with very few unique values (e.g., 
WHERE gender='male'
), a bitmap index is often inefficient. * Why: Even though the index quickly identifies the rows, those rows are usually scattered across almost every data page. Since the "Page" is the smallest unit of I/O, StarRocks still ends up reading most of the table. The extra overhead of loading the bitmap index can actually make the query slower than a standard scan. Comparison with Other Indexes for Point Lookups While bitmap indexes are powerful, they aren't the only option for point lookups in StarRocks: * Prefix Index (Sort Key): This is the most efficient. If your point lookup is on the first few columns of the table's sort key, StarRocks uses the prefix index automatically for near-instant retrieval. * Bloom Filter Index: Also suitable for high-cardinality columns. It is particularly good for 
in
and 
=
queries to quickly determine if a value does not exist in a data block, avoiding unnecessary reads. Summary Tip StarRocks has a default threshold for using bitmap indexes: it will automatically use the index only if the ratio of (distinct values / total rows) is less than a certain limit (default 
1/1000
). This prevents the system from using the index in cases where it would be counter-productive. References • table_design/indexes/Bitmap_index.md
c
@Rocky starrocks engine is not using bitmap index on column but does olap scan causing increased query processing time
r
Hello! I'm Rocky, and I can help you understand why StarRocks might be skipping your bitmap index and performing a full OLAP scan instead. StarRocks uses an adaptive selection strategy for bitmap indexes. It doesn't always use them because, in certain scenarios (especially with low-cardinality columns), loading and processing the index can actually be slower than a direct scan. Here are the primary reasons why your bitmap index might not be used: 1. The "Filtering Efficiency" Threshold StarRocks calculates whether an index will be effective using a default ratio. By default, it only uses a bitmap index if: 
(Number of values in filter) / (Column Cardinality) < bitmap_max_filter_ratio / 1000
 * The default 
bitmap_max_filter_ratio
is 1. * Example (Low Cardinality): If you have a 
gender
column with 2 distinct values (Male/Female) and you filter 
WHERE gender = 'Male'
, the ratio is 
1/2 (0.5)
. This is much larger than 
1/1000 (0.001)
, so StarRocks will skip the index and perform a scan. * Example (High Cardinality): If a 
user_id
column has 1,000,000 distinct values and you filter for one ID, the ratio is 
1/1,000,000
. Since this is less than 
0.001
, the index will be used. 2. Multi-Column Predicates If you are filtering on multiple columns, StarRocks multiplies their ratios. A query with multiple low-cardinality filters might eventually drop below the threshold and trigger index usage, whereas a single-column filter on the same data would not. 3. Page-Level Pruning Efficiency A bitmap index is most effective when it can help the engine skip entire Pages of data. If the values you are filtering for are scattered randomly across every page in the segment, the index will successfully "filter" rows, but the engine still has to load every page to read those rows. In this case, the overhead of loading the bitmap index actually increases the total query time. How to Troubleshoot and Fix Step 1: Verify Index Usage Check the Query Profile for your execution. Look for these metrics in the `OLAP_SCAN_NODE`: * `BitmapIndexFilter`: Time spent filtering with the index. * `BitmapIndexFilterRows`: Number of rows filtered out by the index. If these are 0 or missing, the index was not used. Step 2: Force Index Usage (For Testing) If you believe the index should be faster, you can force StarRocks to use it by changing a BE configuration: 1. In 
be.conf
on each BE node, set: 
bitmap_max_filter_ratio=1000
. 2. Restart the BE nodes. Note: Setting this to 1000 forces the index for all queries. Monitor your overall system performance, as this can slow down queries on very low-cardinality columns. Step 3: Column Requirements Ensure your column meets the requirements for bitmap indexes: * Supported Types: 
TINYINT
, 
SMALLINT
, 
INT
, 
BIGINT
, 
CHAR
, 
VARCHAR
, 
DATE
, 
DATETIME
. * Note: Bitmap indexes are not supported on 
FLOAT
, 
DOUBLE
, or 
DECIMAL
types. If the "increased processing time" is significant and the column has high cardinality, double-check that the index was actually created successfully using 
SHOW INDEX FROM table_name
. References • administration/management/BE_configuration.md • table_design/indexes/Bitmap_index.md
c
@Rocky how to pass query hint to use index?
r
In StarRocks, there is no direct equivalent to MySQL's 
USE INDEX
or 
FORCE INDEX
hint for standard indexes. Instead, StarRocks provides specific hints and system variables to influence how the optimizer uses different types of indexes or materialized views. 1. Forcing Bitmap Indexes StarRocks uses an adaptive mechanism to decide whether to use a bitmap index based on column cardinality and query filtering. To force the use of a bitmap index, you can set the 
bitmap_max_filter_ratio
variable. Setting it to 
1000
(the maximum) tells StarRocks to use the bitmap index regardless of its estimated filtering effect. You can pass this as a query hint using `SET_VAR`:
Copy code
sql
SELECT /*+ SET_VAR(bitmap_max_filter_ratio=1000) */ * 
FROM your_table 
WHERE your_column = 'value';
2. Forcing Synchronous Materialized Views (Rollups) Synchronous materialized views (often called rollups) are used to accelerate aggregations. While the optimizer usually selects the best rollup automatically, you can explicitly query a synchronous materialized view by name using the 
[_SYNC_MV_]
hint:
Copy code
sql
-- Query the synchronous MV directly by its name
SELECT * FROM your_mv_name [_SYNC_MV_];
Note: The brackets 
[]
are required in the hint. 3. Join Hints If your query performance is related to how tables are joined (which often involves index usage in other databases), StarRocks uses Join hints: * 
[BROADCAST]
* 
[SHUFFLE]
* 
[BUCKET]
* 
[COLOCATE]
Example:
Copy code
sql
SELECT * FROM table_a JOIN [BROADCAST] table_b ON table_a.id = table_b.id;
4. Statement-Level Variable Hints You can use the 
/_+ SET_VAR(...) _/
syntax to adjust other optimizer behaviors for a specific query: * `query_timeout`: Set query execution timeout. * `enable_materialized_view_rewrite`: Enable or disable MV rewrites.
Copy code
sql
SELECT /*+ SET_VAR(query_timeout=60, enable_materialized_view_rewrite=true) */ ...
Summary * Prefix Index/Primary Key Index: Used automatically based on your 
ORDER BY
or 
PRIMARY KEY
column order in the table definition. No hint available. * Bitmap Index: Use 
SET_VAR(bitmap_max_filter_ratio=1000)
. * Rollup/Sync MV: Use 
[_SYNC_MV_]
after the MV name. * Bloom Filter: Automatically used if created on the column and the predicate is supported. References • best_practices/query_tuning/query_hint.md • using_starrocks/accelerate-cardinality-preserving-joins.md • faq/Sql_faq.md
c
@Rocky getting
Copy code
SQL Error [1193] [HY000]: Unknown system variable 'bitmap_max_filter_ratio', the most similar variables are {'insert_max_filter_ratio', 'cbo_cte_max_limit', 'enable_min_max_optimization'}
for below query
Copy code
sql
SELECT /*+ SET_VAR(bitmap_max_filter_ratio=1000) */ * 
FROM your_table 
WHERE your_column = 'value';
r
The error 
SQL Error [1193] [HY000]: Unknown system variable 'bitmap_max_filter_ratio'
occurs because 
bitmap_max_filter_ratio
is a BE (Backend) configuration parameter, not a session or system variable. In StarRocks, variables used inside 
SET_VAR(...)
or with the 
SET
command must be session variables. Because 
bitmap_max_filter_ratio
is a configuration for the storage engine on the BE nodes, it cannot be modified at the query or session level. How to resolve this: If you want to change the 
bitmap_max_filter_ratio
(for example, to force the use of a bitmap index by setting it to 
1000
), you must modify the configuration file on each BE node: 1. Edit 
be.conf
: Open the 
be.conf
file (usually located in the 
conf/
directory of your BE installation) on every BE node. 2. Add/Modify the parameter:
Copy code
properties
    bitmap_max_filter_ratio = 1000
3. Restart the BE: You must restart the BE processes for this change to take effect. Understanding 
bitmap_max_filter_ratio
 StarRocks uses an adaptive selection mechanism for bitmap indexes. It decides whether to use a bitmap index based on the following threshold: 
number of values in filter condition / column cardinality < bitmap_max_filter_ratio / 1000
* Default value: 
1
(threshold is 1/1000). * Force usage: Setting it to 
1000
forces StarRocks to use a bitmap index whenever one is available for the column in the query. * Why it's adaptive: Bitmap indexes are most effective when they filter out a large amount of data (typically 99.9%). Using them on low-cardinality columns with poor filtering can sometimes be slower than a standard scan due to the overhead of loading the index pages. Checking your configuration You can verify the current value of BE configurations by running:
Copy code
sql
SHOW BACKENDS\G
Then check the 
web_port
of a BE and visit 
http://<be_host>:<be_web_port>/configs
in your browser to see all active BE settings. References • administration/management/BE_configuration.md • table_design/indexes/Bitmap_index.md
c
@Rocky does this
Copy code
+--------------+---------------------------+----------------+---------------+---------------+---------------------+
| TABLE_SCHEMA | TABLE_NAME                | total_segments | total_rowsets | total_size_mb | avg_segment_size_mb |
+--------------+---------------------------+----------------+---------------+---------------+---------------------+
| test_db      | table1 				   |            589 |            48 |     621241.62 |             1054.74 |
+--------------+---------------------------+----------------+---------------+---------------+---------------------+
have small files based on below query?
Copy code
sql
SELECT 
    t.TABLE_SCHEMA, 
    t.TABLE_NAME, 
    bt.total_segments,
    bt.total_rowsets,
    bt.total_size_mb,
    bt.avg_segment_size_mb
FROM (
    -- Step 1: Aggregate tablet metrics first to reduce row count
    SELECT 
        TABLE_ID, 
        SUM(NUM_SEGMENT) AS total_segments,
        SUM(NUM_ROWSET) AS total_rowsets,
        ROUND(SUM(DATA_SIZE) / 1024 / 1024, 2) AS total_size_mb,
        ROUND(SUM(DATA_SIZE) / NULLIF(SUM(NUM_SEGMENT), 0) / 1024 / 1024, 2) AS avg_segment_size_mb
    FROM information_schema.be_tablets
    GROUP BY TABLE_ID
) bt
-- Step 2: Join with tables_config (much smaller than partitions_meta)
JOIN information_schema.tables_config t ON bt.TABLE_ID = t.TABLE_ID
-- Recommended: Filter for a specific database if possible
-- WHERE t.TABLE_SCHEMA = 'your_database_name'
ORDER BY total_segments DESC;
r
Based on the output you provided, the answer is no, this table does not have a "small files" problem. In fact, its storage health looks very good. Here is the breakdown of why these numbers indicate a healthy table: 1. Average Segment Size (~1 GB) * Your Value: 
avg_segment_size_mb
= 1,054.74 MB * Analysis: StarRocks typically aims for segment files to be around 512 MB to 1 GB. * Why it's good: An average size of ~1 GB is near the "sweet spot" for StarRocks. Large segments allow the system to leverage columnar storage benefits (like better compression and more effective zone-map pruning) and reduce the I/O overhead of opening many small files. * What "Small Files" would look like: You would typically start worrying if the 
avg_segment_size_mb
was consistently below 100 MB, and especially if it was in the single digits (1–10 MB). 2. Total Rowsets (48) * Your Value: 
total_rowsets
= 48 * Analysis: For a table that is 621 GB in size, having only 48 rowsets is excellent. * Why it's good: Each rowset represents a version of data created by a load job or a compaction task. Having a low number of rowsets relative to the data size indicates that the background compaction process is working efficiently, merging smaller incoming data batches into large, optimized structures. * What a problem would look like: If you saw thousands of rowsets for this same volume of data, it would indicate a "Small File" issue (specifically, a lack of compaction), which would lead to high metadata overhead and slow query performance. 3. Segment Count (589) * Your Value: 
total_segments
= 589 * Analysis: For ~621 GB of data, 589 files is a very manageable number for the storage engine. Summary Comparison Metric | Your Table | "Small Files" Warning Sign Avg Segment Size | ~1,054 MB (Healthy) | < 100 MB (Inefficient) Total Rowsets | 48 (Well-compacted) |
1,000
 (Compaction lag) Data/Rowset Ratio | ~13 GB per rowset | < 100 MB per rowset Recommendations Since your table looks healthy, you don't need to take corrective action. However, if you see these numbers degrade over time (e.g., rowsets climbing into the thousands while avg size drops): 1. Check Ingestion Frequency: Avoid very frequent, tiny "Stream Loads." Batch your data more if possible. 2. Verify Compaction: Ensure BE resources aren't so pegged that background compaction can't keep up. 3. Partitioning/Bucketing: Ensure you haven't over-partitioned the table (e.g., creating thousands of partitions/tablets for only a few GBs of data), as this is the most common cause of small files. References • knowledge/usage/best-practice.md • best_practices/table_clustering.md • best_practices/partitioning.md • loading/loading_introduction/loading_considerations.md
c
@Rocky query is spending most time olap scan even though it doesn't have small files as shown in above output
Copy code
OLAP_SCAN (id=0)                                                                                                                       |
|              Estimates: [row: 172148293, cpu: ?, memory: ?, network: ?, cost: 2.9006987496875E10]                                               |
|              TotalTime: 29s171ms (99.99%) [CPUTime: 16.543ms, ScanTime: 29s154ms]                                                               |
|              OutputRows: 3                                                                                                                      |
|              Table: : table1                                                                                                 |
|              SubordinateOperators:                                                                                                              |
|                  CHUNK_ACCUMULATE                                                                                                               |
|              Detail Timers: [ScanTime = IOTaskExecTime + IOTaskWaitTime]                                                                        |
|                  IOTaskExecTime: 15s967ms [min=3s201ms, max=25s664ms]                                                                           |
|                      IOStatistics:                                                                                                              |
|                          IOTimeRemote: 5s812ms [min=913.491ms, max=11s980ms]                                                                    |
|                          IOTimeTotal: 5s872ms [min=922.159ms, max=12s93ms]                                                                      |
|                      IOTime: 5s872ms [min=922.159ms, max=12s93ms]                                                                               |
|                      SegmentInit: 15s954ms [min=3s199ms, max=25s659ms]                                                                          |
|                          ColumnIteratorInit: 12s520ms [min=2s64ms, max=20s773ms]                                                                |
|                  IOTaskWaitTime: 6s414ms [min=108.611ms, max=18s72ms]
r
Based on the profile snippet provided, your query is indeed bottlenecked by the initialization phase of the scan, specifically within the storage layer. Although you mentioned there are no "small files," the metrics point to an overhead in managing the number of segments or the metadata associated with them. Analysis of the Profile Metrics * 
SegmentInit
(15.9s) / 
ColumnIteratorInit
(12.5s): This is the time spent opening segment files, reading their footers (metadata), and initializing the iterators for each column. Spending 12.5 seconds here is extremely high. * 
IOTimeRemote
(5.8s): This suggests that a significant portion of the initialization time involves fetching metadata from remote storage (like S3, HDFS, or a Shared-Data backend). * 
IOTaskWaitTime
(6.4s): This indicates that the I/O thread pool is saturated. Tasks are waiting to be executed, which usually happens when there are a very large number of tablets or segments to process concurrently. ────────── Potential Causes and Solutions 1. High Rowset/Segment Count (Lack of Compaction) Even if your files are not "small" (e.g., they are 256MB+), if a tablet has a large number of Rowsets (created by frequent imports) that haven't been compacted yet, StarRocks must open every single rowset to satisfy the query. * Check: Run 
SHOW TABLE STATUS FROM <db_name> LIKE '<table_name>';
and look at the tablet/rowset distribution. Or check 
RowsetsReadCount
in a more detailed section of your profile. * Solution: Trigger a manual compaction or tune your compaction threads to be more aggressive if the rowset count per tablet is high (typically > 10-20 is where performance degrades). *2. Excessive Column Count (
SELECT *
)* 
ColumnIteratorInit
scales with the number of columns you are reading. If you are performing a 
SELECT *
on a table with hundreds of columns, the engine must initialize an iterator for every column in every segment. * Solution: Explicitly select only the columns required for the query. 3. Remote Metadata Latency If you are using StarRocks Shared-Data mode or scanning External Tables (Iceberg/Hive), every segment footer read is a remote network call. 5.8s of 
IOTimeRemote
during initialization suggests network RTT overhead. * Solution: * Ensure Data Cache is enabled and sufficiently sized to keep segment footers/metadata in local disk/memory. * If using Shared-Data, check if the metadata cache is effective. 4. I/O Thread Pool Saturation The 6.4s 
IOTaskWaitTime
shows that the scanner threads are queuing. * Solution: You can try increasing the number of scan threads, but this usually only helps if your CPU is underutilized. * Check/Adjust: 
pipeline_scan_thread_pool_thread_num
(BE config) or session variable 
pipeline_dop
. _ _Note:* It is usually better to fix the "too many segments" issue than to simply add more threads. 5. Dictionary or Index Initialization If the columns being read have very large Global Dictionaries or complex Bitmap Indexes, 
ColumnIteratorInit
will include the time to load these into memory. * Check: See if the 
Detail Timers
show high 
BitmapIndexFilter
or 
IndexLoad
times (often nested under SegmentInit). Summary Recommendation Start by checking the number of rowsets/segments being scanned. If the number is high, compaction is the most likely fix. If you are reading many columns, try reducing the column list to see if 
ColumnIteratorInit
drops significantly. References • using_starrocks/caching/query_cache.md • using_starrocks/caching/block_cache.md • administration/management/BE_configuration.md
c
@Rocky can't see table/rowset distribution in below suggested query output
Copy code
mysql> SHOW TABLE STATUS FROM test_db like 'table1';
+---------------------------+-----------+---------+------------+-----------+----------------+--------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| Name                      | Engine    | Version | Row_format | Rows      | Avg_row_length | Data_length  | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation       | Checksum | Create_options | Comment |
+---------------------------+-----------+---------+------------+-----------+----------------+--------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| table1 | StarRocks |    NULL |            | 688593175 |            894 | 615822896081 |            NULL |         NULL |      NULL |           NULL | 2026-02-09 20:07:57 | 2026-02-09 21:42:13 | NULL       | utf8_general_ci |     NULL |                | OLAP    |
+---------------------------+-----------+---------+------------+-----------+----------------+--------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
1 row in set (0.28 sec)
r
Hello! I'm Rocky, your StarRocks assistant. How can I help you today? If you have questions about SQL syntax, performance tuning, or any other StarRocks-related topics, feel away to ask!
The 
SHOW TABLE STATUS
command is a MySQL-compatible statement that provides general table metadata (like row count and data length), but it does not include StarRocks-specific internal distribution details such as partitions, buckets, tablets, or rowsets. To view the distribution and rowset information for your table, you should use the following StarRocks commands: 1. View Partition and Bucket Distribution To see how many buckets (tablets) are in each partition and their data size:
Copy code
sql
SHOW PARTITIONS FROM table1;
* Buckets: The number of tablets in that partition. * DataSize: The total size of data in that partition. 2. View Tablet Distribution across Backends To see a summary of how replicas are distributed across your Backend (BE) nodes:
Copy code
sql
ADMIN SHOW REPLICA DISTRIBUTION FROM table1;
This provides a graphical representation of whether your data is balanced across the cluster. 3. View Detailed Tablet and Rowset Info To see individual tablet details, including the VersionCount (which represents the number of rowsets in that tablet):
Copy code
sql
SHOW TABLET FROM table1;
* TabletId: The unique ID of the data shard. * BackendId: Which BE the tablet is stored on. * VersionCount: The number of rowsets currently in this tablet. A high version count (e.g., > 100-200) usually indicates that compaction is lagging. * CompactionStatus: A URL you can visit (or 
curl
) to see the specific rowsets and their versions within that tablet. 4. Deep Dive into Rowsets (Shared-nothing) If you need to see the actual rowset IDs and versions for a specific tablet, you can use the 
SHOW PROC
command. First, get the 
TabletId
from the 
SHOW TABLET
command above, then run:
Copy code
sql
SHOW TABLET <tablet_id>;
The output will contain a 
DetailCmd
column like: 
SHOW PROC '/dbs/1001/1002/partitions/1003/1004/<tablet_id>';
Running that 
SHOW PROC
command will list every individual rowset, its version, and its size. References • sql-reference/sql-statements/table_bucket_part_index/SHOW_TABLET.md
c
@Rocky can pipeline_dop also used for read query performance improvement or is it limited for load with broker command?
r
Hello! I'm Rocky. That's a great question about performance tuning! The short answer is yes, 
pipeline_dop
(Degree of Parallelism) is used for both read query performance and data loading. It is not limited to broker commands or loading jobs. Here is a breakdown of how it works for each: 1. Read Query Performance 
pipeline_dop
is a key variable for controlling how many pipeline instances (parallel threads) are used to execute a single query fragment on each Backend (BE). * Default (0): From StarRocks v3.0 onwards, the system automatically and adaptively adjusts this based on the query and available resources. * Manual Tuning: You can increase it to speed up heavy analytical queries by using more CPU cores. Generally, setting it to half the number of physical CPU cores is a common practice for performance testing. * High Concurrency: Conversely, in scenarios with very high query concurrency (many users querying at once), you might actually decrease 
pipeline_dop
(even to 1) to prevent a single query from consuming all CPU resources, which improves overall system throughput. Example for a read query:
Copy code
sql
SELECT /*+ SET_VAR(pipeline_dop = 8) */ count(*) FROM my_table;
2. Loading Performance For loading jobs (like Broker Load, Stream Load, or Routine Load), 
pipeline_dop
controls the parallelism of the loading process on OLAP tables. * Increasing this value can help utilize more CPU resources to speed up data parsing and encoding during the load. * Note: For specific sinks like Iceberg or Hive (using 
INSERT INTO
), there is a related variable called 
pipeline_sink_dop
. Summary * Read Queries: Controls execution parallelism to balance latency vs. concurrency. * Loads: Controls the parallelism of the ingestion process into OLAP tables. If you are looking to optimize a specific slow query, you might also want to look at the Query Profile to see if the current 
pipeline_dop
is the bottleneck! Let me know if you need help with that. References • sql-reference/System_variable.md • knowledge/usage/best-practice.md • faq/Sql_faq.md • using_starrocks/caching/query_cache.md • faq/operation_maintenance_faq.md
c
@Rocky is it possible to download and cache indexes locally in shared mode architecture?
r
Yes, it is possible and highly recommended to download and cache indexes locally in StarRocks' shared-data (shared-storage) architecture. StarRocks achieves this through two primary mechanisms depending on the index type: 1. General Data Cache (for Bitmap, Bloom Filter, etc.) In a shared-data architecture, compute nodes (CNs) use a Data Cache (which replaced the older File Cache) to store frequently accessed data from remote storage (S3, GCS, HDFS) on their local disks. * How it works: Indexes such as Bitmap indexes, Bloom filter indexes, and built-in indexes (Prefix, Ordinal, ZoneMap) are stored within or alongside the segment files. When a query requires these indexes, they are pulled from remote storage and cached locally in blocks on the CN's disk. * Benefit: Subsequent queries hitting the same data/index will read directly from the local disk, providing performance comparable to a shared-nothing architecture. 2. Cloud-Native Persistent Index (for Primary Key Tables) For Primary Key tables, the primary key index is crucial for performance during upserts and deletes. StarRocks provides a specific "Cloud Native" persistent index mode: * Configuration: Set the table property 
persistent_index_type
to 
CLOUD_NATIVE
(supported from v3.3.2 onwards). * Mechanism: The full index data is stored permanently in remote object storage. Local disks on the compute nodes act as a cache for this index. * Benefit: This solves several issues: * Elasticity: If a node is added or removed, you don't need to rebuild the entire index from scratch on the new node; it simply pulls what it needs from the remote storage into its local cache. * Disk Savings: It eliminates the strict dependency on local disk capacity for massive indexes, as the "source of truth" is in the cloud storage. How to Enable/Verify * Data Cache: Is generally enabled by default in newer versions (v3.2.3+). You can check the BE/CN configuration 
starlet_use_star_cache=true
and table property 
datacache.enable=true
. * PK Persistent Index:
Copy code
sql
  PROPERTIES (
      "enable_persistent_index" = "true",
      "persistent_index_type" = "CLOUD_NATIVE"
  );
By using these features, you get the cost-efficiency of shared storage with the high performance of local disk access. References • deployment/shared_data/feature-support-shared-data.md • best_practices/primarykey_table.md • table_design/table_types/primary_key_table.md
c
@Rocky what does below command do?
Copy code
ALTER TABLE table1 SET ("bloom_filter_columns" = "col1");
r
The command 
ALTER TABLE table1 SET ("bloom_filter_columns" = "col1");
is used to add or modify Bloom filter indexes for the specified column(s) in a StarRocks table. In this specific case, it configures 
col1
to use a Bloom filter index. What does it do? * Performance Optimization: A Bloom filter index is a space-efficient data structure that allows StarRocks to quickly determine if a value might exist in a data file. If the Bloom filter shows the value is definitely not there, StarRocks skips scanning that file entirely, reducing I/O and query latency. * Overwriting Behavior: Note that this command overwrites the existing Bloom filter configuration. If 
table1
previously had Bloom filters on 
colA
and 
colB
, running this command would remove them and leave only 
col1
with a Bloom filter. * Asynchronous Operation: This is an asynchronous process. After you run the command, StarRocks will build the index for the existing data in the background. You can check the progress using:
Copy code
sql
    SHOW ALTER TABLE;
When should you use it? * High Cardinality: It is most effective for columns with many unique values (e.g., 
user_id
, 
order_id
). * Specific Operators: It only speeds up queries using the 
=
(equality) and 
IN
operators. It does not help with range queries (like 
>
) or 
LIKE
patterns. * Non-Prefix Columns: You typically use it for columns that are NOT part of the table's prefix index (sort keys), as those are already optimized for fast lookups. Important Restrictions: * Table Types: You can create them on any column in Duplicate Key or Primary Key tables. For Aggregate or Unique Key tables, they can only be created on key columns. * Unsupported Types: They cannot be used on 
TINYINT
, 
FLOAT
, 
DOUBLE
, or 
DECIMAL
columns. * Data Types Supported: 
SMALLINT
, 
INT
, 
BIGINT
, 
LARGEINT
, 
CHAR
, 
VARCHAR
, 
STRING
, 
DATE
, and 
DATETIME
. If you want to add multiple columns, you can separate them with commas:
Copy code
sql
ALTER TABLE table1 SET ("bloom_filter_columns" = "col1,col2,col3");
References • table_design/indexes/Bloomfilter_index.md • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • knowledge/usage/best-practice.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md
c
@Rocky will above still work if existing table doesn't have any bloom filter indexes?
r
To check if your table currently has Bloom filter indexes and to ensure everything works if it doesn't, you can follow these steps: 1. How to check for existing Bloom filter indexes You can check if a table has any Bloom filter indexes by running:
Copy code
sql
SHOW CREATE TABLE <table_name>;
In the output, look for the 
PROPERTIES
section. If Bloom filter indexes are configured, you will see a property called 
"bloom_filter_columns"
. * If it's missing or empty: No Bloom filter indexes are currently defined. * If it contains column names: Those specific columns have Bloom filter indexes. 2. Will it still "work" if none exist? Yes. If a table doesn't have Bloom filter indexes: * Queries will still run correctly: StarRocks simply won't use that specific optimization technique. It will rely on other indexes (like the Prefix Index or Zone Maps) to filter data. * Performance: Queries on high-cardinality columns (like IDs) that use 
=
or 
IN
operators might be slower than they would be with a Bloom filter, as StarRocks may need to scan more data blocks. 3. How to add them to an existing table If you find that no Bloom filter indexes exist and you want to add them, you can do so using an 
ALTER TABLE
statement:
Copy code
sql
ALTER TABLE <table_name> SET ("bloom_filter_columns" = "column1,column2");
Important Notes: * Asynchronous: This is an asynchronous operation. You can check its progress using 
SHOW ALTER TABLE COLUMN;
. * Storage Key Types: You can create Bloom filter indexes for all columns in Duplicate Key or Primary Key tables. For Aggregate or Unique Key tables, you can only create them for key columns. * Data Types: Supported types include 
INT
, 
BIGINT
, 
VARCHAR
, 
STRING
, 
DATE
, 
DATETIME
, etc. 4. How to verify they are being used Once the 
ALTER TABLE
task is finished, you can verify if your queries are actually hitting the Bloom filter index by checking the Query Profile. Look for the metric: * `BloomFilterFilterRows`: This indicates how many rows were filtered out specifically by the Bloom filter index. If you have a specific query or scenario in mind ("the above"), feel free to share more details! References • table_design/indexes/Bloomfilter_index.md • knowledge/usage/best-practice.md • table_design/indexes/Ngram_Bloom_Filter_Index.md
Yes, it will still work. In StarRocks, the 
bloom_filter_columns
property is a table-level setting that you can modify at any time. If your table currently has no bloom filter indexes, you can simply use the 
ALTER TABLE
command to define them for the first time. How it works: * Creating the first index: If the property is currently empty or not set, running 
ALTER TABLE <table_name> SET ("bloom_filter_columns" = "column1, column2");
will initiate an asynchronous job to build the bloom filter indexes for those specific columns. * Query functionality: Your queries will always work regardless of whether bloom filter indexes exist. The indexes are purely a performance optimization to help StarRocks skip scanning data files that definitely don't contain the values you're looking for (using 
=
or 
IN
operators). * Asynchronous process: After you run the command, StarRocks starts a background task to generate the index for existing data. You can check the progress using:
Copy code
sql
    SHOW ALTER TABLE COLUMN;
Important Notes: 1. Table Types: You can add bloom filters to any column in Duplicate Key or Primary Key tables. For Aggregate or Unique Key tables, you can only add them to Key columns. 2. Performance: If you add them to a table with a lot of existing data, the background job will take some time and consume some IO/CPU resources while it builds the index files. 3. Check if they are active: Once the alter job is finished, you can verify they are being used by checking the 
BloomFilterFilterRows
metric in a query profile. Is there a specific column type or table size you are concerned about? References • knowledge/usage/best-practice.md • table_design/indexes/Bloomfilter_index.md • table_design/indexes/Ngram_Bloom_Filter_Index.md
c
@Rocky does increasing compute nodes in shared architecture cluster by 3x impact metadata and query performance?
r
In a StarRocks shared-data (disaggregated) architecture, tripling the number of compute nodes (CNs) has distinct effects on metadata and query performance. Because compute and storage are decoupled, this scaling is highly elastic and efficient. 1. Impact on Metadata Performance Increasing the number of CN nodes has minimal to no direct impact on the volume or storage of metadata, but it introduces a slight overhead in management: * Metadata Storage: Metadata (table schemas, partitions, permissions) is managed by the Frontend (FE) nodes and stored in their memory (replicated via BDB JE). Adding CNs does not increase the size of this metadata. * Management Overhead: Each CN node sends heartbeats to the FE. Tripling the CNs increases the number of heartbeats the FE must process. While this is generally low impact, in extremely large clusters (hundreds of nodes), it can slightly increase the FE's CPU utilization for cluster management. * Scheduling Load: The FE is responsible for query scheduling. With 3x more CNs, the FE has a larger pool of resources to coordinate. If you also increase query concurrency to match the new capacity, the FE's query planning and scheduling tasks will increase accordingly. 2. Impact on Query Performance Tripling your CN nodes typically leads to a significant improvement in query performance, especially for heavy workloads: * Higher Parallelism: For large, complex queries, StarRocks can distribute the workload across more nodes. With 3x the nodes, you have 3x the total CPU and memory available for execution, leading to lower latency for resource-intensive queries. * Increased Concurrency: The cluster can handle a much higher volume of simultaneous queries without queuing or resource contention. * Zero Data Rebalancing: Unlike shared-nothing architectures, adding CNs does NOT trigger data rebalancing. Since data resides in remote storage (S3, HDFS, etc.), new CNs are ready to work the moment they are added. * Data Cache Warming (The "Cold Start" Effect): * Initial Impact: New CN nodes start with an empty local disk cache. The first few queries assigned to these new nodes will fetch data from remote storage, which is slower than reading from a local cache. * Stabilization: As the new CNs process more queries, they will populate their local caches with "hot" data. Once the cache is warmed, performance typically matches or exceeds the original cluster state. Summary Table Feature | Impact of 3x CN Nodes | Reason Metadata Size | None | Metadata is stored in FE memory, independent of compute nodes. FE CPU/Memory | Slight Increase | More heartbeats and scheduling overhead for the FE Leader. Query Latency | Decreases | More CPU/RAM available for parallel processing. Query Throughput | Increases | Cluster can process significantly more concurrent requests. Data Rebalancing | None | Shared-data architecture separates storage from compute. Rocky's Tip: If you triple your CN nodes to handle higher concurrency, ensure your FE nodes have sufficient CPU and memory, as they may become the bottleneck for query planning before the CNs hit their limits. You can add FE Observer nodes to scale query planning and connection handling if needed! References • introduction/Architecture.md • faq/operation_maintenance_faq.md • administration/Meta_recovery.md • deployment/plan_cluster.md • quick_start/shared-data.md • knowledge/trouble_shooting/fe_memory_problems.md
c
@Rocky does
Copy code
cache select <...>
statement downloads table data uniformly on all compute nodes?
r
The 
CACHE SELECT
statement in StarRocks is used for Block Cache Warmup (also known as Data Cache). When you execute 
CACHE SELECT
, StarRocks does not download the entire table onto every single node. Instead, it distributes the data blocks across all active compute nodes (BEs or CNs). Here is how the distribution and downloading process works: 1. Sharded Distribution (Consistent Hashing) StarRocks uses a consistent hashing mechanism to determine which node is responsible for which data block. • The data files are split into smaller blocks (typically 1MB by default). • Each block is assigned to a specific compute node based on its unique cache key (derived from the filename and block ID). • This ensures that each piece of data is cached on exactly one node (StarRocks currently does not support multiple replicas for cached data). 2. Distributed Downloading When you run 
CACHE SELECT <...>
, the process is as follows: • The FE (Frontend) analyzes which data blocks need to be warmed up. • The task is dispatched to all relevant BE/CN nodes. • Each node concurrently downloads only its assigned portion of the data from the remote storage (like HDFS or S3) to its local disk cache. • Because of the hashing strategy, the workload and the resulting disk usage are generally distributed uniformly across the cluster, provided your data files and blocks are numerous enough to be balanced by the hash function. 3. Verifying Uniformity You can verify how the data was distributed by using the 
verbose
property in your statement:
Copy code
sql
CACHE SELECT * FROM your_external_table PROPERTIES("verbose" = "true");
This will return a breakdown per node (by IP), showing: • `WRITE_CACHE_SIZE`: How much data that specific node downloaded and wrote to its local cache. • `TOTAL_CACHE_USAGE`: The current cache disk usage percentage on that node. Summary * Is it uniform? Yes, the workload is distributed across all nodes to ensure balanced resource usage and disk capacity. * Does every node get the same data? No. The table is sharded across the nodes. If you have 10 nodes and a 100GB table, each node will download and cache approximately 10GB of unique data. Note: If you notice significant differences in cache usage between nodes, it is often due to factors like nodes being added at different times, differences in the number of tablets/files, or existing data already present in the cache. References • using_starrocks/caching/query_cache.md • data_source/block_cache_warmup.md • deployment/shared_data/feature-support-shared-data.md
c
@Rocky does
Copy code
cache select <..>
need to be executed again if we increase compute nodes from 3 to 6? or will it rebalance cache automatically?
r
When you increase your compute nodes (e.g., from 3 to 6), the short answer is yes, you should execute 
CACHE SELECT
again if you want to ensure the new nodes are fully warmed up and provide optimal performance. Here is the detailed breakdown of why: 1. How Cache Routing Works StarRocks uses a consistent hashing mechanism to determine which node caches which data block (especially for Data Lake/Shared-data scenarios). When you scale the cluster from 3 nodes to 6 nodes: * The hash ring changes. * Data blocks that were previously assigned to nodes 1, 2, or 3 may now be assigned to the new nodes 4, 5, or 6. * The new nodes start with empty local disks, so they will experience cache misses for any data now assigned to them. 2. Does it rebalance automatically? No, the cache does not "rebalance" proactively in the background like tablets do. * Existing cached data on the original 3 nodes stays on their disks until it is evicted by the LRU/SLRU policy. * Data does not automatically migrate from node 1's disk to node 4's disk just because the cluster scaled. 3. The Role of "Cache Sharing" If you have Cache Sharing enabled (available in v3.3.0+), the impact of scaling is mitigated: * How it helps: If Node 4 receives a request for data it doesn't have, it will check if Node 1, 2, or 3 has it in their cache and fetch it over the internal network instead of going all the way back to remote storage (like S3/HDFS). * Config: To enable this, you need 
enable_datacache_sharing = true
and 
enable_trace_historical_node = true
. * Limitation: Even with Cache Sharing, fetching data from a peer node over the network is slower than reading it from a local NVMe disk. Summary Recommendation * Run 
CACHE SELECT
again: This is the best way to "re-warm" the cluster. It will trigger the new nodes to pull the data from remote storage (or peer nodes) and save it to their local disks according to the new distribution. * Automate: If you are using StarRocks v3.3+, you can use the Cache Warmup Job feature to schedule these warmups regularly, which makes handling scaling events much easier. Pro-tip: Before re-running the warmup, ensure your new nodes have enough disk space allocated for the cache (
datacache_disk_size
or 
starlet_star_cache_disk_size_percent
). References • data_source/data_cache.md • data_source/block_cache_warmup.md • deployment/shared_data/feature-support-shared-data.md • administration/management/Scale_up_down.md
c
@Rocky how to check FE health? what are good metrics to monitor apart from cpu and memory?
r
To check the health of a StarRocks Frontend (FE) and effectively monitor it beyond basic CPU and memory, you can use the following methods and metrics. 1. Quick Health Check (SQL & HTTP) * SQL Command: Connect to any FE via a MySQL client and run:
Copy code
sql
    SHOW PROC '/frontends'\G
* 
Alive
: Must be 
true
. If 
false
, the FE is down or disconnected. * 
Role
: One node should be 
LEADER
, others should be 
FOLLOWER
(or 
OBSERVER
). * 
ReplayedJournalId
: Followers should have a value close to the Leader's to ensure metadata is in sync. * HTTP API: You can check the status via the health API (returns 200 OK if healthy):
Copy code
bash
    curl http://<fe_ip>:<fe_http_port>/api/health
────────── 2. Key Metrics to Monitor (Beyond CPU/Memory) For production monitoring, StarRocks exposes metrics in Prometheus format at 
http://<fe_ip>:<fe_http_port>/metrics
. Here are the most critical FE-specific metrics: Metadata & Cluster Stability * 
starrocks_fe_bdb_log_count
(Meta Log Count): * Why: Measures the number of metadata log entries. * Threshold: Alert if it exceeds 100,000. High values usually mean the FE is failing to checkpoint (flush metadata to disk), which can lead to long restart times or OOM. * 
starrocks_fe_tablet_num
: * Why: Monitors the total number of tablets in the cluster. Too many tablets (millions) can strain FE metadata management. * 
starrocks_fe_connection_total
: * Why: Tracks the number of active MySQL client connections. Ensure it doesn't hit the 
qe_max_connection
limit (default 1024). Query Performance * 
starrocks_fe_query_total
& 
starrocks_fe_query_err
: * Why: Monitor QPS (Queries Per Second) and error rates. A sudden spike in errors often indicates cluster-wide issues. * 
starrocks_fe_query_latency
(P95/P99): * Why: Essential for tracking user experience. Monitor the 95th and 99th percentile latencies. * 
starrocks_fe_unfinished_query
: * Why: High numbers indicate queries are queuing or hanging, often due to BE resource exhaustion or lock contention. JVM & Garbage Collection * 
starrocks_fe_jvm_heap_usage_percent
: * Why: Alert if usage is
= 80%
. Since the FE is Java-based, high heap usage leads to frequent Stop-the-World GC pauses, which can cause the FE to "freeze" and lose leadership. * 
jvm_gc_pause_seconds_sum
: * Why: Monitor total GC pause time. Long pauses trigger cluster instability and RPC timeouts. Transaction & Loading * 
starrocks_fe_txn_publish_latency_ms
: * Why: Tracks how long it takes for data to become visible after a commit. High latency here usually points to slow BEs or metadata pressure. * 
starrocks_fe_slow_lock_wait_time_ms
: * Why: Tracks internal FE lock contention. If this spikes, it means metadata operations (like schema changes or tablet reports) are blocking each other. Recommended Tooling It is highly recommended to use the official StarRocks Grafana Dashboard, which pre-configures these metrics into logical groups for easy visualization. References • administration/management/monitoring/metrics.md • administration/management/monitoring/Monitor_and_Alert.md
c
@Rocky is bitmap_max_filter_ratio applicable in starrocks 4.1.* version?
r
Hi! I'm Rocky. As of now, StarRocks has not released a version 4.1. The current stable versions are in the 3.x series (with 3.3 being the most recent major release). Regarding the parameter 
bitmap_max_filter_ratio
, this is not a standard configuration or session variable in StarRocks. It is possible you might be looking for one of the following related features or settings: 1. Runtime Filters: StarRocks uses runtime filters (including Bloom Filter, Min/Max Filter, and Bitmap Filter) to optimize Join performance. You can control these using: * `runtime_filter_type`: To specify the type (e.g., 
IN
, 
BLOOM_FILTER
, 
BITMAP
). * `runtime_filter_scan_wait_timeout`: To set how long a scan node waits for the filter. 2. Bitmap Indexes: If you are looking to optimize filtering on specific columns using bitmap indexes, you define those at the table schema level. 3. Variable Name Discrepancy: If you are migrating or comparing from another system, please note that StarRocks may use different naming conventions for its optimization flags. Could you please verify the version number or describe the optimization you are trying to achieve? I'd be happy to help you find the correct StarRocks equivalent!
c
@Rocky how to get starrocks version from mysql cli?
r
Hi! I'm Rocky. To get the StarRocks version from a MySQL CLI, you can use any of the following methods: 1. Using the 
VERSION()
function:
Copy code
sql
   SELECT VERSION();
2. Using `SHOW VARIABLES`:
Copy code
sql
   SHOW VARIABLES LIKE 'version%';
This will return several version-related variables, including the 
version
(which often includes the StarRocks version string) and 
version_comment
. 3. Check the connection greeting: When you first connect to StarRocks via the MySQL client, the version is typically displayed in the welcome message:
Copy code
bash
   mysql -h <fe_host> -P <fe_query_port> -u <user>
Look for a line like: 
Server version: 5.1.0-StarRocks-x.y.z
 Let me know if you need anything else!
c
@Rocky getting
Copy code
Server version: 8.0.33 3.5.13-dca7c0a
r
Hello! It looks like you're sharing the version information for your StarRocks cluster. From the string `Server version: 8.0.33 3.5.13-dca7c0a`: * 8.0.33: This is the MySQL protocol version that StarRocks uses for compatibility with MySQL clients. * 3.5.13: This is your StarRocks version. * dca7c0a: This is the specific git commit hash for that build. Did you have a specific question about this version, or are you encountering a query error or performance issue that you'd like me to look into? If you're seeing an error, please share the full error message and the query you're running!
c
@Rocky is bitmap_max_filter_ratio applicable in starrocks 3.5.13 version?
r
Yes, the 
bitmap_max_filter_ratio
session variable is applicable in StarRocks 3.5.x, including version 3.5.13. What it does: This variable is used by the query optimizer to decide whether to use a Bitmap Index for a specific query. * Threshold: It defines a threshold for the selectivity of the filter. * Logic: If the estimated ratio of rows that match the filter condition exceeds this value, the optimizer may decide not to use the bitmap index. This is because if a filter isn't selective enough (i.e., it matches too many rows), reading the index and then the data can sometimes be slower than a direct scan. Default Value: The default value is typically 
1000
. The ratio is calculated out of 
10000
, so 
1000
corresponds to 10%. How to use it: You can check or modify this variable at the session or global level: 1. Check the current value:
Copy code
sql
SHOW VARIABLES LIKE 'bitmap_max_filter_ratio';
2. Set for the current session: If you find that a bitmap index is not being used for a query where it should be, you can try increasing this ratio:
Copy code
sql
SET bitmap_max_filter_ratio = 2000; -- Increase to 20%
3. Set globally:
Copy code
sql
SET GLOBAL bitmap_max_filter_ratio = 2000;
In StarRocks 3.x, the cost-based optimizer (CBO) is very mature, but this hint/variable remains a useful knob for fine-tuning performance when dealing with large-scale bitmap index scans.
c
@Rocky getting
Copy code
ERROR 1193 (HY000): Unknown system variable 'bitmap_max_filter_ratio', the most similar variables are {'insert_max_filter_ratio', 'cbo_cte_max_limit', 'enable_min_max_optimization'}
r
It looks like you're trying to set a system variable that doesn't exist in StarRocks. The error message 
Unknown system variable 'bitmap_max_filter_ratio'
confirms this. Based on the name, you are likely looking for one of the following: 1. 
insert_max_filter_ratio
(System Variable) If you are performing an 
INSERT INTO ... SELECT
and want to define how many bad rows (e.g., due to data type mismatch) are allowed before the task fails, use:
Copy code
sql
SET insert_max_filter_ratio = 0.1; -- Allows up to 10% of rows to be filtered
This is a session or global variable that applies to 
INSERT
operations. 2. 
max_filter_ratio
(Load Property) If you are performing a Stream Load, Broker Load, or Routine Load, this is not a system variable but a property defined within the load command itself. * Stream Load: Passed in the header 
-H "max_filter_ratio:0.1"
* Broker Load: Defined in the 
PROPERTIES
block:
Copy code
sql
    PROPERTIES (
        "max_filter_ratio" = "0.1"
    );
3. Runtime Filter Variables If your goal was related to performance tuning of Bitmap Runtime Filters, you might be looking for: * `runtime_filter_mode`: To enable/disable runtime filters (SET to 
GLOBAL
or 
LOCAL
). * `runtime_bloom_filter_max_size`: To adjust the maximum size of the bloom filters generated. Recommendation: Check if you intended to use 
insert_max_filter_ratio
. You can verify the available variables in your version by running:
Copy code
sql
SHOW VARIABLES LIKE '%filter_ratio%';
The error message indicates that 
bitmap_max_filter_ratio
is not a recognized system variable in StarRocks. This variable name is often confused with variables from other systems. Depending on what you are trying to achieve, you likely want one of the following: 1. Data Loading Error Tolerance If you are trying to set the maximum percentage of "bad" rows (rows that fail to parse) allowed during an 
INSERT
or a data load before the entire task fails, the correct variable is:
Copy code
sql
SET insert_max_filter_ratio = 0.05; -- Allows 5% error rows
2. Runtime Filter Configuration If you were trying to adjust how StarRocks handles bitmap-based filters during query execution (Runtime Filters), StarRocks uses a different set of variables. You can control runtime filters using: * 
runtime_filter_mode
: Controls the level of runtime filters (OFF, LOCAL, GLOBAL). * 
runtime_filter_type
: Specifies the type of runtime filter (e.g., Bloom Filter, Min/Max). * 
runtime_filter_max_size_in_bytes
: Sets the maximum size for the runtime filter. Example:
Copy code
sql
SET runtime_filter_mode = 'GLOBAL';
3. How to check available variables If you want to see all available variables related to "filter" or "bitmap" to find the one you need, you can run:
Copy code
sql
SHOW VARIABLES LIKE '%filter%';
SHOW VARIABLES LIKE '%bitmap%';
Rocky's Tip: If you are migrating a script or configuration from another environment, keep in mind that StarRocks has its own optimized set of system variables. Always check the current version's documentation or use 
SHOW VARIABLES
to confirm the exact naming.
c
@Rocky explain and optimize
Copy code
Fragment 3                                                                                                                                           |
| │   BackendNum: 1                                                                                                                                    |
| │   InstancePeakMemoryUsage: 91.984 KB, InstanceAllocatedMemoryUsage: 298.406 KB                                                                     |
| │   PrepareTime: ?                                                                                                                                   |
| └──DATA_STREAM_SINK (id=4)                                                                                                                           |
|    │   PartitionType: UNPARTITIONED                                                                                                                  |
|    └──PROJECT (id=3)                                                                                                                                 |
|       │   Estimates: [row: ?, cpu: ?, memory: ?, network: ?, cost: ?]                                                                                |
|       │   TotalTime: 4.231us (0.06%) [CPUTime: 4.231us]                                                                                              |
|       │   OutputRows: 1                                                                                                                              |
|       │   Expression: [11: etl_ts]                                                                                                                   |
|       └──OLAP_SCAN (id=2)                                                                                                                            |
|              Estimates: [row: 1, cpu: 16.48, memory: 0.00, network: 0.00, cost: 8.24]                                                                |
|              TotalTime: 718.503us (10.98%) [CPUTime: 275.805us, ScanTime: 442.698us]                                                                 |
|              OutputRows: 1                                                                                                                           |
|              Table: : table1                                                                              |
|              SubordinateOperators:                                                                                                                   |
|                  CHUNK_ACCUMULATE
r
Hi! I'm Rocky. Let's break down this fragment of your StarRocks query profile and look for optimization opportunities. Explanation of Fragment 3 This fragment represents a data retrieval operation from a table named 
table1
. Here is what each part means: 1. OLAP_SCAN (id=2): * This is the leaf operator that reads data from the StarRocks storage engine. * OutputRows: 1: Only one row was read and passed up. This suggests a highly selective query (like a point lookup with a primary key, a 
LIMIT 1
, or an aggregation that resulted in one row). * ScanTime: 442.698us: The actual time spent reading from the disk/cache. This is extremely fast (under 0.5 milliseconds). * CHUNK_ACCUMULATE: This is an internal operator that buffers small batches of rows into larger "chunks" to make vectorized processing more efficient. 2. PROJECT (id=3): * Expression: [11: etl_ts]: This operator selects only the 
etl_ts
column (identified by ID 11). It discards any other columns read during the scan to save memory and CPU. * TotalTime: 4.231us: The overhead for this projection is negligible. 3. DATA_STREAM_SINK (id=4): * PartitionType: UNPARTITIONED: This operator sends the resulting 1 row to the next fragment (likely the "Global Merge" or the final result output). Since it’s unpartitioned, it's gathering data to a single destination. 4. Resource Usage: * InstancePeakMemoryUsage: 91.984 KB: The memory footprint is very small, which is excellent. ────────── Optimization Analysis To be honest, this fragment is already performing exceptionally well. It completed the entire scan and projection in about 0.7 milliseconds. However, if you are looking to ensure this stays fast as your data grows, here are a few things to check: 1. Verify Pruning (Partition & Bucket) In the full profile (higher up from this snippet), check if StarRocks is pruning partitions and buckets. * Why: If 
table1
is partitioned by a time column and your query includes a 
WHERE
clause on that column, StarRocks should only scan the relevant partitions. * What to look for: Check the 
OLAP_SCAN
details in the full profile for 
PartitionStorageNum
vs 
TotalPartitionNum
. 2. Indexing If this 
OLAP_SCAN
is part of a frequent query: * Short Key Index: Ensure the column used in your 
WHERE
clause is at the beginning of your 
DUPLICATE KEY
or 
PRIMARY KEY
definition. This allows for extremely fast binary search within the data files. * Bloom Filter: If you are filtering on a high-cardinality column (like a UUID) that isn't the first column of your short key, adding a Bloom Filter index can skip reading data blocks entirely. 3. Column Data Type * The column 
etl_ts
is being projected. Ensure this column is using the most efficient type (e.g., 
DATETIME
or 
BIGINT
instead of 
STRING
) to reduce the I/O and processing overhead. 4. Is this a "Point Lookup"? If you are doing many single-row lookups (based on a primary key), ensure you are using the Primary Key Storage Engine. It is specifically optimized for these types of high-concurrency, low-latency scans and performs better than the Duplicate Key engine for this specific pattern. Summary: Your current performance is sub-millisecond, which is the "gold standard" for analytical queries. Unless this fragment is being called millions of times per second, no immediate changes are necessary!
c
@Rocky here's the full profile. why scan is unpartitioned even when both table1 and table2 are partitioned on org_name and org_name is passed in where clause.
Untitled
r
Hi! I'm Rocky. It can be frustrating when partition pruning doesn't seem to kick in as expected. While I can't see the attached profile directly, here are the most common reasons why an OlapScanNode might not show partition pruning in StarRocks, even if the partition column is in the 
WHERE
clause: 1. Expressions on the Partition Column If you wrap the partition column in a function, partition pruning will be disabled. * Doesn't work: 
WHERE upper(org_name) = 'MY_ORG'
* Works: 
WHERE org_name = 'MY_ORG'
2. Implicit Type Conversion Ensure the data type of the value in your 
WHERE
clause matches the table definition exactly. If 
org_name
is a 
VARCHAR
but you are comparing it against a type that forces a cast on the column side, pruning may fail. 3. "Unpartitioned" might refer to Data Distribution, not Pruning In a StarRocks query profile, you might see the term "UNPARTITIONED" in an 
Exchange
node or a 
DataStreamSink
. This usually refers to how data is being shuffled between BE nodes (e.g., a Broadcast Join or a result being gathered to the FE), rather than whether partition pruning happened at the scan level. * Check the OlapScanNode section of the profile specifically. Look for 
PartitionStorageNames
or 
SelectedPartitions
. If 
SelectedPartitions
is significantly smaller than the total partitions, pruning is working. 4. Partitioning vs. Bucketing (Distribution) Are you sure 
org_name
is the Partition Key and not just the Distribution Key (Bucket Key)? * Partitioning (e.g., 
PARTITION BY LIST(org_name)
) allows the scanner to skip entire directories/files. * Bucketing (e.g., 
DISTRIBUTED BY HASH(org_name)
) determines which BE nodes store which rows. If it is only a distribution key, you will see "Tablet Pruning" instead of "Partition Pruning." 5. Colocate Join If you are joining 
table1
and 
table2
on 
org_name
and expect a performance boost because they are both partitioned/distributed on that column, you should ensure they belong to the same Colocation Group. Without this, StarRocks may still shuffle the data, which might be why you see "Unpartitioned" in the exchange nodes. 6. Join Reordering or Predicate Pushdown In some complex queries involving multiple joins or CTEs, if the filter 
org_name = '...'
is applied after a join rather than being pushed down to the scan, the scan will read all partitions. You can check the 
Explain
plan to see if the 
PREDICATES
are actually located within the 
OlapScanNode
. To help you further, could you check the 
EXPLAIN
output of the query? Look for the 
OlapScanNode
and see if the 
partition
field lists specific partitions or says 
ALL
. Also, verify if the 
org_name
predicate appears in the 
partition predicates
section.
c
@Rocky why index is not getting used in below profile?
Copy code
Summary                                                                                                                                                  |
|     QueryId: d2e19549-16ef-11f1-ba42-c6fd0248bd3e                                                                                                        |
|     Version: 3.5.13-dca7c0a                                                                                                                              |
|     State: Finished                                                                                                                                      |
|     TotalTime: 181ms                                                                                                                                     |
|         ExecutionTime: 164.762ms [Scan: 79.974ms (48.54%), Network: 713.461us (0.43%), ResultDeliverTime: 0ns (0.00%), ScheduleTime: 154.237ms (93.61%)] |
|         CollectProfileTime: 5ms                                                                                                                          |
|         FrontendProfileMergeTime: 1.578ms                                                                                                                |
|     QueryPeakMemoryUsage: ?, QueryAllocatedMemoryUsage: 5.882 GB                                                                                         |
|     Top Most Time-consuming Nodes:                                                                                                                       |
|         1. OLAP_SCAN (id=0) : 126.589ms (99.23%)                                                                                                    |
|         2. EXCHANGE (id=2) : 871.052us (0.68%)                                                                                                           |
|         3. RESULT_SINK: 74.717us (0.06%)                                                                                                                 |
|         4. PROJECT (id=1) : 30.139us (0.02%)                                                                                                             |
|     Top Most Memory-consuming Nodes:                                                                                                                     |
|     NonDefaultVariables:                                                                                                                                 |
|         enable_adaptive_sink_dop: false -> true                                                                                                          |
|         enable_async_profile: true -> false                                                                                                              |
|         enable_profile: false -> true                                                                                                                    |
|         parallel_fragment_exec_instance_num: 1 -> 48                                                                                                     |
|         pipeline_dop: 0 -> 64                                                                                                                            |
|         query_timeout: 300 -> 3600                                                                                                                       |
| Fragment 0                                                                                                                                               |
| │   BackendNum: 1                                                                                                                                        |
| │   InstancePeakMemoryUsage: 1.029 MB, InstanceAllocatedMemoryUsage: 1.410 MB                                                                            |
| │   PrepareTime: ?                                                                                                                                       |
| └──RESULT_SINK                                                                                                                                           |
|    │   TotalTime: 74.717us (0.06%) [CPUTime: 74.717us]                                                                                                   |
|    │   OutputRows: 3                                                                                                                                     |
|    │   SinkType: MYSQL_PROTOCAL                                                                                                                          |
|    └──EXCHANGE (id=2)                                                                                                                                    |
|           Estimates: [row: ?, cpu: ?, memory: ?, network: ?, cost: ?]                                                                                    |
|           TotalTime: 871.052us (0.68%) [CPUTime: 157.591us, NetworkTime: 713.461us]                                                                      |
|           OutputRows: 3                                                                                                                                  |
|           PeakMemory: ?, AllocatedMemory: ?                                                                                                              |
|                                                                                                                                                              |
| Fragment 1                                                                                                                                               |
| │   BackendNum: 1                                                                                                                                        |
| │   InstancePeakMemoryUsage: 35.213 MB, InstanceAllocatedMemoryUsage: 5.881 GB                                                                           |
| │   PrepareTime: ?                                                                                                                                       |
| └──DATA_STREAM_SINK (id=2)                                                                                                                               |
|    │   PartitionType: UNPARTITIONED                                                                                                                      |
|    └──PROJECT (id=1)                                                                                                                                     |
|       │   Estimates: [row: ?, cpu: ?, memory: ?, network: ?, cost: ?]                                                                                    |
|       │   TotalTime: 30.139us (0.02%) [CPUTime: 30.139us]                                                                                                |
|       │   OutputRows: 3                                                                                                                                  |
|       │   Expression: [1: id, 11: city, 12: state, 13: country, 18: slintel_company_id, 19: mid, ...]                                                    |
|       └──OLAP_SCAN (id=0)                                                                                                                           |
|              Estimates: [row: 2, cpu: 604.40, memory: 0.00, network: 0.00, cost: 302.20]                                                            |
|              TotalTime: 126.589ms (99.23%) [CPUTime: 46.615ms, ScanTime: 79.974ms]                                                                  |
|              OutputRows: 3                                                                                                                          |
|              Table: : table1                                                                                                     |
|              SubordinateOperators:                                                                                                                  |
|                  CHUNK_ACCUMULATE                                                                                                                   |
|              Detail Timers: [ScanTime = IOTaskExecTime + IOTaskWaitTime]                                                                            |
|                  IOTaskExecTime: 12.709ms [min=457.044us, max=52.259ms]                                                                             |
|                      SegmentInit: 9.833ms [min=149.790us, max=49.755ms]                                                                             |
|                          ColumnIteratorInit: 8.876ms [min=52.738us, max=39.315ms]                                                                   |
|                  IOTaskWaitTime: 13.602ms [min=46.190us, max=66.679ms]
r
Hello! I'm Rocky. Looking at the profile snippet you provided, it's actually missing the "Rows" statistics section under the 
OLAP_SCAN
node, which is where StarRocks reports index usage. To confirm if an index is being used, you need to look for specific counters in the full profile under 
OLAP_SCAN
. If these counters are 0 or missing, here is why that might be happening. 1. Key Counters to Check In the 
OLAP_SCAN
section of a complete profile, look for: * 
ShortKeyFilterRows
: Rows filtered by the Prefix Index (Sort Key). * 
BitmapIndexFilterRows
: Rows filtered by a Bitmap Index. * 
BloomFilterFilterRows
: Rows filtered by a Bloom Filter Index. * 
ZoneMapIndexFilterRows
: Rows filtered by Zone Maps (Min/Max values stored in metadata). * 
RowsInvertedIndexFiltered
: Rows filtered by an Inverted Index. 2. Common Reasons Indexes are Skipped If you don't see these counters or they are 0, check the following: * Prefix Index (Sort Key) Order: The Prefix Index only works if you use the leading columns of your 
DUPLICATE/PRIMARY KEY
. If your key is 
(A, B, C)
and your query is 
WHERE B = 1
, the prefix index cannot be used efficiently. * Functions on Columns: If you wrap your column in a function, like 
WHERE UPPER(city) = 'NEW YORK'
, the index on 
city
will be bypassed. * Data Type Mismatch: If the column is a 
VARCHAR
and you compare it to an integer (e.g., 
WHERE string_col = 123
), the implicit type conversion may prevent index usage. * Unsupported Operators: Bloom filters only support equality (
=
) and 
IN
predicates. They do not support range queries (
>
, 
<
) or 
LIKE
. * Small Data Volume: From your profile, 
OutputRows
is only 3 and 
IOTaskExecTime
is very low (12ms). If the table is very small or the data is cached in the Page Cache, StarRocks might perform a fast scan that completes before index structures are even fully leveraged. * Low Selectivity: The optimizer might skip certain indexes if it determines that a full scan is more efficient than the overhead of loading index pages. Recommendation To give you a more precise answer, could you provide: 1. The SQL query you are running. 2. The Table Schema (the 
CREATE TABLE
statement). 3. The Filter section of the profile (look further down in the 
OLAP_SCAN
node for the "Rows" counters). If you are using a version like 3.x (as seen in your version 
3.5.13
), ensure that your most frequently filtered columns are part of your Sort Key for the best performance!
c
@Rocky how to increase memory limit?
Copy code
ERROR 1064 (HY000): Memory of process exceed limit. Pipeline Backend: kube-starrocks-cn-0.kube-starrocks-cn-search.starrocks.svc.cluster.local, fragment: e09a436f-1881-11f1-b165-f2341dca6183 Used: 95671183800, Limit: 95670396518. Mem usage has exceed the limit of BE: BE:10001
r
Based on the error message 
Mem usage has exceed the limit of BE
, your StarRocks Compute Node (CN) process has reached its total allowed memory limit. The current limit is set to approximately 89 GB (
95670396518
bytes), and the process tried to use more. Since your hostnames indicate you are running on Kubernetes (
kube-starrocks-cn-0
), here is how to resolve this: 1. Increase Kubernetes Resource Limits The most likely cause is that your Kubernetes Pod has a memory limit defined in its specification. StarRocks (by default) sets its internal 
mem_limit
to 90% of the container's available memory. To increase this, update your 
StarRocksCluster
YAML (if using the Operator) or your Helm `values.yaml`:
Copy code
yaml
spec:
  starRocksCnSpec: # or starRocksBeSpec
    resources:
      requests:
        memory: "128Gi" # Increase this value
      limits:
        memory: "128Gi" # Increase this value
2. Adjust the 
mem_limit
Parameter If you cannot increase the total physical memory of the machine/container, you can check if 
mem_limit
is explicitly restricted in your configuration (
cn.conf
or 
be.conf
). * Parameter: 
mem_limit
* Default: 
90%
(of the total physical memory) * How to change: You can set it to a higher percentage or a specific value like 
100G
. * Location: In Kubernetes, this is usually managed via a 
ConfigMap
or the 
config
section of your CRD/Helm chart. 3. Check for Single Query Memory Hogs If this error happens during a specific query, that query might be consuming too much memory. While the error you see is for the total BE/CN limit, you can restrict individual queries to prevent one query from crashing the whole node: * Session Variable: 
exec_mem_limit
* Usage: 
SET exec_mem_limit = 10737418240; -- 10GB
* Note: If a query hits this limit, it will fail with a "Single query memory limit exceeded" error instead of hitting the "Limit of BE" error. 4. Troubleshoot Memory Usage To see exactly what is consuming memory inside the process, you can use the internal memory tracker. Run the following command from a machine that can reach the CN/BE pod:
Copy code
bash
curl -XGET http://<CN_IP>:<CN_HTTP_PORT>/mem_tracker
(Default HTTP port is 
8040
for BE/CN) This will return a breakdown of memory usage by various components (like Cache, Compaction, Load, and Query). Summary of common fixes: 1. K8s: Increase 
resources.limits.memory
in your deployment manifest. 2. Config: Ensure 
mem_limit
in `cn.conf`/`be.conf` is set to 
90%
(default) or higher if no other processes are running on the node. 3. Scale: If your workload genuinely needs more than the available node memory, consider scaling out by adding more CN/BE nodes. References • administration/management/BE_configuration.md • deployment/sr_operator.md • administration/management/resource_management/Memory_management.md • quick_start/helm.md • deployment/environment_configurations.md
c
@Rocky how to share full query profile here for analysis?
r
To share a full query profile for analysis, you can use any of the following methods. The Web UI or the 
get_query_profile
 function are generally the most common ways to get the detailed execution data. 1. Using the SQL Function (Best for Copy/Paste) If you are using a SQL client, you can retrieve the profile directly as text: 1. Enable profiling for your session:
Copy code
sql
    SET enable_profile = true;
2. Run your query. 3. Get the Query ID of the last executed query:
Copy code
sql
    SELECT last_query_id();
4. Retrieve the profile text:
Copy code
sql
    -- Replace <query_id> with the result from the previous step
    SELECT get_query_profile('<query_id>');
Note: If your client truncates text, you might need to use 
\G
(in MySQL CLI) or export the result to a file. ────────── 2. Using the Web UI (Best for Visualization) StarRocks provides a built-in visualizer that is very helpful for identifying bottlenecks: 1. Open your browser and go to 
http://<fe_ip>:<fe_http_port>
(default port is 
8030
). 2. Click on the "queries" tab in the top navigation bar. 3. Find your query in the list and click the link in the "Profile" column. 4. You can take a screenshot of the visual plan or copy the "Text Profile" provided on that page. ────────── 3. Using 
EXPLAIN ANALYZE
 If you want to run the query and get the profile output immediately in one go:
Copy code
sql
EXPLAIN ANALYZE <YOUR_SQL_QUERY>;
This will execute the query and return a formatted text profile showing where time was spent in each operator. ────────── 4. For Deep Analysis: Query Dump If you are reporting a bug or a complex optimization issue, a Query Dump is often requested because it includes the schema, statistics, and session variables:
Copy code
bash
curl --user <username>:<password> \
     --data-binary @"query.sql" \
     "http://<fe_ip>:<fe_http_port>/api/query_dump?db=<database_name>" \
     > query_dump.json
How to share it here: * Small Profiles: Wrap the text in triple backticks (\`\`\`) to keep it readable. * Large Profiles: Attach the output as a 
.txt
file or use a snippet tool. * Visuals: Screenshots of the Web UI's "Fragment" or "Operator" view are always welcome! I'm ready to take a look once you have it! References • best_practices/query_tuning/query_profile_overview.md • best_practices/query_tuning/query_profile_text_based_analysis.md • faq/Sql_faq.md • faq/Dump_query.md
c
@Rocky how to share query profile to @Rocky for performance improvements
r
To share a query profile for performance analysis in StarRocks, you can use several methods depending on whether you are sharing with your team or StarRocks technical support. 1. Enable Profiling Before you can share a profile, you must ensure it is enabled for your session:
Copy code
sql
SET enable_profile = true;
-- Run your query here
2. Methods to Obtain and Share the Profile Method A: Using SQL (Best for quick sharing) You can retrieve the profile as a text block directly from your SQL editor. 1. Get the 
query_id
of your last query:
Copy code
sql
   SELECT last_query_id();
2. Fetch the profile:
Copy code
sql
   SELECT get_query_profile('<query_id>');
3. To Share: Copy the entire output text and save it as a 
.txt
file or paste it into a code block. Method B: Using the FE Web UI (Best for visual analysis) 1. Open your browser and go to 
http://<FE_IP>:<FE_HTTP_PORT>
(default port is 
8030
). 2. Click on the Queries tab in the top navigation. 3. Find your query in the list and click the link in the Profile column. 4. To Share: You can copy the text from the profile page or take screenshots of the visualized metrics if using the Enterprise/Managed version. Method C: Using 
query_dump
(Recommended for Support) If you are sharing with technical support, a "Query Dump" is the most helpful format because it includes not just the profile, but also the table schema, session variables, and statistics. 1. Use a 
curl
or 
wget
command to generate the dump:
Copy code
bash
   wget --user=<username> --password=<password> \
   --post-file query.sql \
   "http://<FE_IP>:<FE_HTTP_PORT>/api/query_dump?db=<db_name>" \
   -O query_dump.json
2. To Share: Send the resulting 
query_dump.json
file. 3. What to include for Performance Tuning If you are asking for help with optimization, it is best to provide: * The Query Profile: (Obtained via the methods above). * Explain Plan: Run 
EXPLAIN COSTS <your_query>;
and share the output. * Table Schema: The result of 
SHOW CREATE TABLE <table_name>;
. * Data Volume: Approximate row counts for the tables involved. Summary of useful commands: Feature | Command Enable Profile | 
SET enable_profile = true;
Get Query ID | 
SELECT last_query_id();
Get Text Profile | 
SELECT get_query_profile('id');
List Recent Profiles | 
SHOW PROFILELIST;
Rocky's Tip: If the query is running for a long time and hasn't finished, you can use Runtime Profile (v3.1+) to see progress by setting 
SET runtime_profile_report_interval = 10;
. This helps identify bottlenecks while the query is still active! References • faq/Dump_query.md • administration/management/logs.md • faq/Sql_faq.md • unloading/arrow_flight.md
c
@Rocky does index_page_cache_size property work in shared architecture mode in 3.5.x version?
r
Hi, I'm Rocky! In StarRocks 3.5.x shared-data architecture (shared storage mode), there isn't actually a parameter named 
index_page_cache_size
. If you are looking to manage the memory used for caching index and data pages, you should look at the following parameters which do work and are highly recommended for performance in shared architecture: 1. Storage Page Cache (
storage_page_cache_limit
) The Page Cache is an in-memory cache that stores decompressed data and index pages (like zonemaps, ordinal indexes, and bitmap indexes). * Parameter: 
storage_page_cache_limit
(BE configuration). * Behavior in 3.5.x Shared-Data: It works exactly as it does in shared-nothing mode. When data is read from the remote storage (or from the local Data Cache), the decompressed pages are stored here to accelerate repeated scans. * Default: 20% of the BE's total memory. * Note: Index pages and data pages share this memory pool, and it is managed using an LRU (Least Recently Used) strategy. 2. Data Cache (StarCache) In shared-data mode, the Data Cache (sometimes called StarCache or Block Cache) is the most critical caching layer because it caches raw data blocks from S3/OSS/HDFS onto the BE's local disk or memory. * Parameter: 
datacache_enable
(or 
starlet_use_star_cache
in older 3.x sub-versions). * Memory vs. Disk: You can configure the Data Cache to use memory via 
datacache_mem_size
. * Functionality: It caches both data and index files from remote storage. 3. Primary Key Index Caching If you are using Primary Key tables in shared-data mode, the index is also stored on remote storage. StarRocks 3.5.x handles the caching of these indexes automatically using the Data Cache and a dedicated metadata cache. Summary for Version 3.5.x: If you want to ensure your indexes are being cached effectively in a shared-data cluster: 1. Ensure 
storage_page_cache_limit
is set appropriately (the default 20% is usually a good start). 2. Ensure Data Cache is enabled (
datacache_enable = true
). 3. Ensure the specific index caches are enabled (they are 
true
by default): * 
enable_zonemap_index_memory_page_cache
* 
enable_bitmap_index_memory_page_cache
* 
enable_ordinal_index_memory_page_cache
If you are seeing specific "cache miss" issues or performance degradation, I recommend checking the Query Profile for metrics like 
DataCacheReadBytes
and 
StoragePageCacheHitCount
. References • administration/management/BE_configuration.md • knowledge/usage/best-practice.md • data_source/data_cache.md • administration/management/monitoring/metrics.md
In StarRocks, there is no configuration parameter named 
index_page_cache_size
. Instead, index pages and data pages are collectively managed within the Page Cache. For StarRocks 3.5.x in shared architecture (shared-data mode), the relevant configurations to manage memory and disk caches are as follows: 1. Memory Page Cache (L1 Cache) The Page Cache stores decompressed data pages and index pages in memory to accelerate repeated queries. * Property: 
datacache_mem_size
* Description: This BE configuration sets the maximum memory usage limit for the Data Cache's memory portion (the Page Cache). You can set it as a percentage (e.g., 
10%
) or a specific size (e.g., 
10G
). * Applicability: In shared-data mode, this is the primary parameter for controlling memory-resident cache. 2. Disk Data Cache (L2 Cache) In shared-data clusters, StarRocks also uses a local disk cache (often called Block Cache or Starlet Star Cache) to store data fetched from remote storage (like S3 or HDFS). * Properties: 
datacache_disk_size
or 
starlet_star_cache_disk_size_percent
. * Description: Sets the disk capacity used for caching. The system will use the larger value of the two if both are set. 3. Enabling/Disabling Specific Index Caches While there isn't a "size" parameter just for indexes, you can control whether specific types of indexes are cached in memory using these BE boolean flags (all 
true
by default): * 
enable_bitmap_index_memory_page_cache
* 
enable_ordinal_index_memory_page_cache
* 
enable_zonemap_index_memory_page_cache
Summary for Version 3.5.x If you are trying to adjust the memory allocated to caching indexes in a shared-data cluster, you should use 
datacache_mem_size
 in your 
be.conf
. If you are using a shared-nothing cluster, the equivalent parameter is 
storage_page_cache_limit
. Both are fully supported in version 3.5.x. References • deployment/shared_data/feature-support-shared-data.md • data_source/data_cache.md • using_starrocks/caching/block_cache.md
c
@Rocky getting below error with 24 threaded concurrent connections
Copy code
Batch failed with exception: (2003, "Can't connect to MySQL server on '<http://starrocks.playground.6si.com:9030|starrocks.playground.6si.com:9030>' (111)")
r
The error 
(2003, "Can't connect to MySQL server on ... (111)")
with code 
111
specifically means "Connection Refused." This usually indicates that the client reached the host, but the Frontend (FE) service either isn't listening on port 9030, is crashing, or is rejecting the connection at the OS/Network level. Since this happens specifically during concurrent connections (24 threads), here are the most likely causes and troubleshooting steps: 1. Check FE Health and Logs If the FE is crashing or restarting when concurrency increases, you will see "Connection Refused." * Check FE process: Run 
ps aux | grep StarRocksFE
to ensure the process is still running. * Check FE Logs: Look at 
fe/log/fe.warn.log
or 
fe/log/fe.log
. Look for 
OutOfMemoryError
or any "Full GC" logs that might cause the FE to become unresponsive. 2. Increase OS-level Connection Limits With concurrent connections, the operating system's listen queue might be overflowing. * somaxconn: This limits the maximum number of connection requests queued for the listening socket.
Copy code
bash
    # Check current value (default is often 128)
    sysctl net.core.somaxconn
    # Increase it to 1024 or higher
    sudo sysctl -w net.core.somaxconn=1024
* File Descriptors (ulimit): Ensure the user running StarRocks has enough file descriptors.
Copy code
bash
    # Recommended in /etc/security/limits.conf
    * soft nofile 655350
    * hard nofile 655350
3. Check FE Connection Configuration StarRocks has internal limits on how many connections it can handle. * 
qe_max_connection
: This is the global limit for FE connections (Default is 1024 or 4096 depending on version). You can check this in 
fe.conf
. * 
max_user_connections
: Check if the specific user has a property limit.
Copy code
sql
    -- Check if there is a limit for your user
    SHOW PROPERTY FOR 'your_user';
    -- To increase it:
    ALTER USER 'your_user' SET PROPERTIES ('max_user_connections'='1000');
4. High "Wait" or Idle Connections If your 24 threads are opening and closing connections rapidly, you might be running out of local ephemeral ports or hitting the 
wait_timeout
. * Check how many connections are currently active: 
SHOW PROCESSLIST;
* Reduce 
wait_timeout
(default is 8 hours) to close idle connections faster:
Copy code
sql
    SET GLOBAL wait_timeout = 3600; -- 1 hour
5. Network / Load Balancer If you are using a Load Balancer or Proxy (like F5 or ProxySQL) in front of your FE: * Ensure the Load Balancer itself isn't reaching its max connection limit. * Check if the Load Balancer's health check is failing when the FE is under load. Recommendation: Start by checking the 
fe.warn.log
to see if the FE is struggling with memory or if it's logging any "Reach limit of connections" errors. If the logs are clean, focus on the OS 
somaxconn
 and network stability between your client and port 9030. References • faq/operation_maintenance_faq.md • administration/management/FE_configuration.md • deployment/environment_configurations.md • administration/management/monitoring/alert.md • administration/management/BE_configuration.md • sql-reference/System_variable.md
c
@Rocky seeing
Copy code
2026-03-25 13:48:45.006+08:00 WARN (starrocks-mysql-nio-pool-346|158815) [AcceptListener.lambda$handleEvent$1():125] connect processor exception because
 java.net.SocketException: Connection reset
in fe.warn.log during above error
r
The error 
java.net.SocketException: Connection reset
in 
fe.warn.log
(specifically within the 
starrocks-mysql-nio-pool
) typically indicates that a client (like a BI tool, application, or proxy) closed the connection abruptly before the StarRocks Frontend (FE) could complete the handshake or process the request. Here are the most common causes and troubleshooting steps: 1. Client-Side Disconnections This is the most frequent cause. It happens when: * Timeouts: The client-side application or a load balancer (like HAProxy, Nginx, or F5) has a shorter timeout than the StarRocks server and forcibly closes the connection. * Connection Pools: An application connection pool (e.g., HikariCP, Druid) is configured to "evict" or "test" connections, and it closes them without a proper TCP shutdown. * Abrupt Exits: A script or application finishes its task and exits immediately without calling 
close()
on the database connection. 2. OS/Network Level Bottlenecks If you see this error frequently across many different clients, the OS might be dropping connections because it cannot handle the volume of incoming requests. Troubleshooting Commands: Run these on the FE node to check for TCP backlog drops:
Copy code
bash
# Check if the TCP backlog is full and dropping connections
netstat -s | grep -i LISTEN
netstat -s | grep TCPBacklogDrop

# Check the maximum number of connections the kernel will queue
cat /proc/sys/net/core/somaxconn
If 
TCPBacklogDrop
is increasing, you may need to increase 
somaxconn
and the FE's 
backlog
configuration. 3. Load Balancer / Proxy Settings If you are using a load balancer (LB) in front of the FEs: * Ensure the Idle Timeout on the LB is longer than the 
wait_timeout
and 
interactive_timeout
in StarRocks (default is usually 8 hours). * Check if the LB is performing "L4" health checks that just open and close a port, which can trigger these warnings in the FE logs. 4. Configuration Check Ensure that the FE's 
priority_networks
is correctly configured in 
fe.conf
. If the FE has multiple network interfaces and isn't bound to the correct one, it can lead to intermittent connection resets. Summary: If your application is running normally and queries are completing, these warnings are often "noise" caused by how clients or load balancers manage their connection pools. However, if users are reporting "Connection lost" errors, you should investigate the network path and client-side timeouts. References • faq/operation_maintenance_faq.md • administration/Meta_recovery.md • administration/management/logs.md
c
@Rocky seeing in fe.warn.log
Copy code
2026-03-25 13:49:33.368+08:00 WARN (port-connectivity-checker-3|152) [PortConnectivityChecker.isPortConnectable():113] socket connection to kube-starrocks-fe-3.kube-starrocks-fe-search.starrocks.svc.cluster.local:9010 failed, reason: kube-starrocks-fe-3.kube-starrocks-fe-search.starrocks.svc.cluster.local
during connection failures
r
The warning log you're seeing from the 
PortConnectivityChecker
indicates that one of your Frontend (FE) nodes cannot establish a TCP connection to FE node 3 on port 9010. In StarRocks, port 9010 is the 
edit_log_port
. This port is critical for cluster health because it is used by BDB JE (Berkeley DB Java Edition) for internal communication, leader election, and metadata synchronization between FE nodes. Since you are running in Kubernetes, here is a checklist to troubleshoot this connection failure: 1. Check Pod Status Ensure that the pod 
kube-starrocks-fe-3
is actually running and healthy. If the pod is in a 
CrashLoopBackOff
or 
Pending
state, other FE nodes will naturally fail to connect to it.
Copy code
bash
kubectl get pods -n <your-namespace> | grep fe-3
2. Verify Port Binding Exec into the 
fe-3
pod and check if the FE process is actually listening on port 9010.
Copy code
bash
kubectl exec -it kube-starrocks-fe-3 -n <your-namespace> -- netstat -tulpn | grep 9010
If nothing is listening, check the 
fe.log
on fe-3 to see if the process failed to start or if there was a port conflict. 3. DNS and Headless Service Resolution StarRocks in Kubernetes usually uses a Headless Service (e.g., 
kube-starrocks-fe-search
) to allow FEs to discover each other via DNS. * Try to resolve the address from another FE pod:
Copy code
bash
    kubectl exec -it <another-fe-pod> -n <your-namespace> -- nslookup kube-starrocks-fe-3.kube-starrocks-fe-search.starrocks.svc.cluster.local
* If the DNS doesn't resolve to an IP, there may be an issue with your Kubernetes CoreDNS or the Headless Service definition. 4. Network Policies Check if there are any NetworkPolicies in your namespace that might be restricting traffic between FE pods. FE nodes must be able to communicate with each other on the following ports: * 9010: 
edit_log_port
(Metadata sync) * 9020: 
rpc_port
(Internal Thrift RPC) * 9030: 
query_port
(MySQL) * 8030: 
http_port
(Web UI/API) 5. 
priority_networks
Configuration In some K8s environments (like those with multiple network interfaces), the FE might bind to the wrong IP address. Check your `fe.conf`: * Ensure 
priority_networks
is configured if the pods have multiple IPs. * However, if you are using the official StarRocks Operator, this is usually handled automatically. Summary of Impact If this connection remains failed: * The cluster may fail to elect a Leader if the majority of nodes cannot talk to each other (Quorum failure). * Follower nodes will fall behind on metadata updates, leading to inconsistent query results or "Master not found" errors. Next Step: Check the logs of 
kube-starrocks-fe-3
specifically to see why it isn't accepting connections on 9010. References • deployment/deploy_manually.md • faq/operation_maintenance_faq.md • administration/management/FE_configuration.md • administration/Meta_recovery.md • administration/management/logs.md
c
@Rocky 
kube-starrocks-fe-3
doesn't exist in above case and still getting errors for it
r
This issue occurs because StarRocks manages its own internal list of FE nodes (metadata) for high availability and replication. When you scale down or delete a pod in Kubernetes (like 
kube-starrocks-fe-3
), the Kubernetes pod is removed, but the StarRocks Leader FE still expects that node to be part of the cluster's replication group. As a result, the Leader will keep trying to connect to the non-existent node, leading to connection timeout errors in the logs and potential cluster health issues. To resolve this, you must manually remove the "ghost" FE node from the StarRocks metadata: 1. Identify the missing FE node Log in to your StarRocks cluster using a MySQL client and check the status of all frontends:
Copy code
sql
SHOW PROC '/frontends';
In the output, look for the row where the 
Name
or 
IP
corresponds to 
kube-starrocks-fe-3
. You will likely see 
Alive
as 
false
. 2. Drop the Follower Run the following command to permanently remove the node from the metadata. Replace 
<fe_host>
with the IP or hostname (FQDN) shown in the 
SHOW PROC
output for that node (usually it is 
kube-starrocks-fe-3.kube-starrocks-fe-search...
or similar).
Copy code
sql
-- The default edit_log_port is 9010
ALTER SYSTEM DROP FOLLOWER "<fe_host>:9010";
_Note: If the node was added as an 
OBSERVER
instead of a 
FOLLOWER
, use 
ALTER SYSTEM DROP OBSERVER "<fe_host>:9010";
._ Why did this happen? While the StarRocks Operator handles pod lifecycle, StarRocks FE nodes use BDBJE for metadata replication, which requires a strict quorum. Safely removing a Follower requires an explicit SQL command to update the replication group state, ensuring the remaining nodes don't wait for a pod that will never come back. Tips for Kubernetes * Check the Operator: If you are using the StarRocks Operator, ensure you scaled down by updating the 
replicas
count in your 
StarRocksCluster
YAML rather than deleting pods manually. * Clean up PVCs: If you don't intend to bring this node back, remember to delete the Persistent Volume Claim (PVC) associated with 
kube-starrocks-fe-3
to free up storage, as Kubernetes often retains these by design. References • sql-reference/sql-statements/cluster-management/nodes_processes/ALTER_SYSTEM.md • administration/Meta_recovery.md • faq/operation_maintenance_faq.md • deployment/deploy_manually.md
c
@Rocky are these variables available in starrocks for FE? query_port_worker_threads and mysql_service_nio_threads
r
The variables 
query_port_worker_threads
 and 
mysql_service_nio_threads
 do not exist in StarRocks. It is likely you are looking for these equivalent FE configuration parameters: 1. For MySQL/Query Port Thread Management: If you are looking to control the number of threads handling queries on the MySQL port (default 
9030
), the relevant parameters are: * 
max_mysql_service_task_threads_num
: * Default: 4096 * Description: The maximum number of threads that the MySQL server in the FE node can run to process tasks (queries). * 
mysql_service_io_threads_num
: * Default: 4 * Description: The number of threads used by the MySQL server in the FE node to process I/O events. This is the closest match to "nio_threads". 2. For Internal Service (Thrift) Thread Management: If you are referring to the internal communication between FE and BE: * 
thrift_server_max_worker_threads
: * Default: 4096 * Description: The maximum number of worker threads supported by the Thrift server in the FE node. How to check or change them: These are FE configuration parameters (usually set in 
fe.conf
). You can check their current values by running:
Copy code
sql
ADMIN SHOW FRONTEND CONFIG LIKE '%mysql%';
-- or
ADMIN SHOW FRONTEND CONFIG LIKE '%thrift%';
Most of these are not mutable at runtime and require an FE restart if changed in 
fe.conf
. References • administration/management/BE_configuration.md • administration/management/FE_configuration.md • sql-reference/System_variable.md
c
@Rocky how to remove disconnected cn from cluster?
r
To remove a disconnected Compute Node (CN) from your StarRocks cluster, you should use the 
ALTER SYSTEM DROP COMPUTE NODE
statement. Unlike Backend (BE) nodes, CN nodes are stateless and do not store data, so they cannot be "decommissioned." You must "drop" them directly. Steps to remove a CN node: 1. Identify the CN node details Run the following command to get the 
IP
(Host) and 
HeartbeatPort
of the CN node you want to remove:
Copy code
sql
    SHOW COMPUTE NODES;
2. Drop the CN node Use the 
ALTER SYSTEM
command with the host and heartbeat port (default is 
9050
) of the disconnected node:
Copy code
sql
    ALTER SYSTEM DROP COMPUTE NODE "host:heartbeat_service_port";
Example:
Copy code
sql
    ALTER SYSTEM DROP COMPUTE NODE "172.16.0.10:9050";
Important Notes: * No Decommission: You cannot use 
ALTER SYSTEM DECOMMISSION
for CN nodes because they do not have data to migrate. * Immediate Removal: The 
DROP
command removes the node from the metadata immediately. * Blacklist: If the node was automatically added to a blacklist (supported in newer versions), dropping it will also clear its association with the cluster metadata. References • administration/management/BE_blacklist.md • sql-reference/sql-statements/cluster-management/nodes_processes/ALTER_SYSTEM.md • deployment/downgrade.md • deployment/upgrade.md • administration/stargo.md • administration/management/enable_fqdn.md • sql-reference/sql-statements/cluster-management/nodes_processes/DELETE_BACKEND_BLACKLIST.md
c
@Rocky getting
Copy code
double free or corruption (!prev)
Aborted (core dumped)
during data ingestion. What does this mean?
r
The error message 
double free or corruption (!prev)
followed by 
Aborted (core dumped)
indicates a critical failure in the StarRocks Backend (BE) process. This is a low-level memory management error from the C library (
glibc
), meaning the program tried to free the same memory address twice or the internal heap structures were corrupted (e.g., by a buffer overflow). During data ingestion, this usually points to a bug in the C++ code of the BE, often triggered by specific data patterns, complex data types (like JSON, ARRAY, or Map), or a memory management race condition. How to Troubleshoot 1. Check 
be.out
for the Stack Trace When a BE crashes, it typically prints a stack trace to the 
be.out
file (located in the 
log/
directory of your BE deployment). * Open 
be.out
and look for the lines immediately preceding the "Aborted" message. * The stack trace will show which function call triggered the crash. This is the most helpful information for identifying the root cause. 2. Identify the Ingestion Context Check the 
<http://be.INFO|be.INFO>
log at the time of the crash to see which ingestion task was running. * Look for keywords like 
StreamLoad
, 
RoutineLoad
, or 
BrokerLoad
. * Note if there are specific complex columns involved (JSON, large strings, nested types). 3. Enable and Collect Core Dumps To get more detail, you should analyze the core dump file using 
gdb
. * Enable core dumps:
Copy code
bash
    # Get the BE process ID
    ps aux | grep starrocks_be
    # Set core limit to unlimited for that process
    prlimit -p <BE_PID> --core=unlimited:unlimited
* Locate the core file: It is usually generated in the BE's root directory or the directory specified by your system's 
/proc/sys/kernel/core_pattern
. * Analyze with GDB:
Copy code
bash
    gdb <path_to_be_binary> <path_to_core_file>
    # Inside gdb, type:
    (gdb) bt
This backtrace (
bt
) will provide the exact line of code where the corruption was detected. 4. Common Causes * Complex Data Types: Bugs in the serialization or deserialization of JSON, Map, or Array types. * Large Objects: Extremely large rows or columns that exceed internal buffers. * Version Regressions: If you recently upgraded, it might be a known issue in that specific version. Recommended Action If you are on an older version of StarRocks, check the release notes for your version or consider upgrading to the latest stable patch release (e.g., if on 3.1.x, move to the latest 3.1.z), as memory-related bugs are frequently fixed in maintenance releases. If you have the 
be.out
stack trace, feel free to share the top few lines of the trace so I can help pinpoint the specific component! References • faq/Sql_faq.md • administration/management/BE_configuration.md • administration/Meta_recovery.md
c
@Rocky getting "MySQLdb._exceptions.OperationalError: (5609, 'Memory limit exceeded: Memory of process exceed limit. try consume:1536 Backend: kube-starrocks-cn-0.kube-starrocks-cn-search.starrocks.svc.cluster.local, Used: 95437582376, Limit: 95670396518. Mem usage has exceed the limit of BE: BE:2833548')"
r
The error code 5609 (
PROCESS_MEMORY_LIMIT_EXCEEDED
) indicates that the total memory used by the StarRocks Backend (BE) process has reached its configured upper limit. In your case, the BE is using 95.4 GB out of a 95.6 GB limit. This is a "hard limit" check for the entire process, including queries, data loading, metadata, and caches. 1. Identify the Cause To see exactly what is consuming the memory, you should check the BE's memory tracker. Run the following command from a machine that can reach the BE node:
Copy code
bash
# Replace <BE_IP> and <BE_HTTP_PORT> (default 8040) with your actual values
curl -XGET http://<BE_IP>:<BE_HTTP_PORT>/mem_tracker
Look for the following categories in the output: * Query: Memory used by active queries. * Load: Memory used by data ingestion (Stream Load, Broker Load, etc.). * StoragePageCache: Memory used for caching data pages from disk. * Metadata: Memory used for tablet and rowset metadata. * Compaction: Memory used for background data merging. 2. Configuration Adjustments Since you are running on Kubernetes (
kube-starrocks-cn-0
), StarRocks typically defaults the BE memory limit (
mem_limit
) to 90% of the container's memory limit. Option A: Increase Container Resources If your physical nodes have more RAM available, increase the memory 
limit
and 
request
for the BE/CN containers in your Kubernetes manifest or StarRocksOperator spec. Option B: Adjust 
mem_limit
 If you cannot increase the container size, you can try to adjust the 
mem_limit
in the 
be.conf
, though this is risky if the container itself is small (you might face a K8s OOM kill). * Parameter: 
mem_limit
* Default: 
90%
* Example: 
mem_limit = 100G
or 
mem_limit = 95%
Option C: Tune Specific Caches If the 
mem_tracker
shows that StoragePageCache is taking up too much space, you can limit it in `be.conf`:
Copy code
conf
# Limit page cache (default is 20% of mem_limit)
storage_page_cache_limit = 20G
3. Query-Level Troubleshooting If the error occurs during a specific heavy query, you can try to limit that individual query's memory to prevent it from crashing the entire BE:
Copy code
sql
-- Set limit for the current session (e.g., 16GB)
SET query_mem_limit = 17179869184;
Summary of the Error Details: * Used: 95,437,582,376 bytes (~88.8 GB) * Limit: 95,670,396,518 bytes (~89.1 GB) * Backend: 
kube-starrocks-cn-0
The BE is currently being throttled because it is at 99.7% of its allowed memory. I recommend checking the 
/mem_tracker
immediately to see if a specific load or query is "leaking" or if the Page Cache has simply grown to fill the space. References • administration/user_privs/authorization/User_privilege.md • administration/user_privs/authorization/user_privs.md • administration/user_privs/authorization/built_in_roles.md
c
@Rocky what's the meaning of below variable values?
Copy code
mysql> show variables like '%mem%'
    -> ;
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| connector_scan_use_query_mem_ratio | 0.3   |
| load_mem_limit                     | 0     |
| query_mem_limit                    | 0     |
| query_queue_mem_used_pct_limit     | 0.0   |
| scan_use_query_mem_ratio           | 0.3   |
+------------------------------------+-------+
r
The variables you are seeing in 
show variables like '%mem%'
are related to how StarRocks manages memory for queries and data loading. Here is a breakdown of what each one means: 1. 
query_mem_limit
(Default: 0) This is the memory limit for a single query on each BE (Backend) node. * Value of 0: Means there is no specific per-query limit. The query can use as much memory as the BE's total memory allows (controlled by the BE's 
mem_limit
configuration). * Usage: If you encounter "Memory Limit Exceeded" errors for specific queries, you can increase this value (in bytes) for your session or globally to allow the query to use more memory. 2. 
load_mem_limit
(Default: 0) This is the memory limit for a single import (load) task on each BE node. * Value of 0: StarRocks will use a default value (historically related to 
exec_mem_limit
or the BE's 
default_load_mem_limit
setting). * Usage: You can adjust this to control how much memory a single 
INSERT
or 
STREAM LOAD
can consume. 3. 
scan_use_query_mem_ratio
(Default: 0.3) This variable controls the memory buffer used by the Scan Operator for internal (OLAP) tables. * Meaning: It specifies that the scan operator can use up to 30% (0.3) of the total 
query_mem_limit
for its chunk buffers. * Purpose: This prevents the scanner from being too "greedy" and pre-reading too much data into memory, ensuring there is enough memory left for other operations like Joins and Aggregations. 4. 
connector_scan_use_query_mem_ratio
(Default: 0.3) This is the same as 
scan_use_query_mem_ratio
, but specifically for External Tables (using connectors like Hive, Iceberg, Hudi, etc.). * Meaning: It limits the scan buffers for external data sources to 30% of the query's memory limit. 5. 
query_queue_mem_used_pct_limit
(Default: 0.0) This is a Global variable used by the Query Queue feature. * Meaning: It defines the memory threshold (as a percentage of BE memory) at which new queries should be queued rather than executed immediately. * Value of 0.0: Indicates that memory-based queuing is effectively disabled or not being used as a hard threshold in the current configuration. Summary Table Variable | Scope | Description 
query_mem_limit
| Session/Global | Max memory a query can use on a BE. 
load_mem_limit
| Session/Global | Max memory a load task can use on a BE. 
scan_use_query_mem_ratio
| Session/Global | % of query memory allowed for native scan buffers. 
connector_scan_use_query_mem_ratio
| Session/Global | % of query memory allowed for external scan buffers. 
query_queue_mem_used_pct_limit
| Global | BE memory % threshold to start queuing queries. References • administration/management/resource_management/Memory_management.md • sql-reference/System_variable.md • administration/management/BE_configuration.md • administration/management/FE_configuration.md
The variables you've listed are primarily used to manage and limit memory consumption for queries and load processes in StarRocks. Here is a breakdown of what each one means: 1. 
scan_use_query_mem_ratio
(Default: 0.3) This variable defines the maximum percentage of a query's memory limit (
query_mem_limit
) that the Scan Operator can use for its internal chunk buffer when reading data from StarRocks native (OLAP) tables. * Meaning: With a value of 
0.3
, up to 30% of the query's memory can be allocated to the scan buffer. This helps prevent the scan phase from consuming all available query memory, leaving room for other operators like Join or Aggregate. 2. 
connector_scan_use_query_mem_ratio
(Default: 0.3) Similar to the above, but specifically for External Tables (e.g., Hive, Iceberg, Hudi) via the StarRocks Connectors. * Meaning: It limits the chunk buffer of the Connector Scan Operator to 30% of the 
query_mem_limit
. 3. 
query_mem_limit
(Default: 0) This sets the maximum memory that a single query can consume on each BE (Backend) node. * Value 0: Means there is no specific limit imposed on the query by this variable (though it is still bound by the BE's global 
mem_limit
). * Usage: If you encounter "Memory Limit Exceeded" errors for a specific query, you can increase this value (e.g., 
SET query_mem_limit = 8589934592;
for 8GB) to allow it more head room. 4. 
load_mem_limit
(Default: 0) This specifies the memory limit for a single Load (Import) task on each BE node. * Value 0: If set to 0, the system typically uses the value of 
query_mem_limit
(or a default internal value) as the limit for the load process. * Usage: You can increase this for large-scale imports that involve complex transformations or very wide tables. 5. 
query_queue_mem_used_pct_limit
(Default: 0.0) This variable is related to the Query Queuing feature, which prevents the system from being overwhelmed by too many concurrent queries. * Value 0.0: Means the memory-based queuing trigger is disabled. * Meaning: If set to a value greater than 0 (e.g., 
0.8
), StarRocks will start queuing new queries if the BE's total memory usage exceeds 80%. This helps maintain system stability under high load. Summary Table Variable | Scope | Purpose 
query_mem_limit
| Session/Global | Total memory a query can use per BE. 
load_mem_limit
| Session/Global | Total memory a load task can use per BE. 
scan_use_query_mem_ratio
| Session/Global | Buffer limit for internal table scans (% of 
query_mem_limit
). 
connector_scan_use_query_mem_ratio
| Session/Global | Buffer limit for external table scans (% of 
query_mem_limit
). 
query_queue_mem_used_pct_limit
| Global | Threshold to start queuing queries based on BE memory pressure. If you are experiencing memory-related issues, these are the primary "knobs" you can turn to balance performance and stability. Let me know if you need help tuning a specific workload! References • administration/management/resource_management/Memory_management.md • administration/management/BE_configuration.md • sql-reference/System_variable.md • administration/management/FE_configuration.md
c
@Rocky how to check resource groups and their configs?
r
To check resource groups and their configurations in StarRocks, you can use several SQL commands depending on whether you want to see the definitions or the current usage. 1. View Resource Group Definitions and Configs To see the resource groups that have been created and their specific configurations (like 
cpu_weight
, 
mem_limit
, and 
classifiers
), use the following command: * Show all resource groups:
Copy code
sql
    SHOW RESOURCE GROUPS ALL;
* Show a specific resource group:
Copy code
sql
    SHOW RESOURCE GROUP <resource_group_name>;
Example Output: The result will display columns such as 
name
, 
cpu_weight
, 
mem_limit
, 
concurrency_limit
, and the associated 
classifiers
. 2. View Current Resource Usage (v3.1.4+) If you want to see how much CPU and memory each resource group is currently consuming across your Backend (BE) nodes, use:
Copy code
sql
SHOW USAGE RESOURCE GROUPS;
This shows real-time metrics like 
BEInUseCpuCores
, 
BEInUseMemBytes
, and 
BERunningQueries
. 3. Verify Which Resource Group a Query is Using If you want to confirm which resource group a specific query is hitting, you have three options: * Before running (Plan stage): Run 
EXPLAIN VERBOSE <your_query>;
and look for the 
Resource Group
field in the output. * While running: Execute 
SHOW PROC '/current_queries';
and check the 
ResourceGroup
column. * After execution: Check the 
ResourceGroup
field in the 
fe.audit.log
on your FE node. Note on System Resource Groups StarRocks includes two built-in resource groups by default: • `default_wg`: Used for regular queries that don't match any specific classifier. • `default_mv_wg`: Used for asynchronous materialized view refresh tasks. References • administration/management/resource_management/resource_group.md • sql-reference/sql-statements/cluster-management/resource_group/SHOW_RESOURCE_GROUP.md • faq/resource_isolation_faq.md • sql-reference/sql-statements/cluster-management/resource_group/SHOW_USAGE_RESOURCE_GROUPS.md
c
@Rocky what's the default value for 
default_load_mem_limit
?
r
The default value for the BE configuration parameter 
default_load_mem_limit
is 2147483648 bytes (which is 2 GB). Details: * Parameter Type: BE configuration (typically found in 
be.conf
). * Description: This parameter defines the memory limit on the receiving side for a single import instance. If this limit is reached, a flush to disk (disk swipe) is triggered. * Relation to Session Variables: This value acts as the default if the session variable 
load_mem_limit
is not explicitly set (or is set to 0). If you are looking for the Session Variable named 
load_mem_limit
, its default value is 
0
, which means it will use 
query_mem_limit
(or 
exec_mem_limit
in older versions) instead. References • administration/management/BE_configuration.md • sql-reference/sql-statements/loading_unloading/BROKER_LOAD.md • sql-reference/System_variable.md • administration/management/resource_management/Memory_management.md
r
@Rocky can you explain these values ? • qe_max_connection • query_timeout • max_connection_idle_time • max_backend_worker_threads
r
Hello! I’m Rocky. I can certainly help explain those values for you. These parameters and variables are used to manage connections and resource allocation within a StarRocks cluster. Here is a breakdown of what they represent: 1. 
qe_max_connection
 * Type: FE Configuration (
fe.conf
) * Default: 4096 (since v3.1.12/v3.2.7; previously 1024) * Description: This parameter defines the maximum number of client connections that a single Frontend (FE) node can handle simultaneously across all users. If this limit is reached, new connection attempts from clients (like MySQL CLI or BI tools) will be rejected until existing ones are closed. 2. 
query_timeout
 * Type: Session Variable * Default: 300 (seconds) * Description: This is the maximum execution time allowed for a single query. If a query runs longer than this value, StarRocks will automatically cancel it to prevent long-running queries from hogging system resources. * How to change: You can adjust this for your current session using 
SET query_timeout = 600;
or globally for all new sessions using 
SET GLOBAL query_timeout = 600;
. 3. 
max_connection_idle_time
(often referred to as 
wait_timeout
) * Type: Session/Global Variable * Default: 28800 (8 hours) * Description: While the exact string 
max_connection_idle_time
is sometimes used in specific drivers, StarRocks follows the MySQL protocol using 
wait_timeout
. It determines how long (in seconds) the FE will wait for an idle connection to send a new request before closing it automatically. This prevents "leaked" or abandoned connections from consuming the 
qe_max_connection
slots. 4. 
max_backend_worker_threads
(similar to 
be_service_threads
or 
pipeline_exec_thread_pool_thread_num
) * Type: BE Configuration (
be.conf
) * Description: StarRocks typically uses more specific names for thread pools rather than a single "max_backend_worker_threads" parameter. Depending on what you are looking to tune, you are likely referring to one of these: * 
be_service_threads
: The number of worker threads the Backend (BE) Thrift server uses to handle RPC requests (Default: 64). * 
pipeline_exec_thread_pool_thread_num
: In the modern Pipeline execution engine, this controls the number of threads used for query execution (Default: Number of CPU cores). * 
fragment_pool_thread_num_max
: The maximum number of threads for the older non-pipeline query engine (Default: 4096). Summary Tip: If you are seeing "Too many connections" errors, you should check 
qe_max_connection
. If your queries are being cancelled prematurely, you should increase 
query_timeout
. Do you have a specific performance issue you're trying to solve with these? I'd be happy to dig deeper! References • administration/management/FE_configuration.md • administration/management/resource_management/query_queues.md • administration/management/BE_configuration.md • sql-reference/System_variable.md
r
@Rocky how can we set these values ? 
max_connection_idle_time
, 
pipeline_exec_thread_pool_thread_num
. We are using Shared architecture with FE and CN (compute node)
r
To set these values in a StarRocks Shared-Data architecture (with FE and CN), you need to modify the configuration files for the Frontend (FE) and Compute Node (CN/BE). 1. 
pipeline_exec_thread_pool_thread_num
 This parameter controls the number of threads in the pipeline execution engine's thread pool on the Compute Node (CN) or Backend (BE). * Where to set: In the 
cn.conf
 (or 
be.conf
) file of your Compute Nodes. * Default value: 
0
(This means StarRocks will automatically set the thread number to the number of CPU cores on the node). * How to set: 1. Open the 
conf/cn.conf
file on each Compute Node. 2. Add or modify the line:
Copy code
properties
        pipeline_exec_thread_pool_thread_num = <desired_value>
3. Restart the CN process for the change to take effect, as this parameter is typically not dynamic. 2. 
max_connection_idle_time
 In StarRocks, the standard way to control how long an idle client connection persists is through the 
wait_timeout
 system variable. If you are looking for a configuration parameter to manage idle connections: Client-side Idle Timeout (MySQL Protocol) If you want to set the idle timeout for your client applications (e.g., BI tools, JDBC drivers): * Variable name: 
wait_timeout
* Unit: Seconds (Default is 28800, which is 8 hours). * How to set (Global):
Copy code
sql
    SET GLOBAL wait_timeout = 3600; -- Set to 1 hour
Note: This does not require a restart. Internal bRPC Connection Idle Timeout If you are specifically looking for a configuration for internal communication (FE to CN) idle time: * Parameter names: 
brpc_idle_wait_max_time
(FE/BE) or 
brpc_min_evictable_idle_time_ms
(FE). * Where to set: In 
fe.conf
or 
cn.conf
. * Example in `fe.conf`:
Copy code
properties
    brpc_idle_wait_max_time = 10000
Summary for Shared Architecture: Parameter | Location | File | Restart Required? 
pipeline_exec_thread_pool_thread_num
| Compute Node | 
cn.conf
| Yes 
wait_timeout
(Idle connections) | FE (SQL) | N/A | No (SQL command) 
brpc_idle_wait_max_time
| FE & CN | 
fe.conf
/ 
cn.conf
| Yes Note: Always ensure that if you are using Compute Nodes in a shared-data cluster, you apply changes to the 
cn.conf
file rather than 
be.conf
, although they serve the same purpose. References • administration/management/BE_configuration.md • sql-reference/System_variable.md
Open in Slack
PreviousNext
Powered by