List of Engine Variables
On this page
The following tables list the sync engine variables and non-sync engine variables.
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.Description
column in the following table.
Note
Setting global variables requires SingleStoreDB Cloud version 7.
The Description
column will denote if an engine variable is configurable in Cloud.
|
Name |
Description |
Default |
|---|---|---|
|
|
Shows a JSON array of users whose query runs are not considered active to measure the cluster's idle time. |
|
|
|
You can use this variable to control the supported key size. List of allowed values are: Optionally an encryption mode string in the same format can be provided to the AES_ This variable can sync to all nodes, including aggregator and leaf nodes. This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
Deprecated in MemSQL 6. |
|
|
|
Allows the query optimizer to materialize and reuse common table expressions without recomputing them when the query contains |
|
|
|
Allows modification of global variables ( This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
Determines whether to allow errors while consuming '/proc/cpuinfo' to append CPU flags to the code generation's hash. |
|
|
|
Specifies if the aggregator will try to attach a node automatically after it has been marked offline, but starts responding to heartbeats again. |
|
|
|
Determines the mode of auto profiling. |
|
|
|
Number of seconds a table's incremental autostats can go unused before being flushed to disk. |
|
|
|
Number of seconds to wait after a new node has attached to the workspace before running a rebalance. |
|
|
|
Specifies if every audit log record is synchronously written and persisted to the disk. |
|
|
|
Indicates the retention period (in days) for audit log files. |
|
|
|
How often (in seconds) background statistics will check for out-of-date statistics (rowstore only). |
|
|
|
Threshold as a fraction of table row count triggering the collection of autostats. |
|
|
|
Helps tune backup performance. This variable can sync to all nodes, including aggregator and leaf nodes. This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
Determines if external functions are batched. |
|
|
|
Deprecated. |
|
|
|
Possible values are This variable can sync to all nodes, including aggregator and leaf nodes. This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
Maximum frequency (in seconds) that change count metadata will be written. |
|
|
|
The character set of the node. This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
Specifies whether to use a heuristic to more accurately estimate date predicates that are larger than the histograms range.
|
|
|
|
Sets the collation that is used on the node. This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
Sets the collation that is used on the node. This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
Sets the collation that is used on the node. This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
Sends |
|
|
|
At this threshold (fraction of |
|
|
|
Controls the rowstore-backed segment size for columnstore tables. This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
Timeout (in seconds) to queue a columnstore ingest query before returning an error. This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
Sets the threshold at which multiple inserts to a columnstore table with unique keys will switch from row value lock to table lock. |
|
|
|
Controls the maximum row count for a columnstore segment. This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
Maximum file size (in bytes) that two or more column blobs can be combined into. |
|
|
|
Verifies the checksum of a blob before merging it. |
|
|
|
Sets a limit (in megabytes) for the memory amount used for query object code images. This engine variable is configurable on SingleStoreDB Cloud. |
l |
|
|
Sets the percentage of This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
If This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
Determines whether consensus can be configured or not. |
|
|
|
Generates a core if a file IO API encounters |
|
|
|
Generates a core instead of throwing an error if a stack overflow occurs during query execution. |
|
|
|
Limits the size of some critical diagnostics sent to SingleStoreDB. |
|
|
|
Specifies the level of data conversion behavior to use. This variable can sync to all nodes, including aggregator and leaf nodes. This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
See Row Locking. |
|
|
|
Sets the default type of cardinality autostats for newly created columnstore tables. |
|
|
|
Sets the default state for autostats sampling for newly created columnstore tables. |
l |
|
|
Whether autostats is enabled by default on newly created columnstore tables. This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
Sets the default type of automatic histograms on newly created tables. |
|
|
|
Sets the default type of cardinality autostats for newly created rowstore tables. |
|
|
|
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. |
|
|
|
The time (in milliseconds) to wait for a distributed DDL transaction to commit. This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
The default number of partitions a newly created database will be created with. |
|
|
|
A global variable used for MySQL compatibility. |
|
|
|
The default table type to use when creating a new table. This engine variable is configurable on SingleStoreDB Cloud. |
When you upgrade a cluster, the setting of the variable does not change. |
|
|
Controls the default value for If it is enabled and Once it is turned on, it impacts only the newly created users. This variable can sync to all aggregators. This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
Controls pushdown logic of pushing down reference table clauses to the leaves. |
|
|
|
Prevents the removal of |
|
|
|
Whether merging subqueries are disabled if they contain This is a session variable that can also be set globally. |
|
|
|
|
|
|
|
The interval in which a query plan must be read from disk (plancache directory) before it is removed. This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
Controls how long (in seconds) an aggregator caches a distributed transaction outcome. |
|
|
|
Deprecated in MemSQL 6. |
|
|
|
As of MemSQL 6. |
|
|
|
This variable is deprecated but still exists for backward compatibility purposes. This variable can sync to all nodes, including aggregator and leaf nodes. |
|
|
|
The minimum number of tables that must be present in a join for initial heuristics to be run before join optimization. This is a session variable that can also be set globally. |
|
|
|
Enabling this variable will result in a return to pre-7. This is a session variable that can also be set globally. |
|
|
|
The maximum number of tables present in a join that will result in an unrestricted search being performed to find the optimal query plan. This is a session variable that can also be set globally. |
|
|
|
Allows the user to set the minimum amount of time to wait for Disaster Recovery (DR) replication in milliseconds (ms). |
|
|
|
Trims leading spaces for column name aliases. |
|
|
|
If set If set to Please note, a profiled query will have a different query plan than a non-profiled query. This variable can sync to all nodes, including aggregator and leaf nodes. |
|
|
|
Controls whether the background statistics thread can invalidate plans automatically. This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
If set to |
|
|
|
When this variable is set to |
|
|
|
When this variable is set to |
|
|
|
Enables queuing on columnstore ingest queries when the background flusher is behind. |
|
|
|
Determines whether to use LRU (least recently used) eviction of query object code images. This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
Enable removal of stale on-disk plans from the plancache directory based on the value of |
|
|
|
Retains human-readable query plan summaries for compiled query plans. This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
When set to |
|
|
|
See the Enabling External Functions section in CREATE [OR REPLACE] EXTERNAL FUNCTION. |
|
|
|
Enables the ability for histograms to estimate predicates using stored procedure parameters or implicit/explicit typecasted literals. |
|
|
|
Allows the on-disk IR cache to be enabled or disabled. |
|
|
|
When this variable is set to |
|
|
|
Enables the forwarding of DDL queries from a child aggregator to the master. |
|
|
|
Enables spilling onto disk for HashGroupBy, hash join, order by, and window function operations during query processing.
The sync variables This variable can sync to all nodes, including aggregator and leaf nodes. |
|
|
|
Enables tracing in backup subprocesses. |
|
|
|
Enables compressing identical strings on |
|
|
|
Uses heuristic or histogram to estimate missing sampling data instead of returning zero rows. |
|
|
|
Specifies how SingleStoreDB estimates the equality predicate for uncorrelated scalar subselects. This variable can sync to all nodes, including aggregator and leaf nodes. |
|
|
|
Number of cpu cores expected on each leaf. |
|
|
|
Specifies the maximum number of characters to be used by expressions when outputting the This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
Specifies whether the server disables certain nonstandard behaviors for default values and NULL-value handling in This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
Deprecated and not used. |
|
|
|
Port number to use for prometheus exporter http endpoint. |
|
|
|
Path to user CA file to use for prometheus exporter connection. |
|
|
|
Path to user directory with CA files to use for prometheus exporter connection. |
|
|
|
Provides the path to the ssl certificate. |
|
|
|
Used by the monitoring process when a user selects what entity to trust for the custom private key. |
|
|
|
Used when the |
|
|
|
Adds the ability to use HTTPS. |
|
|
|
See the Enabling External Functions section in CREATE [OR REPLACE] EXTERNAL FUNCTION. |
|
|
|
Assists with controlling the batch size while making external function calls. |
|
|
|
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 |
|
|
|
Controls whether the cluster fails over if disk use falls below |
|
|
|
Maximum number of tables in a join which SingleStoreDB will still cost bushy joins. |
|
|
|
The radius of the sphere is used for distance calculation, in meters. This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
This variable is the maximum length string This variable can sync to all nodes, including aggregator and leaf nodes. This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
The size of a block of characters used to logically divide up string columns when using the full-text search highlight functionality. This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
Maximum number of fragments to return from highlight function. This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
This variable controls the assumed correlation between columns when using histograms for selectivity estimation. |
|
|
|
Allows the most commonly used This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
When this variable is set to |
|
|
|
Specifies the maximum number of list values to consider during histogram estimation for a query with an This is a session variable that can also be set globally. |
|
|
|
Amount of time (in seconds) the background thread waits before trying to flush on a table with data less than |
|
|
|
Specifies the maximum number of nested common table expressions (CTEs). |
|
|
|
Controls whether the This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
The maximum number of children allowed during schema inference. |
|
|
|
Limits the number of JSON key paths inferred within a segment. |
|
|
|
The number of children after which the sparse condition is checked. |
|
|
|
The minimum average frequency to infer the schema for a json sub-object. |
|
|
|
The minimum absolute frequency to infer the schema for a json sub-object. |
|
|
|
The soft limit to how much data fits in a single internal page while compressing a json column. |
|
|
|
Controls the collation setting for the This is a session variable that can also be set globally. This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
The endpoint to get JWKS updates from. |
|
|
|
When set, specifies the value that must be present in JWT |
|
|
|
The CA certificate file to be used for SSL JWKS endpoint host verification when HTTPS protocol is used. |
|
|
|
The JWKS update interval in seconds. |
|
|
|
When set, specifies which JWT field contains username overriding other rules. |
|
|
|
Whether or not the master aggregator should detect leaf failures and trigger failovers. |
|
|
|
Specifies the placement of replica partitions in a workspace. |
|
|
|
Hides license-related variables from |
|
|
|
The amount of time, in minutes, that a This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
If this variable is set to This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
Maximum number of unparsed bytes read by This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
Number of bytes read at a time by This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
Number of bytes written at a time by This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
Restricts access to the local file system. |
|
|
|
Time (in seconds) to wait for a row lock before returning an error. This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
Determines whether to use the lockfree backup implementation that is introduced in version 7. |
|
|
|
Specifies the log file size (in bytes) for partitions. |
|
|
|
Specifies the log file size (in bytes) for reference databases. |
|
|
|
Specifies the amount of time online failover will wait to drain open transactions on a master partition (whether running or idle) before killing them. |
|
|
|
Maximum allowed protocol packet size (in bytes). This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
The number of compiles that can run asynchronously at a given time on each node, when This variable can sync to all nodes, including aggregator and leaf nodes. This engine variable is configurable on SingleStoreDB Cloud. |
Half the number of cores on the host machine. |
|
|
The maximum amount of memory (in megabytes) used to compile a query. This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
The maximum time allowed (in seconds) to compile a query. This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
If the number of interrupted connections from a host exceeds the value of |
|
|
|
The maximum number of expressions within a query item. |
Maximum unsigned |
|
|
The maximum number of expressions within an entire query. |
Maximum unsigned |
|
|
The maximum number of stashed connections per leaf. This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
Maximum number of threads to use for pre-fetching per scan. This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
The maximum number of simultaneous prepared statements. This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
Sets the maximum amount of space that can be used by the blob cache. For example:
This variable can sync to all nodes, including aggregator and leaf nodes. This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
Displays the version of SingleStoreDB initially installed. |
|
|
|
Indicates the size (in bytes) of the SingleStoreDB database log upon which a new snapshot will be kicked off. |
|
|
|
The period in minutes to keep historical monitoring data. |
|
|
|
Preferred number of tuples in multi-inserts that aggregators send to leaves. |
|
|
|
Time (in seconds) that is allowed for a multi-statement transaction to remain idle while holding locks. |
|
|
|
Controls the number of threads per leaf node for parallel columnstore scans. |
|
|
|
Variable to write default |
|
|
|
Controls the number of background merger threads to start for each node. The allowed values range between 2 and 64. This variable can sync to all nodes, including aggregator and leaf nodes. This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
A statement count threshold for a procedure or function. |
|
|
|
Controls the beam search width. |
|
|
|
Increased the default value to reduce the chance of cartesian joins being included when there are incorrect estimations. |
|
|
|
Setting this to |
|
|
|
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. |
|
|
|
Applies to a computed column when matching |
|
|
|
Allows the optimizer to rewrite leaf |
|
|
|
The minimum total number of rows estimates in joined reference tables for gathering non-reference tables to be considered. |
|
|
|
The minimum number of reference tables joined in a query that will be considered before gathering non-reference tables. |
|
|
|
The maximum number of scalar constants a single query can contain. |
|
|
|
The interval in which a query plan must be reused at least once or it gets unloaded from the query plan cache. This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
A global variable that preserves This variable can sync to all nodes, including aggregator and leaf nodes. |
|
|
|
A global variable that controls whether privilege transferability is determined by This variable can sync to all nodes, including aggregator and leaf nodes. This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
The maximum number of bytes available to the |
|
|
|
Default timeout (in milliseconds) to wait for a |
|
|
|
This variable is deprecated. Maximum number of simultaneous running queries. This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
The fraction of available cores that will be used on each leaf for a single query. This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
Serializes query shape metadata to disk. |
|
|
|
Maximum size (in bytes) for each query shape in a serialization file. |
|
|
|
Stores actual parameter values to on-disk query shape metadata serialization. |
|
|
|
If This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
If set to 1, there is no redundancy across leaves. |
|
|
|
Specifies the heap allocation limit of the regexp compiler (in megabytes). Can be manually adjusted by the user. |
|
|
|
Specifies the regular expression format to be used by regular expression functions that you call. This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
Determines whether the mode of control over the This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
The function that is executed at runtime that selects the resource pool to use when the user runs a query. This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
Sets the lifetime (in seconds) of a result table error before garbage collection. |
|
|
|
Displays the service edition: |
|
|
|
A JSON array of strings with engine variable names. |
|
|
|
If This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
Adds conditional compatibility comments to outputs like This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
Maximum number of tables present in a join which will utilize the cost-based singlebox join optimizer. This is a session variable that can also be set globally. |
|
|
|
Controls when segment elimination will not use an |
|
|
|
The aggregate size of transaction logs (in bytes) which, when reached, will trigger a new snapshot. This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
Specifies how much time (in seconds) to wait between snapshots before taking a snapshot for blob garbage collection from previous snapshots. |
|
|
|
Number of snapshot and log files to keep for backup and replication. This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
Specifies whether different query plans are generated for This variable can sync to all aggregators. This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
Specifies the maximum percentage of disk space to be used by spilling during query execution. This variable can sync to all nodes, including aggregator and leaf nodes. |
|
|
|
Memory threshold (ratio of total node memory, 0 - 1) to start spilling. |
|
|
|
Memory threshold (bytes) for a query operator to be considered for spilling. |
|
|
|
Specifies the SQL mode or modes that affect the SQL syntax SingleStoreDB Cloud supports and the query validation checks it performs. This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
If This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
The maximum number of rows returned by a This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
The number of sub-partitions per physical partition that a new database will be created with. |
|
|
|
Makes subprocess errors easier to read. |
|
|
|
This engine variable is deprecated. |
|
|
|
The maximum number of retries for |
|
|
|
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 workspace is on ec2 from which implicit credentials can be obtained. This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
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. This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
This variable is the setting for the This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
This session variable fine-tunes the behavior of the rewrite If the variable is set to This variable can sync to all nodes, including aggregator and leaf nodes. |
|
|
|
This aggregator will synchronize its permissions with other aggregators. |
|
|
|
This engine variable is deprecated. |
|
|
|
Sets the case-sensitivity for database object names. This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
Transaction isolation level. This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
Turns the Two-Phase Transaction Commit feature ON or OFF. |
|
|
|
Transaction isolation level. This engine variable has been deprecated. This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
Determines whether |
|
|
|
Used to enable seekable encoding for JSON. |
|
|
|
This variable has been deprecated and is no longer operational in SingleStoreDB Cloud version 5. |
|
|
|
For a column defined as type This variable can sync to all nodes, including aggregator and leaf nodes. |
|
|
|
Limits the application of join predicate transitivity to predicates that may be estimated. |
|
|
|
See UPDATE and DELETE for more information. |
|
|
|
Specifies the workspace name used by the backup. |
Password Complexity Sync Variables
For information about how to configure a password complexity policy, see Configuring a Password Policy.
|
Name |
Description |
Default |
|---|---|---|
|
|
The maximum number of consecutive repeated characters allowed ( This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
The maximum number of consecutive characters allowed ( This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
The minimum number of characters required ( This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
The minimum number of lowercase characters required ( This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
The minimum number of numeric digit characters required ( This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
The minimum number of special (non-alphanumeric) characters required ( This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
The minimum number of uppercase characters required ( This engine variable is configurable on SingleStoreDB Cloud. |
|
Pipelines Sync Variables
You cannot set a variable for a specific pipeline – each variable setting applies to all pipelines in the workspace.
|
Name |
Description |
Default |
|---|---|---|
|
|
Specifies whether to enable security features for HDFS pipelines. |
|
|
|
Allows custom java pipelines. |
|
|
|
The maximum amount of disk space (in megabytes) that is used to log errors for pipelines. This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
Implements an extractor interface used for experimental Java pipelines. |
|
|
|
Heap size (in megabytes) for HDFS pipelines. |
|
|
|
The number of batch metadata entries to persist before they are overwritten by incoming batches. This variable can sync to all nodes, including aggregator and leaf nodes. This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
For keyless sharded destination tables, if less than this fraction of batch partitions are active, reshuffle to avoid skew. This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
The amount of time in minutes that a pipeline error is stored on disk. This variable can sync to all nodes, including aggregator and leaf nodes. This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
Generates core dumps on pipeline extractor operations that timeout. |
|
|
|
Specifies whether to enable extractor debugging for Kafka or HDFS pipelines. This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
The maximum time (in milliseconds) to wait for offset data to be returned from the data source before returning an error. This variable can sync to all aggregators. |
|
|
|
The maximum time (in milliseconds) that a pipeline will wait for more data from an external source. |
|
|
|
Maximum memory of a pipeline extractor (in megabytes). |
|
|
|
Max number of files that will be garbage collected in one iteration. |
|
|
|
The Kafka version used for the Kafka extractor. This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
The maximum number of pipelines running concurrently. This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
The maximum number of pipeline batch partitions running concurrently. This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
Deprecated in MemSQL 6. This variable can sync to all nodes, including aggregator and leaf nodes. This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
The maximum number of data source partition offsets to extract in a single batch transaction. This variable can sync to all aggregators. This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
Max pooled extractors for Kafka pipelines. This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
The number of retry attempts for writing batch partition data to the destination table. This variable can sync to all aggregators. This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
Amount of time in minutes allowed for pipeline metadata timestamp skew. |
|
|
|
If the number of errors per batch partition exceeds the value, the pipeline will stop or skip the current batch and move to the next batch based on if the Set the variable with an integer value. This engine variable is configurable on SingleStoreDB Cloud. |
0 |
|
|
Max number of batches for a pooled extractor for Kafka pipelines. This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
The buffer size for standard error output (in bytes). This variable can sync to all nodes, including aggregator and leaf nodes. This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
Specifies whether or not each pipeline in the workspace should stop when an error occurs. This variable can sync to all aggregators. This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
If set to This variable can sync to all aggregators. This engine variable is configurable on SingleStoreDB Cloud. |
|
Wasm Sync Variables
|
Name |
Description |
Default |
|---|---|---|
|
|
Specifies if a user can create or call Wasm UDFs/TVFs. Note
|
|
|
|
Specifies the maximum memory (in bytes) that can be allocated to a Wasm module directly from the operating system in case of emergencies. This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
Specifies the maximum number of times SingleStoreDB retries memory allocation if an out-of-memory error occurs in the Wasm runtime. This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
Specifies the time (in milliseconds) before SingleStoreDB retries memory allocation if an out-of-memory error occurs in the Wasm runtime. |
|
|
|
Enables creation of Wasm functions from a remote URL. This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
Specifies the maximum size (in bytes) that a compiled Wasm module can use. This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
Specifies the maximum size (in bytes) that is available for caching Wasm query object code images. |
|
|
|
Specifies the maximum linear memory (in bytes) that an individual Wasm module can use. This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
Specifies the maximum size (in bytes) of Wasm modules that may be loaded. This engine variable is configurable on SingleStoreDB Cloud. |
|
Workload Management Sync Variables
|
Name |
Description |
Default |
|---|---|---|
|
|
Whether to train or enable the resource usage model for workload management. This variable can sync to all aggregators. This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
Specifies whether to enable workload management for the workspace. This variable can sync to all aggregators. This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
Sets the ratio of the available leaf memory for workload_ |
|
|
|
Enables/disables the dynamic reuse of WM queues feature. This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
When enabled allows workload management to classify queries as large solely based on memory usage. |
|
|
|
Enables static resource partitioning for deterministic behavior by workload management. |
|
|
|
The expected number of aggregators that will be used to run a high volume of client queries which require fully distributed execution. This variable can sync to all aggregators. This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
The maximum number of connections to use per leaf node in the workspace. This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
The maximum depth of the query queue, which is the maximum number of queries that can be queued. This variable can sync to all aggregators. This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
The maximum number of threads to use per leaf. This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
Percentage of memory a query can use before it will get queued. This variable can sync to all aggregators. This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
Whether to turn on queuing based on memory usage of queries. This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
Specifies how many large queries can be waiting for execution in the LARGE queue when we want to move to that queue a MEDIUM query from the MEDIUM queue. This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
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. This variable can sync to all aggregators. This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
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. This engine variable is configurable on SingleStoreDB Cloud. |
|
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.
Note
The Description
column will denote if an engine variable is configurable in Cloud.
Connection Management Variables
|
Name |
Description |
Default Setting |
Exceptions to When Variable can be Set |
|---|---|---|---|
|
|
The number of seconds the node’s SingleStoreDB Cloud waits for a connection packet before sending back a |
|
|
|
|
The number of simultaneous clients allowed. |
|
|
|
|
The maximum number of kernel threads for processing queries. |
|
|
|
|
Specifies the number of connections reserved for admin users (users granted the You connect via a socket file, for example: |
|
|
|
|
Controls whether to perform name resolution. |
|
You can only set this variable while the node is offline. |
Database Optimization Variables
|
Name |
Description |
Default Setting |
Exceptions to When Variable can be Set |
|---|---|---|---|
|
|
Deprecated alias to |
|
|
|
|
This variable has been deprecated and is no longer operational in SingleStoreDB Cloud. |
||
|
|
Whether to merge the newly loaded data segments (for |
|
|
|
|
Number of seconds an online failover will wait to drain open idle write transactions before issuing a kill to those transactions. |
|
|
|
|
Number of seconds to wait for more data from a connection before aborting the read. |
|
|
|
|
Number of seconds to wait for a block to be written to a connection before aborting the write. |
|
|
|
|
This variable has been deprecated and is no longer operational in SingleStoreDB Cloud. |
||
|
|
This variable has been deprecated and is no longer operational in SingleStoreDB Cloud. |
Logging Variables
|
Name |
Description |
Default Setting |
Exceptions to When Variable can be Set |
|---|---|---|---|
|
|
Turning |
|
|
|
|
Sends usage and critical error diagnostics to SingleStoreDB Cloud. |
|
|
|
|
If set to |
|
|
|
|
Log connections and queries to a given file. |
|
|
|
|
Defines how SingleStoreDB Cloud behaves when it encounters unsupported functionality. |
|
Password Complexity Non-Sync Variables
|
Name |
Description |
Default Setting |
Exceptions to When Variable can be Set |
|---|---|---|---|
|
|
Specifies whether the root password can expire. |
|
|
|
|
The time (in seconds) before a password expires. |
|
|
|
|
The number of previous passwords per user that SingleStoreDB Cloud will store and disallow from reuse. |
|
Pipelines Non-Sync Variables
You cannot set a variable for a specific pipeline – each variable setting applies to all pipelines in the workspace.
|
Name |
Description |
Default Setting |
Exceptions to When Variable can be Set |
|---|---|---|---|
|
|
Specifies the value that SingleStoreDB Cloud sets the node’s operating system environment variable |
You can only set this variable while the node is offline. |
|
|
|
Specifies the path that the HDFS extractor uses to run Java. |
Other Variables
|
Name |
Description |
Default Setting |
Exceptions to When Variable can be Set |
|---|---|---|---|
|
|
Enables support for filesystems without |
|
|
|
|
|
|
|
|
|
Specifies the local or network directory to write log files. |
|
You can only set this variable while the node is offline. |
|
|
|
|
You can only set this variable while the node is offline. |
|
|
Specifies the maximum size per log file (in bytes). |
|
You can only set this variable while the node is offline. |
|
|
Specifies the maximum time duration to write to a single log file (in seconds). |
|
You can only set this variable while the node is offline. |
|
|
If |
|
|
|
|
Specifies the maximum number of concurrent threads, per leaf, used to run a backup on each leaf. The number of threads, if set, is shared among active backups, with the first backup using as many as it needs. |
|
|
|
|
Specifies the path where the installation directory can be found. |
You can only set this variable while the node is offline. |
|
|
|
This variable exists for backwards compatibility with MySQL. |
||
|
|
When this variable is set as |
|
You can only set this variable while the node is offline. |
|
|
Specifies the type of core dump to generate if SingleStoreDB Cloud terminates abnormally. |
|
You can only set this variable while the node is offline. |
|
|
This is a configuration variable that is used to set the time zone on a host by specifying an offset from UTC time. |
||
|
|
This variable has been deprecated and is no longer operational in SingleStoreDB Cloud version 5. |
||
|
|
The local path to the keytab file created on the KDC. |
You can only set this variable while the node is offline. |
|
|
|
The SPN for SingleStoreDB Cloud that was created on the KDC. |
You can only set this variable while the node is offline. |
|
|
|
Speficies the server hostname at startup. |
||
|
|
Enables SingleStore's Data API endpoints on the built-in HTTP server when used in conjunction with the |
|
|
|
|
Controls how long a pooled connection can remain idle (or unused) before it will be closed. Please contact SingleStore Customer Support if you need to change this value. |
|
|
|
|
Controls how long a pooled connection can remain open. Please contact SingleStore Customer Support if you need to change this value. |
|
|
|
|
Specifies the maximum number of connections each pool can have open at any point in time when using the SingleStore's Data API. |
|
|
|
|
Specifies the maximum number of idle connections to retain in each connection pool when using the SingleStore's Data API. |
|
|
|
|
Specifies the port on which the WebSocket proxy should run. The Note: This variable is mutually exclusive with the |
||
|
|
Specifies the port on which WebSocket proxy should run. The Note: This variable is mutually exclusive with the |
||
|
|
Idle threads terminate after the specified number of seconds. |
|
|
|
|
Contains the value of |
You cannot set this variable while the node is offline. |
|
|
|
If set to |
|
|
|
|
If set to |
|
|
|
|
Controls whether SingleStore compiles or interprets query plans. |
|
|
|
|
Controls the maximum length (in bytes) of JSON_ |
|
|
|
|
This variable has been deprecated and is no longer operational in SingleStoreDB Cloud. |
||
|
|
Specifies the directory where error messages are stored. |
|
You can only set this variable while the node is offline. |
|
|
This variable has been deprecated and is no longer operational in SingleStoreDB Cloud version 5. |
||
|
|
Specifies the setting to materialize the common table expressions (CTEs). For more information, see WITH (Common Table Expressions). |
|
|
|
|
The ID of the SingleStoreDB Cloud node whose config you would like to update. |
||
|
|
SingleStoreDB Cloud version number. |
||
|
|
The build date of the SingleStoreDB Cloud version currently running. |
||
|
|
SingleStoreDB Cloud version hash. |
||
|
|
Sets the minimal available disk space allowed (in megabytes). |
|
|
|
|
Specifies the size (in bytes) of the connection buffer and the result buffer with which each client thread starts. |
|
|
|
|
The path name of the process ID file. |
|
You can only set this variable while the node is offline. |
|
|
This variable enables the collection of additional data with |
|
|
|
|
Specifies the version of the client/server protocol. |
|
|
|
|
The number of threads pooled for replication management of reference databases. |
|
|
|
|
The number of threads pooled for replication management of partition databases. |
|
|
|
|
Specifies the resource pool to be used. |
|
|
|
|
This variable determines verbose debug tracing for rewrites. |
|
|
|
|
Specifies a single audience restriction for the SAML assertion. . |
You can only set this variable while the node is offline. |
|
|
|
Specifies the intended recipient for a wrapped encryption key. . |
You can only set this variable while the node is offline. |
|
|
|
Specifies the file path for the private key used to decrypt an encrypted assertion. . |
You can only set this variable while the node is offline. |
|
|
|
Specifies if authentication should fail when both the SAML response and SAML assertion are unencrypted. . |
|
You can only set this variable while the node is offline. |
|
|
Specifies if authentication should fail when both the SAML response and SAML assertion are unsigned. . |
|
You can only set this variable while the node is offline. |
|
|
Specifies whether a username value should be extracted from the . |
|
You can only set this variable while the node is offline. |
|
|
Specifies the username attribute in a SAML assertion that should be used to determine if a user exists in the database. . |
You can only set this variable while the node is offline. |
|
|
|
Specifies the file path for the identity provider’s public x509 signing certificate. . |
You can only set this variable while the node is offline. |
|
|
|
Specifies the directory to which any import or export operations should be limited, or |
You can only set this variable while the node is offline. |
|
|
|
Specifies the Unix socket file to use for local connections. |
|
You can only set this variable while the node is offline. |
|
|
If the node's disk space (in MBs) is less than |
|
|
|
|
If This engine variable is configurable on SingleStoreDB Cloud. |
|
|
|
|
System time zone. |
|
|
|
|
This variable has been deprecated and is no longer operational in SingleStoreDB Cloud version 5. |
||
|
|
Specifies how many inactive connection threads should be cached. |
|
|
|
|
Determines how the server handles connection threads. |
|
You can only set this variable while the node is offline. |
|
|
Specifies the stack size (in bytes) for each thread. |
|
You can only set this variable while the node is offline. |
|
|
The current time zone. |
|
|
|
|
Used to capture the original timestamp of the client. |
|
|
|
|
Indicates the TLS version with which to configure the node. |
You can only set this variable while the node is offline. |
|
|
|
MemSQL Ops writes temporary data to |
You can only set this variable while the node is offline. |
|
|
|
The type of the server binary. |
|
|
|
|
The operating system on which SingleStoreDB Cloud was built. |
|
In-Depth Variable Definitions
This section contains supplemental information about engine variables that require more understanding to configure properly.
compiled_
These variables are used to specify the memory limit available for caching query object code images.compiled_ and compiled_ are set to 0), SingleStoreDB allocates the compiled_ value by default in respect to the maximum_ as follows:
|
|
Percentage of ( |
|---|---|
|
0-1 |
Prohibited ( |
|
1-64 |
10% of |
|
64+ |
5% of |
The module cache limit is derived at runtime from the values of the compiled_ and compiled_ variables as follows (where _compiled_ and _compiled_, respectively):
|
_ |
_ |
Effective Module Cache Limit |
|---|---|---|
|
0. |
0 |
The value is derived in accordance with the default allocation rule specified in the table above. |
|
0. |
> 0 |
If _ |
|
> 0. |
Any value |
The value of _ |
If the compiled_ variable is set to a value other than 0.compiled_ is ignored.
Note
Changes to maximum_ may result in changes in the module cache limit, in accordance to the rules specified above.
max_
max_ is the maximum number of kernel-level threads the SingleStoreDB Cloud node will use to handle connections (i.max_ setting on an aggregator is essentially a limit on the number of queries - including internal SingleStoreDB Cloud queries - the aggregator will run simultaneously.
The maximum value of max_ is 12192.
If the max_ limit is reached on an aggregator, queries are queued until a thread becomes available, which can potentially cause unresponsiveness, latency spikes, and failures.max_ limit is reached on an aggregator, increasing the limit should solve the problem.
max_
max_ is the maximum number of connections cached between nodes.max_ is reached.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.
sp_
Syntax
SET GLOBAL sp_query_dynamic_param = {ON | OFF};
Remarks
When set to ON, sp_ 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
NULLor notNULL.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
NULLor notNULL.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_ is enabled, compilation time and plancache size are reduced.
Note
The OFF setting of sp_ disables the plan generation behavior described above.
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_.TIME datatype.
If sp_ is disabled,
DELIMITER //CREATE OR REPLACE PROCEDURE sp_ex (t TIME) ASBEGINECHO SELECT t AS "t";END; //DELIMITER ;CALL sp_ex(9006060);
+-----------+
| t |
+-----------+
| 838:59:59 |
+-----------+If sp_ is enabled,
DELIMITER //CREATE OR REPLACE PROCEDURE sp_ex (t TIME) ASBEGINECHO SELECT t AS "t";END; //DELIMITER ;CALL sp_ex(9006060);
+----------+
| t |
+----------+
| 00:00:00 |
+----------+json_
json_ allows you to control the collation of JSON_ output.
-
json: Specifies that the result ofJSON_will use the collation of the JSON argument.EXACT_ STRING If the collation of the argument is not set, the default ( utf8_) is used.bin -
Since the old
AUTO/SERVERbehavior 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 ofJSON_result will be the same as collation of the server defined by theEXTRACT_ STRING collation_variable.server Generally, this value is utf8_.general_ ci -
SERVER_: Functions the same as server/auto, in previous version older than 7.V1 8. 21 but without the parser lockdown. -
SERVER_: When theV2 collation_isserver utf8and the JSON object isutf8mb4, the output will useutf8mb4.This is the default beginning with version 7. 8. 21
sql_
sql_ specifies the current SQL mode (or modes) that the current session runs in.
-
ANSI_: ChangesQUOTES "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_enabled, you cannot use double quotation marks to quote literal strings because they are interpreted as identifiers.QUOTES -
ANSI: Setssql_tomode STRICT_,ALL_ TABLES ONLY_,FULL_ GROUP_ BY PIPES_, andAS_ CONCAT ANSI_together.QUOTES -
NO_: Throws an error when using GRANT on non-existent users.AUTO_ CREATE_ USER Default behavior is to create a user instead. -
ONLY_: Throws an error when fields in theFULL_ GROUP_ BY SELECTlist,HAVINGcondition, orORDER BYlist are not in aGROUP BYclause, 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_: ChangesAS_ CONCAT ||to be treated as string concatenation operation and not as an OR operation.You can still use built-in function CONCATas a string concatenation with this mode enabled. -
STRICT_: Always enabled, regardless of other modes being enabled or not.ALL_ TABLES If set to an empty value, behavior is the same as if STRICT_is set.ALL_ TABLES
Example
The following examples show how to set sql_ 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_BYSET 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_BYSET 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 togetherSET 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)sql_
The following examples show how to set sql_ with PIPES_.
Create a table and add values:
CREATE TABLE concat_exp(col_1 TEXT, col_2 TEXT);INSERT INTO concat_exp VALUES('base', 'ball');INSERT INTO concat_exp VALUES('rail', 'road');INSERT INTO concat_exp VALUES('sun', 'flower');INSERT INTO concat_exp VALUES('moon', 'light');
Set the sql_:
SET sql_mode = 'PIPES_AS_CONCAT';
Create a function:
DELIMITER //CREATE or REPLACE FUNCTION func_1(base TEXT, ball TEXT)RETURNS TEXT AS BEGIN RETURN base || ball;END //
Set the sql_:
delimiter ;set sql_mode = '';
Call the function previously created:
SELECT func_1(col_1, col_2) pipe_1 FROM concat_exp;
+-----------+
| pipe_1 |
+-----------+
| baseball |
| sunflower |
| railroad |
| moonlight |
+-----------+Select the columns from the table:
SELECT col_1 || col_2 as pipe_2 FROM concat_exp;
+--------+
| pipe_2 |
+--------+
| 0 |
| 0 |
| 0 |
| 0 |
+--------+Last modified: November 30, 2022