Reset the ROUNDROBIN Load Balancing Counter: Quick Tip

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? [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();" 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". Helpful Links:

https://www.vertica.com/docs/latest/HTML/Content/Authoring/AdministratorsGuide/ManagingClientConnections/LoadBalancing/Legacy/LegacyClusterLoadBalancing.htm

https://www.vertica.com/docs/latest\/HTML/Content/Authoring/SQLReferenceManual/Functions/VerticaFunctions/ConnectionManagement/RESET_LOAD_BALANCE_POLICY.htm

Have fun!