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

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/latest/HTML/Content/Authoring/AdministratorsGuide/ManagingClientConnections/LoadBalancing/ConnectionLoadBalancing.htm https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/SystemTables/CATALOG/DATABASES.htm

Have fun!