http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> I created a table with DDL ```CREAT...
# questions-and-troubleshooting
r
@Rocky I created a table with DDL
Copy code
CREATE TABLE IF NOT EXISTS `test_db`.`test_table` (external_id string,secondary_id string,source_activity_action string,source_activity_type string,source_activity_name string,source_activity_url string,source_activity_referrer_url string,source_activity_desc string,metric_name string,metric_value double,source_activity_url_r string,source_activity_agent string,source_activity_base_request string,mid string,hq_company_id string,source_id string,source_activity_channel string,city string,state string,country string,dt date,data_source string,product string,activity_type string,activity_target string,activity_metadata string,source_activity_name_normalized string,activity_action string,is_anonymous bigint,activity_class string,org_name string,is_deleted BOOLEAN , INDEX mid(mid)) 
            ENGINE=OLAP 
            PARTITION BY date_trunc('month', dt)
            DISTRIBUTED BY HASH(hq_company_id) BUCKETS 16
            ORDER BY (org_name,product,data_source,dt)
            PROPERTIES (
                "replication_num" = "3",
                "compression" = "LZ4"
            );
but on show create table the out put is
Copy code
CREATE TABLE `test_table` (
  `external_id` varchar(65533) NULL COMMENT "",
  `secondary_id` varchar(65533) NULL COMMENT "",
  `source_activity_action` varchar(65533) NULL COMMENT "",
  `source_activity_type` varchar(65533) NULL COMMENT "",
  `source_activity_name` varchar(65533) NULL COMMENT "",
  `source_activity_url` varchar(65533) NULL COMMENT "",
  `source_activity_referrer_url` varchar(65533) NULL COMMENT "",
  `source_activity_desc` varchar(65533) NULL COMMENT "",
  `metric_name` varchar(65533) NULL COMMENT "",
  `metric_value` double NULL COMMENT "",
  `source_activity_url_r` varchar(65533) NULL COMMENT "",
  `source_activity_agent` varchar(65533) NULL COMMENT "",
  `source_activity_base_request` varchar(65533) NULL COMMENT "",
  `mid` varchar(65533) NULL COMMENT "",
  `hq_company_id` varchar(65533) NULL COMMENT "",
  `source_id` varchar(65533) NULL COMMENT "",
  `source_activity_channel` varchar(65533) NULL COMMENT "",
  `city` varchar(65533) NULL COMMENT "",
  `state` varchar(65533) NULL COMMENT "",
  `country` varchar(65533) NULL COMMENT "",
  `dt` date NULL COMMENT "",
  `data_source` varchar(65533) NULL COMMENT "",
  `product` varchar(65533) NULL COMMENT "",
  `activity_type` varchar(65533) NULL COMMENT "",
  `activity_target` varchar(65533) NULL COMMENT "",
  `activity_metadata` varchar(65533) NULL COMMENT "",
  `source_activity_name_normalized` varchar(65533) NULL COMMENT "",
  `activity_action` varchar(65533) NULL COMMENT "",
  `is_anonymous` bigint(20) NULL COMMENT "",
  `activity_class` varchar(65533) NULL COMMENT "",
  `org_name` varchar(65533) NULL COMMENT "",
  `is_deleted` boolean NULL COMMENT "",
  INDEX mid (`mid`) USING BITMAP COMMENT ''
) ENGINE=OLAP 
DUPLICATE KEY(`external_id`)
COMMENT "OLAP"
PARTITION BY date_trunc('month', dt)
DISTRIBUTED BY HASH(`hq_company_id`) BUCKETS 16 
ORDER BY(`org_name`, `product`, `data_source`, `dt`)
PROPERTIES (
"compression" = "LZ4",
"datacache.enable" = "true",
"enable_async_write_back" = "false",
"replication_num" = "3",
"storage_volume" = "builtin_storage_volume"
);
why is type String getting converted to type Varchar ?
r
In StarRocks, the 
STRING
data type is an alias for 
VARCHAR(65533)
. When you define a column as 
STRING
in your 
CREATE TABLE
statement, StarRocks automatically converts it to its underlying implementation, which is a variable-length character string with a maximum length of 65,533 bytes. This is why when you run 
SHOW CREATE TABLE
, you see 
VARCHAR(65533)
instead of 
STRING
. Here are a few key points to note: * Storage Efficiency: Both 
STRING
and 
VARCHAR
are variable-length. They only store the actual bytes used plus a small overhead (typically 1-2 bytes for length). Storing "hello" in a 
VARCHAR(65533)
takes the same amount of space as storing it in a 
VARCHAR(10)
. * Historical Limit: The limit of 65,533 bytes was the maximum size for 
VARCHAR
in earlier versions of StarRocks. * Higher Limits: Starting from StarRocks v2.1, 
VARCHAR
can support up to 1,048,576 bytes (1 MB). If you need to store strings longer than 65,533 bytes, you should explicitly define the column as 
VARCHAR(1048576)
. * Performance: For columns that are frequently used in filters or as keys, it is still a best practice to specify a realistic 
VARCHAR(N)
length that matches your data, as this helps with memory estimation and execution plan optimization in certain scenarios. In summary, what you are seeing is the expected behavior where the 
STRING
shortcut is expanded to its full 
VARCHAR
definition. References • sql-reference/data-types/string-type/VARCHAR.md • faq/Others.md • sql-reference/data-types/string-type/BINARY.md • sql-reference/data-types/string-type/STRING.md • knowledge/usage/best-practice.md • sql-reference/System_limit.md
r
@Rocky is there any config similar to 
load_process_max_memory_limit_percent
and how to update it ?
r
Yes, StarRocks has a configuration parameter exactly named 
load_process_max_memory_limit_percent
. Configuration Details * Description: This parameter defines the soft limit (as a percentage of the total BE memory limit, 
mem_limit
) that can be consumed by all loading processes on a BE node. * Default Value: 
30
(30%). * Related Parameter: 
load_process_max_memory_limit_bytes
(which sets the limit in bytes). How to Update It According to the StarRocks documentation, 
load_process_max_memory_limit_percent
is not mutable dynamically. This means you cannot update it using an SQL command while the cluster is running. To update it, you must follow these steps: 1. Modify the Configuration File: Log in to each BE node and edit the 
conf/be.conf
file.
Copy code
properties
    # Example: Increase the load memory limit to 50%
    load_process_max_memory_limit_percent = 50
2. Restart the BE: For the changes to take effect, you must restart the BE process:
Copy code
bash
    bin/stop_be.sh
    bin/start_be.sh --daemon
Other Related Memory Configs (v3.3+) In newer versions of StarRocks (v3.3.2+), there are additional parameters you might find useful for managing load memory: * 
load_process_max_memory_hard_limit_ratio
: A hard limit ratio. If the memory exceeds 
load_process_max_memory_limit_percent * load_process_max_memory_hard_limit_ratio
, new loads will be rejected. This parameter is mutable and can be updated dynamically via SQL:
Copy code
sql
    ALTER SYSTEM MODIFY CONFIG "load_process_max_memory_hard_limit_ratio" = "3";
* 
enable_new_load_on_memory_limit_exceeded
: Determines whether to allow new loading processes when the hard limit is reached. This is also mutable dynamically. References • administration/management/BE_configuration.md
r
@Rocky does load_process_max_memory_limit_percent help with performance improving while running insert into queries for batch files ?
r
Hello! I'm Rocky. The 
load_process_max_memory_limit_percent
parameter can indeed help with the performance and stability of 
INSERT INTO
queries (including those using 
FILES()
for batch loading), but it works more as a global safety valve and resource allocator rather than a direct "speed boost" for a single query. Here is how it impacts performance for batch loading: 1. What it does This BE (Backend) configuration parameter defines the maximum percentage of the BE's total memory that can be consumed by all active loading processes (including 
INSERT INTO
, Stream Load, and Broker Load) combined. The default is usually 30%. 2. How it affects performance * Preventing Throttling/Failures: If you are running multiple large 
INSERT INTO
queries or batch loads simultaneously, and the total memory usage of these loads exceeds this limit, StarRocks may reject new load requests or trigger aggressive memory management (like more frequent flushes of MemTables to disk). * MemTable Management: Data being inserted is first buffered in memory (MemTables). If this global limit is too low, StarRocks is forced to flush data to disk more frequently to stay under the limit. Frequent flushes create many small files (segments), which increases I/O overhead and can slow down the loading process and subsequent query performance. * Concurrency: Increasing this percentage allows the system to handle more concurrent batch loads or larger individual loads without hitting memory ceilings. 3. When to increase it You should consider increasing 
load_process_max_memory_limit_percent
if: * You have a high-concurrency loading environment. * You are loading very large files and seeing errors like 
Memory limit exceeded
for load processes. * The BE nodes have a large amount of RAM, and you want to dedicate more of it to ingestion rather than just query execution. 4. Other important parameters for 
INSERT INTO
 While the BE configuration sets the global limit, individual query performance is often governed by these session variables: * 
query_mem_limit
(or 
exec_mem_limit
): Limits the memory for a single query/insert operation. * `load_mem_limit`: Specifically sets the memory limit for the import part of an 
INSERT INTO
operation. If this is 
0
, it defaults to using the 
query_mem_limit
. Summary Increasing 
load_process_max_memory_limit_percent
helps performance by reducing flush frequency and allowing higher concurrency, provided your BE nodes have enough physical RAM to support it without starving the OS or query execution. If you're looking to speed up a single 
INSERT INTO
query, also ensure that your session-level 
query_mem_limit
is high enough to accommodate the data being processed. References • administration/management/BE_configuration.md • sql-reference/System_variable.md • sql-reference/sql-statements/loading_unloading/BROKER_LOAD.md • faq/loading/Insert_into_faq.md • loading/loading_introduction/loading_considerations.md
r
@Rocky how to clear cache for the tables/which have been queried already ?
r
To clear caches in StarRocks, you need to identify which type of cache you are trying to clear. There are three main categories: Query Cache, Data Cache, and Metadata Cache. 1. Clear Query Cache (Intermediate Results) StarRocks (from v2.5+) uses a Query Cache to store intermediate aggregation results in the BE's memory. If you want to clear this cache for all queries: * Action: Use the RESTful API on each BE node. * Command:
Copy code
bash
    curl -XPUT http://<be_host>:<be_http_port>/api/query_cache/invalidate_all
_Replace 
<be_host>
and 
<be_http_port>
(default is 8040) with your actual BE details._ 2. Clear Data Cache (Block Cache for Data Lakes) The Data Cache stores data blocks from remote storage (like S3, HDFS, or OSS) on the local disks of BE/CN nodes. There is no direct "FLUSH" SQL command, but you can clear it using these methods: * Method A: Dynamic Config Update (No restart required) You can temporarily set the cache quota to 
0
to trigger an automatic cleanup, then set it back to your desired size.
Copy code
sql
    -- Identify your BE ID via 'SHOW BACKENDS'
    -- Set to 0 to clear
    UPDATE be_configs SET VALUE="0" WHERE NAME="datacache_disk_size" AND BE_ID=<be_id>;
    -- Set back to original size (e.g., 2TB)
    UPDATE be_configs SET VALUE="2T" WHERE NAME="datacache_disk_size" AND BE_ID=<be_id>;
* Method B: Manual Deletion (Requires restart) 1. Stop the BE/CN node. 2. Delete the 
datacache
subdirectory within your 
storage_root_path
. 3. Restart the node. 3. Clear/Refresh Metadata Cache (External Tables) If you have updated data in a data lake (Hive, Iceberg, Hudi) and StarRocks is still seeing old metadata, you can manually refresh it. * Action: Use the 
REFRESH EXTERNAL TABLE
command. * Command:
Copy code
sql
    -- Refresh a specific table
    REFRESH EXTERNAL TABLE <table_name>;

    -- Refresh a specific catalog/database table
    REFRESH EXTERNAL TABLE <catalog_name>.<db_name>.<table_name>;

    -- Refresh specific partitions
    REFRESH EXTERNAL TABLE <table_name> PARTITION ('partition_column=value');
Summary Table Cache Type | What it caches | How to clear/refresh Query Cache | Aggregation results | 
curl .../api/query_cache/invalidate_all
Data Cache | Remote data blocks | 
UPDATE be_configs
or delete 
datacache
dir Metadata Cache | File lists/Schemas (Data Lake) | 
REFRESH EXTERNAL TABLE <name>
References • data_source/catalog/hive_catalog.md • sql-reference/sql-statements/table_bucket_part_index/REFRESH_EXTERNAL_TABLE.md • data_source/External_table.md • data_source/catalog/maxcompute_catalog.md • data_source/catalog/iceberg/iceberg_catalog.md
r
@Rocky if we have multiple CNs up for a shared storage, can we specifically route certain queries (eg. INSERT or LOAD queries) to specific CN ? Instead of sharing across all available CNs ?
r
2 Views
Open in Slack
PreviousNext
Powered by