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

Sync Variables

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

The sync 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.

You can set sync variables on the master aggregator, only. After you set a sync variable, your update is propagated to either other aggregators only or to all of the nodes in the cluster, depending on the variable.

Name	Description	Default	Exceptions to When Variable can be Set
aggregator_failure_detection	Deprecated in MemSQL 6.0, this is the timeout used when gathering workload management statistics from leaves. When you set this variable, its value is propagated to other aggregators only.	ON
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. When you set this variable, its value is propagated to other aggregators only.	ON
attach_rebalance_delay_seconds	Number of seconds to wait after a new node has attached to the cluster before running a rebalance. This mechanism is used to batch up rebalancing if many nodes fail and then come back online within a short period of time. When you set this variable, its value is propagated to other aggregators only.	120
cardinality_estimation_level	Specifies whether to use newer, more advanced histograms and algorithms to perform cardinality estimation (“6.5” and above), or use the previous histogram format (“6.0”). When you set this variable, its value is propagated to other aggregators only.	“6.5” (for new installs), “6.0” (for upgrades from prior releases)	You cannot set this variable to take effect when the node starts.
cluster_name	Specifies the cluster name used by the backup.
data_conversion_compatibility_level	Specifies whether to use new data conversion functionality that throws errors for integer under/overflow and string truncation issues. Values can be either 6.0 or 6.5 with 6.0 representing the previous, now deprecated behavior and 6.5 representing the new behavior. When you set this variable, its value is propogated to all nodes.	6.0	You cannot set this variable to take effect when the node starts.
default_autostats_enabled	Whether autostats is enabled by default on newly created columnstore tables. When you set this variable, its value is propagated to other aggregators only.	ON
default_distributed_ddl_timeout	The time, in milliseconds, to wait for a distributed DDL transaction to commit. This value sets the timeout for both ALTER TABLE and BACKUP commands. If the timeout is reached, the transaction is rolled back. When you set this variable, its value is propagated to other aggregators only.	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
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 used when gathering workload management statistics from leaves. Before MemSQL 6.0 this was the timeout of the heartbeat query used to trigger failovers. When you set this variable, its value is propagated to other aggregators only.	10
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 aggregators and all leaves.	TRUE
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. See the Password Expiration topic for details.	OFF
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 withing grace_interval_seconds. This is avoid a cycle of a problem node failing and coming back online. When you set this variable, its value is propagated to other aggregators only.	300
highlight_max_number_fragments	Maximum number of fragments to return from highlight function. When you set this variable, its value is propagated to all nodes.	0	You cannot set this variable to take effect when the node starts.
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. Minimum value is 10, maximum value is 65535. When you set this variable, its value is propagated to all nodes.	100	You cannot set this variable to take effect when the node starts.
leaf_failure_detection	Whether or not the master aggregator should detect leaf failures and trigger failovers. WARNING: Turning this variable OFF disables failover. When you set this variable, its value is propagated to other aggregators only.	On
password_expiration_seconds	The time in seconds before a password expires. The value 0 indicates that the password will never expire. See the Password Expiration topic for details.	0
password_history_count	The number of previous passwords per user that MemSQL will store and disallow from reuse. The value 0 indicates that any previous password can be reused. The maximum is 10. See the Password Reuse topic for details.	0
preserve_original_colstore_json	Set this variable to ON to preserve NULL values and empty arrays in a JSON object that is written to a columnstore table. It is a session variable that can be set globally. For more information on this variable, see the JSON Guide topic.	AUTO (same as OFF)
redundancy_level	If set to 1, there is no redundancy across leaves. If set to 2, turns on MemSQL’s High Availability mode. When you set this variable, its value is propagated to other aggregators only. For more information on this variable, see the Managing High Availability topic.	1
regexp_format	Specifies the regular expression format to be used by regular expression functions that you call. Possible values are 'extended' and 'advanced'. When you set this variable, its value is propagated to all nodes. For more information on this variable, see the Regular Expressions topic.	‘extended’
replication_sync	This variable can only be set set via the CREATE DATABASE and RESTORE commands. When set to ON, changes the default replication for replica partition to sync replication when a database is created. When this variable is set, its value is propagated to other aggregators only.	OFF	This variable can only be set via the CREATE DATABASE and RESTORE commands.
resource_governor_cpu_limit_mode	Determines whether the mode of control over the CPU for resource pools is HARD or SOFT. For more information on this variable, see CREATE RESOURCE POOL. This variable can sync to all aggregators and all leaves.	SOFT
resource_usage_model	Whether to train or enable the resource usage model for workload management. When you set this variable, its value is propagated to other aggregators only.	OFF	You cannot set this variable to take effect when the node starts.
strict_passwords	When set to ON, MemSQL will require that all passwords be at least 6 characters in length and contain at least one letter and one number. See the Password Complexity Requirements topic for details. Deprecated in version 7.1 and newer.	OFF
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.	240000
sync_permissions	This aggregator will synchronize its permissions with other aggregators. When you set this variable, its value is propagated to other aggregators only.	OFF

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	Exceptions to When Variable can be Set
advanced_hdfs_pipelines	Specifies whether to enable security features for HDFS pipelines.	OFF
java_pipelines_heap_size	Heap size in megabytes for HDFS pipelines.	8
pipelines_deskew_batch_partitions_threshold	For keyless sharded destination tables, if less than this fraction of batch partitions are active, reshuffle to avoid skew. When you set this variable, its value is propogated to all nodes.	0.75	You cannot set this variable to take effect when the node starts.
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. When you set this variable, its value is propagated to other aggregators only.	20000
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. When you set this variable, its value is propogated to all nodes.	120000	You cannot set this variable to take effect when the node starts.
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. When you set this variable, its value is propagated to other aggregators only.	1000000
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. When you set this variable, its value is propagated to other aggregators only.	4
pipelines_stop_on_error	Specifies whether or not each pipeline in the cluster should stop when an error occurs. If set to OFF, batches will be retried up to the number specified in the pipelines_max_retries_per_batch_partition variable. After all retries have failed, the batch will be skipped. When a batch is skipped, data loss can occur. If set to ON, the batch transaction that caused the error will be retried up to the number specified in the pipelines_max_retries_per_batch_partition variable. After all retries have failed, the pipeline will enter a Stopped state and must be manually started. When you set this variable, its value is propagated to other aggregators only.	ON
pipelines_stored_proc_exactly_once	If set to ON, run stored procedures from pipelines in a transaction. When you set this variable, its value is propagated to other aggregators only.	ON	You cannot set this variable to take effect when the node starts.

