pt-osc原理、限制、及与原生online-ddl比较
1. pt-osc工作過程
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作為主鍵，如何進行 |
?
?
會涉及到以下修改動作：
如果你夠聰明，應該會把這三個操作放在同一個 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` |
?
轉載于:https://www.cnblogs.com/DataArt/p/10174783.html
總結
以上是生活随笔為你收集整理的pt-osc原理、限制、及与原生online-ddl比较的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 求矩阵中各列数字的和 Exerci
- 下一篇： 微信小程序使用阿里图标