Outdated Version

You are viewing an older version of this section. View current production version.

memsql.cnf (MemSQL configuration)

MemSQL node settings are configured in the memsql.cnf file, which is a compatible extension of MySQL’s my.cnf file.

Default memsql.cnf Settings

The code listing below shows the default memsql.cnf file included with MemSQL.

  • For a basic MemSQL installation, this file is handled wholly by MemSQL Ops. You should not need to change these settings at all.
  • For a customized installation, see the description of each setting possible on this page.
  • To update MemSQL settings, we recommend using the memsql-ops MEMSQL-UPDATE-CONFIG command.
; ------------------------------------------------------------------
; MemSQL Ops controls the data in this file.  Please be careful
; when editing it.
; For more information, see our documentation at http://docs.memsql.com
; ------------------------------------------------------------------
basedir = .
bind_address =
default_partitions_per_leaf = 8
lc_messages_dir = ./share
lock_wait_timeout = 60
max_connections = 100000
snapshot_trigger_size = 256m
socket = /memsql-ops/installs/master-3306/memsql.sock
tmpdir = .
transaction_buffer = 64m
; ------------------------------------------------------------------
; Variables below this header are controlled by MemSQL Ops.
; Please do not edit any of these values directly.
; ------------------------------------------------------------------
port = 3306

Basic Settings

Setting Default Functionality
bind-address If the address is, memsql accepts connections on all network interfaces, otherwise it only accepts connections for the given IP address.
flush_before_replicate OFF If set, data is written and flushed to disk locally before it is replicated. It will increase replication latency, but will guarantee that replicas will not be able to flush the data to disk before the master does.
master_aggregator no default value With no arguments it runs as the master aggregator, otherwise host:port of the master.
maximum_memory 90% of System RAM The maximum amount of memory to be used by MemSQL (in MB). By default this is set to a percentage of all physical RAM on the machine. If you want to override the value, set it in Megabytes.
maximum_table_memory 90% of maximum_memory The maximum amount of memory to be used for table storage by MemSQL (in MB). By default this is set to a percentage of maximum_memory on the machine. If you want to override the value, set it in Megabytes.
port 3306 Port number to use for connection.
port_open_timeout 0 Timeout in seconds to wait while the server port is marked as in-use (0 means no wait).
redundancy_level 1 If set to 1, no redundancy across leaves. If set to 2, turns on MemSQL’s High Availability Mode.
reported_hostname no default value The hostname or IP of this machine that it will send other machines that wish to communicate with it.
snapshot_trigger_size 268435456 byte Indicates the size upon log reaching which a new snapshot will be kicked off.
snapshots_to_keep 2 Number of snapshot and log files to keep for backup and replication.
user, u current user Run memsqld daemon as user.
datadir ./data Directory path for the data directory. This directory contains snapshots, logs, and columnstore segments.
plancachedir ./plancache Directory path for the plancache directory. This directory contains compiled plans used for codegen.
tracelogsdir ./tracelogs Directory path for the tracelogs directory. This directory contains log files, including memsql.log and the query log.

Cluster Management Settings

Setting Default Functionality
aggregator_failure_detection ON Whether or not aggregators should detect failures of other aggregators.
auto_attach ON If the aggregator is in redundancy 1, should it try to attach a node automatically, after it dies
distributed_heartbeat_timeout 10 Query timeout for distributed heartbeats.
leaf_failure_detection ON Whether or not the master aggregator should detect leaf failures.

Connection Management Settings

Setting Default Functionality
connect_timeout 10 The number of seconds the memsqld server is waiting for a connect.
max_allowed_packet 104857600 Maximum allowed protocol packet size.
max_connect_errors 10 If there is more than this number of interrupted connections from a host this host will be blocked from further connections.
max_connections 100000 The number of simultaneous clients allowed.
max_connection_threads 8192 The maximum number of kernel threads for processing queries.
max_pooled_connections 1024 The maximum number of stashed connections per leaf.
skip_name_resolve AUTO Controls whether to perform name resolution.OFF, ON, or AUTO. By default AUTO will only perform a reverse DNS lookup if there are any host-based security rules.
sync_slave_timeout 10000 Maximum amount of time in milliseconds for the master to wait for acknowledgement from the synchronous replica.

Database Optimization Settings

Setting Default Functionality
buffered_rows 1024 The number of rows to buffer during a distributed join.
columnstore_disk_insert_threshold 0.5 At this threshold (fraction of columnar_segment_rows), multi inserts to the columnstore will be written straight to disk.
columnar_segment_rows 102400 Max number of rows in a segment file.
columnstore_window_size 2147483648 Total size in bytes of columnstore data to keep for replicas. Larger window will help prevent columnstore tables in replicas from going out of sync.
default_partitions_per_leaf 8 The number of default partitions per leaf.
load_data_read_size 8192 Number of bytes read at a time by LOAD DATA.
load_data_write_size 8192 Number of bytes written at a time by LOAD DATA.
lock_wait_timeout 60 Timeout in seconds to wait for a row lock before returning an error.
max_prepared_stmt_count 16382 Maximum number of simultaneous prepared statements.
multi_insert_tuple_count 1000 Preferred number of tuples in multi-inserts aggregators send to leaves.
net_first_packet_read_timeout 30 Number of seconds to wait for an idle transaction when it’s blocking DDL operations.
net_read_timeout 3600 Number of seconds to wait for more data from a connection before aborting the read.
net_write_timeout 3600 Number of seconds to wait for a block to be written to a connection before aborting the write.
optimize_columnar_tables ON Optimize columnstore segments in a background thread.
plan_expiration_minutes 720 A query plan needs to be re-used at least once in this interval or it gets unloaded from the query plan cache.
query_parallelism 0 Maximum number of simultaneous running queries.
recovery_batch_size 5 Controls how many rows are batched per replay thread during recovery from disk.
replication_timeout_ms 60000 Network timeout in milliseconds for replication
rows_per_period 1 The number of rows the SimpleStreamingIterator pulls per round robin period.
transaction_buffer 67108864 Defines the size of the transaction buffer MemSQL keeps in memory.

Geospatial Settings

Setting Default Functionality
geo_sphere_radius 6367444.657120 The radius of the sphere used for distance calculation (defaults to average Earth radius in meters).

Logging Settings

Setting Default Functionality
core_file ON Produce full core dumps on crashes.
critical_diagnostics ON Send usage and critical error diagnostics to MemSQL.
debug_mode OFF Generate debug information during code generation.
general_log OFF Log connections and queries to a table or log file. OFF - no logging, ON - log every query, PARTIAL - log only when load is light
general_log_file /var/lib/memsql/tracelogs/query.log Log connections and queries to given file.
warn_level WARNINGS Uncommon syntax support: ERRORS, WARNINGS or EXPERIMENTAL.

Security Settings

Setting Default Functionality
ssl_ca none CA file to be used for SSL connections.
ssl_capath none CA directory to be used for SSL connections.
ssl_cert none Certificate file to be used for SSL connections.
ssl_cipher none Cipher be used for SSL connections.
ssl_key none Public-private key pair file to be used for SSL connections.