Changes using Percona Live Open Source Database Conference - PowerPoint PPT Presentation

Automating Schema � Changes using Percona Live Open Source Database Conference 2017-04-27 | 12:50 PM - 1:40 PM | Ballroom D � How people build so fu ware

� Tom Krouper • @CaptainEyesight • � @tomkrouper • Database Infrastructure Engineer � How people build so fu ware 2

MySQL at GitHub • GitHub stores repositories in git, and uses MySQL as the backend database for all related metadata: • Repository metadata, users, issues, pull requests, comments etc. � • Website/API/Auth/more all use MySQL. • We run a few (growing number of) clusters, totaling around 100 MySQL servers. • The setup isn’t very large but very busy. • Our MySQL service must be highly available. � How people build so fu ware 3

� GitHub Online Schema Transmogrifier � How people build so fu ware 4

gh-ost � gh-ost � --host=replica.with.rbr.com --database="my_schema" � � � --table="my_table" --alter="engine=innodb" � --max-load=Threads_running=25 --critical-load=Threads_running=1000 --critical-load-interval-millis=3000 --throttle-http="http://freno/${cluster}" � — throttle-control-replicas=“replica1,replica2" --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] https://github.com/github/gh-ost � How people build so fu ware 5

Migrations � without the locking ALTER � How people build so fu ware 6

Rails � $ cat db/migrate/20170427125019_add_some_id_to_example_table.rb # frozen_string_literal: true class AddSomeIdToExampleTable < GitHub::Migration def up add_column :example_table, :some_id, :integer end def down remove_column :example_table, :some_id end end gh-ost ... — table="example_table" — alter=“ADD COLUMN some_id int(11) DEFAULT NULL” ... � How people build so fu ware 7

Hubot � Chatops automation � How people build so fu ware 8

Chatops commands � . migration . nagios . mysql . xtrabackup . qmtd . mysqlproxy . queue for github . truck me . deploy . where is � How people build so fu ware 9

Pu tu ing it all together � automate all the things… 
 well, at least some of the things credit: https://hyperboleandahalf.blogspot.com/2010/06/this-is-why-ill-never-be-adult.html � How people build so fu ware 10

Schema Migrations � � � How people build so fu ware 11

Schema Migrations � � $ cat db/migrate/20170427125019_add_some_id_to_example_table.rb # frozen_string_literal: true class AddSomeIdToExampleTable < GitHub::Migration def up add_column :example_table, :some_id, :integer end def down remove_column :example_table, :some_id end end � How people build so fu ware 12

Schema Migration: Automation � � .migration-queue Usage: .migration-queue <command> [<args>...] Workflow chatops for database migrations show [needs_review|reviewed|scheduled] - Show all migrations in queue. add <pr-number> - Add a migration to the queue. schedule <pr-number> - Schedule a migration to be run. completed <pr-number> <migration-version> - Mark a migration as completed. � How people build so fu ware 13

Schema Migrations � � � How people build so fu ware 14

Schema Migrations � � � How people build so fu ware 15

.migration queue show � � � How people build so fu ware 16

.migration queue add � � � How people build so fu ware 17

.migration queue schedule � � � How people build so fu ware 18

Schema Migrations � � • CREATE • DROP � How people build so fu ware 19

Schema Migrations � � • CREATE • DROP � How people build so fu ware 20

Schema Migrations � � • CREATE • Scheduling migrations outputs the CREATE TABLE statement • DROP • Outputs chatops command to “RENAME TABLE“ � How people build so fu ware 21

Schema Migrations � � • ALTER � How people build so fu ware 22

Schema Migrations � � • ALTER • Runs a script that calls gh-ost � How people build so fu ware 23

gh-migrate-ghost � gh-ost \ --conf=/etc/mysql/gh-ost.cnf \ --host=$ghost_replica \ --database="$database_name" --table="$table_name" --alter="$ddl" \ --max-load=Threads_running=25 --critical-load=Threads_running=1000 \ --critical-load-interval-millis=3000 --chunk-size=$chunk_size \ --throttle-http="http://${freno}:8111/check/gh-ost/mysql/${cluster}" \ --max-lag-millis=500 --heartbeat-interval-millis=100 \ --switch-to-rbr --allow-master-master --cut-over=default \ --timestamp-old-table \ --exact-rowcount --concurrent-rowcount --default-retries=120 \ --panic-flag-file=/tmp/ghost.panic.flag \ --postpone-cut-over-flag-file=/etc/github/ghost.postpone.flag \ --serve-socket-file="$socket_file" \ --hooks-path=${hooks_directory} --hooks-hint="${hooks_hint_user}" \ --verbose \ $execute $extra_args � How people build so fu ware 24

Running Migration Automation � .migration � Manage running gh-ost migrations Usage: .migration sup show brief status of running migrations .migration status show full status of running migrations .migration local show status of running migrations on localhost (useful from SSH logins) .migration nice-ratio <ratio> set new nice-ratio for active migration .migration max-lag-millis <maxlag> set new max-lag milliseconds for active migration .migration max-load <status=val> set new status check for gh-ost to pause on, e.g., threads_running=30 .migration critical-load <status=val> set new status check for gh-ost to stop on, e.g., threads_running=1000 .migration throttle-control-replicas <hosts> set the replicas using commma delimited list of hosts. .migration throttle-http <url> change freno URL for running migrations. Empty string to disable throttling via freno. .migration throttle|pause|suspend force throttling of active migrations .migration no-throttle|continue|resume terminate forced throttling (other throttling reasons may still apply) .migration unpostpone|cut-over <table_name> cease to actively postpone; proceed to cut-over and completion .migration panic kill the running migration (requires magic_word) � How people build so fu ware 25

Running Migration Automation � .migration sup � � How people build so fu ware 26

Running Migration Automation � .migration status � � How people build so fu ware 27

Running Migration Automation � .migration nice-ratio <ratio> � .migration max-lag-millis <maxlag> .migration max-load <status=val> .migration critical-load <status=val> .migration thro tu le-control-replicas <hosts> .migration thro tu le-h tu p <url> � How people build so fu ware 28

Running Migration Automation � .migration thro tu le � .migration resume .migration cut-over <table_name> .migration panic � How people build so fu ware 29

Running Migration Automation � .migration cut-over <table_name> � � How people build so fu ware 30

Finishing the Migration � .migration-queue completed <pr> � � How people build so fu ware 31

Merging the PR � � • qmtd <url for pr> • … wait • update branch • wait for ci tests • merge (deploy) � How people build so fu ware 32

Next Pull Request � � � … � How people build so fu ware 33

Automation Issues � � � • Migration Scheduler • Automatic gh-ost runs • Automatic pull request merge How people build so fu ware 34

Lessons Learned � � � • Smaller changes • Ask for help • Dig in as deep as you can first • You can’t automate everything How people build so fu ware 35

Opportunity for improvements � • Everything is iterative • More automation ideas • PR approval could include `.migration-queue add`. • merge `.migration-queue add` & `schedule` • rails level changes to avoid merge conflicts � How people build so fu ware 36

� We’re Hiring: Platform Data Engineer h tu ps://bit.ly/platform-data Questions / Thanks @CaptainEyesight tomkrouper@github.com � How people build so fu ware