The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!
Resize cluster 3 => 1, unused buddies
After resizing a cluster from multiple nodes that have ksafe=1 down to a single node with ksafe=0 there are still all the "_b1" buddy projections around.
Does Vertica recommend a way to clean these out? As I understand they are just duplicated super projections at this point.
0
Comments
I think you'll need to drop the b1 projections yourself, but it's really easy to automate the drops..
Example:
dbadmin=> SELECT current_fault_tolerance FROM system; current_fault_tolerance ------------------------- 1 (1 row) dbadmin=> SELECT COUNT(*) FROM nodes; COUNT ------- 3 (1 row) dbadmin=> CREATE TABLE test (c1 INT) SEGMENTED BY HASH(c1) ALL NODES; CREATE TABLE dbadmin=> INSERT /*+ DIRECT */ INTO test SELECT RANDOMINT(10000) FROM vs_columns; OUTPUT -------- 9319 (1 row) dbadmin=> COMMIT; COMMIT dbadmin=> SELECT node_name, projection_name, row_count FROM projection_storage ORDER BY 1, 2; node_name | projection_name | row_count --------------------------+-----------------+----------- v_test_k1_to_k0_node0001 | test_b0 | 3106 v_test_k1_to_k0_node0001 | test_b1 | 3111 v_test_k1_to_k0_node0002 | test_b0 | 3102 v_test_k1_to_k0_node0002 | test_b1 | 3106 v_test_k1_to_k0_node0003 | test_b0 | 3111 v_test_k1_to_k0_node0003 | test_b1 | 3102 (6 rows) dbadmin=> SELECT MARK_DESIGN_KSAFE(0); WARNING 6022: Setting K-safety to 0 could result in catastrophic data loss in the event of a failure. Do not use k=0 in a production environment. For test, dev or other non-production environments, K=0 may be acceptable however Vertica still recommends a minimum value of K=1 MARK_DESIGN_KSAFE ---------------------- Marked design 0-safe (1 row) dbadmin=> \q [dbadmin@vertica01 ~]$ admintools -t list_allnodes Node | Host | State | Version | DB --------------------------+---------------+-------+-----------------+--------------- v_test_k1_to_k0_node0001 | 192.168.2.200 | UP | vertica-9.1.1.4 | test_k1_to_k0 v_test_k1_to_k0_node0002 | 192.168.2.201 | UP | vertica-9.1.1.4 | test_k1_to_k0 v_test_k1_to_k0_node0003 | 192.168.2.202 | UP | vertica-9.1.1.4 | test_k1_to_k0 [dbadmin@vertica01 ~]$ admintools -t db_remove_node -d test_k1_to_k0 -s v_test_k1_to_k0_node0002,v_test_k1_to_k0_node0003 connecting to 192.168.2.200 Starting Data Rebalancing tasks. Please wait.... This process could take a long time; allow it to complete uninterrupted. Use Ctrl+C if you must cancel the session. Data Rebalance completed successfully. Attempting to drop node v_test_k1_to_k0_node0002 ( 192.168.2.201 ) Shutting down node v_test_k1_to_k0_node0002 Sending node shutdown command to '['v_test_k1_to_k0_node0002', '192.168.2.201', '/home/dbadmin', '/home/dbadmin']' Deleting catalog and data directories Update admintools metadata for v_test_k1_to_k0_node0002 Attempting to drop node v_test_k1_to_k0_node0003 ( 192.168.2.202 ) Shutting down node v_test_k1_to_k0_node0003 Sending node shutdown command to '['v_test_k1_to_k0_node0003', '192.168.2.202', '/home/dbadmin', '/home/dbadmin']' Deleting catalog and data directories Update admintools metadata for v_test_k1_to_k0_node0003 Reload spread configuration Replicating configuration to all nodes Checking database state Node Status: v_test_k1_to_k0_node0001: (UP) [dbadmin@vertica01 ~]$ vsql -c "SELECT node_name, projection_name, row_count FROM projection_storage ORDER BY 1, 2;" node_name | projection_name | row_count --------------------------+-----------------+----------- v_test_k1_to_k0_node0001 | test_b0 | 9319 v_test_k1_to_k0_node0001 | test_b1 | 9319 (2 rows) [dbadmin@vertica01 ~]$ vsql -Atc "SELECT 'DROP PROJECTION ' || projection_schema || '.' || projection_name || ' CASCADE;' FROM projections WHERE is_segmented LIMIT 1 OVER(PARTITION BY anchor_table_id ORDER BY projection_schema, projection_name DESC);" | vsql DROP PROJECTION [dbadmin@vertica01 ~]$ vsql -c "SELECT node_name, projection_name, row_count FROM projection_storage ORDER BY 1, 2;" node_name | projection_name | row_count --------------------------+-----------------+----------- v_test_k1_to_k0_node0001 | test_b0 | 9319 (1 row)Be careful! First run this command and make sure the DROP commands are for the projections you want to DROP!
vsql -Atc "SELECT 'DROP PROJECTION ' || projection_schema || '.' || projection_name || ' CASCADE;' FROM projections WHERE is_segmented LIMIT 1 OVER(PARTITION BY anchor_table_id ORDER BY projection_schema, projection_name DESC);"In the example above the output from that command are a series of DROP PROJECTION commands that are piped through vsql where they'll run.