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

GitHub's online schema migrations for MySQL

Avatar for Shlomi Noach Shlomi Noach
October 05, 2016

GitHub's online schema migrations for MySQL

gh-ost is a new tool by GitHub which changes the paradigm of MySQL online schema changes, designed to overcome today's limitations and difficulties in online migrations. gh-ost is:

- Triggerless: no triggers placed;
- Pausable: can suspend master writes altogether
- Lightweight: makes a low impact on the master database
- Controllable: one can interact with an executing gh-ost process, get info and reconfigure parameters
- Testable: gh-ost allows for testable, safe, non obtrusive migrations in production
- Designed to allow for multiple concurrent migrations

In this session we will:

- Introduce gh-ost, explain the reasoning for developing a new tool
- Describe the underlying logic
- Compare with existing online schema change tools
- Show off extra perks that make gh-ost operations so friendly
- Discuss the roadmap and present with surprising implications

gh-ost is open sourced under the MIT license

Avatar for Shlomi Noach

Shlomi Noach

October 05, 2016
Tweet

More Decks by Shlomi Noach

Other Decks in Technology

Transcript

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

    MySQL Tom Krouper, Shlomi Noach GitHub 1 ! Illustrated with ghosts
  2. 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
  3. 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
  4. 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
  5. 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
  6. 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
  7. 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
  8. 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
  9. How people build software ! ! 9 ! " "

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

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

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

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

    $ Testing in production ! $ ! $ ! ! ! production replicas testing replicas master
  20. 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
  21. 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
  22. 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
  23. 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
  24. 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
  25. 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
  26. 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
  27. 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
  28. 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.
  29. 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
  30. How people build software ! ! 30 ! " original

    table ghost table # insert delete update binary log row copy " $ row-copy still couples tables
  31. 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
  32. 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
  33. 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
  34. How people build software ! ! ! 34 ! original

    table # insert delete update $ master " ghost table " some unrelated server
 in a far galaxy remote, live table migration
  35. 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
  36. How people build software ! Open source How gh-ost is

    developed and what we envision 36
  37. 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
  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
  39. 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
  40. 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