List of Engine Variables

The following tables list the sync engine variables and non-sync engine variables. Some variables have an in-depth description that supplements the description found in the table.

Sync Variables Lists

See the engine variables overview to learn how sync variables work.

Note

All of the sync variables listed in the following table are global variables, except when noted as a session variable that can also be set globally in the Description column.

You can set sync variables on the master aggregator, only. After you set a sync variable, the nodes affected by your update (all aggregators, all leaves, or both) depend on the variable’s type and the command you use to set the variable. Each variable’s type is indicated in the last sentence in the Description column in the following table.

Name

Description

Default

active_query_users_blacklist

Shows a JSON array of users whose query runs are not considered active to measure the cluster's idle time. This variable can sync to all nodes, including aggregator and leaf nodes.

aggregator_failure_detection

Deprecated in MemSQL 6.0, this is the timeout used when gathering workload management statistics from leaves. This variable can sync to all aggregators.

ON

allow_modifying_sysvars_with_computed_shard_key

Allows modification of global variables ( data_conversion_compatibility_level)  even in the presence of computed shard keys.

OFF

allow_proc_cpuinfo_errors

Determines whether to allow errors while consuming '/proc/cpuinfo' to append CPU flags to the code generation's hash. This variable can sync to all nodes, including aggregator and leaf nodes.

FALSE

auto_attach

Specifies if the aggregator will try to attach a node automatically after it has been marked offline, but starts responding to heartbeats again. This variable can sync to all aggregators.

ON

autostats_flush_interval_secs

Number of seconds a table's incremental autostats can go unused before being flushed to disk. 0 means they will never be flushed. This variable can sync to all nodes, including aggregator and leaf nodes. 

600

attach_rebalance_delay_seconds

Number of seconds to wait after a new node has attached to the cluster before running a rebalance. This mechanism is used to batch up rebalancing if many nodes fail and then come back online within a short period of time. This variable can sync to all aggregators.

120

auditlog_disk_sync

Specifies if every audit log record is synchronously written and persisted to the disk. By default, it delays the audit log writes to the disk. For more information on this variable, see Configure Audit Logging. This variable can sync to all nodes, including aggregator and leaf nodes.

OFF

auditlog_retention_period

Indicates the retention period (in days) for audit log files. This variable can sync to all nodes, including aggregator and leaf nodes.

0 (store files indefinitely)

background_statistics_collection_interval

How often (in seconds) background statistics will check for out-of-date statistics (rowstore only). This variable can sync to all aggregators.

60

background_statistics_collection_threshold

Threshold as a fraction of table row count triggering the collection of autostats. This variable can sync to all aggregators.

0.5

batch_external_functions

Determines if external functions are batched. The engine variable accepts ALWAYS, AUTO, and NEVER values. This variable can sync to all nodes, including aggregator and leaf nodes.

ALWAYS

batch_external_functions_size

Deprecated. Use external_functions_batch_size instead.

512

cardinality_estimation_level

Possible values are '6.0', '6.5', '7.0', '7.1', and '7.3'. The behavior of each setting follows: '6.0': Use legacy histograms; '6.5': Use advanced histograms; '7.0': Same use of histograms as 6.5; '7.1': Use histograms for join estimation; '7.3': Use sampling and histograms together (when both are available), to improve selectivity estimation.

This variable can sync to all nodes, including aggregator and leaf nodes.

This engine variable is configurable on SingleStore Helios.

'7.3'

change_count_write_interval

Maximum frequency (in seconds) that change count metadata will be written. Will never occur more frequently than background_statistics_collection_interval. This variable can sync to all aggregators.

1200

character_set_server

The character set of the node. This is a session variable that can also be set globally. This variable can sync to all nodes, including aggregator and leaf nodes.

utf8

collation_connection

Sets the collation that is used on the node. When you set this variable, collation_database and collation_server are automatically set to the same value. For more information on this variable, see the In-Depth Variable Definitions section below. This is a session variable that can also be set globally. This variable can sync to all nodes, including aggregator and leaf nodes.

utf8_general_ci

collation_database

Sets the collation that is used on the node. When you set this variable, collation_connection and collation_server are automatically set to the same value. For more information on this variable, see the In-Depth Variable Definitions section below. This is a session variable that can also be set globally. This variable can sync to all nodes, including aggregator and leaf nodes.

utf8_general_ci

collation_server

Sets the collation that is used on the node. When you set this variable, collation_connection and collation_database are automatically set to the same value. For more information on this variable, see the In-Depth Variable Definitions section below. This is a session variable that can also be set globally. This variable can sync to all nodes, including aggregator and leaf nodes.

utf8_general_ci

collect_average_size

Sends AVG(LENGTH()) queries to collect average serialization data for string columns. This variable can sync to all aggregators.

FALSE

columnstore_disk_insert_threshold

At this threshold (fraction of columnstore_flush_bytes), INSERT, LOAD DATA, and UPDATE queries to the columnstore will write straight to disk. For more information, see Advanced Columnstore Configuration Options. This variable can sync to all nodes, including aggregator and leaf nodes.

0.5

columnstore_flush_bytes

Controls the rowstore-backed segment size for columnstore tables. For more information, see Advanced Columnstore Configuration Options. This variable can sync to all nodes, including aggregator and leaf nodes.

33554432

columnstore_ingest_management_queue_timeout

Timeout (in seconds) to queue a columnstore ingest query before returning an error. This variable can sync to all nodes, including aggregator and leaf nodes.

3600

columnstore_row_value_table_lock_threshold

Sets the threshold at which multiple inserts to a columnstore table with unique keys will switch from row value lock to table lock. The value's metric is the number of rows for each partition in a database. This variable can sync to all nodes, including aggregator and leaf nodes.

1000000 (rows per partition)

columnstore_segment_rows

Controls the maximum row count for a columnstore segment. For more information, see Advanced Columnstore Configuration Options. This variable can sync to all nodes, including aggregator and leaf nodes.

1024000

columnstore_validate_blob_before_merge

Verifies the checksum of a blob before merging it. For more information, see CHECK BLOB CHECKSUM and Columnstore. This variable can sync to all nodes, including aggregator and leaf nodes.

OFF

compiled_images_eviction_memory_limit_percent

Sets the percentage of maximum_memory that is available for caching query object code images. Refer to In-Depth Variable Definitions for more information. This variable can sync to all nodes, including aggregator and leaf nodes.

consensus_enabled

Determines whether consensus can be configured or not. This variable can sync to all nodes, including aggregator and leaf nodes.

FALSE

core_on_bad_errnos_in_file_io_apis

Generates a core if a file IO API encounters EFAULT, EBADF , or EINVAL. This variable can sync to all nodes, including aggregator and leaf nodes.

0

core_on_stack_overflow

Generates a core instead of throwing an error if a stack overflow occurs during query execution. This variable can sync to all nodes, including aggregator and leaf nodes.

0

critical_diagnostics_max_payload_size

Limits the size of some critical diagnostics sent to SingleStore. This variable can sync to all nodes, including aggregator and leaf nodes.

65536

data_conversion_compatibility_level

Specifies the level of data conversion behavior to use. Higher levels throw error for integer under/overflow, illegal conversion of strings to integers, and string truncation issues. Values can be either: '7.5','7.0' , '6.5', or '6.0'. This variable can sync to all nodes, including aggregator and leaf nodes. It’s recommended to set this to the highest available level for new application development. For more information, see Data Type Conversion.

This variable can sync to all nodes, including aggregator and leaf nodes.

'6.0'

deadlock_avoidance_strategy

See Row Locking. This variable can sync to all nodes, including aggregator and leaf nodes.

wait_die

default_autostats_columnstore_cardinality_mode

Sets the default type of cardinality autostats for newly created columnstore tables. Values can be OFF, INCREMENTAL , or PERIODIC. This variable can sync to all aggregators.

INCREMENTAL

default_autostats_columnstore_sampling

Sets the default state for autostats sampling for newly created columnstore tables. This variable can sync to all aggregators.

lON

default_autostats_enabled

Whether autostats is enabled by default on newly created columnstore tables. This variable can sync to all aggregators.

ON

default_autostats_histogram_mode

Sets the default type of automatic histograms on newly created tables. Values can be either OFF, CREATE, or UPDATE. This variable can sync to all aggregators.

CREATE

default_autostats_rowstore_cardinality_mode

Sets the default type of cardinality autostats for newly created rowstore tables. Values can be OFF, INCREMENTAL or PERIODIC. This variable can sync to all aggregators.

PERIODIC

default_columnstore_table_lock_threshold

Sets a threshold for the number of rows that are locked before a table lock is acquired when updating or deleting rows in a columnstore table. When set to 0, the threshold of 5000 rows is used. It is a session variable that can be set globally. This variable can sync to all nodes, including aggregator and leaf nodes.

0

default_distributed_ddl_timeout

The time (in milliseconds) to wait for a distributed DDL transaction to commit. This value sets the timeout for both ALTER TABLE and BACKUP commands. If the timeout is reached, the transaction is rolled back. This variable can sync to all nodes, including aggregator and leaf nodes.

180000

default_partitions_per_leaf

The default number of partitions a newly created database will be created with. When you set this variable, its value is propagated to other aggregators only.

8

default_storage_engine

A global variable used for MySQL compatibility. This variable can sync to all nodes, including aggregator and leaf nodes.

default_table_type

The default table type to use when creating a new table. The value can be set to columnstore or rowstore. When the value is columnstore you can create a columnstore table using the standard CREATE TABLE syntax. This variable can sync to all aggregators.

columnstore for newly created clusters.

When you upgrade a cluster, the setting of the variable does not change.

default_user_require_ssl

Controls the default value for REQUIRE SSL in the CREATE USER DDL statement.

If it is enabled and REQUIRE is not specified in the CREATE USER statement, SSL will be required by default.

Once it is turned on, it impacts only the newly created users. It will not enforce SSL for existing users.

This variable can sync to all aggregators.

OFF

disable_reference_table_leftjoin_where_pushdown

Controls pushdown logic of pushing down reference table clauses to the leaves. When set to OFF, the logic is enabled. When set to ON, the logic is disabled. This is a session variable that can also be set globally. This variable can sync to all nodes, including aggregator and leaf nodes.

ON

disable_remove_redundant_gby_rewrite

Prevents the removal of GROUP BY columns when used in an ORDER BY clause. The default of AUTO is equal to OFF. To disable this behavior the variable must be set to ON. This variable can sync to all nodes, including aggregator and leaf nodes.

AUTO

disable_subquery_merge_with_straight_joins

Whether merging subqueries are disabled if they contain STRAIGHT_JOIN. Here, merging subqueries refers to removing unnecessary nested layers from joins including subqueries. For example, the query select * from (select * from table_a straight_join table_b) table_c straight_join table_d would be rewritten as select * from table_a straight_join table_b straight_join table_d if the engine variable disable_subquery_merge_with_straight_joins is set to OFF. If the variable is set to ON, the query would not be rewritten. The default setting AUTO is equivalent to ON in 7.3.

