REBALANCE PARTITIONS

Syntax

REBALANCE PARTITIONS ON db_name [FULL] [FORCE]

Remarks

• db_name is the name of a SingleStoreDB database.

• REBALANCE_PARTITIONS restores redundancy by replicating any partitions with only one instance and then moving partitions around to ensure balance across all the leaves. Use RESTORE REDUNDANCY instead if you want to restore redundancy only, without rebalancing partitions.

• The FORCE option causes rebalancing to create empty partitions for any partition on the cluster which has neither a master nor replica partition attached. Use this option carefully. If an offline node has the only copy of a partition, then running REBALANCE with FORCE will cause the offline node’s partition to be orphaned. When the offline node comes back online the partition won’t be attached back to the cluster. It will have been replaced with an empty partition by REBALANCE. For some example cases where FORCE is useful, see Dealing with Failures.

• The FULL option takes effect when the number of partitions in the database is not divisible by the number of leaves. Suppose you have a cluster with 20 partitions and 8 leaves. When using the FULL option to rebalance the database, 2 partitions (20/8) would be evenly placed on each of the 8 leaves. The remaining 4 partitions would be placed on the leaves with a fewer number of master partitions. When not using the FULL option to rebalance the database, 2 partitions (20/8) would be evenly placed on each of the 8 leaves, but the remaining 4 partitions would be placed on the first 4 available leaves; the number of partitions on each leaf would be 3,3,3,3,2,2,2,2.

• To see a list of operations that the REBALANCE is going to execute, run EXPLAIN REBALANCE PARTITIONS.

• To see the status of a running rebalance command, run SHOW REBALANCE STATUS

• This command can be run on the master aggregator node, or a child aggregator node (see Node Requirements for SingleStoreDB Commands ).

• REBALANCE is an online operation, meaning that as it runs you can continue to read and write data in the database you are rebalancing. However, since REBALANCE often needs to move the location of a master partition to another leaf, write transactions may be temporarily blocked or even aborted during the rebalance 'promote' process as the partition is moved. If a transaction is aborted under these circumstances, the following error is generated:

  ER_ROLLED_BACK_TRANSACTION: The open transaction was aborted due to a concurrent failover or rebalance operation.

• To rebalance all the databases in a single query, use the REBALANCE ALL DATABASES command.

• This command causes implicit commits. See COMMIT for more information.

• This command cannot be run on system databases.

• Refer to the Permission Matrix for the required permission.

Example

REBALANCE PARTITIONS ON test;

Query OK, 1 row affected (9.80 sec)

Using REBALANCE PARTITIONS

It is not necessary to run REBALANCE PARTITIONS often as SingleStoreDB heals itself in most scenarios when leaf nodes go offline and come back online.

The main concern with REBALANCE PARTITIONS is running it when leaf nodes are offline.  REBALANCE PARTITIONS will assume those offline nodes are not coming back and start moving partitions to cover up for the offline nodes.

If the offline node is going to come back online reasonably quickly after some short-lived issue, then the rebalancing work will need to be "undone" once the node comes back online by moving data back to that node.  This is unnecessary work and time-consuming on a larger database.

The three most common use cases for REBALANCE PARTITIONS are:

1. When expanding the size of the cluster: ADD LEAF doesn't move data onto new leaves.  You need to run REBALANCE PARTITIONS to do that. The reason is that typically you add more than one leaf at a time and all new leaves should be added before running a single REBALANCE PARTITIONS to rebalance the data onto the new leaves.

2. When an offline leaf node is going to be offline for a long time; for example, the hardware is off for repair and will not be available for a prolonged time. Then it’s better to rebalance the master/replica partitions to account for this leaf being offline. In pairing mode, if a leaf is offline you will have no redundancy, as replica partitions are not automatically recreated.

3. When your database is running with synchronous replication and has "out of sync" replica partitions: synchronous replication does not failover automatically to “out of sync” replica partitions as this would cause data loss.  If you want to get online even at the cost of some lost data, then use REBALANCE PARTITIONS.. FORCE to force the failover to happen.