Workload Management Sync Variables

Name	Description	Default	Exceptions to When Variable can be Set
resource_usage_model	Whether to train or enable the resource usage model for workload management. When set to ON, workload management will estimate memory usage and queue queries that are estimated to use too much. When set to TRAINONLY, PROFILE can be run on queries to compare estimated memory usage of queries with their actual use. This is recommended when upgrading from any version prior to 6.7, in order to allow for the workload manager to do the necessary machine learning for accurate estimates. Once estimated memory use in PROFILE begins matching actual use, consider setting this variable to ON. This variable can sync to all aggregators.	OFF
workload_management	Specifies whether to enable workload management for the cluster. If this variable is set to ON, the other workload management engine variables will affect the way a query is executed. If set to OFF, the feature is disabled and no queueing or system resource optimization will occur.	ON
workload_management_expected_aggregators	The expected number of aggregators that will be used to run a high volume of client queries which require fully distributed execution. The default value is 0, which is equivalent to setting it to the total number of aggregators in the cluster. You may want to use a non-default value if you run your workload on a subset of aggregator nodes, only. For example, if you use two child aggregators, and only run your workload against the child aggregators, you can set this variable to 2. When you set this variable, its value is propagated to other aggregators only.	0
workload_management_max_connections_per_leaf	The maximum number of connections to use per leaf node in the cluster. When you set this variable, its value is propagated to other aggregators only.	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 a ER_TOO_MANY_QUEUED_QUERIES error will appear. When you set this variable, its value is propagated to other aggregators only.	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. When you set this variable, its value is propagated to other aggregators only.	8192
workload_management_memory_queuing	Whether to turn on queueing based on memory usage of queries. When you set this variable, its value is propagated to other aggregators only.	ON	You cannot set this variable to take effect when the node starts.
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. When you set this variable, its value is propagated to other aggregators only.	0.01	You cannot set this variable to take effect when the node starts.
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.	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. When you set this variable, its value is propagated to other aggregators only.	3600

