Connection Load Balancing: Quick Tip

Posted August 28, 2018 by Phil Molea, Sr. Information Developer, Vertica

Modern Database Analytics
Jim Knicely authored this tip.

Each client connection to a host in the Vertica cluster requires a small overhead in memory and processor time. If many clients connect to a single host, this overhead can begin to affect the performance of the database. You can attempt to spread the overhead of client connections by dictating that certain clients connect to specific hosts in the cluster. However, this manual balancing becomes difficult as new clients and hosts are added to your environment.

Vertica to the rescue!

Native connection load balancing is a feature built into the Vertica Analytic Database server and client libraries, as well as vsql.

Example:
[dbadmin@s18384357 ~]$ vsql -c "SELECT currentclientbalancingpolicy FROM vs_global_settings;"
currentclientbalancingpolicy
------------------------------
roundrobin
(1 row)

[dbadmin@localhost ~]$ vsql -C
Welcome to vsql, the Vertica Analytic Database interactive terminal.

Type: \h or \? for help with vsql commands
\g or terminate with semicolon to execute query
\q to quit

INFO: Connected using a load-balanced connection.
INFO: Connected to 192.168.56.102 at port 5433.

dbadmin=> \q

[dbadmin@localhost ~]$ vsql -C
Welcome to vsql, the Vertica Analytic Database interactive terminal.

Type: \h or \? for help with vsql commands
\g or terminate with semicolon to execute query
\q to quit

INFO: Connected using a load-balanced connection.
INFO: Connected to 192.168.56.103 at port 5433.

dbadmin=> \q

[dbadmin@localhost ~]$ vsql -C
Welcome to vsql, the Vertica Analytic Database interactive terminal.

Type: \h or \? for help with vsql commands
\g or terminate with semicolon to execute query
\q to quit

INFO: Connected using a load-balanced connection.
INFO: Connected to 192.168.56.104 at port 5433.

dbadmin=> \q

[dbadmin@localhost ~]$ vsql -C
Welcome to vsql, the Vertica Analytic Database interactive terminal.

Type: \h or \? for help with vsql commands
\g or terminate with semicolon to execute query
\q to quit

INFO: Connected using a load-balanced connection.
INFO: Connected to 192.168.56.102 at port 5433.
Helpful link:
https://my.vertica.com/docs/latest/HTML/index.htm#Authoring/AdministratorsGuide/LoadBalancing/ConnectionLoadBalancing.htm

Have fun!