Designating Hosts for SQL Connections

Whereas the use case in the previous topic has to do with the database using only certain hosts in the domain, this use case has to do with the database client directing its SQL connection to a particular host in the domain. By default, NuoDB uses a round-robin load balancer for directing SQL connections to TEs in the domain. See Balancing Database Load Across Hosts. Normally, the database client has no control over which host will handle its SQL requests.

However, there is a non-default load balancer offered, called the ChainableTagBalancer (or AffinityBalancer in NuoDB versions prior to 2.1.1, however AffinityBalancer is deprecated and is likely to be removed in a future release). Setting the balancer property (in to ChainableTagBalancer, tells the broker, that if the SQL connection request contains the connection property "LBTag", then look for an existing host with the same host tag key ("LBTag") and matching host tag value. If no hosts in the domain contain a matching LBTag host tag, and no other load balancer policy is chained to this policy in a comma separated list of balancers specified to the balancer property in, then a connection error will occur.

Let's say you have database B which has multiple hosts provisioned for it. Perhaps some hosts are better for TEs doing regular application transaction processing, and other hosts are better better for running TEs doing data analytics. The goal is to separate the two groups of hosts in order to avoid, as an example, a long-running analytics query over a large data set that might cause cache evictions on TEs that are optimized for short application transactions and different access paths. Let's say the host tag values you choose are "TP" for transaction processing and "AN" for analytics. Set the host tag key "LBTag" to tag value "TP" for hosts that are better for transaction processing and set "LBTag" to the tag value "AN" for hosts that are better for running data analytics.

In your SQL connection, if the SQL requests will be simple transaction processing, specify the connection property "LBTag=TP" and if the SQL requests will be more data analytics specify "LBTag=AN". Here is an example for how to do this by involking nuosql:

$ nuosql db2@localhost --user dba --password pwd --connection-property "LBTag=TP"

The equivalent JDBC connection string is as follows: