Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Redistribute data across nodes

My current data usage looks like:
dev=> select * from disk_storage;
      node_name      |                    storage_path                    | storage_usage | rank | throughput | latency | storage_status | disk_block_size_bytes | disk_space_used_blocks | disk_space_used_mb | disk_space_free_blocks | disk_space_free_mb | disk_space_free_percent
---------------------+----------------------------------------------------+---------------+------+------------+---------+----------------+-----------------------+------------------------+--------------------+------------------------+--------------------+-------------------------
 v_armdevdb_node0001 | /data/ARMDEVDB/v_armdevdb_node0001_catalog/Catalog | CATALOG       |    0 |          0 |       0 | Active         |                  4096 |               29380292 |             114766 |                1104914 |               4316 | 3%
 v_armdevdb_node0001 | /data/ARMDEVDB/v_armdevdb_node0001_data            | DATA,TEMP     |    0 |          0 |       0 | Active         |                  4096 |               29380292 |             114766 |                1104914 |               4316 | 3%
 v_armdevdb_node0002 | /data/ARMDEVDB/v_armdevdb_node0002_catalog/Catalog | CATALOG       |    0 |          0 |       0 | Active         |                  4096 |               28109057 |             109801 |                2376149 |               9281 | 7%
 v_armdevdb_node0002 | /data/ARMDEVDB/v_armdevdb_node0002_data            | DATA,TEMP     |    0 |          0 |       0 | Active         |                  4096 |               28109057 |             109801 |                2376149 |               9281 | 7%
 v_armdevdb_node0004 | /data/ARMDEVDB/v_armdevdb_node0004_catalog/Catalog | CATALOG       |    0 |          0 |       0 | Active         |                  4096 |                2451305 |               9575 |               28033901 |             109507 | 91%
 v_armdevdb_node0004 | /data/ARMDEVDB/v_armdevdb_node0004_data            | DATA,TEMP     |    0 |          0 |       0 | Active         |                  4096 |                2451305 |               9575 |               28033901 |             109507 | 91%
(6 rows)
How can I rebalance the disk usage?


Comments

  • You can redistribute data using the rebalance_cluster function, or the start_rebalance_cluster() if you want to do so asynchronously. However, if you had all 4 nodes in the cluster from initial deployment and got the skewed distribution seen in the disk_storage table, you may want to check you projection segmentation columns and use columns that are high cardinality (or use db designer);

    /Sajan
  • Hmm, database designer always fails to run to completion, hasn't run properly for me for a long while...

    The rebalance_cluster() function also failed to complete, probably due to lack of disk space.

    Apart from dumping the database to files and restarting from scratch is there any hope for rebalancing in this case?
  • You can look at the skew per projection using the following query:
     select node_name,projection_name,sum(used_bytes) from projection_storage group by node_name,projection_name order by projection_name,sum(used_bytes) desc;

    If you have a lot of non-superprojections, and you can drop/ recreate them without stopping down user queries significantly (or get a maintenence downtime), get the projection creation schema for these projections using:
    select export_objects('','<projection_name>');

    Then, recreate the projection with a modified segmentation clause that distributes the data evenly, and dont forget to use the MODULARHASH or the HASH function in the segmentation clause.

    You should be able to iterate through the projections, and gradually free up more space in the heavily loaded node.

    Hope that helps.

    /Sajan
  • Just to add,

    If you see data skew in projections and you want to do rebalance only for few projections. Then this is possible in Vertica 7.1 Dragline.

    REBALANCE_DATA_IN_VERTICA 7.1

    Hope this helps.
    NC
  • Thanks for the help!

    I am in a bit of a pickle here I think, I am on Vertica 6.1.2-0 with no real chance of upgrade at the moment.

    The two largest projections in the entire database are super projections, they are an order of magnitude larger than others.
    > select node_name,projection_name,sum(used_bytes) from projection_storage where projection_name like 'project%' group by node_name,projection_name order by projection_name,sum(used_bytes)  desc ;      node_name      |     projection_name      |     sum
    ---------------------+--------------------------+--------------
     v_armdevdb_node0001 | project_results_node0001 | 103241448221
     v_armdevdb_node0002 | project_results_node0002 | 102344885480
     v_armdevdb_node0004 | project_results_node0004 |            0
    (3 rows)
    > select projection_name,projection_basename,anchor_table_name,node_name,is_prejoin,verified_fault_tolerance,is_up_to_date,has_statistics,is_segmented,is_super_projection from projections where anchor_table_name = 'project_results';     projection_name      |   projection_basename    | anchor_table_name |      node_name      | is_prejoin | verified_fault_tolerance | is_up_to_date | has_statistics | is_segmented | is_super_projection
    --------------------------+--------------------------+-------------------+---------------------+------------+--------------------------+---------------+----------------+--------------+---------------------
     project_results_node0002 | project_results_node0002 | project_results   | v_armdevdb_node0002 | f          |                        1 | t             | f              | f            | t
     project_results_node0001 | project_results_node0001 | project_results   | v_armdevdb_node0001 | f          |                        1 | t             | f              | f            | t
     project_results_node0004 | project_results          | project_results   | v_armdevdb_node0004 | f          |                        2 | f             | f              | f            | t
    (3 rows)
    > drop projection project_results_node0002;ROLLBACK:  Projection project_results_node0002 cannot be dropped because K-safety would be violated
    DETAIL:  Current K-safety value = 1
    HINT:  Use mark_design_ksafe() to decrement K-safety value
    If I create another super projection then It will probably fail to be created on the two overloaded nodes because they have no disk space.

    Not sure where to go now, any ideas?


  • Firstly, you drop the base projection project_results by doing DROP PROJECTION project_results;
    as you see it is not refreshed.

    Follow Sajan's notes. Create segmented Super projection and drop the unsegmented. You should be good.

    Sashi

    ~ Vertica Support

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.