Cases18 cases

8 to 12 of 18

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-0255 Basecamp's two redundant links between data centers dropped together and a Redis replica resync loop stalled chat

   37signals2020

   Symptoms

   At 11:59AM CDT on Friday 28 August 2020, Basecamp's Ops team received a flood of alerts from PagerDuty, Nagios and Prometheus. Monitoring showed that the newer of the two metro links between the data centers in Ashburn, VA and Chicago, IL had been lost for about 20-30 seconds, and BC3 kept responding slowly after the link came back.

   Link flappingHigh latencyReplication lag

   Trigger

   Both redundant metro links between Basecamp's Ashburn and Chicago data centers dropped for short periods; monitoring showed the newer link down for about 20-30 seconds.

   Root cause

   Dependency failure

   Component

   Backbone network (WAN)BGP routingIn-memory cacheDatabase replication

   Contributing factor

   Single point of failureRetry stormSignal buried in noise

   Detection

   A flood of alerts from PagerDuty, Nagios and Prometheus, with monitoring showing the lost metro link. A lingering alert for one Redis DB instance later pointed to the replication problem.

   Remediation

   • Turned chat off at the load balancer layer so the rest of BC3 could stabilise
   • Restarted Redis on the replica that was stuck in the resynchronization loop

   Action items

   • Make the documented Redis configuration changes for the known replication issue
   • Make concrete plans to remove the shared physical path under the two metro links
   • Prove the failover systems in production at peak load
   • Make explicit room for post-incident follow-up work and push other work out

   Lessons

   Planned redundancy and live testing had given Basecamp a false sense of security, especially at peak load: moving between redundant network links or shifting load across data centers should work without issue, and Basecamp would rather find out in a controlled test with the full team ready. The follow-up from an outage needs urgency and explicitly cleared time, not just a place on a list.

   View case
3. 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
4. IL-0443 A Repro Aurora MySQL reader restarted unexpectedly and writer insert latency rose about sevenfold for a day or more

   Repro2019

   Symptoms

   A reader of Repro's Aurora MySQL cluster restarted for no known reason, and right after that the writer's insert latency rose to about seven times normal and writer performance stayed badly degraded for a long time. This happened more than once, twice within two weeks by 11 November 2019. While the writer was slow, the applications that process data collected by the Repro SDK fell behind, and the team could only watch until the writer recovered.

   High latencyReplication lagThroughput degradation

   Trigger

   Long-running queries on the reader; the author recalls that the cause was a query sent to the reader from Redash.

   Root cause

   Capacity exhaustion

   Component

   DatabaseDatabase replication

   Contributing factor

   Monitoring blind spot

   Remediation

   • Waited for writer performance to return, since the rollback segment release could not be sped up

   Action items

   • Add a monitor on the rollback segment, mysql.innodb.history_list_length in Datadog
   • Kill queries that run long enough to make the reader restart as soon as they are found
   • Set max_execution_time so queries are stopped after a fixed time

   Lessons

   Once the rollback segment has grown large, nothing speeds up its release across the cluster, so the way to avoid the latency is to keep transactions small and stop long-running queries before the rollback segment grows.

   View case
5. IL-0323 A RAID battery backup unit failure crashed db1075, the MariaDB primary of section s3, and every s3 wiki went read-only

   Wikimedia Foundation2019

   Symptoms

   At 18:40 UTC db1075, the primary database of section s3, stopped answering, and all s3 wikis went read-only because no writes could reach it. Reads kept working from the replicas. At 18:42:45 Icinga reported "PROBLEM - Host db1075 is DOWN: PING CRITICAL - Packet loss = 100%".

   Writes rejectedReplication lagHigh latency

   Trigger

   The RAID battery backup unit (BBU) of db1075, the s3 primary database host, failed and the whole host crashed.

   Root cause

   Hardware failure

   Component

   DatabaseDatabase replication

   Contributing factor

   Pages not deliveredHarmful manual action

   Detection

   Icinga reported db1075 HOST DOWN on IRC at 18:42:45, but that alert does not page; replica Slave IO alerts followed, users reported problems on #wikimedia-operations, and the first SMS page went out at 18:56. Volunteers and staff noticed the failure before the alerts did.

   Remediation

   • Power-cycled db1075 from the iDRAC after finding the BBU problem
   • Let MySQL finish InnoDB recovery on db1075
   • Removed read_only=ON from db1075 by hand
   • Ran puppet by hand a second time so pt-heartbeat started

   Action items

   • Implement or refactor a script that moves replicas when the primary is unavailable
   • Fix the MediaWiki heartbeat model so pt-heartbeat does not use a super-user, avoids a single point of failure and follows the real primary without depending on puppet
   • Decide what to do with the batch of hosts that already had BBU issues
   • Buy a new BBU for db1075
   • Remove db1075 from the primary role
   • Address MediaWiki spam while the database is read-only or the primary is unavailable
   • Make primary database hosts page on HOST DOWN
   • Track hardware errors better in Netbox

   Lessons

   A BBU failure should not crash a whole host, yet it had happened before on HP hosts. HOST DOWN on a primary database should page instead of only alerting on IRC. Starting pt-heartbeat through puppet races with MySQL startup, and the unnoticed failure kept lag reported and made the outage 8 minutes longer.

   View case

By root cause9