List of Engine Variables
On this page
The following tables list the sync engine variables and non-sync engine variables.
When reading or setting engine variables keep in mind that the values ON and 1 are synonymous, as are the values OFF and 0.
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 SingleStore Helios version 7.
The Description
column will denote if an engine variable is configurable in SingleStore Helios.
|
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 SingleStore Helios. |
|
|
|
Deprecated in MemSQL 6. |
|
|
|
Allows the query optimizer to materialize and reuse common table expressions without recomputing them when the query contains This engine variable is configurable on SingleStore Helios. |
|
|
|
Allows modification of global variables even in the presence of computed shard keys. The behavior changing sysvars include:
This engine variable is configurable on SingleStore Helios. |
|
|
|
Determines whether to allow errors while consuming '/proc/cpuinfo' to append CPU flags to the code generation's hash. |
|
|
|
controls behavior where SingleStore will do extra work to avoid issues caused by UDFs that return different values when repeatedly invoked (e. This engine variable is configurable on SingleStore Helios. |
|
|
|
Specifies if every audit log record is synchronously written and persisted to the disk. |
|
|
|
Indicates the retention period (in days) for audit log files. |
|
|
|
Indicates the retention size (in megabytes) for audit log files. |
|
|
|
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. |
|
|
|
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 SingleStore Helios. |
|
|
|
Determines if external functions are batched. This engine variable is configurable on SingleStore Helios. |
|
|
|
Deprecated. This engine variable is configurable on SingleStore Helios. |
|
|
|
Possible values are This variable can sync to all nodes, including aggregator and leaf nodes. This engine variable is configurable on SingleStore Helios. |
|
|
|
Maximum frequency (in seconds) that change count metadata will be written. |
|
|
|
The character set of the node. For more information, see Specifying Character Set and Collation for Workspaces. This engine variable is configurable on SingleStore Helios. |
|
|
|
Specifies whether to use a heuristic to more accurately estimate date predicates that are larger than the histograms range.
This engine variable is configurable on SingleStore Helios. |
|
|
|
Sets the collation that is used on the node. For more information, see Specifying Character Set and Collation for Workspaces. This engine variable is configurable on SingleStore Helios. |
|
|
|
Sets the collation that is used on the node. For more information, see Specifying Character Set and Collation for Workspaces. This engine variable is configurable on SingleStore Helios. |
|
|
|
Sets the collation that is used on the node. For more information, see Specifying Character Set and Collation for Workspaces. This engine variable is configurable on SingleStore Helios. |
|
|
|
Sends |
|
|
|
At this threshold (fraction of |
|
|
|
Controls the rowstore-backed segment size for columnstore tables. This engine variable is configurable on SingleStore Helios. |
|
|
|
Timeout (in seconds) to queue a columnstore ingest query before returning an error. This engine variable is configurable on SingleStore Helios. |
|
|
|
Sets the threshold at which multiple inserts to a columnstore table with unique keys will switch from row value lock to table lock. This engine variable is configurable on SingleStore Helios. |
|
|
|
Controls the maximum row count for a columnstore segment. This engine variable is configurable on SingleStore Helios. |
|
|
|
Maximum file size (in bytes) that two or more column blobs can be combined into. |
|
|
|
Verifies the checksum of a blob before merging it. This engine variable is configurable on SingleStore Helios. |
|
|
|
Sets a limit (in megabytes) for the memory amount used for query object code images. This engine variable is configurable on SingleStore Helios. |
|
|
|
Sets the percentage of This engine variable is configurable on SingleStore Helios. |
|
|
|
If This engine variable is configurable on SingleStore Helios. |
|
|
|
Determines whether consensus can be configured or not. |
|
|
|
If set to This engine variable is configurable on SingleStore Helios. |
|
|
|
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. |
|
|
|
Sets the maximum number of tables used in a non-critical query optimization estimate. This engine variable is configurable on SingleStore Helios. |
|
|
|
Limits the size of some critical diagnostics sent to SingleStore. |
|
|
|
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 SingleStore Helios. |
|
|
|
See Row Locking. |
|
|
|
Sets the default type of cardinality autostats for newly created columnstore tables. This engine variable is configurable on SingleStore Helios. |
|
|
|
Sets the default state for autostats sampling for newly created columnstore tables. This engine variable is configurable on SingleStore Helios. |
l |
|
|
Whether autostats is enabled by default on newly created tables. This engine variable is configurable on SingleStore Helios. |
|
|
|
Sets the default type of automatic histograms on newly created tables. This engine variable is configurable on SingleStore Helios. |
|
|
|
Sets the default type of cardinality autostats for newly created rowstore tables. This engine variable is configurable on SingleStore Helios. |
|
|
|
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. This engine variable is configurable on SingleStore Helios. |
|
|
|
The time (in milliseconds) to wait for a distributed DDL transaction to commit. This engine variable is configurable on SingleStore Helios. |
|
|
|
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 SingleStore Helios. |
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 SingleStore Helios. |
|
|
|
Controls pushdown logic of pushing down reference table clauses to the leaves. This engine variable is configurable on SingleStore Helios. |
|
|
|
Prevents the removal of This engine variable is configurable on SingleStore Helios. |
|
|
|
Whether merging subqueries are disabled if they contain This is a session variable that can also be set globally. This engine variable is configurable on SingleStore Helios. |
|
|
|
The interval in which a query plan must be read from disk (plancache directory) before it is removed. This engine variable is configurable on SingleStore Helios. |
|
|
|
Controls how long (in seconds) an aggregator caches a distributed transaction outcome. |
|
|
|
Deprecated in MemSQL 6. |
|
|
|
As of MemSQL 6. |
|
|
|
At a high level, this variable makes the optimizer more (lower) or less (higher) likely to consider a broadcast when planning distributed joins. This is a session variable that can also be set globally. This engine variable is configurable on SingleStore Helios. |
|
|
|
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. This engine variable is configurable on SingleStore Helios. |
|
|
|
Enabling this variable will result in a return to pre-7. This is a session variable that can also be set globally. This engine variable is configurable on SingleStore Helios. |
|
|
|
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. This engine variable is configurable on SingleStore Helios. |
|
|
|
Allows the user to set the minimum amount of time to wait for Disaster Recovery (DR) replication in milliseconds (ms). This variable can sync to all nodes, including aggregator and leaf nodes. |
|
|
|
Trims leading spaces for column name aliases. This engine variable is configurable on SingleStore Helios. |
|
|
|
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 SingleStore Helios. |
|
|
|
If set to |
|
|
|
When this variable is set to This engine variable is configurable on SingleStore Helios. |
|
|
|
This global variable controls whether we collect block stats. This variable can sync to all nodes, including aggregator and leaf nodes. This engine variable is configurable on SingleStore Helios. |
|
|
|
This global variable controls whether we read and use the block stats during scan. This variable can sync to all nodes, including aggregator and leaf nodes. This engine variable is configurable on SingleStore Helios. |
|
|
|
When this variable is set to This engine variable is configurable on SingleStore Helios. |
|
|
|
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 SingleStore Helios. |
|
|
|
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 SingleStore Helios. |
|
|
|
When set to |
|
|
|
See the Enabling External Functions section in CREATE [OR REPLACE] EXTERNAL FUNCTION. |
|
|
|
Enables the automatic creation of histograms based on This engine variable is configurable on SingleStore Helios. |
|
|
|
Enables the ability for histograms to estimate predicates using stored procedure parameters or implicit/explicit typecasted literals. This engine variable is configurable on SingleStore Helios. |
|
|
|
Enables Iceberg ingest. This engine variable is configurable on SingleStore Helios. |
|
|
|
Used to reduce table memory overhead for idle tables on a cluster. To completely remove any overhead, use the non-sync variable This engine variable is configurable on SingleStore Helios. |
|
|
|
Allows the on-disk IR cache to be enabled or disabled. |
|
|
|
When this variable is set to This engine variable is configurable on SingleStore Helios. |
|
|
|
Enables query plan pinning. |
|
|
|
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. This engine variable is configurable on SingleStore Helios. |
|
|
|
Enables tracing in backup subprocesses. |
|
|
|
Allows the query optimizer to use stale statistics instead of querying for statistics during optimization. This engine variable is configurable on SingleStore Helios. |
|
|
|
Enables compressing identical strings on This engine variable is configurable on SingleStore Helios. |
|
|
|
Enables creation of writable views. |
|
|
|
Uses heuristic or histogram to estimate missing sampling data instead of returning zero rows. This engine variable is configurable on SingleStore Helios. |
|
|
|
Specifies how SingleStore estimates the equality predicate for uncorrelated scalar subselects. This variable can sync to all nodes, including aggregator and leaf nodes. This engine variable is configurable on SingleStore Helios. |
|
|
|
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 SingleStore Helios. |
|
|
|
Specifies whether the server disables certain nonstandard behaviors for default values and NULL-value handling in This engine variable is configurable on SingleStore Helios. |
|
|
|
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. |
|
|
|
Sets the maximum size (in mb) of the memory-mapped region used to communicate between the engine and collocated services. This engine variable is configurable on SingleStore Helios. |
|
|
|
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 SingleStore will still cost bushy joins. |
|
|
|
The radius of the sphere is used for distance calculation, in meters. This engine variable is configurable on SingleStore Helios. |
|
|
|
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 SingleStore Helios. |
|
|
|
Controls whether This engine variable is configurable on SingleStore Helios. |
|
|
|
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 SingleStore Helios. |
|
|
|
Maximum number of fragments to return from highlight function. This engine variable is configurable on SingleStore Helios. |
|
|
|
This variable controls the assumed correlation between columns when using histograms for selectivity estimation. This engine variable is configurable on SingleStore Helios. |
|
|
|
Allows the most commonly used This engine variable is configurable on SingleStore Helios. |
|
|
|
When this variable is set to This engine variable is configurable on SingleStore Helios. |
|
|
|
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. This engine variable is configurable on SingleStore Helios. |
|
|
|
Amount of time (in seconds) the background thread waits before trying to flush on a table with data less than |
|
|
|
Controls the approximate maximum uncompressed size of a columnstore blob in bytes. If a merge or batch write operation estimates that it would produce a segment with blobs larger than this size, the operation would lower the segment size to limit memory use for that operation. Caution It may take approximately up to 3 times the amount of memory specified in this variable to create a blob, for example during loading or columnstore merge process. Increasing this variable can allow segments with large sizes to be created, which can benefit full-text or vector search workloads, with the drawback of using more memory during merge and batch write operations. SingleStore recommends that in a memory-constrained environment, the variable should not be set to a large value. This variable can sync to all nodes, including aggregator and leaf nodes. This engine variable is configurable on SingleStore Helios. |
|
|
|
Specifies the maximum number of nested common table expressions (CTEs). |
|
|
|
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 SingleStore Helios. |
|
|
|
If this variable is set to This engine variable is configurable on SingleStore Helios. |
|
|
|
Maximum number of unparsed bytes read by This engine variable is configurable on SingleStore Helios. |
|
|
|
Number of bytes read at a time by This engine variable is configurable on SingleStore Helios. |
|
|
|
Number of bytes written at a time by This engine variable is configurable on SingleStore Helios. |
|
|
|
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 SingleStore Helios. |
|
|
|
Specifies the log file size (in bytes) for partitions. This engine variable is configurable on SingleStore Helios. |
|
|
|
Specifies the log file size (in bytes) for reference databases. This engine variable is configurable on SingleStore Helios. |
|
|
|
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 SingleStore Helios. |
|
|
|
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 SingleStore Helios. |
Half the number of cores on the host machine. |
|
|
Sets the maximum number of merger threads that the autostats updater can run on. This variable can sync to all nodes, including aggregator and leaf nodes. This engine variable is configurable on SingleStore Helios. |
|
|
|
The maximum amount of memory (in megabytes) used to compile a query. This engine variable is configurable on SingleStore Helios. |
|
|
|
The maximum time allowed (in seconds) to compile a query. This engine variable is configurable on SingleStore Helios. |
|
|
|
If the number of interrupted connections from a host exceeds the value of |
|
|
|
The maximum number of expressions within a query item. This engine variable is configurable on SingleStore Helios. |
Maximum unsigned |
|
|
The maximum number of expressions within an entire query. This engine variable is configurable on SingleStore Helios. |
Maximum unsigned |
|
|
The maximum number of stashed connections per leaf. This engine variable is configurable on SingleStore Helios. |
|
|
|
Sets the maximum number of threads to use for pre-fetching per scan. This engine variable is configurable on SingleStore Helios. |
|
|
|
Sets the maximum number of simultaneous prepared statements. This engine variable is configurable on SingleStore Helios. |
|
|
|
Sets the maximum amount of memory (in bytes) required when creating or attaching a database. This engine variable is configurable on SingleStore Helios. |
|
|
|
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 SingleStore Helios. |
|
|
|
Displays the version of SingleStore initially installed. |
|
|
|
Indicates the size (in bytes) of the SingleStore 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. This engine variable is configurable on SingleStore Helios. |
|
|
|
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 SingleStore Helios. |
|
|
|
Specifies the write timeout, in seconds, for the socket used by an This variable can sync to all aggregators. |
|
|
|
Specifies the write timeout, in seconds, for the socket used by an |
|
|
|
Enable or disable optimization for JSON computed columns. This variable can sync to all nodes, including aggregator and leaf nodes. This engine variable is configurable on SingleStore Helios. |
|
|
|
A statement count threshold for a procedure or function. This engine variable is configurable on SingleStore Helios. |
|
|
|
Controls the beam search width. This engine variable is configurable on SingleStore Helios. |
|
|
|
Increased the default value to reduce the chance of cartesian joins being included when there are incorrect estimations. This engine variable is configurable on SingleStore Helios. |
|
|
|
Setting this to |
|
|
|
Disables predicate transitivity on query rewrites is set to This engine variable is configurable on SingleStore Helios. |
|
|
|
Setting this variable to This engine variable is configurable on SingleStore Helios. |
|
|
|
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 engine variable is configurable on SingleStore Helios. |
|
|
|
Applies to a computed column when matching This engine variable is configurable on SingleStore Helios. |
|
|
|
Allows the optimizer to rewrite leaf This engine variable is configurable on SingleStore Helios. |
|
|
|
This variable specifies the maximum row count for a derived table to be pushable to each branch of another union all table in the join list. This engine variable is configurable on SingleStore Helios. |
|
|
|
The minimum total number of rows estimates in joined reference tables for gathering non-reference tables to be considered. This engine variable is configurable on SingleStore Helios. |
|
|
|
The minimum number of reference tables joined in a query that will be considered before gathering non-reference tables. This engine variable is configurable on SingleStore Helios. |
|
|
|
Enable or disable the not null filter derivation rewrite in the optimizer. This variable is settable with an Currently, This variable can sync to all nodes, including aggregator and leaf nodes. This engine variable is configurable on SingleStore Helios. |
|
|
|
Estimate row size in distributed join optimization based off sum of sizes in projection fields ( If this variable is set to This is a session variable that can also be set globally. |
|
|
|
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 SingleStore Helios. |
|
|
|
A global variable that preserves This variable can sync to all nodes, including aggregator and leaf nodes. |
|
|
|
This variable can be used to temporarily disable updating privilege caches while performing security operations such as: granting/revoking permissions to/from users, granting/revoking permissions to/from roles, adding/deleting roles to/from groups, adding/deleting users to/from groups, role deletion, group deletion. IT IS IMPORTANT that the privilege cache updates be reenabled (set back to This variable can sync to all aggregators. To use this variable:
This engine variable is configurable on SingleStore Helios. |
|
|
|
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 SingleStore Helios. |
|
|
|
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 SingleStore Helios. |
|
|
|
The fraction of available cores that will be used on each leaf for a single query. This engine variable is configurable on SingleStore Helios. |
|
|
|
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 SingleStore Helios. |
|
|
|
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. This engine variable is configurable on SingleStore Helios. |
|
|
|
Specifies the regular expression format to be used by regular expression functions that you call. This engine variable is configurable on SingleStore Helios. |
|
|
|
This variable controls the output behavior of Possible values are This variable can sync to all nodes, including aggregator and leaf nodes. This engine variable is configurable on SingleStore Helios. |
|
|
|
Determines whether the mode of control over the This engine variable is configurable on SingleStore Helios. |
|
|
|
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 SingleStore Helios. |
|
|
|
Sets the lifetime (in seconds) of a result table error before garbage collection. |
|
|
|
Specifies the method used for sampling. This variable can sync to all nodes, including aggregator and leaf nodes. Reoptimize the most recently profiled query based on previous executions of it. |
|
|
|
Enables sending usage telemetry data (about how the system is configured and used and how it is performing). To enable, set the variable to one of the following (the type of cluster):
You will also need to allow outbound traffic to |
OFF |
|
|
Displays the service edition: |
|
|
|
A JSON array of strings, where each string is the name of an engine variable. |
|
|
|
If This engine variable is configurable on SingleStore Helios. |
|
|
|
Adds conditional compatibility comments to outputs like This engine variable is configurable on SingleStore Helios. |
|
|
|
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. This engine variable is configurable on SingleStore Helios. |
|
|
|
Controls when segment elimination will not use an This engine variable is configurable on SingleStore Helios. |
|
|
|
The aggregate size of transaction logs (in bytes) which, when reached, will trigger a new snapshot. This engine variable is configurable on SingleStore Helios. |
|
|
|
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 SingleStore Helios. |
|
|
|
Specifies whether different query plans are generated for This variable can sync to all aggregators. This engine variable is configurable on SingleStore Helios. |
|
|
|
Specifies the maximum percentage of disk space to be used by spilling during query execution. Under the default setting, spilling will first use available free disk space. The maximum amount of blob cache space that spilling can negotiate to use is 30% of This variable can sync to all nodes, including aggregator and leaf nodes. This engine variable is configurable on SingleStore Helios. |
|
|
|
Memory threshold (ratio of total node memory, 0 - 1) to start spilling. This engine variable is configurable on SingleStore Helios. |
|
|
|
Memory threshold (bytes) for a query operator to be considered for spilling. This engine variable is configurable on SingleStore Helios. |
|
|
|
Specifies the SQL mode or modes that affect the SQL syntax SingleStore Helios supports and the query validation checks it performs. This engine variable is configurable on SingleStore Helios. |
|
|
|
If This engine variable is configurable on SingleStore Helios. |
|
|
|
The maximum number of rows returned by a This engine variable is configurable on SingleStore Helios. |
|
|
|
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 SingleStore Helios. |
|
|
|
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 SingleStore Helios. |
|
|
|
This variable is the setting for the This engine variable is configurable on SingleStore Helios. |
|
|
|
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 engine variable is configurable on SingleStore Helios. |
|
|
|
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 SingleStore Helios. |
|
|
|
Controls how far the system allows a child aggregator to lag behind replicating the transaction log of it's master aggregator before throttling is applied to the master aggregator. This variable can sync to all nodes, including aggregator and leaf nodes. |
|
|
|
The maximum size, in bytes, of the trace events ring buffer,
This variable can sync to all nodes, including aggregator and leaf nodes. This engine variable is configurable on SingleStore Helios. |
16 MB (value must be provided in bytes) Setting this value to Leaving this value set to |
|
|
Indicates the retention period (in days) for trace log files. |
|
|
|
Controls the retention, by total size in megabytes, of the rotated files of each component log separately. For example, the repl log component of the engine will write traces to the memsql_ |
|
|
|
Transaction isolation level. This engine variable is configurable on SingleStore Helios. |
|
|
|
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 SingleStore Helios. |
|
|
|
Determines whether |
|
|
|
Used to enable seekable encoding for JSON. This engine variable is configurable on SingleStore Helios. |
|
|
|
Controls what will be displayed via the This variable can sync to all nodes, including aggregator and leaf nodes. This engine variable is configurable on SingleStore Helios. |
|
|
|
This variable has been deprecated and is no longer operational in SingleStore Helios version 5. |
|
|
|
For a column defined as type This variable can sync to all nodes, including aggregator and leaf nodes. This engine variable is configurable on SingleStore Helios. |
|
|
|
Enables logging to heartbeat thread in order to better diagnose process stalls. This variable can sync to all nodes, including aggregator and leaf nodes. |
|
|
|
Controls the format in which VECTOR-type columns and expressions are projected. This variable can sync to all nodes, including aggregator and leaf nodes. This engine variable is configurable on SingleStore Helios. |
|
|
|
Limits the application of join predicate transitivity to predicates that may be estimated. This engine variable is configurable on SingleStore Helios. |
|
|
|
Controls how much time (in milliseconds) a transaction waits on a rowlock before giving up when the decision is "die". This variable can sync to all nodes, including aggregator and leaf nodes. |
|
|
|
Specifies the workspace name used by the backup. |
Full-Text Search Sync Variables
|
Name |
Description |
Default |
|---|---|---|
|
|
Collocated service control endpoint for full-text search version 2. This variable can sync to all nodes, including aggregator and leaf nodes. |
|
|
|
Collocated service request endpoint for full-text search version 2. This variable can sync to all nodes, including aggregator and leaf nodes. |
|
|
|
The amount of heap memory with which to start the Java process for the full-text search version 2 collocated service. This engine variable is configurable on SingleStore Helios. This variable can sync to all nodes, including aggregator and leaf nodes. |
|
|
|
The maximum number of parallel requests that the full-text search version 2 collocated service can accept before queuing them. The default value is This engine variable is configurable on SingleStore Helios. This variable can sync to all nodes, including aggregator and leaf nodes. |
|
|
|
Sets the maximum size of the memory-mapped region used to communicate with the FTS service (in MB). This engine variable is configurable on SingleStore Helios. This variable can sync to all nodes, including aggregator and leaf nodes. |
|
|
|
The maximum amount of heap memory that the Java process for the full-text search version 2 collocated service may use. This engine variable is configurable on SingleStore Helios. This variable can sync to all nodes, including aggregator and leaf nodes. |
|
|
|
This is the number of seconds between monitor polls (0 disables it). This engine variable is configurable on SingleStore Helios. This variable can sync to all nodes, including aggregator and leaf nodes. |
|
|
|
Sets the positionIncrementGap for use when indexing multi-valued fields. This engine variable is configurable on SingleStore Helios. This variable can sync to all nodes, including aggregator and leaf nodes. |
|
|
|
Sets the maximum number of seconds that a query will wait for a response from the full-text search version 2 service before returning a timeout error. This engine variable is configurable on SingleStore Helios. This variable can sync to all nodes, including aggregator and leaf nodes. |
|
|
|
Sets the service's socket timeout (in milliseconds). This engine variable is configurable on SingleStore Helios. This variable can sync to all nodes, including aggregator and leaf nodes. |
|
|
|
When the full-text search version 2 collocated service is shut down in a controlled manner, this sets the number of seconds to wait before it must be forcibly killed. This engine variable is configurable on SingleStore Helios. This variable can sync to all nodes, including aggregator and leaf nodes. |
|
JSON Sync Variables
|
Name |
Description |
Default |
|---|---|---|
|
|
Enables JSON statistics support if set to This engine variable is configurable on SingleStore Helios. |
|
|
|
Controls the collation of JSON columns. Refer to Using JSON for information. The This variable can sync to all nodes, including aggregator and leaf nodes. This engine variable is configurable on SingleStore Helios. |
When upgrading from SingleStoreversions 8. If |
|
|
Controls whether the This engine variable is configurable on SingleStore Helios. |
|
|
|
The maximum number of children allowed during schema inference. This engine variable is configurable on SingleStore Helios. |
|
|
|
Limits the number of JSON key paths inferred within a segment. This engine variable is configurable on SingleStore Helios. |
|
|
|
The minimum average frequency to infer the schema for a json sub-object. This engine variable is configurable on SingleStore Helios. |
|
|
|
The number of children after which the sparse condition is checked. This engine variable is configurable on SingleStore Helios. |
|
|
|
The minimum absolute frequency to infer the schema for a json sub-object. This engine variable is configurable on SingleStore Helios. |
|
|
|
The soft limit to how much data fits in a single internal page while compressing a json column. This engine variable is configurable on SingleStore Helios. |
|
|
|
Controls the collation setting for the This is a session variable that can also be set globally. This engine variable is configurable on SingleStore Helios. |
|
|
|
Used for parametrizing the JSON key path arguments for This variable can sync to all nodes, including aggregator and leaf nodes. For more details refer This engine variable is configurable on SingleStore Helios. |
|
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 SingleStore Helios. |
|
|
|
The maximum number of consecutive characters allowed ( This engine variable is configurable on SingleStore Helios. |
|
|
|
The minimum number of characters required ( This engine variable is configurable on SingleStore Helios. |
|
|
|
The minimum number of lowercase characters required ( This engine variable is configurable on SingleStore Helios. |
|
|
|
The minimum number of numeric digit characters required ( This engine variable is configurable on SingleStore Helios. |
|
|
|
The minimum number of special (non-alphanumeric) characters required ( This engine variable is configurable on SingleStore Helios. |
|
|
|
The minimum number of uppercase characters required ( This engine variable is configurable on SingleStore Helios. |
|
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 SingleStore Helios. |
|
|
|
Implements an extractor interface used for experimental Java pipelines. |
|
|
|
Heap size (in megabytes) for HDFS pipelines. |
|
|
|
The amount of time in milliseconds that a pipeline waits before checking for new data from its data source. This variable can sync to all nodes, including aggregator and leaf nodes. This engine variable is configurable on SingleStore Helios. |
|
|
|
The number of batch metadata entries to persist per partition, per pipeline 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 SingleStore Helios. |
|
|
|
Specifies a forced delay in row emission while migrating/replicating your tables (or collections) to your SingleStore Helios databases. Refer to Replicate MongoDB® Collections to SingleStore for more information. This variable can sync to all nodes, including aggregator and leaf nodes. This engine variable is configurable on SingleStore Helios. |
|
|
|
Specifies the JVM heap size limit (in MBs) for CDC-in pipelines. Refer to Replicate MongoDB® Collections to SingleStore for more information. This variable can sync to all nodes, including aggregator and leaf nodes. This engine variable is configurable on SingleStore Helios. |
|
|
|
Specifies the maximum number of CDC-in extractor instances that can run concurrently. |
|
|
|
Specifies the minimum duration (in seconds) that the extractor allocates to a single pipeline for ingesting data and listening to CDC events. |
|
|
|
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 SingleStore Helios. |
|
|
|
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 SingleStore Helios. |
|
|
|
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 SingleStore Helios. |
|
|
|
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. |
|
|
|
Heap size (in megabytes) for Iceberg pipelines. This engine variable is configurable on SingleStore Helios. |
|
|
|
The Kafka version used for the Kafka extractor. This variable is not applicable to current (>= 0. For legacy Kafka, This engine variable is configurable on SingleStore Helios. |
|
|
|
The maximum number of pipelines running concurrently. This engine variable is configurable on SingleStore Helios. |
|
|
|
The maximum number of pipeline batch partitions running concurrently. This variable can sync to all nodes, including aggregator and leaf nodes. This engine variable is configurable on SingleStore Helios. |
|
|
|
Deprecated in MemSQL 6. This variable can sync to all nodes, including aggregator and leaf nodes. This engine variable is configurable on SingleStore Helios. |
|
|
|
The maximum number of data source partition offsets to extract in a single batch transaction. This variable applies only to Kafka pipelines. It can be overridden for a single pipeline by using the This variable can sync to all aggregators. This engine variable is configurable on SingleStore Helios. |
|
|
|
Max pooled extractors for Kafka pipelines. This engine variable is configurable on SingleStore Helios. |
|
|
|
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 SingleStore Helios. |
|
|
|
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 SingleStore Helios. |
0 |
|
|
Max number of batches for a pooled extractor for Kafka pipelines. This engine variable is configurable on SingleStore Helios. |
|
|
|
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 SingleStore Helios. |
|
|
|
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 SingleStore Helios. |
|
|
|
If set to This variable can sync to all aggregators. This engine variable is configurable on SingleStore Helios. |
|
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 SingleStore Helios. |
|
|
|
Specifies the maximum number of times SingleStore retries memory allocation if an out-of-memory error occurs in the Wasm runtime. This engine variable is configurable on SingleStore Helios. |
|
|
|
Specifies the time (in milliseconds) before SingleStore 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 SingleStore Helios. |
|
|
|
Specifies the maximum size (in bytes) that a compiled Wasm module can use. This engine variable is configurable on SingleStore Helios. |
|
|
|
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 SingleStore Helios. |
|
|
|
Specifies the maximum size (in bytes) of Wasm modules that may be loaded. This engine variable is configurable on SingleStore Helios. |
|
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 SingleStore Helios. |
|
|
|
Specifies whether to enable workload management for the workspace. This variable can sync to all aggregators. This engine variable is configurable on SingleStore Helios. |
|
|
|
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 SingleStore Helios. |
|
|
|
When enabled (set to When set to This variable can sync to all aggregators. This engine variable is configurable on SingleStore Helios. |
|
|
|
Enables static resource partitioning for deterministic behavior by workload management. This engine variable is configurable on SingleStore Helios. |
|
|
|
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 SingleStore Helios. |
|
|
|
The maximum number of connections to use per leaf node in the workspace. This engine variable is configurable on SingleStore Helios. |
|
|
|
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 SingleStore Helios. |
|
|
|
The maximum number of threads to use per leaf. This engine variable is configurable on SingleStore Helios. |
|
|
|
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 SingleStore Helios. |
|
|
|
Whether to turn on queuing based on memory usage of queries. This engine variable is configurable on SingleStore Helios. |
|
|
|
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 SingleStore Helios. |
|
|
|
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 SingleStore Helios. |
|
|
|
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 SingleStore Helios. |
|
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 |
Exceptions to When Variable can be Set |
|---|---|---|---|
|
|
The number of seconds the node’s SingleStore Helios 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: |
|
|
|
|
This variable exists only for backward compatibility with MySQL. |
||
|
|
Sets the percentage of |
|
|
|
|
Controls whether to perform name resolution. |
|
You can only set this variable while the node is offline. |
Database Optimization Variables
|
Name |
Description |
Default |
Exceptions to When Variable can be Set |
|---|---|---|---|
|
|
Deprecated alias to |
|
|
|
|
This variable has been deprecated and is no longer operational in SingleStore Helios. |
||
|
|
|
|
|
|
|
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 SingleStore Helios. |
||
|
|
This variable has been deprecated and is no longer operational in SingleStore Helios. |
Full-Text Search Non-Sync Variables
|
Name |
Description |
Default |
Exceptions to When Variable can be Set |
|---|---|---|---|
|
|
If set, this provides the JAVA_ |
None |
|
|
|
If set, this provides the path for the Java executable used by full-text search version 2. |
None |
Logging Variables
|
Name |
Description |
Default |
Exceptions to When Variable can be Set |
|---|---|---|---|
|
|
Turning |
|
|
|
|
Sends usage and critical error diagnostics to SingleStore Helios. |
|
|
|
|
If set to |
|
|
|
|
Log connections and queries to a given file. |
|
|
|
|
Indicates the size threshold (in bytes) for tracelog file rotation. |
65536> |
|
|
|
Indicates the time threshold (in seconds) for tracelog file rotation. |
60> |
|
|
|
Defines how SingleStore Helios behaves when it encounters unsupported functionality. |
|
Password Complexity Non-Sync Variables
|
Name |
Description |
Default |
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 SingleStore Helios 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 SingleStore Helios 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. |
Query Plan Pinning Variables
|
Name |
Description |
Default |
Exceptions to When Variable can be Set |
|---|---|---|---|
|
|
Writes a query plan pins when an un-planned pin is first optimized. |
|
|
|
|
This is used to enforce a maximum latency penalty that can be incurred when the server looks for a plan pin before proceeding with compiling a new plan. |
|
|
|
|
This is used to enforce a maximum latency penalty that can be incurred when the server writes a plan pin during compilation. |
|
|
|
|
Loads query plan pins if one is found. |
|
Other Variables
|
Name |
Description |
Default |
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 Note: 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 SingleStore Helios terminates abnormally. |
|
You can only set this variable while the node is offline. |
|
|
Port number to use for Dedicated Admin Connections (DAC). |
|
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. |
||
|
|
If set then allows Dedicated Admin Connections (DAC) to be established at the port specified in |
|
You can only set this variable while the node is offline. |
|
|
Enables the handling of extended data types that are not defined in MySQL |
|
|
|
|
This variable has been deprecated and is no longer operational in SingleStore Helios 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 SingleStore Helios 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 SingleStore Helios. |
||
|
|
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 SingleStore Helios version 5. |
||
|
|
Specifies the setting to materialize the common table expressions (CTEs). For more information, see WITH (Common Table Expressions). |
|
|
|
|
The ID of the SingleStore Helios node whose config you would like to update. |
||
|
|
SingleStore Helios version number. |
||
|
|
The build date of the SingleStore Helios version currently running. |
||
|
|
SingleStore Helios 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. |
|
|
|
|
Number of threads running for rowstore garbage collection. |
|
|
|
|
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 SingleStore Helios. |
|
|
|
|
System time zone. |
|
|
|
|
This variable has been deprecated and is no longer operational in SingleStore Helios 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. The value must be between 131072 and 134217728, and also evenly divisible by 1024. |
|
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 SingleStore Helios was built. |
|
In-Depth Variable Definitions
This section contains supplemental information about engine variables that require more understanding to configure properly.
max_
max_ is the maximum number of kernel-level threads the SingleStore Helios node will use to handle connections (i.max_ setting on an aggregator is essentially a limit on the number of queries - including internal SingleStore Helios 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.
max_
max_ is the maximum number of connections that can be open to a SingleStore Helios node at one time.max_ as lowering its value would not impact resource allocation.
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 data type.
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_ and JSON_ output.
-
json: Specifies that the result will use the collation of the JSON argument.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 toSERVER_immediately after upgrading.V2 See the support bulletin: Using the JSON utf8mb4 Column. . . -
server/auto: Specifies that the collation result will be the same as collation of the server defined by thecollation_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_: This feature is deprecated, It is now enabled by default and generates an error when privileges are granted to a non-existent user.AUTO_ CREATE_ USER -
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: August 30, 2024