天天看點
傳回

pt-osc原理、限制、及與原生online-ddl比較

1. pt-osc工作過程

  1. 建立一個和要執行 alter 操作的表一樣的新的空表結構(是alter之前的結構)
  2. 在新表執行alter table 語句（速度應該很快）
  3. 在原表中建立觸發器3個觸發器分别對應insert,update,delete操作
  4. 以一定塊大小從原表拷貝資料到臨時表，拷貝過程中通過原表上的觸發器在原表進行的寫操作都會更新到建立的臨時表
  5. Rename 原表到old表中，在把臨時表Rename為原表
  6. 如果有參考該表的外鍵，根據alter-foreign-keys-method參數的值，檢測外鍵相關的表，做相應設定的處理
  7. 預設最後将舊原表删除

2. 常用選項說明

隻介紹部分常用的選項

  • --host=xxx --user=xxx --password=xxx

     連接配接執行個體資訊，縮寫

    -h xxx -u xxx -p xxx

     ，密碼可以使用參數

    --ask-pass

      手動輸入。

  • --alter

     結構變更語句，不需要 

    ALTER TABLE

     關鍵字。與原始ddl一樣可以指定多個更改，用逗号分隔。
    • 絕大部分情況下表上需要有主鍵或唯一索引，因為工具在運作當中為了保證新表也是最新的，需要舊表上建立 DELETE和UPDATE 觸發器，同步到新表的時候有主鍵會更快。個别情況是，當alter操作就是在c1列上建立主鍵時，DELETE觸發器将基于c1列。
    • 子句不支援 rename 去給表重命名。

    • alter指令原表就不支援給索引重命名，需要先drop再add，在pt-osc也一樣。(mysql 5.7 支援 RENAME INDEX old_index_name TO new_index_name)

      但給字段重命名，千萬不要drop-add，整列資料會丢失，使用

      change col1 col1_new type constraint

       （保持類型和限制一緻，否則相當于修改 column type，不能online）
    • 子句如果是add column并且定義了not null，那麼必須指定default值，否則會失敗。
    • 如果要删除外鍵（名 fk_foo），使用工具的時候外鍵名要加下劃線，比如

      --alter "DROP FOREIGN KEY _fk_foo"

    • D=db_name,t=table_name

       指定要ddl的資料庫名和表名

    • --max-load

       預設為

      Threads_running=25

       。每個chunk拷貝完後，會檢查 SHOW GLOBAL STATUS 的内容，檢查名額是否超過了指定的門檻值。如果超過，則先暫停。這裡可以用逗号分隔，指定多個條件，每個條件格式： 

      status名額=MAX_VALUE

       或者

      status名額:MAX_VALUE

      。如果不指定MAX_VALUE，那麼工具會這隻其為目前值的120%。

      因為拷貝行有可能會給部分行上鎖，Threads_running 是判斷目前資料庫負載的絕佳名額。

    • --max-lag

       預設1s。每個chunk拷貝完成後，會檢視所有複制Slave的延遲情況（

      Seconds_Behind_Master

       ）。要是延遲大于該值，則暫停複制資料，直到所有從的滞後小于這個值。

      --check-interval

       配合使用，指定出現從庫滞後超過 max-lag，則該工具将睡眠多長時間，預設1s，再檢查。如

      --max-lag=5 --check-interval=2

      。

      熟悉percona-toolkit的人都知道

      --recursion-method

       可以用來指定從庫dsn記錄。另外，如果從庫被停止，将會永遠等待，直到從開始同步，并且延遲小于該值。

    • --chunk-time

      預設0.5s，即拷貝資料行的時候，為了盡量保證0.5s内拷完一個chunk，動态調整chunk-size的大小，以适應伺服器性能的變化。

      也可以通過另外一個選項

      --chunk-size

       禁止動态調整，即每次固定拷貝 1k 行，如果指定則預設1000行，且比 chunk-time 優先生效

    • --set-vars

       使用pt-osc進行ddl要開一個session去操作，

      set-vars

       可以在執行alter之前設定這些變量，比如預設會設定

      --set-vars "wait_timeout=10000,innodb_lock_wait_timeout=1,lock_wait_timeout=60"

       因為使用pt-osc之後ddl的速度會變慢，是以預計2.5h隻能還不能改完，記得加大

      wait_timeout

    • --dry-run

       建立和修改新表，但不會建立觸發器、複制資料、和替換原表。并不真正執行，可以看到生成的執行語句，了解其執行步驟與細節，和

      --print

       配合最佳。。

    • --execute

       确定修改表，則指定該參數。真正執行alter。–dry-run與–execute必須指定一個，二者互相排斥

3. 使用疑惑（限制）

3.1 原表上不能有觸發器存在

