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.

Comments

  • Jim_KnicelyJim_Knicely - Select Field - Administrator
    edited November 2018

    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.

Leave a Comment

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