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.