Load Balancer Policies

The LBQuery expression syntax is used to specify a subset of suitable Transaction Engines (TEs) to service SQL clients and the strategy used to distribute connections to these TEs among SQL clients. This is known as a Policy.

Figure 1. Getting a connection from an AP

There are three ways to specify load balancer policies:

_Explicitly by SQL clients using the LBQuery connection property.
And/or _explicitly by the SQL clients, as a registered name in the NuoDB domain state, using the LBPolicy connection property.
And/or implicitly so that they are automatically used by clients that do not specify either of the load balancer connection properties.

Any of the three criteria listed above will be used to make the connection decision. The interaction between the policies is described in Precedence Rules for TE Selection.

Figure 2. AP load-balancer decides what connection to return

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:

Selectors

random

Randomly selects any TE that satisfies the filter expression.

round_robin

Cycles through TEs that satisfy the filter expression.

A selector is mandatory, even if the filter can only return a single matching TE — in which case, it makes no difference which selector you choose.

Filters

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.

Note that these names use underscores not hyphens.
start-id, server-id, and node-id are not valid (a common error).

<pattern>

The pattern argument is a comma-separated list of values that contain alphanumeric characters, hyphens (-), underscores (_), and wildcard characters (*) for pattern matching.

This is not a regular expression. * matches any 0 or more characters just like ls * or dir *.
Each expression in the comma separated list is evaluated in turn and evaluation stops at the first expression matched, returning the TEs indicated by that expression. All subsequent expressions in the list are not considered, so higher precedence values should be placed first.

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. Syntax:

and(<lbquery1> <lbquery2>)
or(<lbquery1> <lbquery2>)
not(<lbquery>)

Note that the arguments to and and or are separated by a space.

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 and expects two or more filter expressions to evaluate. Syntax: first(<lbquery1> <lbquery2> …)

label

Returns TEs that have a matching label.

Labels allow you to define your own custom subsets of TEs.
To use labels, the TEs must be started explicitly using nuocmd start process with the --labels option.
- Using the --te-server-ids option to nuocmd create database or nuocmd start database. does not allow you to specify TE process labels.
Using nuocmd capture database does capture labels.

Examples of first and label are found in Composite Filter and Filtering TEs by Label respectively.

If the syntax of your LB query expression is invalid you will be unable to connect.

Examples

Simple Expressions

random(start_id(4))

Pick the TE with start ID 4.

Every TE and SM in a domain has a unique start-id that is never reused. Run nuocmd show domain to see start-ids of all TEs in the domain.
This is the preferred way to connect directly to a specific TE instead of using the direct=true connection property, as you do not need to know the host and port number the TE is using.

round_robin(start_id(4))

Also picks the TE with start ID 4. As there is only one possible match, random or round_robin work the same.

random(hostname(server-0))

Randomly pick any TE on host server-0.

random(hostname(server-0,server-1,server-*))

Randomly pick any TE on host server-0 or server-1 or any other server, giving preference to server-0 (first in the list), then server-1 (second in list) and finally any server.

Note that the choices are comma-separated.

random(any)

Randomly pick any TE - this is the default setup when no other rules have been specified.

round-robin(option(mem 12G))

Restrict queries to those TEs started with 12G of memory, using each TE in turn.

To be selected the process must be explicitly started with this memory option:
For example: nuocmd start process --engine-type TE --options mem 12G …

random(label(tx_type oltp))

Randomly pick any TE that was started with the label tx_type having value oltp.

The TEs must be started with this label and value:
nuocmd start process --engine-type TE --labels tx_type oltp …

Compound Expressions

round_robin(not( hostname(server-0) ))

Pick each TE in turn on every server except those on host server-0.

random( and( hostname(server-0) not(start_id(2)) ) )

Randomly pick any TE on host server-0 except the TE with start ID 2.

Note that the arguments to and (and or) are separated by a space.

Verifying Load Balancer Policies

Use nuocmd show database to see what TEs are running in your chosen database.

nuocmd show database --db-name testdb