This is a session variable that can also be set globally. This variable can sync to all nodes, including aggregator and leaf nodes.

AUTO

disable_update_delete_distributed_transactions

OFF

disk_plan_expiration_minutes

The interval in which a query plan must be read from disk (plancache directory) before it is removed. This variable can sync to all nodes, including aggregator and leaf nodes.

20160 (14 days)

dist_txn_outcomes_keepalive_secs

Controls how long (in seconds) an aggregator caches a distributed transaction outcome. This variable can sync to all nodes, including aggregator and leaf nodes.

1800

distributed_commit_lock_timeout

Deprecated in MemSQL 6.5. Replaced by default_distributed_ddl_timeout.

distributed_heartbeat_timeout

As of MemSQL 6.0, this is the timeout (in seconds) used when gathering workload management statistics from leaves. Before MemSQL 6.0 this was the timeout of the heartbeat query used to trigger failovers. This variable can sync to all aggregators.

10

distributed_optimizer_broadcast_mult

At a high level, this variable makes the optimizer more (lower) or less (higher) likely to consider a broadcast when planning distributed joins. SingleStore will choose a broadcast if it saves us moving more than repartitioning. As the default is the number of database partitions, setting the value to a high number (like 100,000) is an effective way to tell the optimizer to not broadcast data at all.

This is a session variable that can also be set globally. This variable can sync to all nodes, including aggregator and leaf nodes.

0

distributed_optimizer_max_join_size

This variable is deprecated but still exists for backward compatibility purposes. As of version 7.3 of SingleStore, it is replaced by two new variables that assist in leveraging significantly increased speed for optimization at large join sizes: distributed_optimizer_unrestricted_search_threshold and singlebox_optimizer_cost_based_threshold. The distributed optimizer works to minimize data movement across the cluster, while the singlebox optimizer makes decisions about how to physically execute a join such as choosing an index. Setting this depreciated variable will result in both new variables being set to the target value.

This variable can sync to all nodes, including aggregator and leaf nodes.

22

distributed_optimizer_min_join_size_run_initial_heuristics

The minimum number of tables that must be present in a join for initial heuristics to be run before join optimization. This means a small cost is incurred for the initial use of these heuristics in excThis is a session variable that can also be set globally. This variable can sync to all nodes, including aggregator and leaf nodes.hange for faster join optimization.

This is a session variable that can also be set globally. This variable can sync to all nodes, including aggregator and leaf nodes.

16

distributed_optimizer_run_legacy_heuristic

Enabling this variable will result in a return to pre-7.3 behavior through the use of heuristics to complete a faster, restricted search for a join plan, once the number of tables in a given join is greater than the threshold set for this with distributed_optimizer_unrestricted_search_threshold. This search is faster, but as the number of tables in a given join increases, the chance of a less optimal query plan also increases.

This is a session variable that can also be set globally. This variable can sync to all nodes, including aggregator and leaf nodes.

FALSE

distributed_optimizer_unrestricted_search_threshold

The maximum number of tables present in a join that will result in an unrestricted search being performed to find the optimal query plan. A query with a greater number of tables will use heuristics to complete a search for a query plan. This restricted search is faster than previous versions of SingleStore. However, this may be at the cost of a less optimal query plan compared to an unrestricted search. distributed_optimizer_run_legacy_heuristic must be set to FALSE for this variable to take effect.

This is a session variable that can also be set globally. This variable can sync to all nodes, including aggregator and leaf nodes.

22

dr_min_connection_timeout_ms

Allows the user to set the minimum amount of time to wait for Disaster Recovery (DR) replication in milliseconds (ms). MS range is between 8 and 2000000.

This variable can sync to all nodes, including aggregator and leaf nodes.

8

enable_alias_space_trim

Trims leading spaces for column name aliases. This engine variable is only used for backward compatibility. This variable can sync to all nodes, including aggregator and leaf nodes.

FALSE

enable_auto_profile

Profile results are not collected automatically. The PROFILE command must be used to run to review usage metrics. The only profile statistics that are saved are the most recent query plan's execution.

Please note, a profiled query will have a different query plan than a non-profiled query. When a query is first profiled compilations of the query are triggered to generate new query plans.

This variable can sync to all nodes, including aggregator and leaf nodes.

OFF

enable_background_plan_invalidation

Controls whether the background statistics thread can invalidate plans automatically. This can improve plans if your data changes over time at the cost of spending time recompiling plans. This variable can sync to all aggregators.

OFF

enable_background_statistics_collection

If set to ON, background statistics are collected for all tables, unless disabled on the table level. If set to OFF, background statistics are not collected for any tables, and background statistics cannot be enabled for individual tables. This variable can sync to all aggregators.

ON

enable_binary_protocol

When this variable is set to ON, server-side prepared statements are enabled. This is a session variable that can also be set globally. This variable can sync to all nodes, including aggregator and leaf nodes.

ON

enable_broadcast_left_join

When this variable is set to TRUE, the query optimizer can choose the broadcast left join optimization. This is a session variable that can also be set globally. This variable can sync to all nodes, including aggregator and leaf nodes.

TRUE

enable_columnstore_ingest_management

Enables queuing on columnstore ingest queries when the background flusher is behind. This variable can sync to all nodes, including aggregator and leaf nodes.

TRUE

enable_disk_plan_expiration

Enable removal of stale on-disk plans from the plancache directory based on the value of disk_plan_expiration_minutes. This variable can sync to all nodes, including aggregator and leaf nodes.

TRUE

enable_disk_plan_explain

Retains human-readable query plan summaries for compiled query plans. Retained information is available through the SHOW PLAN command. This variable can sync to all aggregators.

OFF

enable_dml_query_forwarding

When set to ON, forwards the Data Manipulation Language (DML) queries from a child aggregator to the master aggregator. Currently, this setting is used for write requests to reference tables, which cannot be executed on child aggregators. This variable can sync to all aggregators.

ON

enable_ir_cache

Allows the on-disk IR cache to be enabled or disabled. This variable can sync to all nodes, including aggregator and leaf nodes.

ON

enable_multipartition_queries

When this variable is set to ON, queries are run per leaf instead of per partition on the leaf; the number of threads decreases and performance increases. This is a session variable that can also be set globally. This variable can sync to all nodes, including aggregator and leaf nodes.

ON

enable_query_forwarding

Enables the forwarding of DDL queries from a child aggregator to the master. sync_permissions must also be enabled for this variable to take effect; otherwise, an error is returned for DDL queries that are run on a child aggregator. For further information, see Node Requirements for SingleStore Commands This variable can sync to all aggregators.

ON

enable_spilling

Enables spilling onto disk for HashGroupBy operations during query processing. When enable_spilling is set to ON, the output of SHOW PROFILE JSON includes the following metrics:

spill_outputted_rows: number of rows spilled to disk

spill_disk_usage: number of bytes spilled to disk.

The sync variables spilling_node_memory_threshold_ratio and spilling_query_operator_memory_threshold also define the threshold limit to start spilling onto disk.

This variable can sync to all nodes, including aggregator and leaf nodes.

OFF

enable_subprocess_tracing

Enables tracing in backup subprocesses. This variable can sync to all nodes, including aggregator and leaf nodes.

OFF

enable_varbuffer_dictionary_compression

Enables compressing identical strings on VARCHAR, VARBINARY, LONGTEXT, LONGBLOB, MEDIUMBLOB, BLOB, TINYBLOB, MEDIUMTEXT, TEXT, and TINYTEXT columns. This variable can sync to all nodes, including aggregator and leaf nodes.

FALSE

estimate_zero_rows_when_sampling_data_is_missing

Uses heuristic or histogram to estimate missing sampling data instead of returning zero rows. False enables new estimating behavior. This variable can sync to all nodes, including aggregator and leaf nodes.

FALSE

exclude_scalar_subselects_from_filters

Specifies how SingleStore estimates the equality predicate for uncorrelated scalar subselects. When set to TRUE, the engine estimates scalar subselects as non-selective. When set to FALSE, the engine estimates the equality predicate for uncorrelated scalar subselects as 1/cardinality, and uses heuristics for all other scalar subselect predicates.

This variable can sync to all nodes, including aggregator and leaf nodes.

FALSE

expected_leaf_core_count

Number of cpu cores expected on each leaf. This variable can sync to all nodes, including aggregator and leaf nodes.

8

explain_expression_limit

Specifies the maximum number of characters to be used by expressions when outputting the EXPLAIN for a query. This variable can sync to all nodes, including aggregator and leaf nodes.

500

explicit_defaults_for_timestamp

Specifies whether the server disables certain nonstandard behaviors for default values and NULL-value handling in TIMESTAMP columns. See Timestamp Behavior for details on this variable. This variable can sync to all nodes, including aggregator and leaf nodes.

ON

exporter_port

Port number to use for prometheus exporter http endpoint. This variable can sync to all aggregators.

0

exporter_ssl_ca

Path to user CA file to use for prometheus exporter connection. This variable can sync to all aggregators.

exporter_ssl_capath

Path to user directory with CA files to use for prometheus exporter connection. This variable can sync to all aggregators.

exporter_ssl_cert

Provides the path to the ssl certificate. This variable can sync to all aggregators.

failover_initial_grace_interval_seconds

If a node moves offline a second time after just failing and then coming back online failover won’t be triggered again if it happens within grace_interval_seconds. This is to avoid a cycle of a problem node failing and coming back online. This variable can sync to all aggregators.

300

failover_on_low_disk

Controls whether the cluster fails over if disk use falls below minimal_disk_space. This variable can sync to all nodes, including aggregator and leaf nodes.

TRUE

force_bushy_join_table_limit

Maximum number of tables in a join which SingleStore will still cost bushy joins. Above this threshold, all possible bushy joins will be chosen regardless of cost. This variable can sync to all nodes, including aggregator and leaf nodes.

18

geo_sphere_radius

The radius of the sphere is used for distance calculation, in meters. (Defaults to average Earth radius.) This variable can sync to all nodes, including aggregator and leaf nodes.

6367444.657120

group_concat_max_len

This variable is the maximum length string GROUP_CONCAT() can return (in bytes). It is a session variable that can be set globally and can be set to any value smaller than or equal to max_allowed_packet. For more information on this variable see max_allowed_packet in the previous section and the GROUP_CONCAT SQL reference. This variable should be updated on all nodes and requires a restart to pick up the new value.

This variable can sync to all nodes, including aggregator and leaf nodes.

16777216

highlight_fragment_size

