Outdated Version

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

Cluster Expansion Steps min read

Cluster Expansion Steps

If your MemSQL workload changes over time, MemSQL Ops makes it easy to efficiently scale cluster size to meet increasing performance and sizing demands. You can add more leaf 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 four partitions per leaf and 16 partitions total. If only two leaf nodes are added that totals to 6 leaves; 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 leaves are added, 16 / 8, then there can be an equal number of partitions on each leaf (two).

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

Follow the steps below to add more leaf nodes to your cluster:

Confirm leaf configurations are the same across new and existing leaves.
- Sizing (RAM, Cores)
- Configuration Best Practices (ulimit settings, THP settings, process limits, vm settings)
Generate Cluster Report prior to adding nodes. This will be useful for troubleshooting in case any issues arise during the cluster expansion process.
Check memsql-ops version on all existing leaves (upgrade optional) to ensure you are installing the correct memsql-ops version on the new hosts. You can get the memsql-ops version across all hosts by running:
```
memsql-ops agent-list
```
Un-compress the ops tar file and run the install.sh script on each new node. View the Installation Guide for installation options.
```
sudo ./install.sh
```
Check and compare the settings.cnf file on a new host (that will become a leaf) and an old host (that is a leaf) to make sure they are the same.
From each new host, set each new host to follow the primary ops agent. View the FOLLOW topic for more information.
```
memsql-ops follow -h PRIMARY_AGENT_HOST
```
- Check to see if the new leaves are correctly following the master. You should see the new hosts when running memsql-ops agent-list.
Deploy MemSQL from Master Aggregator
- When deploying from the Master Aggregator, you must specify the Agent ID of the host you want to deploy a MemSQL node on. To get the Agent ID of all hosts, use the agent-list command from step 3.
- If the default port 3306 is already being used by a MemSQL node, you must specify another port with -P PORT.
- If you are running in high availability (i.e. your Master Aggregator is set to --redundancy-level 2) you must specify an availability group for your new leaf nodes, --availability-group {1,2}.
- For more information, see the MEMSQL-DEPLOY documentation.
```
memsql-ops memsql-deploy -a AGENT_ID -r {leaf,aggregator,master}
```
- After adding all new nodes, they should now all be visible when running memsql-ops memsql-list.
Update the root password from Master so all new nodes have a root password. When using MemSQL 6.5 and MemSQL Ops 6.0.11 or later, this is an online operation for both aggregators and leaves. Previous versions required you to remove and re-add each leaf in your cluster.
- All node’s memsql_id can be found by running memsql-ops memsql-list on any host.
- See the MEMSQL-UPDATE-ROOT-PASSWORD topic.
```
memsql-ops memsql-update-root-password -p PASSWORD memsql_id
```
Skip this step if you are on MemSQL v6 or higher. On a new node, connect to the database and run SHOW USERS;. You should see two anonymous users, ''@'localhost' and ''@'127.0.0.1'. You will want to drop these users to prevent someone logging into your database anonymously by running:
```
DROP USER ''@'localhost';
DROP USER ''@'127.0.0.1';
DROP USER 'root'@'localhost';
DROP USER 'dashboard'@'%';
DROP USER 'dashboard'@'localhost';
```
- Run SHOW USERS; again to check if the anonymous users have been successfully removed.
- See Configuring MemSQL User Accounts for more detail.
On any aggregator, connect to the database and run SHOW LEAVES; to make sure all leaves were properly added.
On any host, run memsql-ops memsql-list to make sure all new leaves are ops-aware. You should be able to see all new aggregator or leaf nodes added.
On the Master Aggregator, enter the SQL interface and run REBALANCE PARTITIONS; on all databases.
Check memory and disk usage in the MemSQL Ops user interface.
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;

Was this Article Helpful?

Need help or have suggestions? Go to SingleStore Forums

Cluster Expansion Steps min read

Cluster Expansion Steps

Sorry About That