Calculating Memory Allocation per Host in a Cluster
In disaster recovery (DR) the secondary cluster should have slightly more memory than the primary. While fewer user queries may be run on the secondary cluster, it will still need to accommodate clustering operations like REBALANCE PARTITIONS , as well as large deletes. Deleting rows from a table temporarily uses more memory since rows are first marked for deletion, then deleted by the background garbage collector.
Allocate enough memory per leaf and per aggregator in the secondary cluster to store the same amount of data as the primary. Rowstore tables will use more memory, and columnstore tables will use more disk. You should calculate all memory currently used for the nodes in the primary cluster, then divide that by the count of nodes in the secondary cluster.
The steps are as follows:
1. Calculate memory usage per node
avg_aggregator_usage = sum(memory used for aggregators in primary cluster) / count of aggregators in secondary cluster
avg_leaf_usage = sum(memory use for leaves in primary cluster) / count of leaves in secondary cluster
2. Total the memory needs per host
If there is one aggregator and one leaf, then allocate avg_aggregator_usage + avg_leaf_usage.
If there are two leaves then allocate 2 * avg_leaf_usage.
3. Calculate memory usage in the primary cluster
You can find the total memory usage from the SHOW STATUS EXTENDED command in the client, and grep for Total_server_memory which indicates the usage.
Run this command to see the current total memory usage for all nodes:
SELECT * FROM INFORMATION_SCHEMA.MV_GLOBAL_STATUS WHERE VARIABLE_NAME = 'Total_server_memory';
Assume it returns the result as:
Memory use of nodes: 10.0.3.202:3306 Total_server_memory 464.0 MB Memory use of nodes: 10.0.3.101:3306 Total_server_memory 460.0 MB Memory use of nodes: 10.0.3.303:3306 Total_server_memory 462.5 MB Memory use of nodes: 10.0.3.202:3307 Total_server_memory 1459.0 MB Memory use of nodes: 10.0.3.101:3307 Total_server_memory 1458.9 MB Memory use of nodes: 10.0.3.303:3307 Total_server_memory 1475.0 MB Memory use of nodes: 10.0.3.404:3306 Total_server_memory 1435.0 MB Memory use of nodes: 10.0.3.404:3307 Total_server_memory 1448.9 MB
4. Sum by role
Next total the memory consumption by role. In the example above, the first three lines are for the master aggregator and two child aggregators. The rest are leaves.
Aggregator memory consumption: 464 + 460 + 463 = 1387
Leaf memory consumption: 1459 + 1459 + 1475 + 1435 + 1448 = 7277
5. Divide by new role count
Finally, divide this number by the count of each role in the secondary cluster.
If you were to reduce to only one master aggregator (MA), one child aggregator (CA), and four leaves then:
Aggregators: 1387 / 2 = 694
Leaf nodes: 7277 /4 = 1820
6. Calculate node usage per host
If each host is going to have two nodes then the minimum allocations to support current usage would be:
MA + leaf: 694 + 1820 = 2514
CA + leaf: 694 + 1820 = 2514
2 * leaf: 1820 + 1820 = 3640
In this example, these are the minimum memory allocations per leaf in the secondary cluster to support current usage. You are likely to need more room for data set growth, as well as to accommodate query overhead and unexpected spikes in usage.