The size of a block of characters used to logically divide up string columns when using the full-text search highlight functionality. The minimum value is 10 and the maximum value is 65535. This variable can sync to all nodes, including aggregator and leaf nodes.

100

highlight_max_number_fragments

Maximum number of fragments to return from highlight function. This variable can sync to all nodes, including aggregator and leaf nodes.

0

ignore_insert_into_computed_column

When this variable is set to ON, an INSERT into a computed column succeeds and the INSERT ignores the computed column. When this variable is set to OFF, an INSERT into a computed column fails with an error. This is a session variable that can also be set globally. This variable can sync to all nodes, including aggregator and leaf nodes.

OFF

inlist_precision_limit

Specifies the maximum number of list values to consider during histogram estimation for a query with an IN list; a smaller limit will result in a faster compilation time but may also give a less accurate estimate. NOTE: If you adjust this variable, existing queries will need to be recompiled.

This is a session variable that can also be set globally. This variable can sync to all nodes, including aggregator and leaf nodes.

10000

internal_columnstore_idle_flush_wait_seconds

Amount of time (in seconds) the background thread waits before trying to flush on a table with data less than columnstore_flush_bytes. Must be a value between 30 and 86400, and evenly divisible by 5. This variable can sync to all nodes, including aggregator and leaf nodes.

125

internal_max_cte_depth

Specifies the maximum number of nested common table expressions (CTEs). For more information, refer to the WITH topic. This is a session variable that can also be set globally. This variable can sync to all nodes, including aggregator and leaf nodes.

128

jwks_endpoint

The endpoint to get JWKS updates from. If empty, JWKS update from the endpoint does not happen. This variable can sync to all nodes, including aggregator and leaf nodes.

jwks_ssl_ca_certificate

The CA certificate file to be used for SSL JWKS endpoint host verification when HTTPS protocol is used. This variable can sync to all nodes, including aggregator and leaf nodes.

jwks_update_interval

The JWKS update interval in seconds. If set to 0, JWKS refresh does not happen. This variable can sync to all nodes, including aggregator and leaf nodes.

3600

leaf_failure_detection

Whether or not the master aggregator should detect leaf failures and trigger failovers. WARNING: Turning this variable OFF disables failover. This variable can sync to all aggregators.

ON

leaf_failover_fanout

Specifies the placement of replica partitions in a cluster. It can be set to the following modes: paired and load_balanced. For more information, see Enabling High Availability. This variable can sync to all nodes, including aggregator and leaf nodes.

paired

license_visibility

Hides license-related variables from SHOW commands. This variable can sync to all nodes, including aggregator and leaf nodes.

TRUE

load_data_errors_retention_minutes

The amount of time, in minutes, that a LOAD DATA error is stored on disk. This variable must be set to at least 0. These errors are surfaced in information_schema.LOAD_DATA_ERRORS as long as they are stored on disk. This variable can sync to all nodes, including aggregator and leaf nodes.

1440

load_data_internal_compression

If this variable is set to ON and a network bottleneck is detected, LOAD DATA compresses data on the aggregator before forwarding it to the leaves. This variable can sync to all nodes, including aggregator and leaf nodes.

ON

load_data_max_buffer_size

Maximum number of unparsed bytes read by LOAD DATA before throwing an error. This variable can sync to all nodes, including aggregator and leaf nodes.

1073741823

load_data_read_size

Number of bytes read at a time by LOAD DATA. This variable can sync to all nodes, including aggregator and leaf nodes.

8192

load_data_write_size

Number of bytes written at a time by LOAD DATA. This variable can sync to all nodes, including aggregator and leaf nodes.

8192

local_file_system_access_restricted

Restricts access to the local file system. This variable can sync to all nodes, including aggregator and leaf nodes.

FALSE

lock_wait_timeout

Time (in seconds) to wait for a row lock before returning an error. This is a session variable that can also be set globally. This variable can sync to all nodes, including aggregator and leaf nodes.

60

lockfree_backup

Determines whether to use the lockfree backup implementation that is introduced in version 7.5. This variable can sync to all nodes, including aggregator and leaf nodes.

TRUE

log_file_size_partitions

Specifies the log file size (in bytes) for partitions. Can be set to a minimum of 1 MB (in bytes) to a maximum of 1 TB (in bytes), in multiples of 1 MB. A MB contains 1048576 bytes. Each partition is pre-allocated to two log files. Each of these log files has the size log_file_size_partitions. By default, log_file_size_partitions is 256 MB. Changes in the log file size will not affect the sizes of the partitions that are used by existing databases. This variable can sync to all aggregators.

268435456

log_file_size_ref_dbs

Specifies the log file size (in bytes) for reference databases. Can be set to a minimum of 1 MB (in bytes) to maximum of 1 TB (in bytes), in multiples of 1 MB. A MB contains 1048576 bytes. Each reference database is pre-allocated two log files. Each of these log files has the size log_file_size_ref_dbs. By default, log_file_size_ref_dbs is 64 MB. Changes in the log file size will not affect existing databases. This variable can sync to all aggregators.

67108864

max_async_compilation_concurrency

The number of compiles that can run asynchronously at a given time on each node, when interpreter_mode is set to interpret_first. Its effective value is capped at the number of cores on the host machine. If set to 0, it takes the default value.

This variable can sync to all nodes, including aggregator and leaf nodes.

Half the number of cores on the host machine.

max_compilation_memory_mb

The maximum amount of memory (in megabytes) used to compile a query. An error is returned if the query reaches the specified memory limit. This variable can sync to all nodes, including aggregator and leaf nodes.

4096

max_compilation_time_s

The maximum time allowed (in seconds) to compile a query. An error is returned if the query reaches the specified compilation time limit, and any subsequent attempts to compile the query fail. This variable can sync to all nodes, including aggregator and leaf nodes.

600

max_connect_errors

If the number of interrupted connections from a host exceeds the value of max_connect_errors this host will be blocked from further connections. This variable can sync to all nodes, including aggregator and leaf nodes.

10

max_prefetch_threads

Sets the maximum number of threads to use for pre-fetching per scan. This variable can sync to all nodes, including aggregator and leaf nodes.

1

max_prepared_stmt_count

Sets the maximum number of simultaneous prepared statements. This variable can sync to all nodes, including aggregator and leaf nodes.

16382

memsql_original_server_version

Displays the version of SingleStore initially installed. This variable can sync to all nodes, including aggregator and leaf nodes.

memsql_snapshot_trigger_size

Indicates the size (in bytes) of the SingleStore database log upon which a new snapshot will be kicked off. The minimum value this engine variable can be set is 65536. This variable can sync to all nodes, including aggregator and leaf nodes.

134217728

monitoring_retention_period

The period in minutes to keep historical monitoring data. Stored procedures reference this variable to purge values. This variable can sync to all aggregators.

10080

multi_insert_tuple_count

Preferred number of tuples in multi-inserts that aggregators send to leaves. This variable can sync to all nodes, including aggregator and leaf nodes.

20000

multi_statement_xact_idle_timeout

Time (in seconds) that is allowed for a multi-statement transaction to remain idle while holding locks. This is a session variable that can also be set globally. This variable can sync to all aggregators.

300

node_degree_of_parallelism

Controls the number of threads per leaf node for parallel columnstore scans. This variable can sync to all nodes, including aggregator and leaf nodes.

0

null_timestamps_for_testing

Variable to write default TIMESTAMP columns during testing. This variable can sync to all nodes, including aggregator and leaf nodes.

FALSE

optimize_stmt_threshold

A statement count threshold for a procedure or function. When the threshold is exceeded, the procedure or function compiles faster, but the highest level of code optimizations is not applied; however, SQL query optimizations are still in effect. This is a session variable that can also be set globally. This variable can sync to all nodes, including aggregator and leaf nodes.

50

optimizer_beam_width

Controls the beam search width. The default value is 10. Setting this to 1 reverts to the previous behavior (prior to v7.5). Higher values (maximum is 1024) will make the one-time query optimization time slower. This variable can sync to all nodes, including aggregator and leaf nodes.

10

optimizer_disable_subselect_to_join

Setting this to ON disables the optimizer rewrite that converts subselect to joins automatically. Disabling this rewrite may cause queries to fail if they require the rewrite to run. This variable can sync to all nodes, including aggregator and leaf nodes.

OFF

optimizer_empty_tables_limit

Sets a threshold for the number of empty tables that must be present in a query before the optimizer falls back to being rule-based, instead of cost-based. This is a session variable that can also be set globally. This variable can sync to all nodes, including aggregator and leaf nodes.

0

parametrizer_query_max_params

The maximum number of scalar constants a single query can contain. Any query with more than parametrizer_query_max_params will be failed with an error. This variable can sync to all aggregators.

1048576

plan_expiration_minutes

The interval in which a query plan must be reused at least once or it gets unloaded from the query plan cache. This variable can sync to all nodes, including aggregator and leaf nodes.

720

preserve_original_colstore_json

A global variable that preserves NULL values and empty arrays in a JSON object that is written to a columnstore table. Set this variable to OFF to disable this setting. For more information on this variable, see the JSON Guide.

This variable can sync to all nodes, including aggregator and leaf nodes.

AUTO (same as ON)

processlist_rpc_json_max_size

The maximum number of bytes available to the rpc_info column of information_schema.mv_processlist. This variable can sync to all nodes, including aggregator and leaf nodes.

2048

promote_aggregator_timeout_ms

Default timeout (in milliseconds) to wait for a PROMOTE AGGREGATOR operation to finish before giving up. This variable can sync to all aggregators.

180000

query_parallelism

This variable is deprecated. Use query_parallelism_per_leaf_core instead.

Maximum number of simultaneous running queries. This is a session variable that can also be set globally. This variable can sync to all nodes, including aggregator and leaf nodes.

0

query_shape_serialization_enabled

Serializes query shape metadata to disk. Available values are AUTO, ON, and OFF. This variable can sync to all nodes, including aggregator and leaf nodes.

AUTO

query_shape_serialization_file_size_cap

Maximum size (in bytes) for each query shape in a serialization file. This variable can sync to all nodes, including aggregator and leaf nodes.

1048576

query_shape_serialization_show_parameters

Stores actual parameter values to on-disk query shape metadata serialization. This variable can sync to all nodes, including aggregator and leaf nodes.

TRUE

read_advanced_counters

If ON, this variable enables thecollection of advanced statistics. For more information, see the Advanced Statistics section of the Management View Statistics Reference topic. This variable can sync to all nodes, including aggregator and leaf nodes.

OFF

redundancy_level

If set to 1, there is no redundancy across leaves. If set to 2, turns on SingleStore’s High Availability mode. When you set this variable, its value is propagated to other aggregators only. For more information on this variable, see the Managing High Availability topic. This variable can sync to all nodes, including aggregator and leaf nodes.

1

regexp_compile_mem_mb

