Behavior of Server connection string option

[bgrainger]

What strategy should the connector use when the Server connection string option contains multiple comma-delimited host names?

• Ordered: always try the first, fall back to the second, then the third
• Random: pick one at random
• Round-robin: use a global synchronized index (that wraps around) and cycle through them in order

The behaviour doesn't seem to be specified in the MySQL documentation, nor described in our docs. I believe we currently do "Ordered". Do we want to provide a guarantee; if so, which behavior is most appropriate?

(I haven't checked the Connector/NET source code to see what it might have done back when it supported comma-delimited host names.)

Related to #225

CC @caleblloyd

[caleblloyd]

I think that if we were going to pick one, "Ordered" is the best implementation.

Master-Slave replication is the most common that I've seen, and when the Master breaks, the Slave is typically promoted to Master, and old Master gets rebuilt as the new Slave. "Ordered" handles this setup.

I can see some benefits to the "Round-robin" implementation if using Active-Active replication, but I believe that is a lot less common.

If we were going to introduce more options, a connection string option that defaults to "Ordered" makes the most sense to me.

[bgrainger]

The setup I've most commonly seen is the one given in #318: a single primary and a pool of readonly secondary servers. In that case, you have a "write" connection string and a "read" connection string (and the application picks the connection string to use based on the operations it plans to perform). This lets you "scale out" reads, assuming you have a read-heavy workload and don't need immediate read-after-write (there may be a replication delay).

In this case, round-robin between the secondaries would be the more useful default. However, we could solve everyone's use case by adding another connection string option.

[bgrainger]

Possible algorithms could include "ordered", "round-robin", and "leastconn" (cf. HAProxy balance).

[bgrainger]

Implemented in 0.31.0.

[kostebudinoski]

Is there any way I can tell the load balancer to kick out a server from the cluster in case it is not responsive or unable to connect to the host? This would be great addition if it's not possible at the moment.

I see a possibility in the ILoadBalancer.LoadBalance method to return response of some custom cluster node objects (string that represents the host + boolean that tells if the node is available) + some system timer that checks if there are nodes back to normal and adds them back to the load balancer.

[caleblloyd]

@kostebudinoski you may want to check out MySQL Router for HA functionality

https://dev.mysql.com/doc/mysql-router/8.0/en/