Slide 1

Slide 1 text

pt-oscతԿ͔ͱ ϚΠάϨʔγϣϯͷ ͢Γ͋Θͤʹ͍ͭͯ Genki Sugawara

Slide 2

Slide 2 text

echo $(whoami) • ܙൺणͷํ͔Βདྷ·ͨ͠ • Rubyͱ͔AWSͱ͔৮ͬͯ·͢ • ίʔυԽ͓͡͞Μ΍ͬͯ·͢ • twitter: @sgwr_dts / github: winebarrel • ࡞ͬͨ΋ͷ • https:/ /codenize.tools/ • https:/ /rubygems.org/profiles/winebarrel

Slide 3

Slide 3 text

RailsϚΠάϨʔγϣϯͲ͏Ͱ͠ΐ͏ • ศརͩͱࢥ͍·͢ʢখฒײʣ • Railsͷձࣾʹ7೥͙Β͍ͭͱΊ͍ͯ·͕͢ ͖ͪΜͱӡ༻ͨ͜͠ͱ͕ͳ͍Α͏ͳ… • ຊ೔͸MySQLͷALTER TABLEͭΒ͍ɺͱ͍͏࿩Ͱ͢

Slide 4

Slide 4 text

ฐࣾͰͷεΩʔϚ؅ཧͷมભ εϓϨουγʔτ ↓ WebΞϓϦ ↓ ghe+mysqldump+rakeλεΫ ↓ ridgepole ৄ͘͠͸: http:/ /www.slideshare.net/winebarrel/20140930-anything-ascode/33

Slide 5

Slide 5 text

ridgepole • https:/ /github.com/winebarrel/ridgepole • ႈ౳ੑΛ୲อ͢ΔεΩʔϚ؅ཧπʔϧ • ओʹӡ༻ෛՙͷܰݮΛ໨తʹ࡞੒ • ӶҙRails5ରԠத • σϞ: https:/ /asciinema.org/a/11788

Slide 6

Slide 6 text

ridgepole --- a/Schemafile +++ b/Schemafile @@ -1,6 +1,7 @@ create_table "articles", force: :cascade do |t| t.string "title" t.text "text" + t.text "author" t.datetime "created_at" t.datetime "updated_at" end ↓ ΧϥϜ͕௥Ճ͞ΕΔ

Slide 7

Slide 7 text

εΩʔϚ؅ཧͱ ӡ༻

Slide 8

Slide 8 text

DDLʹΑΔॻ͖ࠐΈͷϩοΫ • εΩʔϚ؅ཧʹ͓͚Δӡ༻ͷఢ • Լखʹద༻͢ΔͱαʔϏε͕ࢭ·Δ

Slide 9

Slide 9 text

DDLʹΑΔॻ͖ࠐΈͷϩοΫ ݸਓతײ֮ • େৎ෉ • 1ສ݅ҎԼ / 1GBҎԼ • ·͍͔ͣ΋… • 10ສ݅ / 5GB͘Β͍ • ͪΐͬͱ଴ͬͯʂ • 100ສ݅ / 10GBҎ্

Slide 10

Slide 10 text

DDLʹΑΔॻ͖ࠐΈͷϩοΫ ฐࣾͰ͸CIͰ୲อͯ͠·͢ • ΫοΫύουʹ͓͚Δ࠷ۙͷActiveRecordӡ༻ࣄ৘ • http:/ /techlife.cookpad.com/entry/2014/08/28/194147

Slide 11

Slide 11 text

DDL͕ॏ͍ͱ͖ Ͳ͏͢Δ͔

Slide 12

Slide 12 text

pt-online-schema-change https:/ /www.percona.com/doc/percona-toolkit/2.1/pt-online- schema-change.html • ௨শpt-osc • Percona Toolkitͷͻͱͭ • ॻ͖ࠐΈΛϩοΫ͠ͳ͍ͰDDLΛ࣮ߦͰ͖Δ • ϨϓϦέʔγϣϯͷ؂ࢹɾϓϥάΠϯػߏɾෛՙ؂ࢹͳͲ ศརػೳଟ਺

Slide 13

Slide 13 text

pt-online-schema-change openark kitͷoak-online-alter-tableͱFacebookͷ OnlineSchemaChange.php͕ݩωλ • openark kit / oak-online-alter-tabl • http:/ /code.openark.org/forge/openark-kit • https:/ /code.google.com/archive/p/openarkkit/wikis/ OakOnlineAlterTableConcurrency.wiki • OnlineSchemaChange.php • https:/ /www.facebook.com/notes/mysql-at-facebook/online- schema-change-for-mysql/430801045932

