Cases2 cases

1 to 2 of 2

1. IL-0392 MariaDB replication of section x1 between eqiad and codfw broke on a missing heartbeat row during a cookbook run

   Wikimedia Foundation2024

   Symptoms

   On 18 September 2024, while a cookbook was reconfiguring Wikimedia's core MariaDB databases from one-way eqiad -> codfw replication to circular replication ahead of a datacenter switchover, replication in the codfw -> eqiad direction broke on the x1 and es6 sections. Engineers watching the run noticed at 21:28 UTC that the x1 primary was not replicating.

   Replication lag

   Trigger

   Running the cookbook sre.switchdc.databases.prepare, which set up codfw -> eqiad replication and started pt-heartbeat, on the row-based x1 and es sections.

   Root cause

   Configuration change

   Component

   DatabaseDatabase replication

   Contributing factor

   Monitoring blind spotGap in automated testsHarmful manual action

   Detection

   Engineers watching the first run of the automation saw on orchestrator that the x1 primary was not replicating, because monitoring of circular replication was not yet enabled. The break in the eqiad -> codfw direction was reported by Icinga.

   Remediation

   • Skipped replication of the heartbeat tables with Replication_wild_ignore_table='heartbeat.%'
   • Inserted the missing heartbeat row with binary logging off on the es hosts
   • Inserted the missing heartbeat row with binary logging off on the x1 codfw hosts and removed the ignore rule

   Action items

   • Avoid the same issue again, probably by inserting the missing row when it is detected
   • Add a post-maintenance health check that replication works in both directions
   • Add options to run the maintenance in another order or on each section individually
   • Update the Phabricator task for each section rather than only at the end
   • Fix the monitoring gaps for circular replication
   • Made pt-heartbeat-wikimedia use STATEMENT format for its session on the x1 and row-based read-write es hosts, fixed in a later deploy

   Lessons

   Circular replication is not a normal working mode, so the first fix only moved the break to the other side; TRUNCATE would have worked better than DELETE because it always replicates as a statement, but it was not considered because it is more dangerous. Running sql_skip_slave_counter was avoided, since it could have made things worse or lost data. The check of the replication channel should also run once all tasks are complete, not only right after setup. The combination of row-based replication, pt-heartbeat and differing heartbeat rows was not described in earlier maintenance or documentation.

   View case
2. IL-0331 An RDS MySQL Multi-AZ failover lost 5 seconds of committed data and broke replication on all five read replicas

   Razorpay2020

   Symptoms

   On a Friday evening in December 2019, Razorpay's internal monitoring alerted on an unusually high number of 5xx errors. The SRE team found that the main application, called API, could not connect to its RDS MySQL database, but within about 110 seconds the application recovered by itself and the alerts stopped. The RDS logs showed that the master instance had gone through a Multi-AZ failover.

   High error rateReplication lagData loss

   Trigger

   The master instance of the API application's RDS MySQL database went through a Multi-AZ failover to its standby in another availability zone.

   Root cause

   Configuration change

   Component

   DatabaseDatabase replicationDatabase backups and snapshots

   Contributing factor

   –

   Detection

   An internal monitoring alert on unusually high 5xx errors, then alerts that replication had failed on all replicas. The data loss was found the next day by matching the database against the application trace logs.

   Remediation

   • Moved all read replica traffic to the master instance
   • Recreated all five read replicas after a six-hour full snapshot and moved traffic back to them
   • Recovered the missing queries from the binary logs and corrected the data stored in RDS
   • Changed innodb_flush_log_at_trx_commit on the master from 2 to 1

   Lessons

   With innodb_flush_log_at_trx_commit set to 2, Razorpay had been open to data loss on any failover all along, even though RDS Multi-AZ copies everything on the primary's disk to the standby. The incident also taught the team how RDS runs a Multi-AZ setup and its failovers.

   View case

By root cause1