# List of Engine Variables The following tables list the [sync engine variables](https://docs.singlestore.com/db/v9.1/reference/configuration-reference/engine-variables/list-of-engine-variables/#sync-variables-lists-2.md) and [non-sync engine variables](https://docs.singlestore.com/db/v9.1/reference/configuration-reference/engine-variables/list-of-engine-variables/#non-sync-variables-list-2.md). Some variables have an [in-depth description](https://docs.singlestore.com/db/v9.1/reference/configuration-reference/engine-variables/list-of-engine-variables/#in-depth-variable-definitions-2.md) that supplements the description found in the table. 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](https://docs.singlestore.com/db/v9.1/reference/configuration-reference/engine-variables.md) to learn how sync variables work. > **📝 Note**: All of the sync variables listed in the following table are global variables, except when noted as a “session variable that can also be set globally” in the “Description” column. You can set sync variables on the master aggregator, only. After you set a sync variable, the nodes affected by your update (all aggregators, all leaves, or both) depend on the variable’s type and the command you use to set the variable. Each variable’s type is indicated in the last sentence in the “Description” column in the following table. | Name | Description | Default | | --------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | --------------------------------------------------------------------------------------------------------------------------------------- | | `active_query_users_blacklist` | Shows a JSON array of users whose query runs are not considered active to measure the cluster's idle time. This variable can sync to all nodes, including aggregator and leaf nodes. | | | `aes_default_encryption_mode` | You can use this variable to control the supported key size. It takes a value in aes-keylen-mode format, where keylen is the key length in bits and mode is the encryption mode. The value is not case-sensitive. Permitted keylen values are 128 and 256. Permitted mode values are ECB, GCM, and CBC.List of allowed values are:`aes-128-ecb`,`aes-256-ecb`,`aes-128-gcm`,`aes-256-gcm`,`aes-128-cbc`,`aes-256-cbc`Optionally an encryption mode string in the same format can be provided to the[AES\_ENCRYPT](https://docs.singlestore.com/db/v9.1/reference/sql-reference/string-functions/aes-encrypt.md)and[AES\_DECRYPT](https://docs.singlestore.com/db/v9.1/reference/sql-reference/string-functions/aes-decrypt.md)functions. If not provided, the default value from this variable will be used. In addition to the key, the initialization vector can be provided to the encryption/decryption function in GCM and CBC encryption modes.This variable can sync to all nodes, including aggregator and leaf nodes. | `aes-128-ecb` | | `aggregator_failure_detection` | Deprecated in MemSQL 6.0, this is the timeout used when gathering workload management statistics from leaves. This variable can sync to all aggregators. | `ON` | | `allow_materialize_cte_with_union` | Allows the query optimizer to materialize and reuse common table expressions without recomputing them when the query contains`UNION`,`UNION ALL`, and other`SET`operations. It is a session variable that is only available in versions 8.1.20 and newer. | `FALSE` | | `allow_modifying_sysvars_with_computed_shard_key` | Allows modification of global variables even in the presence of computed shard keys. Allowing changes to certain sysvars in the presence of computed shard key, only when this variable is set`ON`applies to all behavior changing sysvars.The behavior changing sysvars include: | `OFF` | | `allow_proc_cpuinfo_errors` | Determines whether to allow errors while consuming '/proc/cpuinfo' to append CPU flags to the code generation's hash. This variable can sync to all nodes, including aggregator and leaf nodes. | `FALSE` | | `assume_udfs_deterministic` | Controls behavior whereSingleStorewill do extra work to avoid issues caused by UDFs that return different values when repeatedly invoked (e.g., are non-deterministic). This variable defaults to false, preserving the existing behavior (sinceSingleStoreversion 8.0), whereSingleStorewill ensure these computations take place once on an aggregator node. When set to true,SingleStorewill avoid this extra layer of coordination (and bottleneck) allowing leaf nodes to independently coordinate. This variable can sync to all nodes, including aggregator and leaf nodes. | `FALSE` | | `auditlog_disk_sync` | Specifies if every audit log record is synchronously written and persisted to the disk. By default, it delays the audit log writes to the disk.For more information on this variable, see[Configure Audit Logging](https://docs.singlestore.com/db/v9.1/security/audit-logging/configuring-audit-logging.md).This variable can sync to all nodes, including aggregator and leaf nodes. | `OFF` | | `auditlog_retention_period` | Indicates the retention period (in days) for audit log files. This variable can sync to all nodes, including aggregator and leaf nodes. | `0`(store files indefinitely) | | `auditlog_retention_size` | Indicates the retention size (in megabytes) for audit log files. | `0` | | `auto_attach` | Specifies if the aggregator will try to attach a node automatically after it has been marked offline, but starts responding to heartbeats again. This variable can sync to all aggregators. | `ON` | | `auto_profile_type` | Determines the mode of auto profiling.There are three values:`SMART`,`LITE`, and`FULL`.Auto profiling must be enabled (see the`enable_auto_profile`engine variable below) before setting this variable.This is a session variable that can also be set globally.This variable can sync to all nodes, including aggregator and leaf nodes. | `SMART` | | `autostats_flush_interval_secs` | Number of seconds a table's incremental autostats can go unused before being flushed to disk.`0`means they will never be flushed. This variable can sync to all nodes, including aggregator and leaf nodes. | `600` | | `attach_rebalance_delay_seconds` | Number of seconds to wait after a new node has attached to theclusterbefore running a rebalance. This mechanism is used to batch up rebalancing if many nodes fail and then come back online within a short period of time. This variable can sync to all aggregators. | `120` | | `background_statistics_collection_interval` | How often (in seconds) background statistics will check for out-of-date statistics (rowstore only). This variable can sync to all aggregators. | `60` | | `background_statistics_collection_threshold` | Threshold as a fraction of table row count triggering the collection of autostats. This variable can sync to all aggregators. | `0.5` | | `backup_multipart_upload_concurrency` | Helps tune backup performance. It controls the number of subprocesses concurrently uploading the parts of a multipart upload done by a backup. A backup of a leaf uses one thread per partition and there is no restriction on the number of threads created (aside from system limitations).This variable's config depends on the`backup_max_threads`config.When`backup_max_threads`is zero implying all the backups run at full parallelism then this variable can be set in the range 1 to15inclusive.When`backup_max_threads`is not equal to zero implying a throttle is applied to the number of concurrent backups per leaf, then this variable will be taken as 1, irrespective of the value set.With an increased number of go routines the backup performance improves but it comes with an increased cost of CPU, memory, and network throughput.This variable can sync to all nodes, including aggregator and leaf nodes. | `3` | | `batch_external_functions` | Determines if external functions are batched. The engine variable accepts`ALWAYS`,`AUTO`, and`NEVER`values.See[CREATE EXTERNAL FUNCTION](https://docs.singlestore.com/db/v9.1/reference/sql-reference/procedural-sql-reference/create-or-replace-external-function.md)for more information.This variable can sync to all nodes, including aggregator and leaf nodes. | `ALWAYS` | | `batch_external_functions_size` | Deprecated. Use`external_functions_batch_size`instead. | `512` | | `cardinality_estimation_level` | Possible values are`'6.0'`,`'6.5'`,`'7.0'`,`'7.1'`, and`'7.3'`. The behavior of each setting follows:`'6.0'`: Use legacy histograms;`'6.5'`: Use advanced histograms;`'7.0'`: Same use of histograms as 6.5;`'7.1'`: Use histograms for join estimation;`'7.3'`: Use sampling and histograms together (when both are available), to improve selectivity estimation.This variable can sync to all nodes, including aggregator and leaf nodes. | `'7.3'` | | `cardinality_threshold_for_join_split_rewrite` | Specifies the cardinality threshold for the[Join Memory Reduction Optimization](https://docs.singlestore.com/db/v9.1/query-data/query-tuning/join-memory-reduction-optimization.md). If any join column has a cardinality above this threshold, the`JOIN`query will be split into joins. The default value (LLONG\_MAX) indicates that the optimization is disabled.This variable can sync to all nodes, including aggregator and leaf nodes.This is a session variable that can also be set globally. | `9223372036854775807`(LLONG\_MAX) | | `change_count_write_interval` | Maximum frequency (in seconds) that change count metadata will be written. Will never occur more frequently than`background_statistics_collection_interval`. This variable can sync to all aggregators. | `1200` | | `character_set_server` | The character set of the node. This is a session variable that can also be set globally.This variable can sync to all nodes, including aggregator and leaf nodes.For more information, see[Specifying Character Set and Collation for Clusters](https://docs.singlestore.com/db/v9.1/reference/sql-reference/character-encoding/specifying-character-set-and-collation-for-clusters.md). | `utf8mb4` | | `clamp_histogram_date_estimates` | Specifies whether to use a heuristic to more accurately estimate date predicates that are larger than the histograms range. Available values are`AUTO`,`OFF`, and`ON`. This variable can sync to all nodes, including aggregator and leaf nodes.`AUTO`is`ON`. | `AUTO` | | `collation_connection` | Sets the collation that is used on the node. When you set this variable,`collation_database`and`collation_server`are automatically set to the same value. Although the collation variables are session variables, you must set the variable globally to avoid undesired behavior. An error is generated if any of these variables is set in a session to a value that does not match the value set globally. Change the value globally first, then change it in the session to match. This variable can sync to all nodes, including aggregator and leaf nodes.For more information, see[Specifying Character Set and Collation for Clusters](https://docs.singlestore.com/db/v9.1/reference/sql-reference/character-encoding/specifying-character-set-and-collation-for-clusters.md). | `utf8mb4_bin` | | `collation_database` | Sets the collation that is used on the node. When you set this variable,`collation_connection`and`collation_server`are automatically set to the same value. Although the collation variables are session variables, you must set the variable globally to avoid undesired behavior. An error is generated if any of these variables is set in a session to a value that does not match the value set globally. Change the value globally first, then change it in the session to match. This variable can sync to all nodes, including aggregator and leaf nodes.For more information, see[Specifying Character Set and Collation for Clusters](https://docs.singlestore.com/db/v9.1/reference/sql-reference/character-encoding/specifying-character-set-and-collation-for-clusters.md). | `utf8mb4_bin` | | `collation_server` | Sets the collation that is used on the node. When you set this variable,`collation_connection`and`collation_database`are automatically set to the same value. Although the collation variables are session variables, you must set the variable globally to avoid undesired behavior. An error is generated if any of these variables is set in a session to a value that does not match the value set globally. Change the value globally first, then change it in the session to match. This variable can sync to all nodes, including aggregator and leaf nodes.For more information, see[Specifying Character Set and Collation for Clusters](https://docs.singlestore.com/db/v9.1/reference/sql-reference/character-encoding/specifying-character-set-and-collation-for-clusters.md). | `utf8mb4_bin` | | `collect_average_size` | Sends`AVG(LENGTH())`queries to collect average serialization data for string columns. This variable can sync to all aggregators. | `FALSE` | | `columnstore_disk_insert_threshold` | At this threshold (fraction of`columnstore_flush_bytes`),`INSERT`,`LOAD DATA`, and`UPDATE`queries to the columnstore will write straight to disk. For more information, see[Advanced Columnstore Configuration Options](https://docs.singlestore.com/db/v9.1/create-a-database/configuring-the-columnstore-to-work-effectively.md). This variable can sync to all nodes, including aggregator and leaf nodes. | `0.5` | | `columnstore_flush_bytes` | Controls the rowstore-backed segment size for columnstore tables. For more information, see[Advanced Columnstore Configuration Options](https://docs.singlestore.com/db/v9.1/create-a-database/configuring-the-columnstore-to-work-effectively.md). This variable can sync to all nodes, including aggregator and leaf nodes. | `33554432` | | `columnstore_ingest_management_queue_timeout` | Timeout (in seconds) to queue a columnstore ingest query before returning an error. This variable can sync to all nodes, including aggregator and leaf nodes. | `3600` | | `columnstore_row_value_table_lock_threshold` | Sets the threshold at which multiple inserts to a columnstore table with unique keys will switch from row value lock to table lock. The value's metric is the number of rows for each partition in a database. This variable can sync to all nodes, including aggregator and leaf nodes. | `1000000`(rows per partition) | | `columnstore_segment_rows` | Controls the maximum row count for a columnstore segment. For more information, see[Advanced Columnstore Configuration Options](https://docs.singlestore.com/db/v9.1/create-a-database/configuring-the-columnstore-to-work-effectively.md). This variable can sync to all nodes, including aggregator and leaf nodes. Maximum value is 10000000. | `1024000` | | `columnstore_small_blob_combination_threshold` | Maximum file size (in bytes) that two or more column blobs can be combined into. This variable can sync to all nodes, including aggregator and leaf nodes. | `5242880` | | `columnstore_validate_blob_before_merge` | Verifies the checksum of a blob before merging it. For more information, see[CHECK BLOB CHECKSUM](https://docs.singlestore.com/db/v9.1/reference/sql-reference/operational-commands/check-blob-checksum.md)and[Columnstore](https://docs.singlestore.com/db/v9.1/create-a-database/columnstore.md). This variable can sync to all nodes, including aggregator and leaf nodes. | `OFF` | | `compiled_images_eviction_memory_limit_mb` | Sets a limit (in megabytes) for the memory amount used for query object code images.Refer to[Managing Plancache Memory and Disk Usage](https://docs.singlestore.com/db/v9.1/user-and-cluster-administration/maintain-your-cluster/managing-memory/managing-plancache-memory-and-disk-usage.md)for more information.This variable can sync to all nodes, including aggregator and leaf nodes. | `0` | | `compiled_images_eviction_memory_limit_percent` | Sets the percentage of`maximum_memory`that is available for caching query object code images. Refer to[Managing Plancache Memory and Disk Usage](https://docs.singlestore.com/db/v9.1/user-and-cluster-administration/maintain-your-cluster/managing-memory/managing-plancache-memory-and-disk-usage.md)for more information. This variable can sync to all nodes, including aggregator and leaf nodes. | `0.0` | | `compile_only` | If`ON`,SingleStorewill compile, but not run, each query it receives. This is a session variable that can also be set globally. This variable can sync to all nodes, including aggregator and leaf nodes. | `OFF` | | `consensus_enabled` | Determines whether consensus can be configured or not. This variable can sync to all nodes, including aggregator and leaf nodes. | `FALSE` | | `consider_secondary_projection` | If set to`OFF`, turns off the use of projections in queries except when the projection query hint (`use_projection='projection_name'`) is used. Refer to the[CREATE PROJECTION](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-definition-language-ddl/create-projection.md)page for more details. This variable can sync to all nodes, including aggregator and leaf nodes. | `ON` | | `core_on_bad_errnos_in_file_io_apis` | Generates a core if a file IO API encounters`EFAULT`,`EBADF`, or`EINVAL`. This variable can sync to all nodes, including aggregator and leaf nodes. | `0` | | `core_on_stack_overflow` | Generates a core instead of throwing an error if a stack overflow occurs during query execution. This variable can sync to all nodes, including aggregator and leaf nodes. | `0` | | `costing_max_djo_tables` | Sets the maximum number of tables used in a non-critical query optimization estimate. This variable can sync to all nodes, including aggregator and leaf nodes. | `15` | | `critical_diagnostics_max_payload_size` | Limits the size of some critical diagnostics sent toSingleStore. This variable can sync to all nodes, including aggregator and leaf nodes. | `65536` | | `cross_query_stats_collection_strategy` | Controls which runtime rowcount statistics are collected into the cross query stats cache. Values are: | `Auto`**Note**:`Auto`currently behaves the same as`Off`(disabled). | | `cross_query_stats_expiration_minutes` | Specifies the time-to-live, in minutes, for cross query stats cache entries; an entry must be read or written at least once within this interval, otherwise it is expired and evicted. | `720`(12 hours) | | `cross_query_stats_max_mb` | Approximate upper bound on memory usage (in megabytes) for the cross query stats cache. Once the cache reaches this size, new entries will no longer be inserted until space is freed by eviction. | `1024` | | `cross_query_stats_max_wait_ms` | Controls how long a session, in`milliseconds`, waits for a sampling cache entry to be populated before issuing its own sampling query.When multiple sessions need sampling for the same query shape and no cached stats exist, one session runs the sampling query and populates the cross query stats cache. Other sessions wait briefly up to this value, and if no stats appear in time, they run their own sampling query.This setting helps bound latency for queries that depend on sampling, even when the initial sampling query is slow or fails to update the cache. | `100` | | `cross_query_stats_sampling_valid_time_min` | Maximum duration, in minutes, since the last update during which sampling entries are considered valid for reuse in the cross query stats cache. | `5` | | `data_conversion_compatibility_level` | Specifies the level of data conversion behavior to use. Higher levels throw error for integer under/overflow, illegal conversion of strings to integers, and string truncation issues. Values can be either:`'8.1'`,`'8.0'`,`'7.5'`,`'7.0'`,`'6.5'`, or`'6.0'`. This variable can sync to all nodes, including aggregator and leaf nodes. It’s recommended to set this to the highest available level for new application development. For more information, see[Data Type Conversion](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-types/data-type-conversion.md).This variable can sync to all nodes, including aggregator and leaf nodes. | `'8.0'` | | `deadlock_avoidance_strategy` | See[Row Locking](https://docs.singlestore.com/db/v9.1/query-data/advanced-query-topics/row-locking.md). This variable can sync to all nodes, including aggregator and leaf nodes. | `wait_die` | | `default_autostats_columnstore_cardinality_mode` | Sets the default type of cardinality autostats for newly created columnstore tables. Values can be`OFF`,`INCREMENTAL`, or`PERIODIC`. This variable can sync to all aggregators. | `INCREMENTAL` | | `default_autostats_columnstore_sampling` | Sets the default state for autostats sampling for newly created columnstore tables. This variable can sync to all aggregators. | l`ON` | | `default_autostats_enabled` | Whether autostats is enabled by default on newly created tables. This variable can sync to all aggregators. | `ON` | | `default_autostats_histogram_mode` | Sets the default type of automatic histograms on newly created tables. Values can be either`OFF`,`CREATE`, or`UPDATE`. This variable can sync to all aggregators. | `CREATE` | | `default_autostats_rowstore_cardinality_mode` | Sets the default type of cardinality autostats for newly created rowstore tables. Values can be`OFF`,`INCREMENTAL`or`PERIODIC`. This variable can sync to all aggregators. | `PERIODIC` | | `default_columnstore_table_lock_threshold` | Sets a threshold for the number of rows that are locked before a table lock is acquired when updating or deleting rows in a columnstore table. When set to 0, the threshold of 5000 rows is used. It is a session variable that can be set globally. This variable can sync to all nodes, including aggregator and leaf nodes. | `0` | | `default_distributed_ddl_timeout` | The maximum time (in milliseconds) that a distributed DDL operation waits for in‑progress DML queries on the affected table to finish before starting. If this timeout is reached, the DDL operation times out and is rolled back.This value sets the timeout for both`ALTER TABLE`and`BACKUP`commands.This variable can sync to all nodes, including aggregator and leaf nodes. | `180000` | | `default_partitions_per_leaf` | The default number of partitions a newly created database will be created with. When you set this variable, its value is propagated to other aggregators only. | `8` | | `default_storage_engine` | A global variable used for MySQL compatibility. This variable can sync to all nodes, including aggregator and leaf nodes. | | | `default_table_type` | The default table type to use when creating a new table. The value can be set to`columnstore`or`rowstore`. When the value is`columnstore`you can[create a columnstore table using the standard CREATE TABLE syntax](https://docs.singlestore.com/db/v9.1/create-a-database/creating-a-columnstore-table.md). This variable can sync to all aggregators. | `columnstore`for newly created clusters.When you upgrade a cluster, the setting of the variable does not change. | | `default_user_require_ssl` | Controls the default value for`REQUIRE SSL`in the`CREATE USER`DDL statement.If it is enabled and`REQUIRE`is not specified in the`CREATE USER`statement, SSL will be required by default.Once it is turned on, it impacts only the newly created users. It will not enforce SSL for existing users.This variable can sync to all aggregators. | `OFF` | | `disable_reference_table_leftjoin_where_pushdown` | Controls pushdown logic of pushing down reference table clauses to the leaves. When set to`OFF`, the logic is enabled. When set to`ON`, the logic is disabled. This is a session variable that can also be set globally. This variable can sync to all nodes, including aggregator and leaf nodes. | `ON` | | `disable_remove_redundant_gby_rewrite` | Prevents the removal of`GROUP BY`columns when used in an`ORDER BY`clause. The default of AUTO is equal to`OFF`. To disable this behavior the variable must be set to`ON`. This variable can sync to all nodes, including aggregator and leaf nodes. | `AUTO` | | `disable_subquery_merge_with_straight_joins` | Whether merging subqueries are disabled if they contain`STRAIGHT_JOIN`. Here, merging subqueries refers to removing unnecessary nested layers from joins including subqueries. For example, the query`select * from (select * from table_a straight_join table_b) table_c straight_join table_d`would be rewritten as`select * from table_a straight_join table_b straight_join table_d`if the engine variable`disable_subquery_merge_with_straight_joins`is set to`OFF`. If the variable is set to`ON`, the query would not be rewritten. The default setting`AUTO`is equivalent to`ON`in 7.3.This is a session variable that can also be set globally. This variable can sync to all nodes, including aggregator and leaf nodes. | `AUTO` | | `disconnect_client_on_invalid_connection_state` | If enabled, client connections are closed when their state becomes invalid. For example, when reprovisioning a reference database causes an aggregator to drop temporary tables, the client connections that created these tables are closed. This variable can sync to all aggregators. | `TRUE` | | `disk_plan_expiration_minutes` | The interval in which a query plan must be read from disk (plancache directory) before it is removed. This variable can sync to all nodes, including aggregator and leaf nodes. | `20160`(14 days) | | `disk_plan_gc_pause_seconds_on_startup` | Disables disk plan garbage collection on startup. Files in the on-disk plancache will not be removed until`disk_plan_gc_pause_seconds_on_startup`seconds after startup.This variable can sync to all nodes, including aggregator and leaf nodes. | `10*60` | | `disk_plan_gc_pause_minutes_for_detached_tables` | Specifies the number of seconds to cache detached table modules before eviction from disk.This variable can sync to all nodes, including aggregator and leaf nodes. | `12*60 minutes` | | `dist_txn_outcomes_keepalive_secs` | Controls how long (in seconds) an aggregator caches a distributed transaction outcome. This variable can sync to all nodes, including aggregator and leaf nodes. | `1800` | | `distributed_commit_lock_timeout` | Deprecated in MemSQL 6.5. Replaced by`default_distributed_ddl_timeout`. | | | `distributed_heartbeat_timeout` | As of MemSQL 6.0, this is the timeout (in seconds) used when gathering workload management statistics from leaves. Before MemSQL 6.0 this was the timeout of the heartbeat query used to trigger failovers. This variable can sync to all aggregators. | `10` | | `distributed_optimizer_broadcast_mult` | This variable makes the optimizer more likely to choose a broadcast for distributed joins when the value is low, and less likely to choose a broadcast when the value is high. When`distributed_optimizer_broadcast_mult`is 0, the optimizer uses the partition estimate returned by`OptimizerEstimatePartitions`to determine the effective broadcast cost multiplier. In most cases, this estimate corresponds to the shard partition count for the relevant database. However, if scan partition parallelism and flexible parallelism are enabled, the estimate is based approximately on the number of leaves multiplied by the computed query parallelism per leaf, so it may differ from the database partition count.A non-zero value overrides this estimate. Setting the value to a very large number, such as 100000, can strongly discourage broadcast operations.This is a session variable that can also be set globally.This variable can sync to all nodes, including aggregator and leaf nodes. | `0` | | `distributed_optimizer_max_join_size` | This variable is deprecated but still exists for backward compatibility purposes. As of version 7.3 ofSingleStore, it is replaced by two new variables that assist in leveraging significantly increased speed for optimization at large join sizes:`distributed_optimizer_unrestricted_search_threshold`and`singlebox_optimizer_cost_based_threshold`. The distributed optimizer works to minimize data movement across thecluster, while the singlebox optimizer makes decisions about how to physically execute a join such as choosing an index. Setting this depreciated variable will result in both new variables being set to the target value.This variable can sync to all nodes, including aggregator and leaf nodes. | `22` | | `distributed_optimizer_min_join_size_run_initial_heuristics` | The minimum number of tables that must be present in a join for initial heuristics to be run before join optimization. This means a small cost is incurred for the initial use of these heuristics in excThis is a session variable that can also be set globally. This variable can sync to all nodes, including aggregator and leaf nodes.hange for faster join optimization.This is a session variable that can also be set globally. This variable can sync to all nodes, including aggregator and leaf nodes. | `16` | | `distributed_optimizer_run_legacy_heuristic` | Enabling this variable will result in a return to pre-7.3 behavior through the use of heuristics to complete a faster, restricted search for a join plan, once the number of tables in a given join is greater than the threshold set for this with`distributed_optimizer_unrestricted_search_threshold`. This search is faster, but as the number of tables in a given join increases, the chance of a less optimal query plan also increases.This is a session variable that can also be set globally. This variable can sync to all nodes, including aggregator and leaf nodes. | `FALSE` | | `distributed_optimizer_unrestricted_search_threshold` | The maximum number of tables present in a join that will result in an unrestricted search being performed to find the optimal query plan. A query with a greater number of tables will use heuristics to complete a search for a query plan. This restricted search is faster than previous versions ofSingleStore. However, this may be at the cost of a less optimal query plan compared to an unrestricted search.`distributed_optimizer_run_legacy_heuristic`must be set to`FALSE`for this variable to take effect.This is a session variable that can also be set globally. This variable can sync to all nodes, including aggregator and leaf nodes. | `22` | | `distributed_plancache_max_download_plans` | Specifies  the maximum number of plans downloaded in an aggregator synchronization task for the distributed plancache.This variable can sync to all nodes, including the aggregator and leaf nodes. | `1000` | | `dr_min_connection_timeout_ms` | Allows the user to set the minimum amount of time to wait for Disaster Recovery (DR) replication in milliseconds (ms). MS range is between 8400 and 2000000.This variable can sync to all nodes, including aggregator and leaf nodes. | `8400` | | `early_snapshot_timeout_seconds` | Specifies the time (in seconds) to wait after an`ALTER`or`TRUNCATE`command before taking an early snapshot.This variable can sync to all nodes, including aggregator and leaf nodes. | 60 | | `enable_alias_space_trim` | Trims leading spaces for column name aliases. This engine variable is only used for backward compatibility. This variable can sync to all nodes, including aggregator and leaf nodes. | `FALSE` | | `enable_auto_profile` | If set`ON`, eligible queries are profiled. Execution and network times are not collected on the`FULL`auto-profiling mode. Execution times, network times, memory usage, and network traffic are not collected on the`LITE`auto-profiling mode. These modes are set by the`auto_profile_type`engine variable. If a profiling error occurs, the query is still executed.If set to`OFF`, profile results are not collected automatically. The[PROFILE](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-manipulation-language-dml/profile.md)command must be used to run to review usage metrics. For both`ON`and`OFF`settings, the only profile statistics that are saved are the most recent query plan's execution.Please note, a profiled query will have a different query plan than a non-profiled query. When a query is first profiledor`enable_auto_profile`is set to`ON`compilations of the query are triggered to generate new query plans.This variable can sync to all nodes, including aggregator and leaf nodes. | `OFF` | | `enable_automatic_feedback_reoptimization` | Enables automatic feedback reoptimization.This is a session variable that can also be set globally.This variable can sync to all nodes, including aggregator and leaf nodes. | `ON` | | `enable_background_plan_invalidation` | Controls whether the background statistics thread can invalidate plans automatically. This can improve plans if your data changes over time at the cost of spending time recompiling plans. This variable can sync to all aggregators. | `OFF` | | `enable_background_statistics_collection` | If set to`ON`, background statistics are collected for all tables, unless disabled on the table level. If set to`OFF`, background statistics are not collected for any tables, and background statistics cannot be enabled for individual tables. This variable can sync to all aggregators. | `ON` | | `enable_binary_protocol` | When this variable is set to`ON`, server-side prepared statements are enabled. This is a session variable that can also be set globally. This variable can sync to all nodes, including aggregator and leaf nodes. | `ON` | | `enable_block_level_stats_collection` | This global variable controls whether we collect block stats. After it is turned on, block stats will be collected for sort key columns for all subsequently written columns.This variable can sync to all nodes, including aggregator and leaf nodes. | `true` | | `enable_block_stats_use_in_query` | This global variable controls whether we read and use the block stats during scan. This does not affect the storage format. After it is turned on, block stats will be used by all subsequent scan queries. For segments written without block stats, the behavior will be the same as 8.7.This variable can sync to all nodes, including aggregator and leaf nodes. | `true` | | `enable_broadcast_left_join` | When this variable is set to`TRUE`, the query optimizer can choose the broadcast left join optimization. This is a session variable that can also be set globally. This variable can sync to all nodes, including aggregator and leaf nodes. | `TRUE` | | `enable_columnstore_ingest_management` | Enables queuing on columnstore ingest queries when the background flusher is behind. This variable can sync to all nodes, including aggregator and leaf nodes. | `TRUE` | | `enable_compiled_images_eviction` | Determines whether to use LRU (least recently used) eviction of query object code images. This variable can sync to all nodes, including aggregator and leaf nodes. | `ON` | | `enable_cross_query_stats_for_sampling` | Controls whether the optimizer uses cached sampling results from the cross query stats cache when it would otherwise issue a sampling query. Values can be`OFF`,`ON`, or`AUTO`(currently equivalent to`OFF`). This is a session variable that can also be set globally. | `AUTO`**Note**:`AUTO`currently behaves the same as`OFF`(disabled). | | `enable_cross_query_stats_store_query_text` | Controls whether the cross query stats cache stores the query text associated with sampling entries. When enabled, the`QUERY_TEXT`column in the cache tables contains the text of sampling queries that created each entry.This is disabled by default to reduce memory usage. | `FALSE` | | `enable_disk_plan_expiration` | Enable removal of stale on-disk plans from the plancache directory based on the value of`disk_plan_expiration_minutes`. This variable can sync to all nodes, including aggregator and leaf nodes. | `TRUE` | | `enable_disk_plan_explain` | Retains human-readable query plan summaries for compiled query plans. Retained information is available through the SHOW PLAN command. This variable can sync to all aggregators. | `OFF` | | `enable_distributed_plancache` | Specifies whether the distributed plancache is enabled.This variable can sync to all nodes, including the aggregator and leaf nodes. | `FALSE` | | `enable_dml_query_forwarding` | When set to`ON`, forwards the Data Manipulation Language (DML) queries from a child aggregator to the master aggregator. Currently, this setting is used for write requests to reference tables, which cannot be executed on child aggregators.This variable can sync to all aggregators. | `ON` | | `enable_external_functions` | See the Enabling External Functions section in[CREATE EXTERNAL FUNCTION](https://docs.singlestore.com/db/v9.1/reference/sql-reference/procedural-sql-reference/create-or-replace-external-function/#section-idm4605862999480032701863844886.md). This variable can sync to all nodes, including aggregator and leaf nodes. | `OFF` | | `enable_dpc_lookup` | Enables asynchronous[DPC](https://docs.singlestore.com/db/v9.1/user-and-cluster-administration/maintain-your-cluster/managing-memory/distributed-plancache.md)lookups.This is a session variable that can also be set globally.This variable can sync to all aggregators. | `AUTO` | | `enable_histogram_auto_creation_for_joins` | Enables the automatic creation of histograms based on`JOIN`clauses. This variable can sync to all nodes, including aggregator and leaf nodes. | `TRUE` | | `enable_histogram_to_unwrap_literals` | Enables the ability for histograms to estimate predicates using stored procedure parameters or implicit/explicit typecasted literals. This variable needs to be manually enabled by setting it to`ON`. This variable can sync to all nodes, including aggregator and leaf nodes. | `OFF` | | `enable_iceberg_ingest` | Enables Iceberg ingest. Refer to[Iceberg Ingest](https://docs.singlestore.com/db/v9.1/load-data/data-sources/iceberg-ingest.md)for more information. This variable can sync to all nodes, including aggregator and leaf nodes. | `OFF` | | `enable_idle_table_eviction` | Used to reduce table memory overhead for idle tables on a cluster. The feature is enabled by default on all new and existing clusters. This variable can be set during a session (though a very small amount of overhead will remain until the server is restarted) and can be set to`Full`,`SkipListsOnly`, and`Off`. It defaults to`SkipListsOnly`, which meansSingleStorewill only evict skiplist indexes for idle tables on the cluster.`Full`means it will evict skiplists and table modules, and`Off`means no eviction. This variable can sync to all nodes, including aggregator and leaf nodes.To completely remove any overhead, use the non-sync variable`enable_idle_table_optimizations`which needs to be set at runtime and requires a restart for changes to take effect. It can be set to`OFF`or`ON`(default). | `Full` | | `enable_ir_cache` | Allows the on-disk IR cache to be enabled or disabled. This variable can sync to all nodes, including aggregator and leaf nodes. | `ON` | | `enable_multipartition_queries` | When this variable is set to`ON`, queries are run per leaf instead of per partition on the leaf; the number of threads decreases and performance increases. This is a session variable that can also be set globally. This variable can sync to all nodes, including aggregator and leaf nodes. | `ON` | | `enable_overloaded_functions` | Enables creating overloaded functions (UDFs) and stored procedures. Refer to[Overloaded Functions and Stored Procedures](https://docs.singlestore.com/db/v9.1/reference/sql-reference/procedural-sql-reference/overloaded-functions-and-stored-procedures.md)for more information.This variable can sync to all nodes, including aggregator and leaf nodes. | `OFF` | | `enable_plan_pinning` | Enables[query plan pinning](https://docs.singlestore.com/db/v9.1/query-data/query-plan-pinning.md). The available values are`OFF`and`ON`. When set to`OFF`, no plan pinning actions will take place. This variable can sync to all nodes, including aggregator and leaf nodes. | `OFF` | | `enable_query_forwarding` | Enables the forwarding of DDL queries from a child aggregator to the master.`sync_permissions`must also be enabled for this variable to take effect; otherwise, an error is returned for DDL queries that are run on a child aggregator. For further information, see[Cluster Management Commands](https://docs.singlestore.com/db/v9.1/reference/sql-reference/cluster-management-commands.md)This variable can sync to all aggregators. | `ON` | | `enable_rowstore_flush_in_shallow_copy` | Controls whether the rowstore segment of a table is copied during a shallow copy.This variable can sync to all nodes, including aggregator and leaf nodes.This variable can sync to all nodes, including aggregator and leaf nodes. | `TRUE` | | `enable_spilling` | Enables spilling onto disk for HashGroupBy, hash join, order by, and window functionoperations during query processing. When`enable_spilling`is set to`ON`, the output of`SHOW PROFILE JSON`includes the following metrics:`spill_outputted_rows`: number of rows spilled to disk`spill_disk_usage`: number of bytes spilled to disk.The sync variables`spilling_node_memory_threshold_ratio`and`spilling_query_operator_memory_threshold`also define the threshold limit to start spilling onto disk.This variable can sync to all nodes, including aggregator and leaf nodes. | `ON` | | `enable_subprocess_tracing` | Enables tracing in backup subprocesses. This variable can sync to all nodes, including aggregator and leaf nodes. | `OFF` | | `enable_synchronous_dpc_lookup` | Specifies if nodes look for a plan in the DPC when a plan is not found in the node's local PPC.This variable can sync to all aggregators. | `FALSE` | | `enable_use_of_stale_incremental_stats` | Allows the query optimizer to use stale statistics instead of querying for statistics during optimization. This variable can sync to all aggregators. | `FALSE` | | `enable_varbuffer_dictionary_compression` | Enables compressing identical strings on`VARCHAR`,`VARBINARY`,`LONGTEXT`,`LONGBLOB`,`MEDIUMBLOB`,`BLOB`,`TINYBLOB`,`MEDIUMTEXT`,`TEXT`, and`TINYTEXT`columns. This variable can sync to all nodes, including aggregator and leaf nodes. | `FALSE` | | `enable_writable_views` | Enables creation of writable views. Refer to[CREATE VIEW](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-definition-language-ddl/create-view.md)for more information.This variable can sync to all nodes, including aggregator and leaf nodes. | `0` | | `error_on_compilation_failure` | Controls whether the engine returns an error when[code generation](https://docs.singlestore.com/db/v9.1/query-data/advanced-query-topics/code-generation.md)fails for a query.If`error_on_compilation_failure`is set to`FALSE`, when code generation fails, the engine executes the bytecode instead of returning an error.This variable can sync to all nodes, including aggregator and leaf nodes. | `TRUE` | | `estimate_zero_rows_when_sampling_data_is_missing` | Uses heuristic or histogram to estimate missing sampling data instead of returning zero rows. False enables new estimating behavior. This variable can sync to all nodes, including aggregator and leaf nodes. | `FALSE` | | `exclude_scalar_subselects_from_filters` | Specifies howSingleStoreestimates the equality predicate for uncorrelated scalar subselects. When set to`TRUE`, the engine estimates scalar subselects as non-selective. When set to`FALSE`, the engine estimates the equality predicate for uncorrelated scalar subselects as 1/cardinality, and uses heuristics for all other scalar subselect predicates.This variable can sync to all nodes, including aggregator and leaf nodes. | `FALSE` | | `expected_leaf_core_count` | Number of cpu cores expected on each leaf. This variable can sync to all nodes, including aggregator and leaf nodes. | `8` | | `experimental_features_config` | Enables advanced configurations forSingleStorefeatures that might change in the future.Accepts key-value pairs in the format`'key1=val1,key2=val2,...'`for feature-specific settings and experimental feature enablement.This variable can sync to all nodes, including aggregator and leaf nodes. | | | `explain_expression_limit` | Specifies the maximum number of characters to be used by expressions when outputting the`EXPLAIN`for a query. This variable can sync to all nodes, including aggregator and leaf nodes. | `500` | | `explicit_defaults_for_timestamp` | Specifies whether the server disables certain nonstandard behaviors for default values and NULL-value handling in`TIMESTAMP`columns. See[Timestamp Behavior](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-types/time-and-date.md)for details on this variable. This variable can sync to all nodes, including aggregator and leaf nodes. | `ON` | | `exporter_port` | Port number to use for prometheus exporter http endpoint. This variable can sync to all aggregators. | `0` | | `exporter_ssl_ca` | Path to user CA file to use for prometheus exporter connection. This variable can sync to all aggregators. | | | `exporter_ssl_capath` | Path to user directory with CA files to use for prometheus exporter connection. This variable can sync to all aggregators. | | | `exporter_ssl_cert` | Provides the path to the ssl certificate. This variable can sync to all aggregators. | | | `exporter_ssl_key` | Used by the monitoring process when a user selects what entity to trust for the custom private key. This variable can sync to all aggregators. | | | `exporter_ssl_key_passphrase` | Used when the`exporter_ssl_key`is encrypted so the monitoring process can decrypt it. This variable can sync to all aggregators. | | | `exporter_use_https` | Adds the ability to use HTTPS. The default is`FALSE`, set to`TRUE`to enable. This variable is used in conjunction with engine variables`exporter_ssl_cert`and`exporter_ssl_key`. This variable can sync to all aggregators. | `FALSE` | | `external_functions_allowlist` | See the Enabling External Functions section in[CREATE EXTERNAL FUNCTION](https://docs.singlestore.com/db/v9.1/reference/sql-reference/procedural-sql-reference/create-or-replace-external-function/#section-idm4605862999480032701863844886.md). This variable can sync to all nodes, including aggregator and leaf nodes. | | | `external_functions_batch_size` | Assists with controlling the batch size while making external function calls. This variable can sync to all nodes, including aggregator and leaf nodes. | `512`(rows) | | `external_functions_service_buffer_mb` | Sets the maximum size (in mb) of the memory-mapped region used to communicate between the engine and collocated services. This variable can sync to all nodes, including aggregator and leaf nodes. | `1024`(mb) | | `failover_initial_grace_interval_seconds` | If a node moves offline a second time after just failing and then coming back online failover won’t be triggered again if it happens within`grace_interval_seconds`. This is to avoid a cycle of a problem node failing and coming back online. This variable can sync to all aggregators. | `300` | | `failover_on_low_disk` | Controls whether the cluster fails over if disk use falls below`minimal_disk_space`. This variable can sync to all nodes, including aggregator and leaf nodes. | `TRUE` | | `force_bushy_join_table_limit` | Maximum number of tables in a join whichSingleStorewill still cost bushy joins. Above this threshold, all possible bushy joins will be chosen regardless of cost. This variable can sync to all nodes, including aggregator and leaf nodes. | `18` | | `geo_sphere_radius` | The radius of the sphere is used for distance calculation, in meters. (Defaults to average Earth radius.) This variable can sync to all nodes, including aggregator and leaf nodes. | `6367444.657120` | | `group_concat_max_len` | This variable is the maximum length string`GROUP_CONCAT()`can return (in bytes). It is a session variable that can be set globally and can be set to any value smaller than or equal to`max_allowed_packet`. For more information on this variable see`max_allowed_packet`in the previous section and the[GROUP\_CONCAT](https://docs.singlestore.com/db/v9.1/reference/sql-reference/aggregate-functions/group-concat.md)SQL reference. This variable should be updated on all nodes and requires a restart to pick up the new value.This variable can sync to all nodes, including aggregator and leaf nodes. | `16777216` | | `highlight_allow_ascii_binary_strings` | Controls whether`HIGHLIGHT`allows ascii binary strings as input argument. This variable controls the behavior of a`HIGHLIGHT (expression) AGAINST (query_expression, [max_number_fragments])`expression if it is allowed to process ascii binary strings or not. Possible values are`ON`and`OFF`. This variable can sync to all nodes, including aggregator and leaf nodes. | `OFF` | | `highlight_fragment_size` | The size of a block of characters used to logically divide up string columns when using the full-text search highlight functionality. The minimum value is 10 and the maximum value is 65535. This variable can sync to all nodes, including aggregator and leaf nodes. | `100` | | `highlight_max_number_fragments` | Maximum number of fragments to return from highlight function. This variable can sync to all nodes, including aggregator and leaf nodes. | `0` | | `histogram_column_correlation` | This variable controls the assumed correlation between columns when using histograms for selectivity estimation. This variable can sync to all nodes, including aggregator and leaf nodes. | `0.5` | | `info_schema_show_null_dbs` | Allows non-privileged users to view the rows in`*_BOTTOMLESS_*`information schema views where associated database name is`NULL`.This variable can sync to all nodes, including aggregator and leaf nodes. | `FALSE` | | `ignore_foreign_keys` | Allows the most commonly used`FOREIGN KEY`syntax to be accepted, but not enforced. This variable can sync to all nodes, including aggregator and leaf nodes. See[Specifying Unenforced Unique Constraints](https://docs.singlestore.com/db/v9.1/create-a-database/specifying-unenforced-unique-constraints.md)for more information. | `OFF` | | `ignore_insert_into_computed_column` | When this variable is set to`ON`, an`INSERT`into a computed column succeeds and the`INSERT`ignores the computed column. When this variable is set to`OFF`, an`INSERT`into a computed column fails with an error. This is a session variable that can also be set globally. This variable can sync to all nodes, including aggregator and leaf nodes. | `OFF` | | `inlist_precision_limit` | Specifies the maximum number of list values to consider during histogram estimation for a query with an`IN`list; a smaller limit will result in a faster compilation time but may also give a less accurate estimate.**NOTE**: If you adjust this variable, existing queries will need to be recompiled.This is a session variable that can also be set globally. This variable can sync to all nodes, including aggregator and leaf nodes. | `10000` | | `internal_columnstore_idle_flush_wait_seconds` | Amount of time (in seconds) the background thread waits before trying to flush on a table with data less than`columnstore_flush_bytes`. Must be a value between 30 and 86400, and evenly divisible by 5. This variable can sync to all nodes, including aggregator and leaf nodes. | `125` | | `internal_columnstore_max_uncompressed_blob_size` | Controls the approximate maximum uncompressed size of a columnstore blob in bytes. The maximum value is 10737418240.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.