Slide 14

Slide 14 text

ridgepoleͱpt-osc ridgepoleʹ͸֎෦εΫϦϓτͰSQLΛ࣮ߦ͢Δػೳ͕͋Δ $ cat test.sh #!/bin/sh SQL="$1" CONFIG_JSON="$2" echo "$SQL" | mysql -u root my_db $ ridgepole -c config.yml --apply --external-script ./test.sh https:/ /github.com/winebarrel/ridgepole#execute-sql-using-external- script

Slide 15

Slide 15 text

ridgepoleͱpt-osc ALTER͕ॏ͗ͯ͢CI͕͚͜Δ ↓ --external-script෇͖ͰridgepoleΛ࣮ߦ ↓ pt-osc࣮ߦ༻ͷεΫϦϓτ͕ग़ྗ͞ΕΔ ʢ௚઀࣮ߦ͸͠ͳ͍ʣ ↓ εΫϦϓτΛ࣮ߦ

Slide 16

Slide 16 text

ALTER TABLE ͭΒ͘ͳ͍ʂ

Slide 17

Slide 17 text

׬

Slide 18

Slide 18 text

No content

Slide 19

Slide 19 text

৽نҊ݅ͱ ϚΠάϨʔγϣϯ

Slide 20

Slide 20 text

৽نҊ݅ͱϚΠάϨʔγϣϯ • rigdepole͸ศར͚ͩͲϨʔϧ͔Β֎Ε͍ͯΔ • ৽نҊ݅͸ී௨ʹϚΠάϨʔγϣϯΛ࢖͍ͬͯΔ • σʔλ͕গͳ͍͏ͪ͸ಛʹ໰୊ͳ͍

Slide 21

Slide 21 text

σʔλ͕૿͑ͨΒ Ͳ͏͢Δ͔…ʁ

Slide 22

Slide 22 text

1. ϝϯςφϯεʹೖΕΔ Pros • γϯϓϧɺૉ௚ • ೔ৗతʹϝϯςφϯεʹೖΕΔΑ͏ʹ͢Δ͜ͱͰ ϝϯςφϯε͕͠΍͘͢ͳΔ

Slide 23

Slide 23 text

1. ϝϯςφϯεʹೖΕΔ Cons • αʔϏεͷػձଛࣦ • ػձଛࣦ௿ݮͷͨΊͷਂ໷ϝϯς →ӡ༻ෛՙ૿େ • ن໛͕େ͖͘ͳΔͱϝϯςφϯεʹೖΕʹ͍͘

Slide 24

Slide 24 text

2. खಈͰpt-osc pt-online-schema-change \ --alter 'ADD user_id int(11)' \ D=hello_development,t=items \ --recursion-method none \ --check-alter \ --user root \ --dry-run # --execute

Slide 25

Slide 25 text

2. खಈͰpt-osc Pros • μ΢ϯλΠϜͳ͠ • pt-oscͷศརػೳΛͦͷ··࢖͑Δ • ్தͰʮ͋ɺ·͍ͣʯͱͳͬͯ΋҆શʹఀࢭͰ͖Δ

Slide 26

Slide 26 text

2. खಈͰpt-osc Cons • ਓྗ • ϚΠάϨʔγϣϯ͔ΒSQLΛ૊ΈཱͯΔඞཁ͕͋Δ • ίϛοτ͔ΒϚΠάϨʔγϣϯϑΝΠϧ࡟আ →खಈ࣮ߦɺͱ͍͏ख͕͔͔ؒΔ

Slide 27

Slide 27 text

3. MySQL 5.6 ΦϯϥΠϯDDL MySQL :: MySQL 5.6 ϦϑΝϨϯεϚχϡΞϧ 14.11.1 ΦϯϥΠϯ DDL ͷ֓ཁ ςʔϒϧ͕มߋ͞Ε͍ͯΔ࠷தʹ SELECT ΫΤϦʔ΍ INSERTɺ UPDATEɺ͓Αͼ DELETE (DML) εςʔτϝϯτͷॲཧΛଓߦͰ͖ ΔΑ͏ʹͳΓ·͢ɻ͜ͷػೳͷ૊Έ߹Θͤ͸ݱࡏɺΦϯϥΠϯ DDL ͱݺ͹Ε·͢ɻ — https:/ /dev.mysql.com/doc/refman/5.6/ja/innodb-create-index- overview.html

