Cases12 cases

9 to 12 of 12

1. IL-0300 Basecamp 3 went read-only for almost five hours when its events table reached the ceiling of 2,147,483,647

   37signals2018

   Symptoms

   On November 9th, 2018, from 7:21am to 12:11pm CST, Basecamp 3 was in read-only mode. Users could open existing messages, to-do lists and files, but could not add anything new or change anything that was already there.

   Writes rejected

   Trigger

   The busy events table, which records almost every activity in Basecamp 3, reached its ceiling of 2,147,483,647, and new events could not be written.

   Root cause

   Capacity exhaustion

   Component

   Database

   Contributing factor

   No capacity planning

   Remediation

   • Raised the ceiling of the tables from 2,147,483,647 to 9,223,372,036,854,775,807, the same change as the Rails 5.1 default

   Lessons

   The outage was avoidable. The team did not account for how quickly the events table was running out of headroom while it was already working on the problem, and it should have reviewed its own database schema when Rails 5.1 announced the new default in 2017.

   View case
2. 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
3. IL-0335 A bugcheck in the primary SQL Server put the Stack Exchange network into read-only mode and then offline for 12 minutes

   Stack Exchange2017

   Symptoms

   On January 24, 2017 at 17:53 UTC the Stack Exchange network, including Stack Overflow, went into read-only mode for about 5 minutes and then went offline for 12 minutes. It took the team 2 minutes to notice the problem, 5 minutes to locate its source and 10 minutes to restore service.

   Writes rejectedService outageFailover failureData loss

   Trigger

   At 17:53 UTC the primary SQL Server, NY-SQL02, triggered a bugcheck in the SQL Server process.

   Root cause

   unknown

   Component

   Database

   Contributing factor

   Single point of failure

   Remediation

   • Restarted the SQL service on NY-SQL02, which brought the network back online in read-only mode
   • Put the sites back into read-write mode after a sanity check of SQL health
   • Brought the network online without waiting for the memory dump to complete
   • Took NY-SQL02 out of production for testing, including a memory test, and ran the normal load on NY-SQL01
   • Brought the SQL cluster up to 2016 SP1 CU1

   View case
4. IL-0320 Postmark's primary MySQL disk filled but MySQL kept running, so MySQL-MMM never failed over and sending was delayed

   Postmark2011

   Symptoms

   At about 8:15am EDT Postmark's primary MySQL server ran out of disk space, and email sending, the API and the web interface were delayed. Disk space alerts fired, but the space had grown much faster than usual and MySQL was already struggling when the alerts were noticed. MySQL kept running while it failed to write some data to disk, so MySQL-MMM, which normally detects problems and fails over to a healthy server, never triggered a failover; the team spent about 30 minutes trying to free logs and disk space before failing over by hand.

   Node disk fullWrites rejectedFailover failure

   Root cause

   Capacity exhaustion

   Component

   DatabaseDatabase failover manager

   Contributing factor

   –

   Detection

   Per-server monitoring raised disk space alerts, but the disk filled so quickly that MySQL was already struggling by the time the alerts were noticed.

   Remediation

   • Tried to free up logs and disk space on the primary server
   • Failed over manually to the second MySQL server
   • Recovered the jobs that had failed
   • Sent the emails that each server had queued locally while the database was unavailable

   Action items

   • Upgrade the MySQL cluster with a focus on availability and performance
   • Make the alerts more sensitive to catch problems sooner

   Lessons

   Looking back, Postmark said it should have failed over to the second server right away instead of first making sure replication was healthy.

   View case

By root cause6