Cases21 cases

16 to 20 of 21

1. IL-0301 Basecamp 3's primary database went into total gridlock at the top of the hour and Basecamp 3 went down twice

   37signals2022

   Symptoms

   At 13:00 UTC on Monday, June 27, 2022, Basecamp 3 became extremely slow, then showed error pages, and then stopped working altogether. Basecamp 2 and Basecamp Classic kept working normally.

   Lock contention or deadlockDatabase overloadHigh latencyHigh error rateService outage

   Trigger

   At 13:00 UTC, when Basecamp 3 sends scheduled notifications in massive batches, a rush of demanding queries reached the primary database and started a cascade of heavy lock contention.

   Root cause

   Capacity exhaustion

   Component

   Database

   Contributing factor

   No capacity planningHarmful manual actionMonitoring blind spot

   Detection

   The team saw the Basecamp 3 primary database become completely congested within 30 seconds of 13:00 UTC and responded immediately.

   Remediation

   • Paused and disabled parts of Basecamp 3, such as delaying progress report emails
   • Failed over from the primary database to a replica, which brought Basecamp 3 back until it tipped over again
   • Put Basecamp 3 into maintenance mode
   • Set hard limits on database query time
   • Brought features back online step by step with the query limits in place

   Action items

   • Keep hard limits on database query time so slow queries fail instead of piling up
   • Tune the limits on concurrency
   • Treat top-of-the-hour database load spikes from scheduled batches as unacceptable
   • Reduce the number of parallel workers for some scheduled jobs
   • Let some scheduled activities run over several minutes instead of all at once
   • Move bursty queries from the primary database to read-only replicas
   • Fix the quiet performance regressions found in a review of the past year of database metrics
   • Find and spread out other bursty activities that concentrate load on the database
   • Tune the database for more predictable behaviour under heavy load and high concurrency
   • Add observability into the database's internal locking and transaction states
   • Build tools to switch off high-load Basecamp 3 features selectively during an incident
   • Run operational fire drills for a wedged database
   • Evaluate upgrading the database software and hardware
   • Set service level objectives for the components behind Basecamp 3
   • Hold quarterly performance and load reviews to catch slow, subtle changes

   Lessons

   A system running just past its limits can flip from stable to unstable without any particular trigger, and steady growth plus small regressions that raised no alarm had brought Basecamp 3's primary database to that point. Under lock contention, terminating queries does not relieve the database, because rolling back their transactions waits on the same locks. The team struggled to respond with confidence because there was no single clear cause and little visibility into the database's internals.

   View case
2. IL-0385 GitHub MySQL read replicas hit a semaphore deadlock during a schema migration's table rename and kept crash-recovering

   GitHub2021

   Symptoms

   On 27 November 2021 at 20:40 UTC, a significant part of GitHub's MySQL read replicas entered a semaphore deadlock while a schema migration on a large MySQL table ran its final step, the rename that moves the updated table into place. Core GitHub services, including GitHub Actions, API Requests, Codespaces, Git Operations, Issues, GitHub Packages, GitHub Pages, Pull Requests and Webhooks, became degraded.

   Lock contention or deadlockDatabase overload

   Trigger

   The final rename step of a schema migration on a large MySQL table. Schema migrations are a common task at GitHub and often take weeks to complete.

   Root cause

   Software defect

   Component

   DatabaseSchema migration

   Contributing factor

   No headroomNo staged rollout

   Remediation

   • Promoted every healthy internal replica into the production path to add read capacity
   • Removed production traffic from the broken replicas until each had processed the table rename
   • Moved the recovered replicas back into production to restore capacity

   Action items

   • Continue functional partitioning so migrations can run in canary mode on a single shard
   • Update internal procedures to increase how much each cluster is over-provisioned
   • Pause schema migrations until it is known how to safeguard against this failure
   • Keep investigating the specific failure scenario
   • Test the migration tooling and classify ways to improve it for this kind of scenario

   View case
