The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

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

Jim_KnicelyJim_Knicely - Select Field - Administrator

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!

Tagged:
Sign In or Register to comment.