How people build software ! GitHub's online schema migrations for MySQL Tom Krouper, Shlomi Noach GitHub 1 ! Illustrated with ghosts

How people build software ! 2 ! • The world’s largest Octocat T-shirt and stickers store • And water bottles • And hoodies • We also do stuff related to things GitHub

How people build software ! 3 ! • gh-ost is GitHub’s MySQL schema migration tool • GitHub Online Schema Transmogrifier/Transfigurator/Transfer/Thingy • Developed by the @github/database-infrastructure • Used in production daily • Open source, github.com/github/gh-ost But, what is this all about? gh-ost

How people build software ! 4 ! • No support for foreign keys (partially possible to add) • No support for triggers (possible to add) • RBR required on at least one server. FULL binlog image required (for now) • No support for 5.7 generated columns (possible to add) • Multisource replication not supported • Active-active master-master replication not supported (possible to add) • Bugs: gh-ost owns far more logic and data transfer, therefore needs to get its hands dirty with timezones, characters sets, etc.. See Issues. Complete listing in github.com/github/gh-ost/blob/master/doc/ requirements-and-limitations.md, github.com/github/gh-ost/issues Known limitations: let’s get this out of our way

How people build software ! 5 ! • GitHub stores repositories in git, and uses MySQL as the backend database for all related metadata: • Repository metadata, users, issues, pull requests, comments etc. • Our MySQL servers must be available, responsive and in good state: • Write throughput expected to be high • Write latency expected to be low • Replica lag expected to be low MySQL

How people build software ! 6 ! • MySQL schema migration is a known problem • Addressed by schema migration tools since 2009. Most common are: • pt-online-schema-change by Percona • fb-osc by Facebook • GitHub develops rapidly. Engineers require changes to MySQL tables daily, and these changes should take place quickly • Migrations must not block development • Migrations must not impact availability Migrations

How people build software ! 7 ! • We’ve been using pt-online-schema-change for years • As we grew in volume and traffic, we hit more and more problems • Some migrations cause such high load that writes were stalled and GitHub performance degraded • Others would cause consistent replication lags • Some tables could only be migrated off-peak • Some tables could only be migrated during weekend • We would attend to running migrations • Some tables could not be migrated • In 2016, we suffered outages due to migrations on our busiest tables • We had a list of “risky” migrations GitHub migration pains

How people build software ! ! Synchronous triggers based migration 8 ! " " original table ghost table # insert delete update insert delete update pt-online-schema-change oak-online-alter-table LHM

How people build software ! ! 9 ! " " original table ghost table # insert delete update inserts " changelog table Asynchronous triggers based migration fb-osc

How people build software ! 10 ! • Stored routines • Interpreted, not compiled. Latency to each transaction • Locks • Transaction space competes for multiple, uncoordinated locks • Metadata locks • Unsuspendible • Even as throttling is required, triggers must continue to work • Concurrent migrations • Trust issues • No reliable testing • Either cannot test in production, or test does not get actual write workload What’s wrong with triggers?

How people build software ! 11 ! • gh-ost connects as replica and pulls binary log entries (RBR format) • Interprets related DML (INSERT, UPDATE, DELETE) entries and transforms them to meet refactored table structure • Applies on ghost table • gh-ost connects to master and iterates rows • One chunk after the other, copies rows from the original table to the ghost table • Much like existing tools, but more on this later • maintains a “changelog” table for internal lightweight bookkeeping Binlog based design

How people build software ! ! 12 ! " " original table ghost table # insert delete update no triggers $ binary log Triggerless, binlog based migration

How people build software ! ! 13 ! " " # $ ! " " master replica Binlog based migration, utilize replica

How people build software ! 14 ! • Binary logs can be read from anywhere • gh-ost prefers connecting to a replica, offloading work from master • gh-ost controls the entire data flow • It can truly throttle, suspending all writes on the migrated server • gh-ost writes are decoupled from the master workload • Write concurrency on master turns irrelevant • gh-ost’s design is to issue all writes sequentially • Completely avoiding locking contention • Migrated server only sees a single connection issuing writes • Migration algorithm simplified Binlog based design implications

How people build software ! 15 ! ! ! $ " " master replica binary log original table ghost table gh-ost migration: - creates ghost table on migrated server - alters ghost table - hooks up as a MySQL replica, streams binary log events - interchangeably: - applies events on ghost table - copies rows from original table onto ghost table - cut-over Preferred setup: - connects to replica - inspects table structure, table dimensions on replica - hooks as replica onto replica - apply all changes on master - writes internal & heartbeat events onto master, 
 expects them on replica " " gh-ost design

How people build software ! 16 ! ! $ ! $ ! $ ! $ ! $ ! $ $ ! ! $ ! $ a. connect to replica b. connect to master c. migrate/test on replica gh-ost operation modes

How people build software ! Trust What makes gh-ost, a newcomer tool, trusted with our data? As trusted as - or more trusted than - existing solution? 17

How people build software ! 18 ! • Other than unit tests and integration tests, gh-ost supports testing in production • You will execute a gh-ost migration on a replica • gh-ost will execute as normal, but applying changes on replica • Just before cut-over it stops replication • Execution ends with both original and ghost tables in place, replication stopped • At your leisure, you can compare/checksum the two tables • We have dedicated servers that continuously test our entire production table set • Each table is migrated on replica via “trivial” (no schema change) migration • Tables data checksummed and expected to be identical Testing

How people build software ! 19 ! ! ! ! $ Testing in production ! $ ! $ ! ! ! production replicas testing replicas master