Specifies the heap allocation limit of the regexp compiler (in megabytes). This variable can sync to all nodes, including aggregator and leaf nodes.

1

regexp_format

Specifies the regular expression format to be used by regular expression functions that you call. Possible values are 'extended' and 'advanced'. When you set this variable, its value is propagated to all nodes. For more information on this variable, see the Using Regular Expression Commands topic. This variable can sync to all nodes, including aggregator and leaf nodes.

'extended'

resource_governor_cpu_limit_mode

Determines whether the mode of control over the CPU for resource pools is HARD or SOFT. For more information on this variable, see CREATE RESOURCE POOL. This variable can sync to all nodes, including aggregator and leaf nodes.

SOFT

resource_pool_statement_selector_function

The function that is executed at runtime that selects the resource pool to use when the user runs a query. The resource_pool variable must be set to system_auto for the resource_pool_statement_selector_function to be used. This variable can sync to all nodes, including aggregator and leaf nodes.

For more information on this variable, see the Setting Resource Limits topic.

result_table_error_lifetime

Sets the lifetime (in seconds) of a result table error before garbage collection. This variable can sync to all nodes, including aggregator and leaf nodes.

1000

service_edition

Displays the service edition: STANDARD, PREMIUM, or DEV. This variable can sync to all nodes, including aggregator and leaf nodes.

STANDARD

show_with_portability_comments

Adds conditional compatibility comments to outputs like SHOW CREATE TABLE to help with portability. This variable can sync to all nodes, including aggregator and leaf nodes.

FALSE

singlebox_optimizer_cost_based_threshold

Maximum number of tables present in a join which will utilize the cost-based singlebox join optimizer. For joins with more than this many tables, singlebox join optimization will use an alternate, rule-based algorithm which is faster but less effective. The singlebox join optimizer is responsible for making decisions about the local aspects of how to efficiently execute a join, such as choosing an index. This variable must be set between 0 and 30 tables.

This is a session variable that can also be set globally. This variable can sync to all nodes, including aggregator and leaf nodes.

18

snapshot_trigger_size

The aggregate size of transaction logs (in bytes) which, when reached, will trigger a new snapshot. This variable can sync to all nodes, including aggregator and leaf nodes.

2147483648

snapshot_wait_for_blob_gc_seconds

Specifies how much time (in seconds) to wait between snapshots before taking a snapshot for blob garbage collection from previous snapshots. This variable can sync to all nodes, including aggregator and leaf nodes.

180

snapshots_to_keep

Number of snapshot and log files to keep for backup and replication. This variable may be set live. This variable can sync to all nodes, including aggregator and leaf nodes.

2

sp_query_dynamic_param

Specifies whether different query plans are generated for CALL queries when arguments to the stored procedure are NULL or not NULL arguments. If enabled, queries that use variables inside stored procedures will use the same query plan. For more details, see the In-Depth Variable Definitions section below.

This variable can sync to all aggregators.

AUTO

spilling_node_memory_threshold_ratio

Memory threshold (ratio of total node memory, 0 - 1) to start spilling. This variable can sync to all nodes, including aggregator and leaf nodes.

0.75

spilling_query_operator_memory_threshold

Memory threshold (bytes) for a query operator to be considered for spilling. This variable can sync to all nodes, including aggregator and leaf nodes.

104857600

sql_select_limit

The maximum number of rows returned by a SELECT query. If the LIMIT clause is specified in a SELECT query, the value in the LIMIT clause overrides sql_select_limit. This is a session variable that can also be set globally. This variable can sync to all nodes, including aggregator and leaf nodes.

2^64-1

subproc_abridged_errors

Makes subprocess errors easier to read. This variable can sync to all nodes, including aggregator and leaf nodes.

TRUE

subprocess_azure_retries

This engine variable is deprecated. Maximum retries for Azure operations. This variable can sync to all nodes, including aggregator and leaf nodes.

10

subprocess_backup_retries

The maximum number of retries for BACKUP upload and download subprocesses. This variable can sync to all nodes, including aggregator and leaf nodes.

3

subprocess_ec2_metadata_timeout_ms

The maximum amount of time (in milliseconds) the engine waits for or retries a request before timing out to return metadata used to verify the cluster is on ec2 from which implicit credentials can be obtained. This variable can sync to all nodes, including aggregator and leaf nodes.

60000

subprocess_io_idle_timeout_ms

The maximum amount of time (in milliseconds) the engine waits for or retries a request before timing out and failing the backup when connecting to cloud providers. When you set this variable, its value is propagated to all nodes. This variable can sync to all nodes, including aggregator and leaf nodes.

600000

subquery_merge_with_outer_joins

This session variable fine-tunes the behavior of the rewrite SubQueryMerge.

If the variable is set to ON, always merge the sub-select with outer join(s). If the variable is set to HEURISTIC, check to see if merging the sub-select with outer join(s) will cause negative performance for the overall query. If the variable is set to OFF, don’t merge sub-selects with outer join(s). If the variable is set to AUTO (this is the default); for v7.5 and previous versions, AUTO will behave like ON. For versions after v7.5, AUTO will behave like HEURISTIC.

This variable can sync to all nodes, including aggregator and leaf nodes.

AUTO

sync_permissions

This aggregator will synchronize its permissions with other aggregators. When ON, this variable also enables DDL forwarding. See Synchronizing Permissions Across Your Cluster for more information about utilizing this variable. This variable can sync to all aggregators.

OFF

sync_slave_timeout

This engine variable is deprecated. Maximum amount of time (in milliseconds) for the primary to wait for acknowledgment from the synchronous replica. This variable can sync to all nodes, including aggregator and leaf nodes.

10000

table_name_case_sensitivity

Sets the case-sensitivity for database object names. If set to OFF, tables, views, table aliases, and user-defined table-valued functions (TVFs) are not case-sensitive. For details on case-sensitivity, refer to Database Object Case-Sensitivity. This variable can sync to all nodes, including aggregator and leaf nodes.

ON

transaction_isolation

Transaction isolation level. This is a session variable that can also be set globally. This variable can sync to all aggregators.

This engine variable is configurable on SingleStore Helios.

READ-COMMITTED

tx_isolation

Transaction isolation level. This is a session variable that can also be set globally. This variable can sync to all aggregators.

This engine variable has been deprecated. It is being kept for backward compatibility. Please see the transaction_isolation engine variable.

READ-COMMITTED

trim_malloc

Determines whether malloc_active_memory usage is trimmed periodically. See SHOW STATUS EXTENDED for more information. This variable can sync to all nodes, including aggregator and leaf nodes.

TRUE

use_vectorized_join

This variable has been deprecated and is no longer operational in SingleStore version 5.0 or newer. This variable can sync to all nodes, including aggregator and leaf nodes.

ON

wait_die_retry_on_die_sleep_factor

See UPDATE and DELETE for more information. This variable can sync to all nodes, including aggregator and leaf nodes.

250 (in milliseconds)

cluster_name

Specifies the cluster name used by the backup. This variable can sync to all nodes, including aggregator and leaf nodes.

JSON Sync Variables

Name

Description

Default

json_compatibility_level

Controls whether the JSON_SET_<type> function creates a keypath if it does not exist. The default is 7.8, which means missing keypaths are not created. This is a session variable that can also be set globally. This variable can sync to all nodes, including aggregator and leaf nodes.

7.8

Password Complexity Sync Variables

For information about how to configure a password complexity policy, see Configuring a Password Policy.

Name

Description

Default

password_max_consec_repeat_chars

The maximum number of consecutive repeated characters allowed (0 to 100). For example, if set to 3, passwords with 4 or more consecutive repeated characters (e.g., aaaa or 1111) are disallowed. This variable can sync to all nodes, including aggregator and leaf nodes.

0

password_max_consec_sequential_chars

The maximum number of consecutive characters allowed (0 to 100). For example, if set to 3, passwords with a 4-letter sequence or longer (e.g. 1234 or abcd) are disallowed. This variable can sync to all nodes, including aggregator and leaf nodes.

0

password_min_length

The minimum number of characters required (0 to 100). This variable can sync to all nodes, including aggregator and leaf nodes.

0

password_min_lowercase_chars

The minimum number of lowercase characters required (0 to 100). This variable can sync to all nodes, including aggregator and leaf nodes.

0

password_min_numeric_chars

The minimum number of numeric digit characters required (0 to 100). This variable can sync to all nodes, including aggregator and leaf nodes.

0

password_min_special_chars

The minimum number of special (non-alphanumeric) characters required (0 to 100). This variable can sync to all nodes, including aggregator and leaf nodes.

0

password_min_uppercase_chars

The minimum number of uppercase characters required (0 to 100). This variable can sync to all nodes, including aggregator and leaf nodes.

0

Pipelines Sync Variables

You cannot set a variable for a specific pipeline – each variable setting applies to all pipelines in the cluster.

Name

Description

Default

advanced_hdfs_pipelines

Specifies whether to enable security features for HDFS pipelines. See Enabling Wire Encryption and Kerberos on HDFS Pipelines for more information. This variable can sync to all nodes, including aggregator and leaf nodes.

OFF

custom_java_pipelines

Allows custom java pipelines. This variable can sync to all nodes, including aggregator and leaf nodes.

OFF

java_pipelines_extractor_class

Implements an extractor interface used for experimental Java pipelines. This variable can sync to all nodes, including aggregator and leaf nodes.

java_pipelines_heap_size

Heap size (in megabytes) for HDFS pipelines. This variable can sync to all nodes, including aggregator and leaf nodes.

8

pipelines_batches_metadata_to_keep

The number of batch metadata entries to persist before they are overwritten by incoming batches. As data is extracted from a source, it’s written in batches to a destination table on a leaf node. Metadata about these batches is temporarily persisted in the master aggregator’s information_schema.PIPELINES_BATCHES table. As new batches are loaded into the database, the oldest batch metadata entries will be removed from the information_schema.PIPELINES_BATCHES table. See the PIPELINES_BATCHES section for more information about this metadata.

This variable can sync to all nodes, including aggregator and leaf nodes.

1000

pipelines_deskew_batch_partitions_threshold

For keyless sharded destination tables, if less than this fraction of batch partitions are active, reshuffle to avoid skew. This variable can sync to all nodes, including aggregator and leaf nodes.

0.75

pipelines_errors_retention_minutes

The amount of time in minutes that a pipeline error is stored on disk. These errors are surfaced in PIPELINES_ERRORS as long as they are stored on disk. This variable must be set to at least 0.

This variable can sync to all nodes, including aggregator and leaf nodes.

1440

pipelines_extractor_core_on_timeout

Generates core dumps on pipeline extractor operations that timeout. This variable can sync to all nodes, including aggregator and leaf nodes.

FALSE

pipelines_extractor_debug_logging

