Sign inTry Free

Change Memory Limits

On this page

Note

If you increase the amount of memory available to SingleStore, you must also ensure that your license covers the new amount  Refer to Determining capacity and usage for your license for more information.

Overview

The maximum_table_memory and maximum_memory engine variable values are based on a host's memory capacity at the time SingleStore is deployed. These values can be updated via SQL, Toolbox, or defined in the memsql.cnf file of each node. Note that these engine variables have no impact on licensing.

If Toolbox is used to optimize for NUMA, then these values are not automatically updated when a host's memory capacity changes. Toolbox sets a value of maximum_memory in the memsql.cnf file and, if memory is later added to the host, the memsqld process will not pick it up. Refer to Configuring SingleStore for NUMA for more information.

If Toolbox is not used to optimize for NUMA, the database engine sets the value of maximum_memory, which is based on the amount of memory at the time the memsqld process is started. If memory is later added to the host, the memsqld process will pick it up when it's restarted.

In some cases, you may wish to change the memory limit for each node after deploying SingleStore. If you opt to do so, please note the following:

  • Decrease the maximum_table_memory value before decreasing the maximum_memory value.

    If maximum_memory is set to a value below maximum_table_memory, the value of maximum_table_memory is automatically decreased as well, to the extent possible. If Alloc_table_memory  (viewable via SHOW STATUS EXTENDED LIKE 'Alloc_table_memory';) is sufficiently high, then maximum_table_memory may be set to an atypically higher value than 90% of maximum_memory, which is the default.

  • When decreasing the maximum_table_memory value, ensure that the value of the Alloc_table_memory  summary variable does not exceed the new maximum_table_memory value. If attempted, an error will occur and the value will not be set.

  • By default, maximum_memory is set to 90% of the physical memory on the host, or physical memory minus 10GB, whichever value is greater. SingleStore does not recommend exceeding either of these two limits when setting the maximum_memory value.

  • When decreasing the maximum_memory value, ensure that the value of the Total_server_memory summary variable (also viewable via SHOW STATUS EXTENDED LIKE 'Total_server_memory';) does not exceed the new maximum_memory value. If attempted, an error will occur and the value will not be set.

  • Do not set the maximum_memory value less than 1GB.

SingleStore recommends that each leaf node is allocated an equal amount of CPU and RAM. Refer to Calculating Memory Allocation per Host in a Cluster for more information.

Example

The following example illustrates how to update the values of the maximum_table_memory and maximum_memory variables after adding memory to an 8 GB (8192 MB) virtual machine, or “host,” with an instance of SingleStore non-Docker cluster-in-a-box, which consists of one Master Aggregator node and one leaf node.

Note that you must restart the affected nodes after increasing or decreasing these values.

  1. Use the following command to obtain the node IDs from the MEMSQL_ID column.

    sdb-admin list-nodes

  2. You can view the current maximum_table_memory and maximum_memory values of each node by running the following Toolbox commands.

    Master Aggregator 

    sdb-admin describe-node --property Variables.maximum_table_memory --memsql-id <Master_Aggregator_MEMSQL_ID>
    6014
    sdb-admin describe-node --property Variables.maximum_memory --memsql-id <Master_Aggregator_MEMSQL_ID>
    7038

    Leaf Node 

    sdb-admin describe-node --property Variables.maximum_table_memory --memsql-id <leaf_node_MEMSQL_ID>
    6014
    sdb-admin describe-node --property Variables.maximum_memory --memsql-id <leaf_node_MEMSQL_ID>
    7038

  3. If you later add an additional 2 GB (2048 MB) of RAM to this host, run the following command to update the memory limits and optimize other related settings.

    sdb-admin optimize

    This command will automatically reset these memory limits based on the host's memory capacity, and new values will be reflected for both maximum_table_memory and maximum_memory.

    Master Aggregator 

    sdb-admin describe-node --property Variables.maximum_table_memory --memsql-id <Master_Aggregator_MEMSQL_ID>
    7829
    sdb-admin describe-node --property Variables.maximum_memory --memsql-id <Master_Aggregator_MEMSQL_ID>
    8853

    Leaf Node 

    sdb-admin describe-node --property Variables.maximum_table_memory --memsql-id <leaf_node_MEMSQL_ID>
    7829
    sdb-admin describe-node --property Variables.maximum_memory --memsql-id <leaf_node_MEMSQL_ID>
    8853

  4. To pin the Master Aggregator and leaf node's maximum_memory at 8 GB (8192 MB), which will allow other applications to use the remaining memory, run the following commands.

    1. First, decrease the maximum_table_memory value:

      Master Aggregator 

      sdb-admin update-config --key maximum_table_memory --value 7209 --memsql-id <Master_Aggregator_MEMSQL_ID>

      Leaf Node 

      sdb-admin update-config --key maximum_table_memory --value 7209 --memsql-id <leaf_node_MEMSQL_ID>

    2. Next, decrease the maximum_memory value:

      Master Aggregator 

      sdb-admin update-config --key maximum_memory --value 8192 --memsql-id <Master_Aggregator_MEMSQL_ID>

      Leaf Node 

      sdb-admin update-config --key maximum_memory --value 8192 --memsql-id <leaf_node_MEMSQL_ID>

  5. Restart the cluster.

    sdb-admin restart-node --all

