Load Balancer Policies

Load balancer policies are 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 of available to a TE, or by specific TE names, 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.

Creating Load Balancer Policies

To create an load balancer policy, run the following command from nuocmd:

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

Or if using environment variables within lb-expression, run a command similar to the following:

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

LBQuery lb-expression Syntax

The format of LBQuery syntax is described as follows:

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

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

selector := random | round_robin
filter := any
        |colocated
        |region(<pattern>)
        |label(<name> <pattern>)
        |server_id(<pattern>)
        |node_id(<pattern>)
        |pid(<pattern>)
        |address(<pattern>)
        |hostname(<pattern>)
        |not(<filter>)
        |and(<filter>*)
        |or(>filter>*)
        |first(<filter>*)
        |option(<name> <pattern>)

Selector

Filter

region(us-northeast,us-east,*)

If no TEs in the us-northeast region are available, TEs in the us-east region are returned, otherwise it will return any available region and a TE in that region.

--label(<name> <pattern>)

For example:

--label(tx_type oltp)
--label(tx_type htap)

Note: The name and pattern parameters to the label option are arbitrary alphanumeric character strings values.

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))

Using Environment Variables.

If an environment is the empty string, then it is replaced with *, which matches everything. If you don’t want the empty string to equate to a wildcard, you can change the query to:

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

After this change, each variable is defaulted to empty string, which will not match anything.

option(mem 16g)
or(node_id(2) node_id(3))

The following commands return all TEs other than node IDs 2 and 3:

not(or(node_id(2) node_id(3)))
and(not(node_id(2)) not(node_id(3)))

The first value returns only and all TEs that match the first sub-filter that does not return an empty set of TEs. It has similar behavior to the pattern syntax used to match TE attributes.

For example:

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

The following code sample shows an example of using variable substitution, including an example with default value injection where LABEL = 'zone zone1'.

nuocmd set load-balancer --policy-name zoneX --lb-query 'round_robin(label(${LABEL}))'

Note: Single quotation marks (') are required when using variables.

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)

Displaying Database Process Labels

Database process labels are created at process create time.

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

The output of this command is formatted json. For example, run this command for a database named test.

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

The output will include a section similar this text indicating the TE's label value.

"labels": {
"te": "te1"
},{code}

Referencing LBPolicy and LBQuery from Client Applications

When a SQL application connects to NuoDB, load balancing functionlity enables applications to connect to a specific (or a random) Transaction Engine (TE) to service the database connection and run SQL requests. To perform selective load balancing across TEs, add either the LBPolicy or the LBQuery property 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();
TIME     CONNECTED TO HOST
-------- ------------------
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();
TIME     CONNECTED TO HOST
-------- ------------------
20:36:45 host1
SQL> exit;