A RetroSearch Logo

Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Search Query:

Showing content from https://docs.starrocks.io/docs/sql-reference/System_variable/ below:

System variables | StarRocks

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; 
SET GLOBAL query_mem_limit = 137438953472;
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:

View variables​

You can view all or some variables by using SHOW VARIABLES [LIKE 'xxx']. Example:


SHOW VARIABLES;


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.

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;

SET_VAR can only be placed after the above keywords and enclosed in /*+...*/.

Example:

SELECT  name FROM people ORDER BY name;

SELECT  sleep(3);

UPDATE  tbl SET c1 = 2 WHERE c1 = 1;

DELETE 
FROM my_table PARTITION p1
WHERE k1 = 3;

INSERT 
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:

Set variables as user properties​

You can set session variables as user properties using the ALTER USER. This feature is supported from v3.3.3.

Example:


ALTER USER 'jack' SET PROPERTIES ('session.query_timeout' = '600');
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)​

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.

chunk_size​ big_query_profile_threshold​ catalog​ cbo_decimal_cast_string_strict​ cbo_enable_low_cardinality_optimize​ cbo_eq_base_type​ cbo_prune_subfield​ enable_sync_materialized_view_rewrite​ query_including_mv_names​ query_excluding_mv_names​ optimizer_materialized_view_timelimit​ enable_materialized_view_agg_pushdown_rewrite​ enable_materialized_view_text_match_rewrite​ materialized_view_subuqery_text_match_max_count​ enable_force_rule_based_mv_rewrite​ enable_view_based_mv_rewrite​ enable_materialized_view_union_rewrite​ enable_materialized_view_plan_cache​ enable_parquet_reader_bloom_filter​ enable_plan_advisor​ enable_plan_analyzer​ enable_parquet_reader_bloom_filter​ enable_parquet_reader_page_index​ follower_query_forward_mode​ character_set_database (global)​ connector_io_tasks_per_scan_operator​ connector_sink_compression_codec​ connector_sink_target_max_file_size​ count_distinct_column_buckets​ default_rowset_type (global)​

Used to set the default storage format used by the storage engine of the computing node. The currently supported storage formats are alpha and beta.

default_table_compression​ disable_colocate_join​ disable_streaming_preaggregations​

Used to enable the streaming pre-aggregations. The default value is false, meaning it is enabled.

div_precision_increment​

Used for MySQL client compatibility. No practical usage.

dynamic_overwrite​ enable_datacache_async_populate_mode​ enable_connector_adaptive_io_tasks​ enable_distinct_column_bucketization​ enable_gin_filter​ enable_group_level_query_queue (global)​ enable_iceberg_metadata_cache​ enable_metadata_profile​ plan_mode​ enable_iceberg_column_statistics​ metadata_collect_query_timeout​ enable_insert_strict​ insert_max_filter_ratio​ insert_timeout​ enable_materialized_view_for_insert​ enable_rule_based_materialized_view_rewrite​ enable_short_circuit​ enable_spill​ enable_spill_to_remote_storage​ enable_strict_order_by​ enable_profile​ enable_query_queue_load (global)​ enable_query_queue_select (global)​ enable_query_queue_statistic (global)​ enable_query_tablet_affinity​ enable_lake_tablet_internal_parallel​ tablet_internal_parallel_mode​ enable_scan_datacache​ populate_datacache_mode​ enable_datacache_io_adaptor​ enable_file_metacache​ enable_datacache_sharing​ datacache_sharing_work_period​ historical_nodes_min_update_interval​ enable_tablet_internal_parallel​ enable_query_cache​ enable_adaptive_sink_dop​ enable_pipeline_engine​ enable_sort_aggregate​ enable_global_runtime_filter​

Whether to enable global runtime filter (RF for short). RF filters data at runtime. Data filtering often occurs in the Join stage. During multi-table joins, optimizations such as predicate pushdown are used to filter data, in order to reduce the number of scanned rows for Join and the I/O in the Shuffle stage, thereby speeding up the query.

StarRocks offers two types of RF: Local RF and Global RF. Local RF is suitable for Broadcast Hash Join and Global RF is suitable for Shuffle Join.

Default value: true, which means global RF is enabled. If this feature is disabled, global RF does not take effect. Local RF can still work.

enable_multicolumn_global_runtime_filter​

Whether to enable multi-column global runtime filter. Default value: false, which means multi-column global RF is disabled.

If a Join (other than Broadcast Join and Replicated Join) has multiple equi-join conditions:

enable_write_hive_external_table​ enable_query_trigger_analyze​ event_scheduler​

Used for MySQL client compatibility. No practical usage.

enable_strict_type​ force_streaming_aggregate​

Used to control whether the aggregation node enables streaming aggregation for computing. The default value is false, meaning the feature is not enabled.

forward_to_leader​

Used to specify whether some commands will be forwarded to the leader FE for execution. Alias: forward_to_master. The default value is false, meaning not forwarding to the leader FE. There are multiple FEs in a StarRocks cluster, one of which is the leader FE. Normally, users can connect to any FE for full-featured operations. However, some information is only available on the leader FE.

For example, if the SHOW BACKENDS command is not forwarded to the leader FE, only basic information (for example, whether the node is alive) can be viewed. Forwarding to the leader FE can get more detailed information including the node start time and last heartbeat time.

The commands affected by this variable are as follows:

group_concat_max_len​ hash_join_push_down_right_table​ init_connect (global)​

Used for MySQL client compatibility. No practical usage.

interactive_timeout​

Used for MySQL client compatibility. No practical usage.

io_tasks_per_scan_operator​ jit_level​ language (global)​

Used for MySQL client compatibility. No practical usage.

license (global)​ load_mem_limit​

Specifies the memory limit for the import operation. The default value is 0, meaning that this variable is not used and query_mem_limit is used instead.

This variable is only used for the INSERT operation which involves both query and import. If the user does not set this variable, the memory limit for both query and import will be set as exec_mem_limit. Otherwise, the memory limit for query will be set as exec_mem_limit and the memory limit for import will be as load_mem_limit.

Other import methods such as BROKER LOAD, STREAM LOAD still use exec_mem_limit for memory limit.

log_rejected_record_num (v3.1 and later)​

Specifies the maximum number of unqualified data rows that can be logged. Valid values: 0, -1, and any non-zero positive integer. Default value: 0.

lower_case_table_names (global)​

Used for MySQL client compatibility. No practical usage. Table names in StarRocks are case-sensitive.

lower_upper_support_utf8​ low_cardinality_optimize_on_lake​ materialized_view_rewrite_mode (v3.2 and later)​

Specifies the query rewrite mode of asynchronous materialized views. Valid values:

max_allowed_packet​ max_pushdown_conditions_per_column​ max_scan_key_num​ nested_mv_rewrite_max_level​ net_buffer_length​

Used for MySQL client compatibility. No practical usage.

net_read_timeout​

Used for MySQL client compatibility. No practical usage.

net_write_timeout​

Used for MySQL client compatibility. No practical usage.

new_planner_optimize_timeout​ parallel_exchange_instance_num​

Used to set the number of exchange nodes that an upper-level node uses to receive data from a lower-level node in the execution plan. The default value is -1, meaning the number of exchange nodes is equal to the number of execution instances of the lower-level node. When this variable is set to be greater than 0 but smaller than the number of execution instances of the lower-level node, the number of exchange nodes equals the set value.

In a distributed query execution plan, the upper-level node usually has one or more exchange nodes to receive data from the execution instances of the lower-level node on different BEs. Usually the number of exchange nodes is equal to the number of execution instances of the lower-level node.

In some aggregation query scenarios where the amount of data decreases drastically after aggregation, you can try to modify this variable to a smaller value to reduce the resource overhead. An example would be running aggregation queries using the Duplicate Key table.

