Upgrade to Pro — share decks privately, control downloads, hide ads and more …

pt-osc的何かとマイグレーションのすりあわせについて

 pt-osc的何かとマイグレーションのすりあわせについて

Genki Sugawara

May 30, 2016
Tweet

More Decks by Genki Sugawara

Other Decks in Technology

Transcript

  1. echo $(whoami) • ܙൺणͷํ͔Βདྷ·ͨ͠ • Rubyͱ͔AWSͱ͔৮ͬͯ·͢ • ίʔυԽ͓͡͞Μ΍ͬͯ·͢ • twitter:

    @sgwr_dts / github: winebarrel • ࡞ͬͨ΋ͷ • https:/ /codenize.tools/ • https:/ /rubygems.org/profiles/winebarrel
  2. 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 ↓ ΧϥϜ͕௥Ճ͞ΕΔ
  3. DDLʹΑΔॻ͖ࠐΈͷϩοΫ ݸਓతײ֮ • େৎ෉ • 1ສ݅ҎԼ / 1GBҎԼ • ·͍͔ͣ΋…

    • 10ສ݅ / 5GB͘Β͍ • ͪΐͬͱ଴ͬͯʂ • 100ສ݅ / 10GBҎ্
  4. pt-online-schema-change https:/ /www.percona.com/doc/percona-toolkit/2.1/pt-online- schema-change.html • ௨শpt-osc • Percona Toolkitͷͻͱͭ •

    ॻ͖ࠐΈΛϩοΫ͠ͳ͍ͰDDLΛ࣮ߦͰ͖Δ • ϨϓϦέʔγϣϯͷ؂ࢹɾϓϥάΠϯػߏɾෛՙ؂ࢹͳͲ ศརػೳଟ਺
  5. 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
  6. 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
  7. ׬

  8. 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
  9. 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
  10. 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'
  11. 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
  12. 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) ===================================================
  13. 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ͷόʔδϣϯʹ͸ґଘ͍ͯ͠ͳ͍
  14. 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 # (ଓ͘)
  15. 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
  16. 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 ʣ
  17. ͓·͚: ͭͬͯ͘Έͨ 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
  18. ͓·͚: ͭͬͯ͘Έͨ 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
  19. ͓·͚: ͭͬͯ͘Έͨ $ 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. (ଓ͘)
  20. ͓·͚: ͭͬͯ͘Έͨ (ଓ͖) 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) =====================================