Outdated Version
You are viewing an older version of this section. View current production version.
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.
Basic Settings
| Name | Description | Default Setting |
|---|---|---|
| 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 |
| 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 |
| 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. |
|
| maximum_memory | Maximum memory MemSQL will use, in MB. WARNING: do not set to more than 90% of system RAM | 90% of System RAM |
| 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 |
| port | MemSQL port number. | 3306 |
| 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 |
| 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 |
| datadir | Directory path for the data directory. This directory contains snapshots, logs, and columnstore segments. | |
| plancachedir | Directory path for the plancache directory. This directory contains compiled plans used for codegen. | ./plancache |
| tracelogsdir | Directory path for the tracelogs directory. This directory contains log files, including memsql.log and the query log. | ./tracelogs |
Cluster Management Settings
| Name | Description | Default Setting |
|---|---|---|
| aggregator_failure_detection | Decides whether or not aggregators should detect failures of other aggregators. WARNING: turning this variable OFF disables failover. |
ON |
| 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 |
| leaf_failure_detection | Decides whether or not the master aggregator should detect leaf failures. WARNING: turning this variable OFF disables failover. |
ON |
Connection Management Settings
| Name | Description | Default Setting |
|---|---|---|
| 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. | 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_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, see the In-Depth Variable Definitions section below. | 1024 |
| 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 |
| sync_slave_timeout | Maximum amount of time in milliseconds for the master to wait for acknowledgement from the synchronous replica. | 20000 |
Database Optimization Settings
| Name | Description | Default Setting |
|---|---|---|
| columnstore_disk_insert_threshold | At this threshold (fraction of columnar_segment_rows), multi inserts to the columnstore will be written straight to disk. | 0.5 |
| columnar_segment_rows | Controls the maximum row count for a columnstore segment. For more information, see the Managing Columnstore Segments section of the Columnstore topic. | 102400 |
| columnstore_window_size | Controls how much columnstore data is kept on disk for replicas to fetch. | 2147483648 |
| default_partitions_per_leaf | Number of default partitions by leaf. | 8 |
| 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. | 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. | 3600 |
| net_write_timeout | Number of seconds to wait for a block to be written to a connection before aborting the write. | 3600 |
| optimize_columnar_tables | This variable has been deprecated and is no longer operational in MemSQL. | |
| 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. | 0 |
| transaction_buffer | Defines the size of the transaction buffer MemSQL keeps in memory. | 67108864 |
Geospatial Settings
| Name | Description | Default Setting |
|---|---|---|
| geo_sphere_radius | The radius of the sphere used for distance calculation, in meters. (Defaults to average Earth radius.) | 6367444.657120 |
Logging Settings
| Name | Description | Default Setting |
|---|---|---|
| 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 |
Security Settings
| Name | Description | Default Setting |
|---|---|---|
| ssl_ca | CA file to be used for SSL connections. | |
| ssl_capath | CA directory to be used for SSL connections. | |
| ssl_cert | Certificate file to be used for SSL connections. | |
| ssl_cipher | Cipher to be used for SSL connections. | |
| ssl_key | Public-private key pair file to be used for SSL connections. |
Other Variables
| Name | Description | Default Setting |
|---|---|---|
| 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 |
| 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 |
| 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 |
| 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 |
| auto_replicate | This variable exists for backwards compatibility with MySQL and is non-operational in MemSQL. | — |
| autocommit | If ON, transactions will take effect immediately. |
ON |
| basedir | Specifies the path where the installation directory can be found. | |
| character_set_client | Specifies the character set to use in cases when the character set requested by the client is unknown. | utf8 |
| character_set_connection | The character set used for values that do not specify a character set and for number-to-string conversions. | utf8 |
| 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 |
| character_set_results | The character set used for displaying query results. | utf8 |
| character_set_server | The default character set of the server. | utf8 |
| character_set_system | The character set used to store identifiers. The value is always utf8. |
utf8 |
| character_sets_dir | Specifies the directory where character sets are stored. | /var/lib/memsql/share/charsets/ |
| collation_connection | This variable exists for backwards compatibility with MySQL and is non-operational in MemSQL. | utf8_general_ci |
| collation_database | This variable exists for backwards compatibility with MySQL and is non-operational in MemSQL. | utf8_general_ci |
| collation_server | This variable exists for backwards compatibility with MySQL and is non-operational in MemSQL. | utf8_general_ci |
| compile_only | If ON, MemSQL will compile, but not run, each query it receives. |
OFF |
| 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 |
| 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 |
| 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. | |
| gssapi_keytab_path | The local path to the keytab file created on the KDC. For more information, see the Kerberos Authentication topic. | |
| gssapi_principal_name | The SPN for MemSQL that was created on the KDC. For more information, see the Kerberos Authentication topic. | |
| hostname | The server host name specified by the server at startup. | |
| identity | Contains the value of last_insert_id. |
|
| interactive_timeout | This variable exists for backwards compatibility with MySQL and is non-operational in MemSQL. | 28800 |
| 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/ |
| 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. | |
| memsql_version | MemSQL version number. | |
| memsql_version_date | The build date of the MemSQL version currently running. | — |
| memsql_version_hash | MemSQL version hash. | |
| 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. | 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 |
| pipelines_batches_metadata_to_keep | The number of Pipelines batch metadata entries to persist before they are overwritten by incoming batches. | 1000 |
| pipelines_extractor_debug_logging | Specifies whether to enable extractor debugging for Kafka pipelines. This variable currently does not apply to S3 pipelines. | OFF |
| pipelines_kafka_version | The Kafka version used for the Kafka extractor. | 0.8.2.2 |
| pipelines_max_errors_per_partition | The maximum number of error event rows per leaf node partition to persist before they are deleted. | 1000 |
| pipelines_max_offsets_per_batch_partition | The maximum number of data source partition offsets to extract in a single batch transaction. | 1000000 |
| pipelines_max_retries_per_batch_partition | The number of retry attempts for writing batch partition data to the destination table. | 4 |
| pipelines_stderr_bufsize | The buffer size for standard error output, in bytes. | 65535 |
| pipelines_stop_on_error | Specifies whether or not each pipeline in the cluster should stop when an error occurs. | ON |
| protocol_version | Specifies the version of the client/server protocol. | 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 |
| saml_assertion_audience | Specifies a single audience restriction for the SAML assertion. For more information, see the Configuring SAML Global Variables topic. | |
| saml_message_recipient | Specifies the intended recipient for a wrapped encryption key. For more information, see the Configuring SAML Global Variables topic. | |
| 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. | |
| 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 |
| 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 |
| 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 |
| 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. | |
| 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. | |
| 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. |
|
| 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 |
| socket | Specifies the Unix socket file to use for local connections. | memsql.sock |
| sql_quote_show_create | If ON, identifiers are quoted by the server for SHOW CREATE commands. |
ON |
| system_time_zone | System time zone. | 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 |
| thread_stack | Specifies the stack size for each thread. | 1048576 |
| 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. |
SYSTEM |
| timestamp | Used to capture the original timestamp of the client. | 1391112305 |
| tls_version | Indicates the TLS version with which to configure 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. |
|
| tx_isolation | Transaction isolation level. | 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. | x86_64 |
| version_compile_os | The operating system on which MemSQL was built. | 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 |
| workload_management | Specifies whether to enable workload management for the cluster. For more information, see the Workload Management topic. | 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. For more information, see the Workload Management topic. | 1 |
| 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 |
| workload_management_max_queue_depth | The maximum number of queries that can be queued. For more information, see the Workload Management topic. | 100 |
| workload_management_max_threads_per_leaf | The maximum number of threads to use per leaf. For more information, see the Workload Management topic. | 8192 |
| 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 |
| 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 |
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.
