REBALANCE_SHARDS

Rebalances shard assignments in a subcluster or across the entire cluster in Eon Mode.

Eon Mode only

Rebalances shard assignments in a subcluster or across the entire cluster in Eon Mode. If the current session ends, the operation immediately aborts. The amount of time required to rebalance shards scales in a roughly linear fashion based on the number of objects in your database.

Important

If your database has multiple namespaces, REBALANCE_SHARDS rebalances the shards across all namespaces.

Run REBALANCE_SHARDS after you modify your cluster using ALTER NODE or when you add nodes to a subcluster.

Note

Vertica rebalances shards in a subcluster automatically when you:

Remove a node from a subcluster.
Add a new subcluster with the admintools command db_add_subcluster with the -s option followed by a list of hosts.

After you rebalance shards, you will no longer be able to restore objects from a backup taken before the rebalancing. (Full backups are always possible.) After you rebalance, make another full backup so you will be able to restore objects from it in the future.

This is a meta-function. You must call meta-functions in a top-level SELECT statement.

Behavior type

Volatile

Syntax

REBALANCE_SHARDS(['subcluster-name'])

Parameters

subcluster-name: The name of the subcluster where shards will be rebalanced. If you do not supply this parameter, all subclusters in the database rebalance their shards.

Privileges

Superuser

Examples

The following shows that the nodes in the in the newly-added analytics_subcluster do not yet have shard subscriptions:

=> SELECT subcluster_name, n.node_name, shard_name, subscription_state FROM
   v_catalog.nodes n LEFT JOIN v_catalog.node_subscriptions ns ON (n.node_name
   = ns.node_name) ORDER BY 1,2,3;

   subcluster_name    |      node_name       | shard_name  | subscription_state
----------------------+----------------------+-------------+--------------------
 analytics_subcluster | v_verticadb_node0004 |             |
 analytics_subcluster | v_verticadb_node0005 |             |
 analytics_subcluster | v_verticadb_node0006 |             |
 default_subcluster   | v_verticadb_node0001 | replica     | ACTIVE
 default_subcluster   | v_verticadb_node0001 | segment0001 | ACTIVE
 default_subcluster   | v_verticadb_node0001 | segment0003 | ACTIVE
 default_subcluster   | v_verticadb_node0002 | replica     | ACTIVE
 default_subcluster   | v_verticadb_node0002 | segment0001 | ACTIVE
 default_subcluster   | v_verticadb_node0002 | segment0002 | ACTIVE
 default_subcluster   | v_verticadb_node0003 | replica     | ACTIVE
 default_subcluster   | v_verticadb_node0003 | segment0002 | ACTIVE
 default_subcluster   | v_verticadb_node0003 | segment0003 | ACTIVE
(12 rows)

Rebalance the shards to analytics_subcluster, then confirm the rebalance was successful by querying the NODES system table:


=> SELECT REBALANCE_SHARDS('analytics_subcluster');
 REBALANCE_SHARDS
-------------------
 REBALANCED SHARDS
(1 row)

=> SELECT subcluster_name, n.node_name, shard_name, subscription_state FROM
   v_catalog.nodes n LEFT JOIN v_catalog.node_subscriptions ns ON (n.node_name
   = ns.node_name) ORDER BY 1,2,3;

   subcluster_name    |      node_name       | shard_name  | subscription_state
----------------------+----------------------+-------------+--------------------
 analytics_subcluster | v_verticadb_node0004 | replica     | ACTIVE
 analytics_subcluster | v_verticadb_node0004 | segment0001 | ACTIVE
 analytics_subcluster | v_verticadb_node0004 | segment0003 | ACTIVE
 analytics_subcluster | v_verticadb_node0005 | replica     | ACTIVE
 analytics_subcluster | v_verticadb_node0005 | segment0001 | ACTIVE
 analytics_subcluster | v_verticadb_node0005 | segment0002 | ACTIVE
 analytics_subcluster | v_verticadb_node0006 | replica     | ACTIVE
 analytics_subcluster | v_verticadb_node0006 | segment0002 | ACTIVE
 analytics_subcluster | v_verticadb_node0006 | segment0003 | ACTIVE
 default_subcluster   | v_verticadb_node0001 | replica     | ACTIVE
 default_subcluster   | v_verticadb_node0001 | segment0001 | ACTIVE
 default_subcluster   | v_verticadb_node0001 | segment0003 | ACTIVE
 default_subcluster   | v_verticadb_node0002 | replica     | ACTIVE
 default_subcluster   | v_verticadb_node0002 | segment0001 | ACTIVE
 default_subcluster   | v_verticadb_node0002 | segment0002 | ACTIVE
 default_subcluster   | v_verticadb_node0003 | replica     | ACTIVE
 default_subcluster   | v_verticadb_node0003 | segment0002 | ACTIVE
 default_subcluster   | v_verticadb_node0003 | segment0003 | ACTIVE
(18 rows)

If your database has multiple namespaces, you can run the following query to confirm the rebalance was successful across all namespaces:

=> SELECT nodes.subcluster_name AS subcluster_name, vs_namespaces.name AS namespace_name, vs_shards.shardname, vs_nodes.name AS nodename, vs_node_subscriptions.type, vs_node_subscriptions.state
FROM vs_node_subscriptions
INNER JOIN vs_shards
ON vs_node_subscriptions.shardoid=vs_shards.oid
INNER JOIN vs_shard_groups ON vs_shard_groups.oid=vs_shards.shardgroupoid
INNER JOIN vs_nodes ON vs_nodes.oid=vs_node_subscriptions.nodeoid
INNER JOIN nodes ON nodes.node_id=vs_nodes.oid
INNER JOIN vs_namespaces ON vs_namespaces.oid=vs_shard_groups.namespaceoid;
     subcluster_name   |   namespace_name   |  shardname  |       nodename       |   type    | state  
-----------------------+--------------------+-------------+----------------------+-----------+--------
  default_subcluster   |  default_namespace | replica     | v_verticadb_node0002 | SECONDARY | ACTIVE
  default_subcluster   |  default_namespace | replica     | v_verticadb_node0003 | SECONDARY | ACTIVE
  default_subcluster   |  default_namespace | replica     | v_verticadb_node0001 | PRIMARY   | ACTIVE
  default_subcluster   |  default_namespace | segment0001 | v_verticadb_node0002 | SECONDARY | ACTIVE
  default_subcluster   |  default_namespace | segment0001 | v_verticadb_node0001 | PRIMARY   | ACTIVE
  default_subcluster   |  default_namespace | segment0002 | v_verticadb_node0002 | PRIMARY   | ACTIVE
  default_subcluster   |  default_namespace | segment0002 | v_verticadb_node0003 | SECONDARY | ACTIVE
  default_subcluster   |  default_namespace | segment0003 | v_verticadb_node0003 | SECONDARY | ACTIVE
  default_subcluster   |  default_namespace | segment0003 | v_verticadb_node0001 | PRIMARY   | ACTIVE
  default_subcluster   |  ns1               | replica     | v_verticadb_node0002 | SECONDARY | ACTIVE
  default_subcluster   |  ns1               | replica     | v_verticadb_node0003 | SECONDARY | ACTIVE
  default_subcluster   |  ns1               | replica     | v_verticadb_node0001 | PRIMARY   | ACTIVE
  default_subcluster   |  ns1               | segment0001 | v_verticadb_node0002 | SECONDARY | ACTIVE
  default_subcluster   |  ns1               | segment0001 | v_verticadb_node0001 | PRIMARY   | ACTIVE
  default_subcluster   |  ns1               | segment0002 | v_verticadb_node0002 | PRIMARY   | ACTIVE
  default_subcluster   |  ns1               | segment0002 | v_verticadb_node0003 | SECONDARY | ACTIVE
  default_subcluster   |  ns1               | segment0003 | v_verticadb_node0003 | PRIMARY   | ACTIVE
  default_subcluster   |  ns1               | segment0003 | v_verticadb_node0001 | SECONDARY | ACTIVE
  default_subcluster   |  ns1               | segment0004 | v_verticadb_node0002 | SECONDARY | ACTIVE
  default_subcluster   |  ns1               | segment0004 | v_verticadb_node0001 | PRIMARY   | ACTIVE
  analytics_subcluster |  default_namespace | replica     | v_verticadb_node0005 | SECONDARY | ACTIVE
  analytics_subcluster |  default_namespace | replica     | v_verticadb_node0006 | SECONDARY | ACTIVE
  analytics_subcluster |  default_namespace | replica     | v_verticadb_node0004 | PRIMARY   | ACTIVE
  analytics_subcluster |  default_namespace | segment0001 | v_verticadb_node0005 | SECONDARY | ACTIVE
  analytics_subcluster |  default_namespace | segment0001 | v_verticadb_node0004 | PRIMARY   | ACTIVE
  analytics_subcluster |  default_namespace | segment0002 | v_verticadb_node0005 | PRIMARY   | ACTIVE
  analytics_subcluster |  default_namespace | segment0002 | v_verticadb_node0006 | SECONDARY | ACTIVE
  analytics_subcluster |  default_namespace | segment0003 | v_verticadb_node0006 | SECONDARY | ACTIVE
  analytics_subcluster |  default_namespace | segment0003 | v_verticadb_node0004 | PRIMARY   | ACTIVE
  analytics_subcluster |  ns1               | replica     | v_verticadb_node0005 | SECONDARY | ACTIVE
  analytics_subcluster |  ns1               | replica     | v_verticadb_node0006 | SECONDARY | ACTIVE
  analytics_subcluster |  ns1               | replica     | v_verticadb_node0004 | PRIMARY   | ACTIVE
  analytics_subcluster |  ns1               | segment0001 | v_verticadb_node0005 | SECONDARY | ACTIVE
  analytics_subcluster |  ns1               | segment0001 | v_verticadb_node0004 | PRIMARY   | ACTIVE
  analytics_subcluster |  ns1               | segment0002 | v_verticadb_node0006 | PRIMARY   | ACTIVE
  analytics_subcluster |  ns1               | segment0002 | v_verticadb_node0006 | SECONDARY | ACTIVE
  analytics_subcluster |  ns1               | segment0003 | v_verticadb_node0006 | PRIMARY   | ACTIVE
  analytics_subcluster |  ns1               | segment0003 | v_verticadb_node0004 | SECONDARY | ACTIVE
  analytics_subcluster |  ns1               | segment0004 | v_verticadb_node0005 | SECONDARY | ACTIVE
  analytics_subcluster |  ns1               | segment0004 | v_verticadb_node0004 | PRIMARY   | ACTIVE
(40 rows)

REBALANCE_SHARDS

Important

Note

Behavior type

Syntax

Parameters

Privileges

Examples

See also