Cases4 cases

1 to 4 of 4

1. 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
2. IL-0210 GitHub's MySQL clusters failed over to the US West Coast and the site served slow, stale, inconsistent data for 24 hours

   GitHub2018

   Symptoms

   At 22:54 UTC on 21 October 2018 GitHub's internal monitoring started alerting on many faults at once. By 23:02 UTC the first responders saw that the replication topologies of many MySQL clusters were in an unexpected state: the Orchestrator API showed topologies made only of servers in the US West Coast data center. When connectivity came back, the application tier had immediately started sending writes to the new West Coast primaries.

   Split brainFailover failureReplication lagHigh latency

   Trigger

   At 22:52 UTC on 21 October, routine maintenance to replace failing 100G optical equipment cut connectivity between GitHub's US East Coast network hub and its primary US East Coast data center for 43 seconds.

   Root cause

   DNS or network fault

   Component

   DatabaseDatabase failover managerDatabase replicationDatabase backups and snapshotsNIC, cable and switch port link

   Contributing factor

   Automation amplificationUnverified assumption

   Detection

   GitHub's internal monitoring raised alerts on numerous faults at 22:54 UTC, two minutes after the partition. By 23:02 UTC the first responders had found, by querying the Orchestrator API, that many database clusters had a topology with only US West Coast servers.

   Remediation

   • Locked the internal deployment tooling to stop further changes
   • Paused webhook delivery and GitHub Pages builds to stop jobs writing metadata
   • Restored the affected MySQL clusters from backups into the US East Coast data center and let them replicate from the West Coast
   • Found ways to restore directly from the West Coast to get around the throughput limits of downloading from off-site storage
   • Moved all database primaries back to the US East Coast data center
   • Spread read traffic over additional MySQL read replicas in the US East Coast public cloud so replication could catch up
   • Failed over to the original topology once the replicas were in sync
   • Processed the backlog of webhook events and Pages builds, pausing to raise an internal TTL after about 200,000 webhook payloads were dropped

   Action items

   • Reconcile the writes from the captured MySQL binary logs that were not replicated, automatically where possible and with user outreach otherwise
   • Make sure status updates can be published even when GitHub Pages builds are paused
   • Give more accurate public estimates of the time to repair
   • Configure Orchestrator so it cannot promote database primaries across regional boundaries
   • Move to a status reporting mechanism that shows the status of each component of the platform
   • Serve GitHub traffic from multiple data centers in an active/active/active design with N+1 redundancy at the facility level
   • Test assumptions about the system more proactively
   • Validate failure scenarios systematically, with investment in fault injection and chaos engineering tooling

   Lessons

   Orchestrator did what it was configured to do, but the application tier could not work with primaries on the other side of the country: leader election within one region is generally safe, while the sudden cross-country latency was a major contributing factor. The behaviour emerged from a partition larger than any GitHub had seen. Replication caught up along a power decay curve, not a straight line, so the linear estimates of recovery time were wrong. GitHub concluded that tighter operational controls and faster response are not enough on their own and that failure scenarios have to be validated before they happen.

   View case
