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

COSCUP 2018 - How to alter a large table

COSCUP 2018 - How to alter a large table

Web page of talk: https://2018.coscup.org/programs/large-table/

COSCUP ( Conference for Open Source Coders, Users and Promoters) is an annual conference since 2006. It's a major force of Free software movement advocacy in Taiwan.

Luka Huang

August 11, 2018
Tweet

More Decks by Luka Huang

Other Decks in Programming

Transcript

  1. Overview • What is altering a table? • If rows

    > 10 millions, how to alter a table? • If we have read replicas, how to alter a table? • How to use an open source solution to alter a table?
  2. Overview • What is altering a table? • If rows

    > 10 millions, how to alter a table? • If we have read replicas, how to alter a table? • How to use an open source solution to alter a table?
  3. class AlterUsersTable < ActiveRecord::Migration def up add_column :users, :group_id, :integer

    add_index :users, [:group_id] rename_column :users, :email, :mail end def down remove_column :users, :group_id remove_index :users, [:group_id] rename_column :users, :mail, :email end end Example of Rails Migration
  4. 執行 rake db:migrate 會產生這樣的 SQL 指令 ALTER TABLE `users` ADD

    `group_id` int(11) `updated_at` datetime DEFAULT NULL CREATE INDEX `index_users_on_group_id` ON `users` (`group_id`) ALTER TABLE `users`
  5. Outlines • If rows > 10 millions, how to alter

    a table? • If we have multiple read replicas, how to alter a table? • How to use open source solution to alter a large table? • How to use LHM without Ruby on Rails?
  6. 剛剛的例子 ALTER TABLE `users` ADD `group_id` int(11) `updated_at` datetime DEFAULT

    NULL CREATE INDEX `index_users_on_group_id` ON `users` (`group_id`) ALTER TABLE `users`
  7. 直接使用 ALTER TABLE 會有什麼問題? • 在 ALTER TABLE 的過程中,寫操作持續地被鎖 住。

    • 如果是一個寫入頻繁的系統(write-heavy system),會有一大堆的寫操作在等待被鎖住的 row 釋放它的鎖。 • 這樣的狀況很可能造成資料庫的 CPU 使用率和 記憶體使用率飆高,甚至當掉都有可能。
  8. What is LHM? • SoundCloud’s open source project • Since

    2012 (v1.0.0) • LHM is still working today. • Shopify maintain a fork here
  9. Example of LHM migration require 'lhm' class MigrateUsers < ActiveRecord::Migration

    def self.up Lhm.change_table :users do |m| m.add_column :group_id, "INT(12)" m.add_index [:group_id] m.rename_column(:email, :mail) end end def self.down Lhm.change_table :users do |m| m.remove_index [:group_id] m.remove_column :group_id m.rename_column(:mail, :email) end end end
  10. class AlterUsersTable < ActiveRecord::Migration def up add_column :users, :group_id, :integer

    add_index :users, [:group_id] rename_column :users, :email, :mail end def down remove_column :users, :group_id remove_index :users, [:group_id] rename_column :users, :mail, :email end end 對照之前未使用LHM的 Migration
  11. 如果你的系統使用了讀寫分離,並且沒 有實際去了解 LHM 的機制,很可能遇到 的問題以下的問題: l 即使 LHM 使用 trigger

    的方式,該 table 執行 寫入的次數仍然會是原來的兩倍。 l 因此在執行 LHM 的同時,slave lag 很有可能 會變長
  12. SlaveLag Throttler 大致上的概念是: l 如果 RDMS 的 SlaveLag 大於你設定的最 高延遲時間

    LHM 同步的速度就會減緩。 l 直到 SlaveLag 降低至可以接受的範圍才 會停止。
  13. 其中最關鍵的一段(2) def throttle_seconds lag = max\_current\_slave_lag if lag > @allowed_lag

    && @timeout_seconds < MAX_TIMEOUT Lhm.logger.info("Increasing timeout between strides from #{@timeout_seconds} to #{@timeout_seconds * 2} because #{lag} seconds of slave lag detected is greater than the maximum of #{@allowed_lag} seconds allowed.") @timeout_seconds = @timeout_seconds * 2 elsif lag <= @allowed_lag && @timeout_seconds > INITIAL_TIMEOUT Lhm.logger.info("Decreasing timeout between strides from #{@timeout_seconds} to #{@timeout_seconds / 2} because #{lag} seconds of slave lag detected is less than or equal to the #{@allowed_lag} seconds allowed.") @timeout_seconds = @timeout_seconds / 2 else @timeout_seconds end end
  14. Improvements by Shopify’s fork • 搬移的筆數預設從 40000 筆降低至 2000 筆。

    • 在 LHM 執行的同時,可以在 Log 看到目前 的搬移狀況與 Read Replica 延遲的狀況 • 持續維護中,2018 的 7 月至今不斷有新的 commit 加入。
  15. 執行 ruby migration_file.rb 即可使用 LHM require 'active_record' require 'lhm' ActiveRecord::Base.establish_connection(

    :adapter => 'mysql', :host => '127.0.0.1', :database => 'lhm' ) # and migrate Lhm.change_table :users do |m| m.add_column :arbitrary, "INT(12)" m.add_column :locale, "VARCHAR(2) NOT NULL DEFAULT 'en'" m.add_index [:arbitrary_id, :created_at] m.ddl("alter table %s add column flag tinyint(1)" % m.name) end