Many features and configuration settings in MemSQL depend on system variables, and most of them must be set on all nodes in a cluster. Due to MemSQL’s distributed nature, users commonly expect that a modified system variable will be automatically applied to every node in the cluster. However, each node persists its own system variables, and therefore any modified system variable is scoped to a single node.

It’s also important to consider what system variables have been modified when increasing your cluster size. Any modified system variable settings will not be automatically applied to new nodes when they’re added to the cluster; for example, no system variable inheritance occurs from the master aggregator to leaf nodes. Therefore when scaling your cluster, each new node’s settings must be updated with the same values as other nodes.

The easiest way to set system variables is to use MemSQL Ops’ memsql-update-config command, which can apply changes to each node in the cluster. If your cluster is not administered by MemSQL Ops, you can also set the variables manually on each node by using the SET GLOBAL command or by updating their respective memsql.cnf file.

The following sections describe how to use these options to update system variables:

• Setting System Variables Using MemSQL Ops
• Setting System Variables Using SET GLOBAL VARIABLE
• Setting System Variables Using the memsql.cnf File

Setting System Variables Using MemSQL Ops

If your cluster is administered by MemSQL Ops, the best way to change system variables is to use the memsql-update-config command. This command automatically updates one or more nodes’ memsql.cnf file. See the MEMSQL-UPDATE-CONFIG topic and the memsql.cnf topic for more information.

1. While connected to the master aggregator node, create a new terminal window.
2. At the prompt, execute the following command, replacing my_variable_name and my_value with the variable key and value for your desired variable:

memsql-ops memsql-update-config --set-global --key my_variable_name --value my_value --all

MemSQL Ops will attempt to change each node’s global system variable to the desired setting. If the operation was successful, MemSQL Ops will output the following message for each node:

Successfully updated config for MemSQL node <node-ID>.

3. You can also verify that the change was successful by connecting to the master aggregator node with a MySQL client and executing the following command:

memsql> SHOW GLOBAL VARIABLES LIKE '%my_variable_name%';
+------------------+----------+
| Variable_name    | Value    |
+------------------+----------+
| my_variable_name | my_value |
+------------------+----------+
1 row in set (0.00 sec)

Setting System Variables Using SET GLOBAL VARIABLE

The SET GLOBAL VARIABLE command can be used to change global variable settings. Here the GLOBAL keyword implies that the variable setting is persistent across all future client connections on the server until the next restart; each GLOBAL variable setting will be removed upon restart of the server. To permanently persist variables across all future client connections, see the Setting System Variables Using the memsql.cnf File section below.

If the GLOBAL keyword is not used and SET VARIABLE is executed instead, a variable setting applies only to your current client connection (session), not all other current and future client connections. Some system variables in MemSQL can only be set globally; refer to the documentation for the specific feature or setting to determine if a global setting is required.

To apply system variable changes across all nodes in the cluster, the SET GLOBAL command must be executed on each node individually. If you add more nodes to your cluster after setting a variable, you must also apply the same variable settings to each additional node.

1. Use a MySQL client to connect to the desired node. Note that every node in the cluster must be updated.
2. At the prompt, execute the following command, replacing my_variable_name and my_value with your desired variable name and value:

memsql> SET GLOBAL my_variable_name = my_value;
Query OK, 0 rows affected (0.00 sec)

You can also verify that the change was successful by executing the following command:

memsql> SHOW GLOBAL VARIABLES LIKE '%my_variable_name%';
+------------------+----------+
| Variable_name    | Value    |
+------------------+----------+
| my_variable_name | my_value |
+------------------+----------+
1 row in set (0.00 sec)

Setting System Variables Using the memsql.cnf Configuration File

Each node has its own memsql.cnf file that is used to load the node’s configuration when MemSQL starts. System variables can be specified in this file, and they will automatically be applied to the node’s configuration when the node starts.

To set system variables in the memsql.cnf file:

1. Ensure that the cluster is in a stopped state before continuing.
2. Open the node’s memsql.cnf file in a text editor. Note that every other node’s memsql.cnf file must be updated as well.
3. Add the following line to the file anywhere below the [server] declaration, replacing my_variable_name and my_value with your desired variable name and value:

[server]
...
my_variable_name = my_value
...

4. Save the file.
5. Insert the same line to each node’s memsql.cnf file.
6. After the memsql.cnf file has been updated for each node in the cluster, start the cluster.
7. Verify that the change was successful by executing the following command on one of the affected nodes:

memsql> SHOW GLOBAL VARIABLES LIKE '%my_variable_name%';
+------------------+----------+
| Variable_name    | Value    |
+------------------+----------+
| my_variable_name | my_value |
+------------------+----------+
1 row in set (0.00 sec)

Was this Article Helpful?

Thanks For Your Feedback!