Reset the ROUNDROBIN Load Balancing Counter: Quick Tip

Posted February 12, 2019 by Jim Knicely, Big Data Solutions Architect, Vertica

Designers discussing project in office sample library
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!