Load Balancing Options

This blog post was authored by Soniya Shah. Connection load balancing automatically spreads the overhead of client connections across the cluster by redirecting connections. Each client connection a host in your Vertica cluster requires memory and processor time. If a lot of clients connect to a single host, this can affect database performance. The initiator node is associated with the specific host to which the connection was made. You can attempt to spread the overhead of client connections by specifying that certain clients connect to specific hosts on the cluster. However, this manual balancing can be difficult to manage as you add clients and hosts to your cluster. Vertica’s native connection load balancing feature helps distribute client connections among hosts in your cluster, which in turns balances the memory and processor overhead. Every node in Vertica can be the initiator node. The initiator node is the node associated with the specific host to which the connection was made. This node has extra work to do and needs more resources and processing time to prepare queries and compile the results. For example, a working node may have the following tasks: execution_step | duration ----------------------------+--------------- PreparePlan:DeserializePlan |00:00:00.005837 PreparePlan:TakeTableLocks |00:00:00.000005 PreparePlan:DistPlanner |00:00:00.000096 PreparePlan:LocalPlan |00:00:00.00167 PreparePlan:EEcompile |00:00:00.0226622 PreparePlan:ReserveResources|00:00:00.000094 PreparePlan:EEpreexecute |00:00:00.113643 And an initiator node has additional tasks, highlighted in red: execution_step | duration ----------------------------+--------------- Plan |00:00:00.028933 InitPlan |00:00:00.002279 SerializePlan |00:00:00.005837 PreparePlan |00:00:00.045177 PreparePlan:TakeTableLocks |00:00:00.000004 PreparePlan:DistPlanner |00:00:00.000701 PreparePlan:LocalPlan |00:00:00.001656 PreparePlan:EEcompile |00:00:00.027767 CompilePlan |00:00:00.116073 CompilePlan:ReserveResources|00:00:00.000077 CompilePlan:EEpreexecute |00:00:00.109732 ExecutePlan |00:00:00.358681 AbandonPlan |00:00:00.005791 You can check if your workload is balanced by querying the query_profiles table: dbadmin=> SELECT node_name, COUNT(distinct session_id) session_count, COUNT (*) num_requests FROM query_profiles GROUP BY 1; There are a few different types of load balancers: • Hardware options such as Cisco or Barracuda • Software options such as DNS • Native connection load balancer The following table outlines the pros and cons of each type of load balancer. This table is intended to help you understand your options: