System variables
StarRocks provides many system variables that can be set and modified to suit your requirements. This section describes the variables supported by StarRocks. You can view the settings of these variables by running the SHOW VARIABLES command on your MySQL client. You can also use the SET command to dynamically set or modify variables. You can make these variables take effect globally on the entire system, only in the current session, or only in a single query statement.
The variables in StarRocks refer to the variable sets in MySQL, but some variables are only compatible with the MySQL client protocol and do not function on the MySQL database.
NOTE
Any user has the privilege to run SHOW VARIABLES and make a variable take effect at session level. However, only users with the SYSTEM-level OPERATE privilege can make a variable take effect globally. Globally effective variables take effect on all the future sessions (excluding the current session).
If you want to make a setting change for the current session and also make that setting change apply to all future sessions, you can make the change twice, once without the
GLOBAL
modifier and once with it. For example:SET query_mem_limit = 137438953472; -- Apply to the current session.
SET GLOBAL query_mem_limit = 137438953472; -- Apply to all future sessions.
Variable hierarchy and types
StarRocks supports three types (levels) of variables: global variables, session variables, and SET_VAR
hints. Their hierarchical relationship is as follows:
- Global variables take effect on global level, and can be overridden by session variables and
SET_VAR
hints. - Session variables take effect only on the current session, and can be overridden by
SET_VAR
hints. SET_VAR
hints take effect only on the current query statement.
View variables
You can view all or some variables by using SHOW VARIABLES [LIKE 'xxx']
. Example:
-- Show all variables in the system.
SHOW VARIABLES;
-- Show variables that match a certain pattern.
SHOW VARIABLES LIKE '%time_zone%';
Set variables
Set variables globally or for a single session
You can set variables to take effect globally or only on the current session. When set to global, the new value will be used for all the future sessions, while the current session still uses the original value. When set to "current session only", the variable will only take effect on the current session.
A variable set by SET <var_name> = xxx;
only takes effect for the current session. Example:
SET query_mem_limit = 137438953472;
SET forward_to_master = true;
SET time_zone = "Asia/Shanghai";
A variable set by SET GLOBAL <var_name> = xxx;
takes effect globally. Example:
SET GLOBAL query_mem_limit = 137438953472;
The following variables only take effect globally. They cannot take effect for a single session, which means you must use SET GLOBAL <var_name> = xxx;
for these variables. If you try to set such a variable for a single session (SET <var_name> = xxx;
), an error is returned.
- activate_all_roles_on_login
- character_set_database
- default_rowset_type
- enable_query_queue_select
- enable_query_queue_statistic
- enable_query_queue_load
- init_connect
- lower_case_table_names
- license
- language
- query_cache_size
- query_queue_fresh_resource_usage_interval_ms
- query_queue_concurrency_limit
- query_queue_mem_used_pct_limit
- query_queue_cpu_used_permille_limit
- query_queue_pending_timeout_second
- query_queue_max_queued_queries
- system_time_zone
- version_comment
- version
In addition, variable settings also support constant expressions, such as:
SET query_mem_limit = 10 * 1024 * 1024 * 1024;
SET forward_to_master = concat('tr', 'u', 'e');
Set variables in a single query statement
In some scenarios, you may need to set variables specifically for certain queries. By using the SET_VAR
hint, you can set session variables that will take effect only within a single statement.
StarRocks supports using SET_VAR
in the following statements;
- SELECT
- INSERT (from v3.1.12 and v3.2.0 onwards)
- UPDATE (from v3.1.12 and v3.2.0 onwards)
- DELETE (from v3.1.12 and v3.2.0 onwards)
SET_VAR
can only be placed after the above keywords and enclosed in /*+...*/
.
Example:
SELECT /*+ SET_VAR(query_mem_limit = 8589934592) */ name FROM people ORDER BY name;
SELECT /*+ SET_VAR(query_timeout = 1) */ sleep(3);
UPDATE /*+ SET_VAR(query_timeout=100) */ tbl SET c1 = 2 WHERE c1 = 1;
DELETE /*+ SET_VAR(query_mem_limit = 8589934592) */
FROM my_table PARTITION p1
WHERE k1 = 3;
INSERT /*+ SET_VAR(query_timeout = 10000000) */
INTO insert_wiki_edit
SELECT * FROM FILES(
"path" = "s3://inserttest/parquet/insert_wiki_edit_append.parquet",
"format" = "parquet",
"aws.s3.access_key" = "XXXXXXXXXX",
"aws.s3.secret_key" = "YYYYYYYYYY",
"aws.s3.region" = "us-west-2"
);
You can also set multiple variables in a single statement. Example:
SELECT /*+ SET_VAR
(
exec_mem_limit = 515396075520,
query_timeout=10000000,
batch_size=4096,
parallel_fragment_exec_instance_num=32
)
*/ * FROM TABLE;
Descriptions of variables
The variables are described in alphabetical order. Variables with the global
label can only take effect globally. Other variables can take effect either globally or for a single session.
activate_all_roles_on_login (global)
- Description: Whether to enable all roles (including default roles and granted roles) for a StarRocks user when the user connects to the StarRocks cluster.
- If enabled (
true
), all roles of the user are activated at user login. This takes precedence over the roles set by SET DEFAULT ROLE. - If disabled (
false
), the roles set by SET DEFAULT ROLE are activated.
- If enabled (
- Default: false
- Introduced in: v3.0
If you want to activate the roles assigned to you in a session, use the SET ROLE command.
auto_increment_increment
Used for MySQL client compatibility. No practical usage.
autocommit
Used for MySQL client compatibility. No practical usage.
batch_size
- Description: Used to specify the number of rows of a single packet transmitted by each node during query execution. The default is 1024, i.e., every 1024 rows of data generated by the source node is packaged and sent to the destination node. A larger number of rows will improve the query throughput in large data volume scenarios, but may increase the query latency in small data volume scenarios. Also, it may increase the memory overhead of the query. We recommend to set
batch_size
between 1024 to 4096. - Default: 1024
big_query_profile_threshold
-
Description: Used to set the threshold for big queries. When the session variable
enable_profile
is set tofalse
and the amount of time taken by a query exceeds the threshold specified by the variablebig_query_profile_threshold
, a profile is generated for that query.Note: In versions v3.1.5 to v3.1.7, as well as v3.2.0 to v3.2.2, we introduced the
big_query_profile_second_threshold
for setting the threshold for big queries. In versions v3.1.8, v3.2.3, and subsequent releases, this parameter has been replaced bybig_query_profile_threshold
to offer more flexible configuration options. -
Default: 0
-
Unit: Second
-
Data type: String
-
Introduced in: v3.1
catalog
- Description: Used to specify the catalog to which the session belongs.
- Default: default_catalog
- Data type: String
- Introduced in: v3.2.4
cbo_decimal_cast_string_strict
- Description: Controls how the CBO converts data from the DECIMAL type to the STRING type. If this variable is set to
true
, the logic built in v2.5.x and later versions prevails and the system implements strict conversion (namely, the system truncates the generated string and fills 0s based on the scale length). If this variable is set tofalse
, the logic built in versions earlier than v2.5.x prevails and the system processes all valid digits to generate a string. - Default: true
- Introduced in: v2.5.14
cbo_enable_low_cardinality_optimize
- Description: Whether to enable low cardinality optimization. After this feature is enabled, the performance of querying STRING columns improves by about three times.
- Default: true
cbo_eq_base_type
- Description: Specifies the data type used for data comparison between DECIMAL data and STRING data. The default value is
VARCHAR
, and DECIMAL is also a valid value. This variable takes effect only for=
and!=
comparison. - Data type: String
- Introduced in: v2.5.14
cbo_materialized_view_rewrite_related_mvs_limit
- Description: Specifies the maximum number of candidate materialized views allowed during query planning.
- Default: 64
- Introduced in: v3.1.9, v3.2.5