Save 37% off PRO during our Black Friday Sale! »

gh-ost: GitHub's online schema migrations for MySQL

168ccec72eee0530b818d44f3fedaacf?s=47 Shlomi Noach
September 14, 2016

gh-ost: GitHub's online schema migrations for MySQL

Introducing gh-ost, a triggerless MySQL online schema migration solution by GitHub.

gh-ost changes the online migration paradigm and overcomes today's limitations and difficulties in MySQL table migrations. gh-ost was designed with performance and operations in mind. It is triggerless, controllable, auditable, suspendable, and testable.


Shlomi Noach

September 14, 2016


  1. How people build software ! GitHub's online schema migrations for

    MySQL Shlomi Noach GitHub 1 ! Illustrated with ghosts
  2. How people build software ! 2 ! • gh-ost is

    GitHub’s MySQL schema migration tool • GitHub Online Schema Transmogrifier/Transfigurator/Transfer/Thingy • Developed by the database-infrastructure team as GitHub • Used in production daily • Open source, But, what is this all about? gh-ost
  3. How people build software ! 3 ! • 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
  4. How people build software ! 4 ! • 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
  5. How people build software ! 5 ! • 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
  6. How people build software ! ! Synchronous triggers based migration

    6 ! " " original table ghost table # insert delete update insert delete update pt-online-schema-change oak-online-alter-table LHM
  7. How people build software ! ! 7 ! " "

    original table ghost table # insert delete update inserts " changelog table Asynchronous triggers based migration fb-osc
  8. How people build software ! 8 ! • 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?
  9. How people build software ! 9 ! • 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
  10. How people build software ! ! 10 ! " "

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

    # $ ! " " master replica Binlog based migration, utilize replica
  12. How people build software ! 12 ! • 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
  13. How people build software ! 13 ! ! ! $

    " " 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
  14. How people build software ! 14 ! ! $ !

    $ ! $ ! $ ! $ ! $ $ ! ! $ ! $ a. connect to replica b. connect to master c. migrate/test on replica gh-ost operation modes
  15. 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? 15 !
  16. How people build software ! 16 ! • 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
  17. How people build software ! 17 ! ! ! !

    $ Testing in production ! $ ! $ ! ! ! production replicas testing replicas master
  18. How people build software ! 18 ! • 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
  19. How people build software ! 19 ! • 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
  20. How people build software ! 20 ! • 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
  21. How people build software ! 21 ! • 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
  22. How people build software ! throttling in production 22 !

    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
  23. How people build software ! 23 ! • 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
  24. How people build software ! 24 ! • 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
  25. How people build software ! gh-ost chatops @ GitHub 25

    ! • 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.
  26. 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 26 !
  27. How people build software ! ! 27 ! " original

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

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

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

    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
  31. How people build software ! ! ! 31 ! original

    table # insert delete update $ master " ghost table " some unrelated server
 in a far galaxy remote, live table migration
  32. How people build software ! Open source How gh-ost is

    developed and what we envision 32 !
  33. How people build software ! 33 ! • 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
  34. How people build software ! 34 ! • No support

    for foreign keys (partially possible to add) • No support for triggers (possible to add) • RBR required on at least one server • No support for 5.7 generated columns (possible to add) • Multisource replication not supported • Active-active master-master replication not supported (possible to add) Complete listing in Known limitations
  35. How people build software ! Thank you! Questions? 35 ! @ShlomiNoach