Cases10 cases

6 to 10 of 10

1. IL-0334 Instapaper's RDS MySQL bookmarks table hit the 2TB ext3 file size limit, inserts failed and the service went down

   Instapaper2017

   Symptoms

   On Wednesday, February 9, 2017 at 12:30PM PT, inserts of new entries into the bookmarks table of Instapaper's MySQL database on Amazon RDS started to fail with an error. The bookmarks table, which stores the articles Instapaper users save, had grown past 2TB, and the Instapaper service was down until Thursday, February 10 at 7:30pm PT.

   Writes rejectedService outage

   Trigger

   At 12:30PM PT on February 9 the bookmarks table grew past the 2TB file size limit of the instance's filesystem.

   Root cause

   Capacity exhaustion

   Component

   DatabaseDatabase backups and snapshotsDatabase replication

   Contributing factor

   Unknown limitMonitoring blind spotUntested backupNo runbookLimited debug access

   Remediation

   • Dumped the production database to a 5.5TB RAID-0 disk on an i2.8xlarge instance with Pinterest's SRE team
   • Launched a temporary production instance with limited access to archives
   • Set up an Aurora read replica of the old database as one recovery path
   • Imported the data into a new MySQL RDS instance without secondary indexes, then built the indexes
   • Had an Amazon engineer rsync the data from the ext3 filesystem to an ext4 filesystem mounted on the failed database
   • Synced the new ext4 database with the temporary production database through row-based replication
   • Promoted the new database to master and pointed the application at it

   Action items

   • Define a workflow that escalates system-wide Instapaper outages immediately to Pinterest's SRE team
   • Test MySQL backups every month instead of every three months
   • Put a process in place with Pinterest's SRE team to recover from this type of failure

   Lessons

   A filesystem limit that Instapaper did not know about and could not see made both the production database and all its backups useless, and the only way out was to restore the data onto a new filesystem. Because the hosted database could only be reached through MySQL, filesystem-level fixes such as rsync needed Amazon engineers. Even with a perfect response, rebuilding the database would have taken at least 10 hours. The lack of a disaster recovery plan for this kind of failure, and the estimate of the dump time from row counts, made the downtime and recovery longer than necessary, and the response could have drawn better on Pinterest's and AWS's people. The new escalation workflow and monthly backup tests would not have prevented this failure but would speed up the response to a future one.

   View case
2. 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
3. IL-0573 A developer's mistake dropped nine main tables of the Baemin billing MariaDB database and payments briefly stopped

   Woowa Brothers2016

   Symptoms

   At 15:00 on 27 December 2016, while a developer was working on a payment system module, nine main tables of the billing database were dropped. The monitoring system turned red, failure alerts poured in, and payments in 배달의민족 (Baemin) stopped for a while.

   Data lossService outage

   Trigger

   While developing a payment system module, the developer who wrote the post dropped nine main tables of the billing database by mistake.

   Root cause

   Operator error

   Component

   DatabaseDatabase backups and snapshots

   Contributing factor

   –

   Detection

   The monitoring system turned red and failure alerts fired.

   Remediation

   • Pointed the application at a temporary database, which kept the outage short
   • Restored the dropped tables from the 6:00 mysqldump full backup
   • Replayed the binary log from 6:00 to 15:00 with mysqlbinlog, filtered to the nine tables and stopping before the DROP TABLE

   Action items

   • Replace mysqldump backups of the billing database with full backups taken by XtraBackup

   Lessons

   There are many points where such failures can be managed: separate database accounts for the service and for administration, check once more before running a query, and guard against rm -rf. Without the full backup and the binary log the data could not have been recovered, so every team should check that it has a full recovery point for a failure.

   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
5. IL-0485 Dropbox went down when an OS upgrade script reinstalled active MySQL master-replica pairs and backup restores ran slow

   Dropbox2014

   Symptoms

   On Friday evening, during scheduled maintenance that started at 5:30 PM PT, the Dropbox service went down. Most functionality came back about three hours later, but core service was not fully restored until 4:40 PM PT on Sunday.

   Service outage

   Trigger

   A planned maintenance on Friday at 5:30 PM PT upgraded the operating system on some machines with a script that was meant to check that a machine held no active data before reinstalling it.

   Root cause

   Software defect

   Component

   DatabaseDatabase backups and snapshots

   Contributing factor

   No safeguard on destructive change

   Remediation

   • Restored the affected databases from backups

   Action items

   • Add a check that makes each machine verify its own state locally before running incoming commands, so machines running critical processes refuse destructive operations
   • Use a new tool that replays binary logs in parallel to recover large MySQL backups faster
   • Open source the parallel binary log replay tool

   Lessons

   Scripts that verify a machine's production state remotely before upgrading or repurposing it can be wrong, so machines need to check their own state before accepting destructive commands. Replicas give redundancy, but when every replica is lost the restore from backup decides the length of the outage, and the standard MySQL restore tool is slow on large data sets.

   View case

By root cause6