MySQL async replication HA, part 3 – replicas die, and the reads you sent them

Series: 1 the build · 2 the source dies · 3 replicas die, and the reads you sent them

Sooner or later somebody looks at two idle replicas and asks why we’re paying for them. The usual answer is a ProxySQL rule that sends SELECTs their way. That’s a fine answer, but it changes what a dead replica means. I wanted to see that change in sysbench lines, so I killed a replica twice: once while the application only used the writer, and once after I’d routed reads to the replicas.

Both experiments use the cluster from part 1. The first one ran before the source failover in part 2, while mysql-1 was still the source.

A replica dies, and nobody is reading from it

I expected nothing at all from the application’s side. Every oltp_read_write transaction is pinned to hostgroup 10, mysql-3 sits in hostgroup 20, and nothing sent queries there yet.

docker kill mysql-3

The kill was at 01:16:02:

01:16:02 [ 605s ] thds: 4 tps: 855.82 qps: 17099.32 err/s: 0.00 reconn/s: 0.00
01:16:03 [ 606s ] thds: 4 tps: 1066.98 qps: 21339.67 err/s: 0.00 reconn/s: 0.00
01:16:09 [ 612s ] thds: 4 tps: 809.31 qps: 16152.09 err/s: 0.00 reconn/s: 0.00

err/s and reconn/s stayed at 0 through the kill and for the following minute, including after docker start mysql-3. ProxySQL had mysql-1 ONLINE in hostgroup 10, mysql-2 ONLINE in hostgroup 20, and mysql-3 SHUNNED in hostgroup 20:

Why shunned so fast? docker kill removes the container’s DNS name, so the monitor’s connect fails and the missed pings get the server shunned. The connect-log rows captured in those same seconds were still the earlier successful connects (about 0.4–2 ms, no error text). The error row for this hostname only showed up the next time I killed that container: connect_error = Unknown server host 'mysql-3' (-2). The connection graph barely moved, because the writer pool is the one sysbench was using.

After docker start mysql-3 the replication receiver was ON within a few seconds. ProxySQL, though, left the server SHUNNED for longer than the configured 10-second recovery window, and LOAD MYSQL SERVERS TO RUNTIME is what put it back to ONLINE. The application didn’t have to wait for that.

So a replica nobody reads from is invisible when it dies. I’d still want the alert, because you’ve lost a copy of the data and the next source failure has one candidate fewer. The application staying green isn’t the same as the cluster staying healthy.

Sending reads to the replicas

The rule goes in on the ProxySQL admin port:

DELETE FROM mysql_query_rules;
INSERT INTO mysql_query_rules (rule_id, active, match_digest, destination_hostgroup, apply, comment)
VALUES (1, 1, '^SELECT', 20, 1, 'reads to reader hostgroup');
LOAD MYSQL QUERY RULES TO RUNTIME;

It won’t move the SELECTs inside oltp_read_write, and that surprised me less than it might surprise you. The user sbuser has transaction_persistent=1, so the first statement of a transaction picks a server and the rest stay there. oltp_read_write starts with BEGIN, which goes to hostgroup 10. To get reads onto the replicas I left the read/write sysbench running and started a second client that sends only reads, outside a transaction, as text:

docker run --rm --network mysql-ha --entrypoint sysbench mysql-async-ha-sysbench:1 \
  /usr/share/sysbench/oltp_read_only.lua \
  --db-driver=mysql --mysql-host=proxysql --mysql-port=6033 \
  --mysql-user=sbuser --mysql-password=sbpass --mysql-db=sbtest \
  --tables=4 --table-size=10000 --threads=2 --time=0 \
  --report-interval=1 --mysql-ignore-errors=all \
  --db-ps-mode=disable --skip-trx=on \
  run

--skip-trx=on is the flag that lets the rule match. --db-ps-mode=disable matters too, because ProxySQL pins a prepared statement to a server when it’s prepared and never looks at the text again. The lines below start with a clock time because the lab prefixed one; a line from the command above starts at [ 10s ], so compare from thds onward.

The read/write client kept succeeding, with err/s 0 and anywhere from about 640 to 1000 tps depending on how busy the box was. Its SELECTs never moved. The admin digest showed the split that did happen: before the replicas were killed, hostgroup 10 had about 14.4 million calls and hostgroup 20 had 215 182 calls. The rule-hit counter (stats_mysql_query_rules) climbed with the read client, from 169 881 hits after a few seconds to 668 142 by the end of the fallback test below. The read client itself sat around 520–640 tps, reads only, err/s 0.

Then the replicas die

docker kill mysql-3 at 01:17:02:

01:17:02 [ 10s ] thds: 2 tps: 579.99 qps: 8121.88 err/s: 0.00 reconn/s: 0.00
01:17:03 [ 11s ] thds: 2 tps: 820.61 qps: 11490.60 err/s: 1.00 reconn/s: 0.00
01:17:04 [ 12s ] thds: 2 tps: 731.40 qps: 10249.63 err/s: 0.00 reconn/s: 0.00

One thread hit the dead replica. From the next second the reads ran on mysql-2, around 730–820 tps, err/s 0, and writes on mysql-1 never noticed.

docker kill mysql-2 at 01:17:14 left hostgroup 20 with no ONLINE server, and nobody had copied the writer into it:

01:17:14 [ 22s ] thds: 2 tps: 912.90 qps: 12775.59 err/s: 0.00
01:17:15 [ 23s ] thds: 2 tps: 266.83 qps: 3741.59  err/s: 0.00
01:17:16 [ 24s ] thds: 2 tps: 0.00   qps: 0.00     err/s: 2.00
01:17:17 [ 25s ] thds: 2 tps: 0.00   qps: 0.00     err/s: 0.00
01:17:18 [ 26s ] thds: 2 tps: 0.00   qps: 0.00     err/s: 2.00

Reads stopped. err/s alternates between 2.00 and 0.00 because each of the two threads fails, waits out the connect timeout, and fails again, and that went on for as long as hostgroup 20 held only shunned servers. ProxySQL doesn’t fall through to the user’s default hostgroup. The write client, still on hostgroup 10, didn’t notice a thing:

01:17:17 [ 680s ] thds: 4 tps: 1013.47 qps: 20281.40 err/s: 0.00 reconn/s: 0.00
01:17:22 [ 685s ] thds: 4 tps: 901.94  qps: 18028.73 err/s: 0.00 reconn/s: 0.00

The setting that sounds like a fallback

ProxySQL has a variable whose name promises exactly what I needed. At 01:17:23 I ran:

UPDATE global_variables SET variable_value='true'
 WHERE variable_name='mysql-monitor_writer_is_also_reader';
LOAD MYSQL VARIABLES TO RUNTIME;

Four seconds later the runtime table was still:

hostgroup_id  hostname  status
10            mysql-1   ONLINE
20            mysql-2   SHUNNED
20            mysql-3   SHUNNED

Reads stayed at 0 tps through 01:17:26, and ProxySQL logged read_only_action_v2() detected RO=0 on server mysql-1:3306 for the first time after commit(), but no need to reconfigure. On ProxySQL 2.7.3 the variable is consulted when a server changes to read_only=0 and has to be moved. A server that’s already the writer is left where it is. The replication-hostgroup table has no column of its own for “writer is also a reader” either; that column exists only on the group-replication and Galera tables.

What did restore reads was putting the current writer into hostgroup 20 by hand:

INSERT INTO mysql_servers (hostgroup_id, hostname, port, max_connections, status, comment)
VALUES (20, 'mysql-2', 3306, 200, 'ONLINE', 'writer also reader');
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

I measured this one after the failover in part 2, so the writer was already mysql-2 and the only live replica was mysql-3. docker kill mysql-3 at 01:23:33 dropped the read client to 0 tps, and the insert ran at 01:23:40. The same second, reads resumed:

01:23:33 [ 21s ] thds: 2 tps: 438.23 qps: 6156.28  err/s: 0.00
01:23:34 [ 22s ] thds: 2 tps: 0.00   qps: 0.00     err/s: 0.00
01:23:35 [ 23s ] thds: 2 tps: 0.00   qps: 0.00     err/s: 1.00
01:23:39 [ 27s ] thds: 2 tps: 0.00   qps: 0.00     err/s: 1.00
01:23:40 [ 28s ] thds: 2 tps: 635.37 qps: 8893.20  err/s: 0.00
01:23:41 [ 29s ] thds: 2 tps: 834.58 qps: 11686.17 err/s: 0.00
01:23:45 [ 33s ] thds: 2 tps: 888.99 qps: 12449.87 err/s: 0.00

The extra row stayed, too. Turning the global back to false didn’t remove it, because once ProxySQL has seen read_only=0 it doesn’t revisit that copy. Writes stayed on hostgroup 10 at around 700–750 tps, err/s 0, while the same server answered the reads through hostgroup 20.

The failover hook from part 2 doesn’t do this insert. It doesn’t need to for the default workload, which never reads from hostgroup 20. If you add the SELECT rule, though, put the current writer into hostgroup 20 as well, or accept that losing every replica fails your reads while writes carry on.

When something goes wrong

These are the failures I hit while building and breaking this lab across all three parts.

Before you take this shape to production

The shape is what you’d recognise in production: a source, two replicas, a proxy and an external failover manager. The settings aren’t, and part 2 covers the ones that matter for a source failure. Two more belong here.

ProxySQL and Orchestrator are each a single container in this lab, and losing either one is an outage of its own. In production I’d run more than one proxy, and often more than one Orchestrator process, so the failover tool can die without taking the cluster with it. The passwords and buffer pool in these files are for a laptop, and so is the missing TLS. sync_binlog=1 and innodb_flush_log_at_trx_commit=1 are set so a commit is on disk on that server, but they do nothing about the replication gap.

To clean up, run ./scripts/lab.sh teardown from the lab folder. It runs docker compose -f docker/docker-compose.yml down -v --remove-orphans, which deletes the containers and the data volumes along with the mysql-ha network. The read-only sysbench you started by hand isn’t part of Compose, so remove it with docker rm -f if it’s still running.

Verdict

A replica the application doesn’t read from can die without anyone noticing, which is exactly why it needs its own alert. Once you route reads to replicas, their failures become your application’s failures: one replica left was enough, and zero meant every read failed while writes carried on happily. Don’t count on mysql-monitor_writer_is_also_reader to save you on ProxySQL 2.7.3. If you want reads to fall back to the source, put the writer into the reader hostgroup yourself, and make your failover hook do the same for the new source.