Cases11 cases

10 to 11 of 11

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-0566 Hot ranges after a Cookie Run: Kingdom guild update drove CockroachDB nodes to 100% CPU, forcing emergency maintenance

   Devsisters2021

   Symptoms

   About half an hour after the Cookie Run: Kingdom guild update maintenance ended at 11:00 on 6 March 2021, one CockroachDB node was using all 24 cores of its 24-core machine and server metrics began to show a significant number of errors. Soon two more nodes showed abnormal CPU use.

   Load imbalanceDatabase overloadHigh error rate

   Trigger

   During the guild update maintenance, new guild tables were added and pre-split across the cluster, and a new database node was added to replace one removed a few days earlier for a physical hardware issue. When the game reopened, all players at once wrote to existing tables whose primary-key prefixes were new or had changed in the update.

   Root cause

   Faulty deployment

   Component

   Database

   Contributing factor

   Unverified assumptionOverlapping failures

   Detection

   Monitoring after the maintenance showed one node's CPU at the ceiling together with errors in server metrics. Later a Slack alert reported a node failing its Host Status Check, and the abnormal-log channel reported filemap_fdatawait_range_keep_errors.

   Remediation

   • Blocked new logins without kicking players already in the game, using the maintenance switch as a makeshift access queue
   • Put the game into full emergency maintenance at 14:50 when several nodes failed health checks
   • Backed up the game data table by table after the full database backup stalled
   • Paused writing play records to reduce load on the database before reopening
   • Split the problem tables from about 18:30
   • Decommissioned the node that failed its Host Status Check
   • Reopened the game and used the login-block queue again when a node reached 100% CPU
   • Terminated the node that kept returning to 100% CPU

   Lessons

   In a single-server relational database a change to the primary-key prefix is usually harmless, but in CockroachDB's distributed design such a change on a production cluster needs great care. The team had studied this in CockroachDB documentation, but living through it taught far more than reading about it.

   View case

By root cause1