Search code examples
mysqlhaproxyfailover

What happens to in-flight mysql transactions during a write failover in haproxy?

I currently have haproxy configured to load balance and failover a set of mysql servers. I have a backend configured for write transactions like so:

backend pool_mysql_write
timeout connect 10s
timeout server 1m
mode tcp
option mysql-check user haproxy_check
server primary <primary_node>:3306 check fastinter 1000
server secondary <secondary_node>:3306 check fastinter 1000 backup

The "backup" directive is there so that all writes go to the primary DB node only and will failover to secondary only if primary goes down.

EDIT: The DB nodes are in master-master replication mode.

My question is, what happens to in-flight mysql write queries while haproxy is in the process of failing over to the secondary DB node? For example, if it takes 5 seconds for haproxy to promote the secondary DB node for writing, what happens to all of the write queries that may have been trying to write to the DB within the 5 seconds of failover time?

Do they just disappear? Do they get queued up somewhere so that they could be committed once the secondary DB node gets promoted?

Solution

  • When a new TCP connection to haproxy is initiated (by a client), it in turn opens a new TCP connection to the upstream server. Once that upstream TCP connection is established, haproxy just connects those TCP connections together, relaying communications back and forth.

    If either of those TCP connections (i.e. the one it has to the client, or the one it has to the upstream server) drop, haproxy simply drops the other one—in other words, haproxy won't handover existing TCP connections to alternative servers (it just redirects new connections). The counterparty is responsible for deciding what to do next. (For a smarter approach, you'd need a layer-7 proxy like MariaDB MaxScale, which can reroute existing connections).

    Typically, if its connection was dropped unexpectedly, the client will attempt to reconnect (whereupon haproxy could end up connecting it with a different upstream server, e.g. because the original one is no longer available).

    The question is, what if the client had sent commands to the original server that would have mutated its state?

    • If the client had received a commit acknowledgement prior to the connection dropping, then that the client will understand its writes to have been committed. You must therefore be certain that primary does not acknowledge commits until writes have been replicated to secondary—hence why I asked above how you are performing that replication: at very least, you want Semisynchronous Replication:

      MySQL replication by default is asynchronous. The master writes events to its binary log but does not know whether or when a slave has retrieved and processed them. With asynchronous replication, if the master crashes, transactions that it has committed might not have been transmitted to any slave. Consequently, failover from master to slave in this case may result in failover to a server that is missing transactions relative to the master.

      Semisynchronous replication can be used as an alternative to asynchronous replication:

      [ deletia ]

      While the master is blocking (waiting for acknowledgment from a slave), it does not return to the session that performed the transaction. When the block ends, the master returns to the session, which then can proceed to execute other statements. At this point, the transaction has committed on the master side, and receipt of its events has been acknowledged by at least one slave.

    • If the client had not received commit acknowledgement prior to the connection dropping, then the client should assume that the writes were not committed and handle accordingly—e.g. reattempt the transaction once a new connection is established.

      However, it is possible that primary had in fact committed the writes (and indeed replicated them to secondary) but then failed just before it sent the commit acknowledgement, or it sent the commit acknowledgement but the connection failed before it was received by the client. I don't think there's much one can do about this (comments please?), but the risk is extremely small (since no processing occurs between commits being completed and acknowledgements being sent).