Load Balance Older Clients (That You Can’t Upgrade for Some Weird Reason)

Jim_Knicely

Native connection load balancing is a feature built into the Vertica Analytic Database server and client libraries as well as vsql. To load balance vsql you can use the --enable_connection load_balance or -C command line options. In really old versions of vsql those options did not exist.

How do you load balance connections from those clients?

The RUN_LOAD_BALANCE_POLICY function returns a string formatted as address:port that results from running the current Native Load Balancer policy. You can use it to load balance older clients!

Example:

[dbadmin@SE-Sandbox-26-node1 ~]$ vsql -c "SELECT load_balance_policy FROM databases;"
load_balance_policy
---------------------
roundrobin
(1 row)

[dbadmin@SE-Sandbox-26-node1 ~]$ vsql -c "CREATE USER load_balance_me;"
CREATE USER

[dbadmin@SE-Sandbox-26-node1 ~]$ vsql -U load_balance_me -h `vsql -U load_balance_me -Atc "SELECT run_load_balance_policy();" | awk -F":" '{print $1}'` -c "SELECT user, local_node_name();"
  current_user   |  local_node_name
-----------------+--------------------
load_balance_me | v_test_db_node0001
(1 row)

[dbadmin@SE-Sandbox-26-node1 ~]$ vsql -U load_balance_me -h `vsql -U load_balance_me -Atc "SELECT run_load_balance_policy();" | awk -F":" '{print $1}'` -c "SELECT user, local_node_name();"
  current_user   |  local_node_name
-----------------+--------------------
load_balance_me | v_test_db_node0002
(1 row)

[dbadmin@SE-Sandbox-26-node1 ~]$ vsql -U load_balance_me -h `vsql -U load_balance_me -Atc "SELECT run_load_balance_policy();" | awk -F":" '{print $1}'` -c "SELECT user, local_node_name();"
  current_user   |  local_node_name
-----------------+--------------------
load_balance_me | v_test_db_node0003
(1 row)

By the way, for better compatibility and performance, please upgrade your Vertica vsql clients / drivers instead of using the above method of load balancing older clients!

Helpful Links:
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/AdministratorsGuide/ManagingClientConnections/LoadBalancing/ConnectionLoadBalancing.htm
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/SystemTables/CATALOG/DATABASES.htm

Have fun!