Outdated Version

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

Engine Variables

MemSQL defines engine variables as either sync or non-sync. The following sections discuss these types of variables and how to set them.

Sync Variables

Sync variables are cluster-wide variables that you set on the master aggregator. After you set a sync variable, your update is propagated to either other aggregators only or to all of the nodes in the cluster, depending on the variable.

You can set a sync variable in two ways:

  • Using a MemSQL client, run SET GLOBAL on the master aggregator.
  • At the Linux command line, run the following command to update the node’s configuration, according to whether you are using the MemSQL tools or MemSQL Ops to manage your cluster.

MemSQL tools:

Run MEMSQL-ADMIN UPDATE-CONFIG.

MemSQL Ops:

Run MEMSQL-OPS MEMSQL-UPDATE-CONFIG.

Include the --set-global flag. Using the -memsql-id parameter, specify the master aggregator node.

See the list of sync variables that you can set.

Alert

You can set a sync variable on the master aggregator only. You will receive an error if you attempt to set a sync variable on any other type of node.

Sync variables can only be set globally; you cannot use the SET SESSION command to set sync variables on the session level.

You should not set a sync variable by editing the memsql.cnf file. Attempting to do so may result in an error, or the setting may have no effect. Instead, set sync variables using methods described above.

Note: Use the @@ selector to read the value of a variable. See Reading Variables for more information.

Non-Sync Variables

Most engine variables are non-sync. They are set to take effect on individual nodes. They have one the following four states:

  • Settable to take effect when a node starts
  • Settable to take effect while a node is running
  • Settable to take effect both when a node starts and while the node is running
  • Read-only (not settable)

Scopes of Non-Sync Runtime Variables

There are three types of non-sync variables that you can set to take effect while a node is running:

  • Global. When you set a global variable, its value is effective for your current connection to the node and any subsequent connections, initiated by any user.
  • Session, only. When you set a session variable, its value is effective for your current connection to the node.
  • Session that can also be set globally. This type of variable can be set for the session or globally. When you set it for the session, its value is effective for your current connection to the node. When you set it globally, its value is NOT effective for any current connections to the node but is effective for any subsequent connections to the node, initiated by any user.

Note: Use the @@ selector to read the value of a variable. See Reading Variables for more information.

Setting Non-Sync Variables

You can use a MemSQL client to set the three types of non-sync variables listed above. SET SESSION sets a session variable. SET GLOBAL sets a variable globally. If you use either of these commands to set a non-sync variable, the variable’s value will not persist if the node is restarted.

To set the variable’s value to take effect when the node starts, run the following command to update the node’s configuration, according to whether you are using the MemSQL tools or MemSQL Ops to manage your cluster.

MemSQL tools:

Run MEMSQL-ADMIN UPDATE-CONFIG at the Linux command line.

MemSQL Ops:

Run MEMSQL-OPS MEMSQL-UPDATE-CONFIG at the Linux command line.

Specify the --all flag if you want to propagate the non-sync variable’s value to all nodes. You can also use the --set-global flag to have the variable’s value take effect immediately (assuming the node is running and the variable can be set at runtime) in addition to when the node restarts.

See the list of non-sync variables that you can set.

Alert

When you add new nodes to a cluster, non-sync variables that you set previously are not automatically applied to the new nodes.

Reading Variables

To retrieve the value of a variable, use the @@ selector with the variable in a SELECT statement. For example,

SELECT @@sql_mode;
****
+-------------------+
| @@sql_mode        |
+-------------------+
| STRICT_ALL_TABLES |
+-------------------+
1 row in set (0.27 sec)