Administration

Native Vertica Load Balancing Implementation

2

As a follow up to an earlier post on Load Balancing Tips, this post will include some other tips from an implementation perspective. As Norbert mentioned, starting with Vertica 7.0 native load balancing has been built in to the product; but by default it is not enabled.

In addition to having the minimum Vertica Server Version (7.0) there are at least 3 things which must be in place to support Vertica Native Load Balancing:

  1. Server load balancing configuration settings
  2. Proper client driver
  3. Client load balancing configuration settings

Note: the example used in this post is done using a JDBC based tool; but the ideas in this blog can also be used for ODBC and ADO.net drivers as well.

Implementation Details

Server side settings.

Here is a simple query to identify if your DBA has enabled Vertica load balancing on the server:

If enabled, the result will either be roundrobin or random. Otherwise, check with your DBA to enable server side load balancing.

Proper client driver

In our organization we obtain our client tools and drivers from an internal site instead of the Vertica site. Our internal group had the old Vertica 6.1.3 driver which did not have the capability to enable load balancing. There are a few methods to find out what Version of the client driver you are using for JDBC.

  • Usually the file name contains the version but this is not always reliable since some 3rd party vendors rename the file to something like vertica.jdbc. I have also seen some 3rd party tools rename a more recent 7.1 driver to 6.3 because their GUI’s have not been updated to support more recent versions of Vertica.
  • Most 3rd party tools show you what the version of the client driver is in their properties panel. DBVisualizer and many other developer tools do this. This is usually the easiest and most reliable since they tools use the Vertica API to obtain the version.
  • Run a query in Vertica to verify the version. Here is a simple query you can run that will tell you what client version you are running.

When you run the query above you will see a verbose description of the driver used in the results. In my case I had the following:

The default client_label includes the driver name. You will also find out what node you are connected to. In my case I am connected to node v_mycompany_node0002. Whenever I connect to Vertica I am always running my queries on this node which means I am not using load balancing.

lb1_2

I am also competing for resources with others since many other people are also using this node to run their queries.

Here is my query which is how I was able to identify my example from the many other results that came back:

In my case I can not just use the user id since we have a shared userid for many users. You can also use the IP address as well, which is also listed in the results. Later on in another post, we will explain how to use the session label connection setting to more easily identify our queries.

Vertica Client Driver Downloads

Most recent Vertica client drivers can be found at https://my.vertica.com/vertica-client-drivers/.

I replaced the original JDBC driver (6.1.3) with the new one downloaded (7.1.2) and restarted DBVisualizer. Now when I connect I can see that my client tool is indeed using the new driver.

Client side load balancing enabling

As you can see from above I am still always connecting to node2. Another way to identify what node you are connecting to is the run the following query:

So in order for me to connect using load balancing I need to enable it in my client tool (DBVisualizer). This is done on the connection properties page. In the screenshot below you should change the 0 to 1 to enable load balancing:

lb2_2

Every client tool is different on where this setting is enabled. You can also just include it on the JDBC connection string as well if your tool does not have a GUI for custom settings. Something like:

After restarting DBVisualizer and running my query, I can see that I am now connecting to a different Vertica node. Now, if I can only get everyone else in our company to use load balancing we can prevent node002 from getting hammered with queries and prevent it from becoming the weakest link.

For more information on Vertica native load balancing see the following links:

To find out why “At least” is highlighted above see future blog post on Using Vertica Client Side Logging.

About the author / 

Mark Warner

Mark Warner previously worked for Vertica for nearly 4 years with the Partner Engineering team. Mark is currently an Integration Engineer working with Vertica for Tapjoy; a mobile platform for publishers.

2 Comments

  1. Yakir Gibraltar March 1, 2016 at 12:42 AM -  Reply

    Hi Mark, nice article.
    I also added BackupServerNode in driver properties for case that host node2 is down.

  2. Mark Warner March 20, 2016 at 1:09 PM -  Reply

    Good point here is an example using jdbc..
    jdbc:vertica://docd03.verticacorp.com:5433/vmart?connectionloadbalance=1&backupservernode=node5

Leave a Reply

Upcoming Events

  • No upcoming events
AEC v1.0.4

Subscribe to Blog via Email

Enter your email address to subscribe and receive notifications of new posts by email.

Read more use cases here.

Notice

This site is not affiliated, endorsed or associated with HPE Vertica. This site makes no claims on ownership of trademark rights. The author contributions on this site are licensed under CC BY-SA 3.0 with attribution required.
%d bloggers like this: