Rebalance a Large Partitioned Table

This blog was co-written with Sumeet Keswani

Your organization is collecting data as fast as your Vertica cluster can store it, so fast that you need to add a few nodes to your cluster. After you add those nodes, Vertica must distribute the data across all the existing and new nodes for best performance. We call this operation rebalancing. (If you want to learn what goes on during rebalancing and how to optimize for rebalancing, read these great articles in our Developer Community.)

Vertica rebalances tables one partition at a time, per table per node. If your database includes large partitioned tables, rebalancing can be time consuming.  But fear not—this blog describes a technique that can speed up the rebalance process for large partitioned tables: Split your table into partitions and have Vertica rebalance those partitions in parallel.

Vertica would like to thank Ilia Gulman of Twingo for developing the technique described in this blog.

Splitting Large Partitioned Table by Partition

The following figure presents a high-level view of this technique. Given a large partitioned table that you need to rebalance, these steps are the major steps to take:

1. Split the large partitioned table into multiple tables by partition and move them to intermediate tables.
2. Run the rebalance in parallel for each partition on as many nodes as you need.
3. Reconstruct the original table from those partitions.
4. Drop the intermediate tables.

 

Are you ready to dive into the details?

The example table

This example uses a simple table. It’s not large, but it has five partitions:
=> SELECT DISTINCT table_name, partition_key,
SUM(ros_row_count)/1000000000 row_count_Bln FROM tables JOIN projections
ON (table_id = anchor_table_id) JOIN partitions USING (projection_id)
GROUP BY 1,2 ORDER BY 1;
table_name | partition_key | row_count_Bln
------------+---------------+---------------
t0 | 1 | 56
t0 | 2 | 56
t0 | 3 | 42
t0 | 4 | 56
t0 | 5 | 28
(5 rows)

Step 1: Move partitions to intermediate tables

Next, move the t0 partitions into several intermediate tables. If you have CREATE permissions on the schema and the target tables do not exist, MOVE_PARTITIONS_TO_TABLE creates the tables for you.

