Upgrade to Pro — share decks privately, control downloads, hide ads and more …

gh-ost: GitHub's online schema migrations for M...

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
Tweet

More Decks by Shlomi Noach

Other Decks in Technology

Transcript

  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, github.com/github/gh-ost 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 https://github.com/github/gh-ost/blob/master/doc/ requirements-and-limitations.md Known limitations
  35. How people build software ! Thank you! Questions? 35 !

    github.com/shlomi-noach @ShlomiNoach