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

▶
changes using percona live open source database
SMART_READER_LITE
LIVE PREVIEW

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

Jun 15, 2023 206 likes •593 views

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

slide-1
SLIDE 1 How people build sofuware
  • Automating Schema

Changes using

Percona Live Open Source Database Conference 2017-04-27 | 12:50 PM - 1:40 PM | Ballroom D
slide-2
SLIDE 2 How people build sofuware
  • 2

Tom Krouper

  • @CaptainEyesight
  • @tomkrouper
  • Database Infrastructure Engineer
slide-3
SLIDE 3 How people build sofuware
  • 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.
3
slide-4
SLIDE 4 How people build sofuware
  • 4

GitHub Online Schema Transmogrifier

slide-5
SLIDE 5 How people build sofuware
  • 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]
  • gh-ost
5
  • https://github.com/github/gh-ost
slide-6
SLIDE 6 How people build sofuware
  • Migrations
without the locking ALTER 6
slide-7
SLIDE 7 How people build sofuware
  • Rails
7 $ 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” ...
slide-8
SLIDE 8 How people build sofuware
  • 8

Hubot

Chatops automation
slide-9
SLIDE 9 How people build sofuware
  • Chatops commands
9

.migration .mysql .qmtd .queue for github .deploy .nagios .xtrabackup .mysqlproxy .truck me .where is

slide-10
SLIDE 10 How people build sofuware
  • Putuing it all together
automate all the things…
 well, at least some of the things 10
  • credit: https://hyperboleandahalf.blogspot.com/2010/06/this-is-why-ill-never-be-adult.html
slide-11
SLIDE 11 How people build sofuware
  • Schema Migrations
11
slide-12
SLIDE 12 How people build sofuware
  • Schema Migrations
12
  • $ 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
slide-13
SLIDE 13 How people build sofuware
  • Schema Migration: Automation
13 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.

.migration-queue

slide-14
SLIDE 14 How people build sofuware
  • Schema Migrations
14
slide-15
SLIDE 15 How people build sofuware
  • Schema Migrations
15
slide-16
SLIDE 16 How people build sofuware
  • .migration queue show
16
slide-17
SLIDE 17 How people build sofuware
  • .migration queue add
17
slide-18
SLIDE 18 How people build sofuware
  • .migration queue schedule
18
slide-19
SLIDE 19 How people build sofuware
  • Schema Migrations
19
  • CREATE
  • DROP
slide-20
SLIDE 20 How people build sofuware
  • Schema Migrations
20
  • CREATE
  • DROP
slide-21
SLIDE 21 How people build sofuware
  • Schema Migrations
21
  • CREATE
  • Scheduling migrations outputs the CREATE TABLE statement
  • DROP
  • Outputs chatops command to “RENAME TABLE“
slide-22
SLIDE 22 How people build sofuware
  • Schema Migrations
22
  • ALTER
slide-23
SLIDE 23 How people build sofuware
  • Schema Migrations
23
  • ALTER
  • Runs a script that calls gh-ost
slide-24
SLIDE 24 How people build sofuware
  • gh-migrate-ghost
24 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
slide-25
SLIDE 25 How people build sofuware
  • Running Migration Automation
25 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)

.migration

slide-26
SLIDE 26 How people build sofuware
  • Running Migration Automation
26

.migration sup

slide-27
SLIDE 27 How people build sofuware
  • Running Migration Automation
27

.migration status

slide-28
SLIDE 28 How people build sofuware
  • Running Migration Automation
28

.migration nice-ratio <ratio> .migration max-lag-millis <maxlag> .migration max-load <status=val> .migration critical-load <status=val> .migration throtule-control-replicas <hosts> .migration throtule-htup <url>

slide-29
SLIDE 29 How people build sofuware
  • Running Migration Automation
29

.migration throtule .migration resume .migration cut-over <table_name> .migration panic

slide-30
SLIDE 30 How people build sofuware
  • Running Migration Automation
30

.migration cut-over <table_name>

slide-31
SLIDE 31 How people build sofuware
  • Finishing the Migration
31

.migration-queue completed <pr>

slide-32
SLIDE 32 How people build sofuware
  • Merging the PR
32
  • qmtd <url for pr>
  • … wait
  • update branch
  • wait for ci tests
  • merge (deploy)
slide-33
SLIDE 33 How people build sofuware …
  • Next Pull Request
33
slide-34
SLIDE 34 How people build sofuware
  • Automation Issues
34
  • Migration Scheduler
  • Automatic gh-ost runs
  • Automatic pull request merge
slide-35
SLIDE 35 How people build sofuware
  • Lessons Learned
35
  • Smaller changes
  • Ask for help
  • Dig in as deep as you can first
  • You can’t automate everything
slide-36
SLIDE 36 How people build sofuware
  • Opportunity for improvements
36
  • 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
slide-37
SLIDE 37 How people build sofuware
  • @CaptainEyesight
tomkrouper@github.com

Questions / Thanks

We’re Hiring: Platform Data Engineer htups://bit.ly/platform-data