Specifies whether to enable extractor debugging for Kafka or HDFS pipelines. This variable currently does not apply to S3 pipelines. This variable can sync to all nodes, including aggregator and leaf nodes.

OFF

pipelines_extractor_get_offsets_timeout_ms

The maximum time (in milliseconds) to wait for offset data to be returned from the data source before returning an error. Increase this value if you experience timeout errors, such as ERROR 1970 (HY000): Subprocess timed out. Use the value 0 to indicate no timeout.

This variable can sync to all aggregators.

35000

pipelines_extractor_idle_timeout_ms

The maximum time (in milliseconds) that a pipeline will wait for more data from an external source. Use the value 0 to indicate no timeout. This variable can sync to all nodes, including aggregator and leaf nodes.

120000

pipelines_extractor_max_memory

Maximum memory of a pipeline extractor (in megabytes). Minimum value for this engine variable is 1. This variable can sync to all nodes, including aggregator and leaf nodes.

500

pipelines_gc_max_files

Max number of files that will be garbage collected in one iteration. Minimum value for this engine variable is 1. This variable can sync to all aggregators.

10000

pipelines_kafka_version

The Kafka version used for the Kafka extractor. This variable can sync to all nodes, including aggregator and leaf nodes.

This variable is not applicable to current (>= 0.10.0.10) Kafka versions as SingleStore requests a list of supported Kafka broker features using the ApiVersionRequest.

For legacy Kafka, pipelines_kafka_version or kafka_version from a pipeline config is used to determine the list of supported features.

0.10.0.10

pipelines_max_concurrent

The maximum number of pipelines running concurrently. See Load Data with Pipelines page for more information. This variable can sync to all nodes, including aggregator and leaf nodes.

50

pipelines_max_concurrent_batch_partitions

The maximum number of pipeline batch partitions running concurrently. This variable can sync to all nodes, including aggregator and leaf nodes.

0

pipelines_max_errors_per_partition

Deprecated in MemSQL 6.7. The maximum number of error event rows per leaf node partition to persist before they are deleted. Once the specified number of rows in the information_schema.PIPELINES_ERRORS table is reached, the database will eventually remove the oldest rows from the table. The removal mechanism for older error data is based on heuristics. Old errors are guaranteed to exist up to the specified number, but they may not immediately be removed.

This variable can sync to all nodes, including aggregator and leaf nodes.

1000

pipelines_max_offsets_per_batch_partition

The maximum number of data source partition offsets to extract in a single batch transaction. If the data source’s partition contains fewer than the specified number of offsets, all of the partition’s offsets will be batched into the destination table. This variable applies only to Kafka pipelines.

This variable can sync to all aggregators.

1000000

pipelines_max_pooled_extractors

Max pooled extractors for Kafka pipelines. The value must be set to at least 1. This variable can sync to all nodes, including aggregator and leaf nodes.

256

pipelines_max_retries_per_batch_partition

The number of retry attempts for writing batch partition data to the destination table. If pipelines_stop_on_error is set to OFF and the specified retry number is reached without success, the batch partition will be skipped and will not appear in the destination table. If a batch partition is skipped, data loss can occur. If pipelines_stop_on_error is set to ON and the specified retry number is reached without success, the pipeline will stop. No batch partition data will be skipped. This configuration variable applies to the entire batch transaction, which includes extraction from a data source, optional transformation, and loading of the data into the destination table. If the batch transaction fails at any point during extraction, transformation, or loading, it will be retried up to the specified number.

This variable can sync to all aggregators.

4

pipelines_offsets_gc_skew_time_minutes

Amount of time in minutes allowed for pipeline metadata timestamp skew. The minimum value for this engine variable is 1. This variable can sync to all aggregators.

1440

pipelines_pooled_extractor_batches

Max number of batches for a pooled extractor for Kafka pipelines. The value must be set to between 1 and 2048. This variable can sync to all nodes, including aggregator and leaf nodes.

256

pipelines_stderr_bufsize

The buffer size for standard error output (in bytes). Error messages that exceed this size will be truncated when written to the information_schema.PIPELINES_ERRORS table. However, the complete standard error text can be viewed by using the BATCH_ID and querying the information_schema.PIPELINES_BATCHES table.

This variable can sync to all nodes, including aggregator and leaf nodes.

65535

pipelines_stop_on_error

Specifies whether or not each pipeline in the cluster should stop when an error occurs. If set to OFF, batches will be retried up to the number specified in the pipelines_max_retries_per_batch_partition variable. After all retries have failed, the batch will be skipped. When a batch is skipped, data loss can occur. If set to ON, the batch transaction that caused the error will be retried up to the number specified in the pipelines_max_retries_per_batch_partition variable. After all retries have failed, the pipeline will enter a Stopped state and must be manually started.

This variable can sync to all aggregators.

ON

pipelines_stored_proc_exactly_once

If set to ON, run stored procedures from pipelines in a transaction. If this variable is OFF and there is a stored procedure attached to a pipeline, it may only partially succeed. If the procedure fails only the data that was written will remain, instead of rolling back on failure.

This variable can sync to all aggregators.

ON

Unlimited Storage Database (Bottomless) and Point-in-Time Restore Variables

Name

Description

Default

blob_cache_threadpool_max_concurrent

Controls the maximum number of threads available for downloading blobs from unlimited storage. This variable can sync to all nodes, including aggregator and leaf nodes.

32

bottomless_gc_retention_period_minutes

Specifies the amount of time, in minutes, a restore point for a remote storage database is retained after it is created. This determines how far back in time you can do a PITR. For example, to configure retention for 90 days, you should set the value to 90*24*60 = 129600 minutes.

This variable can sync to all nodes, including aggregator and leaf nodes.

1440

bottomless_idle_upload_ms

Number of milliseconds elapsed between unlimited storage uploads. After which, unlimited storage will upload what's queued. This variable can sync to all nodes, including aggregator and leaf nodes.

60000

bottomless_incremental_download_max_buffer_size

Maximum size (in bytes) an unlimited storage download's buffer can grow to. This variable can sync to all nodes, including aggregator and leaf nodes.

1048576

bottomless_skip_create_database_api_checks

Skips API checks when creating a new bottomless database. This variable can sync to all nodes, including aggregator and leaf nodes.

FALSE

bottomless_snapshot_trigger_log_chunks

Determines how often snapshot files are uploaded to the object store. A higher value will cause snapshot files to be uploaded less frequently. Snapshot files are used during recovery. For example, copying bucket files in an object store to a new location and then running ATTACH at the new location will trigger recovery.

This variable can sync to all nodes, including aggregator and leaf nodes.

300

bottomless_upload_max_concurrent

A higher value can increase the concurrency of uploading data updates made on the cluster to the object store. A higher value will increase the CPU and memory usage, and potentially the network usage on the cluster.

This variable can sync to all nodes, including aggregator and leaf nodes.

8

bottomless_upload_throttle_hard_limit_secs

Amount of lag (in seconds) after which all columnstore ingest is throttled, subject to bottomless_upload_throttle_min_disk_limit_mb.

This variable can sync to all nodes, including aggregator and leaf nodes.

1800

bottomless_upload_throttle_min_disk_limit_mb

Minimum disk space when an unlimited storage columnstore ingest kicks in. A value of 0 means there is no minimum and ingest is throttled subject to the hard and soft time limits.

This variable can sync to all nodes, including aggregator and leaf nodes.

0

bottomless_upload_throttle_soft_limit_secs

Amount of lag (in seconds) after which some columnstore ingest may be throttled, subject to bottomless_upload_throttle_min_disk_limit_mb. This engine variable has no effect if the value is higher than the value set for bottomless_upload_throttle_hard_limit_secs.

This variable can sync to all nodes, including aggregator and leaf nodes.

600

Workload Management Sync Variables

Name

Description

Default

resource_usage_model

Whether to train or enable the resource usage model for workload management. When set to ON, workload management will estimate memory usage and queue queries that are estimated to use too much. When set to TRAINONLY, PROFILE can be run on queries to compare the estimated memory usage of queries with their actual use. This is recommended when upgrading from any version prior to 6.7, to allow for the workload manager to do the necessary machine learning for accurate estimates. Once estimated memory use in PROFILE begins matching actual use, consider setting this variable to ON.

This variable can sync to all aggregators.

OFF

workload_management

Specifies whether to enable workload management for the cluster. If this variable is set to ON, the other workload management engine variables will affect the way a query is executed. If set to OFF, the feature is disabled and no queueing or system resource optimization will occur.

This variable can sync to all aggregators.

ON

workload_management_available_leaf_memory_ratio

Sets the ratio of the available leaf memory for workload_management to use when queuing queries. Valid ranges are [0.0 to 1.0]. The variable sets the ratio of the total amount of memory workload management uses when deciding to queue queries. For example, if it is set 0.9, then SingleStore will assume there is only 90% of the total leaf memory available, and will end up running fewer queries before hitting the limit and needing to queue. This variable can sync to all aggregators.

1.0

workload_management_dynamic_resource_allocation

Enables/disables the dynamic reuse of WM queues feature. This variable can sync to all aggregators.

OFF

workload_management_enable_static_partitioning

Enables static resource partitioning for deterministic behavior by workload management. This variable can sync to all aggregators.

FALSE

workload_management_max_connections_per_leaf

The maximum number of connections to use per leaf node in the cluster. This variable can sync to all aggregators.

10000

workload_management_max_queue_depth

The maximum depth of the query queue, which is the maximum number of queries that can be queued. If this number is reached, additional queries will not execute, and an ER_TOO_MANY_QUEUED_QUERIES error will appear.

This variable can sync to all aggregators.

100

workload_management_max_threads_per_leaf

The maximum number of threads to use per leaf. This number correlates with the max_connection_threads engine variable, and they should generally be set to the same value. This variable can sync to all aggregators.

8192

workload_management_memory_queue_threshold

Percentage of memory a query can use before it will get queued. If an individual query is projected to use more than workload_management_memory_queue_threshold * (leaf_maximum_memory - leaf_current_table_memory) / workload_management_expected_aggregators, then it will be queued.

This variable can sync to all aggregators.

0.01

workload_management_memory_queuing

Whether to turn on queuing based on memory usage of queries. This variable can sync to all aggregators.

ON

workload_management_queue_size_allow_upgrade

Workload management contains two queues for execution and depending on the amount of resources required for query execution, a query will be in one of the two available queues (MEDIUM or LARGE). If the MEDIUM queue is saturated and the LARGE queue is less than the value of workload_management_queue_size_allow_upgrade, the query will move from the MEDIUM queue to the LARGE queue.

This variable can sync to all aggregators.

1

workload_management_queue_time_warning_ratio

Specifies when a warning will appear based on the ratio of time spent by a query in the queue versus the actual execution time of the query. For example, if a query waits in the queue for one second and it takes four seconds to execute, the ratio is 1:4 or 0.25. Once the specified ratio is reached for a query, a ER_QUERY_QUEUED_WARNING warning will appear.

This variable can sync to all aggregators.

0.5

workload_management_queue_timeout

The time duration (in seconds) after which a query times out and is removed from the queue without being executed.

This variable can sync to all aggregators.

3600

Non-Sync Variables List

See the engine variables overview to learn how non-sync variables work.

Note

All of the non-sync variables listed in the following table are global variables, except when noted as a session variable that can also be set globally in the Description column.

The variables listed in the following tables can be set to take effect on node startup and can be set to take effect while the node is running. Exceptions are noted in the fourth column.

Basic Variables

Name

Description

Default Setting

Exceptions to When Variable can be Set

bind_address

Defines the IP address to use to listen for incoming connections.

If the address is 0.0.0.0, SingleStore accepts connections on all network interfaces; otherwise, it only accepts connections for the given IP address.

0.0.0.0

You can only set this variable while the node is offline. The changes to this variable will take effect on the next start of the node.

datadir

Directory path for the data directory. This directory contains snapshots, logs, and columnstore segments.

You can only set this variable while the node is offline. The changes to this variable will take effect on the next start of the node.

div_precision_increment

The number of digits by which to increase the scale of division results performed with the division (/) operator.

4

You can set this variable but it is currently not supported in SingleStore. SingleStore always returns the results to the fourth precision value.

flush_before_replicate

If ON, data is written and flushed to disk locally before being replicated. This will increase replication latency but will guarantee that replicas will not flush data to disk before the master does.

OFF

You can only set this variable while the node is offline. The changes to this variable will take effect on the next start of the node.

master_aggregator

Appending master_aggregator (with no arguments) to the end of a node’s memsql.cnf specifies that node as the cluster’s master aggregator. For more information on this variable, see Master Aggregator.

You can only set this variable while the node is offline. The changes to this variable will take effect on the next start of the node.

maximum_blob_cache_size_mb

Specifies the maximum blob cache size per leaf node. The cache stores recently accessed columnstore data objects and is located on the cluster's local storage. The cache exists in datadir. The cache size is limited by available space in  datadir.

maximum_blob_cache_size_percentsetting overrides this variable.

Version <=7.9

For disk range 0-40GB, cache value is 100% of disk.

For disk range 40GB+, cache value is 80% of disk.

Version =>8.0

For disk range 0-40GB, cache value is 100% of disk.

For disk range 40-140GB, cache value is 40GB.

For disk range 140-666GB, cache value is 75% of disk.

For disk range 667GB+, cache value is 90% of disk - 100GB.

maximum_memory

Maximum memory SingleStore will use, in MB. WARNING: Do not set it to more than the default.

90% of System RAM or System RAM minus 10 GB, whichever is greater

You are required to restart a node only when the variable is being reduced.

maximum_table_memory

Maximum memory SingleStore will use for table storage, in MB. WARNING: Do not set it to more than the default.

If maximum_memory >= 10 GB, then 90% of maximum_memory. Else if maximum_memory >= 1.25 GB, then maximum_memory - 1 GB

You are required to restart a node only when the variable is being reduced.

port

SingleStore port number.

3306

You can only set this variable while the node is offline. The changes to this variable will take effect on the next start of the node.

reported_hostname

The hostname or IP of the machine in use.

plancachedir

Directory path for the plancache directory. This directory contains compiled plans used for codegen.

./plancache

You can only set this variable while the node is offline. The changes to this variable will take effect on the next start of the node.

tracelogsdir

Directory path for the tracelogs directory. This directory contains log files, including memsql.log and the query log.

./tracelogs

You can only set this variable while the node is offline. The changes to this variable will take effect on the next start of the node.

Connection Management Variables

Name

Description

Default Setting

Exceptions to When Variable can be Set

connect_timeout

The number of seconds the node’s SingleStore waits for a connection packet before sending back a Bad handshake response.

10

max_allowed_packet

Maximum allowed protocol packet size (in bytes). This is a session variable that can also be set globally. The maximum value is 1GB.

104857600

You can only set this variable while the node is offline. The changes to this variable will take effect on the next start of the node.

max_connections

The number of simultaneous clients allowed. For more, see In-Depth Variable Definitions.

100000

max_connection_threads

The maximum number of kernel threads for processing queries. For more, see In-Depth Variable Definitions.

8192

max_dedicated_admin_connections

Specifies the number of connections reserved for admin users (users granted the SUPER permission). This is the number of connections over and above the max_connections. Its purpose is to allow admin users to login even if max_connections number of non-admin users are already logged in. For example, if max_connections is set to 100 and max_dedicated_admin_connections is set to 5, then even if 100 non-admin user connections are active at any time, 5 admin users can still log in. This is to prevent administrative users from being locked out of the system during heavy traffic.

You connect via a socket file, for example: mysql -u root --socket ‘/home/xyz/memsql/bincache/cluster_10000/10000/data/memsql.sock' instead of using mysql -u root -h <host> --port <port>

Refer to the Dedicated Admin Connections page for how to create dedicated admin connections.

5

max_pooled_connections

The maximum number of stashed connections per leaf. For more information on this variable, see In-Depth Variable Definitions.

1024

You can only set this variable while the node is offline. The changes to this variable will take effect on the next start of the node.

skip_name_resolve

Controls whether to perform name resolution. By default, AUTO will only perform a reverse Domain Name System (DNS) lookup if there are any host-based security rules. Options include AUTO, OFF, or ON. For more information on this variable, see In-Depth Variable Definitions.

AUTO

You can only set this variable while the node is offline. The changes to this variable will take effect on the next start of the node.

Database Optimization Variables

Name

Description

Default Setting

Exceptions to When Variable can be Set

columnar_segment_rows

Deprecated alias to columnstore_segment_rows.

1024000

columnstore_window_size

This variable has been deprecated and is no longer operational in SingleStore.

enable_sort_on_load

Whether to merge the newly loaded data segments (for LOAD DATA, INSERT SELECT, and PIPELINE statements) into a single sorted run.

ON

net_first_packet_read_timeout

Number of seconds an online failover will wait to drain open idle write transactions before issuing a kill to those transactions. After the idle write transactions are killed, an application-level retry is required.

30

net_read_timeout

Number of seconds to wait for more data from a connection before aborting the read. This is a session variable that can also be set globally.

3600

net_write_timeout

Number of seconds to wait for a block to be written to a connection before aborting the write. This is a session variable that can also be set globally.

3600

optimize_columnar_tables

This variable has been deprecated and is no longer operational in SingleStore.

transaction_buffer

This variable has been deprecated and is no longer operational in SingleStore.

Logging Variables

Name

Description

Default Setting

Exceptions to When Variable can be Set

core_file

Turning core_file on or off determines whether or not full core dumps are produced upon a crash.

ON

critical_diagnostics

Sends usage and critical error diagnostics to SingleStore.

ON

general_log

If set to ON, every query will be logged to a table or log file. If set to PARTIAL, will only log when load is light. OFF turns off logging.

OFF

general_log_file

Log connections and queries to a given file.

'./query.log'

warn_level

Defines how SingleStore behaves when it encounters unsupported functionality. For more information, visit the Unsupported Feature List section of the Unsupported MySQL Features topic.

WARNINGS

Password Complexity Non-Sync Variables

Name

Description

Default Setting

Exceptions to When Variable can be Set

expire_root_password

Specifies whether the root password can expire. If set to ON, the root password will expire after the password_expiration_seconds duration is reached.

OFF

password_expiration_seconds

The time (in seconds) before a password expires. The value 0 indicates that the password will never expire.

0

password_history_count

The number of previous passwords per user that SingleStore will store and disallow from reuse. The value 0 indicates that any previous password can be reused. The maximum is 10.

0

Pipelines Non-Sync Variables

You cannot set a variable for a specific pipeline – each variable setting applies to all pipelines in the cluster.

Name

Description

Default Setting

Exceptions to When Variable can be Set

java_pipelines_java_home

Specifies the value that SingleStore sets the node’s operating system environment variable JAVA_HOME to. If java_pipelines_java_home is not set, SingleStore does not set JAVA_HOME.

You can only set this variable while the node is offline. The changes to this variable will take effect on the next start of the node.

java_pipelines_java_path

Specifies the path that the HDFS extractor uses to run Java.

Security Variables

Name

Description

Default Setting

Exceptions to When Variable can be Set

node_replication_ssl_only

When this variable is set to ON (assuming that SSL is enabled), SSL is used for cross-cluster replication, but not for intra-cluster communication. When the variable is OFF, SSL is used for both cross-cluster as well as intra-cluster replication.

OFF

You can only set this variable while the node is offline. The changes to this variable will take effect on the next start of the node.

ssl_ca

CA file to be used for SSL connections.

You can only set this variable while the node is offline. The changes to this variable will take effect on the next start of the node.

ssl_capath

CA directory to be used for SSL connections.

You can only set this variable while the node is offline. The changes to this variable will take effect on the next start of the node.

ssl_cert

Certificate file to be used for SSL connections.

You can only set this variable while the node is offline. The changes to this variable will take effect on the next start of the node.

ssl_cipher

Cipher to be used for SSL/TLS connections.

You must manually enumerate all desired cipher suites.

For example:

ssl_cipher  = DHE-RSA-AES128-GCM-SHA256:DHE-RSA-AES256-GCM-SHA384:ECDHE-ECDSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES256-GCM-SHA384:ECDHE-RSA-AES128-GCM-SHA256:ECDHE-RSA-AES256-GCM-SHA384

This will enable only the enumerated cipher suites in both the SingleStore engine and websocket proxy.

You can only set this variable while the node is offline. The changes to this variable will take effect on the next start of the node.

ssl_fips_mode

When this variable is set to ON, FIPS mode is enabled.

OFF

You can only set this variable while the node is offline. The changes to this variable will take effect on the next start of the node.

ssl_key

Public-private key pair file to be used for SSL connections.

You can only set this variable while the node is offline. The changes to this variable will take effect on the next start of the node.

ssl_key_passphrase

Passphrase for encrypted ssl_key.

You can only set this variable while the node is offline. The changes to this variable will take effect on the next start of the node.

Other Variables

Name

Description

Default Setting

Exceptions to When Variable can be Set

allow_unsupported_filesystem

Enables support for filesystems without fallocate support.

FALSE

activities_delta_sleep_s

activities_delta_sleep_s specifies an interval of time (in seconds) which is used by the mv_activities_cumulative variable to determine recent resource usage. For more information, see Management View Reference. This is a session variable that can also be set globally.

1

auditlogsdir

Specifies the local or network directory to write log files. For more information on this variable, see Configure Audit Logging.

/<path-to-node-directory>/auditlogs

You can only set this variable while the node is offline. The changes to this variable will take effect on the next start of the node.