Non-Sync Variables

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

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.

The variables that can be set to take effect while a node is running are global variables, unless otherwise noted as a different variable type in the Description column.

Basic Variables

Name	Description	Default Setting	Exceptions to When Variable can be Set
bind_address	If the address is 0.0.0.0, memsql accepts connections on all network interfaces, otherwise it only accepts connections for the given IP address.	0.0.0.0	You cannot set this variable to take effect while the node is running.
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 cannot set this variable to take effect while the node is running.
master_aggregator	Appending master_aggregator (with no arguments) to the end of a node’s memsql.cnf specifies that node as the cluster’s master aggregator. For more information on this variable, see the Master Aggregator topic.		You cannot set this variable to take effect while the node is running.
maximum_memory	Maximum memory MemSQL will use, in MB. WARNING: Do not set to more than the default.	90% of System RAM or System RAM minus 10 GB, whichever is greater
maximum_table_memory	Maximum memory MemSQL will use for table storage, in MB. WARNING: Do not set 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
port	MemSQL port number.	3306	You cannot set this variable to take effect while the node is running.
reported_hostname	The hostname or IP of the machine in use.
snapshot_trigger_size	The log size (in bytes) which, when reached, will trigger a new snapshot.	268435456
snapshots_to_keep	Number of snapshots and log files to keep for backup and replication.	2	You cannot set this variable to take effect while the node is running.
datadir	Directory path for the data directory. This directory contains snapshots, logs, and columnstore segments.		You cannot set this variable to take effect while the node is running.
plancachedir	Directory path for the plancache directory. This directory contains compiled plans used for codegen.	./plancache	You cannot set this variable to take effect while the node is running.
tracelogsdir	Directory path for the tracelogs directory. This directory contains log files, including memsql.log and the query log.	./tracelogs	You cannot set this variable to take effect while the node is running.

Connection Management Variables

Name	Description	Default Setting	Exceptions to When Variable can be Set
connect_timeout	The number of seconds the node’s MemSQL process is waiting for a connection.	10
max_allowed_packet	Maximum allowed protocol packet size. Session variable that can also be set globally.	104857600
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.	10
max_connections	The number of simultaneous clients allowed. For more, see the In-Depth Variable Definitions section below.	100000
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.	Half the number of cores on the host machine.
max_connection_threads	The maximum number of kernel threads for processing queries. For more, see the In-Depth Variable Definitions section below.	192
max_pooled_connections	The maximum number of stashed connections per leaf. For more information on this variable, see the In-Depth Variable Definitions section below.	1024	You cannot set this variable to take effect while the node is running.
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 the In-Depth Variable Definitions section below.	AUTO	You cannot set this variable to take effect while the node is running.
sync_slave_timeout	Maximum amount of time in milliseconds for the master to wait for acknowledgement from the synchronous replica.	10000

Database Optimization Variables