⚠️ Warning:

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.SingleStorerecommends 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. | `536870912` | | `internal_max_cte_depth` | Specifies the maximum number of nested common table expressions (CTEs). For more information, refer to the[WITH](https://docs.singlestore.com/db/v9.1/query-data/advanced-query-topics/with-common-table-expressions.md)topic. This is a session variable that can also be set globally. This variable can sync to all nodes, including aggregator and leaf nodes. | `128` | | `jwks_endpoint` | The endpoint to get JWKS updates from. If empty, JWKS update from the endpoint does not happen. This variable can sync to all nodes, including aggregator and leaf nodes. | | | `jwks_require_audience` | When set, specifies the value that must be present in JWT`aud`field. If JWT`aud`field is an array, then the value must be equal to one of array’s items. This variable can sync to all nodes, including aggregator and leaf nodes. | | | `jwks_ssl_ca_certificate` | The CA certificate file to be used for SSL JWKS endpoint host verification when HTTPS protocol is used. This variable can sync to all nodes, including aggregator and leaf nodes. | | | `jwks_update_interval` | The JWKS update interval in seconds. If set to 0, JWKS refresh does not happen. This variable can sync to all nodes, including aggregator and leaf nodes. | `3600` | | `jwks_username_field` | When set, specifies which JWT field contains username overriding other rules. This variable can sync to all nodes, including aggregator and leaf nodes. | | | `leaf_failure_detection` | Whether or not the master aggregator should detect leaf failures and trigger failovers.**WARNING**: Turning this variable`OFF`disables failover. This variable can sync to all aggregators. | `ON` | | `leaf_failover_fanout` | Specifies the placement of replica partitions in acluster. It can be set to the following modes:`paired`and`load_balanced`.For more information, see[Enabling High Availability](https://docs.singlestore.com/db/v9.1/user-and-cluster-administration/high-availability-and-disaster-recovery/managing-high-availability/enabling-high-availability.md).This variable can sync to all nodes, including aggregator and leaf nodes. | `paired` | | `license_visibility` | Hides license-related variables from`SHOW`commands. This variable can sync to all nodes, including aggregator and leaf nodes. | `TRUE` | | `load_data_errors_retention_minutes` | The amount of time, in minutes, that a`LOAD DATA`error is stored on disk. This variable must be set to at least`0`. These errors are surfaced in[information\_schema.LOAD\_DATA\_ERRORS](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-manipulation-language-dml/load-data.md)as long as they are stored on disk. This variable can sync to all nodes, including aggregator and leaf nodes. | `1440` | | `load_data_internal_compression` | If this variable is set to`ON`and a network bottleneck is detected,`LOAD DATA`compresses data on the aggregator before forwarding it to the leaves. This variable can sync to all nodes, including aggregator and leaf nodes. | `ON` | | `load_data_max_buffer_size` | Maximum number of unparsed bytes read by`LOAD DATA`before throwing an error. This variable can sync to all nodes, including aggregator and leaf nodes. | `1073741823` | | `load_data_read_size` | Number of bytes read at a time by`LOAD DATA`. This variable can sync to all nodes, including aggregator and leaf nodes. | `8192` | | `load_data_write_size` | Number of bytes written at a time by`LOAD DATA`. This variable can sync to all nodes, including aggregator and leaf nodes. | `8192` | | `local_file_system_access_restricted` | Restricts access to the local file system. This variable can sync to all nodes, including aggregator and leaf nodes. | `FALSE` | | `lock_wait_timeout` | Time (in seconds) to wait for a row lock before returning an error. This is a session variable that can also be set globally. This variable can sync to all nodes, including aggregator and leaf nodes. | `30` | | `log_file_size_partitions` | Specifies the log file size (in bytes) for partitions. Can be set to a minimum of 1 MB (in bytes) to a maximum of 1 TB (in bytes), in multiples of 1 MB. A MB contains 1048576 bytes. Each partition is pre-allocated to two log files. Each of these log files has the size`log_file_size_partitions`. By default,`log_file_size_partitions`is 256 MB. Changes in the log file size will not affect the sizes of the partitions that are used by existing databases. Use caution when adjusting this variable as it can result in large log files and thus consume more disk space. This variable can sync to all aggregators. | `268435456` | | `log_file_size_ref_dbs` | Specifies the log file size (in bytes) for reference databases. Can be set to a minimum of 1 MB (in bytes) to maximum of 1 TB (in bytes), in multiples of 1 MB. A MB contains 1048576 bytes. Each reference database is pre-allocated two log files. Each of these log files has the size`log_file_size_ref_dbs`. By default,`log_file_size_ref_dbs`is 64 MB. Changes in the log file size will not affect existing databases. Use caution when adjusting this variable as it can result in large log files and thus consume more disk space. This variable can sync to all aggregators. | `67108864` | | `managed_functions_credentials_reload_period_ms` | Specifies the grace period, in milliseconds, before expiration when the system proactively refreshes AWS IRSA (IAM Roles for Service Accounts) credentials.This setting ensures that managed external functions, including Python UDFs, AI functions, and ML functions, do not encounter authentication failures during long-running invocations.When the remaining time before credential expiration falls below this threshold, the system refreshes the credentials and re-signs requests for each batch or retry.This variable can sync to all nodes, including aggregator and leaf nodes. | `60000` | | `master_promote_kill_timeout_seconds` | Specifies the amount of time online failover will wait to drain open transactions on a master partition (whether running or idle) before killing them. This variable can sync to all nodes, including aggregator and leaf nodes. | `120` | | `max_allowed_packet` | Maximum allowed protocol packet size (in bytes). This is a session variable that is readable at the session level, but which must be set globally. The maximum value is`1`GB and the lowest allowable value is32KiB, though setting this to a value that low is not recommended. This variable can sync to all nodes, including aggregator and leaf nodes. | `104857600` | | `max_async_compilation_concurrency` | The number of compiles that can run asynchronously at a given time on each node, when`interpreter_mode`is set to`interpret_first`. Its effective value is capped at the number of cores on the host machine. If set to 0, it takes the default value.This variable can sync to all nodes, including aggregator and leaf nodes. | Half the number of cores on the host machine. | | `max_autostats_update_workers` | 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. | `1` | | `max_compilation_memory_mb` | The maximum amount of memory (in megabytes) used to compile a query. An error is returned if the query reaches the specified memory limit. This variable can sync to all nodes, including aggregator and leaf nodes. | `4096` | | `max_compilation_time_s` | The maximum time allowed (in seconds) to compile a query. An error is returned if the query reaches the specified compilation time limit, and any subsequent attempts to compile the query fail. This variable can sync to all nodes, including aggregator and leaf nodes. | `600` | | `max_connect_errors` | If the number of interrupted connections from a host exceeds the value of`max_connect_errors`this host will be blocked from further connections. This variable can sync to all nodes, including aggregator and leaf nodes. | `10` | | `max_dpc_lookup_threads` | Controls the maximum concurrent download threads for asynchronous[DPC](https://docs.singlestore.com/db/v9.1/user-and-cluster-administration/maintain-your-cluster/managing-memory/distributed-plancache.md)lookups.This variable can sync to all nodes, including aggregator and leaf nodes. | `50` | | `max_expression_item_limit` | The maximum number of expressions within a query item. The value can be set between 100 and the maximum unsigned`INT`value. The maximum unsigned`INT`value disables this feature. This is a session variable that can also be set globally. This engine variable can sync to all aggregators. | Maximum unsigned`INT`value | | `max_expression_query_limit` | The maximum number of expressions within an entire query. The value can be set between 100 and the maximum unsigned`INT`value. The maximum unsigned`INT`value disables this feature. This is a session variable that can also be set globally. This engine variable can sync to all aggregators. | Maximum unsigned`INT`value | | `max_pooled_connections` | The maximum number of stashed connections per leaf. For more information on this variable, see[In-Depth Variable Definitions](https://docs.singlestore.com/db/v9.1/reference/configuration-reference/engine-variables/list-of-engine-variables/#in-depth-variable-definitions-3.md). This variable can sync to all nodes, including aggregator and leaf nodes. | `1024` | | `max_prefetch_threads` | Sets the maximum number of threads to use for pre-fetching per scan. This variable can sync to all nodes, including aggregator and leaf nodes. | `1` | | `max_prepared_stmt_count` | Sets the maximum number of simultaneous prepared statements. This variable can sync to all nodes, including aggregator and leaf nodes. | `16382` | | `max_table_memory_room_mb` | Sets the maximum amount of memory (in bytes) required when creating or attaching a database. This variable can sync to all nodes, including aggregator and leaf nodes. | `1024` | | `max_vector_index_cache_memory_percent` | Specifies the maximum percentage of available memory (`maximum_memory`) to be used by the vector index cache. Must be strictly between`0`and`100`.This variable can sync to all nodes, including aggregator and leaf nodes. | `99` | | `max_vector_index_cache_memory_mb` | The maximum amount of memory that can be used by the vector index cache (in MB). This variable is calculated based on the value of`max_vector_index_cache_memory_percent`. The maximum value of this variable is (`maximum_memory`- 800MB). This variable is read-only and is provided for user convenience. | | | `maximum_blob_cache_shrink_percent_under_default_spilling_max` | The maximum percentage by which the blob cache can shrink from its original size, when`spilling_maximum_disk_percent = -1`.The value must be between 0 and 0.5.This variable can sync to all nodes, including aggregator and leaf nodes. | `0.3` | | `maximum_blob_cache_size_percent` | Sets the maximum amount of space that can be used by the blob cache. It is a percent of the disk space and its value should be between 0 and 1.For example:`SET GLOBAL maximum_blob_cache_size_percent = 0.90`It is set on the MA unlike`maximum_blob_cache_size_mb`This variable can sync to all nodes, including aggregator and leaf nodes. | `0` | | `memsql_original_server_version` | Displays the version ofSingleStoreinitially installed. This variable can sync to all nodes, including aggregator and leaf nodes. | | | `memsql_snapshot_trigger_size` | Indicates the size (in bytes) of theSingleStoredatabase log upon which a new snapshot will be kicked off. The minimum value this engine variable can be set is`65536`. This variable can sync to all nodes, including aggregator and leaf nodes. | `134217728` | | `monitoring_retention_period` | The period in minutes to keep historical monitoring data. Stored procedures reference this variable to purge values. This variable can sync to all aggregators. | `10080` | | `multi_insert_tuple_count` | Preferred number of tuples in multi-inserts that aggregators send to leaves. This variable can sync to all nodes, including aggregator and leaf nodes. | `20000` | | `multi_statement_xact_idle_timeout` | Time (in seconds) that is allowed for a multi-statement transaction to remain idle while holding locks.This is a session variable that can also be set globally.This variable can sync to all aggregators. | `300` | | `node_degree_of_parallelism` | Controls the number of threads per leaf node for parallel columnstore scans. This variable can sync to all nodes, including aggregator and leaf nodes. | `0` | | `null_timestamps_for_testing` | Variable to write default`TIMESTAMP`columns during testing. This variable can sync to all nodes, including aggregator and leaf nodes. | `FALSE` | | `num_background_merger_threads` | Controls the number of background merger threads to start for each node. The default value of 2 implies two threads run on each node and the work is bound to 2 CPUs. On large nodes, with many CPUs, it is easier for nodes to outpace two merger threads. When there is a high-throughput ingest workload, the merger cannot keep up and query processing gets slower as the data is not well merged.  Use the`mv_columnstore_merge_status`view to find out if the merger process is slow. If it gets behind, try increasing the value of this variable. For example, if with 32 core nodes, you find the merger is not keeping up then increase it from 2 to 8 and observe if the merger is keeping up with the ingest workload. Only 2 merger threads per partition per table are allowed at a time. Hence more partitions per node can use more threads. For one partition on one table increasing the value of this variable will not make any difference.The allowed values range between 2 and 64.This variable can sync to all nodes, including aggregator and leaf nodes. | `2` | | `optimize_stmt_threshold` | A statement count threshold for a procedure or function. When the threshold is exceeded, the procedure or function compiles faster, but the highest level of code optimizations is not applied; however, SQL query optimizations are still in effect. This is a session variable that can also be set globally. This variable can sync to all nodes, including aggregator and leaf nodes. | `50` | | `optimizer_beam_width` | Controls the beam search width. The default value is`10`. Setting this to`1`reverts to the previous behavior (prior to v7.5). Higher values (maximum is`1024`) will make the one-time query optimization time slower. This variable can sync to all nodes, including aggregator and leaf nodes. | `10` | | `optimizer_cross_join_cost` | Increased the default value to reduce the chance of cartesian joins being included when there are incorrect estimations.This variable can sync to all nodes, including aggregator and leaf nodes. | `1.0` | | `optimizer_disable_analyze_cardinality_on_json` | Controls if the cardinality of JSON columns is collected by[autostats](https://docs.singlestore.com/db/v9.1/query-data/query-tuning/statistics-and-sampling/statistics-and-sampling-tasks.md)in`PERIODIC`mode.When`optimizer_disable_analyze_cardinality_on_json`is set to`On`, the engine does not collect cardinality on JSON columns during`ANALYZE`; instead the cardinality is estimated as the number of rows in the table.May be set to`Off`,`On`, or`Auto`.This variable can sync to all aggregators. | `Auto (Off)` | | `optimizer_disable_cte_full_predicate_pushdown` | This variable disables optimizer Common Table Expression (CTE) predicate pushdown.This variable controls whether predicates are pushed into CTEs. Pushing predicates into CTEs typically improves performance; however, there are cases where this pushdown causes poor performance. For example if you have an`IN(...)`filter with many references on the Materialized Common Table Expression (MCTE), the engine is unable to recognize that the`IN(...)`filters are the same. As a result, the engine pushes an`OR`of all the`IN(...)`filters equal to the number of references.This is a session variable that can also be set globally.This variable can sync to all nodes, including aggregator and leaf nodes. | `FALSE` | | `optimizer_disable_limit_pushdown_below_external_udf` | Disable the optimizer rewrite that pushes limits below external User Defined Functions (UDFs).This variable can sync to all nodes, including aggregator and leaf nodes. | `FALSE` | | `optimizer_disable_subselect_to_join` | Setting this to`ON`disables the optimizer rewrite that converts subselect to joins automatically. Disabling this rewrite may cause queries to fail if they require the rewrite to run.This variable can sync to all nodes, including aggregator and leaf nodes. | `OFF` | | `optimizer_disable_transitive_predicates` | Disables predicate transitivity on query rewrites is set to`TRUE`. This is a session variable that can also be set globally.This variable can sync to all nodes, including aggregator and leaf nodes. | `FALSE` | | `optimizer_disable_right_join` | Setting this variable to`TRUE`disables the right join in a query. This allows the left join to be used by the query plan, thereby allowing disk spilling to happen.This variable can sync to all nodes, including aggregator and leaf nodes. | `FALSE` | | `optimizer_disable_subselect_to_join_cte_preprocess` | When set to`TRUE`, this variable disables the subselect to join rewrite for Common Table Expression (CTE) preprocessing before inlining or materialization.This variable can sync to all nodes, including aggregator and leaf nodes. | `FALSE` | | `optimizer_empty_tables_limit` | Sets a threshold for the number of empty tables that must be present in a query before the optimizer falls back to being rule-based, instead of cost-based. This is a session variable that can also be set globally.This variable can sync to all nodes, including aggregator and leaf nodes. | `0` | | `optimizer_enable_json_text_matching` | Applies to a computed column when matching`json_text`as a`json_col persisted text`. When the variable is enabled, a`json_text`computed column can use the following three text types:`TEXT`,`MEDIUM_TEXT`, and`LONG_TEXT`. Disabling this rewrite may cause queries to fail if they require the rewrite to run.This variable can sync to all nodes, including aggregator and leaf nodes. | `FALSE` | | `optimizer_enable_merge_unioned_queries_rewrite` | Allows the optimizer to rewrite unioned queries into a single query.This variable can sync to all nodes, including aggregator and leaf nodes. | `TRUE` | | `optimizer_enable_orderby_limit_self_join` | Allows the optimizer to rewrite leaf`ORDER BY LIMIT`queries using a self-join. Defaults to`TRUE`(enabled). Disabling this rewrite may cause queries to fail if they require the rewrite to run. This variable can sync to all nodes, including aggregator and leaf nodes. | `TRUE` | | `optimizer_feedback_reoptimization_auto_marking` | Switch for whether to enable auto marking for feedback reoptimization.Values:`OFF`,`ON`,`AUTO`This is a session variable that can also be set globally.This variable can sync to all nodes, including aggregator and leaf nodes. | `AUTO` | | `optimizer_histogram_multi_column_correlation_max_selectivity` | Controls an optimizer decision about when to use histograms in join estimates or not.SingleStorerecommends that customers adjust this variable only with guidance fromSingleStore.This is a session variable that can also be set globally.This variable can sync to all nodes, including aggregator and leaf nodes. | `0.9` | | `optimizer_max_cross_joined_array_filters` | This variable controls the conditions under which an optimizer rewrite, which improves the performance of queries with`JOIN`conditions that use`JSON_ARRAY_CONTAINS`, will be performed. The rewrite may be disabled by setting the value to`0`.Refer to[optimizer\_max\_cross\_joined\_array\_filters](https://docs.singlestore.com/db/v9.1/reference/configuration-reference/engine-variables/list-of-engine-variables/#in-depth-variable-definitions-4.md)for details.This is a session variable that can also be set globally.This variable can sync to all nodes, including aggregator and leaf nodes. | `3` | | `optimizer_max_cross_joined_outer_tables_for_subselect_to_join` | Controls the maximum number of outer tables to cross join as part of a subselect to join optimizer rewrite.When joining subselects against outer tables with conditions other than AND/=, the engine needs to cross join all outer tables together. This variable limits the maximum number of outer tables that can be cross joined.This is a session variable that can also be set globally.This variable can sync to all nodes, including aggregator and leaf nodes. | `3` | | `optimizer_max_table_row_count_as_default_cardinality` | This variable controls the maximum estimated table row count to be used as the per column cardinality when no column-level statistics exists.This is a session variable that can also be set globally.This variable can sync to all nodes, including aggregator and leaf nodes. | `10` | | `optimizer_max_tpu_pushdown_table_rowcount` | 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. By pushing down the table and doing joins earlier, more segments can potentially be eliminated earlier. One common case for this rewrite to be useful is when a subselect predicate gets transformed into a table in the join list by the optimizer and there is another union all table in the join list. By turning on this rewrite, the optimizer can pushdown the predicate to each branch of the union all table. Note that this rewrite can only work when force\_heuristic\_rewrites variable gets turned on. A value of`0`indicates to always try to push down table(s) to union, but the pushdown order is not guaranteed.This variable can sync to all nodes, including aggregator and leaf nodes. | `100` | | `optimizer_min_reference_rows_for_gathered_join` | The minimum total number of rows estimates in joined reference tables for gathering non-reference tables to be considered. This variable can sync to all nodes, including aggregator and leaf nodes. | `1000000` | | `optimizer_min_reference_tables_for_gathered_join` | The minimum number of reference tables joined in a query that will be considered before gathering non-reference tables. 0 disables this feature. This variable can sync to all nodes, including aggregator and leaf nodes. | `0` | | `optimizer_not_null_filter_derivation` | Enable or disable the not null filter derivation rewrite in the optimizer. When enabled (`ON`), the optimizer can derive not null filters for base tables based on equality joins. If this filter can be pushed down to a table scan, query execution time for these subsequent joins may be significantly reduced.This variable is settable with an`enum {ON, OFF, AUTO}`and defaults to`AUTO`.Currently,`AUTO`has the same behavior as`OFF`.This variable can sync to all nodes, including aggregator and leaf nodes. | `AUTO` | | `optimizer_use_cross_query_feedback_stats` | Controls whether the optimizer imports feedback rowcounts from the cross query stats cache during cardinality estimation. When enabled and a matching entry is found, the cached rowcount overrides other estimates. This variable also affects the plan cache query hash, so changing it may cause different plans to be generated.Values can be`OFF`,`ON`, or`AUTO`(currently equivalent to`OFF`). This is a session variable that can also be set globally. | `AUTO`**Note**:`AUTO`currently behaves the same as`OFF`(disabled). | | `parametrizer_query_max_params` | The maximum number of scalar constants a single query can contain. Any query with more than`parametrizer_query_max_params`will be failed with an error. This variable can sync to all aggregators. | `1048576` | | `plan_expiration_minutes` | The interval in which a query plan must be reused at least once or it gets unloaded from the query plan cache. This variable can sync to all nodes, including aggregator and leaf nodes. | `720` | | `plan_gc_max_plan_num_per_iteration` | Controls the number of plans that the engine attempts to evict during each iteration of the[plancache](https://docs.singlestore.com/db/v9.1/user-and-cluster-administration/maintain-your-cluster/managing-memory/managing-plancache-memory-and-disk-usage.md)garbage collection scan. The garbage collection scan frequency is determined as:`plan_expiration_minutes`/(`num_of_plans_in_plancache`/`plan_gc_max_plan_num_per_iteration`).This variable can sync to all nodes, including aggregator and leaf nodes. | `10000` | | `preserve_original_colstore_json` | A global variable that preserves`NULL`values and empty arrays in a JSON object that is written to a columnstore table. Set this variable to`OFF`to disable this setting. For more information on this variable, see the[JSON Guide](https://docs.singlestore.com/db/v9.1/create-a-database/using-json.md).This variable can sync to all nodes, including aggregator and leaf nodes. | `AUTO`(same as ON) | | `privilege_caches_update_mode` | 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. This can result in a performance gain.**IT IS IMPORTANT**that the privilege cache updates be reenabled (set back to`always`) once the administration work is finished.This variable can sync to all aggregators.To use this variable:
  1. Set the value to never.
  2. Perform the operations.
  3. Set the value back to always.
| `always` | | `privilege_transfer_mode` | A global variable that controls whether privilege transferability is determined by`GRANT OPTION`or`TRANSFERABLE`keyword in the[GRANT](https://docs.singlestore.com/db/v9.1/reference/sql-reference/security-management-commands/grant.md)security management command. The allowed values for this variable are`grant_option`(default) or`per_privilege`.**Note:**This global variable can be set on aSingleStorecluster.This variable can sync to all nodes, including aggregator and leaf nodes. | `grant_option` | | `processlist_rpc_json_max_size` | The maximum number of bytes available to the`rpc_info`column of`information_schema.mv_processlist`. This variable can sync to all nodes, including aggregator and leaf nodes. | `2048` | | `promote_aggregator_timeout_ms` | Default timeout (in milliseconds) to wait for a`PROMOTE AGGREGATOR`operation to finish before giving up. This variable can sync to all aggregators. | `180000` | | `query_parallelism` | This variable is**deprecated**. Use`query_parallelism_per_leaf_core`instead.Maximum number of simultaneous running queries. This is a session variable that can also be set globally. This variable can sync to all nodes, including aggregator and leaf nodes. | `0` | | `query_parallelism_per_leaf_core` | The fraction of available cores that will be used on each leaf for a single query. The available values are any decimal from 0 to 1. If this value is set a 0, flexible parallelism is turned off. If this value is set at 1, all of the available core's processing power will work on a single query. This is a session variable that can be set on aggregators only. | `1.0` | | `query_shape_serialization_enabled` | Serializes query shape metadata to disk. Available values are`AUTO`,`ON`, and`OFF`. This variable can sync to all nodes, including aggregator and leaf nodes. | `AUTO` | | `query_shape_serialization_file_size_cap` | Maximum size (in bytes) for each query shape in a serialization file. This variable can sync to all nodes, including aggregator and leaf nodes. | `1048576` | | `query_shape_serialization_show_parameters` | Stores actual parameter values to on-disk query shape metadata serialization. This variable can sync to all nodes, including aggregator and leaf nodes. | `TRUE` | | `read_advanced_counters` | If`ON`, this variable enables thecollection of advanced statistics. For more information, see the Advanced Statistics section of the[Management View Statistics Reference](https://docs.singlestore.com/db/v9.1/reference/information-schema-reference/management.md)topic. This variable can sync to all nodes, including aggregator and leaf nodes. | `OFF` | | `recovery_concurrency` | Controls the replay and database initialization concurrency during database recovery. The default is`0`, which means all the CPU cores are used. This variable can sync to all nodes, including aggregator and leaf nodes. | `0` | | `redundancy_level` | If set to 1, there is no redundancy across leaves. If set to 2, turns onSingleStore’s High Availability mode. When you set this variable, its value is propagated to other aggregators only.For more information on this variable, see the[Managing High Availability](https://docs.singlestore.com/db/v9.1/user-and-cluster-administration/high-availability-and-disaster-recovery/managing-high-availability.md)topic.This variable can sync to all nodes, including aggregator and leaf nodes. | `1` | | `regexp_compile_mem_mb` | Specifies the heap allocation limit of the regexp compiler (in megabytes). This variable can sync to all nodes, including aggregator and leaf nodes.Can be manually adjusted by the user. | `2` | | `regexp_error_handling_level` | Controls the behaviour of error handling for[regular expression functions](https://docs.singlestore.com/db/v9.1/reference/sql-reference/regular-expression-functions.md). Allowed values are`'8.7'`,`'8.9'`, and`'9.0'`.`'8.7'`: Preserves prior behavior. NULL is returned when the memory limit is reachedand when the pattern is not a string literal. Provided for backward compatibility.`'8.9'`: Returns an error when the memory limit is reached.Returns NULL on other errors when the pattern is not a string literal.`'9.0'`: Returns an error when the memory limit is reached and when the pattern is not a string literal.Changing this variable will cause query plans using regular expression functions to be re-generated.This variable can sync to all nodes, including aggregator and leaf nodes. | `'9.0'` | | `regexp_format` | Specifies the regular expression format to be used by regular expression functions that you call. Possible values are`'extended'`and`'advanced'`. When you set this variable, its value is propagated to all nodes. For more information on this variable, see the[Regular Expression Functions](https://docs.singlestore.com/db/v9.1/reference/sql-reference/regular-expression-functions.md)topic. This variable can sync to all nodes, including aggregator and leaf nodes. | `'extended'` | | `regexp_output_validation_mode` | This variable controls the output behavior of`REGEXP_REPLACE`and`REGEXP_SUBSTR`expressions if a returned string is invalid under its collation setting. Currently, regular expression built-ins could produce non-utf8 strings since they don't have full support for multi-byte characters.Possible values are`skip`,`return_null`, and`throw_error`.`skip`means no extra validation checks are performed and invalid strings will be allowed.`return_null`means "`SQL-NULL`" will be returned in such cases.`throw_error`means an error will be reported.This variable can sync to all nodes, including aggregator and leaf nodes. | `throw_error` | | `remove_leaf_force_remove_online_leaf` | Controls the behavior of the`REMOVE LEAF ... FORCE`command when`leaf_failure_detection`is`OFF`.When`remove_leaf_force_remove_online_leaf`is`ON`and`leaf_failure_detection`is`OFF`, the`REMOVE LEAF ... FORCE`command forcefully removes the target leaf node from the cluster even if it is online. Refer to[REMOVE LEAF](https://docs.singlestore.com/db/v9.1/reference/sql-reference/cluster-management-commands/remove-leaf.md)for related information.This variable can sync to all aggregators. | `ON` | | `resource_governor_cpu_limit_mode` | Determines whether the mode of control over the`CPU`for resource pools is`HARD`or`SOFT`.On bare‑metal or VM‑based deployments, “available CPU capacity” is based on the host CPU resources visible to the node.On Kubernetes‑based deployments (v9.0.10 and later), resource governance is cgroup‑aware: the engine uses the minimum of the host cores and the cgroup CPU limit configured for the container when interpreting pool CPU percentages.For more information on this variable, see[CREATE RESOURCE POOL](https://docs.singlestore.com/db/v9.1/reference/sql-reference/resource-pool-commands/create-resource-pool.md). This variable can sync to all nodes, including aggregator and leaf nodes. | `SOFT` | | `resource_pool_statement_selector_function` | The function that is executed at runtime that selects the resource pool to use when the user runs a query. The`resource_pool`variable must be set to`system_auto`for the`resource_pool_statement_selector_function`to be used. This variable can sync to all nodes, including aggregator and leaf nodes.For more information on this variable, see the[Setting Resource Limits](https://docs.singlestore.com/db/v9.1/user-and-cluster-administration/use-the-workload-manager-and-set-resource-limits/set-resource-limits/select-a-resource-pool-dynamically.md)topic. | | | `max_user_rg_pools` | Controls the maximum number of resource pools a user can create. By default, users are allowed to create up to 10 resource pools. However, this limit can now be increased by setting this variable to any value between 10 and 50.It cannot be set to a value lower than the number of resource pools the user has already created. For example, if the current value is 30 and the user has already created 20 pools, the variable cannot be reduced to anything below 20. The allowed range for this variable is therefore always between 10 and 50, but must also be at least equal to the number of existing resource pools.For more information refer[Set Resource Limits](https://docs.singlestore.com/db/v9.1/user-and-cluster-administration/use-the-workload-manager-and-set-resource-limits/set-resource-limits.md) | `10` | | `result_table_error_lifetime` | Sets the lifetime (in seconds) of a result table error before garbage collection. This variable can sync to all nodes, including aggregator and leaf nodes. | `1000` | | `sampling_estimate_approach` | Specifies the method used for sampling. Possible values include:`auto`,`aggregation`,`lightweight`, and`filtered`. See[Statistics and Sampling Concepts](https://docs.singlestore.com/db/v9.1/query-data/query-tuning/statistics-and-sampling/statistics-and-sampling-concepts/#section-idm4556665136652832788440675389.md)for more information.This variable can sync to all nodes, including aggregator and leaf nodes.Reoptimize the most recently profiled query based on previous executions of it. | `auto` | | `send_usage_telemetry` | 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 based on the type of cluster:You will also need to allow outbound traffic to`https://phone-home.singlestore.com`.Refer to[Telemetry and Diagnostics](https://docs.singlestore.com/db/v9.1/reference/configuration-reference/engine-variables/list-of-engine-variables/#in-depth-variable-definitions-5.md)for more information. | `OFF` | | `sequence_cache_resize_down_time_us` | Specifies a minimum time interval (in microseconds) that must elapse between cache requests beforeSingleStoredowngrades the cache size for the sequence by 2x.Refer to[AUTO\_INCREMENT AS SEQUENCE](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-definition-language-ddl/create-table/#section-idm234966854099799.md)for more information.This variable can sync to all nodes, including aggregator and leaf nodes. | `1800000000`(1800 seconds or 30 minutes) | | `sequence_cache_resize_up_time_us` | Specifies a time interval in microseconds. If cache requests arrive more frequently than the specified time interval,SingleStoreincreases the cache size for the sequence by 2x.Refer to[AUTO\_INCREMENT AS SEQUENCE](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-definition-language-ddl/create-table/#section-idm234966854099799.md)for more information.This variable can sync to all nodes, including aggregator and leaf nodes. | `1000` | | `sequence_max_cache_count` | Maximum size the pre-fetch cache can reach after`sequence_cache_resize_up_time_us`.Refer to[AUTO\_INCREMENT AS SEQUENCE](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-definition-language-ddl/create-table/#section-idm234966854099799.md)for more information.This variable can sync to all nodes, including aggregator and leaf nodes. | `10000` | | `sequence_min_cache_count` | Minimum number of sequence values pre-fetched by an aggregator.Refer to[AUTO\_INCREMENT AS SEQUENCE](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-definition-language-ddl/create-table/#section-idm234966854099799.md)for more information.This variable can sync to all nodes, including aggregator and leaf nodes. | `100` | | `service_edition` | Displays the service edition:`STANDARD`or`ENTERPRISE`. This variable can sync to all nodes, including aggregator and leaf nodes. | `STANDARD` | | `set_global_variables_allowed_list` | A JSON array of strings, where each string is the name of an engine variable. This list provides a means of access to engine variables that are not normally settable bySingleStore Heliosusers. The contents of the list can be set only by SingleStore customer support engineers. In consultation with SingleStore support engineers, this can allow you to set certain variables yourself -- providing you have`SYSTEM_VARIABLES_ADMIN`privilege -- that would not be otherwise available for you to control. This variable can sync to all nodes, including aggregator and leaf nodes. | | | `show_query_parameters` | If`ON`, query parameters will be visible in the output of`SHOW PROCESSLIST`and in the output of`SELECT`from`INFORMATION_SCHEMA.PROCESSLIST`. If it is`OFF`, parameters will be hidden. This variable can sync to all nodes, including aggregator and leaf nodes. | `ON` | | `show_with_portability_comments` | Adds conditional compatibility comments to outputs like`SHOW CREATE TABLE`to help with portability. This variable can sync to all nodes, including aggregator and leaf nodes. | `FALSE` | | `singlebox_optimizer_cost_based_threshold` | Maximum number of tables present in a join which will utilize the cost-based singlebox join optimizer. For joins with more than this many tables, singlebox join optimization will use an alternate, rule-based algorithm which is faster but less effective. The singlebox join optimizer is responsible for making decisions about the local aspects of how to efficiently execute a join, such as choosing an index. This variable must be set between 0 and 30 tables.This is a session variable that can also be set globally. This variable can sync to all nodes, including aggregator and leaf nodes. | `18` | | `skip_segelim_with_inlist_threshold` | Controls when segment elimination will not use an`IN`clause that is too large. This variable can sync to all nodes, including aggregator and leaf nodes. | `1000`(elements) | | `skip_sharding_warmup_query` | When set to true, disables the warmup process that sends queries to all leaves when the query accesses a single partition. Useful for one-time, non-parameterized queries.This variable can sync to all nodes, including aggregator and leaf nodes.This is a session variable that can also be set globally. | `FALSE` | | `snapshot_trigger_size` | The aggregate size of transaction logs (in bytes) per partition which, when reached, will trigger a new snapshot. This variable can sync to all nodes, including aggregator and leaf nodes. | `2147483648` | | `snapshot_wait_for_blob_gc_seconds` | Specifies how much time (in seconds) to wait between snapshots before taking a snapshot for blob garbage collection from previous snapshots. This variable can sync to all nodes, including aggregator and leaf nodes. | `180` | | `snapshots_to_keep` | Number of snapshot and log files to keep for backup and replication. This variable may be set live. This variable can sync to all nodes, including aggregator and leaf nodes. | `2` | | `sp_query_dynamic_param` | Specifies whether different query plans are generated for`CALL`queries when arguments to the stored procedure are`NULL`or not`NULL`arguments. If enabled, queries that use variables inside stored procedures will use the same query plan. For more details, see the[In-Depth Variable Definitions](https://docs.singlestore.com/db/v9.1/reference/configuration-reference/engine-variables/list-of-engine-variables/#in-depth-variable-definitions-6.md)section below\.This variable can sync to all aggregators. | `AUTO` | | `spilling_maximum_disk_percent` | Specifies the maximum percentage of disk space to be used by spilling during query execution. The value must be between 0 and 1, or -1 (default).  The default is recommended in general.Under the default setting, spilling will first use available free disk space. Spilling will not use space from the blob cache until the free usable disk space hits a low threshold, which is 10% of (total disk space -`minimal_disk_space`). After this threshold is reached, spilling is allowed to to use up to 30% of the blob cache space set by the engine variable`maximum_blob_cache_size_mb`.The maximum amount of blob cache space that spilling can negotiate to use  is 30% of`maximum_blob_cache_size_mb`.Refer to[Disk Spilling](https://docs.singlestore.com/db/v9.1/user-and-cluster-administration/maintain-your-cluster/managing-memory/disk-spilling.md)for more details on spilling.This variable can sync to all nodes, including aggregator and leaf nodes. | `-1` | | `spilling_node_memory_threshold_ratio` | Memory threshold (ratio of total node memory, 0 - 1) to start spilling. This variable can sync to all nodes, including aggregator and leaf nodes. | `0.75` | | `spilling_query_operator_memory_threshold` | Memory threshold (bytes) for a query operator to be considered for spilling. This variable can sync to all nodes, including aggregator and leaf nodes. | `104857600` | | `sql_mode` | Specifies the SQL mode or modes that affect the SQL syntaxSingleStoresupports and the query validation checks it performs. See the[sql\_mode](https://docs.singlestore.com/db/v9.1/reference/configuration-reference/engine-variables/list-of-engine-variables/#in-depth-variable-definitions-7.md)section below for more information. This is a session variable that can also be set globally. This variable can sync to all aggregators. | `STRICT_ALL_TABLES, NO_AUTO_CREATE_USER` | | `sql_quote_show_create` | If`ON`, identifiers are quoted by the server for`SHOW CREATE`commands. This is a session variable that can also be set globally. This variable can sync to all nodes, including aggregator and leaf nodes. | `ON` | | `sql_select_limit` | The maximum number of rows returned by a`SELECT`query. If the`LIMIT`clause is specified in a`SELECT`query, the value in the`LIMIT`clause overrides`sql_select_limit`. This is a session variable that can also be set globally. This variable can sync to all nodes, including aggregator and leaf nodes. | `2^64-1` | | `sub_to_physical_partition_ratio` | The number of sub-partitions per physical partition that a new database will be created with. It must be set at 0 or a power of 2 value of the physical partition count. The available values are 2, 4, 8, 16, 32, and 64. This variable can sync to all nodes, including aggregator and leaf nodes. | `4` | | `subproc_abridged_errors` | Makes subprocess errors easier to read. This variable can sync to all nodes, including aggregator and leaf nodes. | `TRUE` | | `subprocess_azure_retries` | This engine variable is deprecated. Maximum retries for Azure operations. This variable can sync to all nodes, including aggregator and leaf nodes. | `10` | | `subprocess_backup_retries` | The maximum number of retries for`BACKUP`upload and download subprocesses. This variable can sync to all nodes, including aggregator and leaf nodes. | `3` | | `subprocess_ec2_metadata_timeout_ms` | The maximum amount of time, in milliseconds, the engine waits for or retries a request to retrieve EC2 instance metadata, which is used to verify that the cluster is running on AWS and to obtain implicit credentials. It is used by features like S3 pipelines and`BACKUP`/`RESTORE`when credentials are not explicitly provided.This variable can sync to all nodes, including aggregator and leaf nodes.If your deployment is not on AWS, or if access to instance metadata service (IMDS) is blocked, reduce this value to avoid pipeline create/start timeouts when fetching implicit credentials is attempted. For example, if the`169.254.169.254`IP address is blocked by a firewall, then reduce this value to`1000`or lower. | `60000` | | `subprocess_io_idle_timeout_ms` | The maximum amount of time (in milliseconds) the engine waits for or retries a request before timing out and failing the backup when connecting to cloud providers. When you set this variable, its value is propagated to all nodes. This variable can sync to all nodes, including aggregator and leaf nodes. | `600000` | | `subprocess_max_retries` | This variable is the setting for the`MaxRetries`parameter for S3 and GCS. Connectivity issues that are retry-able will automatically try to connect the default number of times. This variable can sync to all nodes, including aggregator and leaf nodes. | `10` | | `subquery_merge_with_outer_joins` | This session variable fine-tunes the behavior of the rewrite`SubQueryMerge`.If the variable is set to`ON`, always merge the sub-select with outer join(s). If the variable is set to`HEURISTIC`, check to see if merging the sub-select with outer join(s) will cause negative performance for the overall query. If the variable is set to`OFF`, don’t merge sub-selects with outer join(s). If the variable is set to`AUTO`(this is the default); for v7.5 and previous versions,`AUTO`will behave like`ON`. For versions after v7.5,`AUTO`will behave like`HEURISTIC`.This variable can sync to all nodes, including aggregator and leaf nodes. | `AUTO` | | `suppress_dml_warnings` | When set to`TRUE`, Data Manipulation Language (DML) warnings are suppressed during query execution; DML warnings will continue to appear in the results of`SHOW WARNINGS`.This variable can sync to all aggregators. | `FALSE` | | `sync_partitions_timeout_sec` | Specifies the timeout (in seconds) to synchronize theclustermetadata across thecluster. This variable can sync to all nodes, including aggregator and leaf nodes. | `180` | | `sync_permissions` | This aggregator will synchronize its permissions with other aggregators. When ON, this variable also enables DDL forwarding.See[Synchronizing Permissions Across Your Cluster](https://docs.singlestore.com/db/v9.1/security/administration/synchronizing-permissions-across-your-cluster.md)for more information about utilizing this variable.This variable can sync to all aggregators. | `ON` | | `sync_slave_timeout` | This engine variable is deprecated. Maximum amount of time (in milliseconds) for the primary to wait for acknowledgment from the synchronous replica. This variable can sync to all nodes, including aggregator and leaf nodes. | `10000` | | `synchronize_reference_timeout_ms` | Specifies the time (in seconds) long running queries wait for reference databases to synchronize on commit in the cluster. This variable can sync to all nodes, including aggregator and leaf nodes. | `20` | | `table_name_case_sensitivity` | Sets the case-sensitivity for database object names. If set to`OFF`, tables, views, table aliases, and user-defined table-valued functions (TVFs) are not case-sensitive. For details on case-sensitivity, refer to[Database Object Case-Sensitivity](https://docs.singlestore.com/db/v9.1/reference/sql-reference/database-object-case-sensitivity.md). This variable can sync to all nodes, including aggregator and leaf nodes. | `ON` | | `target_group_size_for_join_split_rewrite` | Specifies the maximum input size (group size), in rows, for the smaller joins in the[Join Memory Reduction Optimization](https://docs.singlestore.com/db/v9.1/query-data/query-tuning/join-memory-reduction-optimization.md). Value must be greater than 10000000.This variable can sync to all nodes, including aggregator and leaf nodes.This engine variable is configurable onSingleStore Helios. | `10000000` | | `throttle_replay_behind_tail_mb` | 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. Values can range from 10MB to 4GB.This variable can sync to all nodes, including aggregator and leaf nodes. | `2048`(in MB) | | `trace_events_queue_size` | The maximum size, in bytes, of the trace events ring buffer,`MV_TRACE_EVENTS,`where the oldest events are overwritten when the buffer wraps around. To save events, other software, such as the[Query History](https://docs.singlestore.com/db/v9.1/query-data/query-tuning/query-history.md)feature, must gather them before they age out of`MV_TRACE_EVENTS`.This variable can sync to all nodes, including aggregator and leaf nodes.This engine variable is configurable onSingleStore Helios. | `0`MB (value must be provided in bytes)Setting this value to`0`clears the queueLeaving this value set to`0`disables**all**event tracing | | `tracelog_retention_period` | Indicates the retention period (in days) for trace log files. | `0` | | `tracelog_retention_size` | Controls the retention, by total size in megabytes, of the rotated files of each component log separately.SingleStorewill create a separate "memsql\_\.log" file for each component. As trace events happen they are added to the corresponding component log.For example, the repl log component of the engine will write traces to the memsql\_REPL log. If the total retention is 500MB and all REPL\* files are over that limit they will be removed until the size is below the limit. This is done for every component and also for the main "memsql.log" log file. | `0` | | `transaction_isolation` | Transaction isolation level. This is a session variable that can also be set globally. This variable can sync to all aggregators.This engine variable is configurable onSingleStore Helios. | `READ-COMMITTED` | | `two_phase_commit` | This variable has been deprecated and is no longer operational inSingleStoreversion 9.0 or later. | `ON` | | `tx_isolation` | Transaction isolation level. This is a session variable that can also be set globally. This variable can sync to all aggregators.This engine variable has been deprecated. It is being kept for backward compatibility. Please see the`transaction_isolation`engine variable. | `READ-COMMITTED` | | `trim_malloc` | Determines whether`malloc_active_memory`usage is trimmed periodically. See[SHOW STATUS EXTENDED](https://docs.singlestore.com/db/v9.1/reference/sql-reference/show-commands/show-status-extended.md)for more information. This variable can sync to all nodes, including aggregator and leaf nodes. | `TRUE` | | `use_seekable_json` | Used to enable seekable encoding for JSON. This variable can sync to all nodes, including aggregator and leaf nodes.See[Columnstore Seekability using JSON](https://docs.singlestore.com/db/v9.1/create-a-database/columnstore/columnstore-seekability-using-json.md)for more information about utilizing this variable. | `ON` | | `use_user_provided_index_types_in_show` | Controls what will be displayed via the`DESCRIBE `or`SHOW COLUMNS`syntaxes for backward compatibility. When set to`TRUE,`"PRI" is displayed only if a primary key was created on a table. If set to`FALSE`, "PRI" is displayed only for sort keys created on a table.This variable can sync to all nodes, including aggregator and leaf nodes. | `TRUE` | | `use_vectorized_join` | This variable has been deprecated and is no longer operational inSingleStoreversion 5.0 or newer. This variable can sync to all nodes, including aggregator and leaf nodes. | `ON` | | `varchar_column_string_optimization_length` | For a column defined as type`CHAR`of length`len`; stores the column as a`VARCHAR`of length`len`, if`len`is greater than or equal to the value of this variable. If the value of this variable is`0`, the column is**not**stored as a`VARCHAR`.This variable can sync to all nodes, including aggregator and leaf nodes. | `0` | | `verbose_time_heartbeat_thread` | Enables logging to heartbeat thread in order to better diagnose process stalls. This logging is not intended for customer consumption and should be requested by and provided toSingleStorefor investigation. The default value is`off`, setting this to`on`will enable the logging.This variable can sync to all nodes, including aggregator and leaf nodes. | `OFF` | | `vector_index_fallback_non_index_scan` | Controls the mechanism for falling back to a full table scan when indexed vector search does not return sufficient results.This variable can sync to all nodes, including aggregator and leaf nodes. | `TRUE` | | `vector_join_optimization` | Enables the`JSON_TO_ARRAY()`optimization. Refer to[JSON Array Performance](https://docs.singlestore.com/db/v9.1/create-a-database/using-json/#section-idm323419580718356.md)for more information.This variable can sync to all nodes, including aggregator and leaf nodes. | `ON` | | `vector_type_project_format` | Controls the format in which VECTOR-type columns and expressions are projected. Allowed options are JSON and BINARY. For new deployments, the default value will be BINARY. For upgrades from 8.5, the value will be set to JSON to preserve 8.5 behavior.This variable can sync to all nodes, including aggregator and leaf nodes. | `BINARY` | | `verify_fields_in_transitivity` | Limits the application of join predicate transitivity to predicates that may be estimated. To use, set this variable to`ON`. This variable can sync to all nodes, including aggregator and leaf nodes. | `OFF` | | `wait_die_retry_on_die_sleep_factor` | Controls how much time (in milliseconds) a transaction waits on a rowlock before giving up when the decision is "die". The value of the variable is multiplied by the number of locks that the transaction already holds, so if a transaction already has a lot of locks, it will wait longer. The purpose of this mechanism is to avoid unnecessary rollbacks because sometimes there is no real deadlock and instead of giving up right away we wait a bit for the lock to be released before rolling back all the work that was done.This variable can sync to all nodes, including aggregator and leaf nodes. | `250` | | `cluster_name` | Specifies theclustername used by the backup. This variable can sync to all nodes, including aggregator and leaf nodes. | | ## Full-Text Search Sync Variables | Name | Description | Default | | -------------------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ---------------- | | `enable_fts2_merger` | Enables the full-text index merger (LINK).This variable can sync to all nodes, including aggregator and leaf nodes. | `FALSE` | | `fts2_cache_blob_memory_mb` | The maximum size (in MB) of an index blob that can be cached in memory in the full-text version 2 collocated service. Must be at least`0`.Requres restarting the full-text version 2 service with`FULLTEXT SERVICE STOP`to take effect.This variable can sync to all nodes, including aggregator and leaf nodes. | `20` | | `fts2_cache_enable` | Enables the full-text search version 2 collocated service's index cache.This variable can sync to all nodes, including aggregator and leaf nodes. | `FALSE` | | `fts2_cache_duration_secs` | This is the time-to-live (TTL) for cache objects in the full-text version 2 collocated service. Must be at least`1`.Requres restarting the full-text version 2 service with`FULLTEXT SERVICE STOP`to take effect.This variable can sync to all nodes, including aggregator and leaf nodes. | `120` | | `fts2_cache_max_size` | The maximum number of index blobs the full-text version 2 collocated service can hold at a time. Must be at least`1`.Requres restarting the full-text version 2 service with`FULLTEXT SERVICE STOP`to take effect.This variable can sync to all nodes, including aggregator and leaf nodes. | `128` | | `fts2_ctrl_endpoint` | Collocated service control endpoint for full-text search version 2.**Not configurable onSingleStore Helios.**This variable can sync to all nodes, including aggregator and leaf nodes. | `/tmp/s2-ftsctl` | | `fts2_endpoint` | Collocated service request endpoint for full-text search version 2.**Not configurable onSingleStore Helios.**This variable can sync to all nodes, including aggregator and leaf nodes. | `/tmp/s2-fts` | | `fts2_index_threadpool_size` | Controls the number of threads in the full-text version 2 index thread pool.Refer to`fts2_use_index_threadpool`.Requres restarting the full-text version 2 service with`FULLTEXT SERVICE STOP`to take effect.This variable can sync to all nodes, including aggregator and leaf nodes. | `8` | | `fts2_init_memory_mb` | The amount of heap memory with which to start the Java process for the full-text search version 2 collocated service. If it is`0`, the Java default is used.Requres restarting the full-text version 2 service with`FULLTEXT SERVICE STOP`to take effect.This variable can sync to all nodes, including aggregator and leaf nodes. | `0` | | `fts2_max_connections` | The maximum number of parallel requests that the full-text search version 2 collocated service can accept before queuing them.The default value is`32`for versions 8.7.12 and forward.Requres restarting the full-text version 2 service with`FULLTEXT SERVICE STOP`to take effect.This variable can sync to all nodes, including aggregator and leaf nodes. | `32` | | `fts2_max_idle_seconds` | The number of seconds that the full-text search collocated service may be idle before shutting itself down.Requres restarting the full-text version 2 service with`FULLTEXT SERVICE STOP`to take effect.This variable can sync to all nodes, including aggregator and leaf nodes. | `1800` | | `fts2_max_service_buffer_mb` | Sets the maximum size of the memory-mapped region used to communicate with the FTS service (in MB). Minimum value is 1MB.This variable can sync to all nodes, including aggregator and leaf nodes. | `2048` | | `fts2_max_memory_mb` | The maximum amount of heap memory that the Java process for the full-text search version 2 collocated service may use. If it is 0, the Java default is used.This variable can sync to all nodes, including aggregator and leaf nodes. | `0` | | `fts2_merge_soft_limit_mb` | The maximum size (in MB) of a full-text version 2 index that is eligible to be merged. This size excludes estimated space taken up by invalidated or deleted segments. This limit is not strictly enforced and may be exceeded in some cases.This variable can sync to all nodes, including aggregator and leaf nodes. | `5 * 1024` | | `fts2_monitor_interval_secs` | This is the number of seconds between monitor polls (0 disables it).This variable can sync to all nodes, including aggregator and leaf nodes. | `60` | | `fts2_position_increment_gap` | Sets the positionIncrementGap for use when indexing multi-valued fields such as in JSON or BSON columns. Concatenates multiple values within a field. This variable controls the amount of virtual white space that is inserted between same-field values during a search. This helps prevent proximity and phrase queries from erroneously matching across field instances. The default value gives good results in most cases.This variable can sync to all nodes, including aggregator and leaf nodes. | `100` | | `fts2_query_timeout` | 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 variable can sync to all nodes, including aggregator and leaf nodes. | `5` | | `fts2_sentinel_duration_secs` | Sets the ping interval of the full-text version 2 service (in seconds). Must be at least`1`.Requres restarting the full-text version 2 service with`FULLTEXT SERVICE STOP`to take effect.This variable can sync to all nodes, including aggregator and leaf nodes. | `2` | | `fts2_socket_timeout_ms` | Sets the full-text version 2 service's socket timeout (in milliseconds).This variable can sync to all nodes, including aggregator and leaf nodes. | `250` | | `fts2_start_timeout_secs` | The maximum number of seconds to wait for the full-text version 2 collocated service to become initially pingable before it will be automatically killed and restarted.Requres restarting the full-text version 2 service with`FULLTEXT SERVICE STOP`to take effect.This variable can sync to all nodes, including aggregator and leaf nodes. | `5` | | `fts2_stats_cache_duration_secs` | This is the time-to-live (TTL) for stats cache objects in the full-text version 2 collocated service. Value must be at least 1.Requres restarting the full-text version 2 service with`FULLTEXT SERVICE STOP`to take effect.This variable can sync to all nodes, including aggregator and leaf nodes. | `120` | | `fts2_stats_cache_enable` | Enables the full-text version 2 collocated service's statistics cache.This variable can sync to all nodes, including aggregator and leaf nodes. | `FALSE` | | `fts2_stats_cache_max_size` | The maximum number of index blobs which can cache statistics in the full-text version 2 collocated service at a time. Value must be at least 1.Requres restarting the full-text version 2 service with`FULLTEXT SERVICE STOP`to take effect.This variable can sync to all nodes, including aggregator and leaf nodes. | `128` | | `fts2_stats_cache_max_terms` | The maximum number of term statistics per index blob which can be cached in the statistics cache in the full-text version 2 collocated service at a time. Value must be at least 1.Requres restarting the full-text version 2 service with`FULLTEXT SERVICE STOP`to take effect.This variable can sync to all nodes, including aggregator and leaf nodes. | `4096` | | `fts2_stop_timeout_secs` | When the full-text version 2 collocated service is shut down in a controlled manner with the[FULLTEXT SERVICE STOP](https://docs.singlestore.com/db/v9.1/reference/sql-reference/operational-commands/fulltext-service-stop.md)comand, this sets the number of seconds to wait before it must be forcibly killed.This variable can sync to all nodes, including aggregator and leaf nodes. | `60` | | `fts2_use_index_threadpool` | Enables the usage of a separate thread pool for full-text version 2 indexing.Requres restarting the full-text version 2 service with`FULLTEXT SERVICE STOP`to take effect.This variable can sync to all nodes, including aggregator and leaf nodes. | `FALSE` | ## JSON Sync Variables | Name | Description | Default | | ----------------------------------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `enable_json_statistics` | Enables JSON statistics support if set to`TRUE`. This variable can sync to all nodes, including aggregator and leaf nodes. | `FALSE` | | `json_collation` | Controls the collation of JSON columns. When the collation for a JSON column is not set explicitly, the collation for that column is the value of`json_collation`. Can be`utf8mb4_bin`or`utf8_bin`.Refer to[Using JSON](https://docs.singlestore.com/db/v9.1/create-a-database/using-json/#UUID-00e2ca08-8dc7-bc24-f268-b6bfde552e6a.md)for information.The`json_collation`variable is independent of other character set and collation variables for backward compatibility reasons. The values of those variables, including`character_set_server`, do not impact the value of`json_collation`.This variable can sync to all nodes, including aggregator and leaf nodes. | `utf8mb4_bin`When upgrading fromSingleStoreversions 8.5 and earlier, the default value of`json_collation`depends on the value of`character_set_server`.If`character_set_server`is`utf8`, then the default value of`json_collation`will be`utf8_bin`. If`character_set_server`is`utf8mb4`, then the default value of`json_collation`will be`utf8mb4_bin`. | | `json_compatibility_level` | Controls whether the`JSON_SET_`function creates a keypath if it does not exist. The default is`7.8`, which means missing keypaths are not created.This variable can sync to all nodes, including aggregator and leaf nodes. | `7.8` | | `json_document_max_children` | The maximum number of children allowed during schema inference. Must be between1and100000. This variable can sync to all nodes, including aggregator and leaf nodes. | `10000` | | `json_document_max_leaves` | Limits the number of JSON key paths inferred within a segment. Must be between 1 and100000. This variable can sync to all nodes, including aggregator and leaf nodes. | `10000` | | `json_document_sparse_children_check_ratio` | The minimum average frequency to infer the schema for a json sub-object. Must be between 10 and 100000. This variable can sync to all nodes, including aggregator and leaf nodes. | `100` | | `json_document_sparse_children_check_threshold` | The number of children after which the sparse condition is checked. Must be between 128 and 16384. This variable can sync to all nodes, including aggregator and leaf nodes. | `250` | | `json_document_absolute_sparse_key_check_ratio` | The minimum absolute frequency to infer the schema for a json sub-object. Must be between 10 and 100000. This variable can sync to all nodes, including aggregator and leaf nodes. | `1000` | | `json_document_page_data_soft_limit` | The soft limit to how much data fits in a single internal page while compressing a json column. Must be between 1 and 1000000000000. This variable can sync to all nodes, including aggregator and leaf nodes. | `1024*1024*1024` | | `json_extract_string_collation` | Controls the collation setting for the`JSON_EXTRACT_STRING`and`JSON_MATCH_ANY`function. This variable can sync to all nodes, including aggregator and leaf nodes. For versions before 7.8.21, the default is`AUTO`. In version 7.8.21 and forward`SERVER_V2`is the default. For more details, see the[In-Depth Variable Definitions](https://docs.singlestore.com/db/v9.1/reference/configuration-reference/engine-variables/list-of-engine-variables/#in-depth-variable-definitions-8.md)section below\.This variable can sync to all nodes, including aggregator and leaf nodes. | `auto/SERVER_V2` | | `parametrize_json_keys` | Used for parametrizing the JSON key path arguments for`JSON_EXTRACT_xxx`functions and the`::`syntax so that query plans can be reused.This variable can sync to all nodes, including aggregator and leaf nodes. | `FALSE` | ## Password Complexity Sync Variables For information about how to configure a password complexity policy, see [Configuring a Password Policy](https://docs.singlestore.com/db/v9.1/security/authentication/configuring-a-password-policy.md). | Name | Description | Default | | -------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | ------- | | `password_max_consec_repeat_chars` | The maximum number of consecutive repeated characters allowed (`0`to`100`). For example, if set to 3, passwords with 4 or more consecutive repeated characters (e.g.,“aaaa”or“1111”) are disallowed. This variable can sync to all nodes, including aggregator and leaf nodes. | `0` | | `password_max_consec_sequential_chars` | The maximum number of consecutive characters allowed (`0`to`100`). For example, if set to 3, passwords with a 4-letter sequence or longer (e.g.“1234”or“abcd”) are disallowed. This variable can sync to all nodes, including aggregator and leaf nodes. | `0` | | `password_min_length` | The minimum number of characters required (`0`to`100`). This variable can sync to all nodes, including aggregator and leaf nodes. | `0` | | `password_min_lowercase_chars` | The minimum number of lowercase characters required (`0`to`100`). This variable can sync to all nodes, including aggregator and leaf nodes. | `0` | | `password_min_numeric_chars` | The minimum number of numeric digit characters required (`0`to`100`). This variable can sync to all nodes, including aggregator and leaf nodes. | `0` | | `password_min_special_chars` | The minimum number of special (non-alphanumeric) characters required (`0`to`100`). This variable can sync to all nodes, including aggregator and leaf nodes. | `0` | | `password_min_uppercase_chars` | The minimum number of uppercase characters required (`0`to`100`). This variable can sync to all nodes, including aggregator and leaf nodes. | `0` | ## Pipelines Sync Variables You cannot set a variable for a specific pipeline – each variable setting applies to all pipelines in the cluster. | Name | Description | Default | | --------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ----------- | | `advanced_hdfs_pipelines` | Specifies whether to enable security features for HDFS pipelines.See[Enabling Wire Encryption and Kerberos on HDFS Pipelines](https://docs.singlestore.com/db/v9.1/load-data/data-sources/load-data-from-hdfs-using-a-pipeline/enabling-wire-encryption-and-kerberos-on-hdfs-pipelines.md)for more information.This variable can sync to all nodes, including aggregator and leaf nodes. | `OFF` | | `custom_java_pipelines` | Allows custom java pipelines. This variable can sync to all nodes, including aggregator and leaf nodes. | `OFF` | | `enable_eks_irsa` | Enables or disables EKS IRSA authentication. Used for pipelines consuming data from AWS S3 or Iceberg tables utilizing AWS Glue and S3.This variable can sync to all nodes, including aggregator and leaf nodes. | `OFF` | | `ingest_errors_max_disk_space_mb` | The maximum amount of disk space (in megabytes) that is used to log errors for pipelines. This variable can sync to all nodes, including aggregator and leaf nodes. | `102400` | | `java_pipelines_extractor_class` | Implements an extractor interface used for experimental Java pipelines. This variable can sync to all nodes, including aggregator and leaf nodes. | | | `java_pipelines_heap_size` | Heap size (in megabytes) for HDFSpipelines. This variable can sync to all nodes, including aggregator and leaf nodes. | `36` | | `pipelines_avro_schema_cache_enabled` | Reuses schemas from the Confluent Schema Registry across batches for each pipeline to avoid redundant schema registry fetches.This variable can sync to all nodes, including aggregator and leaf nodes.This engine variable is configurable onSingleStore Helios. | `OFF` | | `pipelines_batch_interval` | 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. | `<1>` | | `pipelines_batches_metadata_to_keep` | The number of batch metadata entries to persist*per partition, per pipeline*before they are overwritten by incoming batches. As data is extracted from a source, it’s written in batches to a destination table on a leaf node. Metadata about these batches is temporarily persisted in the master aggregator’s`information_schema.PIPELINES_BATCHES`table. As new batches are loaded into the database, the oldest batch metadata entries will be removed from the`information_schema.PIPELINES_BATCHES`table. See the[PIPELINES\_BATCHES](https://docs.singlestore.com/db/v9.1/reference/information-schema-reference/data-ingest/pipelines-batches.md)section for more information about this metadata.This variable can sync to all nodes, including aggregator and leaf nodes. | `1000` | | `pipelines_cdc_row_emit_delay_us` | Specifies a forced delay in row emission while migrating/replicating your tables (or collections) to yourSingleStoredatabases. It can have a maximum value of`1000000`.Refer to[Replicate Data from MongoDB®](https://docs.singlestore.com/db/v9.1/load-data/data-sources/replicate-data-from-mongodb.md)for more information.This variable can sync to all nodes, including aggregator and leaf nodes. | `1` | | `pipelines_cdc_java_heap_size` | Specifies the JVM heap size limit (in MBs) for CDC-in pipelines.Refer to[Replicate Data from MongoDB®](https://docs.singlestore.com/db/v9.1/load-data/data-sources/replicate-data-from-mongodb.md)for more information.This variable can sync to all nodes, including aggregator and leaf nodes. | `128` | | `pipelines_deskew_batch_partitions_threshold` | For keyless sharded destination tables, if less than this fraction of batch partitions are active, reshuffle to avoid skew. This variable can sync to all nodes, including aggregator and leaf nodes. | `0.75` | | `pipelines_errors_retention_minutes` | The amount of time in minutes that a pipeline error is stored on disk. These errors are surfaced in[PIPELINES\_ERRORS](https://docs.singlestore.com/db/v9.1/reference/information-schema-reference/data-ingest/pipelines-errors.md)as long as they are stored on disk. This variable must be set to at least`0`.This variable can sync to all nodes, including aggregator and leaf nodes. | `1440` | | `pipelines_extractor_core_on_timeout` | Generates core dumps on pipeline extractor operations that timeout. This variable can sync to all nodes, including aggregator and leaf nodes. | `FALSE` | | `pipelines_extractor_debug_logging` | Specifies whether to enable extractor debugging for Kafka or HDFS pipelines. This variable currently does not apply to S3 pipelines. This variable can sync to all nodes, including aggregator and leaf nodes. | `OFF` | | `pipelines_extractor_get_offsets_timeout_ms` | The maximum time (in milliseconds) to wait for offset data to be returned from the data source before returning an error. Increase this value if you experience timeout errors, such as`ERROR 1970 (HY000): Subprocess timed out.`Use the value`0`to indicate no timeout.This variable can sync to all aggregators. | `35000` | | `pipelines_extractor_idle_timeout_ms` | The maximum time (in milliseconds) that a pipeline will wait for more data from an external source. Use the value`0`to indicate no timeout. This variable can sync to all nodes, including aggregator and leaf nodes. | `120000` | | `pipelines_extractor_max_memory` | Maximum memory of a pipeline extractor (in megabytes). Minimum value for this engine variable is`1`. This variable can sync to all nodes, including aggregator and leaf nodes. | `500` | | `pipelines_gc_max_files` | Max number of files that will be garbage collected in one iteration. Minimum value for this engine variable is`1`. This variable can sync to all aggregators. Refer to[Pipeline Using Metadata Garbage Collection (GC)](https://docs.singlestore.com/db/v9.1/reference/sql-reference/pipelines-commands/create-pipeline/#section-id235172265956434.md)for more information. | `10000` | | `pipelines_iceberg_heap_size` | Java heap size (in megabytes) for Iceberg metadata workers (aggregators) which process Iceberg table metadata files. | `96` | | `pipelines_iceberg_data_workers_heap_size` | Java heap size (in megabytes) for Iceberg data workers (leaves) which process Iceberg table data files. This variable can sync to all nodes, including aggregator and leaf nodes. | `500` | | `pipelines_kafka_version` | The Kafka version used for the Kafka extractor. This variable can sync to all nodes, including aggregator and leaf nodes.This variable is not applicable to current (>= 0.10.0.10) Kafka versions asSingleStorerequests a list of supported Kafka broker features using the`ApiVersionRequest`.For legacy Kafka,`pipelines_kafka_version`or`kafka_version`from a pipeline config is used to determine the list of supported features. | `0.10.0.10` | | `pipelines_max_concurrent` | The maximum number of pipelines running concurrently. Refer to[About SingleStore Pipelines](https://docs.singlestore.com/db/v9.1/load-data/about-singlestore-pipelines.md)for more information. This variable can sync to all nodes, including aggregator and leaf nodes.It can have a maximum value of`4096`.**Note**: While increasing the maximum number of concurrently running pipelines can improve ingestion parallelism, it may also increase peak resource consumption and resource contention with other workloads. | `50` | | `pipelines_max_concurrent_batch_partitions` | The maximum number of pipeline batch partitions running concurrently. The number of partitions when using the default setting (0) is 2, or the number of leaves on theclustertimes the`default_partitions_per_leaf`setting, whichever is higher.This variable can sync to all nodes, including aggregator and leaf nodes. | `0` | | `pipelines_max_errors_per_partition` | Deprecated in MemSQL 6.7. The maximum number of error event rows per leaf node partition to persist before they are deleted. Once the specified number of rows in the`information_schema.PIPELINES_ERRORS`table is reached, the database will eventually remove the oldest rows from the table. The removal mechanism for older error data is based on heuristics. Old errors are guaranteed to exist up to the specified number, but they may not immediately be removed.This variable can sync to all nodes, including aggregator and leaf nodes. | `1000` | | `pipelines_max_offsets_per_batch_partition` | The maximum number of data source partition offsets to extract in a single batch transaction. If the data source’s partition contains fewer than the specified number of offsets, all of the partition’s offsets will be batched into the destination table.This variable applies only to Kafka pipelines.It can be overridden for a single pipeline by using the`ALTER PIPELINE SET MAX_OFFSETS_PER_BATCH_PARTITION`command, or the`CREATE PIPELINE ... MAX_OFFSETS_PER_BATCH_PARTITION = `command.This variable can sync to all aggregators. | `1000000` | | `pipelines_max_pooled_extractors` | Max pooled extractors for Kafka pipelines. The value must be set to at least`1`. This variable can sync to all nodes, including aggregator and leaf nodes. | `256` | | `pipelines_max_retries_per_batch_partition` | The number of retry attempts for writing batch partition data to the destination table. If`pipelines_stop_on_error`is set to`OFF`and the specified retry number is reached without success, the batch partition will be skipped and will not appear in the destination table. If a batch partition is skipped, data loss can occur. If`pipelines_stop_on_error`is set to`ON`and the specified retry number is reached without success, the pipeline will stop. No batch partition data will be skipped. This configuration variable applies to the entire batch transaction, which includes extraction from a data source, optional transformation, and loading of the data into the destination table. If the batch transaction fails at any point during extraction, transformation, or loading, it will be retried up to the specified number.This variable can sync to all aggregators. | `4` | | `pipelines_offsets_gc_skew_time_minutes` | Amount of time in minutes allowed for pipeline metadata timestamp skew. The minimum value for this engine variable is`1`. This variable can sync to all aggregators. Refer to[Pipeline Using Metadata Garbage Collection (GC)](https://docs.singlestore.com/db/v9.1/reference/sql-reference/pipelines-commands/create-pipeline/#section-id235172265956434.md)for more information. | `1440` | | `pipelines_parse_errors_threshold` | 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`pipelines_stop_on_error`variable is set to true or false. This variable can sync to all aggregators.Set the variable with an integer value. Disable = 0 | 0 | | `pipelines_pooled_extractor_batches` | Max number of batches for a pooled extractor for Kafka pipelines. The value must be set to between`1`and`2048`. This variable can sync to all nodes, including aggregator and leaf nodes. | `256` | | `pipelines_stderr_bufsize` | The buffer size for standard error output (in bytes). Error messages that exceed this size will be truncated when written to the`information_schema.PIPELINES_ERRORS`table. However, the complete standard error text can be viewed by using the`BATCH_ID`and querying the`information_schema.PIPELINES_BATCHES`table.This variable can sync to all nodes, including aggregator and leaf nodes. | `65535` | | `pipelines_stop_on_error` | Specifies whether or not each pipeline in theclustershould stop when an error occurs. If set to`OFF`, batches will be retried up to the number specified in the`pipelines_max_retries_per_batch_partition`variable. After all retries have failed, the batch will be skipped. When a batch is skipped, data loss can occur. If set to`ON`, the batch transaction that caused the error will be retried up to the number specified in the`pipelines_max_retries_per_batch_partition`variable. After all retries have failed, the pipeline will enter a`Stopped`state and must be manually started.This variable can sync to all aggregators. | `ON` | | `pipelines_stored_proc_exactly_once` | If set to`ON`, run stored procedures from pipelines in a transaction. If this variable is OFF and there is a stored procedure attached to a pipeline, it may only partially succeed. If the procedure fails only the data that was written will remain, instead of rolling back on failure.This variable can sync to all aggregators. | `ON` | ## Unlimited Storage Database (Bottomless) and Point-in-Time Restore Variables | Name | Description | Default | | ------------------------------------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | --------- | | `blob_cache_threadpool_max_concurrent` | Controls the maximum number of threads available for downloading blobs from unlimited storage. This variable can sync to all nodes, including aggregator and leaf nodes. | `32` | | `bottomless_api_request_timeout_ms` | Sets the timeout, in milliseconds, for CURL HTTP requests made by the unlimited storage cloud API (AWS/Azure). This variable can sync to all nodes, including aggregator and leaf nodes. | `600000` | | `bottomless_cloud_debug_tracing` | Turns on full debug tracing in an unlimited storage cloud API (AWS/Azure). This variable can sync to all nodes, including aggregator and leaf nodes. | `OFF` | | `bottomless_experimental_blobstore_mode` | When this is set to`TRUE`, additional verification would be done to ensure correctness of the blob store. This variable can sync to all nodes, including aggregator and leaf nodes. | `FALSE` | | `bottomless_gc_retention_period_minutes` | Specifies the amount of time, in minutes, a restore point for a remote storage database is retained after it is created. This determines how far back in time you can do a PITR. For example, to configure retention for 90 days, you should set the value to 90\*24\*60 = 129600 minutes.This variable can sync to all nodes, including aggregator and leaf nodes. | `1440` | | `bottomless_idle_upload_ms` | Number of milliseconds elapsed between unlimited storage uploads. After which, unlimited storage will upload what's queued. This variable can sync to all nodes, including aggregator and leaf nodes. | `60000` | | `bottomless_incremental_download_max_buffer_size` | Maximum size (in bytes) an unlimited storage download's buffer can grow to. This variable can sync to all nodes, including aggregator and leaf nodes. | `1048576` | | `bottomless_skip_create_database_api_checks` | Skips API checks when creating a new bottomless database. This variable can sync to all nodes, including aggregator and leaf nodes. | `FALSE` | | `bottomless_snapshot_trigger_log_chunks` | Determines how often snapshot files are uploaded to the object store. A higher value will cause snapshot files to be uploaded less frequently. Snapshot files are used during recovery. For example, copying bucket files in an object store to a new location and then running`ATTACH`at the new location will trigger recovery.This variable can sync to all nodes, including aggregator and leaf nodes. | `300` | | `bottomless_upload_max_concurrent` | A higher value can increase the concurrency of uploading data updates made on theclusterto the object store. A higher value will increase the CPU and memory usage, and potentially the network usage on thecluster.This variable can sync to all nodes, including aggregator and leaf nodes. | `8` | | `bottomless_upload_throttle_hard_limit_cache_usability` | The usability (free space + evictable space) of blob cache below which all columnstore ingest is throttled. | `.05` | | `bottomless_upload_throttle_hard_limit_secs` | Amount of lag (in seconds) after which all columnstore ingest is throttled, subject to`bottomless_upload_throttle_min_disk_limit_mb`.This variable can sync to all nodes, including aggregator and leaf nodes. | `1800` | | `bottomless_upload_throttle_min_disk_limit_mb` | Minimum disk space when an unlimited storage columnstore ingest kicks in. A value of`0`means there is no minimum and ingest is throttled subject to the hard and soft time limits.This variable can sync to all nodes, including aggregator and leaf nodes. | `0` | | `bottomless_upload_throttle_soft_limit_cache_usability` | The usability (free space + evictable space) of blob cache below which some columnstore ingest is throttled. | `.1` | | `bottomless_upload_throttle_soft_limit_secs` | Amount of lag (in seconds) after which some columnstore ingest may be throttled, subject to`bottomless_upload_throttle_min_disk_limit_mb`. This engine variable has no effect if the value is higher than the value set for`bottomless_upload_throttle_hard_limit_secs`.This variable can sync to all nodes, including aggregator and leaf nodes. | `600` | ## Wasm Sync Variables | Name | Description | Default | | ------------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ---------------------------------- | | `enable_wasm` | Specifies if a user can create or call Wasm UDFs/TVFs.

📝 Note:

enable_wasm is a non-sync variable. It is listed with the Wasm sync variables for convenience sake.

| `ON` | | `wasm_alloc_max_flex_size` | Specifies the maximum memory (in bytes) that can be allocated to a Wasm module directly from the operating system  in case of emergencies. This variable can sync to all nodes, including aggregator and leaf nodes. | `2097152`(2MB) | | `wasm_alloc_retry_count` | Specifies the maximum number of timesSingleStoreretries memory allocation if an out-of-memory error occurs in the Wasm runtime. This variable can sync to all nodes, including aggregator and leaf nodes. | `3` | | `wasm_alloc_retry_interval` | Specifies the time (in milliseconds) beforeSingleStoreretries memory allocation if an out-of-memory error occurs in the Wasm runtime. This variable can sync to all nodes, including aggregator and leaf nodes. | `1000` | | `wasm_create_from_url` | Enables creation of Wasm functions from a remote URL. Refer to[Wasm UDF Sources](https://docs.singlestore.com/db/v9.1/reference/code-engine-powered-by-wasm/create-wasm-udfs/#section-idm4582794702529633102330989475.md)for more information. This variable can sync to all aggregators. | `ON` | | `wasm_max_compiled_module_size` | Specifies the maximum size (in bytes) that a compiled Wasm module can use. This variable can sync to all aggregators. | `26214400`(25MB) | | `wasm_max_image_cache_size` | Specifies the maximum size (in bytes) that is available for caching Wasm query object code images. This variable can sync to all nodes, including aggregator and leaf nodes. | `26214400`(25MB) | | `wasm_max_linear_memory_size` | Specifies the maximum linear memory (in bytes) that an individual Wasm module can use. This will further constrain UDF/TVF-specific`GROW TO`values. This variable can sync to all nodes, including aggregator and leaf nodes. | `16777216`(16MB or 256 Wasm pages) | | `wasm_max_raw_module_size` | Specifies the maximum size (in bytes) of Wasm modules that may be loaded. The size is defined as the size of raw, uncompiled data passed in the`CREATE FUNCTION`statement. This variable can sync to all aggregators. | `26214400`(25MB) | ## Workload Management Sync Variables | Name | Description | Default | | ------------------------------------------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ------- | | `resource_usage_model` | Whether to train or enable the resource usage model for workload management. When set to`ON`, workload management will estimate memory usage and queue queries that are estimated to use too much. When set to`TRAINONLY`,[PROFILE](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-manipulation-language-dml/profile.md)can be run on queries to compare the estimated memory usage of queries with their actual use. This is recommended when upgrading from any version prior to 6.7, to allow for the workload manager to do the necessary machine learning for accurate estimates. Once estimated memory use in`PROFILE`begins matching actual use, consider setting this variable to`ON`.This variable can sync to all aggregators. | `OFF` | | `workload_management` | Specifies whether to enable workload management for thecluster. If this variable is set to`ON`, the other workload management engine variables will affect the way a query is executed. If set to`OFF`, the feature is disabled and no queueing or system resource optimization will occur.This variable can sync to all aggregators. | `ON` | | `workload_management_available_leaf_memory_ratio` | Sets the ratio of the available leaf memory for workload\_management to use when queuing queries. Valid ranges are \[0.0 to 1.0]. The variable sets the ratio of the total amount of memory workload management uses when deciding to queue queries. For example, if it is set 0.9, thenSingleStorewill assume there is only 90% of the total leaf memory available, and will end up running fewer queries before hitting the limit and needing to queue. This variable can sync to all aggregators. | `1.0` | | `workload_management_dynamic_resource_allocation` | Enables/disables the dynamic reuse of WM queues feature. This variable can sync to all aggregators. | `OFF` | | `workload_management_enable_large_memory_classification` | When enabled (set to`ON`), either memory usage or connections/threads usage can cause a query to be classified as large.When set to`OFF`, only connections/threads usage can classify a query as large.This variable can sync to all aggregators. | `TRUE` | | `workload_management_enable_static_partitioning` | Enables static resource partitioning for deterministic behavior by workload management. This variable can sync to all aggregators. | `FALSE` | | `workload_management_expected_aggregators` | The expected number of query aggregators (routers) that will be used to run a high volume of fully distributed queries in thiscluster.Workload Management uses this value to divide leaf resources (threads, connections, memory) across those aggregators when deciding whether to queue a query. | `0` | | `workload_management_max_connections_per_leaf` | The maximum number of connections to use per leaf node in thecluster. This variable can sync to all aggregators. | `10000` | | `workload_management_max_queue_depth` | The maximum depth of the query queue, which is the maximum number of queries that can be queued. If this number is reached, additional queries will not execute, and an`ER_TOO_MANY_QUEUED_QUERIES`error will appear.This variable can sync to all aggregators. | `100` | | `workload_management_max_threads_per_leaf` | The maximum number of threads to use per leaf. This number correlates with the`max_connection_threads`engine variable, and they should generally be set to the same value. This variable can sync to all aggregators. | `8192` | | `workload_management_memory_queue_threshold` | Percentage of memory a query can use before it will get queued. If an individual query is projected to use more than`workload_management_memory_queue_threshold * (leaf_maximum_memory - leaf_current_table_memory) / workload_management_expected_aggregators`, then it will be queued.This variable can sync to all aggregators. | `0.01` | | `workload_management_memory_queuing` | Whether to turn on queuing based on memory usage of queries. This variable can sync to all aggregators. | `ON` | | `workload_management_queue_size_allow_upgrade` | 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 variable can sync to all aggregators. | `1` | | `workload_management_queue_time_warning_ratio` | Specifies when a warning will appear based on the ratio of time spent by a query in the queue versus the actual execution time of the query. For example, if a query waits in the queue for one second and it takes four seconds to execute, the ratio is 1:4 or 0.25. Once the specified ratio is reached for a query, a`ER_QUERY_QUEUED_WARNING`warning will appear.This variable can sync to all aggregators. | `0.5` | | `workload_management_queue_timeout` | The time duration (in seconds) after which a query times out and is removed from the queue without being executed.This variable can sync to all aggregators. | `3600` | | `workload_management_min_sample_based_memory_estimation_runs` | Minimum number of runs for workload management to use sample based rather than static row size based memory estimation. When disabled (set to 0), the first time query run will not be managed by workload management based on its memory.This variable can sync to all aggregators.This engine variable is configurable onSingleStore Helios | 1 | ## DR Replication Sync Variables SingleStore recommends contacting [Support](https://support.singlestore.com/) before changing the values of these variables. | Name | Description | Default | | ----------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ------- | | `repl_page_desc_limit_before_stall` | This limits the number of page descriptors in the page map that a 'ReplLog' can use before stalling new txs. The default 256K page desciptor limit means the hash table will not have load greater than 4. | 256K | | `repl_io_buffer_limit` | This limits the number of IO buffers that a 'ReplLog' can use before stalling new txs. 128 IO buffers is 16MB, since each IO buffer is 128K. | 16MB | | `[dr_]repl_network_buffer_size` | Replication tries to set both send and receive buffer sizes to reduce waits on TCP ack packets. 8MB is a sane battle-tested value. Can be set for both DR and non-DR replicas. | 8MB | | `[dr_]repl_max_chunk_size` | When sending logs or snapshots to a replica, limits chunk size to this value. Too long of a chunk increases the chance of timeout waiting for the replica's response packet (since the replica must process the entire chunk before acking). This defaults to 8MB - a sane, battle-tested value. Maximum value is 1GB, and it must be divisible by 128KB, so it's a multiple of IO buffer size. Can be set for both DR and non-DR replicas. | 8MB | ## Non-Sync Variables List See the [engine variables overview](https://docs.singlestore.com/db/v9.1/reference/configuration-reference/engine-variables.md) to learn how non-sync variables work. > **📝 Note**: All of the non-sync variables listed in the following table are global variables, except when noted as a “session variable that can also be set globally” in the “Description” column. The variables listed in the following tables can be set to take effect on node startup and can be set to take effect while the node is running. Exceptions are noted in the fourth column. ## Basic Variables | Name | Description | Default | Exceptions to When Variable can be Set | | ------------------------------------------------ | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `allow_ipv6` | Whether to enable IPv6 support.When set to false, theSingleStoreengine can only use an IPv4 network.When set to true,SingleStorecan use either an IPv4 or IPv6 network.The actual network interface to use is determined by the`bind_address`engine variable (below). | `FALSE` | You can only set this variable while the node is offline. The changes to this variable will take effect on the next start of the node.**Note**: If`allow_ipv6`is false and`bind_address`is in IPv6 format, memsqld will produce an error message and refuse to start | | `bind_address` | Defines the IP address to use to listen for incoming connections.Used in conjunction with the`allow_ipv6`engine variable (above).When`allow_ipv6`is set to true, this variable can be set to an IPv6 address (for example, ::), and will switchSingleStoreto an IPv6 mode.When`allow_ipv6`is set to false, this variable can only be set to an IPv4 address (for example, 0.0.0.0).If the address is 0.0.0.0(IPv4) or :: (IPv6),SingleStoreaccepts connections on all network interfaces; otherwise, it only accepts connections for the given IP address. | `0.0.0.0` | You can only set this variable while the node is offline. The changes to this variable will take effect on the next start of the node. | | `datadir` | Directory path for the data directory. This directory contains snapshots, logs, and columnstore segments. | | You can only set this variable while the node is offline. The changes to this variable will take effect on the next start of the node. | | `distributed_plancache_agg_sync_s` | Specifies the interval between aggregators' periodic synchronizations for the distributed plancache.Value must be >= 30. | `600` | | | `distributed_plancache_worker_threads` | Specifies the number of threads used to process tasks in the task queue for the distributed plancache.Value must be between 1 and 32 | `8` | | | `div_precision_increment` | The number of digits by which to increase the scale of division results performed with the division (/) operator. | `4` | You can set this variable but it is currently not supported inSingleStore.SingleStorealways returns the results to the fourth precision value. | | `enable_periodic_distributed_plancache_agg_sync` | Specifies if aggregators periodically synchronize their plancache with the distributed plancache. | `FALSE` | | | `flush_before_replicate` | If`ON`, data is written and flushed to disk locally before being replicated. This will increase replication latency but will guarantee that replicas will not flush data to disk before the master does. | `OFF` | You can only set this variable while the node is offline. The changes to this variable will take effect on the next start of the node. | | `java_path` | If set, specifies the path to the Java binary. Java Runtime Environment 25+ (JRE 25+) is required. Example: “/usr/bin/java”.Is used for CDC pipelines and full-text search version 2.Requres restarting the full-text version 2 service with`FULLTEXT SERVICE STOP`to take effect.**Not configurable onSingleStore Helios**. | None | | | `master_aggregator` | Appending`master_aggregator`(with no arguments) to the end of a node’s**memsql.cnf**specifies that node as thecluster’s master aggregator. For more information on this variable, see[Master Aggregator](https://docs.singlestore.com/db/v9.1/introduction/distributed-architecture/cluster-components.md). | | You can only set this variable while the node is offline. The changes to this variable will take effect on the next start of the node. | | `max_execution_time` | This variable is unused and setting this variable has no effect. It exists so certain MySQL-compatible apps that have this variable set will not fail when connected toSingleStore. | `0` | | | `maximum_blob_cache_size_mb` | Specifies the maximum blob cache size per leaf node. The cache stores recently accessed columnstore data objects and is located on thecluster's local storage. The cache exists in`datadir`. The cache size is limited by available space in`datadir`.`maximum_blob_cache_size_percent`setting overrides this variable. | **Version <=7.9**For disk range 0-40GB, cache value is 100% of disk.For disk range 40GB+, cache value is 80% of disk.**Version =>8.0 and and < 8.9.24**For disk range 0-40GB, cache value is 100% of disk.For disk range 40-140GB, cache value is 40GB.For disk range 140-666GB, cache value is 75% of disk.For disk range 667GB+, cache value is 90% of disk - 100GB.**Version >= 8.9.24**For disk range 0-400GB, cache value is 75%.For disk range 400GB+, value is set to 90% of total disk size - 100GB. | | | `maximum_memory` | Maximum memorySingleStorewill use, in MB.**WARNING**: Do not set it to more than the default. | 90% of System RAM or System RAM minus 10 GB, whichever is greater | You are required to restart a node only when the variable is being reduced. | | `maximum_table_memory` | Maximum memorySingleStorewill use for table storage, in MB.**WARNING**: Do not set it to more than the default. | If`maximum_memory`>= 10 GB, then 90% of`maximum_memory`. Else if`maximum_memory`>= 1.25 GB, then`maximum_memory`- 1 GB | You are required to restart a node only when the variable is being reduced. | | `port` | SingleStoreport number. | `3306` | You can only set this variable while the node is offline. The changes to this variable will take effect on the next start of the node. | | `reported_hostname` | The hostname or IP of the machine in use. | | | | `plancachedir` | Directory path for the plancache directory. This directory contains compiled plans used for codegen. | `./plancache` | You can only set this variable while the node is offline. The changes to this variable will take effect on the next start of the node. | | `tracelogsdir` | Directory path for the tracelogs directory. This directory contains log files, including memsql.log and the query log. | `./tracelogs` | You can only set this variable while the node is offline. The changes to this variable will take effect on the next start of the node. | | `auditlog_root_only` | Log queries only for the root user.When set to`TRUE`, audit logging for every other user will be set to`FALSE`, and the changes to`auditlog_level`will only apply to the root user. | `FALSE` | You can set this variable only at startup. | ## Connection Management Variables | Name | Description | Default | Exceptions to When Variable can be Set | | --------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | -------- | -------------------------------------------------------------------------------------------------------------------------------------- | | `connect_timeout` | The number of seconds the node’sSingleStorewaits for a connection packet before sending back a`Bad handshake`response. | `10` | | | `max_connections` | The number of simultaneous clients allowed. For more, see[In-Depth Variable Definitions](https://docs.singlestore.com/db/v9.1/reference/configuration-reference/engine-variables/list-of-engine-variables/#in-depth-variable-definitions-9.md). | `100000` | | | `max_connection_threads` | The maximum number of kernel threads for processing queries. For more, see[In-Depth Variable Definitions](https://docs.singlestore.com/db/v9.1/reference/configuration-reference/engine-variables/list-of-engine-variables/#in-depth-variable-definitions-10.md). | `8192` | | | `max_dedicated_admin_connections` | Specifies the number of connections reserved for admin users. This is the number of connections over and above the`max_connections`. Its purpose is to allow admin users to login even if`max_connections`number of non-admin users are already logged in. For example, if`max_connections`is set to`100`and`max_dedicated_admin_connections`is set to`5`, then even if 100 non-admin user connections are active at any time, 5 admin users can still log in. This is to prevent administrative users from being locked out of the system during heavy traffic.You connect via a socket file, for example:`mysql -u root --socket ‘/home/xyz/memsql/bincache/cluster_10000/10000/data/memsql.sock'`instead of using`mysql -u root -h --port `Refer to the[Dedicated Admin Connections](https://docs.singlestore.com/db/v9.1/user-and-cluster-administration/dedicated-admin-connections.md)page for how to create dedicated admin connections. | `5` | | | `max_execution_time` | This variable exists only for backward compatibility with MySQL. | | | | `max_sys_mem_usage_for_load_data` | Sets the percentage of`maximum_memory`available for allocation to the`load_data_read_size`and`load_data_write_size`variables. This variable limits the memory usage by`LOAD DATA`read/write operations. | `20` | | | `skip_name_resolve` | Controls whether to perform name resolution. By default,`AUTO`will only perform a reverse Domain Name System (DNS) lookup if there are any host-based security rules. Options include`AUTO`,`OFF`, or`ON`. For more information on this variable, see[In-Depth Variable Definitions](https://docs.singlestore.com/db/v9.1/reference/configuration-reference/engine-variables/list-of-engine-variables/#in-depth-variable-definitions-11.md). | `AUTO` | You can only set this variable while the node is offline. The changes to this variable will take effect on the next start of the node. | ## Database Optimization Variables | Name | Description | Default | Exceptions to When Variable can be Set | | --------------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | --------- | -------------------------------------- | | `columnar_segment_rows` | Deprecated alias to`columnstore_segment_rows`. | `1024000` | | | `columnstore_window_size` | This variable has been deprecated and is no longer operational inSingleStore. | | | | `enable_idle_table_optimizations` | `enable_idle_table_optimizations`needs to be set at runtime and requires a restart for changes to take effect. It can be set to OFF or ON (default). The other related variable,`enable_idle_table_eviction`, can be set during a session (though a very small amount of overhead will remain until the server is restarted) and can be set to Full, SkipListsOnly, and Off. It defaults to SkipListsOnly, which meansSingleStorewill only evict skiplist indexes for idle tables on the cluster. Full means it will evict skiplists and table modules, and Off means no eviction. | `ON` | | | `enable_sort_on_load` | Whether to merge the newly loaded data segments (for`LOAD DATA`,`INSERT SELECT`, and`PIPELINE`statements) into a single sorted run. | `ON` | | | `net_first_packet_read_timeout` | Number of seconds an online failover will wait to drain open idle write transactions before issuing a kill to those transactions. After the idle write transactions are killed, an application-level retry is required. | `30` | | | `net_read_timeout` | Number of seconds to wait for more data from a connection before aborting the read.This is a session variable that can also be set globally. | `3600` | | | `net_write_timeout` | Number of seconds to wait for a block to be written to a connection before aborting the write.This is a session variable that can also be set globally. | `3600` | | | `optimize_columnar_tables` | This variable has been deprecated and is no longer operational inSingleStore. | | | | `transaction_buffer` | This variable has been deprecated and is no longer operational inSingleStore. | | | ## Full-Text Search Non-Sync Variables | Name | Description | Default | Exceptions to When Variable can be Set | | ---------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ------- | -------------------------------------- | | `fts2_java_home` | If set, provides the JAVA\_HOME for the Full-Text Search service.**Not configurable onSingleStore Helios.**Requres restarting the full-text version 2 service with`FULLTEXT SERVICE STOP`to take effect. | None | | | `fts2_java_path` | Deprecated. Is superceded by`java_path`. | None | | ## Logging Variables | Name | Description | Default | Exceptions to When Variable can be Set | | ------------------------ | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | --------------- | -------------------------------------- | | `core_file` | Turning`core_file`on or off determines whether or not full core dumps are produced upon a crash. | `ON` | | | `critical_diagnostics` | Sends usage and critical error diagnostics toSingleStore. Disabling`critical_diagnostics`also disables`send_usage_telemetry`.Refer to[Telemetry and Diagnostics](https://docs.singlestore.com/db/v9.1/reference/configuration-reference/engine-variables/list-of-engine-variables/#in-depth-variable-definitions-12.md)for more information. | `OFF` | | | `general_log` | If set to`ON`, every query will be logged to a table or log file. If set to`PARTIAL`, will only log when load is light.`OFF`turns off logging. | `OFF` | | | `general_log_file` | Log connections and queries to a given file. | `'./query.log'` | | | `tracelog_rotation_size` | Indicates the size threshold (in bytes) for tracelog file rotation. | | 65536> | | `tracelog_rotation_time` | Indicates the time threshold (in seconds) for tracelog file rotation. | | 60> | | `warn_level` | Defines howSingleStorebehaves when it encounters unsupported functionality. For more information, visit the Unsupported Feature List section of the[Unsupported MySQL Features](https://docs.singlestore.com/db/v9.1/connect-to-singlestore/connect-with-mysql/connect-with-mysql-client/unsupported-mysql-features.md)topic. | `WARNINGS` | | ## Password Complexity Non-Sync Variables | Name | Description | Default | Exceptions to When Variable can be Set | | ----------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ----------- | -------------------------------------- | | `expire_root_password` | Specifies whether the root password can expire. If set to`ON`, the root password will expire after the`password_expiration_seconds`duration is reached. | `OFF` | | | `password_expiration_seconds` | The time (in seconds) before a password expires. The value`0`indicates that the password will never expire. | `0` | | | `password_expiration_mode` | Specifies what happens when a user's password expires. If set to`NO_ACCESS`, the user cannot login after the password expires. If set to`LIMITED_ACCESS`, the user can login after the password expires but can only update their password using`ALTER USER`or`SET PASSWORD`commands. Until the password is updated, no other commands can be executed except`SET SESSION`or`SET LOCAL`. | `NO_ACCESS` | | | `password_history_count` | The number of previous passwords per user thatSingleStorewill store and disallow from reuse. The value`0`indicates that any previous password can be reused. The maximum is`10`. | `0` | | ## Pipelines Non-Sync Variables You cannot set a variable for a specific pipeline – each variable setting applies to all pipelines in the cluster. | Name | Description | Default Setting | Exceptions to When Variable can be Set | | ---------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | --------------- | -------------------------------------------------------------------------------------------------------------------------------------- | | `java_pipelines_java_home` | Specifies the value thatSingleStoresets the node’s operating system environment variable`JAVA_HOME`to. If`java_pipelines_java_home`is not set,SingleStoredoes not set`JAVA_HOME`. | | You can only set this variable while the node is offline. The changes to this variable will take effect on the next start of the node. | | `java_pipelines_java_path` | Specifies the path that the HDFS extractor uses to run Java. | | | | `java_pipelines_java11_path` | Specifies the path of the (JRE 11+)`java`binary. Example: “/usr/bin/java”.Is used for Iceberg, CDC pipelines.For HDFS pipelines, use the`java_pipelines_java_path`engine variable instead. | | | ## Query Plan Pinning Variables | Name | Description | Default | Exceptions to When Variable can be Set | | --------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | ------- | -------------------------------------- | | `pin_all_plans` | Writes a query plan pins when an un-planned pin is first optimized. The available values are`AUTO`,`OFF`,`ACTIVATED`, and`DEACTIVATED`. If set to`ACTIVATED`, all new plans are pinned in the activated state. If set to`DEACTIVATED`, all new plans are pinned in the deactivated state. If set to`OFF`, no plans are pinned automatically.`AUTO`is the same as`ACTIVATED`. | `OFF` | | | `plan_pin_read_timeout_ms` | 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 behavior would become relevant if the cluster hosting the plan pins became severely overloaded or experienced networking issues. For commands that need to read pins by explicit user action -`ACTIVATE PIN`,`DEACTIVATE PIN`- usual RPC timeouts are used instead. | `2000` | | | `plan_pin_write_timeout_ms` | This is used to enforce a maximum latency penalty that can be incurred when the server writes a plan pin during compilation. This is only relevant for the case when a new query is being compiled without the presence of a pin, and defines the worst-case additional latency when`pin_all_plans`is set to`ACTIVATED`. For commands that need to write pins by explicit user action -`ACTIVATE PIN`,`DEACTIVATE PIN`- usual RPC timeouts are used instead. | `2000` | | | `use_plan_pins` | Loads query plan pins if one is found. The available values are`AUTO`,`TRUE`, and`FALSE`. When set to`TRUE`, if the`plan_pin_id`is not found in memory or on disk, the optimizer will search for the plan in the pinning database. If set to`FALSE`, the optimizer will not look in the plan\_pin\_id in the pinning database.`AUTO`is the same as`TRUE`. | `OFF` | | ## Security Variables | Name | Description | Default | Exceptions to When Variable can be Set | | --------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | ------------------------------------------------------------------------------------------ | -------------------------------------------------------------------------------------------------------------------------------------- | | `node_replication_ssl_only` | When this variable is set to`ON`(assuming that SSL is enabled), SSL is used for cross-clusterreplication, but not for intra-clustercommunication. When the variable is`OFF`, SSL is used for both cross-clusteras well as intra-clusterreplication. | `OFF` | You can only set this variable while the node is offline. The changes to this variable will take effect on the next start of the node. | | `ssl_ca` | CA file to be used for SSL connections. | | You can only set this variable while the node is offline. The changes to this variable will take effect on the next start of the node. | | `ssl_capath` | CA directory to be used for SSL connections. | | You can only set this variable while the node is offline. The changes to this variable will take effect on the next start of the node. | | `ssl_cert` | Certificate file to be used for SSL connections. | | You can only set this variable while the node is offline. The changes to this variable will take effect on the next start of the node. | | `ssl_cipher` | Cipher to be used for SSL/TLS connections.You must manually enumerate all desired cipher suites.For example:ssl\_cipher  = DHE-RSA-AES128-GCM-SHA256:DHE-RSA-AES256-GCM-SHA384:ECDHE-ECDSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES256-GCM-SHA384:ECDHE-RSA-AES128-GCM-SHA256:ECDHE-RSA-AES256-GCM-SHA384This will enable only the enumerated cipher suites in both theSingleStoreengine and websocket proxy. | | You can only set this variable while the node is offline. The changes to this variable will take effect on the next start of the node. | | `ssl_fips_mode` | When this variable is set to`ON`, FIPS mode is enabled. | `OFF` | You can only set this variable while the node is offline. The changes to this variable will take effect on the next start of the node. | | `ssl_key` | Public-private key pair file to be used for SSL connections. | | You can only set this variable while the node is offline. The changes to this variable will take effect on the next start of the node. | | `ssl_key_passphrase` | Passphrase for encrypted`ssl_key`. | | You can only set this variable while the node is offline. The changes to this variable will take effect on the next start of the node. | | `ssl_last_reload_attempt_time` | Contains the sequence number of reload attempts. | | | | `ssl_last_successful_reload_time` | Contains the date/time of the last successful reload. | | | | `ssl_system_capath` | Defines the system path to the certificate directory which is used to verify the peer. | The value is autodetected on system startup. Is set to NULL if a system path is not found. | | | `ssl_ca_for_client_cert` | Specifies which Certificate Authority (CA) should be used to verify client certificates when establishing a secure connection using mTLS. It references the correct CA for client certificate verification, separating it from the CA used for server-side connections. | | | ## Other Variables | Name | Description | Default | Exceptions to When Variable can be Set | | ------------------------------------------------ | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ------------------------------------- | -------------------------------------------------------------------------------------------------------------------------------------- | | `allow_unsupported_filesystem` | Enables support for filesystems without`fallocate`support. | `FALSE` | | | `activity_tracking_id` | An ID that can be used to identify the activities associated with a session. Configure the`activity_tracking_id`session engine variable to specify an ID for all the activities associated with the queries run in the current session. | | | | `activities_delta_sleep_s` | `activities_delta_sleep_s`specifies an interval of time (in seconds) which is used by the`mv_activities_cumulative`variable to determine recent resource usage. For more information, see[Management View Reference](https://docs.singlestore.com/db/v9.1/reference/information-schema-reference/management.md).This is a session variable that can also be set globally. | `1` | | | `auditlogsdir` | Specifies the local or network directory to write log files.For more information on this variable, see[Configure Audit Logging](https://docs.singlestore.com/db/v9.1/security/audit-logging/configuring-audit-logging.md). | `//auditlogs` | You can only set this variable while the node is offline. The changes to this variable will take effect on the next start of the node. | | `auditlog_level` | `auditlog_level`is used to specify the level of logging in a node. There are 11 logging levels.For more information on this variable, refer to[Audit Logging Levels](https://docs.singlestore.com/db/v9.1/security/audit-logging/audit-logging-levels.md). | `OFF` | You can only set this variable while the node is offline. The changes to this variable will take effect on the next start of the node. | | `auditlog_rotation_size` | Specifies the maximum size per log file (in bytes). Required if logging is enabled.For more information on this variable, see[Configure Audit Logging](https://docs.singlestore.com/db/v9.1/security/audit-logging/configuring-audit-logging.md). | `134217728` | You can only set this variable while the node is offline. The changes to this variable will take effect on the next start of the node. | | `auditlog_rotation_time` | Specifies the maximum time duration to write to a single log file (in seconds).For more information on this variable, see[Configure Audit Logging](https://docs.singlestore.com/db/v9.1/security/audit-logging/configuring-audit-logging.md). | `3600` | You can only set this variable while the node is offline. The changes to this variable will take effect on the next start of the node. | | `autocommit` | If`ON`, individual SQL statements are run in a single transaction that commits when the statement finishes. If`OFF`, individual SQL statements run in a multi-statement transaction by default, which must be ended explicitly with`COMMIT`or`ROLLBACK`.**Note**: If`BEGIN`is used to start a transaction, it overrides the behavior of`autocommit`engine variable for this transaction, and the transaction must be ended explicitly with`COMMIT`or`ROLLBACK`. | `ON` | | | `backup_max_threads` | Specifies the maximum number of concurrent threads, per leaf, used to run a backup on each leaf. By default, a backup of a leaf uses one thread*per partition*and there is no restriction on the number of threads created (aside from system limitations).The number of threads, if set, is shared among active backups, with the first backup using as many as it needs. The remainder, if any, goes to the next backup, etc. | `0` | | | `basedir` | Specifies the path where the installation directory can be found. | | You can only set this variable while the node is offline. The changes to this variable will take effect on the next start of the node. | | `compat_version` | This variable exists for backwards compatibility with MySQL. It specifies the MySQL compatibility version. | | | | `convert_nonunique_hash_to_skiplist` | When this variable is set as`TRUE`, any non-unique hash index will be recovered as a skiplist index. Any newly created table will also have its non-unique hash indexes created as skiplists. | `TRUE` | You can only set this variable while the node is offline. The changes to this variable will take effect on the next start of the node. | | `core_file_mode` | Specifies the type of core dump to generate ifSingleStoreterminates abnormally. Options include`NONE`,`PARTIAL`, or`FULL`.`PARTIAL`omits most user data to keep the dump small, while a`FULL`core dump uses the equivalent amount of disk space as the amount of memory used bySingleStore. | `PARTIAL` | You can only set this variable while the node is offline. The changes to this variable will take effect on the next start of the node. | | `dac_ip_port` | Port number to use for Dedicated Admin Connections (DAC). | `9306` | You can only set this variable while the node is offline. The changes to this variable will take effect on the next start of the node. | | `default_time_zone` | This is a configuration variable that is used to set the time zone on a host by specifying an offset from UTC time.Refer to the[Setting the Time Zone](https://docs.singlestore.com/db/v9.1/user-and-cluster-administration/maintain-your-cluster/setting-the-time-zone.md)page for details on this variable. | | | | `enable_dac_ip_port` | If set then allows Dedicated Admin Connections (DAC) to be established at the port specified in`dac_ip_port`. | `FALSE` | You can only set this variable while the node is offline. The changes to this variable will take effect on the next start of the node. | | `enable_extended_types_metadata` | Enables the handling of extended data types that are not defined in MySQL`enum_field_types`. When set to`TRUE`,SingleStoremay send extended metadata to the client. It allows distinction between different data types and handling them appropriately in client applications. | `FALSE` | | | `enable_gc_events` | Enable reporting of garbage collection events. Gates the`MV_GC_EVENTS`information schema view that provides visibility into garbage collection passes. | `FALSE` | | | `flat_plancache` | This variable has been deprecated and is no longer operational inSingleStoreversion 5.0 or newer. | | | | `gssapi_keytab_path` | The local path to the keytab file created on the KDC.For more information on this variable, see[Kerberos Authentication](https://docs.singlestore.com/db/v9.1/security/authentication/kerberos-authentication.md). | | You can only set this variable while the node is offline. The changes to this variable will take effect on the next start of the node. | | `gssapi_principal_name` | The SPN forSingleStorethat was created on the KDC.For more information on this variable, see[Kerberos Authentication](https://docs.singlestore.com/db/v9.1/security/authentication/kerberos-authentication.md). | | You can only set this variable while the node is offline. The changes to this variable will take effect on the next start of the node. | | `hostname` | Specifies the server hostname at startup. This variable is read-only. | | | | `http_api` | EnablesSingleStore's Data API endpoints on the built-in HTTP server when used in conjunction with the`https_proxy_port`or the`http_proxy_port`variable. This variable must be set locally on each aggregator node on which the Data API needs to be run. | `ON` | | | `http_api_max_idle_seconds` | Controls how long a pooled connection can remain idle (or unused) before it will be closed.`0`means the connection will remain open.Please contact SingleStore Customer Support if you need to change this value. | `86400` | | | `http_api_max_lifetime_seconds` | Controls how long a pooled connection can remain open.`0`means the connection will remain open.Please contact SingleStore Customer Support if you need to change this value. | `0` | | | `http_api_pool_capacity` | Specifies the maximum number of connections each pool can have open at any point in time when using theSingleStore's Data API. In other words, this variable controls the number of parallel queries that can be run by aSingleStoreuser. | `2048` | | | `http_api_pool_max_idle` | Specifies the maximum number of idle connections to retain in each connection pool when using theSingleStore's Data API. Each connection pool is associated with a singleSingleStoreuser. | `1024` | | | `http_proxy_port` | Specifies the port on which the WebSocket proxy should run. This WebSocket allows Studio to connect with theSingleStoreserver over HTTP using TLS .The`http_proxy_port`variable when used in conjunction with the`http_api`variable provides access toSingleStore's Data API via the HTTP server.**Note**: This variable is mutually exclusive with the`https_proxy_port`variable. | | | | `https_proxy_port` | Specifies the port on which WebSocket proxy should run. This WebSocket allows Studio to connect with theSingleStoreserver over HTTPS using TLS. It requires SSL to be enabled.The`https_proxy_port`variable when used in conjunction with the`http_api`variable provides access toSingleStore's Data API via the HTTPS server.**Note**: This variable is mutually exclusive with the`http_proxy_port`variable. | | | | `idle_thread_lifetime_seconds` | Idle threads terminate after the specified number of seconds. | `3600` | | | `identity` | Contains the value of`last_insert_id`.This is a session variable that can also be set globally. | | You cannot set this variable while the node is offline. | | `internal_columnstore_validate_blob_after_write` | If set to`ON`, the checksum of a blob is verified immediately after it is created. | `OFF` | | | `internal_columnstore_validate_blob_before_read` | If set to`ON`, the checksum of a blob is verified before it is read. | `OFF` | | | `interpreter_mode` | Controls whetherSingleStorecompiles or interprets query plans.When compiling, this variable also controls how the query plans are compiled.Allowed settings include:`llvm`,`mbc`,`compile`,`compile_lite`,`compile_lite_first`,`interpret`,`interpret_first`.This is a session variable that can also be set globally.See[Code Generation](https://docs.singlestore.com/db/v9.1/query-data/advanced-query-topics/code-generation/#UUID-66438a95-e25e-c75a-fa53-32e256194c5d.md)for more details. | `interpret_first` | | | `json_agg_max_len` | Controls the maximum length (in bytes) of[JSON\_AGG](https://docs.singlestore.com/db/v9.1/reference/sql-reference/json-functions/json-agg.md)output. It can be set to a minimum of 1 MB (in bytes).This is a session variable that can also be set globally. | `16777216` | | | `kerberos_server_keytab` | This variable has been deprecated and is no longer operational inSingleStore. | | | | `lc_messages_dir` | Specifies the directory where error messages are stored. | `/var/lib/memsql/share/` | You can only set this variable while the node is offline. The changes to this variable will take effect on the next start of the node. | | `load_data_batch_size` | This variable has been deprecated and is no longer operational inSingleStoreversion 5.0 or newer. | | | | `materialize_ctes` | Specifies the setting to materialize the common table expressions (CTEs). If set to`OFF`, it inlines the CTEs similar to view definitions. If set to`ALL`, then each CTE appearing more than once in a query is materialized into an internal result table. If set to`AUTO`, optimizations are enabled. Currently, only the predicate pushdown optimization is available.For more information, see[WITH (Common Table Expressions)](https://docs.singlestore.com/db/v9.1/query-data/advanced-query-topics/with-common-table-expressions.md). | `AUTO` | | | `memsql_id` | The ID of theSingleStorenode whose config you would like to update. This variable is read-only. | | | | `memsql_version` | SingleStoreversion number. This variable is read-only. | | | | `memsql_version_date` | The build date of theSingleStoreversion currently running. This variable is read-only. | | | | `memsql_version_hash` | SingleStoreversion hash. This variable is read-only. | | | | `minimal_disk_space` | Sets the minimal available disk space allowed (in megabytes). If the available disk space falls below`minimal_disk_space`on the disk where the data directories (such as logs, snapshots, blobs, plancache, and tracelogs) are mounted,SingleStorewill halt new write queries. | `100` | | | `net_buffer_length` | Specifies the size (in bytes) of the connection buffer and the result buffer with which each client thread starts.This is a session variable that can also be set globally. | `102400` | | | `pid_file` | The path name of the process ID file. | `'.pid'` | You can only set this variable while the node is offline. The changes to this variable will take effect on the next start of the node. | | `profile_for_debug` | This variable enables the collection of additional data with`PROFILE`that can be displayed using`SHOW PROFILE JSON`and is useful for troubleshooting query optimizer issues. For more information, see[PROFILE](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-manipulation-language-dml/profile.md).This is a session variable that can also be set globally. | `OFF` | | | `protocol_version` | Specifies the version of the client/server protocol. This variable is read-only. | `10` | | | `ref_repl_mgmt_threads` | The number of threads pooled for replication management of reference databases. Must be between 1 and 100. | `8` | | | `repl_mgmt_threads` | The number of threads pooled for replication management of partition databases. Must be between 1 and 100. | `0` | | | `resource_pool` | Specifies the resource pool to be used.This is a session variable that can also be set globally.It can be set while a node is running. It is settable on the self-managed service and gets synced across aggregators. For more information on this variable, see[Set Resource Limits](https://docs.singlestore.com/db/v9.1/user-and-cluster-administration/use-the-workload-manager-and-set-resource-limits/set-resource-limits.md). | `default_pool` | | | `rewrite_verbose` | This variable determines verbose debug tracing for rewrites. OFF: no output. ON: query before and after each rewrite; some extra tracing inside some rewrites. | `OFF` | | | `rowstore_gc_thread_count` | Number of threads running for rowstore garbage collection. Must be between 2 and 16. | `2` | | | `saml_assertion_audience` | Specifies a single audience restriction for the SAML assertion.For more information on this variable, see[Configuring SAML Global Variables](https://docs.singlestore.com/db/v9.1/security/authentication/saml-authentication/configuring-saml-global-variables.md). | | You can only set this variable while the node is offline. The changes to this variable will take effect on the next start of the node. | | `saml_message_recipient` | Specifies the intended recipient for a wrapped encryption key.For more information on this variable, see[Configuring SAML Global Variables](https://docs.singlestore.com/db/v9.1/security/authentication/saml-authentication/configuring-saml-global-variables.md). | | You can only set this variable while the node is offline. The changes to this variable will take effect on the next start of the node. | | `saml_private_decryption_key` | Specifies the file path for the private key used to decrypt an encrypted assertion.For more information on this variable, see[Configuring SAML Global Variables](https://docs.singlestore.com/db/v9.1/security/authentication/saml-authentication/configuring-saml-global-variables.md). | | You can only set this variable while the node is offline. The changes to this variable will take effect on the next start of the node. | | `saml_require_encryption` | Specifies if authentication should fail when both the SAML response and SAML assertion are unencrypted.For more information on this variable, see[Configuring SAML Global Variables](https://docs.singlestore.com/db/v9.1/security/authentication/saml-authentication/configuring-saml-global-variables.md). | `OFF` | You can only set this variable while the node is offline. The changes to this variable will take effect on the next start of the node. | | `saml_require_signature_validation` | Specifies if authentication should fail when both the SAML response and SAML assertion are unsigned.For more information on this variable, see[Configuring SAML Global Variables](https://docs.singlestore.com/db/v9.1/security/authentication/saml-authentication/configuring-saml-global-variables.md). | `OFF` | You can only set this variable while the node is offline. The changes to this variable will take effect on the next start of the node. | | `saml_use_NameID` | Specifies whether a username value should be extracted from the``element in a SAML assertion.For more information on this variable, see[Configuring SAML Global Variables](https://docs.singlestore.com/db/v9.1/security/authentication/saml-authentication/configuring-saml-global-variables.md). | `OFF` | You can only set this variable while the node is offline. The changes to this variable will take effect on the next start of the node. | | `saml_user_name_attribute` | Specifies the username attribute in a SAML assertion that should be used to determine if a user exists in the database.For more information on this variable, see[Configuring SAML Global Variables](https://docs.singlestore.com/db/v9.1/security/authentication/saml-authentication/configuring-saml-global-variables.md). | | You can only set this variable while the node is offline. The changes to this variable will take effect on the next start of the node. | | `saml_x509_certificate` | Specifies the file path for the identity provider’s public x509 signing certificate.For more information on this variable, see[Configuring SAML Global Variables](https://docs.singlestore.com/db/v9.1/security/authentication/saml-authentication/configuring-saml-global-variables.md). | | You can only set this variable while the node is offline. The changes to this variable will take effect on the next start of the node. | | `secure_file_priv` | Specifies the directory to which any import or export operations should be limited, or`unrestricted`import and export if set to`NULL`. | | You can only set this variable while the node is offline. The changes to this variable will take effect on the next start of the node. | | `scheduler_slow_loop_seconds` | Specifies the threshold for triggering the verbose logging of scheduler thread timing. It can be set to values in the range of 1 to 10, inclusive. | `5` | | | `scheduler_slow_ready_queue_seconds` | Specifies the threshold for triggering logging of slow ready queue draining. It can be set to values in the range of 1 to 15, inclusive. | `15` | | | `socket` | Specifies the Unix socket file to use for local connections. | `'memsql.sock'` | You can only set this variable while the node is offline. The changes to this variable will take effect on the next start of the node. | | `spilling_minimal_disk_space` | If the node's disk space (in MBs) is less than`spilling_minimal_disk_space`, queries that require spilling will fail instead of spilling to disk. | `500` | | | `sql_quote_show_create` | If`ON`, identifiers are quoted by the server for`SHOW CREATE`commands. This is a session variable that can also be set globally. | `ON` | | | `system_time_zone` | System time zone. This variable is read-only. | `UTC` | | | `table_precompiled_header` | This variable has been deprecated and is no longer operational inSingleStoreversion 5.0 or newer. | | | | `thread_cache_size` | Specifies how many inactive connection threads should be cached. | `0` | | | `thread_handling` | Determines how the server handles connection threads. A value of`no-threads`will cause the server to use a single thread to handle one connection.`one-thread-per-connection`causes the server to use one thread for each client connection. | `one-thread-per-connection` | You can only set this variable while the node is offline. The changes to this variable will take effect on the next start of the node. | | `thread_stack` | Specifies the stack size (in bytes) for each thread.The value must be between 131072 and 134217728,*and also*evenly divisible by 1024. | `1048576` | You can only set this variable while the node is offline. The changes to this variable will take effect on the next start of the node. | | `time_zone` | The current time zone. By default, it is set to the same value as that of`system_time_zone`. This is a session variable that can also be set globally, but it is a noop, for MySQL compatibility.Change the[time zone using your OS](https://docs.singlestore.com/db/v9.1/user-and-cluster-administration/maintain-your-cluster/setting-the-time-zone/setting-the-time-zone-on-the-host-linux-os.md)(preferred) or the[Setting the Time Zone](https://docs.singlestore.com/db/v9.1/user-and-cluster-administration/maintain-your-cluster/setting-the-time-zone.md)config variable. | `SYSTEM` | | | `timestamp` | Used to capture the original timestamp of the client. This is a session variable that can also be set globally. It can be set while a node is running, but cannot be set on node startup. | `0` | | | `tls_version` | Indicates the TLS version with which to configure the node.For more information, see[Specifying the TLS Version](https://docs.singlestore.com/db/v9.1/security/encryption/ssl-secure-connections/specifying-the-tls-version.md) | | You can only set this variable while the node is offline. The changes to this variable will take effect on the next start of the node. | | `tmpdir` | MemSQL Ops writes temporary data to`/tmp`and requires available free space. It is possible to change the temporary directory by changing`tmpdir`. | | You can only set this variable while the node is offline. The changes to this variable will take effect on the next start of the node. | | `version_compile_machine` | The type of the server binary. This variable is read-only. | `x86_64` | | | `version_compile_os` | The operating system on whichSingleStorewas built. This variable is read-only. | `Linux` | | | `websocket_proxy_stdin_conf` | Sends configuration to websocket\_proxy via STDIN instead of via command line arguments. | `TRUE` | | ## In-Depth Variable Definitions This section contains supplemental information about engine variables that require more understanding to configure properly. Ensure that you understand these details before modifying any engine variables listed in this section. ## interpreter\_mode Controls both whether and how SingleStore compiles or interprets query plans. Allowed settings include the following modes: * `llvm` or `compile`: Queries are compiled to machine code. `compile` is an alias for `llvm`. * `mbc` or `interpret` : Queries are interpreted and not compiled. `interpret` is an alias for `mbc`. * `interpret_first`: Queries start out as interpreted and dynamically switch to compiled during the first query execution. This mode can help improve ad-hoc query performance. The `interpret_first` mode is turned on by default and can be used in production deployments. This variable can also be set as a query option by adding `OPTION (interpreter_mode = { interpret | compile | interpret_first})` at the end of the query. `interpreter_mode` is non-operational on the leaf node since it is forwarded from the aggregator to the leaf. For more information on `interpreter_mode` and query compilation behavior, see [Code Generation](https://docs.singlestore.com/db/v9.1/query-data/advanced-query-topics/code-generation.md). ## json\_extract\_string\_collation `json_extract_string_collation` allows you to control the collation of `JSON_EXTRACT_STRING` and `JSON_MATCH_ANY` output. It takes the following values: * `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_bin`) is used. * Since the old `AUTO/SERVER` behavior is error-prone, existing customers should set this engine variable to `SERVER_V2` immediately after upgrading. See the support bulletin: [Using the JSON utf8mb4 Column...](https://www.singlestore.com/forum/t/use-of-json-utf8mb4-column-without-utf8mb4-server-collation-when-using-json-extract-string-can-require-cluster-restart/6323) * `server`/`auto`: Specifies that the collation result will be the same as collation of the server defined by the `collation_server` variable. Generally, this value is `utf8_general_ci`. * `SERVER_V1`: Functions the same as server/auto, in previous version older than 7.8.21 but without the parser lockdown. * `SERVER_V2`: When the `collation_server` is `utf8` and the JSON object is `utf8mb4`, the output will use `utf8mb4`. This is the default beginning with version 7.8.21 ## max\_connection\_threads `max_connection_threads` is the maximum number of kernel-level threads the SingleStore node will use to handle connections (i.e. running queries - not including background threads). Each query takes exactly one thread on the aggregator, so the `max_connection_threads` setting on an aggregator is essentially a limit on the number of queries - including internal SingleStore queries - the aggregator will run simultaneously. When the limit is reached, further queries are queued until a thread becomes available. The maximum value of `max_connection_threads` is 12192. The default for aggregators is 192, and the default for leaves is 8192. If the `max_connection_threads` limit is reached on an aggregator, queries are queued until a thread becomes available, which can potentially cause unresponsiveness, latency spikes, and failures. On the other hand, in rarer cases, too many queries running simultaneously on some workloads (such as high volume concurrent writes on larger clusters) can exhaust cluster resources. Typically, if the `max_connection_threads` limit is reached on an aggregator, increasing the limit should solve the problem. If raising the limit causes further problems on your workload, you may need to explore other avenues to resolve the root cause. ## max\_pooled\_connections `max_pooled_connections` is the maximum number of connections cached between nodes. Every connection that is opened to run a query between nodes will be left open and reused until the limit set by `max_pooled_connections` is reached. If more connections are needed to run a workload, the connections will be opened/closed as needed as the query runs. This is why running `SHOW PROCESSLIST` on a leaf that has been running a workload will show both open and idle connections. The default value is 1024, which is typically sufficient. Some heavy distributed join workloads may need more internode connections, in which case this variable can be changed. ## max\_connections `max_connections` is the maximum number of connections that can be open to a SingleStore node at one time. The default is 100,000, which is the maximum allowed. There is no reason to change the value of `max_connections` as lowering its value would not impact resource allocation. ## node\_degree\_of\_parallelism Sets the per-node degree of parallelism for applicable query operators. If the variable is set to `0` the parallelism is chosen automatically (i.e. no change from default behavior). When `node_degree_of_parallelism` is set to a value other than **0**, say **n**, then **n** processor threads are started on each leaf node to handle all the partitions on that leaf. ## optimizer\_max\_cross\_joined\_array\_filters This variable controls the conditions under which an optimizer rewrite, which improves the performance of queries with `JOIN` conditions that use `JSON_ARRAY_CONTAINS`, will be performed. An example of such a query shape is a `JOIN` condition containing `JSON_ARRAY_CONTAINS_STRING(table_one.jsonField, table_two.field)`. The rewrite in question adds a `JOIN TABLE(JSON_TO_ARRAY(table_one.jsonField)` to the query shape, with the filter modified accordingly. That is, the rewrite transforms conditions like `JSON_ARRAY_CONTAINS` into a join against a `TABLE(JSON_TO_ARRAY())` of the column that `JSON_ARRAY_CONTAINS` was filtering on. While this is a performance optimization, if multiple such conditions exist in a query, this rewrite can lead to what are effectively cross joins that increase the row count going into the join filter by the number of items in the various JSON arrays. The `optimizer_max_cross_joined_array_filters` variable limits the maximum number of unique columns across all `JSON_ARRAY_CONTAINS` conditions under which the rewrite will be performed (since the rewrite requires cross-joining all the `TABLE(JSON_TO_ARRAY())` tables). This variable defaults to `3` and the rewrite may be disabled by setting the value to `0`. ## sp\_query\_dynamic\_param ## Syntax ```sql SET GLOBAL sp_query_dynamic_param = {ON | OFF}; ``` ## Remarks When set to `ON`, `sp_query_dynamic_param` changes the plan generation behavior as follows: * Calls to a stored procedure use the same plan, regardless of whether the arguments to the procedure are `NULL` or not `NULL`. For example, the following procedure calls use the same plan: ```sql 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: ```sql CALL proc_test(NULL); CALL proc_test(NULL, 7); ``` * All occurrences of a query, inside a stored procedure, that uses variables will use the same plan, regardless of whether the variable values are `NULL` or not `NULL`. This applies when such a query appears in the stored procedure multiple times or when the query runs repeatedly via multiple calls to the stored procedure. When `sp_query_dynamic_param` is enabled, compilation time and plancache size are reduced. > **📝 Note**: The `OFF` setting of `sp_query_dynamic_param` disables the plan generation behavior described above. This setting will no longer be supported around May 2021. You should remove any dependencies on the setting by this time. ## Example: Changes in Type Conversion Behavior A query returns different results for cases where an invalid conversion is being done, depending on the value of `sp_query_dynamic_param`. For example, in the query below, **9006060** is an invalid value for `TIME` data type. If `sp_query_dynamic_param` is disabled, ```sql DELIMITER // CREATE OR REPLACE PROCEDURE sp_ex (t TIME) AS BEGIN ECHO SELECT t AS "t"; END; // DELIMITER ; CALL sp_ex(9006060); ``` ```output +-----------+ | t | +-----------+ | 838:59:59 | +-----------+ ``` If `sp_query_dynamic_param` is enabled, ```sql DELIMITER // CREATE OR REPLACE PROCEDURE sp_ex (t TIME) AS BEGIN ECHO SELECT t AS "t"; END; // DELIMITER ; CALL sp_ex(9006060); ``` ```output +----------+ | t | +----------+ | 00:00:00 | +----------+ ``` ## skip\_name\_resolve `skip_name_resolve` controls whether the server performs name resolution via DNS lookup upon login. The possible values for this variable are: * `AUTO`: By default, `AUTO` will only perform a reverse DNS lookup if there are any host-based security rules. * `OFF`: Always uses name resolution. This is not recommended without a reliable existing DNS. * `ON`: Disables DNS and never uses name resolution. The server matches only IP addresses (not host names) to the list of grants. This can improve performance for users with a slow DNS and many hosts. ## sql\_mode `sql_mode` specifies the current SQL mode (or modes) that the current session runs in. Currently, SingleStore supports the following SQL modes: * `ANSI_QUOTES`: Changes `"` to be treated as the identifier quote character (like the \` quote character) and not as a string quote character. You can still use \` as an identifier quote character with this mode enabled. With `ANSI_QUOTES` enabled, you cannot use double quotation marks to quote literal strings because they are interpreted as identifiers. * `ANSI`: Sets `sql_mode` to `STRICT_ALL_TABLES`, `ONLY_FULL_GROUP_BY`, `PIPES_AS_CONCAT`, and `ANSI_QUOTES` together. * `NO_AUTO_CREATE_USER`: This feature is deprecated, It is now enabled by default and generates an error when privileges are granted to a non-existent user. * `ONLY_FULL_GROUP_BY`: Throws an error when fields in the `SELECT` list, `HAVING` condition, or `ORDER BY` list are not in a `GROUP BY` clause, or are outside of an aggregate function. This is because the result set could include multiple possible values for the non-grouped, non-aggregated field. * `PIPES_AS_CONCAT`: Changes `||` to be treated as string concatenation operation and not as an OR operation. You can still use built-in function `CONCAT` as a string concatenation with this mode enabled. * `STRICT_ALL_TABLES`: Always enabled, regardless of other modes being enabled or not. If set to an empty value, behavior is the same as if `STRICT_ALL_TABLES` is set. ## Example The following examples show how to set `sql_mode` with one more or more values. ```sql SELECT @@sql_mode; ``` ```output +-------------------+ | @@sql_mode | +-------------------+ | STRICT_ALL_TABLES | +-------------------+ 1 row in set (0.00 sec) ``` ```sql -- Set sql_mode to include ONLY_FULL_GROUP_BY SET sql_mode = 'ONLY_FULL_GROUP_BY'; SELECT @@sql_mode; ``` ```output +--------------------------------------+ | @@sql_mode | +--------------------------------------+ | ONLY_FULL_GROUP_BY,STRICT_ALL_TABLES | +--------------------------------------+ 1 row in set (0.01 sec) ``` ```sql -- Set sql_mode to ANSI_QUOTES, which replaces ONLY_FULL_GROUP_BY SET sql_mode = 'ANSI_QUOTES'; SELECT @@sql_mode; ``` ```output +-------------------------------+ | @@sql_mode | +-------------------------------+ | ANSI_QUOTES,STRICT_ALL_TABLES | +-------------------------------+ 1 row in set (0.00 sec) ``` ```sql -- Set ONLY_FULL_GROUP_BY and ANSI_QUOTES together SET sql_mode = 'ONLY_FULL_GROUP_BY,ANSI_QUOTES'; SELECT @@sql_mode; ``` ```output +--------------------------------------------------+ | @@sql_mode | +--------------------------------------------------+ | ANSI_QUOTES,ONLY_FULL_GROUP_BY,STRICT_ALL_TABLES | +--------------------------------------------------+ 1 row in set (0.01 sec) ``` ## sql\_mode=PIPES\_AS\_CONCAT The following examples show how to set `sql_mode` with `PIPES_AS_CONCAT`. Create a table and add values: ```sql 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_mode`: ```sql SET sql_mode = 'PIPES_AS_CONCAT'; ``` Create a function: ```sql DELIMITER // CREATE or REPLACE FUNCTION func_1(base TEXT, ball TEXT) RETURNS TEXT AS BEGIN RETURN base || ball; END // ``` Set the `sql_mode`: ```sql delimiter ; set sql_mode = ''; ``` Call the function previously created: ```sql SELECT func_1(col_1, col_2) pipe_1 FROM concat_exp; ``` ```output +-----------+ | pipe_1    | +-----------+ | baseball  | | sunflower | | railroad  | | moonlight | +-----------+ ``` Select the columns from the table: ```sql SELECT col_1 || col_2 as pipe_2 FROM concat_exp; ``` ```output +--------+ | pipe_2 | +--------+ |      0 | |      0 | |      0 | |      0 | +--------+ ``` ## ssl\_key\_passphrase The passphrase specified with `ssl_key_passphrase` is used to decrypt the encrypted Privacy Enhanced Mail (PEM) or Password Protected (PASS) file. It can be added for encrypted SSL either at runtime or in `memsql.cnf` file and can used for both intra-cluster and inter-cluster configuration. The PEM or PASS file is decrypted once at boot time. ## Telemetry and Diagnostics SingleStore collects cluster-level usage and critical diagnostic information to improve product reliability, guide support, and help identify issues. Use the `send_usage_telemetry` and `critical_diagnostics` engine variables to control the collection and transfer of cluster usage and critical diagnostics information to the SingleStore endpoint at `https://phone-home.singlestore.com`. > **📝 Note**: Ensure that outbound traffic to `https://phone-home.singlestore.com` is enabled. SingleStore recommends enabling telemetry only in environments where usage collection aligns with organizational policy and compliance requirements. ## Cluster Telemetry When `send_usage_telemetry` is enabled, SingleStore collects cluster-level usage metrics from the Master Aggregator in the cluster and forwards it to `https://phone-home.singlestore.com`. This information is used for product improvements and providing support. SingleStore collects the following data: * Cluster type (single-node, distributed) * Number of aggregator and leaf nodes in the cluster * Capacity usage * Uptime * Query counts * Number of databases, tables, and views * Other non-sensitive cluster-level usage metrics ## Critical Diagnostics When `critical_diagnostics` is enabled, SingleStore collects critical diagnostic information and forwards it to `https://phone-home.singlestore.com`. This information is used for troubleshooting and reliability improvements. SingleStore collects the following diagnostic information: * Crash reports and related diagnostic traces * Attempts to use locked features * Generic DDLs and pipeline data * All DDL queries that may contain sensitive information are sanitized before transmission. * Table metadata * Other usage data required for diagnostics *** Modified at: February 6, 2026 Source: [/db/v9.1/reference/configuration-reference/engine-variables/list-of-engine-variables/](https://docs.singlestore.com/db/v9.1/reference/configuration-reference/engine-variables/list-of-engine-variables/) (An index of the documentation is available at /llms.txt)