How people build software ! 20 ! • There are no triggers. gh-ost can completely throttle the operation when it chooses to. • Throttling based on multiple criteria: • Master metrics thresholds (e.g. Threads_running) • Replication lag • Arbitrary query • Flag file • Use command • Trust: you could choose, at any time and effective immediately, to throttle gh-ost’s operation and resume normal master workload. • And you may resume operation once satisfied Throttling

How people build software ! 21 ! • The final migration step: replacing the original table with the ghost table, incurs a brief table lock • This metadata-locks-involved step is a critical point for the migration • During brief lock time, number of connections may escalate • People tend to stick around during this phase. • People actually plan ahead migration start time based on the estimated completion time, so they can guarantee to be around • gh-ost offers postponed cut-over (optional, configurable) • As cut-over is ready, gh-ost just keeps synching the tables via binlog events • Requires an explicit command/hint to cut-over • Trust: I can safely go to bed Cut-over

How people build software ! 22 ! • gh-ost will invoke your hooks at points of interest • If you like, do your own cleanup, collecting, auditing, chatting. • Hooks available for: • startup, validated, row-copy about to begin, routinely status, about to cut-over, stop-replication, success, failure • gh-ost will populate environment variables for your process • https://github.com/github/gh-ost/blob/master/doc/hooks.md • Trust: integrate with your infrastructure Hooks

How people build software ! 23 ! • gh-ost supports niceness • Explicitly forcing it to periodic sleep based on nice-ratio • Trust: one can reduce gh-ost’s load at any time nice

How people build software ! 24 ! • gh-ost monitors replication lag in subsecond-resolution • For control-replicas, it requires a query that is known to return subsecond lag. • At GitHub replication lag is normally kept subsecond • We don’t like it when we see 5 second lag • We really don’t like it when we see 10 second lag • 20 second lag typically leads to investigation • We are able to migrate our busiest tables, during rush hour, and keep replication lag below 300ms • Trust: migrations will do whatever it takes to keep replicas up-to-date Subsecond replication lag

How people build software ! throttling in production 25 ! no migration migration updated
 max-lag-millis=200 migration begins
 max-lag-millis=500 Our production replication lag, before and during migration on one of our busiest tables
 CEST tz

How people build software ! 26 ! • With existing tools, you run your migration tool based on some configuration. • If configuration does not match your workload, you kill the migration and start a new one with more relaxed/aggressive config • gh-ost listens on Unix socket file and/or TCP • You can connect to a running migration and ask: • status • max-lag-millis=500 • throttle • cut-over • Trust: you can always get a reliable status or reconfigure as you see fit Dynamic visibility & control

How people build software ! 27 ! • We work from/with ChatOps • Are slowly and incrementally integrating gh-ost into our flow and ChatOps • We control migrations via chat: • .migration sup • .migration max-lag-millis 300 • .migration cut-over • Migrations ping us in chat to let us know their status; or if they’re ready to cut-over • Migrations are accessible to everyone, not just DBAs gh-ost @ GitHub

How people build software ! gh-ost chatops @ GitHub 28 ! • We control gh-ost via chatops • And gh-ost chats to us • The chat is a changelog visible to all. It tells us what happened when, and who did what.

How people build software ! The future We want to make gh-ost robust We want it to be widely adopted We have a few ideas on where it can go 29

How people build software ! ! 30 ! " original table ghost table # insert delete update binary log row copy " $ row-copy still couples tables

How people build software ! ! 31 ! " " original table ghost table # insert delete update $ binary log read rows no data flow 
 between tables write events,
 write rows decoupling row-copy

How people build software ! ! ! 32 ! original, ghost tables original, ghost tables # insert delete update $ binary log write events,
 write rows master replica " " " " decoupled row-copy, utilizing replica read rows

How people build software ! ! ! ! 33 ! original table original table # insert delete update $ binary log master replica " " ghost table " some unrelated server
 in a far galaxy write events,
 write rows remote, live table migration read rows

How people build software ! ! ! 34 ! original table # insert delete update $ master " ghost table " some unrelated server
 in a far galaxy remote, live table migration

How people build software ! ! 35 ! " " # $ Resurrection • gh-ost bails out on meeting critical-load • Or someone kills it • Resurrect operation! • Resume rowcopy from same place • Resume binlog apply from same position • Both are idempotent, accurate sync is not necessary

How people build software ! Open source How gh-ost is developed and what we envision 36

How people build software ! 37 ! • gh-ost is released under the MIT license • We encourage collaboration • Issues • Bugs • Questions • Feature requests • Sharing experience • Pull requests • Code • Documentation • We suggest some work for the community, that is not on our immediate roadmap Open Source

How people build software ! Usage Best read the docs. 38 !

How people build software ! 39 ! gh-ost --user="gh-ost" --password="123456" --host=replica.with.rbr.com --database="my_schema" --table="my_table" --verbose --alter="engine=innodb" --max-load=Threads_running=25 --critical-load=Threads_running=1000 --chunk-size=1000 --throttle-control-replicas="myreplica.1.com,myreplica.2.com" [continued next slide] Execution sample

How people build software ! 40 ! [continued] --heartbeat-interval-millis=100 --replication-lag-query="select unix_timestamp(now(6)) - unix_timestamp(ts) from meta.heartbeat order by ts desc limit 1" --max-lag-millis=500 --switch-to-rbr --exact-rowcount --concurrent-rowcount --panic-flag-file=/tmp/ghost.panic.flag --postpone-cut-over-flag-file=/tmp/ghost.postpone.flag --hooks-path=/path/to/hooks/ --hooks-hint="@$(whoami)" [--execute] Execution sample

How people build software ! Thank you! Questions? 41 ! github.com/tomkrouper @CaptainEyesight
 
 github.com/shlomi-noach @ShlomiNoach
 
 /cc
 github.com/ggunson @shebang_the_cat
 
 github.com/jonahberquist @hashtagjonah