Load balancing a 3 Nodes HP Vertica 7.2.1 cluster

Hi Michael,

Good to hear you are using the Native Load Balancer! In DBvisualizer, which uses JDBC, you will need to enable the "connectionloadbalance" driver property in the Driver Properties folder of the Properties tab. You will see a list of supported parameters, simply enter 'true' (w/out the quotes) in the value field for the ConnectionLoadBalance parameter and click APPLY.

This also needs to be enabled on the Vertica server.  You can use this query to confirm that the server has this feature enabled

     SELECT LOAD_BALANCE_POLICY FROM V_CATALOG.DATABASES;

And then use the following query to see to which node DBvis is connected:

     SELECT NODE_NAME FROM V_MONITOR.CURRENT_SESSION;

You can then do a RECONNECT in DBvis and run that above command again to see if you are now connected to a different node.

More info on how to modify driver properties in DBvis here.

More info on enabling Vertica Native Load Balancing with JDBC here. 

Myles

Partner Engineering, HPE Vertica

Thank you very much, but it still does not work.

I enabled Load Balancing (RoundRobin) at the Vertica Server level as dbadmin.

Then I created two properties in the driver in DBVisualizer, named:

ConnectionLoadBalance=True

BackupServerNode=list of 3 public Vertica ip's address separated by comma.

I get the connection the first time, then go to Vertica Management Console and put down the node identified in the DBVisualizer connection. T disconnect and try to connect again to the cluster, it's supposed that after a timeout, it should look for the next IP (next node) from BackupServerNode, but the connection is refused, since that particular node is down.

I need to be able to connect to HP Vertica no matter the node specified in the JDBC or ODBC connection is down, since HP Vertica does not expose a public cluster IP address.

I would appreciate your help.

Best regards

Michael Prendergast

After watching this video, I guess there’s a misunderstanding.

https://community.dev.hpe.com/t5/Vertica-Blog/Vertica-Best-Practices-Native-Connection-Load-Balancing/ba-p/223415

I need connection FAULT TOLERANCE, not LOAD BALANCING, i.e., I need to Connect to a cluster that has no public IP address no matter the node is up or down.

I mean is good to have load balancing, but I need to have a connection string that points to a single IP address that works always, no matter that particular node is down.

As connection must point to a single IP, how do I do that?

Thank you

Michael Prendergast

Hi,

Since native connection load balancing works at the application level, clients must opt-in to have their connections load balanced.

In your case, you need load balancer device for FAULT TOLERANCE or put DNS Entry of all node IP with single hostname and use the same  in connection string.

Regards,

Sumit Singh

To enable JDBC Connection Failover in DBvisualizer go to the same dialog where you set connectionloadalance earlier and go the parameter called backupservernode and enter as a value the backup node IP addresses separated by commas. 

If you are on Windows then enabling ODBC Connection Failover is a piece of cake and is independent of the tool (such as DBvisualizer) that you are using.  In the Windows DSN configuration dialog where you put the server IP address you will see another text box just below that called Backup Servers. Again, simply enter a comma-separated list of backup servers. 

After talking to DBVis guys, we found that they deliver the last version of DBVisualizer with JDBC driver v 6.3, which does not support LoadBalancing nor BackupNode... I created a new connection pointing to JDBC driver 7.2.1 and that solved the problem with DBVisualizer.

Regarding ODBC for some reason it did not work with version 7.2.1, so I downgraded the driver to version 7.1.1 and now I have both connections (JDBC for DBVis and ODBC for a Windows based Web BI Tool) working perfectly well with load balancing over a 3 node cluster.

Regards

Michael