The following statement moves each individual partition into its own table. You may prefer to move a range of partitions into each of the intermediate tables, depending on how many partitions you have.
$ vsql -A -t -c "SELECT DISTINCT 'SELECT MOVE_PARTITIONS_TO_TABLE ('''||
table_name || ''',' || partition_key || ',' || partition_key || ',''' ||
table_name || '_p' || partition_key || ''');' FROM tables JOIN projections
ON (table_id = anchor_table_id) JOIN partitions USING (projection_id)
WHERE table_name = 't0' ORDER BY 1;"
SELECT MOVE_PARTITIONS_TO_TABLE ('t0',1,1,'t0_p1');
SELECT MOVE_PARTITIONS_TO_TABLE ('t0',2,2,'t0_p2');
SELECT MOVE_PARTITIONS_TO_TABLE ('t0',3,3,'t0_p3');
SELECT MOVE_PARTITIONS_TO_TABLE ('t0',4,4,'t0_p4');
SELECT MOVE_PARTITIONS_TO_TABLE ('t0',5,5,'t0_p5');
Because you moved all the partitions out of t0 into the t0_p* tables, table t0 is now empty.
$ SELECT COUNT(*) FROM t0;
COUNT
-------
0
(1 row)
Of course, the new tables (t0_p*) are usually much smaller than the original table.

Step 2: Create a view for querying the original table

You might need to query the large table during rebalance. To access that data, create a view as follows:
=> CREATE VIEW temp_t0 AS
SELECT * from t0_p1 UNION ALL
SELECT * from t0_p2 UNION ALL
SELECT * from t0_p3 UNION ALL
SELECT * from t0_p4 UNION ALL
SELECT * from t0_p5;

Step 3: Create the SQL for reconstructing the table

Later in this process, at Step 7, you’ll need to reconstruct the original table. But you need to save the partition keys to reconstruct the original table, so write the SQL statement to move the partitions back now, while you have the partition keys handy.
$ vsql -A -t -c "SELECT DISTINCT ' SELECT MOVE_PARTITIONS_TO_TABLE ('''||
table_name || '_p' || partition_key || ''',' || partition_key || ',' ||
partition_key || ',''' || table_name || ''');' FROM tables JOIN
projections ON (table_id = anchor_table_id) JOIN partitions USING
(projection_id) WHERE table_name = 't0' ORDER BY 1;" | tee -a revert.sql
SELECT MOVE_PARTITIONS_TO_TABLE ('t0_p1',1,1,'t0');
SELECT MOVE_PARTITIONS_TO_TABLE ('t0_p2',2,2,'t0');
SELECT MOVE_PARTITIONS_TO_TABLE ('t0_p3',3,3,'t0');
SELECT MOVE_PARTITIONS_TO_TABLE ('t0_p4',4,4,'t0');
SELECT MOVE_PARTITIONS_TO_TABLE ('t0_p5',5,5,'t0');

Step 4: Configure the REFRESH resource pool

All rebalance operations run in the REFRESH resource pool. Configure that resource pool to accommodate the right number of concurrently running rebalance processes. This example needs five processes:
=> ALTER RESOURCE POOL refresh PLANNEDCONCURRENCY 5;
ALTER RESOURCE POOL

Step 5: Verify that all nodes are permanent

If any of the Vertica nodes are ephemeral, change them to be permanent. Ephemeral nodes  do not participate in rebalancing. For rebalancing, all nodes must be permanent:
=> SELECT node_name, node_type FROM nodes;
node_name | node_type
-----------+-----------
node001 | PERMANENT
node002 | PERMANENT
node003 | PERMANENT
node004 | EPHEMERAL
(4 rows)
=> ALTER NODE node004 PERMANENT;
ALTER NODE
=> SELECT node_name, node_type FROM nodes;
node_name | node_type
-----------+-----------
node001 | PERMANENT
node002 | PERMANENT
node003 | PERMANENT
node004 | PERMANENT

 Step 6: Start the rebalance

Now you can start the rebalance operation. If you need to query the original table during the rebalance operation, query the view you created in Step 2.
=> SELECT START_REBALANCE_CLUSTER()
START_REBALANCE_CLUSTER
-------------------------
REBALANCING
(1 row)
To keep an eye on the rebalancing operation, use the tips in Monitor Rebalancing on the Vertica Knowledge Base.

Step 7: Reconstruct the table

After the rebalance has completed, you need to reconstruct the original table. This step requires that you move all the partitions back into t0. Luckily, you saved the partition keys in Step 3.

First, check to make sure that all partitions have been rebalanced:
=> SELECT table_name, separated_percent, transferred_percent
FROM rebalance_table_status WHERE is_latest;
table_name | separated_percent | transferred_percent
------------+-------------------+---------------------
t0_p2 | 100.00 | 100.00
t0_p4 | 100.00 | 100.00
t0_p5 | 100.00 | 100.00
t0_p1 | 100.00 | 100.00
t0_p3 | 100.00 | 100.00
(5 rows)
Now it’s safe to reconstruct the t0 table from the partitions using the SQL statements you created in Step 3:
=> SELECT MOVE_PARTITIONS_TO_TABLE ('t0_p1',1,1,'t0');
=> SELECT MOVE_PARTITIONS_TO_TABLE ('t0_p2',2,2,'t0');
=> SELECT MOVE_PARTITIONS_TO_TABLE ('t0_p3',3,3,'t0');
=> SELECT MOVE_PARTITIONS_TO_TABLE ('t0_p4',4,4,'t0');
=> SELECT MOVE_PARTITIONS_TO_TABLE ('t0_p5',5,5,'t0');

Step 8: Check the original table

Verify that table t0 is back to its original state and rebalanced across all the nodes:
=> SELECT DISTINCT table_name , partition_key,
SUM(ros_row_count)/1000000000 row_count_Bln
FROM tables JOIN projections ON (table_id = anchor_table_id)
JOIN partitions USING (projection_id) GROUP BY 1,2 ORDER BY 1;
table_name | partition_key | row_count_Bln
------------+---------------+---------------
t0 | 1 | 56
t0 | 2 | 56
t0 | 3 | 42
t0 | 4 | 56
t0 | 5 | 28
(5 rows)

Step 9: Drop the view and the intermediate tables

Table t0 is all set, so you don’t need the view anymore. Go ahead and drop it:
=> DROP VIEW temp_t0;
You don’t need the intermediate tables either, so drop them too:
$ vsql -A -t -c "SELECT DISTINCT 'DROP TABLE ' || table_name || '_p' || partition_key || ' ;'
FROM tables JOIN PROJECTIONS ON (table_id = anchor_table_id)
JOIN PARTITIONS USING (projection_id) WHERE table_name = 't0'
ORDER BY 1 ;"
DROP TABLE t0_p1 ;
DROP TABLE t0_p2 ;
DROP TABLE t0_p3 ;
DROP TABLE t0_p4 ;
DROP TABLE t0_p5 ;

Step 10: Verify the node dependencies

Make sure to check the node dependencies after the rebalance completes. cnt represents the number of segments on each node. For this simple example, cnt is 1.
=> SELECT GET_NODE_DEPENDENCIES();
GET_NODE_DEPENDENCIES
---------------------------------------------------------------
Deps:
0101 - cnt: 1
0110 - cnt: 1
1001 - cnt: 1
1010 - cnt: 1
(1 row)
For detailed information about node dependencies, see Check Node Dependency Status in the Vertica Knowledge Base.

For more information

For in-depth information about rebalancing in your Vertica database, see these two articles in the Vertica Knowledge Base:

• What Happens During Rebalancing
• Optimizing for Rebalancing