Slide 28

Slide 28 text

3. MySQL 5.6 ΦϯϥΠϯDDL Pros • ඪ४తͳػೳ • μ΢ϯλΠϜ͕ൃੜ͠ͳ͍

Slide 29

Slide 29 text

3. MySQL 5.6 ΦϯϥΠϯDDL Cons • ALGORITHM=INPLACEʹͳΔ৚͕݅͋Δ • add_columnͱ͔ͰLOCK=NONE͕͚ͭΒΕͳ͍ • ϨϓϦέʔγϣϯͷ஗Ԇ͕ൃੜ͢Δ

Slide 30

Slide 30 text

4. RailsΛ֦ு͢Δᶃ steverice/pt-osc Runs regular Rails/ActiveRecord migrations via the Percona Toolkit pt- online-schema-change tool. • https:/ /github.com/steverice/pt-osc • 'rails', '~> 3.2'

Slide 31

Slide 31 text

steverice/pt-osc development: adapter: mysql_pt_osc # ~~~~~~~~~~~~ encoding: utf8 database: hello_development username: root host: localhost percona: recursion-method: none

Slide 32

Slide 32 text

steverice/pt-osc class Fix < ActiveRecord::PtOscMigration # ~~~~~~~~~~~~~~~~~~~~~~~~~~~~ def up add_column :items, :user_id, :integer, :null => false end def down remove_column :items, :user_id end end

Slide 33

Slide 33 text

