Vitess: Online Schema Migration Automation

by Shlomi Noach

Slide 1

Slide 1 text

PerconaLive EU 2020 Vitess: online schema migration automation Shlomi Noach

Slide 2

Slide 2 text

Engineer at PlanetScale Author of orchestrator, gh-ost, freno and others Blog at openark.org github.com/shlomi-noach @ShlomiNoach About me

Slide 3

Slide 3 text

• Founded in February 2018 • Venture backed: a16z, SignalFire • ~40 employees distributed • Authors Vitess • Offers PlanetScaleDB for cloud offering and PlanetScaleDB BYOK: Bring Your Own Kubernetes PlanetScale

Slide 4

Slide 4 text

Vitess • CNCF graduated project • Maintained by the community • PlanetScale is a major contributor A database clustering system for horizontal scaling of MySQL

Slide 5

Slide 5 text

Vitess architecture Consider a simple replication topology

Slide 6

Slide 6 text

Vitess architecture vttablet • A daemon/sidecar • Controls the mysqld process • Interacts with the mysqld server • Typically on same host as mysqld

Slide 7

Slide 7 text

Vitess architecture

Slide 8

Slide 8 text

Vitess architecture vtgate vtgate • A smart proxy • Speaks mysql protocol • Impersonates the database • Relays queries to tablets

Slide 9

Slide 9 text

Vitess architecture vtgate • Stateless • Scale out

Slide 10

Slide 10 text

Vitess architecture commerce shard 0 commerce shard 1 internal unsharded

Slide 11

Slide 11 text

Vitess architecture commerce shard 0 commerce shard 1 internal unsharded USE commerce SELECT * FROM orders WHERE customer_id=4

Slide 12

Slide 12 text

Vitess architecture commerce shard 0 commerce shard 1 internal unsharded topo topo • Vitess state backend • etcd/zk/consul • Info: • Sharding schemes • Schemas • Clusters • Tablets, servers, • Operations • ...

Slide 13

Slide 13 text

Vitess architecture commerce shard 0 commerce shard 1 internal unsharded topo vtctld • Daemon • API server • Operations

Slide 14

Slide 14 text

Vitess architecture commerce shard 0 commerce shard 1 internal unsharded topo

Slide 15

Slide 15 text

Mystery card ? A hidden truth

Slide 16

Slide 16 text

• ALTER TABLE is blocking Issues with schema migrations

Slide 17

Slide 17 text

Issues with schema migrations • ALTER TABLE is blocking • gh-ost, pt-online-schema-change, fb-osc as alternatives Installing, formalizing, command line options

Slide 18

Slide 18 text

Issues with schema migrations • ALTER TABLE is blocking • gh-ost, pt-online-schema-change, fb-osc as alternatives • Discovery commerce shard 0 commerce shard 1 internal unsharded

Slide 19

Slide 19 text

Issues with schema migrations • ALTER TABLE is blocking • gh-ost, pt-online-schema-change, fb-osc as alternatives • Discovery • Running commerce shard 0 commerce shard 1

Slide 20

Slide 20 text

Issues with schema migrations • ALTER TABLE is blocking • gh-ost, pt-online-schema-change, fb-osc as alternatives • Discovery • Running • Throttling commerce shard 0 commerce shard 1

Slide 21

Slide 21 text

Issues with schema migrations • ALTER TABLE is blocking • gh-ost, pt-online-schema-change, fb-osc as alternatives • Discovery • Running • Throttling • Visibility commerce shard 0 commerce shard 1

Slide 22

Slide 22 text

Issues with schema migrations • ALTER TABLE is blocking • gh-ost, pt-online-schema-change, fb-osc as alternatives • Discovery • Running • Throttling • Visibility • Control commerce shard 0 commerce shard 1

Slide 23

Slide 23 text

Vitess’ architecture has the knowledge and the capability to address the above. Issues with schema migrations

Slide 24

Slide 24 text

Vitess Online DDL ALTER WITH ‘gh-ost’ TABLE orders ADD COLUMN ts TIMESTAMP ZeroDependenciesNoReally™