Name	Description	Default Setting	Exceptions to When Variable can be Set
columnstore_flush_bytes	Controls the rowstore-backed segment size for columnstore tables. For more information, see Advanced Columnstore Configuration Options.	33554432
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.	0.5
columnstore_segment_rows	Controls the maximum row count for a columnstore segment. For more information, see Advanced Columnstore Configuration Options.	1024000
columnar_segment_rows	Deprecated alias to columnstore_segment_rows.	1024000
columnstore_window_size	Controls how much columnstore data is kept on disk for replicas to fetch.	2147483648
disk_plan_expiration_minutes	The interval in which a query plan must be read from disk (plancache directory) before it is removed.	20160 (14 days)
enable_disk_plan_expiration	Enable removing of stale on disk plans from the plancache directory based on the value of disk_plan_expiration_minutes.	true
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.	10000
load_data_read_size	Number of bytes read at a time by LOAD DATA.	8192
load_data_write_size	Number of bytes written at a time by LOAD DATA.	8192
lock_wait_timeout	Time, in seconds, to wait for a row lock before returning an error. Session variable that can also be set globally.	60
max_prepared_stmt_count	The maximum number of simultaneous prepared statements.	16382
multi_insert_tuple_count	Preferred number of tuples in multi-inserts that aggregators send to leaves.	20000
net_read_timeout	Number of seconds to wait for more data from a connection before aborting the read. 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. Session variable that can also be set globally.	3600
optimize_columnar_tables	This variable has been deprecated and is no longer operational in MemSQL.
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 aggregators and all leaves.	50
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.	720
query_parallelism	Maximum number of simultaneous running queries. Session variable that can also be set globally.	0
transaction_buffer	Defines the size of the transaction buffer MemSQL keeps in memory.	67108864	You cannot set this variable to take effect while the node is running.

Geospatial Variables

Name	Description	Default Setting	Exceptions to When Variable can be Set
geo_sphere_radius	The radius of the sphere used for distance calculation, in meters. (Defaults to average Earth radius.)	6367444.657120

Logging Variables

Name	Description	Default Setting	Exceptions to When Variable can be Set
core_file	Turning core_file on or off determines whether or not full core dumps are produced upon a crash.	ON
critical_diagnostics	Sends usage and critical error diagnostics to MemSQL.	ON
general_log	If ON, every query will be logged to a table or log file. If PARTIAL, will log only when load is light. OFF turns off logging.	OFF
general_log_file	Log connections and queries to given file.	/var/lib/memsql/tracelogs/query.log
warn_level	Defines how MemSQL behaves when it encounters unsupported functionality. For more information, visit the Unsupported Feature List section of the MySQL Features Unsupported in MemSQL topic.	WARNINGS

Pipelines Variables

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

Name	Description	Default Setting	Exceptions to When Variable can be Set
java_pipelines_java_home	Specifies the value that MemSQL sets the node’s operating system environment variable JAVA_HOME to. If java_pipelines_java_home is not set, MemSQL does not set JAVA_HOME.		You cannot set this variable to take effect while the node is running.
java_pipelines_java_path	Specifies the path that the HDFS extractor uses to run Java.
pipelines_batches_metadata_to_keep	The number of batch metadata entries to persist before they are overwritten by incoming batches. As data is extracted from a source, it’s written in batches to a destination table on a leaf node. Metadata about these batches is temporarily persisted in the master aggregator’s information_schema.PIPELINES_BATCHES table. As new batches are loaded into the database, the oldest batch metadata entries will be removed from the information_schema.PIPELINES_BATCHES table. See the information_schema.PIPELINES_BATCHES Table section for more information about this metadata.	1000
pipelines_extractor_debug_logging	Specifies whether to enable extractor debugging for Kafka or HDFS pipelines. This variable currently does not apply to S3 pipelines.	OFF
pipelines_kafka_version	The Kafka version used for the Kafka extractor. While the default version is 0.8.2.2, newer versions can also be specified.	0.8.2.2
pipelines_max_concurrent	The maximum number of pipelines running concurrently.	50
pipelines_max_concurrent_batch_partitions	The maximum number of pipeline batch partitions running concurrently.	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.	1000
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.	65535

Security Variables

