ALTER TABLE taking down GitHub

Can an ALTER TABLE command take down your production? ??

It happened to GitHub on 27th November 2021 when most of their services were down because of a large schema migration.

Why did the outage happen?

GitHub ran a migration on a massive MySQL table and it made their replicas enter deadlock and crash. Here are the 5 insights about their architecture

Insight 1: Schema migration can take weeks to complete

Schema migrations are intense operations as in most cases require you to copy the entire table with the new schema. Hence it might take a schema migration weeks to complete.

Insight 2: The last step of migration is RENAME

To protect the database from not taking excessive locks during the migration, we create an empty ghost table from the main table, apply migration, copy the data, and then rename the table. This reduces the locks we need for the migration.

Insight 3: Read Replicas can have deadlocks

The writes happening through the replication job and the production read load can create deadlocks on the read replicas.

Insight 4: Have a separate fleet of replicas for internal traffic

Have a separate fleet of Read Replicas for internal workflows ex: analytics, etc. This way, any internal load will not affect the production load.

Insight 5: Database failures cascade

When a replica fails, the load on healthy one's increases; which may them down and hence the cascading effect.

Mitigation

The outage happened because there were not enough read replicas to handle the load, hence in order to mitigate it the way out was to add more read replicas. GitHub team very smartly promoted the replicas used for internal workloads to handle production.

Although the move was smart, it did not mitigate the outage because the incoming load was so high that the new replicas added also started crashing.

Data Integrity over Availability

To ensure that the data integrity is not compromised because of repeated crashes, GitHub took a call and let the Read traffic fail. They took the replica out of the production fleet, gave it time to complete the migration, and then added it back.

This way all the replicas got the time they needed to complete the schema migration. It took some but the issue was completely mitigated.

Long-term fix

Vertical Partitioning is a long-term fix for this problem. The idea is to create smaller databases that hold related tables; ex: all tables related to Repositories can go in one DB. This allows migration to quickly complete and during an outage, only the involved functionalities will be affected.

Here's the video of my explaining this in-depth ?? do check it out

Can an ALTER TABLE command take down your production? ??

GitHub had a major outage and it all started with a schema migration. The outage affected their core services like GitHub actions, API requests, pull requests, and many more. Today, we dissect this outage and do an intense deep dive to extract 5 amazing insights. We also see how they very smartly mitigated the outage along with a potential long-term fix.

Outline:

• 00:00 Agenda
• 02:57 Introduction
• 03:23 Insight 1: Schema Migrations can take weeks to complete
• 05:48 Insight 2: How schema are altered when the table is huge
• 08:52 Insight 3: Deadlocks on Read Replicas
• 11:20 Insight 4: Separate Replica fleet for internal read traffic
• 13:59 Insight 5: Database failures cascade
• 18:13 Mitigation Strategy
• 29:28 Lont-term Fix

Outage report: https://github.blog/2021-12-01-github-availability-report-november-2021/

You can also

• Subscribe to the YT Channel Asli Engineering
• Download the notes
• Listen to this on the go on Spotify

Thank you so much for reading ?? If you found this helpful, do spread the word about it on social media; it would mean the world to me.

You can also follow me on your favourite social media LinkedIn, and Twitter.

Yours truly,

Arpit

arpitbhayani.me

Until next time, stay awesome :)

I teach a course on System Design where you'll learn how to intuitively design scalable systems. The course will help you

• become a better engineer
• ace your technical discussions
• get you acquainted with a massive spectrum of topics ranging from Storage Engines, High-throughput systems, to super-clever algorithms behind them.

I have compressed my ~10 years of work experience into this course, and aim to accelerate your engineering growth 100x. To date, the course is trusted by 600+ engineers from 10 different countries and here you can find what they say about the course.

Together, we will build some of the most amazing systems and dissect them to understand the intricate details. You can find the week-by-week curriculum and topics, benefits, testimonials, and other information here https://arpitbhayani.me/masterclass.