這個很容易了解，pt-osc會在原表上建立3個觸發器，而一個表上不能同時有2個相同類型的觸發器，為簡單通用起見，隻能一棍子打死。

是以如果要讓它支援有觸發器存在的表也是可以實作的，思路就是：先找到原表觸發器定義；重寫原表觸發器；最後階段将原表觸發器定義應用到新表。

3.2 通過觸發器寫資料到臨時新表，會不會出現資料不一緻或異常

這其實是我的一個顧慮，因為如果update t1，觸發update t2，但這條資料還沒copy到t2，不就有異常了嗎？背景通過打開general_log，看到它建立的觸發器：

1
2
3
4
5
6
7
8
9
10
11
12
      
6165 Query     CREATE TRIGGER `pt_osc_confluence_sbtest3_del` AFTER DELETE ON `confluence`.`sbtest3` 
        FOR EACH ROW DELETE IGNORE FROM `confluence`.`_sbtest3_new` WHERE `confluence`.`_sbtest3_new`.`id` <=> OLD.`id`
    6165 Query     CREATE TRIGGER `pt_osc_confluence_sbtest3_upd` AFTER UPDATE ON `confluence`.`sbtest3` 
        FOR EACH ROW REPLACE INTO `confluence`.`_sbtest3_new` (`id`, `k`, `c`, `pad`) VALUES (NEW.`id`, NEW.`k`, NEW.`c`, NEW.`pad`)
    6165 Query     CREATE TRIGGER `pt_osc_confluence_sbtest3_ins` AFTER INSERT ON `confluence`.`sbtest3` 
        FOR EACH ROW REPLACE INTO `confluence`.`_sbtest3_new` (`id`, `k`, `c`, `pad`) VALUES (NEW.`id`, NEW.`k`, NEW.`c`, NEW.`pad`)

并且copy操作是：

    6165 Query     INSERT LOW_PRIORITY IGNORE INTO `confluence`.`_sbtest3_new` (`id`, `k`, `c`, `pad`) 
         SELECT `id`, `k`, `c`, `pad` FROM `confluence`.`sbtest3` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '4692805')) AND ((`id` <= '4718680')) 
        LOCK IN SHARE MODE /*pt-online-schema-change 46459 copy nibble*/

在原表上update，新臨時表上是replace into整行資料，是以達到有則更新，無則插入。同時配合後面的 insert ignore，保證這條資料不會因為重複而失敗。

3.3 為什麼外鍵那麼特殊

假設 t1 是要修改的表，t2 有外鍵依賴于 t1，_t1_new 是 alter t1 産生的新臨時表。

這裡的外鍵不是看t1上是否存在外鍵，而是作為子表的 t2。主要問題在 rename t1 時，t1“不存在”導緻t2的外鍵認為參考失敗，不允許rename。

pt-osc提供

--alter-foreign-keys-method

選項來決定怎麼處理這種情況：

  • rebuild_constraints

     ，優先采用這種方式
    • 它先通過 alter table t2 drop fk1,add _fk1 重建外鍵參考，指向新表
    • 再 rename t1 t1_old, _t1_new t1 ，交換表名，不影響用戶端

    • 删除舊表 t1_old

      但如果字表t2太大，以緻alter操作可能耗時過長，有可能會強制選擇 drop_swap。

      涉及的主要方法在 

      pt-online-schema-change

        檔案的 determine_alter_fk_method, rebuild_constraints, swap_tables三個函數中。

  • drop_swap

     ，
    • 禁用t2表外鍵限制檢查 

      FOREIGN_KEY_CHECKS=0
    • 然後 drop t1 原表

    • 再 rename _t1_new t1

      這種方式速度更快，也不會阻塞請求。但有風險，第一，drop表的瞬間到rename過程，原表t1是不存在的，遇到請求會報錯；第二，如果因為bug或某種原因，舊表已删，新表rename失敗，那就太晚了，但這種情況很少見。

      我們的開發規範決定，即使表間存在外鍵參考關系，也不通過表定義強制限制。

3.4 在使用之前需要對磁盤容量進行評估

使用OSC會使增加一倍的空間，包括索引

而且在 Row Based Replication 下，還會寫一份binlog。不要想當然使用

--set-vars

去設定 sql_log_bin=0，因為在這個session級别，alter語句也要在從庫上執行，除非你對從庫另有打算。

4. 使用 pt-osc原生 5.6 online ddl相比，如何選擇

  • online ddl在必須copy table時成本較高，不宜采用
  • pt-osc工具在存在觸發器時，不适用
  • 修改索引、外鍵、列名時，優先采用online ddl，并指定 ALGORITHM=INPLACE
  • 其它情況使用pt-osc，雖然存在copy data
  • pt-osc比online ddl要慢一倍左右，因為它是根據負載調整的
  • 無論哪種方式都選擇的業務低峰期執行
  • 特殊情況需要利用主從特性，先alter從庫，主備切換，再改原主庫