Name	Description	Default Setting	Exceptions to When Variable can be Set
ssl_ca	CA file to be used for SSL connections.		You cannot set this variable to take effect while the node is running.
ssl_capath	CA directory to be used for SSL connections.		You cannot set this variable to take effect while the node is running.
ssl_cert	Certificate file to be used for SSL connections.		You cannot set this variable to take effect while the node is running.
ssl_cipher	Cipher to be used for SSL connections.		You cannot set this variable to take effect while the node is running.
ssl_key	Public-private key pair file to be used for SSL connections.		You cannot set this variable to take effect while the node is running.
ssl_key_passphrase	Passphrase for encrypted ssl_key.		You cannot set this variable to take effect while the node is running.
node_replication_ssl_only	When this variable is set to ON (assuming that SSL is enabled), SSL is used for cross-cluster replication, but not for intra-cluster communication. When the variable is OFF, SSL is used for both cross-cluster as well as intra-cluster replication.	OFF	You cannot set this variable to take effect while the node is running.

Other Variables

Name	Description	Default Setting	Exceptions to When Variable can be Set
activities_delta_sleep_s	activities_delta_sleep_s specifies an interval of time, which is used by the mv_activities_cumulative variable to determine recent resource usage. For more information, see the Management View Reference topic. Session variable that can also be set globally.	1
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, visit the Configuring Audit Logging topic.	OFF	You cannot set this variable to take effect while the node is running.
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, visit the Audit Logging Levels topic.	OFF	You cannot set this variable to take effect while the node is running.
auditlog_rotation_size	Specifies the maximum size per log file in bytes. Required if logging is enabled. For more information on this variable, visit the Configuring Audit Logging topic.	134217728	You cannot set this variable to take effect while the node is running.
auditlog_rotation_time	Specifies the maximum time duration to write to a single log file in seconds. For more information on this variable, visit the Configuring Audit Logging topic.	3600	You cannot set this variable to take effect while the node is running.
auto_replicate	This variable exists for backwards compatibility with MySQL and is non-operational in MemSQL.	—
autocommit	If ON, transactions will take effect immediately. Session variable that can also be set globally.	ON
basedir	Specifies the path where the installation directory can be found.		You cannot set this variable to take effect while the node is running.
character_set_client	This variable exists for backwards compatibility with MySQL and is non-operational in MemSQL.
character_set_connection	This variable exists for backwards compatibility with MySQL and is non-operational in MemSQL.
character_set_database	This variable exists for backwards compatibility with MySQL and is non-operational in MemSQL.
character_set_filesystem	This variable exists for backwards compatibility with MySQL and is non-operational in MemSQL.
character_set_results	This variable exists for backwards compatibility with MySQL and is non-operational in MemSQL.
character_set_server	The character set of the node. Session variable that can also be set globally. For more information on this variable, see the In-Depth Variable Definitions section below.	utf8
character_set_system	The character set used to store identifiers. The value is read-only and always has the value utf8.	utf8
character_sets_dir	Specifies the directory where character sets are stored.	/var/lib/memsql/share/charsets/	You cannot set this variable to take effect while the node is running.
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. Session variable that can also be set globally. For more information on this variable, see the In-Depth Variable Definitions section below.	utf8_general_ci
collation_database	Sets the collation that is used on the node. When you set this variable, collation_connection and collation_server are automatically set to the same value. Session variable that can also be set globally. For more information on this variable, see the In-Depth Variable Definitions section below.	utf8_general_ci
collation_server	Sets the collation that is used on the node. When you set this variable, collation_connection and collation_database are automatically set to the same value. Session variable that can also be set globally. For more information on this variable, see the In-Depth Variable Definitions section below.	utf8_general_ci
compile_only	If ON, MemSQL will compile, but not run, each query it receives. Session variable that can also be set globally.	OFF	You cannot set this variable to take effect when the node starts.
core_file_mode	Specifies type of core dump to generate if MemSQL terminates abnormally. Options include NONE, PARTIAL, or FULL. PARTIAL omits most user data to keep the dump small, while a FULL core dump uses the equivalent amount of disk space as the amount of memory used by MemSQL.	PARTIAL	You cannot set this variable to take effect while the node is running.
date_format	This variable exists for backwards compatibility with MySQL and is non-operational in MemSQL.	%Y-%m-%d
datetime_format	This variable exists for backwards compatibility with MySQL and is non-operational in MemSQL.	%Y-%m-%d %H:%i:%s
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 topic for details on this variable.
div_precision_increment	The number of digits by which to increase the scale of the division results performed with the division (/) operator.	4	You can set this variable but it is currently not supported in MemSQL. MemSQL always returns the results to the fourth precision value.
error_count	This variable exists for backwards compatibility with MySQL and is non-operational in MemSQL.	0
explain_expression_limit	Specifies the maximum number of characters to be used by expressions when outputting the EXPLAIN for a query.	500
external_user	This variable exists for backwards compatibility with MySQL and is non-operational in MemSQL.
flat_plancache	This variable has been deprecated and is no longer operational in MemSQL version 5.0 or newer.
group_concat_max_len	This variable is the maximum length string GROUP_CONCAT() can return in bytes. It is a session variable which can be set globally and can be set to any value smaller than or equal to max_allowed_packet. For more information on this variable see max_allowed_packet in the previous section and the GROUP_CONCAT() SQL reference.	8192
gssapi_keytab_path	The local path to the keytab file created on the KDC. For more information on this variable, see the Kerberos Authentication topic.		You cannot set this variable to take effect while the node is running.
gssapi_principal_name	The SPN for MemSQL that was created on the KDC. For more information on this variable, see the Kerberos Authentication topic.		You cannot set this variable to take effect while the node is running.
hostname	The server host name specified by the server at startup. This variable is read-only.
identity	Contains the value of last_insert_id. Session variable that can also be set globally.		You cannot set this variable to take effect when the node starts.
ignore_insert_into_computed_column	Forces MemSQL server to ignore insert and insert-select values on a computed column. The server computes the value for computed columns based on the COMPUTE specification, and uses the computed value in place of the value specified in the INSERT or INSERT-SELECT statement.	OFF
interactive_timeout	This variable exists for backwards compatibility with MySQL and is non-operational in MemSQL.	28800
interpreter_mode	Controls whether and how MemSQL compiles or interprets query plans. Allowed settings include: llvm, mbc, interpret_first. See the interpreter_mode section below for more details. Session variable that can also be set globally.	interpret_first
kerberos_server_keytab	This variable has been deprecated and is no longer operational in MemSQL.
lc_messages	This variable exists for backwards compatibility with MySQL and is non-operational in MemSQL.
lc_messages_dir	Specifies the directory where error messages are stored.	/var/lib/memsql/share/	You cannot set this variable to take effect while the node is running.
lc_time_names	This variable exists for backwards compatibility with MySQL and is non-operational in MemSQL.	en_US
load_data_batch_size	This variable has been deprecated and is no longer operational in MemSQL version 5.0 or newer.
load_data_max_buffer_size	Maximum number of unparsed bytes read by LOAD DATA before throwing an error.	1073741823
local_infile	This variable exists for backwards compatibility with MySQL and is non-operational in MemSQL.	ON
locked_in_memory	This variable exists for backwards compatibility with MySQL and is non-operational in MemSQL.	OFF
max_user_connections	This variable exists for backwards compatibility with MySQL and is non-operational in MemSQL.
memsql_id	The ID of the MemSQL node whose config you would like to update. This variable is read-only.
memsql_version	MemSQL version number. This variable is read-only.
memsql_version_date	The build date of the MemSQL version currently running. This variable is read-only.
memsql_version_hash	MemSQL version hash. This variable is read-only.
minimal_disk_space	Sets the minimal available disk space allowed, under which MemSQL will halt new write queries.	100
net_buffer_length	Specifies the size of the connection buffer and the result buffer with which each client thread starts. Session variable that can also be set globally.	102400
net_retry_count	This variable exists for backwards compatibility with MySQL and is non-operational in MemSQL.	10
pid_file	The path name of the process ID file.	/var/lib/memsql/memsql.pid	You cannot set this variable to take effect while the node is running.
protocol_version	Specifies the version of the client/server protocol. This variable is read-only.	10
proxy_user	This variable exists for backwards compatibility with MySQL and is non-operational in MemSQL.
read_advanced_counters	If ON, this variable enables collection of advanced statistics. For more information, see the Advanced Statistics section of the Management View Statistics Reference topic.	OFF
resource_pool	Specifies the resource pool to be used. Session variable that can also be set globally. It can be set while a node is running, but cannot be set on node startup. For more information on this variable, see Setting Resource Limits	default_pool
saml_assertion_audience	Specifies a single audience restriction for the SAML assertion. For more information on this variable, see the Configuring SAML Global Variables topic.		You cannot set this variable to take effect while the node is running.
saml_message_recipient	Specifies the intended recipient for a wrapped encryption key. For more information on this variable, see the Configuring SAML Global Variables topic.		You cannot set this variable to take effect while the node is running.
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 the Configuring SAML Global Variables topic.		You cannot set this variable to take effect while the node is running.
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 the Configuring SAML Global Variables topic.	OFF	You cannot set this variable to take effect while the node is running.
saml_require_signature_validation	Specifies if authentication should fail when both the SAML response an SAML assertion are unsigned. For more information on this variable, see the Configuring SAML Global Variables topic.	OFF	You cannot set this variable to take effect while the node is running.
saml_use_NameID	Specifies whether a username value should be extracted from the <saml:NameID> element in a SAML assertion. For more information on this variable, see the Configuring SAML Global Variables topic.	OFF	You cannot set this variable to take effect while the node is running.
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 the Configuring SAML Global Variables topic.		You cannot set this variable to take effect while the node is running.
saml_x509_certificate	Specifies the file path for the identity provider’s public x509 signing certificate. For more information on this variable, see the Configuring SAML Global Variables topic.		You cannot set this variable to take effect while the node is running.
secure_file_priv	Specifies the directory to which any import or export operations should be limited, or disables import and export entirely if set to NULL.		You cannot set this variable to take effect while the node is running.
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.	ON	You cannot set this variable to take effect while the node is running.
socket	Specifies the Unix socket file to use for local connections.	memsql.sock	You cannot set this variable to take effect while the node is running.
sql_mode	Specifies the SQL mode or modes that affect the SQL syntax MemSQL supports and the query validation checks it performs. This is a session variable that can also be set globally. See the sql_mode section below for more information.	STRICT_ALL_TABLES
sql_quote_show_create	If ON, identifiers are quoted by the server for SHOW CREATE commands. Session variable that can also be set globally.	ON
system_time_zone	System time zone. This variable is read-only.	PST
table_precompiled_header	This variable has been deprecated and is no longer operational in MemSQL version 5.0 or newer.
thread_cache_size	Specifies how many inactive connection threads should be cached.	0
thread_handling	Determines how the server handles connection threads. A value of no-threads will cause the server to use a single thread to handle one connection. one-thread-per-connection causes the server to use one thread for each client connection.	one-thread-per-connection	You cannot set this variable to take effect while the node is running.
thread_stack	Specifies the stack size for each thread.	1048576	You cannot set this variable to take effect while the node is running.
time_format	This variable exists for backwards compatibility with MySQL and is non-operational in MemSQL.	%H:%i:%s
time_zone	The current time zone. By default, it is set to the same value as that of system_time_zone. Session variable that can also be set globally.	SYSTEM
timestamp	Used to capture the original timestamp of the client. Session variable that can also be set globally. It can be set while a node is running, but cannot be set on node startup.	1391112305
tls_version	Indicates the TLS version with which to configure the node. For more information, see Client Configuration for Secure Client Connections		You cannot set this variable to take effect while the node is running.
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 cannot set this variable to take effect while the node is running.
tx_isolation	Transaction isolation level. Session variable that can also be set globally.	READ-COMMITTED
use_vectorized_join	This variable has been deprecated and is no longer operational in MemSQL version 5.0 or newer.
version	This variable exists for backwards compatibility with MySQL and is non-operational in MemSQL. To check the version of MemSQL you are running, use the memsql_version variable instead.
version_comment	This variable exists for backwards compatibility with MySQL and is non-operational in MemSQL.
version_compile_machine	The type of the server binary. This variable is read-only.	x86_64
version_compile_os	The operating system on which MemSQL was built. This variable is read-only.	Linux
wait_timeout	This variable exists for backwards compatibility with MySQL and is non-operational in MemSQL.	28800
warning_count	This variable exists for backwards compatibility with MySQL and is non-operational in MemSQL.	0

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.