parallel_fragment_exec_instance_num​

Used to set the number of instances used to scan nodes on each BE. The default value is 1.

A query plan typically produces a set of scan ranges. This data is distributed across multiple BE nodes. A BE node will have one or more scan ranges, and by default, each BE node's set of scan ranges is processed by only one execution instance. When machine resources suffice, you can increase this variable to allow more execution instances to process a scan range simultaneously for efficiency purposes.

The number of scan instances determines the number of other execution nodes in the upper level, such as aggregation nodes and join nodes. Therefore, it increases the concurrency of the entire query plan execution. Modifying this variable will help improve efficiency, but larger values will consume more machine resources, such as CPU, memory, and disk IO.

partial_update_mode​ performance_schema (global)​

Used for compatibility with MySQL JDBC versions 8.0.16 and above. No practical usage.

prefer_compute_node​ pipeline_dop​ pipeline_profile_level​ query_cache_entry_max_bytes​ query_cache_entry_max_rows​ query_cache_agg_cardinality_limit​ query_cache_size (global)​

Used for MySQL client compatibility. No practical use.

query_cache_type​

Used for compatibility with JDBC connection pool C3P0. No practical use.

query_mem_limit​ query_queue_concurrency_limit (global)​ query_queue_cpu_used_permille_limit (global)​ query_queue_max_queued_queries (global)​ query_queue_mem_used_pct_limit (global)​ query_queue_pending_timeout_second (global)​ query_timeout​ range_pruner_max_predicate​ rewrite_count_distinct_to_bitmap_hll​

Used to decide whether to rewrite count distinct queries to bitmap_union_count and hll_union_agg.

runtime_filter_on_exchange_node​ runtime_join_filter_push_down_limit​ runtime_profile_report_interval​ scan_olap_partition_num_limit​ spill_mode (3.0 and later)​

The execution mode of intermediate result spilling. Valid values:

This variable takes effect only when the variable enable_spill is set to true.

spill_storage_volume​ SQL_AUTO_IS_NULL​

Used for compatibility with the JDBC connection pool C3P0. No practical usage.

sql_dialect​ sql_mode​

Used to specify the SQL mode to accommodate certain SQL dialects. Valid values include:

You can set only one SQL mode, for example:

set sql_mode = 'PIPES_AS_CONCAT';

Or, you can set multiple modes at a time, for example:

set sql_mode = 'PIPES_AS_CONCAT,ERROR_IF_OVERFLOW,GROUP_CONCAT_LEGACY';
sql_safe_updates​

Used for MySQL client compatibility. No practical usage.

sql_select_limit​ statistic_collect_parallel​ storage_engine​

The types of engines supported by StarRocks:

streaming_preaggregation_mode​

Used to specify the preaggregation mode for the first phase of GROUP BY. If the preaggregation effect in the first phase is not satisfactory, you can use the streaming mode, which performs simple data serialization before streaming data to the destination. Valid values:

system_time_zone​

Used to display the time zone of the current system. Cannot be changed.

time_zone​

Used to set the time zone of the current session. The time zone can affect the results of certain time functions.

trace_log_mode​ transaction_read_only​ tx_isolation​

Used for MySQL client compatibility. No practical usage. The alias is transaction_isolation.

use_compute_nodes​ use_v2_rollup​

Used to control the query to fetch data using the rollup index of the segment v2 storage format. This variable is used for validation when going online with segment v2. It is not recommended for other cases.

vectorized_engine_enable (deprecated from v2.4 onwards)​

Used to control whether the vectorized engine is used to execute queries. A value of true indicates that the vectorized engine is used, otherwise the non-vectorized engine is used. The default is true. This feature is enabled by default from v2.4 onwards and therefore, is deprecated.

version (global)​

The MySQL server version returned to the client. The value is the same as FE parameter mysql_server_version.

The StarRocks version. Cannot be changed.

wait_timeout​ orc_use_column_names​

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4