RFC: Drain MySQL connection in VTGate upon termination of VTGate

[frouioui]

Current state

In #14219 there was a big improvement on how we shutdown vtgate and its connections. However, now when we shutdown VTGate the next query executed by an idle connection will return the following error: Server shutdown in progress (errno 1053) (sqlstate 08S01). Meaning that as soon as we shutdown VTGate, the next query on an idle connection will return an error, which can lead to more management at the app level.

Proposed changes

It would be nice if we could let idle connections use a VTGate we are trying to shutdown until they disconnect on their own or until onterm_timeout is reached, in this situation we would go from an activity drain timeout to a connection drain timeout. This can be useful for some applications that have a higher onterm_timeout and/or that have their application disconnects and reconnects connections periodically. An example:

• I have an application with a 1-hour refresh that disconnects/reconnects all idle connections
• We shutdown a VTGate: new connections are not allowed
• With Gracefully shutdown VTGate instances #14219, all its idle connection will error on the next query with Server shutdown in progress
• With this RFC, all its idle connections will stay alive and will be usable until they all disconnect (by the "refresh")
• Once all the connections are disconnected: we shutdown VTGate
• By now, we should not have returned a single error to the client as opposed to the current state which returns errors

Since this feature changes a lot of the connection shutdown mechanism, a new "feature" flag would be introduced.

Note that for this scheme to be practical, the application client to vtgate should refresh it's MySQL connections periodically; a period of something like 45-60 minutes is usually a good value, while not short enough to use largecause a lot of re-connection churn. If the vtgate drain timeout is set to an hour along with this, the "drain cycle" when shutting down a vtgate would then be something less than an hour, and not actually need to force-close any application MySQL connections, thus avoiding errors served to MySQL protocol clients. The "refresh" setting is often a feature of the MySQL client connector in use, e.g. in HikariCP the setting is called maxLifetime, and is set by default (to 30 minutes).

[harshit-gangal]

Ideally, this can be dealt with an Nginx server or load balancer in front of VTGates which can help redirect the traffic to the serving VTGates. For the established connection, I think this change will be helpful.

[deepthi]

this can be dealt with an Nginx server or load balancer in front of VTGates which can help redirect the traffic to the serving VTGates

Just to be clear - what you are suggesting here only works for new connections, right? For already established connections (and sessions), they will immediately see errors when vtgate starts shutting down.

[frouioui]

@deepthi that's what I understand too, since he said For the established connection, ....

[frouioui]

Closing this as all the PRs have been merged.