REBALANCE PARTITIONS

Syntax

REBALANCE PARTITIONS ON db_name [FULL] [FORCE] [KILL] 

Remarks

• db_name is the name of a SingleStore 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 causes the placement algorithm to take into account partitions from other databases when deciding where to place the partitions.

• KILL prevents persistent long running write queries from blocking a clustering option for an arbitrarily long time (for example, a scale up or scale down or even an upgrade). Auto-rebalances automatically use the KILL mode if the auto-rebalance fails to run after three attempts.

• 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 SingleStore 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. Refer to COMMIT for more information.

• This command cannot be run on system databases.

• Refer to the Permission Matrix for the required permission.

Example - REBALANCE PARTITIONS

The REBALANCE PARTITIONS command does an individual rebalance and rebalances a database without taking into account other databases that may be on the same system.

REBALANCE PARTITIONS ON test;

Query OK, 1 row affected (9.80 sec)

Example - REBALANCE PARTITIONS … FULL

The REBALANCE PARTITIONS … FULL command does a global rebalance taking into account other databases that may be on the system. 

Consider a system with two leaves and two databases, each of which has 9 partitions; thus, there are 18 partitions total. 

Running REBALANCE PARTITIONS on both databases may result in 10 partitions on one node and 8 partitions on the other node. The two rebalances run individually, and each rebalance may place 5 partitions on one node and 4 on the other node. 

Running REBALANCE PARTITIONS … FULL will take into account both databases and will place 9 partitions on each node.

REBALANCE PARTITIONS ON test FULL;

Query OK, 1 row affected (14.50 sec)

Using REBALANCE PARTITIONS

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

The rebalancing process automatically tries to minimize using up more space. However, there is no specific rule on using additional space. For a node in the cluster, rebalancing may keep the number of partitions the same (so no additional space is used) or lower the number of partitions (so less space is used) or increase the number of partitions (so more space is used).

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.