3. IL-0441 A ZOZO SQL Server database stalled and applications hit timeout storms whenever batch executions peaked in sales

   ZOZO2021

   Symptoms

   From around mid-2020, a SQL Server database behind one ZOZO feature made the applications throw many timeout errors whenever batch executions per second passed a certain threshold.

   Intermittent timeoutsDatabase overloadLock contention or deadlock

   Trigger

   Traffic increases during sales and lucky-bag launches pushed batch executions per second on the database above a threshold.

   Root cause

   unknown

   Component

   Database

   Contributing factor

   –

   Remediation

   • Fixed the plan guides of the four stored procedures whose compilation waits caused blocking
   • Disabled automatic statistics updates on the affected tables during periods of expected high traffic
   • Enabled automatic statistics updates again once traffic calmed down
   • Compressed the large indexes held in the data cache to use memory more efficiently

   Lessons

   When the root cause of a failure cannot be found, choosing among the things observed during the failure those that can be removed or reduced, and acting on them, can still create a state in which the failure no longer happens.

   View case
4. IL-0486 Figma's PostgreSQL 9 database on RDS slowed under high write IOPS and lock contention until the site was unavailable

   Figma2020

   Symptoms

   On January 22, 2020 at 9:45 AM PST, Figma's database metrics showed rising write IOPS and lock contention, while database CPU was lower than normal. An automated alert followed at 10:00 AM, and by 11:42 AM enough API requests had queued up behind the database that Figma became unavailable for some users.

   Lock contention or deadlockDatabase overloadService outage

   Trigger

   An automatic ANALYZE on one of the tables in the query finished at exactly the moment the incident began. The new statistics pushed the planner's row estimate for an inner subquery past 200 rows, and the plan for the whole query switched to an estimate of over 20 million rows.

   Root cause

   Software defect

   Component

   DatabasePostgreSQL vacuum

   Contributing factor

   Overlapping failuresHarmful manual action

   Detection

   Database metrics showed higher write IOPS and lock contention at 9:45 AM PST, and an automated alert at 10:00 AM started the investigation.

   Remediation

   • Cancelled several inessential queries and increased the allocated IOPS
   • Cancelled autovacuum statements running on the largest tables
   • Changed autovacuum_freeze_max_age and restarted the database to stop aggressive autovacuuming
   • Took the service down for emergency maintenance and upgraded the database to PostgreSQL 11

   Action items

   • Revisit transaction timeouts and limits so long-running and expensive queries are terminated
   • Improve metrics, monitoring and database settings for autovacuuming, locking and query performance
   • Audit queries for unnecessary or excessive joins and subqueries that could trip up the query planner
   • Bring the performance tools of the upgraded PostgreSQL into the incident response plans
   • Audit the structure and access patterns of data stored in PostgreSQL and consider moving some of it to datastores suited to those patterns
   • Improve in-app messaging about unexpected maintenance windows
   • Move towards online database upgrades that do not need downtime
   • Let users keep working on already open files safely while offline, even after closing the browser

   Lessons

   Having upgraded staging to PostgreSQL 11, tested it for months and written a production upgrade plan let the team upgrade under poor conditions with confidence. A routine statistics refresh can flip a query to a catastrophic plan, and the same query's plan can differ widely over time, so comparing plans from snapshots taken at different dates exposed the cause.

   View case
5. IL-0457 The HA HDFS NameNode of a log-collection Hadoop cluster failed over around 09:00 after its ZKFC health check timed out

   LINE2019

   Symptoms

   The NameNode of an HA HDFS cluster run by LINE Data Labs occasionally failed over at a particular time of day, around 09:00. At the failover the ZooKeeper Failover Controller (ZKFC) logged that its NameNode health check had timed out after 60 seconds: WARN ha.HealthMonitor (HealthMonitor.java:doHealthChecks(211)) - Transport-level exception trying to monitor health of NameNode at xxxx/xxx.xxx.xxx.xxx:xxxx: java.net.SocketTimeoutException: 60000 millis timeout while waiting for channel to be ready for read.

   Unnecessary failoverLock contention or deadlockIntermittent timeouts

   Trigger

   Around 09:00 the NameNode began deleting the files under the .Trash checkpoint (TrashPolicyDefault#deleteCheckpoint), and deleting one directory held the FSNamesystem write lock for about 36 seconds.

   Root cause

   Capacity exhaustion

   Component

   Shared network filesystemDatabase failover manager

   Contributing factor

   Unreviewed defaults

   Remediation

   • Changed fs.trash.interval from 4320 to 1440 so files under .Trash are deleted daily instead of every 3 days
   • Configured a dedicated Service RPC port for the ZKFC and DataNode health checks

   View case

By root cause8