Node Dependencies, not including unsegmented projections

bmurrell

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' ?

VValdar

Hi Ben,

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

bmurrell

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

VValdar

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.

bmurrell

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)

bmurrell

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 ]

SruthiA

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;

Vertica_Curtis

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.

bmurrell

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)

#

bmurrell

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.

SruthiA

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

bmurrell

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