Back
Outdated Version

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

/Cluster Management/

Expanding a Cluster min read

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 or child aggregator nodes to your MemSQL cluster by following the steps below.

Alert

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.

Also, 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 nodes to your cluster:

  1. Match and confirm that the memsql.cnf configuration files for new nodes are the same as existing nodes.

  2. Generate Cluster Report prior to adding nodes. This will be useful for troubleshooting in case any issues arise during the cluster expansion process.

  3. Check memsql-ops version on all existing hosts (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

  4. 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

  5. Check and compare the settings.cnf file on a new host (that will become a leaf or aggregator) and an old host (that is an existing leaf or aggregator) to make sure they are the same.

  6. 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.

  7. 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 adding leaf nodes, and 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.

  8. Run memsql-list on any host to identify the new nodes and pass the MEMSQL_ID value for those nodes into memsql-restart. You must restart each new node to allow the memsql.cnf configuration settings to take effect.

memsql-ops memsql-list
memsql-ops memsql-restart MEMSQL_ID

  1. Update the root password from Master so all new nodes have a root password.

    memsql-ops memsql-update-root-password -p PASSWORD MEMSQL_ID

    Note: This operation will remove and re-add the leaf to the cluster. This will cause certain connections to fail and should be considered an offline operation.

  2. 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';

  3. To confirm all nodes are properly added:

    • If you added leaves, connect to any aggregator and run SHOW LEAVES;
    • If you added aggregators, connect to any aggregator and run SHOW AGGREGATORS;

  4. On any host, run memsql-ops memsql-list to make sure all new leaves and aggregators are Ops aware. You should be able to see all new aggregator or leaf nodes added.

  5. If you added leaf nodes, enter the SQL interface on the Master Aggregator and run REBALANCE PARTITIONS; on all databases. You do not need to perform this step if you added aggregators.

  6. Check memory and disk usage in the MemSQL Ops user interface.

  7. If you added leaves, enter the SQL interface on any aggregator and output SHOW CLUSTER STATUS into a file you can examine to make sure partitions are evenly distributed. If you added aggregators, run a simple test query on the new aggregator to confirm the aggregator is functioning.

  8. If you added leaf nodes, check for data skew by running the following query 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;
Need help or have suggestions? Go to SingleStore Forums