View Variable Values

You can confirm that the nodes reflect these new values via a variety of methods.

Query the Information Schema

USE information_schema
SELECT * FROM mv_nodes;
+----+-----------+------+------+---------------+---------------------+
| ID | IP_ADDR   | PORT | TYPE | MAX_MEMORY_MB | MAX_TABLE_MEMORY_MB |
+----+-----------+------+------+---------------+---------------------+
|  2 | 127.0.0.1 | 3307 | LEAF |          8192 |                7209 |
|  1 | 127.0.0.1 | 3306 | MA   |          8192 |                7209 |
+----+-----------+------+------+---------------+---------------------+

Note: Columns have been removed to highlight the max_memory_mb and the max_table_memory_mb columns.

Query the Variables

SELECT @@global.maximum_table_memory;
+-------------------------------+
| @@global.maximum_table_memory |
+-------------------------------+
|                          7209 |
+-------------------------------+
SELECT @@global.maximum_memory;
+-------------------------+
| @@global.maximum_memory |
+-------------------------+
|                    8192 |
+-------------------------+

Use Toolbox

Master Aggregator 

sdb-admin describe-node --property Variables.maximum_table_memory --memsql-id <Master_Aggregator_MEMSQL_ID>
7209
sdb-admin describe-node --property Variables.maximum_memory --memsql-id <Master_Aggregator_MEMSQL_ID>
8192

Leaf Node 

sdb-admin describe-node --property Variables.maximum_table_memory --memsql-id <leaf_node_MEMSQL_ID>
7209
sdb-admin describe-node --property Variables.maximum_memory --memsql-id <leaf_node_MEMSQL_ID>
8192

View the memsql.cnf File

Note

Node configuration folders typically reside in /var/lib/memsql/.

As the node subfolder names are GUIDs, you can determine which subfolder corresponds to which node by viewing the port number in the memsql.cnf file of each subfolder.

Refer memsql.cnf for more information.

Master Aggregator 

cat /var/lib/memsql/<Master_Aggregator_MEMSQL_ID>/memsql.cnf | grep maximum_table_memory
maximum_table_memory = 7209
cat /var/lib/memsql/<Master_Aggregator_MEMSQL_ID>/memsql.cnf | grep maximum_memory
maximum_memory = 8192

Leaf Node 

cat /var/lib/memsql/<leaf_node_MEMSQL_ID>/memsql.cnf | grep maximum_table_memory
maximum_table_memory = 7209
cat /var/lib/memsql/<leaf_node_MEMSQL_ID>/memsql.cnf | grep maximum_memory
maximum_memory = 8192

Related Resources

Last modified: March 8, 2024

Was this article helpful?

On this page

Was this article helpful?