Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Reset the ROUNDROBIN Load Balancing Counter

The ROUNDROBIN load balancing scheme chooses the next host from a circular list of hosts in the cluster that are up.

You can use the RESET_LOAD_BALANCE_POLICY function to reset the counter each host in the cluster maintains to track which host it will refer a client to when the native connection load balancing scheme is set to ROUNDROBIN. To reset the counter, you’ll need to run this function on all cluster nodes.

Example:

[dbadmin ~]$ vsql -c "SELECT node_name, node_address FROM nodes ORDER BY 1;"
Timing is on.
    node_name     |  node_address
------------------+----------------
v_vmart_node0001 | 192.168.61.227
v_vmart_node0002 | 192.168.61.228
v_vmart_node0003 | 192.168.61.229
v_vmart_node0004 | 192.168.61.230
v_vmart_node0005 | 192.168.61.231
v_vmart_node0006 | 192.168.61.232
v_vmart_node0007 | 192.168.61.233
v_vmart_node0008 | 192.168.61.234
v_vmart_node0009 | 192.168.61.235
(9 rows)

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

[dbadmin]$ vsql -Cc "SELECT local_node_name();"
local_node_name
------------------
v_vmart_node0001
(1 row)

[dbadmin]$ vsql -Cc "SELECT local_node_name();"
local_node_name
------------------
v_vmart_node0002
(1 row)

[dbadmin]$ vsql -Cc "SELECT local_node_name();"
local_node_name
------------------
v_vmart_node0003
(1 row)

[dbadmin]$ vsql -Cc "SELECT local_node_name();"
local_node_name
------------------
v_vmart_node0004
(1 row)

[dbadmin]$ vsql -Cc "SELECT local_node_name();"
local_node_name
------------------
v_vmart_node0005
(1 row)

[dbadmin]$ vsql -c "SELECT reset_load_balance_policy();"
Timing is on.
                        reset_load_balance_policy
-------------------------------------------------------------------------
Successfully reset stateful client load balance policies: "roundrobin".
(1 row)

[dbadmin]$ vsql -Cc "SELECT local_node_name();" –- Back to node 1!
local_node_name
------------------
v_vmart_node0001
(1 row)

But wait! I only reset the load balance policy counter on one node. I need to reset it on each node. How do I do that easily?

First we need to generate a reset command for each node using dynamic SQL:

[dbadmin ~]$ vsql -qAtc "SELECT '\! vsql -h ' || node_address || ' -qAtc \"SELECT reset_load_balance_policy();\"' FROM nodes;"
\! vsql -h 192.168.61.227 -XqAtc "SELECT reset_load_balance_policy();"
\! vsql -h 192.168.61.228 -XqAtc "SELECT reset_load_balance_policy();"
\! vsql -h 192.168.61.229 -XqAtc "SELECT reset_load_balance_policy();"
\! vsql -h 192.168.61.230 -XqAtc "SELECT reset_load_balance_policy();"
\! vsql -h 192.168.61.231 -XqAtc "SELECT reset_load_balance_policy();"
\! vsql -h 192.168.61.232 -XqAtc "SELECT reset_load_balance_policy();"
\! vsql -h 192.168.61.233 -XqAtc "SELECT reset_load_balance_policy();"
\! vsql -h 192.168.61.234 -XqAtc "SELECT reset_load_balance_policy();"
\! vsql -h 192.168.61.235 -XqAtc "SELECT reset_load_balance_policy();"

Next, we can pipe those commands back into vsql to execute on each node:

[dbadmin ~]$ vsql -qAtc "SELECT '\! vsql -h ' || node_address || ' -qAtc \"SELECT reset_load_balance_policy();\"' FROM nodes;" | vsql
Successfully reset stateful client load balance policies: "roundrobin".
Successfully reset stateful client load balance policies: "roundrobin".
Successfully reset stateful client load balance policies: "roundrobin".
Successfully reset stateful client load balance policies: "roundrobin".
Successfully reset stateful client load balance policies: "roundrobin".
Successfully reset stateful client load balance policies: "roundrobin".
Successfully reset stateful client load balance policies: "roundrobin".
Successfully reset stateful client load balance policies: "roundrobin".
Successfully reset stateful client load balance policies: "roundrobin".

Done!

Helpful Links:
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/AdministratorsGuide/ManagingClientConnections/LoadBalancing/Legacy/LegacyClusterLoadBalancing.htm
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/Functions/VerticaFunctions/ConnectionManagement/RESET_LOAD_BALANCE_POLICY.htm

Have fun!

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.