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

Cluster Management Settings

Connection Management Settings

Database Optimization Settings

Geospatial Settings

Logging Settings

Security Settings

Other Variables

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. Note: compile is an alias for llvm.
• mbc: Queries are interpreted and not compiled. Note: interpret is an alias for mbc.

The interpreter_mode can also be set in the following additional ways:

SELECT WITH (interpreter_mode = mbc) ...
SELECT FORCE_INTERPRETER_MODE ...

The interpreter_mode variable only works when set globally on all nodes.

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

• SELECT GLOBAL
• SET GLOBAL
• SHOW VARIABLES
• SHOW SESSION VARIABLES

Was this Article Helpful?

Thanks For Your Feedback!