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.