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)

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:

[[email protected] ~]$ vsql -c "SELECT load_balance_policy FROM databases;"
load_balance_policy
---------------------
roundrobin
(1 row)

[[email protected] ~]$ vsql -c "CREATE USER load_balance_me;"
CREATE USER

[[email protected] ~]$ 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)

[[email protected] ~]$ 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)

[[email protected] ~]$ 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.

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.