6.4.5. Setting Read Affinity and Direct Reads

Affinity enables you to specify at connection time that the connector should forward the connection to a particular host or service for reads, if the service is available. For example, within user.map:

user password east_west east

Defines a user that uses the east_west service, but prefers being routed to the east service for reading from a slave.

Affinity can also specified within the connection string:

jdbc://connector1:3306/database?affinity=host3&qos=RO_RELAXED

Additionally, affinity JDBC options can be set globally via the tpm command option connector-affinity.

Version Support: 6.0.3

The read affinity setting now supports multiple dataservices with ordering and exclusion (only one was previously allowed).

You may now fine-tune the affinity by specifying an ordered list of dataservice names.

  • The affinity string is an ordered list of dataservice names, separated by commas, where the first dataservice entry will be the one used by default. If the first dataservice in the list is not available, the connector will use the next one listed, and so forth.

  • Dataservices not specified in the list, if any, will be used last and randomly.

  • It is also possible to exclude one or more dataservices by adding a hyphen ("-") in front of the dataservice name.

Affinity can also be combined with other node selection, such as QoS. For example, by combining the affinity and RO_RELAXED, then the specified slave will be used first, if the load-balancer setting matches, then another slave within the same service, and finally the master. For example, in a dataservice with three nodes, where node1 is the master:

shell> mysql -h127.0.0.1 -P3306 databasename@qos=RO_RELAXED\&affinity=node2

Would use node2 first, then node3, and finally node1 if the others are not available.

Note

Within a composite dataservice, you cannot specify a specific host. You can only specify a physical dataservice within the composite dataservice. For example in a composite service with east and west physical dataservices:

shell> mysql -h127.0.0.1 -P3306 databasename@qos=RO_RELAXED\&affinity=east

Additionally, the user.map can be configured to direct specific users to a slave by using the @direct keyword. For example, the following line in user.map will always direct the user to a slave, ignoring latency and load balancing settings:

@direct readme