What the talk is about

Did you ever have to run an alter table on a production MySQL table containing 100M+ rows? If yes, then you have experienced the problem. MySQL Online schema migration for tables with millions of rows is hard.

Alter table in MySQL is mostly a blocking operation, even with Online DDL. It may not be blocking on the primary, but it’s blocking on the replicas since there’s a single SQL thread processing the binary logs on the replica. It is uninterruptible and may cause huge replication lag for large tables.

The popular solutions for this problem are Percona’s pt-online-schema-change, facebook-osc and GitHub’s gh-ost.

In this talk, I will demonstrate how we used GitHub’s gh-ost to perform no downtime, sub-second replica lag, schema migration on large MySQL tables (100M+ rows) in production. I will discuss why we chose gh-ost, how it works, and discuss its operational simplicity and dynamic reconfiguration aspects.

We have been using gh-ost in production for 4+ months and we have performed multiple schema migrations on production tables with 100M+ rows and 100GB+ single table size.

Talk Outline

• Problems with naive alter table
• Introduction to GitHub gh-ost
• Our use case of migrating a table with 100M+ rows (100GB table)
• How we built the confidence in using gh-ost in production - Iterations and learnings
• The D-Day - how we used throttling, dynamic reconfiguration, control over cut-over during the migration
• gh-ost Limitations that we need to be aware of
• Our current CI/CD strategy (as where gh-ost fits in)
• What next: our future roadmap for automating schema migrations for Continuous Delivery.

Demo

This talk will contain live/recorded demos. We will run a sample migration on a table with millions of rows using gh-ost during the talk.

• Demo 1 (Naive alter table, blocking on replica and causing replication lag)
• Demo 2 (Alter table using gh-ost with a focus on operational perks)

Why you should attend this talk

You will learn and appreciate why it is useful to bulid tools with operational simplicity and predictability. Although the talk is specific to MySQL and gh-ost, I will cover design principles that we should keep in mind when building our own tools.

About me

Chinmay(https://www.linkedin.com/in/chinmay185/) is a founder at One2N(https://one2n.in) and has keen interest in solving scaling problems related to backend and reliability engineering. He believes that an answer to any software problem usually begins with “It depends...” So, he reasons about technologies and solutions considering the trade-offs they offer.