Database(default_options={}, default_region_id=0, incarnation=(2, 0), name=testdb, server_assignments={}, state=RUNNING)
  testdb [state = RUNNING]
    [SM] server-1:48007 [start_id = 4] [server_id = nuoadmin-1] [pid = 19168] [node_id = 1] [last_ack =  0.62] MONITORED:RUNNING
    [TE] server-0:48006 [start_id = 5] [server_id = nuoadmin-0] [pid = 1480]  [node_id = 2] [last_ack =  2.36] MONITORED:RUNNING
    [TE] server-0:48008 [start_id = 7] [server_id = nuoadmin-0] [pid = 2497]  [node_id = 4] [last_ack =  3.25] MONITORED:RUNNING
    [TE] server-2:48006 [start_id = 6] [server_id = nuoadmin-2] [pid = 4297]  [node_id = 3] [last_ack =  1.96] MONITORED:RUNNING
    [TE] server-2:48008 [start_id = 8] [server_id = nuoadmin-2] [pid = 5020]  [node_id = 5] [last_ack =  2.75] MONITORED:RUNNING

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

SELECT address,port,id as nodeid,startid,hostname
    FROM system.nodes WHERE id = getnodeid();

    ADDRESS    PORT  NODEID  STARTID    HOSTNAME
 ------------- ----- ------- -------- -------------

 10.200.83.111 48008    3       6       server-0

This displays the address, port number, start ID and hostname of the connected TE.

If your load-balancer expression is invalid or yields no matches:

nuosql testdb@localhost --user dba --password dba --connection-property 'LBQuery=random(pid(xxx))'

Connection failed: no NuoDB nodes are available for database 'testdb@localhost'

Explicit Load Balancer Rules

To explicitly specify load balancing rules use the database connection properties LBQuery or LBPolicy. These work with any NuoDB database driver.

Directly Specifying LBQuery

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

nuosql ... --connection-property LBQuery='<LBQuery-expression>'

