Load Balancer Policies

The LBQuery expression syntax can be used to specify the set of suitable Transaction Engines (TEs) to service SQL clients and the strategy used to distribute connections to these TEs among SQL clients. These load balancer policies can be specified directly by SQL clients using the LBQuery connection property, can be registered by name in the NuoDB domain state and referenced by SQL clients with the LBPolicy connection property, or can be registered as default policies in the NuoDB domain so that they are used by clients that do not specify any of the load balancer connection properties.

LBQuery Expression Syntax

An LBQuery expression is defined by the following syntax:

LBQuery-expression := <selector>(<filter-expression>)

selector := random | round_robin

filter-expression := any
                   | colocated
                   | start_id(<pattern>)
                   | server_id(<pattern>)
                   | node_id(<pattern>)
                   | pid(<pattern>)
                   | address(<pattern>)
                   | hostname(<pattern>)
                   | label(<identifier> <pattern>)
                   | option(<identifier> <pattern>)
                   | not(<filter-expression>)
                   | and(<filter-expression-list>)
                   | or(<filter-expression-list>)
                   | first(<filter-expression-list>)

filter-expression-list := <filter-expression>
                        | <filter-expression> <filter-expression-list>

identifier := [a-zA-Z0-9_-]*

pattern := *
         | <value>
         | <value>*
         | *<value>
         | <pattern>,<pattern>

The filter expression defines the set of suitable TEs, while the selector defines the strategy used to select among those TEs. The available selectors and filters are listed below:


  • random randomly selects any TE that satisfies the filter expression.

  • round_robin cycles through TEs that satisfy the filter expression.


  • any returns any TE for the database.

  • colocated returns TEs with the same address as the client requesting the SQL connection. This may not return TEs that actually are on the same host as the client depending on how the client request is routed.

  • start_id, server_id, node_id, pid, address, and hostname match the value of the named attribute. The pattern argument is a comma-separated list of values that contain alphanumeric characters, hyphens (-), underscores (_), and wildcard characters (*) for pattern matching. The returned TEs are the ones that match the first value in the comma-separated list and all subsequent values in the pattern are not considered, so higher precedence values should be placed first.

  • label returns TEs that have a matching label.

  • option returns TEs that have a matching option.

  • not, and, and or perform the logical operation on the results of other filters. Respectively, they are the inverse, intersection, and union operators on the sets returned by the sub-filters.

  • first returns only and all TEs returned by the first sub-filter (evaluating from left to right) that returns a non-empty set of TEs. This filter is used to define precedence rules.

Directly Specifying Load Balancer Policies (LBQuery)

SQL clients can directly supply an LBQuery expression describing the load balancer policy to use by specifying the LBQuery connection property:

nuosql ... --connection-property LBQuery='<LBQuery-expression>'
When specifying an LBQuery expression in a command terminal (e.g. Bash), the expression should be enclosed in single quotes (').

Registering Named Load Balancer Policies (LBPolicy)

To create a load balancer policy that can be referenced by name using the LBPolicy connection property, use the nuocmd set load-balancer command:

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

These policies can be referenced by name when requesting a SQL connection using the LBPolicy connection property as follows:

nuosql ... --connection-property LBPolicy=<name>

Setting Default Load Balancer Policies

If a client does not specify LBQuery or LBPolicy as a connection property, the default load balancer policy is used. The nuocmd set load-balancer-config subcommand can be used to set the default load balancer policy.

The following command invocation sets the global default policy, which is effective for all databases in the domain that do not define a database default policy:

nuocmd set load-balancer-config --default '<LBQuery-expression>' --is-global

It is also possible to specify default policies at database scope as follows:

nuocmd set load-balancer-config --default '<LBQuery-expression>' --db-name <DB name>

If the user has not defined a custom default load balancer policy using nuocmd set load-balancer-config, then the default policy is random(any).

nuocmd set load-balancer and nuocmd set load-balancer-config are different subcommands. nuocmd set load-balancer registers policies that can be referenced by name (LBPolicy), while nuocmd set load-balancer-config can be used to define default policies and prefilters (described in the next section).

Setting Load Balancer Prefilters

It is also possible to register a prefilter, which is a filter expression that composes with whichever load balancer policy is used. Prefilters are applied unconditionally for each SQL client request, regardless of whether it specifies LBQuery, LBPolicy, or neither. The output set of TEs produced by the prefilter is the input set of TEs that the load balancer policy operates on.

A global prefilter can be specified as follows:

nuocmd set load-balancer-config --prefilter '<filter-expression>' --is-global

It is also possible to specify prefilters at database scope as follows:

nuocmd set load-balancer-config --prefilter '<filter-expression>' --db-name <DB name>
Global and database scope prefilters do not compose with each other and database scope prefilters take precedence over global prefilters. In other words, if a database has a prefilter defined for it, then the global prefilter is not used at all for SQL connection requests for that database.

Precedence Rules for Selection of Load Balancer Policy and Prefilter

The following rules define which load balancer policy is used for a particular SQL connection request:

  1. If the LBQuery connection property is defined, then it is used as the load balancer policy.

  2. If the LBPolicy connection property is defined and refers to a load balancer policy registered with nuocmd set load-balancer, then it is used.

  3. If a default load balancer policy is defined for the database using nuocmd set load-balancer-config --db-name …​ --default …​, then it is used.

  4. If none of the above are true, then the global default load balancer policy is used.

The following rules define which prefilter is used:

  1. If a prefilter is defined for the database using nuocmd set load-balancer-config --db-name …​ --prefilter …​, then it is used.

  2. Otherwise, the global prefilter is used.

Using Variables

Variables can appear in LBQuery expressions, allowing them to be resolved using connection properties at the time that a SQL connection is requested. Variables are specified with the syntax ${VARIABLE_NAME}.

If a variable is not bound by a connection property, then by default it is replaced with the wildcard character (*). An explicit fall-back value can be specified for unresolved variables by using the syntax ${VARIABLE_NAME:<value>}.

For example, the following policy named region is parameterized with the REGION variable, which is replaced by the value east if it is not resolved:

nuocmd set load-balancer \
    --policy-name region \
    --lb-query 'round_robin(label(region ${REGION:east}))'

The variable REGION can be bound at the time that a SQL connection is requested as follows:

nuosql ... \
    --connection-property LBPolicy=region \
    --connection-property REGION=west

A more detailed version of this example is described in a later section.

Displaying Available Load Balancer Configuration

To display available named load balancer policies (i.e. those that can be referenced using the LBPolicy connection property), use the nuocmd get load-balancers subcommand.

nuocmd get load-balancers
LoadBalancerPolicy(lb_query=round_robin(label(zone ${ZONE_NAME:east})), policy_name=zone)

To display the default load balancer policies and prefilters, use the nuocmd get load-balancer-config subcommand.

nuocmd get load-balancer-config
LoadBalancerConfig(default=random(any), is_global=True, prefilter=any)


Connect to a Specific TE

Suppose the SQL client wants to connect to a specific TE that can be uniquely identified by start ID. Then the client can use the following nuosql invocation to connect to the TE:

nuosql ... \
    --connection-property LBQuery='random(start_id(<start ID>))'
In the example above, the choice of selector (random or round_robin) is irrelevant, but formally one has to be specified because filter expressions output sets of TEs (which in this case, would be a singleton set).

Filtering TEs by Label

Assume that the following nuocmd start process invocations were used to start TEs:

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

The following LBQuery expression can be used to obtain connections to oltp and htap TEs, respectively:

round_robin(label(tx_type oltp))
round_robin(label(tx_type htap))

Either of these expressions can be specified using the LBQuery connection property of a client request or registered in any of the ways described in previous sections.

Composite Filter

Continuing with the example above, suppose that the client prefers an oltp TE that is on the same host, but is willing to settle for any non-htap TE. Then the following LBQuery expression can be used:

round_robin(first(and(label(tx_type oltp) colocated) not(label(tx_type htap))))

Filter by Location

Suppose that the process labels zone and region are used to define availability zone (within a region) and region, respectively. Then the following load balancer policy can be registered and used by SQL clients to select the TE closest to it:

nuocmd set load-balancer \
    --name location \
    --lbquery 'random(first(hostname(${HOSTNAME:-}) label(zone ${ZONE:-}) label(region ${REGION:-})))'

This load balancer policy selects a random TE that satisfies one of the following, in descending order of precedence: . Has the same hostname as the SQL client, assuming that the client supplies its hostname with the HOSTNAME connection property. . Has the same availability zone as the SQL client, assuming that the client supplies its availability zone with the ZONE connection property. . Has the same region as the SQL client, assuming that the client supplies its region with the REGION connection property.

The following nuosql invocation would yield the closest available TE to the client:

nuosql ... \
    --connection-property LBPolicy=location \
    --connection-property HOSTNAME=<hostname> \
    --connection-property ZONE=<zone> \
    --connection-property REGION=<region>

Since the fall-back value for all variables is -, any variable that is not bound by a connection property would fail to yield a match for that component of the LBQuery expression. For example, if the SQL client only wants to obtain a connection to a TE on the same host, then the ZONE and REGION connection properties can be omitted:

nuosql ... \
    --connection-property LBPolicy=location \
    --connection-property HOSTNAME=<hostname>

This would yield a TE on the same host or generate a connection failure if there is no such TE.

Similarly, if the client wants to obtain a connection to a TE in the same availability zone or region, but does not have a preference for one on the same host, the HOSTNAME connection property can be omitted:

nuosql ... \
    --connection-property LBPolicy=location \
    --connection-property ZONE=<zone> \
    --connection-property REGION=<region>

Verifying Load Balancer Policies

To verify that a load balancer policy works as expected and selects suitable TEs, the following SQL statement can be executed in a nuosql session obtained using the policy:

select startid,hostname from system.nodes where id = getnodeid();
 -------- ---------

    2      server2

This displays the start ID and hostname of the connected TE. Other columns like address, id (node ID), and port can also be included in the select statement.