auditlog_level

auditlog_level is used to specify the level of logging in a node. There are 11 logging levels. For more information on this variable, see Audit Logging Levels.

OFF

You can only set this variable while the node is offline. The changes to this variable will take effect on the next start of the node.

auditlog_rotation_size

Specifies the maximum size per log file (in bytes). Required if logging is enabled. For more information on this variable, see Configure Audit Logging.

134217728

You can only set this variable while the node is offline. The changes to this variable will take effect on the next start of the node.

auditlog_rotation_time

Specifies the maximum time duration to write to a single log file (in seconds). For more information on this variable, see Configure Audit Logging.

3600

You can only set this variable while the node is offline. The changes to this variable will take effect on the next start of the node.

autocommit

If ON, individual SQL statements are run in a single transaction that commits when the statement finishes. If OFF, individual SQL statements run in a multi-statement transaction by default, which must be ended explicitly with COMMIT or ROLLBACK.

ON

backup_max_threads

Specifies the maximum number of concurrent threads, per leaf, used to run a backup on each leaf. By default, a backup of a leaf uses one thread per partition and there is no restriction on the number of threads created (aside from system limitations).

The number of threads, if set, is shared among active backups, with the first backup using as many as it needs. The remainder, if any, goes to the next backup, etc.

0

basedir

Specifies the path where the installation directory can be found.

You can only set this variable while the node is offline. The changes to this variable will take effect on the next start of the node.

compat_version

This variable exists for backwards compatibility with MySQL. It specifies the MySQL compatibility version.

compile_only

If ON, SingleStore will compile, but not run, each query it receives. This is a session variable that can also be set globally.

OFF

You cannot set this variable while the node is offline.

convert_nonunique_hash_to_skiplist

When this variable is set as TRUE, any non-unique hash index will be recovered as a skiplist index. Any newly created table will also have its non-unique hash indexes created as skiplists.

TRUE

You can only set this variable while the node is offline. The changes to this variable will take effect on the next start of the node.

core_file_mode

Specifies the type of core dump to generate if SingleStore terminates abnormally. Options include NONE, PARTIAL, or FULL. PARTIAL omits most user data to keep the dump small, while a FULL core dump uses the equivalent amount of disk space as the amount of memory used by SingleStore.

PARTIAL

You can only set this variable while the node is offline. The changes to this variable will take effect on the next start of the node.

default_time_zone

This is a configuration variable that is used to set the time zone on a host by specifying an offset from UTC time. Refer to the Setting the Time Zone page for details on this variable.

flat_plancache

This variable has been deprecated and is no longer operational in SingleStore version 5.0 or newer.

gssapi_keytab_path

The local path to the keytab file created on the KDC. For more information on this variable, see Kerberos Authentication.

You can only set this variable while the node is offline. The changes to this variable will take effect on the next start of the node.

gssapi_principal_name

The SPN for SingleStore that was created on the KDC. For more information on this variable, see Kerberos Authentication.

You can only set this variable while the node is offline. The changes to this variable will take effect on the next start of the node.

hostname

Speficies the server hostname at startup. This variable is read-only.

http_api

Enables SingleStore's Data API endpoints on the built-in HTTP server when used in conjunction with the https_proxy_port or the http_proxy_port variable. This variable must be set locally on each aggregator node on which the Data API needs to be run.

ON

http_api_pool_capacity

Specifies the maximum number of connections each pool can have open at any point in time when using the SingleStore's Data API. In other words, this variable controls the number of parallel queries that can be run by a SingleStore user.

2048

http_api_pool_max_idle

Specifies the maximum number of idle connections to retain in each connection pool when using the SingleStore's Data API. Each connection pool is associated with a single SingleStore user.

1024

http_proxy_port

Specifies the port on which the WebSocket proxy should run. This WebSocket allows Studio to connect with the SingleStore server over HTTP using TLS .

The http_proxy_port variable when used in conjunction with the http_api variable provides access to SingleStore's Data API via the HTTP server.  

Note: This variable is mutually exclusive with the https_proxy_port variable.

https_proxy_port

Specifies the port on which WebSocket proxy should run. This WebSocket allows Studio to connect with the SingleStore server over HTTPS using TLS. It requires SSL to be enabled.

The https_proxy_port variable when used in conjunction with the http_api variable provides access to SingleStore's Data API via the HTTPS server. 

Note: This variable is mutually exclusive with the http_proxy_port variable.

idle_thread_lifetime_seconds

Idle threads terminate after the specified number of seconds.

3600

identity

Contains the value of last_insert_id. This is a session variable that can also be set globally.

You cannot set this variable while the node is offline.

internal_columnstore_validate_blob_after_write

If set to ON, the checksum of a blob is verified immediately after it is created.

OFF

internal_columnstore_validate_blob_before_read

If set to ON, the checksum of a blob is verified before it is read.

OFF

interpreter_mode

Controls whether SingleStore compiles or interprets query plans. When compiling, this variable also controls how the query plans are compiled. Allowed settings include: llvm, mbc, compile, compile_lite, compile_lite_first, interpret, interpret_first. See Code Generation for more details. This is a session variable that can also be set globally.

interpret_first

json_agg_max_len

Controls the maximum length (in bytes) of JSON_AGG output. It can be set to a minimum of 1 MB (in bytes). This is a session variable that can also be set globally.

16777216

kerberos_server_keytab

This variable has been deprecated and is no longer operational in SingleStore.

lc_messages_dir

Specifies the directory where error messages are stored.

/var/lib/memsql/share/

You can only set this variable while the node is offline. The changes to this variable will take effect on the next start of the node.

load_data_batch_size

This variable has been deprecated and is no longer operational in SingleStore version 5.0 or newer.

materialize_ctes

Specifies the setting to materialize the common table expressions (CTEs). If set to OFF, it inlines the CTEs similar to view definitions. If set to ALL, then each CTE appearing more than once in a query is materialized into an internal result table. If set to AUTO, optimizations are enabled. Currently, only the predicate pushdown optimization is available.

This engine variable is experimental and should not be enabled in production.

For more information, see WITH (Common Table Expressions).

OFF

memsql_id

The ID of the SingleStore node whose config you would like to update. This variable is read-only.

memsql_version

SingleStore version number. This variable is read-only.

memsql_version_date

The build date of the SingleStore version currently running. This variable is read-only.

memsql_version_hash

SingleStore version hash. This variable is read-only.

minimal_disk_space

Sets the minimal available disk space allowed (in megabytes). If the available disk space falls below minimal_disk_space on the disk where the data directories (such as logs, snapshots, blobs, plancache, and tracelogs) are mounted, SingleStore will halt new write queries.

100

net_buffer_length

Specifies the size (in bytes) of the connection buffer and the result buffer with which each client thread starts. This is a session variable that can also be set globally.

102400

pid_file

The path name of the process ID file.

'.pid'

You can only set this variable while the node is offline. The changes to this variable will take effect on the next start of the node.

profile_for_debug

This variable enables the collection of additional data with PROFILE that can be displayed using SHOW PROFILE JSON and is useful for troubleshooting query optimizer issues. For more information, see PROFILE. This is a session variable that can also be set globally.

OFF

protocol_version

Specifies the version of the client/server protocol. This variable is read-only.

10

ref_repl_mgmt_threads

The number of threads pooled for replication management of reference databases. Must be between 1 and 100.

8

repl_mgmt_threads

The number of threads pooled for replication management of partition databases. Must be between 1 and 100.

0

resource_pool

Specifies the resource pool to be used. This is a session variable that can also be set globally. It can be set while a node is running, but cannot be set on node startup. For more information on this variable, see Set Resource Limits.

default_pool

rewrite_verbose

This variable determines verbose debug tracing for rewrites. OFF: no output. ON: query before and after each rewrite; some extra tracing inside some rewrites.

OFF

saml_assertion_audience

Specifies a single audience restriction for the SAML assertion.

For more information on this variable, see Configuring SAML Global Variables.

You can only set this variable while the node is offline. The changes to this variable will take effect on the next start of the node.

saml_message_recipient

Specifies the intended recipient for a wrapped encryption key.

For more information on this variable, see Configuring SAML Global Variables.

You can only set this variable while the node is offline. The changes to this variable will take effect on the next start of the node.

saml_private_decryption_key

Specifies the file path for the private key used to decrypt an encrypted assertion.

For more information on this variable, see Configuring SAML Global Variables.

You can only set this variable while the node is offline. The changes to this variable will take effect on the next start of the node.

saml_require_encryption

Specifies if authentication should fail when both the SAML response and SAML assertion are unencrypted.

For more information on this variable, see Configuring SAML Global Variables.

OFF

You can only set this variable while the node is offline. The changes to this variable will take effect on the next start of the node.

saml_require_signature_validation

Specifies if authentication should fail when both the SAML response and SAML assertion are unsigned.

For more information on this variable, see Configuring SAML Global Variables.

OFF

You can only set this variable while the node is offline. The changes to this variable will take effect on the next start of the node.

saml_use_NameID

Specifies whether a username value should be extracted from the <saml:NameID> element in a SAML assertion.

For more information on this variable, see Configuring SAML Global Variables.

OFF

You can only set this variable while the node is offline. The changes to this variable will take effect on the next start of the node.

saml_user_name_attribute

Specifies the username attribute in a SAML assertion that should be used to determine if a user exists in the database.

For more information on this variable, see Configuring SAML Global Variables.

You can only set this variable while the node is offline. The changes to this variable will take effect on the next start of the node.

saml_x509_certificate

Specifies the file path for the identity provider’s public x509 signing certificate.

For more information on this variable, see Configuring SAML Global Variables.

You can only set this variable while the node is offline. The changes to this variable will take effect on the next start of the node.

secure_file_priv

Specifies the directory to which any import or export operations should be limited, or unrestricted import and export if set to NULL.

You can only set this variable while the node is offline. The changes to this variable will take effect on the next start of the node.

socket

Specifies the Unix socket file to use for local connections.

'memsql.sock'

You can only set this variable while the node is offline. The changes to this variable will take effect on the next start of the node.

spilling_minimal_disk_space

If the node's disk space (in MBs) is less than  spilling_minimal_disk_space, queries that require spilling will fail instead of spilling to disk.

500

sql_quote_show_create

If ON, identifiers are quoted by the server for SHOW CREATE commands. This is a session variable that can also be set globally.

ON

system_time_zone

System time zone. This variable is read-only.

UTC

table_precompiled_header

This variable has been deprecated and is no longer operational in SingleStore version 5.0 or newer.

thread_cache_size

Specifies how many inactive connection threads should be cached.

0

thread_handling

Determines how the server handles connection threads. A value of no-threads will cause the server to use a single thread to handle one connection. one-thread-per-connection causes the server to use one thread for each client connection.

