Reset the ROUNDROBIN Load Balancing Counter

Jim_Knicely

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!