Cases21 cases

14 to 18 of 21

1. IL-0524 An index migration locked Open Collective's whole Transactions table and degraded the service for nearly an hour

   Open Collective2023

   Symptoms

   At 09:20 UTC on 4 April 2023, seven minutes after a deployment was triggered, Open Collective's service degraded: a migration in the deployment had locked the entire Transactions table in the production database.

   Lock contention or deadlockDatabase overload

   Trigger

   A deployment ran a migration that dropped indexes on the Transactions table and re-created them with CONCURRENTLY, excluding soft-deleted rows.

   Root cause

   Faulty deployment

   Component

   DatabaseSchema migration

   Contributing factor

   Missed in reviewUnrepresentative staging

   Detection

   The degradation of service was noticed at 09:20 UTC, seven minutes after the deployment was triggered.

   Remediation

   • Killed the migration, though the table remained locked
   • Put the frontend into maintenance mode to limit queries
   • Closed the other connections to the database
   • Cancelled the new index creation and restored the old index
   • Restored the frontend and confirmed the issue was resolved

   Action items

   • Add a script that simulates production load locally, using a tool like vegeta
   • Run this kind of migration during specific maintenance windows
   • Create the new index first and drop the old one afterwards in future migrations
   • Insist on a review from another engineer before merging such a pull request

   Lessons

   Dropping the old index before creating the new one was too optimistic under constant production queries, even with CONCURRENTLY, and an unreviewed pull request let it through. Tests locally and on staging did not show the problem because there were no tools to run migrations in stressed environments. Putting the frontend in maintenance mode limited the impact while showing users a clear message.

   View case
2. IL-0314 Daily's newly upgraded Aurora MySQL database deadlocked on meeting-event queries and its call servers kept restarting

   Daily2023

   Symptoms

   At 9:47 AM Eastern (14:47 UTC) on 7 February 2023, Daily's database alerts reported a performance problem under normal operational load: a high level of lock contention on a database that had been upgraded the weekend before and had run normally since Monday. Users could get websocket connection errors when joining calls, and REST API requests could time out or return 5xx errors.

   Lock contention or deadlockHigh error rateIntermittent timeouts

   Trigger

   Database maintenance the weekend before had upgraded the database to a MySQL point release with a low-level behaviour change, and on 7 February, under normal load, that change produced the deadlocks Daily saw on Aurora MySQL 2.11.0.

   Root cause

   Breaking change in an upgrade

   Component

   Database

   Contributing factor

   Unrepresentative stagingAutomation amplificationRetry stormChange with a delayed effect

   Detection

   Database alerts at 9:47 AM Eastern reported a performance issue with high lock contention on the newly upgraded database.

   Remediation

   • Disabled MySQL binlog replication to the old database and restarted the database to reduce load
   • The database restarted by itself at 10:47 AM, the SFUs dropped the stale meeting updates without restarting, and the platform recovered
   • Rate-limited the POST /rooms endpoint to the same limit as DELETE /rooms/:name
   • Updated an index on the affected table in production on the Saturday evening to work around the MySQL behaviour change

   Action items

   • Work with AWS so the Aurora MySQL 2.11.0 deadlock issue is documented, understood and fixed in a future release
   • Add more API request patterns to the testing workload used for infrastructure changes
   • Add production monitoring alarms on more fine-grained database metrics

   Lessons

   Daily tests infrastructure changes under production-like workloads, but its synthetic workload did not have the shape that triggers these deadlocks. A deadlock behaviour change in MySQL 5.7 that was already known turned out far more severe in production than either Daily or the AWS Aurora team expected.

   View case
3. 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
4. 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
5. 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

By root cause8