When specifying an LBQuery expression in a command terminal (such as Bash), the expression should be enclosed in single quotes (') — as shown in the syntax example above. On Windows you must use double quotes.

Example: Connect to a Specific TE

Every TE is uniquely identified by its start ID. Use nuocmd show database to find start-ids for a database’s TEs. Then use the following nuosql invocation to connect to the TE:

nuocmd show database --db-name testdb

Database(default_options={}, default_region_id=0, incarnation=(2, 0), name=testdb, server_assignments={}, state=RUNNING)
  testdb [state = RUNNING]
    [SM] server-1:48007 [start_id = 4] [server_id = nuoadmin-1] [pid = 19168] [node_id = 1] [last_ack =  0.62] MONITORED:RUNNING
    [TE] server-0:48006 [start_id = 5] [server_id = nuoadmin-0] [pid = 1480]  [node_id = 2] [last_ack =  2.36] MONITORED:RUNNING
    [TE] server-0:48008 [start_id = 7] [server_id = nuoadmin-0] [pid = 2497]  [node_id = 4] [last_ack =  3.25] MONITORED:RUNNING
    [TE] server-2:48006 [start_id = 6] [server_id = nuoadmin-2] [pid = 4297]  [node_id = 3] [last_ack =  1.96] MONITORED:RUNNING
    [TE] server-2:48008 [start_id = 8] [server_id = nuoadmin-2] [pid = 5020]  [node_id = 5] [last_ack =  2.75] MONITORED:RUNNING

nuosql testdb ... \
    --connection-property LBQuery='random(start_id(5))'

Example: Connect to any TE on host server-0

Using nuosql:

nuosql testdb --user dba --password dba \
   --connection-property "LBQuery=random(hostname(server-0))"

An application would also specify LBQuery via its connection. Here is the equivalent JDBC example:

jdbc:nuodb.com://localhost/testdb?LBQuery=random(hostname(server-0))

Note that any NuoDB driver supports LBQuery, not just JDBC.

Named Load Balancer Policies (LBPolicy)

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

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

Once defined, reference the policy by name when requesting a SQL connection using the LBPolicy connection property as follows:

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

Example: Force Connections to TEs on a Specific Host

Suppose host server-0 has extra memory or CPU for running demanding queries. Define and use a policy to only connect to TEs on host server-0:

nuocmd set load-balancer --policy-name use-server-0 --lb-query 'random(hostname(server-0))'
nuosql … --connection-property LBPolicy=use-server-0

Example: Force Connections to TEs on a Specific AP Host

The same is achievable using server-id (where AP nuoadmin-0 runs on host server-0):

nuocmd set load-balancer --policy-name use-nuoadmin-0 --lb-query 'random(server_id(nuoadmin-0))'
When running under a Kubernetes cluster, the APs, TEs and SMs never share a host (each runs in its own pod). However each TE is still managed by a specific AP, so specifying server_id(nuoadmin-0) would select all the TEs managed by that AP. Same effect, but now hostname and server_id are not equivalent.

Filtering TEs by Label

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

There are no predefined labels in NuoDB. In our examples, region and tx_type are custom labels invented for the examples. You can specify whatever label names and values suit your requirement.

nuocmd start process ... --labels tx_type oltp

nuocmd start process ... --labels tx_type htap

To obtain nuosql connections to oltp and htap TEs, respectively, use the following LBQuery expressions:

nuosql ... --connection-property 'LBQuery=round_robin(label(tx_type oltp))'

nuosql ... --connection-property 'LBQuery=round_robin(label(tx_type htap))'

When connecting from an application, use the same LBQuery connection properties in your client request.

It is generally easier to make these named policies (ask your database administrator to do this for you if you don’t have nuocmd access). For convenience, we have chosen to use the value of the tx_type label as the policy name. Thus policy oltp corresponds to tx_type oltp and policy htap corresponds to tx_type htap, however the policy names could have been anything we wanted.

nuocmd set load-balancer --policy-name oltp --lb-query 'round_robin(label(tx_type oltp))'

nuocmd set load-balancer --policy-name htap --lb-query 'round_robin(label(tx_type htap))'

To connect, simply specify the policy - much more succinct:

nuosql ... --connection-property LBPolicy=oltp

nuosql ... --connection-property LBPolicy=htap

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.

This is where the first filter becomes useful, evaluating two or more choices and returning the first successful match. Here is the LBQuery expression to use:

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

The and expression finds a colocated OLTP TE.
The not expression finds any non-HTAP TE.

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 (*). Optionally, specify an explicit fall-back value for unresolved variables by using the syntax ${VARIABLE_NAME:<fallback-value>}.

Variables don’t make sense when LBQuery is being used (since you can just specify what you want without needing variables).

Example

The following policy named region is parameterized with the REGION variable, which is replaced by the fall-back value east if it is not resolved:

The label region is not predefined. We invented it for this example.

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

Bind the variable REGION to a SQL connection as follows:

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

A more detailed version of this example is described in the next section.

You will see variables again as we discuss implicit load balancer policies.

Filter by Location

Suppose that the process labels zone and region are used to define availability zone (within a region) and region, respectively.

zone and region are not predefined. When you start a TE (see nuocmd start process) you must specify its labels:

nuocmd start process --engine-type TE --labels region us-east-1 zone us-east-1a

To select the TE closest to a client, register the following load balancer policy:

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, simply omit the ZONE and REGION connection properties:

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, omit the HOSTNAME connection property:

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

Implicit Load Balancer Policies

If a client does not specify LBQuery or LBPolicy as a connection property, the implicit policies are used. There are two types of implicit policy:

Prefilter Policy
- Filters the set of TEs to be considered for connection.
- This policy is always applied first and cannot be circumvented.
Default Policy
- Decides which TEs to actually connect to and how to select them if there is more than one match.
- This policy is only applied if neither LBQuery nor LBPolicy is provided in the connection.

There can only be one of each policy, but you can use and() and or() to combine multiple policies if necessary.

Both Prefilter and Default policies come in two forms: global (apply to any incoming connection request) or scoped to a given database.

Global and database scope policies do not compose with each other and database scope policies take precedence over global policies. Thus, if a database has a prefilter policy defined for it, then the global prefilter policy is not used at all for SQL connection requests for that database. Similarly if there is a database specific default policy then any global default policy will be ignored.

Out of the box, with no custom configuration:

The global pre-filter policy is any, allowing connection to all TEs (no filtering occurs).
The global default is random(any), allowing connection to any TE on any database. Correct username and password credentials are still required.
There are no implicit database policies unless you define them.

Setting Default Load Balancer Policies

Use the nuocmd set load-balancer-config subcommand 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), allowing connections randomly to all any in any given database.

nuocmd set load-balancer and nuocmd set load-balancer-config are different subcommands.

