prepare for switchover by throttling writes - keep replication lag low

Add prepare switchover behaviour that if necessary makes writing slower so that slaves can catch up. That way, once it’s time to do the switchover, there will be no waiting.

Can this reuse anything from sync_transaction to have new transactions wait or delay some to give time for the replica to catch up. but is it too much to have the entire transaction wait versus adding a fixed amount of ms or ascending. What about throttle filter or other to slow writes down.

make solution smarter a little bit

• existing connections wont have/know they need to throttle, need to atomically switch/update those

Design

There are two tasks: the switchover operation itself and the lag reduction. The monitors are responsible for orchestrating the switchover and the routers are responsible for controlling the traffic so that lag is reduced.

For mariadbmon, a new monitor command is needed that is similar to the existing async-switchover which will queue a switchover to take place once the replication lag has gone below some limit. This could also be implemented as an extension to async-switchover by introducing new parameters for the command that define a maximum wait time as well as a minimum lag value under which at least one server must be. This takes care of the orchestration side of things.

A new internal status flag is needed to communicate the intention to perform a switchover at a later point in time, similar to the SERVER_DEMOTING status added for MXS-5555. The new status would indicate to the routers that the servers are lagging too much and that something should be done to reduce the amount of writes going to the cluster. For readwritesplit, this would mean automatically togging on sync_transaction=soft and then slowly injecting more and more delay into each committed transaction until the replication lag falls below a limit.

The lag reduction should eventually cause the monitor to start the switcover once the lower lag limit is reached. After that, the MXS-5555 mechanism will kick in to block transactions while the final lag is caught up and the switchover is done.

The algorithm used for incrementing the value of sync_transaction_timeout will need research but a simple linear increase in e.g. 5ms increments per minute should already provide a relatively smooth transition. This translates to a linear slowdown of the application which in testing has shown to give enough time for replication to then quickly catch up once the tipping point is reached that eventually triggers the switchover.