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-opsMEMSQL-UPDATE-CONFIG command.
; ------------------------------------------------------------------
; THIS CONFIGURATION FILE IS MANAGED BY MEMSQL OPS
; 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
; ------------------------------------------------------------------
[server]
basedir = .
bind_address = 0.0.0.0
core_file
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
; ------------------------------------------------------------------
; MEMSQL OPS VARIABLES
;
; Variables below this header are controlled by MemSQL Ops.
; Please do not edit any of these values directly.
; ------------------------------------------------------------------
master_aggregator
port = 3306
Basic Settings
Setting
Default
Functionality
bind-address
0.0.0.0
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.
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.
interactive_timeout
28800
The number of seconds the server waits for activity on an interactive connection before closing it.
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
151
The number of simultaneous clients allowed.
max_connection_threads
8192
The maximum number of kernel threads for processing connections.
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.
wait_timeout
28800
The number of seconds the server waits for activity on a connection before closing it.
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.