nuocmd set load-balancer registers named policies, referenced using `LBPoli.
nuocmd set load-balancer-config defines default policies and prefilter policies, either globally or per-database.

Example

A common use of default filters is to make sure that a policy cannot be circumvented. Recall the region example above. If no region was specified, it defaulted to east. The following uses a variation to ensure that a REGION must be specified:
- nuocmd set load-balancer-config --default 'round_robin(label(region ${REGION:-}))' --db-name testdb
- Note that the fall-back region is - (minus sign) which will not match any region. Any connection attempt to testdb without the REGION property will default to - and fail unless an explicit LBQuery or LBPolicy connection property is used.

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.

There is no selector in a prefilter rule.

Specify a global prefilter 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.

Example

A common use of a prefilter rule is to make sure tie-breaker TEs never run SQL (since they are only required for majority decisions).
- nuocmd set load-balancer-config --prefilter 'not(label(region tiebreaker))' --is-global
- Tie-breaker TEs must be identified with --labels region tiebreaker when started using nuocmd start process.

Precedence Rules for TE Selection

The following rules define which prefilter is used. As stated above, the prefilter rule is always applied.

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

The default global prefilter rule is a simple pass-through, allowing any connection to any TE. There are no default database prefilter rules.

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

If the LBQuery connection property is defined, then it is used as the load balancer policy.
Otherwise, if the LBPolicy connection property is defined and refers to a load balancer policy registered with nuocmd set load-balancer, then it is used.
Otherwise, if a default load balancer policy is defined for the database using nuocmd set load-balancer-config --db-name … --default …, then it is used.
If none of the above are true, then the global default load balancer policy is used.

The default global rule is random(any), randomly assigning connections to any TE.

Displaying Load Balancer Information

To display available named load balancer policies (those that are 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. For example:

nuocmd get load-balancer-config

LoadBalancerConfig(default=random(any), is_global=True, prefilter=any)

Having run the global and prefilter examples above, here is the output:

nuocmd get load-balancer-config

LoadBalancerConfig(db_name=testdb, default=round_robin(label(region ${REGION:-})), is_global=False)
LoadBalancerConfig(default=random(any), is_global=True, prefilter=not(label(region tiebreaker)))

Removing Load balancers

To remove a named policy (one that is referenced using the LBPolicy connection property), use the nuocmd delete load-balancer subcommand.

nuocmd delete load-balancer --policy-name <policy-name>

To remove a default or prefilter load-balancer use the unregister options to nuocmd set load-balancer-config subcommand.

# Remove a pre-filter
nuocmd set-load-balancer-config [--is-global | --db-name] --unregister-prefilter

# Remove a default filter
nuocmd set-load-balancer-config [--is-global | --db-name] --unregister-default

Controlling Connectivity

A client SQL application can connect to a TE using either an external IP address or internal IP address or hostname. Examples include a VM with external and internal addresses, or Kubernetes cluster that exposes TEs via a service of type LoadBalancer. SQL clients that run outside the network or outside the Kubernetes cluster where NuoDB is deployed should be supplied with a external routable TE address.

It is possible to configure an external address and port for each TE by using two special process labels: external-address and external-port. By default, the external address and external port will be advertised to all SQL clients instead of the process address and port. If the defined external port is not valid, the TE process address and port will be returned.

To change the default behavior described above, set PreferInternalAddress=true connection property which is useful in cases where there are both internal and external SQL clients.

Example

Assume that the following nuocmd start process command is used to configure external address and external port.

nuocmd start process --db-name demo --engine-type TE --this-server \
    --labels external-address 34.122.158.46 external-port 48006

The configured external TE address will be advertized to the SQL clients.

nuosql demo@${ADMIN_ADDRESS} --user dba --password dba --verbose net

2021-10-29T14:27:52.914+0300 [55266] Created AES-256-CTR cipher: ENCRYPT type=0 nid=906 blocksize=1 keylength=32 ivlength=16 flags=5 mode=5
2021-10-29T14:27:52.914+0300 [55266] Created AES-256-CTR cipher: DECRYPT type=0 nid=906 blocksize=1 keylength=32 ivlength=16 flags=5 mode=5
2021-10-29T14:27:52.914+0300 [55266] Enabling outgoing AES-256-CTR encryption to node 34.122.158.46
2021-10-29T14:27:52.914+0300 [55266] Enabling incoming AES-256-CTR encryption from node 34.122.158.46
SQL>

To connect to the internal TE address and port, the PreferInternalAddress connection property should be used.

nuosql demo@${ADMIN_ADDRESS} --user dba --password dba --verbose net \
    --connection-property 'PreferInternalAddress=true'

2021-10-29T11:26:38.505+0000 [55266] Created AES-256-CTR cipher: ENCRYPT type=0 nid=906 blocksize=1 keylength=32 ivlength=16 flags=5 mode=5
2021-10-29T11:26:38.505+0000 [55266] Created AES-256-CTR cipher: DECRYPT type=0 nid=906 blocksize=1 keylength=32 ivlength=16 flags=5 mode=5
2021-10-29T11:26:38.506+0000 [55266] Enabling outgoing AES-256-CTR encryption to node 10.8.1.66
2021-10-29T11:26:38.506+0000 [55266] Enabling incoming AES-256-CTR encryption from node 10.8.1.66
SQL>