System Variables

This topic lists all of the available system variables in MemSQL. For an in-depth discussion on a few of the variables listed in the table below, see the In-Depth Variable Definitions section.

Scope of System Variables

Some system variables, such as bind_address, are defined in the context of a node. Each node can have a different value for the system variable. Other system variables, such as auto_attach, are defined for an entire MemSQL cluster. Cluster-wide variables can only be set on the master aggregator, and the value defined at the master aggregator will override the value set in memsql.cnf on any node.

Basic Settings

Name Description Default Setting Scope
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 Node
flush_before_replicate If ON, data is written and flushed to disk locally before being replicated to slaves. This will increase replication latency but will guarantee that slaves will not flush data to disk before the master does. OFF Node
master_aggregator Appending master_aggregator (with no arguments) to the end of a node’s memsql.cnf specifies that node as the cluster’s master aggregator. For more information, see the Master Aggregator topic. Node
maximum_memory Maximum memory MemSQL will use, in MB. WARNING: do not set to more than 90% of system RAM 90% of System RAM Node
maximum_table_memory Maximum memory MemSQL will use for table storage, in MB. WARNING: do not set to more than 90% of system RAM 90% of maximum_memory Node
port MemSQL port number. 3306 Node
redundancy_level If set to 1, there is no redundancy across leaves. If set to 2, turns on MemSQL’s High Availability mode. For more information, see the Managing High Availability topic. 1 Cluster
reported_hostname The hostname or IP of the machine in use. Node
snapshot_trigger_size The log size (in bytes) which, when reached, will trigger a new snapshot. 268435456 Node
snapshots_to_keep Number of snapshots and log files to keep for backup and replication. 2 Node
datadir Directory path for the data directory. This directory contains snapshots, logs, and columnstore segments. Node
plancachedir Directory path for the plancache directory. This directory contains compiled plans used for codegen. ./plancache Node
tracelogsdir Directory path for the tracelogs directory. This directory contains log files, including memsql.log and the query log. ./tracelogs Node

Cluster Management Settings

Name Description Default Setting Scope
aggregator_failure_detection Decides whether or not aggregators should detect failures of other aggregators. This variable is not used in MemSQL version 6.0 and later. WARNING: turning this variable OFF disables failover. ON Cluster
auto_attach Specifies whether the master aggregator (in redundancy 1 or 2) should attach a node that has died back onto the cluster after the node comes back online. If OFF, the master aggregator will not automatically reattach the node. ON Cluster
leaf_failure_detection Decides whether or not the master aggregator should detect leaf failures. WARNING: turning this variable OFF disables failover. ON Cluster

Connection Management Settings

Name Description Default Setting Scope
connect_timeout The number of seconds the node’s MemSQL process is waiting for a connection. 10 Node
max_allowed_packet Maximum allowed protocol packet size. 104857600 Node
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 Node
max_connections The number of simultaneous clients allowed. For more, see the In-Depth Variable Definitions section below. 100000 Node
max_connection_threads The maximum number of kernel threads for processing queries. For more, see the In-Depth Variable Definitions section below. 192 Node
max_pooled_connections The maximum number of stashed connections per leaf. For more, see the In-Depth Variable Definitions section below. 1024 Node
skip_name_resolve Controls whether to perform name resolution. By default, AUTO will only perform a reverse DNS lookup if there are any host-based security rules. Options include AUTO, OFF, or ON. AUTO Node
sync_slave_timeout Maximum amount of time in milliseconds for the master to wait for acknowledgement from the synchronously replicated slave. 20000 Node

Database Optimization Settings

