Pipelines System Variables

MemSQL Pipelines uses a few system variables that are either specific to an extractor or generic for the feature itself. You can see these variables and their default setting by executing the SHOW VARIABLES LIKE 'pipeline%'; statement:

memsql> SHOW VARIABLES LIKE '%pipeline%';
+---------------------------------------------+----------+
| Variable_name                               | Value    |
+---------------------------------------------+----------+
| pipelines_batches_metadata_to_keep          | 1000     |
| pipelines_deskew_batch_partitions_threshold | 0.750000 |
| pipelines_extractor_debug_logging           | OFF      |
| pipelines_extractor_get_offsets_timeout_ms  | 20000    |
| pipelines_extractor_idle_timeout_ms         | 120000   |
| pipelines_kafka_version                     | 0.8.2.2  |
| pipelines_max_concurrent                    | 50       |
| pipelines_max_concurrent_batch_partitions   | 0        |
| pipelines_max_errors_per_partition          | 1000     |
| pipelines_max_offsets_per_batch_partition   | 1000000  |
| pipelines_max_retries_per_batch_partition   | 4        |
| pipelines_stderr_bufsize                    | 65535    |
| pipelines_stop_on_error                     | ON       |
| pipelines_stored_proc_exactly_once          | ON       |
+---------------------------------------------+----------+
14 rows in set (0.00 sec)

The following table describes each of these variables, arranged by general configuration and extractor-specific configuration:

• General Pipelines System Variables
• Kafka Extractor System Variables

To learn how to set pipelines variables in your cluster, see the How to Set Pipelines System Variables section below.

General Pipelines System Variables

Kafka Extractor System Variables

How to Set Pipelines System Variables

The system variables associated with pipelines 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:

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

Setting Pipelines 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 the pipelines_max_offsets_per_batch_partition variable key with your desired variable:

memsql-ops memsql-update-config --set-global --key pipelines_max_offsets_per_batch_partition --value 500000 --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 '%pipelines_max_offsets_per_batch_partition%';
+-------------------------------------------+--------+
| Variable_name                             | Value  |
+-------------------------------------------+--------+
| pipelines_max_offsets_per_batch_partition | 500000 |
+-------------------------------------------+--------+
1 row in set (0.00 sec)

Setting Pipelines System Variables Using SET GLOBAL VARIABLE

The SET GLOBAL VARIABLE command can be used to change a pipelines global variable setting. Here the GLOBAL keyword implies that the variable setting is persistent across all current and future client connections on the server. If the GLOBAL keyword is not used and SET VARIABLE is executed instead, a variable setting applies only to your current client connection, not all other current and future client connections. Some system variables in MemSQL can only be set globally, including pipelines variables. Therefore, they must be set as GLOBAL variables because their values must be persisted across all client connections.

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 the pipelines_max_offsets_per_batch_partition variable name with your desired variable:

memsql> SET GLOBAL pipelines_max_offsets_per_batch_partition = 500000;
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 '%pipelines_max_offsets_per_batch_partition%';
+-------------------------------------------+--------+
| Variable_name                             | Value  |
+-------------------------------------------+--------+
| pipelines_max_offsets_per_batch_partition | 500000 |
+-------------------------------------------+--------+
1 row in set (0.00 sec)

Setting Pipelines 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 the pipelines_max_offsets_per_batch_partition variable name with your desired variable:

[server]
...
pipelines_max_offsets_per_batch_partition = 500000
...

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 '%pipelines_max_offsets_per_batch_partition%';
+-------------------------------------------+--------+
| Variable_name                             | Value  |
+-------------------------------------------+--------+
| pipelines_max_offsets_per_batch_partition | 500000 |
+-------------------------------------------+--------+
1 row in set (0.00 sec)

Was this Article Helpful?

Thanks For Your Feedback!