Sign inTry Free

Engine Variables

On this page

Engine variables are configuration settings that control various aspects of the database's behavior. Engine variables fine-tune the performance and behavior of SingleStoreDB to best suit specific workload requirements.

SingleStoreDB defines engine variables as either sync or non-sync. Sync variables are cluster-wide variables that you set on the master aggregator. When you set the variable, the value is propagated across the cluster to all relevant nodes. The value is persisted in the internal cluster database. Non-sync variables are set locally, independently on each node. The value does not persist in the engine.

Non-sync variables can be persisted in the engine via the memsql.cnf file which is a part of the SingleStoreDB Toolbox.

Engine variables can be set globally or per session in scope. Global engine variables affect all connections to the database. If a global engine variable value is changed, then the change affects all existing and subsequent connections. Session engine variables affect the individual client connections they are set on. Session engine variables can also be set globally. After setting a session variable globally via the SET GLOBAL command, client connection pools should be flushed to make the new value effective throughout a cluster. Engine variables that can be set per session and globally are denoted in the Description column of the list of variables table by the verbiage This is a session variable that can also be set globally. Otherwise, the engine variable can only be set globally.

Sync

Non-Sync

Global

Sync global variables are cluster-wide engine variables that are set on the master aggregator and their values are propagated across the cluster to all relevant nodes. Their values are effective for the current connection to the node and any other existing or subsequent connections. The values persist across restarts.

Non-sync global engine variables are set locally and independently on each node. Their values are effective for the current connection to the node and any other existing or subsequent connections. The values do not persist across restarts.

Session

Sync session variables are cluster-wide engine variables that are set on the master aggregator and their values are propagated across the cluster to all relevant nodes if set with SET GLOBAL. If set with SET (or SET SESSION), the values will only change the copy if the variable is local to the connection. Their values are specific to each session and do not affect other connections.

Non-sync session engine variables are set locally and independently on each node. Their values are specific to each session and do not affect other connections.

The following sections discuss these 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 all aggregators, all leaves, or both. The nodes affected by your update depend on the variable’s type and the command you use to set the variable. Your update takes effect immediately on the affected nodes (unless the variable’s scope is Session that can also be set globally) and persists if the affected nodes are restarted.

Types of Sync Variables

There are three types of sync variables:

  • Variables that can sync to all aggregators.

  • Variables that can sync to all leaves.

  • Variables that can sync to all nodes (leaves and aggregators).

The last sentence of the Description column in the list of sync variables specifies each variable’s type.

Sync Variable Scopes

Every variable has one of the following scopes.

  • Global. When you set a global variable, its value is effective for your current connection to the node and any other existing or subsequent connections, initiated by any user.

  • Session that can also be set globally. When set for the session, the variable’s value is effective for your current connection to the node. When set globally, this variable’s value is NOT effective for your current connection to the node but is effective for any other existing or subsequent connections to the node, initiated by any user.

Important

SingleStore recommends using a global setting for session variables whose values must be propagated to leaf nodes from the aggregator nodes on which the variable is set. For session variables that are only referenced by the aggregator nodes, the SET SESSION statement can be used instead.

Most sync variables have global scope. Those that instead have the scope session that can also be set globally are indicated in the Description column in the list of sync variables.

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

Setting Sync Variables

Sync variables with global or session scope can be set globally in the following ways.

  • Using a SingleStoreDB client, run one of the following three commands: run SET CLUSTER (the equivalent of SET GLOBAL) to sync a variable to all nodes or to all aggregators; run SET AGGREGATOR to sync a variable to all aggregators; run SET LEAF to sync a variable to all leaves.Connecting to SingleStore

    Important

    When you set a session variable using SET CLUSTER (or its equivalent SET GLOBAL) or SET AGGREGATOR, you need to additionally run FLUSH CONNECTION POOLS on every aggregator node in the cluster. This will clear the session variable value saved earlier in the connection pools and enable the aggregator and leaf nodes to use the new value.

  • To set the variable’s value to take effect when the node starts, run the following command at the Linux command line to update the node’s configuration.

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

Caution

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.

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.

Non-Sync Variables

Non-sync variables 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)

Most non-sync variables are settable to take effect both when a node starts and while the node is running. Exceptions are indicated in the Exceptions to When Variable can be Set column in the list of non-sync variables.

Non-Sync Variable Scopes

Every non-sync variable has one of the following scopes.

  • Global. When you set a global variable, its value is effective for your current connection to the node and any other existing or subsequent connections, initiated by any user.

  • Session that can also be set globally. When set for the session, the variable’s value is effective for your current connection to the node. When set globally, this variable’s value is NOT effective for your current connection to the node but is effective for any other existing or subsequent connections to the node, initiated by any user.

Important

SingleStore recommends using a global setting for session variables whose values must be propagated to leaf nodes from the aggregator nodes on which the variable is set.

For session variables that are only referenced by the aggregator nodes, the SET SESSION statement can be used instead.

Most non-sync variables have global scope. Those that instead have the scope session that can also be set globally are indicated in the Description column in the list of non-sync variables.

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

Setting Non-Sync Variables

Non-sync variables with global or session scope can be set globally in the following ways.

  • SET GLOBAL sets a non-sync variable globally and the variable’s value will not persist if the node is restarted.

    Important

    When you set a session variable using the SET GLOBAL command, you need to additionally run FLUSH CONNECTION POOLS on every aggregator node in the cluster. This will clear the session variable value saved earlier in the connection pools and enable the aggregator and leaf nodes to use the new value.

  • To set the variable’s value to take effect when the node starts, run the following command to update the node’s configuration.

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.

Caution

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)

Last modified: October 24, 2023

Was this article helpful?

On this page

Was this article helpful?