Vertica Native Load balancer clarification

Sam37

Couple of questions on LB:

 

1) Does the Vertica Native Load Balancer consider the status of the node, to see any node that is 'Recovering' is not included in the list for any connections?

 

2) Is there a way to make the TCP connections closed until the node is 'Recovering'? Basically disabling the listeneres until the node is UP.

 

Hardware LB is handing off the connections to nodes that are 'Recovering' as well, causing failed db connection.

 

any suggestions on how you see the implementation of LB and best practices around this.

 

Nimmi_gupta

[1] Only up node can be the part of LB. And to determining to which Node a Client Has Connected, a client can determine the node to which is has connected by querying the NODE_NAME column of the V_MONITOR.CURRENT_SESSION table:

=> SELECT NODE_NAME FROM V_MONITOR.CURRENT_SESSION;

 

[2] While recovery is running the best would be disable the session. See below command.

 

select get_config_parameter('MaxClientSessions');

Take note of this number.

 

SELECT SET_CONFIG_PARAMETER('MaxClientSessions', 0);

 

This will prevent all non-dbadmin connections to the DB to allow recovery to complete. Once recovery is finished, go ahead and re-set the parameter to the initial value.

 

-Nimmi

Sam37

Thanks Nimmi. It so happened that in lower regions, there were instances when node went into recovery for almost 2 days. I guess part of the problem was it was not completely shut and the recovery was taking forever.

 

During this time, we noticed that LB is not setup correctly (not using Native Vertica LB) and though the cluster was still up, some of the LB requests were going to recovering node and is causing db connection to fail. Ideally, recovering node isn't accepting any connections, but as the port is still up, hardware LB assumes that db is up and tries to redirect some of the client connections.

 

I understand that Native LB was introduced as part of version 7 and it requires clients also to upgrade, may not be realistic for an application that is running for a long time. Trying to see what other best practices are followed in the industry or at other vertica clients.

 

Any inputs please welcome