character_set_server

character_set_server is the default character set of the node. MemSQL supports the character sets binary and utf8.

When you set character_set_server to a value that is different than its previous value, MemSQL sets collation_connection, collation_database, and collation_server to the character set’s default collation.

collation_connection, collation_database, and collation_server

A collation defines the rules for a character set that specify how the characters are compared and sorted. When you set any of collation_connection, collation_database, and collation_server, MemSQL sets the other two variables to the same value and applies the collation to the node. Also, when you set any of these three variables, MemSQL sets character_set_server.

MemSQL supports these collations: binary, utf8_bin, utf8_general_ci, utf8_unicode_ci, utf8_czech_ci, utf8_danish_ci, utf8_esperanto_ci, utf8_estonian_ci, utf8_hungarian_ci, utf8_icelandic_ci, utf8_latvian_ci, utf8_lithuanian_ci, utf8_persian_ci, utf8_polish_ci, utf8_roman_ci, utf8_romanian_ci, utf8_sinhala_ci, utf8_slovenian_ci, utf8_slovak_ci, utf8_spanish_ci, utf8_spanish2_ci, utf8_swedish_ci, utf8_turkish_ci.

The following example shows that utf8_general_ci collation is case-insensitive. The output is 1, indicating that A and a match in this collation.

SET collation_connection = 'utf8_general_ci';
SELECT 'A' = 'a';