5.使用pt-osc修改主鍵時注意

使用 pt-online-schema-change 做線上ddl最添加普通索引、列，修改列類型、添加預設值等使用比較正常，但涉及到要修改的是主鍵時就有點棘手。在我修改線上執行個體過程中，有這樣的需求，不妨先思考一下怎麼做才好：

1
      
原表上有個複合主鍵，現在要添加一個自增id作為主鍵，如何進行

會涉及到以下修改動作：

  1. 删除複合主鍵定義
  2. 添加新的自增主鍵
  3. 原複合主鍵字段，修改成唯一索引

如果你夠聰明，應該會把這三個操作放在同一個 alter table 指令執行。percona手冊裡有兩個地方對修改主鍵進行了特殊注解：

–alter

A notable exception is when a PRIMARY KEY or UNIQUE INDEX is being created from existing columns as part of the ALTER clause; in that case it will use these column(s) for the DELETE trigger.

–[no]check-alter

  • DROP PRIMARY KEY

    If –alter contain DROP PRIMARY KEY (case- and space-insensitive), a warning is printed and the tool exits unless –dry-run is specified. Altering the primary key can be dangerous, but the tool can handle it. The tool’s triggers, particularly the DELETE trigger, are most affected by altering the primary key because the tool prefers to use the primary key for its triggers. You should first run the tool with –dry-run and –print and verify that the triggers are correct.

由上一篇文章 pt-online-schema-change使用說明、限制與比較 可知，pt-osc會在原表t1上建立 AFTER DELETE/UPDATE/INSERT 三個觸發器，修改主鍵影響最大的就是 DELETE 觸發器：新表t2上的主鍵字段在舊表t1上不存在，無法根據主鍵條件觸發删除新表t2資料。

but the tool can handle it

，原因是pt-osc把觸發器改成了下面的形式：

1
2
3
4
      
CREATE TRIGGER `pt_osc_confluence_sbtest3_del` AFTER DELETE ON `confluence`.`sbtest3` FOR EACH ROW DELETE IGNORE FROM `confluence`.`_sbtest3_new` 
WHERE `confluence`.`_sbtest3_new`.`id` <=> OLD.`id` AND `confluence`.`_sbtest3_new`.`k` <=> OLD.`k`

注：sbtest3表上以(id,k)作為複合主鍵

但是如果id或k列上沒有索引，這個删除的代價非常高，是以一定要同時添加複合（唯一）索引 

(id,k)

 .

而對于INSERT,UPDATE的觸發器，依然是 

REPLACE INTO

文法，因為它采用的是先插入，如果違反主鍵或唯一限制，則根據主鍵或意義限制删除這條資料，再執行插入。（但是注意你不能依賴于新表的主鍵遞增，因為如果原表有update，新表就會先插入這一條，導緻id與原表記錄所在順序不一樣）

是以如果使用pt-osc去修改删除主鍵，務必同時添加原主鍵為 UNIQUE KEY，否則很有可能導緻性能問題：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
      
$ pt-online-schema-change --user=ecuser --password=ecuser --host=10.0.201.34  \
--alter "DROP PRIMARY KEY,add column pk int auto_increment primary key,add unique key uk_id_k(id,k)" \
D=confluence,t=sbtest3 --print --dry-run

--alter contains 'DROP PRIMARY KEY'.  Dropping and altering the primary key can be dangerous, 
especially if the original table does not have other unique indexes.  ==>注意 dry-run的輸出

ALTER TABLE `confluence`.`_sbtest3_new` DROP PRIMARY KEY,add column pk int auto_increment primary key,add unique key uk_id_k(id,k)
Altered `confluence`.`_sbtest3_new` OK.
Using original table index PRIMARY for the DELETE trigger instead of new table index PRIMARY because ==> 使用原表主鍵值判斷
the new table index uses column pk which does not exist in the original table.

CREATE TRIGGER `pt_osc_confluence_sbtest3_del` AFTER DELETE ON `confluence`.`sbtest3` FOR EACH ROW DELETE IGNORE FROM `confluence`.`_sbtest3_new` 
WHERE `confluence`.`_sbtest3_new`.`id` <=> OLD.`id` AND `confluence`.`_sbtest3_new`.`k` <=> OLD.`k`
mysql pt pt-osc
上一篇: MySQL索引背後的資料結構及算法原理(employees執行個體)
下一篇: MySQL自帶的性能壓力測試工具mysqlslap

繼續閱讀