steverice/pt-osc $ bundle exec rake db:migrate == Fix: migrating ============================================================ -- add_column(:items, :user_id, :integer, {:null=>false}) -> 0.0002s == Fix: Run the following commands: ========================================== pt-online-schema-change --alter ADD\ \`user_id\`\ int\(11\)\ NOT\ NULL D\=hello ^ _development,t\=items --dry-run --recursion-method none --check-alter --user root pt-online-schema-change --alter ADD\ \`user_id\`\ int\(11\)\ NOT\ NULL D\=hello ^ _development,t\=items --execute --recursion-method none --check-alter --user root == Fix: migrated (0.0004s) ===================================================

Slide 34

Slide 34 text

steverice/pt-osc Pros • pt-oscͱϚΠάϨʔγϣϯΛγʔϜϨεʹग़དྷΔ Cons • Rails 4Ҏ্ʹରԠ͞ΕΔؾ഑͕ͳ͍

Slide 35

Slide 35 text

4. RailsΛ֦ு͢Δᶄ soundcloud/lhm Rails style database migrations are a useful way to evolve your data schema in an agile manner. Most Rails projects start like this, and at first, making changes is fast and easy. • https:/ /github.com/soundcloud/lhm • ݩωλ͸pt-oscͱಉ༷ɺOAKͱfacebook tool • Railsͷόʔδϣϯʹ͸ґଘ͍ͯ͠ͳ͍

Slide 36

Slide 36 text

soundcloud/lhm class Fix < ActiveRecord::Migration def self.up Lhm.change_table :items do |m| m.add_column :arbitrary, "INT(12)" m.add_index [:arbitrary] m.ddl("alter table %s add column flag tinyint(1)" % m.name) end Lhm.cleanup(:run) end # (ଓ͘)

Slide 37

Slide 37 text

soundcloud/lhm # (ଓ͖) def self.down Lhm.change_table :items do |m| m.remove_index [:arbitrary] m.remove_column :arbitrary m.ddl("alter table %s drop column flag" % m.name) end Lhm.cleanup(:run) end end

Slide 38

Slide 38 text

soundcloud/lhm $ bundle exec rake db:migrate == 20160528071947 Fix: migrating ============================================== I, [2016-05-28T07:31:00.259004 #18914] INFO -- : Starting LHM run on table=lhmn_items I, [2016-05-28T07:31:00.259640 #18914] INFO -- : Starting run of class=Lhm::Migrator I, [2016-05-28T07:31:00.329716 #18914] INFO -- : Starting run of class=Lhm::Entangler I, [2016-05-28T07:31:00.345568 #18914] INFO -- : Starting run of class=Lhm::Chunker I, [2016-05-28T07:31:00.345621 #18914] INFO -- : Starting run of class=Lhm::AtomicSwitcher == 20160528071947 Fix: migrated (0.1247s) ===================================== ΍͍ͬͯΔ͜ͱ͸pt-oscͱେମ͓ͳ͡ ʢϩά: https:/ /git.io/vr92Q ʣ

Slide 39

Slide 39 text

soundcloud/lhm Pros • ϚΠάϨʔγϣϯͱγʔϜϨε • Railsͱ͸ʢҰԠʣಠཱ͍ͯ͠ΔͷͰόʔδϣϯʹࠨӈ͞Εͳ͍ Cons • ϨϓϦ஗Ԇ؂ࢹͷΑ͏ͳػೳ͸ͳ͍

Slide 40

Slide 40 text

·ͱΊ ਓྗʹ఍߅͕ͳ͍ͳΒpt-oscΛ खಈ࣮ߦͰ͍͍ͱࢥ͏ ͋͘·ͰγʔϜϨεͳ ϚΠάϨʔγϣϯʹͩ͜ΘΔ + ϨϓϦ஗ΕΛؾʹ͠ͳ͍ͳΒ Online DDL or Lhm

Slide 41

Slide 41 text

͓·͚: ͭͬͯ͘Έͨ Arproxyͱ͍͏ϥΠϒϥϦ͕͋Γ·ͯ͠… https:/ /github.com/cookpad/arproxy executeΛϑοΫͰ͖ΔͷͰ ϚΠάϨʔγϣϯͱpt-oscͷ࿈ܞΛࣗ࡞ͯ͠Έ·ͨ͠

Slide 42

Slide 42 text

͓·͚: ͭͬͯ͘Έͨ db/migrate/20160528071947_fix.rb class Fix < ActiveRecord::Migration def self.up Arproxy.enable! execute '/* pt-osc */ {"table":"items", "alter":"ADD user_id int(11)"}' end def self.down Arproxy.enable! execute '/* pt-osc */ {"table":"items", "alter":"DROP user_id"}' end end

Slide 43

Slide 43 text

͓·͚: ͭͬͯ͘Έͨ lib/pt-osc.rb - https:/ /git.io/vr9Vp class PtOsc < Arproxy::Base def execute(sql, name=nil) if sql =~ %r|\A/\*\ pt\-osc\ \*/(.+)| info = JSON.parse($1) system( 'pt-online-schema-change', '--alter', info['alter'], "D=hello_development,t=#{info['table']}", '--recursion-method', 'none', '--user', 'root', '--execute') nil else super(sql, name) end end end Arproxy.configure do |config| config.adapter = "mysql2" config.use PtOsc end

Slide 44

Slide 44 text

͓·͚: ͭͬͯ͘Έͨ $ bundle exec rake db:migrate == 20160528071947 Fix: migrating ============================================== -- execute("/* pt-osc */ {\"table\":\"items\", \"alter\":\"ADD user_id int(11)\"}") No slaves found. See --recursion-method if host mysql-001 has slaves. Not checking slave lag because no slaves were found and --check-slave-lag was not specified. Operation, tries, wait: analyze_table, 10, 1 copy_rows, 10, 0.25 create_triggers, 10, 1 drop_triggers, 10, 1 swap_tables, 10, 1 update_foreign_keys, 10, 1 Altering `hello_development`.`items`... Creating new table... Created new table hello_development._items_new OK. Altering new table... Altered `hello_development`.`_items_new` OK. (ଓ͘)

Slide 45

Slide 45 text

͓·͚: ͭͬͯ͘Έͨ (ଓ͖) 2016-05-28T08:34:59 Creating triggers... 2016-05-28T08:34:59 Created triggers OK. 2016-05-28T08:34:59 Copying approximately 1 rows... 2016-05-28T08:34:59 Copied rows OK. 2016-05-28T08:34:59 Analyzing new table... 2016-05-28T08:34:59 Swapping tables... 2016-05-28T08:34:59 Swapped original and new tables OK. 2016-05-28T08:34:59 Dropping old table... 2016-05-28T08:34:59 Dropped old table `hello_development`.`_items_old` OK. 2016-05-28T08:34:59 Dropping triggers... 2016-05-28T08:34:59 Dropped triggers OK. Successfully altered `hello_development`.`items`. -> 0.2397s == 20160528071947 Fix: migrated (0.2403s) =====================================

Slide 46

Slide 46 text

͓·͚: ͭͬͯ͘Έͨ ࣮ࡍʹ͸ΤϥʔϋϯυϦϯάͳͲͻͭΑ͏ͩͱࢥ͍·͕͢ ࢖͑ͳ͘͸ͳ͍Ͱ͢ɻ Arproxyͷpt-oscϓϥάΠϯ ୭͔࡞͍ͬͯͩ͘͞

Slide 47

Slide 47 text

No content