Outdated Version

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

Cluster Expansion Steps min read

Info

If you are managing your cluster with MemSQL Ops, go here instead.

If your MemSQL workload changes over time, the MemSQL management tools make it easy to efficiently scale cluster size to meet increasing performance and sizing demands. You can add more nodes to your MemSQL cluster by following the steps below.

Alert

Be aware when adding leaf nodes:

Once a database has been created, you cannot change the number of partitions the database was originally created with.

This means:

To maintain the current number of database partitions per leaf, you must export all data, delete the database, recreate the database and all tables, and load data back into the database’s tables.
To maintain an equal number of partitions per leaf, the new total number of leaves must be divisible by the total number of partitions. For example, a four-leaf cluster has has been created with four partitions per leaf (16 partitions total). If only two leaf nodes are added, the total number of leaves is six; however, 16 / 6 is not an integer, meaning there cannot be an equal number of partitions on each leaf. This will cause data skew. If four additional leaves are added instead, 16 / 8, which means there can be an equal number of partitions on each leaf (two).

Also, if you keep the existing number of partitions, then you don’t have to do any reloading; only rebalancing would be required.

Before you begin

Confirm host machine and leaf configurations are the same across new and existing leaves. Configurations for sizing (RAM, cores) and OS tuning (ulimit settings, THP settings, etc.) should match to ensure uniform query performance and cluster health.

See System Requirements and Recommendations for more details.

It’s also important to consider what non-sync engine variables have been modified when increasing your cluster size. Any modified non-sync variable settings will not be automatically applied to new nodes when they are added to the cluster; therefore, when scaling your cluster, each new node non-sync variables must be updated with the same values as other nodes.

Also, generate cluster report using the memsql-report collect command prior to adding nodes. This report will be useful for troubleshooting in case any issues arise during the cluster expansion process.

Add more leaf nodes to your cluster

Log into the host machine that has the MemSQL management tools installed on it. You will use this host machine to add new nodes to your cluster.
If your new nodes will reside on a new host machine (or set of machines) that wasn’t previously part of the cluster, then you must register the host machine(s). If you use a certificate to SSH into your machines, you can use it here with the optional --identity-file flag and memsql-toolbox-config will use it when connecting to the host machine.
```
memsql-toolbox-config register-host --host <hostname|IP>
```
Download and install the memsql-server package (containing the MemSQL engine and memsqlctl) onto the target host machine(s).
```
memsql-deploy install --host <hostname|IP>
```
Note If you added more than one host machine to your cluster, you can pass in additional --host flags (e.g. --host 172.17.0.1 --host 172.17.0.2).
Run memsql-admin create-node. Specify the hostname that you registered earlier and set a secure password for the root database user. If the default port of 3306 is already being used by a MemSQL node on the target host machine, specify another port with -p PORT.
```
memsql-admin create-node --host <hostname|IP> --password <secure_pass>
```
Assign the new node a role such as aggregator or leaf using either add-aggregator or add-leaf, respectively. If you are running in high availability (i.e. your master aggregator is set to --redundancy-level 2), you can specify an availability group for your new leaf nodes, --availability-group {1,2} or let it be auto-assigned to the smaller of the two groups.
```
memsql-admin add-leaf --memsql-id <MemSQL_ID>
```
Verify the new node has been added successfully, by running memsql-admin list-nodes again.

Redistribute data across cluster

Once you have successfully added your new nodes, connect to the host machine running the master aggregator. The memsql-client package contains a client application that you can use to connect to your database by simply running memsql at the command prompt.
As a best practice, run the SNAPSHOT DATABASE command to bring transaction logs up to date. As a general rule, taking a snapshot is helpful before any maintenance/clustering operation where data is being moved or recovered as it compresses files by combining multiple statements into one.
Run REBALANCE PARTITIONS; on all databases.
Check memory and disk usage through MemSQL Studio.
On any aggregator, enter the SQL interface and output SHOW CLUSTER STATUS into a file you can examine to make sure partitions are evenly distributed.

Check for data skew by running the following query in the SQL interface on any aggregator:

SELECT
  DATABASE_NAME,
  TABLE_NAME,
  FLOOR(AVG(ROWS)) AS avg_rows,
  ROUND(STDDEV(ROWS)/AVG(ROWS),3) * 100 AS row_skew,
  FLOOR(AVG(MEMORY_USE)) AS avg_memory,
  ROUND(STDDEV(MEMORY_USE)/AVG(MEMORY_USE),3) * 100 AS memory_skew
  FROM INFORMATION_SCHEMA.TABLE_STATISTICS
  GROUP BY 1, 2
  HAVING SUM(ROWS) > 10000
  ORDER BY row_skew DESC;

For more information about detecting and measuring data skew, see the Data Skew topic.

Was this Article Helpful?