Name Description Default Setting Scope
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”). Must be set on the master aggregator. Value will be synchronized to other nodes in the cluster. “6.5” (for new installs), “6.0” (for upgrades from prior releases) Cluster
columnstore_flush_bytes Controls the rowstore-backed segment size for columnstore tables. For more information, see Advanced Columnstore Configuration Options. 33554432 Node
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 Node
columnstore_segment_rows Controls the maximum row count for a columnstore segment. For more information, see Advanced Columnstore Configuration Options. 1024000 Node
columnar_segment_rows Deprecated alias to columnstore_segment_rows. 1024000 Node
columnstore_window_size Controls how much columnstore data is kept on disk for replication slaves to fetch. 2147483648 Node
default_partitions_per_leaf Number of default partitions by leaf. 8 Cluster
default_distributed_ddl_timeout The time, in milliseconds, to wait for a distributed DDL transaction to commit. This value will set the timeout for both ALTER TABLE and BACKUP commands. 18446744073709551615 Cluster
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) Node
enable_disk_plan_expiration Enable removing of stale on disk plans from the plancache directory based on the value of disk_plan_expiration_minutes. false Node
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. llvm Cluster
load_data_read_size Number of bytes read at a time by LOAD DATA. 8192 Node
load_data_write_size Number of bytes written at a time by LOAD DATA. 8192 Node
lock_wait_timeout Time, in seconds, to wait for a row lock before returning an error. 60 Node
max_prepared_stmt_count The maximum number of simultaneous prepared statements. 16382 Node
multi_insert_tuple_count Preferred number of tuples in multi-inserts that aggregators send to leaves. 20000 Node
net_read_timeout Number of seconds to wait for more data from a connection before aborting the read. 3600 Node
net_write_timeout Number of seconds to wait for a block to be written to a connection before aborting the write. 3600 Node
optimize_columnar_tables This variable has been deprecated and is no longer operational in MemSQL. Node
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 Node
query_parallelism Maximum number of simultaneous running queries. 0 Node
transaction_buffer Defines the size of the transaction buffer MemSQL keeps in memory. 67108864 Node

Geospatial Settings

Name Description Default Setting Scope
geo_sphere_radius The radius of the sphere used for distance calculation, in meters. (Defaults to average Earth radius.) 6367444.657120 Node

Logging Settings

Name Description Default Setting Scope
core_file Turning core_file on or off determines whether or not full core dumps are produced upon a crash. ON Node
critical_diagnostics Sends usage and critical error diagnostics to MemSQL. ON Node
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 Node
general_log_file Log connections and queries to given file. /var/lib/memsql/tracelogs/query.log Node
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 Node

Security Settings

Name Description Default Setting Scope
ssl_ca CA file to be used for SSL connections. Node
ssl_capath CA directory to be used for SSL connections. Node
ssl_cert Certificate file to be used for SSL connections. Node
ssl_cipher Cipher to be used for SSL connections. Node
ssl_key Public-private key pair file to be used for SSL connections. Node
node_replication_ssl_only If set to ON and SSL is enabled, disable SSL internally. OFF Node
sync_permissions The master aggregator will synchronize non-root user account information with other aggregators. OFF Cluster

Other Variables

