$30 off During Our Annual Pro Sale. View Details »

Revertible, Recoverable Schema Migrations in Vitess

Revertible, Recoverable Schema Migrations in Vitess

Recent developments in Vitess take online schema migrations to a new level, allowing us to think of DDLs as little more than a transaction, giving both developers and DBAs new super powers and peace of mind.

- Have you ever completed a schema migration only to realize a column should not have been dropped, an index should not have changed?
- Have you ever dropped a table only to find it was a big mistake?
- Have you ever waited a week for a schema migration, only to see it go down the drain due to a failover? Or have you postponed important maintenance work due to a running migration?

Vitess online schema migrations now utilize _VReplication_, a core component in Vitess that empowers Vitess’s live resharding, materialized views, live imports, and more.

In this session we present VReplication and illustrate how it works. We follow up to explain how online schema changes employ VReplication for both revertible, and recoverable, lossless schema migrations. We will discuss some of the internal logic, and present a demo of these new super powers.

Vitess is a CNCF open source database clustering system for horizontal scaling of MySQL.

Shlomi Noach

June 24, 2021
Tweet

More Decks by Shlomi Noach

Other Decks in Technology

Transcript

  1. Revertible, recoverable
    schema migrations in Vitess
    Shlomi Noach
    PlanetScale
    PerconaLive 2021

    View Slide

  2. About me
    Engineer at PlanetScale
    Author of open source projects orchestrator, gh-ost,
    freno and others
    Maintainer for Vitess
    Blog at http://openark.org
    github.com/shlomi-noach
    @ShlomiNoach

    View Slide

  3. Founded Feb. 2018 by co-creators of Vitess
    ~50 employees
    HQ Mountain View, remote team

    View Slide

  4. Vitess
    A database clustering system for horizontal scaling of
    MySQL
    ● CNCF graduated project
    ● Open source, Apache 2.0 licence
    ● Contributors from around the community

    View Slide

  5. Agenda
    - Recap: proxy/tablet architecture
    - Recap: Online DDL in Vitess
    - Introducing VReplication
    - Revertible, lossless schema changes
    - Recoverable, resumable migrations
    - Declarative schema changes (*)
    (*) Bonus, hot off the git repo

    View Slide

  6. Vitess architecture brief basics
    Focus on VTTablet

    View Slide

  7. Vitess architecture basics
    Consider a common replication cluster

    View Slide

  8. Vitess architecture basics
    Each MySQL server is assigned a vttablet
    - A daemon/sidecar
    - Controls the mysqld process
    - Interacts with the mysqld server
    - Typically on same host as mysqld

    View Slide

  9. VTTablet
    - On primary, creates _vt schema on backend MySQL
    - Manages state of some operations on _vt tables
    - _vt schema data replicated as normal

    View Slide

  10. Vitess architecture basics
    In production you have multiple clusters

    View Slide

  11. Vitess architecture basics
    User and application traffic is routed via
    vtgate
    - A smart, stateless proxy
    - Speaks the MySQL protocol
    - Impersonates as a monolith MySQL
    server
    - Relays queries to vttablets

    View Slide

  12. Vitess architecture basics
    A vitess deployment will run multiple
    vtgate servers for scale out

    View Slide

  13. Vitess architecture basics
    vtgate must transparently route queries
    to correct clusters, to relevant shards
    app
    commerce
    shard 0
    commerce
    shard 1
    internal
    unsharded
    ?

    View Slide

  14. Vitess architecture basics
    Queries route based on schema & sharding scheme
    app
    commerce
    shard 0
    commerce
    shard 1
    internal
    unsharded
    USE commerce;
    SELECT order_id, price
    FROM orders
    WHERE customer_id=4;

    View Slide

  15. Vitess architecture basics
    topo: distributed key/value store
    - Stores the state of vitess: schemas,
    shards, sharding scheme, tablets,
    roles, etc.
    - etcd/consul/zookeeper
    - Small dataset, mostly cached by
    vtgate
    commerce
    shard 0
    commerce
    shard 1
    internal
    unsharded

    View Slide

  16. Recap: Online DDL
    Vitess supports online schema migrations natively
    Previously presented:
    - Native support for gh-ost
    - Native support for pt-online-schema-change
    mysql> SET @@ddl_strategy=’gh-ost’;
    mysql> ALTER TABLE my_table ADD COLUMN my_col INT NOT NULL;
    https://www.youtube.com/watch?v=iQYZ0dRe7O8
    https://fosdem.org/2021/schedule/event/vitess/

    View Slide

  17. Recap: Online DDL
    Automatically:
    - Send DDL to appropriate shards
    - Schedule migration
    - Create & destroy migration account
    - Run gh-ost/pt-online-schema-change
    - Throttle
    - Garbage-collect artifact tables

    View Slide

  18. VReplication
    A distributed flow in Vitess, that can:
    - Move data from “here” to “there”
    - Mutate the data on the fly
    - Live
    https://vitess.io/docs/reference/vreplication/vreplication/

    View Slide

  19. VReplication use cases
    VReplication runs the following core Vitess
    functionalities:
    - Live resharding
    - Materialized views
    - Import from external data sources (e.g. Aurora)
    - Moving tables across clusters
    - With VTGate rerouting traffic to new location

    View Slide

  20. VReplication
    - A flow (workflow) can have 1 or more streams
    - Each stream connects one source tablet (thereby a
    source MySQL server) with one target (a target
    MySQL server)
    - There can be many-to-many streams, eg.. in a
    resharding scenario
    https://vitess.io/docs/reference/vreplication/vreplication/

    View Slide

  21. VReplication: MoveTables
    Example: move large messages table out
    of a crowded monolith cluster into a
    dedicated sharded schema
    - Move data
    - Move traffic
    monolith
    unsharded
    commerce
    shard 0
    commerce
    shard 1

    View Slide

  22. VReplication: MoveTables
    Moving data:
    - Assume table exists on target
    schema/clusters
    - Iterate existing table in monolith
    cluster, a bunch of rows at a time
    - Insert/apply each bunch of rows
    onto commerce keyspace
    - Tail monolith binary logs and apply
    ongoing changes to messages table
    monolith
    unsharded
    commerce
    shard 0
    commerce
    shard 1

    View Slide

  23. VReplication: MoveTables
    Moving traffic (manual):
    - User issues SwitchReads
    - Vitess updates topo with new
    routing rules
    - VTGate notified about topo changes
    - VTGate routes all read traffic on
    messages to commerce
    schema/clusters
    monolith
    unsharded
    commerce
    shard 0
    commerce
    shard 1

    View Slide

  24. VReplication: MoveTables
    Moving traffic (manual):
    - User issues SwitchWrites
    - Vitess updates topo with new
    routing rules
    - VTGate notified about topo changes
    - VTGate routes all write traffic on
    messages to commerce keyspace
    - Client can later explicitly query
    messages on commerce schema
    rather than monolith schema
    monolith
    unsharded
    commerce
    shard 0
    commerce
    shard 1

    View Slide

  25. VReplication: MoveTables
    Reverse replication:
    - It is possible to stream messages
    changes back from commerce to
    monolith
    - Gives the app a path for failback
    monolith
    unsharded
    commerce
    shard 0
    commerce
    shard 1

    View Slide

  26. VReplication for Online DDL
    Same essentials as MoveTables, but:
    - Source and target schemas are the
    same.
    - Automatic creation of target table in
    new schema
    - Automatic analysis of schema and
    filter query
    - Automatic cut-over
    - Instead of SwitchWrites we switch
    tables
    monolith
    unsharded

    View Slide

  27. VReplication Online DDL
    Looks just like a gh-ost or pt-osc online DDL:
    mysql> SET @@ddl_strategy=’online’;
    mysql> ALTER TABLE my_table ADD COLUMN my_col INT NOT NULL;
    Added value:
    - Internal to vitess, unified logic for moving data
    around
    - Super powers, courtesy VReplication’s logic

    View Slide

  28. Revertible Online DDL
    - Run a schema migration.
    - Cut-over. Wait. What. This. Is. Wrong.
    - Revert the migration. Lossless.

    View Slide

  29. Revertible Online DDL
    DEMO

    View Slide

  30. Revertible Online DDL: how?
    - VReplication uses two underlying tables:
    - _vt.vreplication
    - General purpose information
    - Filter/rule query
    - GTID pos
    - Updates in same commit with binlog event changes
    - _vt.copy_state
    - Row-copy information
    - Last known row copy range
    - Empty on startup, empty on completion
    - Updates in same commit with row copy
    - These two tables formulate the state of a
    VReplication stream

    View Slide

  31. Revertible Online DDL: how?
    During “normal” ALTER TABLE:
    - On cut-over, disable writes on original table
    - Consume remaining binlog events
    - Mark GTID pos
    - Rename tables

    View Slide

  32. During REVERT VITESS_MIGRATION:
    - Create a new _vt.vreplication stream entry, a single
    stream workflow
    - Populate with GTID pos from completed migration
    - Formulate new filter/rule query to point back to OLD
    table
    - Keep _vt.copy_state empty for the new workflow
    - Tell VReplication to go on
    - To VReplication the new setup looks to be an
    unfinished workflow, where row copy is complete,
    and with binlog events still in queue
    Revertible Online DDL: how?

    View Slide

  33. - CREATE TABLE and DROP TABLE statements are
    revertible
    - REVERT for a DROP TABLE reinstates the table
    populated with data at time of DROP
    - REVERT for CREATE TABLE vanishes the table
    - As with ALTER TABLE, these REVERTs are
    revertible.
    Revertible Online DDL:
    CREATE & DROP

    View Slide

  34. - You run a schema migration
    - It takes days and days
    - Please. Don’t. Let. There. Be. A. Failover.
    - Sorry. This. Maintenance. Work. Will. Have. To. Wait.
    Recoverable Online DDL

    View Slide

  35. - _vt.vreplication and _vt.copy_state committed
    together with data changes.
    - Both tables are replicated as normal.
    - However lagging a replica may be, _vt.vreplication
    and _vt.copy_state on that replica are always
    consistent with the data on that replica.
    - In case of failover the replica becomes a primary.
    - VReplication on the primary notices
    _vt.vreplication and _vt.copy_state and proceeds
    from that point on.
    Recoverable Online DDL: how?

    View Slide

  36. - Objective: “don’t care” approach. Do your normal
    work and forget about whether a migration is
    running.
    - Status: “works on my machine”
    - More formal validation/testing required
    Recoverable Online DDL: how?

    View Slide

  37. Say where you want to go, not how to get there.
    Declarative Online DDL

    View Slide

  38. DEMO
    Declarative Online DDL

    View Slide

  39. - In a full declarative approach you present your entire
    schema
    - This may not play well for known Vitess use case.
    - Compromise: a hybrid approach. Per table, either:
    - CREATE TABLE, or
    - DROP TABLE
    - But never ALTER TABLE
    Declarative Online DDL: how?

    View Slide

  40. Per table, Vitess compares existing schema with desired
    schema.
    - Creates a table if needed
    - Drops a table if needed
    - Evaluates a diff if needed
    - Either diff is empty, or
    - Diff is a ALTER TABLE statement, passed on to
    VReplication/gh-ost/pt-osc as Online DDL.
    Declarative Online DDL: how?

    View Slide

  41. Resources
    Docs: vitess.io/docs/
    Code: github.com/vitessio/vitess
    Slack: vitess.slack.com

    View Slide

  42. Thank you!
    Questions?
    github.com/shlomi-noach
    @ShlomiNoach

    View Slide