Node Dependencies, not including unsegmented projections

bmurrellbmurrell Community Edition User

I'm trying to work out which nodes are buddy pairs, and looking at https://www.vertica.com/kb/KSafetyBestPractices/Content/BestPractices/KSafetyBestPractices.htm
In my database I have a number of unsegmented tables as they're very small, so they're obviously on all nodes and the dependencies reflect that.
I have K-safety of 1, and on v12.

How do I see the dependency 'ring' ?

Tagged:

Answers

  • VValdarVValdar Vertica Employee Employee

    Hi Ben,

    We have the function SELECT GET_NODE_DEPENDENCIES(); for this.
    Usually it's worth to run first SELECT RECOMPUTE_NODE_DEPENDENCIES();

  • bmurrellbmurrell Community Edition User

    Hi VValdar,

    I've used that, along with SELECT get_node_dependencies_verbose();
    How do I make sense of the below? In my 4-node setup, it looks like node1 is dependent on nodes 2,3,4. If I draw the ring, everything is connected to everything, ignoring the last line referencing 1111.

    Deps:
    0011 - cnt: 29 on [ v_dbadev_node0001, v_dbadev_node0002 ]
    0101 - cnt: 29 on [ v_dbadev_node0001, v_dbadev_node0003 ]
    0110 - cnt: 58 on [ v_dbadev_node0002, v_dbadev_node0003 ]
    1001 - cnt: 58 on [ v_dbadev_node0001, v_dbadev_node0004 ]
    1010 - cnt: 29 on [ v_dbadev_node0002, v_dbadev_node0004 ]
    1100 - cnt: 29 on [ v_dbadev_node0003, v_dbadev_node0004 ]
    1111 - cnt: 80 on [ v_dbadev_node0001, v_dbadev_node0002, v_dbadev_node0003, v_dbadev_node0004 ]

    0001 - name: v_dbadev_node0001
    0010 - name: v_dbadev_node0002
    0100 - name: v_dbadev_node0003
    1000 - name: v_dbadev_node0004

  • VValdarVValdar Vertica Employee Employee

    Seems you're in a good place for a SELECT START_REBALANCE_CLUSTER();
    This one takes a bit of time (depending on the size of your data), run it in non peak hours if you can.

  • bmurrellbmurrell Community Edition User

    I've now run the rebalance (small DBA Dev playpen), and below is the new output from get_node_dependencies_verbose. It looks much better.
    I now need to understand what the rebalance is fixing and why I might need to do it on other environments.

    0011 - cnt: 58 on [ v_dbadev_node0001, v_dbadev_node0002 ]
    0110 - cnt: 58 on [ v_dbadev_node0002, v_dbadev_node0003 ]
    1001 - cnt: 58 on [ v_dbadev_node0001, v_dbadev_node0004 ]
    1100 - cnt: 58 on [ v_dbadev_node0003, v_dbadev_node0004 ]
    1111 - cnt: 80 on [ v_dbadev_node0001, v_dbadev_node0002, v_dbadev_node0003, v_dbadev_node0004 ]

    0001 - name: v_dbadev_node0001
    0010 - name: v_dbadev_node0002
    0100 - name: v_dbadev_node0003
    1000 - name: v_dbadev_node0004
    (1 row)

  • bmurrellbmurrell Community Edition User

    On another cluster I have the below. I've done a rebalance but it remains the same. My first thoughts were that some projections have a higher ksafe, but I can't find any : -

    Deps:
    0011 - cnt: 260 on [ v_marketdata_node0001, v_marketdata_node0002 ]
    0110 - cnt: 260 on [ v_marketdata_node0002, v_marketdata_node0003 ]
    0111 - cnt: 4 on [ v_marketdata_node0001, v_marketdata_node0002, v_marketdata_node0003 ]
    1001 - cnt: 260 on [ v_marketdata_node0001, v_marketdata_node0004 ]
    1011 - cnt: 4 on [ v_marketdata_node0001, v_marketdata_node0002, v_marketdata_node0004 ]
    1100 - cnt: 260 on [ v_marketdata_node0003, v_marketdata_node0004 ]
    1101 - cnt: 4 on [ v_marketdata_node0001, v_marketdata_node0003, v_marketdata_node0004 ]
    1110 - cnt: 4 on [ v_marketdata_node0002, v_marketdata_node0003, v_marketdata_node0004 ]
    1111 - cnt: 68 on [ v_marketdata_node0001, v_marketdata_node0002, v_marketdata_node0003, v_marketdata_node0004 ]

  • SruthiASruthiA Vertica Employee Administrator

    select VERIFIED_FAULT_TOLERANCE from projections should show any projections with higher ksafe. could you please share the output of below query

    select sum(separated_bytes)/(sum(to_separate_bytes)+sum(separated_bytes)) * 100 as percentage_separated, sum(transferred_bytes)/(sum(to_transfer_bytes)+sum(transferred_bytes)) * 100 as percentage_transferred
    from REBALANCE_TABLE_STATUS;

  • select get_node_dependencies('1110') will show you the projections that are aligned in that way. There are 4 of them. Looks like you have some projections that were built as Ksafe=2. You can drop the "b2" projections for these, and that will fix it. It's not really a problem, but they are certainly unnecessary and just taking up extra disk space.

  • bmurrellbmurrell Community Edition User

    Hi SruthiA

    The sql against rebalance_table_status returned nothing, so rebalance was completed.
    However, looking at verified_fault_tolerance has values other than 1 for a number of projections, all very old tables/projections, and in the public schema.
    Below example shows "K: 3". An export_objects shows segmentation as "ALL NODES KSAFE 1".
    It looks like we have some old tables with no data, perhaps create when a previous DBA was testing something.

    e.g.

    > SELECT get_projection_status('public.date_dimension_sp_node0001');

    SELECT get_projection_status('public.date_dimension_sp_node0001');

    get_projection_status

    Current system K is 1.

    of Nodes: 4.

    public.date_dimension_sp_node0001 [Segmented: No] [Seg Cols: ] [K: 3] [public.date_dimension_sp_node0001] [Safe: Yes] [UptoDate: Yes] [Stats: RowCounts]

    (1 row)

    #

  • bmurrellbmurrell Community Edition User

    Hi Vertica_Curtis, I wasn't aware of that command, and can see it's all old stuff in the public schema.

    I will look to tidy up the public schema.

  • SruthiASruthiA Vertica Employee Administrator

    @bmurrell : Please create new tables with ksafe 1 , insert select the data and drop those tables with higher ksafe value

  • bmurrellbmurrell Community Edition User

    Thanks all. Old tables with ksafe different to standard. I've now fixed that and node dependancies look good.

Leave a Comment

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