Share
Embed
Transcript
47 slides
-
echo $(whoami) • ܙൺणͷํ͔Βདྷ·ͨ͠ • Rubyͱ͔AWSͱ͔৮ͬͯ·͢ • ίʔυԽ͓͡͞Μͬͯ·͢ • twitter:
@sgwr_dts / github: winebarrel • ࡞ͬͨͷ • https:/ /codenize.tools/ • https:/ /rubygems.org/profiles/winebarrel -
ฐࣾͰͷεΩʔϚཧͷมભ εϓϨουγʔτ ↓ 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ʹΑΔॻ͖ࠐΈͷϩοΫ ݸਓతײ֮ • େৎ • 1ສ݅ҎԼ / 1GBҎԼ • ·͍͔ͣ…
• 10ສ݅ / 5GB͘Β͍ • ͪΐͬͱͬͯʂ • 100ສ݅ / 10GBҎ্ -
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 -
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 -
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 -
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) =================================================== -
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 ʣ -
͓·͚: ͭͬͯ͘Έͨ 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) =====================================