The following example shows that utf8_bin collation is case-sensitive. The output is 0, indicating that A and a do not match in this collation.

SET collation_connection = 'utf8_bin';
SELECT 'A' = 'a';

max_connection_threads

max_connection_threads is the maximum number of kernel-level threads the MemSQL 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 MemSQL 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 8192. The default for aggregators is 192, and the default for leaves is 8192. Since leaves are defaulted to the highest setting, there is typically no reason to change this variable for leaves.

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 MemSQL 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.

interpreter_mode

Controls both whether and how MemSQL 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.

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, MemSQL supports the following SQL modes:

• 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.
• 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.
• 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 a 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.
• 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.
• ANSI: Sets sql_mode to STRICT_ALL_TABLES, ONLY_FULL_GROUP_BY, PIPES_AS_CONCAT, and ANSI_QUOTES together.

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.

Example

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

SELECT @@sql_mode;
+-------------------+
| @@sql_mode        |
+-------------------+
| STRICT_ALL_TABLES |
+-------------------+
1 row in set (0.00 sec)

-- Set sql_mode to include ONLY_FULL_GROUP_BY

SET sql_mode = 'ONLY_FULL_GROUP_BY';
Query OK, 0 rows affected (0.00 sec)

SELECT @@sql_mode;
+--------------------------------------+
| @@sql_mode                           |
+--------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_ALL_TABLES |
+--------------------------------------+
1 row in set (0.01 sec)

-- Set sql_mode to ANSI_QUOTES, which replaces ONLY_FULL_GROUP_BY

SET sql_mode = 'ANSI_QUOTES';
Query OK, 0 rows affected (0.00 sec)

SELECT @@sql_mode;
+-------------------------------+
| @@sql_mode                    |
+-------------------------------+
| ANSI_QUOTES,STRICT_ALL_TABLES |
+-------------------------------+
1 row in set (0.00 sec)

-- Set ONLY_FULL_GROUP_BY and ANSI_QUOTES together

SET sql_mode = 'ONLY_FULL_GROUP_BY,ANSI_QUOTES';
Query OK, 0 rows affected (0.01 sec)

SELECT @@sql_mode;
+--------------------------------------------------+
| @@sql_mode                                       |
+--------------------------------------------------+
| ANSI_QUOTES,ONLY_FULL_GROUP_BY,STRICT_ALL_TABLES |
+--------------------------------------------------+
1 row in set (0.01 sec)