On this page
Rebalances the partitions for a database.
REBALANCE PARTITIONS ON db_name [FULL] [FORCE]
db_is the name of a SingleStoreDB database.
REBALANCE_restores redundancy by replicating any partitions with only one instance and then moving partitions around to ensure balance across all the leaves.
RESTORE REDUNDANCYinstead if you want to restore redundancy only, without rebalancing partitions.
FORCEoption 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
FORCEwill 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
For some example cases where
FORCEis useful, see Dealing with Failures.
FULLoption 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
FULLoption 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
FULLoption 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
REBALANCEis 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 ).
REBALANCEis an online operation, meaning that as it runs you can continue to read and write data in the database you are rebalancing.
REBALANCEoften 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:
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.
REBALANCE PARTITIONS ON test;
Query OK, 1 row affected (9.80 sec)
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.
The three most common use cases for REBALANCE PARTITIONS are:
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 PARTITIONSto rebalance the data onto the new leaves.
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.
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.to force the failover to happen.
Last modified: May 11, 2023