3. IL-0218 GitLab.com's primary PostgreSQL data was wiped by mistake and, with no working backup, the site was down about 18 hours

   GitLab2017

   Symptoms

   From around 19:00 UTC on 31 January 2017, load on GitLab.com's PostgreSQL database rose, which GitLab suspected was spam, and many users could not post comments on issues and merge requests. Around 23:00 UTC the replication of the PostgreSQL secondary fell behind and then failed, because WAL segments it still needed had already been removed from the primary.

   Database overloadReplication lagData lossService outage

   Trigger

   While trying to restore replication to the secondary, an engineer ran the removal of the PostgreSQL data directory on the primary, db1.cluster.gitlab.com, believing it was the secondary.

   Root cause

   Operator error

   Component

   DatabaseDatabase replicationDatabase backups and snapshotsCloud block storage

   Contributing factor

   Untested backupSingle point of failureNo runbookPages not deliveredUnverified assumptionUnreviewed defaults

   Detection

   The engineer who ran the deletion on the primary noticed the mistake and stopped it a second or two later.

   Remediation

   • Raised max_wal_senders from 3 to 32 and lowered max_connections from 8000 to 2000 so PostgreSQL would restart
   • Stopped the deletion on the primary a second or two after noticing it
   • Restored the database from the LVM snapshot taken about 6 hours before the outage by copying the PostgreSQL data directory from staging
   • Restored the webhooks from a separate database built from the snapshot and imported them as a SQL dump
   • Incremented all database sequences by 100,000 so IDs used before the outage could not be reused
   • Re-enabled GitLab.com gradually
   • Rebuilt projects created after 17:20 UTC in basic form from their Git repositories on the NFS cluster

   Action items

   • Change the shell prompt (PS1) on all hosts to show more clearly which host and environment is in use
   • Monitor backups with Prometheus
   • Set PostgreSQL's max_connections to a sane value
   • Investigate point-in-time recovery and continuous archiving for PostgreSQL
   • Take LVM snapshots of the production databases every hour
   • Take Azure disk snapshots of the production databases
   • Move staging to the ARM environment
   • Recover the production replicas
   • Test recovery of PostgreSQL database backups automatically
   • Improve the PostgreSQL replication documentation and runbooks
   • Investigate pgbarman for PostgreSQL backups
   • Investigate WAL-E for database backup and realtime replication
   • Build a streaming database restore
   • Assign an owner for data durability
   • Set up multiple secondaries and balance load among them with pgpool-II
   • Provide a tool for developers to run and revert Rails migrations on staging
   • Build owned recovery procedures for the rest of the GitLab.com infrastructure, not only the database
   • Publish a backup monitoring dashboard and add more backup data to it
   • Improve the abuse reporting and response system so removal for spam does not hard delete
   • Redact engineers' names in future public incident documents

   Lessons

   GitLab split the incident into two problems: GitLab.com went down because the primary's data directory was removed by mistake during a manual, undocumented replication rebuild, and recovery took over 18 hours because the backups had failed silently, disk snapshots were not enabled and the only copy sat on slow staging disks. The backup procedure was never tested because no one owned it. GitLab chose to focus on fast, regularly practised recovery and on making it obvious which host an engineer is on, rather than on blocking commands, since a safer rm only protects against one of many ways to lose data.

   View case
4. IL-0319 Dropping databases on the MySQL replica mysql2 also dropped them on mysql1, as the pair actually ran master-master

   Oregon State University Open Source Lab2015

   Symptoms

   On July 30th, a few minutes after the OSU Open Source Lab started reloading the MySQL server mysql2 to fix its replication errors, the team saw that databases were being dropped on mysql1 as well. The script was stopped, and the MySQL service behind mysql1-vip stayed down through a long restore.

   Data lossService outageReplication lag

   Trigger

   On July 30th at 4:00PM PDT the team stopped slave replication on mysql2 and began dropping its databases one at a time to reload it and fix replication errors.

   Root cause

   Operator error

   Component

   DatabaseDatabase replicationDatabase backups and snapshots

   Contributing factor

   Unverified assumptionUntested backup

   Detection

   The team watching the reload noticed after a few minutes that mysql1 was also dropping databases; the delays between drops in the script gave them that time.

   Remediation

   • Stopped the script that was dropping databases
   • Restored the databases from the mysql-zrm backups
   • Pulled each database that failed to restore out of the 200GB backup file and restored it separately
   • Recovered most data with the binary logs from the 17th through the 30th

   Action items

   • Evaluate better replication strategies, including row-based replication
   • Store binlogs as a backup on a separate server
   • Take backups with Percona XtraBackup for much faster full restores
   • Use mydumper instead of mysql-zrm for faster logical backups
   • Improve documentation and training for complex systems
   • Test full restores regularly on a spare server as part of the backup process
   • Gather more accurate time estimates for the restore process
   • Audit the hosted databases regularly, since test and very large databases delayed the restore
   • Migrate to a bigger, more powerful MySQL cluster
   • Set a policy and tools to keep the community informed of outages, maintenance and resource use, with dashboards
   • Define and monitor capacity limits for core services
   • Implement redundancy and restore practices for core services, including staff drills
   • Migrate more core services to Chef
   • Review documentation, policies and performance metrics periodically
   • Find better ways to draw on community expertise
   • Raise funds to replace the oldest infrastructure and reduce technical debt

   Lessons

   The lab had stretched its resources to host as many projects as possible, and that came with tradeoffs such as heavily compressed backups to save disk and less redundancy than wanted; the heavy compression made this restore CPU-bound. The lab concluded that it needs to change how it plans, monitors and manages resources, and that the length of the outage and the lack of progress reports were not acceptable.

   View case

By root cause3