Load Balancer Policies

A load balancer policy (LBPolicy) is used in a client application to filter and connect to specific transaction engines (TEs) suitable to service that application. Filtering is based, for example, on geographical location, memory or CPU resources available to a TE, or by specific TE labels or other identifiers to determine appropriate matching TE resources. If multiple TEs match the filter specified, the policy provides the option to select a TE using either round-robin or a random policy.

LB Policies are stored at the NuoDB domain level and can be referenced from a client application to simplify connection syntax when connecting to the database using LBQuery expressions.

Setting Load Balancer Policies

To set a load balancer policy (LBPolicy), run the set load-balancer command:

nuocmd set load-balancer --policy-name <name> --lb-query "<lb-expression>"

Or if using variables within lb-expression, run a command similar to the following (note the single quotes):

nuocmd set load-balancer --policy-name <name> --lb-query '<lb-expression>'

LBQuery lb-expression Syntax

An LBQuery lb-expression can be supplied as a connection property using the following syntax:

--connection-property LBQuery="<selector>(<filter>)"

Where the available selectors and filters are specified by the following syntax:

selector := random | round_robin
filter := any
        |label(<name> <value>)
        |option(<name> <value>)


  • random specifies that any TE satisfying the filter is randomly selected.

  • round-robin cycles through eligible TEs by node ID.


  • any returns any TE for a database.

  • colocated returns TEs with the same address as the client (but may not return TEs on the same host depending on address resolution).

  • server_id, node_id, pid, address, and hostname match the value of the named attribute and take the form <value>,<value>,*, where each value can contain alphanumeric characters and [_-.]', with * performing wildcard matching. The returned TEs are the ones that match the first value in the comma-separated list, so more selective or higher priority values should be placed first.

  • label returns TEs that were started with a label using the following nuocmd start process command option:

    nuocmd start process ... [ --label <name> <value> [ --label <name> <value ] ... ]

    The following command output will include label information in json format:

    nuocmd --show-json get processes --db-name <database name>

    For example, for a database named "test":

    nuocmd --show-json get processes --db-name test

    The output will include a section similar to the following indicating the TE’s label name and value:

    "labels": {
    "te": "te1"
    The label names and values are arbitrary alphanumeric character strings.

    For example, assume the following labels were created when TE processes were started:

    nuocmd start process ... --label tx_type oltp
    nuocmd start process ... --label tx_type htap

    Use the label filter at runtime to select TEs that match specific label values. For example:

    LBQuery=round_robin(label(tx_type oltp))


    LBQuery=round_robin(label(tx_type htap))
  • option returns TEs that match a given name value combination. For example:

    option(mem 16g)
  • not, and, and or perform the logical operation on the results of other filters. That is, they are the inverse, intersection, and union operators on the sets returned by other filters. Examples:

    The following filter could be used to connect to TEs with node IDs 2 or 3:

    "or(node_id(2) node_id(3))"

    Either of the following filters could be used to connect to any TE other than node IDs 2 or 3:

    "not(or(node_id(2) node_id(3)))"


    "and(not(node_id(2)) not(node_id(3)))"
  • first returns only and all TEs that match the first sub-filter that does not return an empty set of TEs.

    For example, to set an LBPolicy 'zone1' to give first preference to a TE with label 'zone zone1', or if there’s no such TE available (for example if 'zone1' is down for any reason) give preference to a TE with label 'zone zone2', or if there’s no such TE available then connect to any TE:

    nuocmd set load-balancer --policy-name zone1 --lb-query \
    "round_robin(first(label(zone zone1) label(zone zone2) any))"

    And similarly to set an LBPolicy for 'zone 2' to give first preference to a TE with label 'zone zone2', etc.:

    nuocmd set load-balancer --policy-name zone2 --lb-query \
    "round_robin(first(label(zone zone2) label(zone zone1) any))"

Using Variables

If a variable resolves to an empty string, then by default it is replaced with the wildcard *, matching every value. If you don’t want a variable to be replaced with the wildcard, suffix the variable with :, to ensure it will not match any value if it resolves to an empty string. For example, the following will randomly pick the first TE with POD_NAME, NODE_NAME or ZONE_NAME defined in that order:

random(first(label(pod ${POD_NAME:}) label(node ${NODE_NAME:}) \
label(zone ${ZONE_NAME:})))

The following shows the use of variable substitution, where for these examples, LABEL='zone zone1'.

Single quotation marks (') are required when using variables in --lb-query.
nuocmd set load-balancer --policy-name zoneX --lb-query \
nuocmd set load-balancer --policy-name zoneX --lb-query \
'round_robin(first(label(${LABEL}) label(zone zone2))'

Displaying Available Load Balancer Policies

To display available load balancer policies, run:

nuocmd get load-balancers

For example:

nuocmd get load-balancers
LoadBalancerPolicy(lb_query=round_robin(first(label(zone zone1) label(zone zone2) any)), policy_name=zone1)
LoadBalancerPolicy(lb_query=round_robin(first(label(zone zone2) label(zone zone1) any)), policy_name=zone2)

Referencing LBPolicy and LBQuery from Client Applications

To perform selective load balancing across TEs, add either an LBPolicy or LBQuery to the connection string or connection properties.

Example 1: Connect using LBQuery using nuosql

nuosql test@nuoadmin1 --user dba --password goalie --connection-property \
"LBQuery=round_robin(first(label(zone zone1) label(zone zone2) any))"
SQL> select current_time "TIME", hostname "CONNECTED TO HOST" from nodes
where ID = getnodeid();

-------- ------------------
20:36:38 host1
SQL> exit;

Example 2: Connect using LBPolicy zone 1 using nuosql

nuosql test@nuoadmin1 --user dba --password goalie \
--connection-property LBPolicy=zone1
SQL> select current_time "TIME", hostname "CONNECTED TO HOST" from nodes
where ID = getnodeid();

-------- ------------------
20:36:45 host1
SQL> exit;