one-thread-per-connection

You can only set this variable while the node is offline. The changes to this variable will take effect on the next start of the node.

thread_stack

Specifies the stack size (in bytes) for each thread.

The value must be between 131072 and 134217728, and also evenly divisible by 1024.

1048576

You can only set this variable while the node is offline. The changes to this variable will take effect on the next start of the node.

time_zone

The current time zone. By default, it is set to the same value as that of system_time_zone. This is a session variable that can also be set globally, but it is a noop, for MySQL compatibility. Change the time zone using your OS (preferred) or the Setting the Time Zone config variable.

SYSTEM

timestamp

Used to capture the original timestamp of the client. This is a session variable that can also be set globally. It can be set while a node is running, but cannot be set on node startup.

0

tls_version

Indicates the TLS version with which to configure the node. For more information, see Specifying the TLS Version

You can only set this variable while the node is offline. The changes to this variable will take effect on the next start of the node.

tmpdir

MemSQL Ops writes temporary data to /tmp and requires available free space. It is possible to change the temporary directory by changing tmpdir.

You can only set this variable while the node is offline. The changes to this variable will take effect on the next start of the node.

version_compile_machine

The type of the server binary. This variable is read-only.

x86_64

version_compile_os

The operating system on which SingleStore was built. This variable is read-only.

Linux

In-Depth Variable Definitions

This section contains supplemental information about engine variables that require more understanding to configure properly. Ensure that you understand these details before modifying any engine variables listed in this section.

max_connection_threads

max_connection_threads is the maximum number of kernel-level threads the SingleStore node will use to handle connections (i.e. running queries - not including background threads). Each query takes exactly one thread on the aggregator, so the max_connection_threads setting on an aggregator is essentially a limit on the number of queries - including internal SingleStore queries - the aggregator will run simultaneously. When the limit is reached, further queries are queued until a thread becomes available.

The maximum value of max_connection_threads is 12192. The default for aggregators is 192, and the default for leaves is 8192.

If the max_connection_threads limit is reached on an aggregator, queries are queued until a thread becomes available, which can potentially cause unresponsiveness, latency spikes, and failures. On the other hand, in rarer cases, too many queries running simultaneously on some workloads (such as high volume concurrent writes on larger clusters) can exhaust cluster resources. Typically, if the max_connection_threads limit is reached on an aggregator, increasing the limit should solve the problem. If raising the limit causes further problems on your workload, you may need to explore other avenues to resolve the root cause.

max_pooled_connections

max_pooled_connections is the maximum number of connections cached between nodes. Every connection that is opened to run a query between nodes will be left open and reused until the limit set by max_pooled_connections is reached. If more connections are needed to run a workload, the connections will be opened/closed as needed as the query runs. This is why running SHOW PROCESSLIST on a leaf that has been running a workload will show both open and idle connections.

The default value is 1024, which is typically sufficient. Some heavy distributed join workloads may need more internode connections, in which case this variable can be changed.

max_connections

max_connections is the maximum number of connections that can be open to a SingleStore node at one time. The default is 100,000, which is the maximum allowed. There is no reason to change the value of max_connections as lowering its value would not impact resource allocation.

node_degree_of_parallelism

Sets the per-node degree of parallelism for applicable query operators. If the variable is set to 0 the parallelism is chosen automatically (i.e. no change from default behavior).

When node_degree_of_parallelism is set to a value other than 0, say n, then n processor threads are started on each leaf node to handle all the partitions on that leaf.

interpreter_mode

Controls both whether and how SingleStore compiles or interprets query plans. Allowed settings include the following modes:

  • llvm or compile: Queries are compiled to machine code. compile is an alias for llvm.

  • mbc or interpret : Queries are interpreted and not compiled. interpret is an alias for mbc.

  • interpret_first: Queries start out as interpreted and dynamically switch to compiled during the first query execution. This mode can help improve ad-hoc query performance. The interpret_first mode is turned on by default and can be used in production deployments.

This variable can also be set as a query option by adding OPTION (interpreter_mode = { interpret | compile | interpret_first}) at the end of the query. interpreter_mode is non-operational on the leaf node since it is forwarded from the aggregator to the leaf.

For more information on interpreter_mode and query compilation behavior, see Code Generation.

sp_query_dynamic_param

Syntax

SET GLOBAL sp_query_dynamic_param = {ON | OFF};

Remarks

When set to ON, sp_query_dynamic_param changes the plan generation behavior as follows:

  • Calls to a stored procedure use the same plan, regardless of whether the arguments to the procedure are NULL or not NULL. For example, the following procedure calls use the same plan:

    CALL proc_test(1, NULL, 7);
    CALL proc_test("Adam", NULL, 4);
    CALL proc_test(NULL, "Sam", "Smith");

    The following procedure calls use different query plans, because of different number of arguments:

    CALL proc_test(NULL);
    CALL proc_test(NULL, 7);
  • All occurrences of a query, inside a stored procedure, that uses variables will use the same plan, regardless of whether the variable values are NULL or not NULL. This applies when such a query appears in the stored procedure multiple times or when the query runs repeatedly via multiple calls to the stored procedure.

When sp_query_dynamic_param is enabled, compilation time and plancache size are reduced.

Note

The OFF setting of sp_query_dynamic_param disables the plan generation behavior described above. This setting will no longer be supported around May 2021. You should remove any dependencies on the setting by this time.

Example: Changes in Type Conversion Behavior

A query returns different results for cases where an invalid conversion is being done, depending on the value of sp_query_dynamic_param. For example, in the query below, 9006060 is an invalid value for TIME data type.

If sp_query_dynamic_param is disabled,

DELIMITER //
CREATE OR REPLACE PROCEDURE sp_ex (t TIME) AS
BEGIN
ECHO SELECT t AS "t";
END; //
DELIMITER ;
CALL sp_ex(9006060);
+-----------+
| t         |
+-----------+
| 838:59:59 |
+-----------+

If sp_query_dynamic_param is enabled,

DELIMITER //
CREATE OR REPLACE PROCEDURE sp_ex (t TIME) AS
BEGIN
ECHO SELECT t AS "t";
END; //
DELIMITER ;
CALL sp_ex(9006060);
+----------+
| t        |
+----------+
| 00:00:00 |
+----------+

json_extract_string_collation

json_extract_string_collation allows you to control the collation of JSON_EXTRACT_STRING output. It takes the following values:

  • json: Specifies that the result of JSON_EXACT_STRING will use the collation of the JSON argument. If the collation of the argument is not set, the default (utf8_bin) is used.

  • Since the old AUTO/SERVER behavior is error-prone, existing customers should set this engine variable to SERVER_V2 immediately after upgrading. See the support bulletin: Using the JSON utf8mb4 Column...

  • server/auto: Specifies that the collation of JSON_EXTRACT_STRING result will be the same as collation of the server defined by the collation_server variable. Generally, this value is utf8_general_ci.

  • SERVER_V1: Functions the same as server/auto, in previous version older than 7.8.21 but without the parser lockdown.

  • SERVER_V2: When the collation_server is utf8 and the JSON object is utf8mb4, the output will use utf8mb4. This is the default beginning with version 7.8.21

skip_name_resolve

skip_name_resolve controls whether the server performs name resolution via DNS lookup upon login. The possible values for this variable are:

  • AUTO: By default, AUTO will only perform a reverse DNS lookup if there are any host-based security rules.

  • OFF: Always uses name resolution. This is not recommended without a reliable existing DNS.

  • ON: Disables DNS and never uses name resolution. The server matches only IP addresses (not host names) to the list of grants. This can improve performance for users with a slow DNS and many hosts.

sql_mode

sql_mode specifies the current SQL mode (or modes) that the current session runs in. Currently, SingleStore supports the following SQL modes:

  • ANSI_QUOTES: Changes " to be treated as the identifier quote character (like the ` quote character) and not as a string quote character. You can still use ` as an identifier quote character with this mode enabled. With ANSI_QUOTES enabled, you cannot use double quotation marks to quote literal strings because they are interpreted as identifiers.

  • ANSI: Sets sql_mode to STRICT_ALL_TABLES, ONLY_FULL_GROUP_BY, PIPES_AS_CONCAT, and ANSI_QUOTES together.

  • NO_AUTO_CREATE_USER: If you grant privileges to a non-existing user, the user will be created automatically unless NO_AUTO_CREATE_USER is specified in sql_mode.

  • ONLY_FULL_GROUP_BY: Throws an error when fields in the SELECT list, HAVING condition, or ORDER BY list are not in a GROUP BY clause, or are outside of an aggregate function. This is because the result set could include multiple possible values for the non-grouped, non-aggregated field.

  • PIPES_AS_CONCAT: Changes || to be treated as string concatenation operation and not as an OR operation. You can still use built-in function CONCAT as a string concatenation with this mode enabled.

  • STRICT_ALL_TABLES: Always enabled, regardless of other modes being enabled or not. If set to an empty value, behavior is the same as if STRICT_ALL_TABLES is set.

Example

The following examples show how to set sql_mode with one more or more values.

SELECT @@sql_mode;
+-------------------+
| @@sql_mode        |
+-------------------+
| STRICT_ALL_TABLES |
+-------------------+
1 row in set (0.00 sec)
-- Set sql_mode to include ONLY_FULL_GROUP_BY
SET sql_mode = 'ONLY_FULL_GROUP_BY';
SELECT @@sql_mode;
+--------------------------------------+
| @@sql_mode                           |
+--------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_ALL_TABLES |
+--------------------------------------+
1 row in set (0.01 sec)
-- Set sql_mode to ANSI_QUOTES, which replaces ONLY_FULL_GROUP_BY
SET sql_mode = 'ANSI_QUOTES';
SELECT @@sql_mode;
+-------------------------------+
| @@sql_mode                    |
+-------------------------------+
| ANSI_QUOTES,STRICT_ALL_TABLES |
+-------------------------------+
1 row in set (0.00 sec)
-- Set ONLY_FULL_GROUP_BY and ANSI_QUOTES together
SET sql_mode = 'ONLY_FULL_GROUP_BY,ANSI_QUOTES';
SELECT @@sql_mode;
+--------------------------------------------------+
| @@sql_mode                                       |
+--------------------------------------------------+
| ANSI_QUOTES,ONLY_FULL_GROUP_BY,STRICT_ALL_TABLES |
+--------------------------------------------------+
1 row in set (0.01 sec)

ssl_key_passphrase

The passphrase specified with ssl_key_passphrase is used to decrypt the encrypted Privacy Enhanced Mail (PEM) or Password Protected (PASS) file. It can be added for encrypted SSL either at runtime or in memsql.cnf file and can used for both intra-cluster and inter-cluster configuration. The PEM or PASS file is decrypted once at boot time.

Last modified: November 30, 2022

Was this article helpful?