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

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

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

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

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

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 ↓ ΧϥϜ͕௥Ճ͞ΕΔ

εΩʔϚ؅ཧͱ ӡ༻

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

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

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

DDL͕ॏ͍ͱ͖ Ͳ͏͢Δ͔

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

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

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

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

ALTER TABLE ͭΒ͘ͳ͍ʂ

׬

No content

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

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

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

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

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

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

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

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

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

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

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

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'

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

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

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) ===================================================

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

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ͷόʔδϣϯʹ͸ґଘ͍ͯ͠ͳ͍

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 # (ଓ͘)

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

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 ʣ

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

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

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

͓·͚: ͭͬͯ͘Έͨ 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

͓·͚: ͭͬͯ͘Έͨ 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

͓·͚: ͭͬͯ͘Έͨ $ 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. (ଓ͘)

͓·͚: ͭͬͯ͘Έͨ (ଓ͖) 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) =====================================

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

No content