Name Description Default Setting Scope
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. 1 Node
auditlog_level auditlog_level is used to specify the level of logging in a node. There are 11 logging levels. For more information, visit the Audit Logging Levels topic. OFF Node
auditlog_rotation_size Specifies the maximum size per log file in bytes. Required if logging is enabled. For more information, visit the Configuring Audit Logging topic. 134217728 Node
auditlog_rotation_time Specifies the maximum time duration to write to a single log file in seconds. For more information, visit the Configuring Audit Logging topic. 3600 Node
auto_replicate This variable exists for backwards compatibility with MySQL and is non-operational in MemSQL. Node
autocommit If ON, transactions will take effect immediately. ON Node
basedir Specifies the path where the installation directory can be found. Node
character_set_client Specifies the character set to use in cases when the character set requested by the client is unknown. utf8 Node
character_set_connection The character set used for values that do not specify a character set and for number-to-string conversions. utf8 Node
character_set_filesystem The character set used to convert file names. By default, no conversion occurs. For systems that allow larger file names, the character_set_filesystem variable can be changed from binary to, e.g., utf8. binary Node
character_set_results The character set used for displaying query results. utf8 Node
character_set_server The default character set of the server. utf8 Node
character_set_system The character set used to store identifiers. The value is always utf8. utf8 Node
character_sets_dir Specifies the directory where character sets are stored. /var/lib/memsql/share/charsets/ Node
collation_connection This variable exists for backwards compatibility with MySQL and is non-operational in MemSQL. utf8_general_ci Node
collation_database This variable exists for backwards compatibility with MySQL and is non-operational in MemSQL. utf8_general_ci Node
collation_server This variable exists for backwards compatibility with MySQL and is non-operational in MemSQL. utf8_general_ci Node
compile_only If ON, MemSQL will compile, but not run, each query it receives. OFF Node
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 Node
date_format This variable exists for backwards compatibility with MySQL and is non-operational in MemSQL. %Y-%m-%d Node
datetime_format This variable exists for backwards compatibility with MySQL and is non-operational in MemSQL. %Y-%m-%d %H:%i:%s Node
data_conversion_compatibility_level Specifies whether to use new data conversion functionality that throws errors for integer under/overflow and string truncation issues. This is a global variable, so it must be set with SET GLOBAL. 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. Changes to this value will be synchronized to all aggregators and leaves. 6.0 Cluster
error_count This variable exists for backwards compatibility with MySQL and is non-operational in MemSQL. 0 Node
explain_expression_limit Specifies the maximum number of characters to be used by expressions when outputting the EXPLAIN for a query. 500 Node
external_user This variable exists for backwards compatibility with MySQL and is non-operational in MemSQL. Node
flat_plancache This variable has been deprecated and is no longer operational in MemSQL version 5.0 or newer. Node
gssapi_keytab_path The local path to the keytab file created on the KDC. For more information, see the Kerberos Authentication topic. Node
gssapi_principal_name The SPN for MemSQL that was created on the KDC. For more information, see the Kerberos Authentication topic. Node
hostname The server host name specified by the server at startup. Node
identity Contains the value of last_insert_id. Node
interactive_timeout This variable exists for backwards compatibility with MySQL and is non-operational in MemSQL. 28800 Node
kerberos_server_keytab This variable has been deprecated and is no longer operational in MemSQL. Node
lc_messages This variable exists for backwards compatibility with MySQL and is non-operational in MemSQL. Node
lc_messages_dir Specifies the directory where error messages are stored. /var/lib/memsql/share/ Node
lc_time_names This variable exists for backwards compatibility with MySQL and is non-operational in MemSQL. en_US Node
load_data_batch_size This variable has been deprecated and is no longer operational in MemSQL version 5.0 or newer. Node
load_data_max_buffer_size Maximum number of unparsed bytes read by LOAD DATA before throwing an error. 1073741823 Node
local_infile This variable exists for backwards compatibility with MySQL and is non-operational in MemSQL. ON Node
locked_in_memory This variable exists for backwards compatibility with MySQL and is non-operational in MemSQL. OFF Node
max_user_connections This variable exists for backwards compatibility with MySQL and is non-operational in MemSQL. Node
memsql_id The ID of the MemSQL node whose config you would like to update. Node
memsql_version MemSQL version number. Node
memsql_version_date The build date of the MemSQL version currently running. Node
memsql_version_hash MemSQL version hash. Node
minimal_disk_space Sets the minimal available disk space allowed, under which MemSQL will halt new write queries. 100 Node
net_buffer_length Specifies the size of the connection buffer and the result buffer with which each client thread starts. 102400 Node
net_retry_count This variable exists for backwards compatibility with MySQL and is non-operational in MemSQL. 10 Node
pid_file The path name of the process ID file. /var/lib/memsql/memsql.pid Node
pipelines_batches_metadata_to_keep The number of Pipelines batch metadata entries to persist before they are overwritten by incoming batches. 1000 Node
pipelines_extractor_debug_logging Specifies whether to enable extractor debugging for Kafka pipelines. This variable currently does not apply to S3 pipelines. OFF Node
pipelines_kafka_version The Kafka version used for the Kafka extractor. 0.8.2.2 Node
pipelines_max_errors_per_partition The maximum number of error event rows per leaf node partition to persist before they are deleted. 1000 Node
pipelines_max_offsets_per_batch_partition The maximum number of data source partition offsets to extract in a single batch transaction. 1000000 Node
pipelines_max_retries_per_batch_partition The number of retry attempts for writing batch partition data to the destination table. 4 Node
pipelines_stderr_bufsize The buffer size for standard error output, in bytes. 65535 Node
pipelines_stop_on_error Specifies whether or not each pipeline in the cluster should stop when an error occurs. ON Node
protocol_version Specifies the version of the client/server protocol. 10 Node
proxy_user This variable exists for backwards compatibility with MySQL and is non-operational in MemSQL. Node
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 Node
regexp_format Specifies the regular expression format to be used by regular expression functions that you call. Possible values are 'extended' and 'advanced'. Can only be set globally. For more information, see the Regular Expressions topic. ‘extended’ Cluster
resource_pool Specifies the resource pool for new connections. For more information, see Setting Resource Limits default_pool Cluster
saml_assertion_audience Specifies a single audience restriction for the SAML assertion. For more information, see the Configuring SAML Global Variables topic. Node
saml_message_recipient Specifies the intended recipient for a wrapped encryption key. For more information, see the Configuring SAML Global Variables topic. Node
saml_private_decryption_key Specifies the file path for the private key used to decrypt an encrypted assertion. For more information, see the Configuring SAML Global Variables topic. Node
saml_require_encryption Specifies if authentication should fail when both the SAML response and SAML assertion are unencrypted. For more information, see the Configuring SAML Global Variables topic. OFF Node
saml_require_signature_validation Specifies if authentication should fail when both the SAML response an SAML assertion are unsigned. For more information, see the Configuring SAML Global Variables topic. OFF Node
saml_use_NameID Specifies whether a username value should be extracted from the <saml:NameID> element in a SAML assertion. For more information, see the Configuring SAML Global Variables topic. OFF 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, see the Configuring SAML Global Variables topic. Node
saml_x509_certificate Specifies the file path for the identity provider’s public x509 signing certificate. For more information, see the Configuring SAML Global Variables topic. Node
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. Node
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 Node
socket Specifies the Unix socket file to use for local connections. memsql.sock Node
sql_mode Specifies the SQL mode or modes that affect the SQL syntax MemSQL supports and the query validation checks it performs. See the sql_mode section below for more information. STRICT_ALL_TABLES Node
sql_quote_show_create If ON, identifiers are quoted by the server for SHOW CREATE commands. ON Node
system_time_zone System time zone. PST Node
table_precompiled_header This variable has been deprecated and is no longer operational in MemSQL version 5.0 or newer. Node
thread_cache_size Specifies how many inactive connection threads should be cached. 0 Node
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 Node
thread_stack Specifies the stack size for each thread. 1048576 Node
time_format This variable exists for backwards compatibility with MySQL and is non-operational in MemSQL. %H:%i:%s Node
time_zone The current time zone. By default, it is set to the same value as that of system_time_zone. SYSTEM Node
timestamp Used to capture the original timestamp of the client. 1391112305 Node
tls_version Indicates the TLS version with which to configure the node. 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. Node
tx_isolation Transaction isolation level. READ-COMMITTED Node
use_vectorized_join This variable has been deprecated and is no longer operational in MemSQL version 5.0 or newer. Node
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. Node
version_comment This variable exists for backwards compatibility with MySQL and is non-operational in MemSQL. Node
version_compile_machine The type of the server binary. x86_64 Node
version_compile_os The operating system on which MemSQL was built. Linux Node
wait_timeout This variable exists for backwards compatibility with MySQL and is non-operational in MemSQL. 28800 Node
warning_count This variable exists for backwards compatibility with MySQL and is non-operational in MemSQL. 0 Node
workload_management Specifies whether to enable workload management for the cluster. For more information, see the Workload Management topic. ON Node
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. For more information, see the Workload Management topic. 1 Node
workload_management_max_connections_per_leaf The maximum number of connections to use per leaf node in the cluster. For more information, see the Workload Management topic. 1024 Node
workload_management_max_queue_depth The maximum number of queries that can be queued. For more information, see the Workload Management topic. 100 Node
workload_management_max_threads_per_leaf The maximum number of threads to use per leaf. For more information, see the Workload Management topic. 8192 Node
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 more information, see the Workload Management topic. 0.500000 Node
workload_management_queue_timeout The time duration in seconds after which a query times out and is removed from the queue without being executed. 3600 Node
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. 100 Node
replication_sync Specifies whether the default replication mode is synchronous (ON) or asynchronous (OFF). For more information, see the CREATE DATABASE or RESTORE topics. OFF Cluster

In-Depth Variable Definitions

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

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: Queries are compiled to machine code.
  • mbc: Queries are interpreted and not compiled.
  • 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. This interpret_first mode is experimental and should not be used in production until further notice.

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

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)

System Variables Commands

Was this article useful?