dev=> select * from disk_storage;How can I rebalance the disk usage?
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)
Redistribute data across nodes
My current data usage looks like:
+1You 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
/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?
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?
+1You 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
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
+2Just 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
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.
Not sure where to go now, any ideas?
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 violatedIf I create another super projection then It will probably fail to be created on the two overloaded nodes because they have no disk space.
DETAIL: Current K-safety value = 1
HINT: Use mark_design_ksafe() to decrement K-safety value
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
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
Sign up
Already have an account? Login
Welcome to the Rocket Forum!
Please log in or register:
Employee Login | Registration Member Login | RegistrationEnter your E-mail address. We'll send you an e-mail with instructions to reset your password.
77 4th Avenue
Waltham, MA 02451 USA
