Bird
Raised Fist0

GitHub Database Failover Incident - What Went Wrong and Prevention

HLDhardInterview freq: medium
Choose your preparation mode4 modes available
ArchitectureTraceChallenge
🏗️
GitHub Database Failover Incident - What Went Wrong and Prevention
Analyzing GitHub's 2020 database failover incident to understand failure causes and design robust prevention
hardSystem Failures and Post-MortemsFreq: medium
Prerequisites:Distributed Systems BasicsObservabilityDatabase Replication and FailoverIncident Post-Mortem Analysis
⚙️
Functional Requirements
  • Support automatic failover from primary to secondary database replicas upon failure detection
  • Ensure zero or minimal data loss during failover with strong consistency guarantees
  • Provide real-time monitoring and alerting for replication lag and failover events
  • Allow manual override and rollback in case of failover errors
  • Maintain read availability during failover with minimal latency impact
  • Support multi-region disaster recovery with geo-replicated databases
📊
Non-Functional Requirements
  • System must handle 2 billion daily active users generating 40 database writes per user per day
  • Failover detection and switch must complete within 30 seconds to meet 99.99% availability SLA
  • Replication lag must be kept under 100ms P99 to avoid data loss during failover
  • Database write throughput must sustain 925K writes per second at peak
  • Failover system must maintain sub-100ms read latency P99 during normal and failover states
❓
Clarifying Questions to Ask
  • What database technology is used (e.g., MySQL, PostgreSQL, or custom)?
  • Is the replication synchronous or asynchronous between primary and replicas?
  • What monitoring and alerting tools are currently in place?
  • Are failovers fully automated or require manual intervention?
  • What is the RPO (Recovery Point Objective) and RTO (Recovery Time Objective)?
  • How is data consistency verified post-failover?
Out of Scope
✗ Design of the entire GitHub application stack beyond database layer
✗ User authentication and authorization mechanisms
✗ Detailed schema design of GitHub's business data models
✗ Network infrastructure and physical data center failover
📌
Assumptions
  • 2 billion daily active users (DAU)
  • Each user generates 40 database writes per day
  • Average write size is 100 bytes
  • Replication factor of 3 (primary + 2 replicas)
⚡
QPS Calculation
write qps2B × 40 / 86400 ≈ 925,926 writes/sec
peak write qpsAssuming 2× peak factor: 925,926 × 2 ≈ 1,851,852 writes/sec
read qpsAssuming 10 reads per write: 925,926 × 10 ≈ 9,259,260 reads/sec
💾
Storage Estimate
daily storage2B × 40 × 100 bytes = 8 TB/day
retention period30 days
total storage8 TB × 30 = 240 TB
with replication240 TB × 3 = 720 TB
🖥️
Server Estimate
write db nodesAssuming each MySQL primary node handles 50K writes/sec, need 1,851,852 / 50,000 ≈ 38 nodes
replica nodes2 replicas per primary → 38 × 2 = 76 replica nodes
🏗️
Architecture Diagram
Architecture Diagram
          +----------------------+
          |   Application Layer   |
          +----------+-----------+
                     |
          +----------v-----------+
          | Load Balancer (HAProxy)|
          +----------+-----------+
                     |
          +----------v-----------+
          |  Primary DB Cluster  |
          | (MySQL Group Replication)|
          +----------+-----------+
                     | Replication (GTID)
          +----------v-----------+
          |  Replica DB Cluster  |
          | (MySQL Group Replication)|
          +----------+-----------+
                     |
          +----------v-----------+
          |  Failover Orchestrator|
          | (Consul + Custom Logic)|
          +----------+-----------+
                     |
          +----------v-----------+
          | Monitoring & Alerting|
          | (Prometheus + Grafana)|
          +----------------------+
🧩
Components
MySQL Group Replication Primary Cluster
Handles all writes and coordinates synchronous replication to replicas
Why thisMySQL Group Replication provides built-in consensus-based synchronous replication ensuring strong consistency and automatic membership changes
Why notPostgreSQL streaming replication is asynchronous by default and requires complex manual failover scripts, increasing failover time and risk
MySQL Group Replication Replica Cluster
Maintains up-to-date copies of primary data for read scaling and failover targets
Why thisGroup Replication replicas maintain near real-time consistency with primary using GTID-based replication
Why notAsynchronous replicas like standard MySQL replication risk data loss during failover due to replication lag
Load Balancer (HAProxy)
Routes application traffic to primary or replica DBs based on health and role
Why thisHAProxy supports health checks and dynamic backend switching with sub-second failover detection
Why notDNS-based routing is slow to propagate and cannot guarantee sub-30 second failover
Failover Orchestrator (Consul + Custom Logic)
Detects primary failures, triggers failover, promotes replica, and updates routing
Why thisConsul provides distributed consensus and health checking; custom logic handles complex failover steps and consistency checks
Why notPacemaker lacks deep integration with MySQL Group Replication and is less flexible for custom consistency validation
Monitoring & Alerting (Prometheus + Grafana)
Continuously monitors replication lag, node health, and failover events; alerts on anomalies
Why thisPrometheus supports high-cardinality metrics and alerting rules; Grafana provides rich visualization for rapid diagnosis
Why notNagios lacks native support for time-series metrics and fine-grained alerting needed at this scale
🔄
Data Flow
1
Application Layer → Load Balancer
Send write/read requests
Load balancer routes writes to primary, reads to replicas
2
Load Balancer → Primary DB Cluster
Forward write requests
Primary executes writes and replicates changes synchronously
3
Primary DB Cluster → Replica DB Cluster
Synchronous replication via GTID
Replicas apply changes with minimal lag
4
Failover Orchestrator → Monitoring System
Poll replication lag and node health
Detects anomalies or primary failure
5
Failover Orchestrator → Replica DB Cluster
Promote replica to primary
Ensures replica is fully caught up before promotion
6
Failover Orchestrator → Load Balancer
Update routing to new primary
Switches traffic to promoted replica

