AWS Elastic Load Balancing with Vertica

Posted July 20, 2015 by Bob_Strahan

Did you know that you can use an Elastic Load Balancer (ELB) to connect to your Vertica cluster running on Amazon Web Services? You can, and you should for several reasons. Amazon’s Elastic Load Balancer:

  • * Provides one DNS name for connecting to your cluster, removing the need to assign elastic IP addresses to multiple cluster nodes.
  • * Automatically distributes incoming connections across all the cluster nodes.
  • * Is automatically highly available, so you don’t need to worry about it failing.
  • * Scales automatically, so you don’t need to worry about it becoming a bottleneck.
  • * Automatically detects when nodes are added, removed, or replaced.
  • * Monitors the health of each cluster node by pinging the database port, and it does not route connections to any failed node.
  • Allows you to monitor your database connections using AWS CloudWatch and/or access log files.

 

elastic1

 

If you already have an Vertica cluster running on AWS, it is easy to set up an Elastic Load Balancer. It’s not intrusive — you don’t have to change any configuration settings on your existing cluster.

Amazon’s Elastic Load Balancing documentation is well worth reading. In the meantime, here is an overview describing how to quickly get going with an Elastic Load Balancer for your Vertica cluster.

 

Before You Start

You should already have an Vertica database cluster up and running in an AWS VPC subnet. See the Vertica on Amazon Web Services Guide.

The new Elastic Load Balancer sits on the same subnet as your Vertica nodes, and will be assigned IP Addresses from your subnet’s address range. Per Amazon documentation, you must have at least 8 free IP addresses in the subnet for the ELB to use.

Create an Elastic Load Balancer

  1. From the AWS Console, open the EC2 Dashboard.
  2. From the navigation bar, select the AWS Region where your Vertica cluster is running.
  3. Select Load Balancers on the left.
  4. Click the blue Create Load balancer button at the top of the page.

Configure the Elastic Load Balancer

Step 1: Define Load Balancer

  1. Name your new Load Balancer
  2. Associate it with the VPC and Subnet containing your Vertica cluster
  3. Configure Load Balancer and Instance protocol and port for Vertica client connections (TCP/5433)
elastic2

 

Step 2: Assign Security Groups

You can assign an existing security group, or create a new one. Be sure that the assigned security group does not block TCP traffic on the Vertica port (5433).

Here we have elected to create a new security group which allows the ELB to forward incoming database connections on port 5433 only.

elastic3

 

Step 3: Configure Security Settings

Ignore the ‘secure listener’ warning.

You could theoretically configure the ELB to handle SSL on behalf of the cluster nodes, but this has not been tested. Instead, to secure your connections, you can enable Vertica native support for secure connections over SSL – see Implementing SSL. Vertica SSL mode is transparent to the Elastic Load Balancer and does not impact any of the setup requirements.

elastic4

Step 4: Configure Health Check

You can accept the defaults for the Health Check settings. The Health Check validates that each Vertica node is accepting connections on the database port. The ELB will not route connections to unhealthy instances.

elastic5

Step 5: Add EC2 Instances

If you have created your own Vertica cluster on AWS (not using auto scaling), select and assign all the EC2 instances serving as nodes in your cluster, from the list.

You can also assign and remove instances later, using the Instances tab in the Load Balancers page of the EC2 Dashboard.

Deselect the Cross-Zone Load Balancing check box, because your Vertica cluster is (hopefully) running in a placement group (inside a single availability zone).

elastic6

 

Step 6 (Optional): Add Tags

Add tags for filtering dashboard views, billing reports, CLI results, and more.

elastic7

Step 7: Review and Create

Double-check the configuration, and click Create (bottom right) to initialize your new Elastic Load Balancer.

elastic8

 

Increase Connection Idle Timeout

By default, the Elastic Load Balancer drops a connection if it finds that it is idle for more than 60 seconds. Long-running queries can look like idle connections, because there is no network traffic between the client and the database while the query is running. To keep connections alive if the session is either idle or running longer queries, change the connection setting’s Idle Timeout value. From the EC2 Dashboard, select the Load Balancers page, and open the Details tab. Click the Connection Settings Edit link, and increase the value up to the allowed maximum of 1 hour (3600 seconds).

elastic9

 

(Optional) Enable Access Logs

To log all the incoming connection requests, configure the Elastic Load Balancer to save access log files to an S3 location of your choice. From the EC2 Dashboard, select the Load Balancers page, and open the Details tab. Click the Access Logs Edit link, and set up the frequency and location for your log files.

elastic10

Associate the Load Balancer with your Auto Scaling Group

This section is relevant only if your cluster is managed by an AWS Auto Scaling group – see Automatic Vertica Scaling and Node Replacement.

From the EC2 Dashboard, select Auto Scaling Groups on the left panel. Select the group for the cluster you want to assign the Elastic Load balancer to, and open the Details tab. Click the Edit button on the right of the tab.

elastic12

Add the new Elastic Load Balancer instance, and click the Save button.

elastic13

Go back to the Load Balancer’s page in the EC2 Dashboard, select the Instances tab, and see that your cluster instances have been automatically added by the auto scaling group. As you use auto scaling to expand, contract, or replace failed nodes in your cluster, the Load Balancer configuration is maintained automatically.

 

Connect to the Database Using the Elastic Load Balancer

First, you need to get the DNS name for your Load Balancer. From the EC2 Dashboard, select the Load Balancers page (on the left), and then open the Details tab. The automatically assigned DNS name is shown.

elastic14

Configure your client connections to use this DNS name as the database host name, and validate that the Elastic Load Balancer is routing different connections to different nodes.

Here, using a remote vsql client, the first connection is routed to node0001, and a second connection to the same host name is routed to node0003.

# First connections
C:UsersBOSTR>vsql -h BobCluster1-ELB-1481344770.us-east-1.elb.amazonaws.com -U dbadmin -w N0tT3ll1ng
Welcome to vsql, the Vertica Analytic Database interactive terminal.
dbadmin=> select node_name from current_session ;
   node_name
------------------
v_vmart_node0001
(1 row)
# Second connection
C:UsersBOSTR>vsql -h BobCluster1-ELB-1481344770.us-east-1.elb.amazonaws.com -U dbadmin -w N0tT3ll1ng
Welcome to vsql, the Vertica Analytic Database interactive terminal.
dbadmin=> select node_name from current_session ;
   node_name
------------------
v_vmart_node0003
(1 row)
The Load Balancer is working!

Monitoring

From the EC2 Dashboard, select the Load Balancers page (on the left), and then open the Monitoring tab to see charts showing connection counts, health check results, and more. Use the Create Alarm button on the top right to configure your custom alerts. For example, you may want to receive an SNS email notification when nodes fail a health check, or when the number of connection requests exceeds your expected threshold.

elastic15

The use of AWS Elastic Load Balancing is not a formally tested or supported Vertica configuration. Nevertheless, we encourage you to experiment, see what works, and post your feedback and best practices back to the community. Good luck!