SAA Lab 03 - Fail a database over and time the gap

Scaffold: 4/5. The VPC, the Multi-AZ RDS for PostgreSQL instance, the EC2 writer, its log group and the three scripts that drive the measurement are all built and wired. What is missing is the writer’s connection handling: the client-side code that decides how much of the outage your users actually see.

The scenario

AWS publishes a number for Multi-AZ failover: typically 60 to 120 seconds for a Multi-AZ DB instance, typically under 35 seconds for a Multi-AZ DB cluster with two readable standbys. The same documentation tells you to go and measure it yourself, because that number describes what RDS does. Between RDS finishing and your users being served again sit the DNS record, the client’s resolver, the TCP socket the application already had open, and whatever the application does with a query that comes back as an error.

A writer inserts one row a second and logs every attempt. You reboot the primary with failover, compare what the RDS event log says against what the writer’s own log says, then fix the writer and measure again.

The requirement

Rewrite connect(), write_once() and the except branch of main() in src/writer.py so the writer recovers as fast as the database does. Three changes, all client-side; nothing in AWS moves.

  1. A socket deadline. pg8000 defaults timeout to None, which leaves the socket with no deadline at all. The insert in flight when RDS interrupts the primary does not come back with an error; it sits in a blocking read while the kernel retransmits, and tcp_retries2 on Linux defaults to a quarter of an hour.
  2. Close the connection, don’t just drop it. The loop as shipped sets conn = None and leaves the old object to the garbage collector, holding a socket open against a host that is no longer the primary. Close it where the error is logged. The standby that got promoted is a different host on a different address, so rebuilding rather than reusing is what sends the client back through the resolver.
  3. Drop the backoff. A failover is an absence with a known duration, not an overloaded service. Thirty seconds of politeness against an outage of sixty to a hundred and twenty adds its own length to the gap.

Run it

./scripts/deploy.sh          # VPC, Multi-AZ instance, writer; ~15 minutes
./scripts/failover.sh        # reboot with failover, wait for RDS-EVENT-0049
./scripts/measure.py         # print both intervals
./scripts/teardown.sh

Give deploy.sh about fifteen minutes the first time, because a Multi-AZ instance provisions two of everything. Later runs re-upload writer.py and restart the systemd unit without touching the stack. Let the writer run for a couple of minutes before you fail anything over, so there is a clean baseline.

Then apply your change, run deploy.sh again to push the new writer.py, wait for a fresh baseline, and fail it over a second time.

Run it a third time before you believe any of it. The RDS-side interval moves around with database activity and crash recovery, and the writer’s gap moves around with where in the cadence the failover lands. One measurement is an anecdote.

Then break it on purpose

Set timeout back to None in your fixed writer, leave the rest of the fix in place, and measure again. The reconnect is still right and the backoff is still gone, and the gap goes back to minutes on any failover where the socket is not torn down promptly. That one keyword argument is the largest term in most of these measurements.

Reveal the solution

SRC=solution ./scripts/deploy.sh && ./scripts/failover.sh && ./scripts/measure.py

Cost

A db.t4g.micro Multi-AZ instance with 20 GB of gp3, one t4g.micro, an S3 bucket holding one file, and a day of CloudWatch Logs retention. Small, and the database bills for two of everything while it exists. Tear down when you are finished; the lab reaper is a backstop, not a plan.

Untested

Every script here is syntax-checked and every template parses, but this bundle has not been deployed against a real AWS account end to end. Treat the first run as the shakedown.