Practice

(1/5)
1. In the message delivery trace, at Step 4 the Delivery Worker checks the consumer offset stored in ZooKeeper before sending messages. What happens if the offset is missing at this step?
easy
A. The Delivery Worker resets the offset to the earliest available message in the partition
B. The Delivery Worker blocks indefinitely waiting for the offset to appear
C. The Delivery Worker skips the message and moves to the next offset
D. The Delivery Worker deletes the partition and requests a new one

Solution

  1. Step 1: Understand consumer offset role

    The offset tracks the last consumed message; if missing, the consumer has no progress marker.

  2. Step 2: Delivery Worker behavior on missing offset

    Kafka semantics dictate resetting to earliest offset to avoid message loss; skipping (A) would lose messages; blocking (C) causes deadlock; deleting partition (D) is invalid.

  3. Final Answer:

    Option A -> Option A

  4. Quick Check:

    Missing offset -> reset to earliest offset to ensure delivery [check]
Hint: Missing offset resets to earliest message, never skips or blocks [check]
Common Mistakes:
2. A system must handle millions of short video uploads per day, ensuring fast ingestion and metadata extraction without blocking the user. Which component is best suited to decouple upload ingestion from processing?
easy
A. Use a distributed message queue like Kafka to buffer uploads for asynchronous processing
B. Use a relational database to store uploads synchronously during user upload
C. Use a CDN edge cache to store uploads temporarily before processing
D. Use a REST API gateway that directly triggers video transcoding synchronously

Solution

  1. Step 1: Identify ingestion decoupling need

    Relational DB (A) blocks user upload due to synchronous writes; CDN (C) caches for delivery, not ingestion; REST API (D) triggers blocking transcoding.

  2. Step 2: Choose buffering component

    Kafka (B) buffers uploads asynchronously, enabling fast user response and reliable processing.

  3. Final Answer:

    Option A -> Option A

  4. Quick Check:

    Kafka buffers uploads asynchronously for scalable ingestion [check]
Hint: Buffer uploads asynchronously with Kafka queue [OK]
Common Mistakes:
3. If the TTL eviction mechanism is disabled in a cache system, what failure occurs when stale data remains in the cache?
medium
A. Stale entries remain indefinitely, causing clients to read outdated data
B. Cache automatically switches to LRU eviction, which is slower
C. Cache entries are evicted based on frequency instead, causing unpredictable behavior
D. Cache evicts entries too aggressively, causing high miss rates

Solution

  1. Step 1: Understand TTL role

    TTL ensures entries expire after a fixed time to prevent stale data.

  2. Step 2: Trace failure without TTL

    Without TTL, stale entries never expire, so clients may read outdated data indefinitely.

  3. Final Answer:

    Option A -> Option A

  4. Quick Check:

    Disabling TTL causes stale data to persist indefinitely [check]
Hint: No TTL means stale data never expires, causing outdated reads [OK]
Common Mistakes:
4. Without the automated rollback system, what exactly breaks when a misconfiguration causes a route leak?
medium
A. The system silently drops all BGP updates, causing network partition
B. Route leak detection alerts flood the monitoring system but traffic continues normally
C. The system automatically quarantines all routes, causing a complete service outage
D. Alice's traffic is routed incorrectly; operators must manually detect and fix the leak, causing prolonged outages

Solution

  1. Step 1: Trace failure without rollback

    Misconfigurations propagate incorrect routes; without rollback, manual detection delays fix.

  2. Step 2: Eliminate other failure modes

    Silent drops (A) don't occur; automatic quarantine (C) would cause total outage, not typical; alert floods (D) don't prevent traffic misrouting.

  3. Final Answer:

    Option D -> Option D

  4. Quick Check:

    Manual fix delays cause prolonged incorrect routing [check]
Hint: No rollback means manual fix delays outages [check]
Common Mistakes:
5. At 10x scale, which component in a distributed lock system using ZooKeeper is most likely to fail first and why?
hard
A. ZooKeeper ensemble leader, due to increased write load and session management overhead
B. Clients, because they cannot handle the increased lock acquisition requests
C. Network routers, because of increased traffic causing packet loss
D. Storage backend, because ephemeral nodes consume excessive disk space

Solution

  1. Step 1: Identify ZooKeeper leader role

    The leader handles all write requests and session heartbeats, becoming a bottleneck under high scale.

  2. Step 2: Evaluate other components

    Clients (A) can be scaled horizontally; network routers (C) are less likely the first bottleneck; ephemeral nodes (D) are lightweight and cleaned up automatically.

  3. Final Answer:

    Option A -> Option A

  4. Quick Check:

    Leader bottleneck due to write and session overhead [check]
Hint: ZooKeeper leader handles all writes -> first bottleneck [check]
Common Mistakes:
Recommended

Ready to prove it?

Pass the test and this pattern moves unknown strong while earning +1 Grip toward your certificate.

10 questions~5 mineasy hard