Slide 25

Slide 25 text

Vitess Online DDL ALTER WITH ‘pt-osc’ TABLE orders ADD COLUMN ts TIMESTAMP ZeroDependenciesNoReally™

Slide 26

Slide 26 text

Vitess Online DDL DEMO

Slide 27

Slide 27 text

Vitess Online DDL What just happened?

Slide 28

Slide 28 text

Online DDL ﬂow commerce shard 0 commerce shard 1 internal unsharded topo vtgate • Intercepts • Parses • Persists

Slide 29

Slide 29 text

Online DDL ﬂow commerce shard 0 commerce shard 1 internal unsharded topo topo • Stores

Slide 30

Slide 30 text

Online DDL ﬂow commerce shard 0 commerce shard 1 internal unsharded topo vtctld • Observes • Discovers • Distributes • Validates

Slide 31

Slide 31 text

Online DDL ﬂow commerce shard 0 commerce shard 1 internal unsharded topo vttablet • Has all the fun

Slide 32

Slide 32 text

vttablet on primary • Schedules • Prepares script(s) • Runs gh-ost or pt-osc • Tracks • Throttles • Cleans up • Tracks state in local _vt meta schema Online DDL ﬂow

Slide 33

Slide 33 text

Vitess Online DDL DEMO, continued

Slide 34

Slide 34 text

Vitess Online DDL ZeroDependenciesNoReally™

Slide 35

Slide 35 text

Q: Which MySQL account and privileges do I need for the migration user? How do I tell Vitess the credentials? A: Vitess creates a migration user with random password for each migration, on your behalf. You do not need to do anything.

Slide 36

Slide 36 text

Q: How do I setup the gh-ost hooks? A: Vitess creates gh-ost hooks directory and scripts for you. Vitess creates a pt-osc plugin for you. You do not need to do anything.

Slide 37

Slide 37 text

Q: Where do I install gh-ost? A: On linux amd64 and with recent glibc, Vitess comes with gh-ost binary pre-compiled and embedded, and will extract the binary on your behalf. You do not need to do anything.

Slide 38

Slide 38 text

Q: Where do I install gh-ost? (Cont.) Otherwise use -gh-ost-path. pt-online-schema-change is not bundled. Install it on all MySQL servers. Use -pt-osc-path

Slide 39

Slide 39 text

Q: How do I configure the throttling replicas? A: Each vttablet runs a throttler service, which automatically and dynamically detects the throttling replicas. It evaluates replication lag autonomically. Both gh-ost and pt-osc are configured to use it. You do not need to do anything (but you can configure -throttle_tablet_types="replica,rdonly" )

Slide 40

Slide 40 text

Q: How do I drop pt-osc leftover triggers in case of failure? A: Vitess will identify a failed migration, even if vttablet itself fails, and will drop the triggers on your behalf. You do not need to do anything.

Slide 41

Slide 41 text

Q: How do I drop the leftover tables? A: Each primary vttablet runs a table lifecycle process. It will collect the leftover tables (whether migration is successful or failed) and will safely and slowly (using the throttler mechanism) purge and drop them. You do not need to do anything.

Slide 42

Slide 42 text

Mystery card reveal ? A hidden truth, In something not said

Slide 43

Slide 43 text

Vitess as an infrastructure framework None of the above requires you to run trafﬁc through Vitess. You may keep running your production trafﬁc as normal, if you wish, and only use Vitess for infrastructure.

Slide 44

Slide 44 text

• GitHub and GitHub team: • github.com/github/gh-ost • github.com/github/freno • Lessons learned • Percona • percona.com/doc/percona-toolkit • Community • Vitess maintainers • PlanetScale team Acknowledgements

Slide 45

Slide 45 text

Resources • Docs: vitess.io/docs • Code: github.com/vitessio/vitess • Slack: vitess.slack.com • Demo: asciinema play https://git.io/JUhMa

Slide 46

Slide 46 text

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