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

Transcript

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

   View Slide

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
   

   View Slide

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
   

   View Slide

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
   

   View Slide

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
   

   View Slide

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
   

   View Slide

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
   

   View Slide

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
   

   View Slide

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

   View Slide

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?
    

    View Slide

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
    

    View Slide

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

    View Slide

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

    View Slide

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
    

    View Slide

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
    

    View Slide

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

    View Slide

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
    

    View Slide

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
    

    View Slide

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

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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.
    

    View Slide

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
    

    View Slide

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

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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

    View Slide

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
    

    View Slide

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

    View Slide

39. 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
    

    View Slide

40. 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
    

    View Slide

41. 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
    

    View Slide