Details
-
New Feature
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Duplicate
-
None
-
None
Description
When lazy_connect or master_reconnection is enabled and a session command is executed with no open connection, the command will always prefer the current master node as the sescmd replier. This is not always ideal as it forces an open connection to the current master node even if it will never get used.
Compared to master_failure_mode=fail_on_write, the session command will prefer the first available server for session commands. This behavior is much better for read-only workloads compared to how lazy_connect currently behaves.
As there's no way to know whether the intended workload will be read-write or read-only, the choice of the sescmd replier type is not known in advance. The ideal solution for this would be to defer the execution of the session commands, if possible, until the server actually ends up being used. For things like SET NAMES and other guaranteed-to-work commands, this would be perfectly fine. For things like COM_STMT_PREPARE the answer must come from the real server.
A short-term solution the problem of lazy_connect always preferring a master server is to detect whether it is enabled, whether a transaction is open and if master_failure_mode would allow the query to be executed. The behavior of server selection for this case is also somewhat inconsistent as with master_failure_mode=fail_on_write the commands can still end up being executed in a way where the master's response would be "wrong". I split this off into MXS-4776 where the lazy_connect behavior should be corrected to be consistent with master_failure_mode=fail_on_write and just be better overall.
The long term solution is described in MXS-4390 that implements deferred execution of session commands. From the lazy_connect perspective, this would be relatively simple to implement by just injecting a fake OK packet whenever situation is detected where there are no open connections and a new connection would be needed. This would effectively result in optimistic deferred execution of session commands that would then delay their execution until an answer from the actual server is needed.