天天看點
傳回

pt-online-schema-change 使用解析1 原理介紹2 環境準備3 文法說明4 使用舉例5 pt-osc還是online DDL？

 本文簡單介紹 pt-online-schema-change 工具。

1 原理介紹

    表格必須帶有主鍵或者唯一索引！！

    假設現有tbosc需要做ALTER操作，使用pt-online-schema-change的時候，根據tbddl表結構及索引情況，建立一個新的空表_tbosc_new，然後從原始表格 tbosc 中拷貝資料到新的表格 _tbosc_new，copy data結束後，使用_tbosc_new替換tbddl，同時，删除舊表。

     簡單流程如上描述，那麼詳細流程是怎麼樣的呢？

     帶着這幾個問題來了解：

  • ALTER操作期間，表格是否支援DML？
  • 如果支援DML，是如何把DML同步到新的臨時表上？
  • 整個操作流程鎖情況是怎麼樣的？
  • 執行期間有什麼性能影響？
  • 該工具有什麼限制情況？

1.1 詳細執行流程

    如何檢視其詳細的執行流程呢？資料庫開啟general log，然後執行pt-online-schema-change，它對資料庫的所有操作，就都呈現在眼前。

     詳細執行流程如下：

  1. 相關環境參數檢查
  2. 檢查該表格是否存在
  3. show create table tbosc
  4. create table _tbosc_new
  5. alter table _tbosc_new 
  6. 建立删除觸發器 pt_osc_dbddl_tbosc_del (如果資料修改的時候，還沒有拷貝過來，修改後再拷貝則是覆寫，正确；如果是已經拷貝過來，再修改，也是正确，這裡同時會檢查是否具有主鍵或者唯一索引，如果都沒有，這一步會報錯，提示The new table `dbosc`.`_tbosc_new` does not have a PRIMARY KEY or a unique index which is required for the DELETE trigger.）
  7. 建立更新觸發器 pt_osc_dbddl_tbosc_upd
  8. 建立插入觸發器 pt_osc_dbddl_tbosc_ins
  9. 按塊拷貝資料到新表，拷貝過程對資料行持有S鎖
  10. analyze 新表
  11. rename 表名，RENAME TABLE `dbddl`.`tbosc` TO `dbddl`.`_tbosc_old`, `dbddl`.`_tbosc_new` TO `dbddl`.`tbosc`
  12. 删除舊表
  13. 删除新表上的删除、更新、插入 觸發器

1.2 問題解答

   根據其執行流程，可以對一開始的提問一 一來解答。

  • ALTER操作期間，表格是否支援DML？
    • ALTER過程采用Copy Table To New Table的方式，建立一個表格，然後在原表上建立3個觸發器：DELETE\UPDATE\INSERT觸發器，一旦新表，拷貝資料到新表的過程中，如果原表資料發生變化，則會通過觸發器更新到新表上。
    • 如果支援DML，是如何把DML同步到新的臨時表上？
    • ALTER過程采用Copy Table To New Table的方式，建立一個表格，然後在原表上建立3個觸發器：DELETE\UPDATE\INSERT觸發器，一旦新表，拷貝資料到新表的過程中，如果原表資料發生變化，則會通過觸發器更新到新表上。
    • INSERT原表的時候，觸發器根據其主鍵ID把新紀錄INSERT到新表上；
    • UPDATE原表的時候，觸發器根據其主鍵ID判斷新舊ID是否一緻，如果一緻則删除，然後在REPLACE INTO新紀錄到新表
    • DELETE原表的時候，觸發器根據其主鍵ID直接删除行記錄
    • 如果資料修改的時候，還沒有拷貝到新表，修改後再拷貝，雖然重複覆寫，但是資料也沒有出錯；如果是資料已經拷貝，原表發生修改，這時觸發器同步修改資料，兩種情況下都保證了資料的一緻性；
  • 整個操作流程鎖情況是怎麼樣的？
    • 建立新表後，按照每一個chunk的大小拷貝資料到新表，每次SELECT都是share mode，帶S鎖，但是每個chunk都比較小，是以鎖時間不大
    • 最後資料拷貝結束，會有一個rename操作，這個操作過程中，是不支援DML操作的，但其速度很快，不會造成長時間鎖表情況
    • 該工具會設定該DDL操作的鎖等待逾時為1s，當出現異常的時候，會是ALTER操作異常，而不是其他業務操作異常，這樣可以最大程度的不影響其他事務的進行
  • 執行期間有什麼性能影響？
    • 總體而言，對資料庫的鎖影響降低到了最小，執行期間允許DML操作
    • 但是注意，任何DDL SQL在這裡，都是轉換成copy table to new table的形式，這個過程中，會極大占用磁盤的IO跟CPU資源，同時跟住從延時帶來一定的影響，還是那句老話，重複了解DDL的影響程度後，再選擇合适時機執行。
    • copy data過程中，如果主從延遲異常超過 max-lag則停止copy data，等待主從延遲恢複，預設為1min，可以通過--max-lag設定
    • 檢測到伺服器負載異常，也會停止操作，可以通過 --max-load，--critical-load設定
  • 該工具有什麼限制情況？
    • 表格必須帶有主鍵或者唯一索引
    • 存在複制過濾掉表格，ALTER操作
    • copy data過程中，如果主從延遲異常超過 max-lag則停止copy data，等待主從延遲恢複，預設為1s，可以通過--max-lag設定
    • 檢測到伺服器負載異常，也會停止操作，可以通過 --max-load，--critical-load設定
    • 設定操作的鎖等待逾時為1s，當出現異常的時候，ALTER操作異常，而不是其他業務操作異常，這樣可以最大程度的不影響其他事務的進行
    • 預設情況下，存在 被外鍵引用的表格是不支援ALTER操作的，除非手動指定參數--alter-foreign-keys-method
    • 不支援修改 Percona XtraDB Cluster （PXC）上節點的 myisam表格

2 環境準備

    工具包下載下傳網頁：https://www.percona.com/downloads/percona-toolkit/LATEST/  （目前最新版本為3.0.2）

   rpm下載下傳位址：https://www.percona.com/downloads/percona-toolkit/3.0.2/binary/redhat/7/x86_64/percona-toolkit-3.0.2-1.el7.x86_64.rpm

   官方使用說明文檔位址： https://www.percona.com/doc/percona-toolkit/LATEST/index.html

   環境安裝依賴項目： Perl, DBI, DBD::mysql

   安裝非常簡單，在安裝好相關的環境依賴項後，執行rpm安裝即可

   rpm -ivh percona-toolkit-3.0.2-1.el7.x86_64.rpm

3 文法說明

3.1 主要選項

  • --alter
    • 指定ALTER 語句，正常的ALTER TABLE TBNAME [ ADD | MODIFY | DROP | ALTER ] COLUMN COLUMN_NAME ...，去除前面的ALTER TABLE TABLE那麼，直接指定後部分的内容
    • 注意事項
      • rename不支援，請直接使用RENAME TABLE tablename TO new_tablename;
      • 如果表格有資料，建立非空無預設值的列，會失敗，如果非空，需要指定預設值；
      • 如果表格有資料，為一個可空的列添加預設值時，舊資料為NULL的是不會被修改，依舊為NULL，以後新加入到資料則會預設為設定的預設值
      • 對于外鍵的删除情況，由于執行是在新表上執行DDL，是以其外鍵值的命名跟原表的命名不一樣，假設删除原表的外鍵名是 fk_foo，那麼新表的外鍵名就為 _fk_foo，是以删除的ALTER語句是： drop foreign key _fk_foo;
  • --alter-foreign-keys-method
    • 如果修改的表格，是其他表格外鍵reference的表格，那麼，最後rename的過程，需要確定一定成功，要不然這些子表就沒能成功reference到其指定的表格名，對子表的操作将會報錯。比如 tba有一個外鍵 fk_tba引用表格 tbb，這個時候tbb需要做DDL操作，根據pt工具的原理得知，最後會有一個rename環節，這個環節可能會導緻限制失效或者執行堵塞等問題。
    • 是以，針對最後rename的這個環節，該工具提供了4中處理方法：
      • auto
        • 自動選擇 rebuild_constraints 或者 drop_swap，優先選擇rebuild_constraints
        • rebuild_constraints
        • rename table 前，先删除子表的外鍵限制，然後重建外鍵限制指向到新表（ALTER TABLE語句添加），最後執行rename操作
        • 這個rename操作即使不成功，它也rename到新表，不會出現reference的表格不存在情況
        • 弊端：如果子表過大，添加外鍵限制的過程中，可能會對子表造成堵塞
      • drop_swap
        • 執行rename之前禁用外鍵檢查，然後删除原表，rename新表為原表名
        • 這個過程非常快并且沒有堵塞
        • 這個方法需要強制指定 --no-swap-tables 跟 --no-drop-old-table.
        • 弊端：當把原表删除而新表還沒rename為原表的名字時，這段時間實際非常短，但是這段時間内，等于原表名的表格時不存在的，子表做一些DML的時候，可能會出現錯誤。rename期間，如果新表rename原表失敗，但是已經删除原表，那麼這段期間，其子表的操作将會出現大面積問題，直到人工修複
      • none
        • 類似drop_swap操作，不同在于對原表的處理。
        • 按正常的pt工具流程，禁用外鍵限制，rename原表為臨時表，rename新表為原表名，删除臨時表
        • 弊端: 當把原表rename為臨時表，而新表還沒rename為原表的名字時，這段時間實際非常短，但是這段時間内，等于原表名的表格時不存在的，子表做一些DML的時候，可能會出現錯誤
      • --drop-old-table
        • 操作成功後，原表是否保留，預設是删除，
        • default：yes，可選：--no-drop-old-table
  • --dry-run
    • 僅建立新表格，但是不執行觸發器、拷貝資料跟替換原表
  • --execute
    • 确定執行ALTER操作，注意，這個操作如果不指定，則僅做安全檢查然後推出。
    • 充分了解工具的使用情況後，才執行，不要啥都不了解測試就線上執行，會挖坑的...
  • --host
    • 連接配接主機名
  • --max-lag
    • 預設1s
    • 檢查從庫延遲的時間，如果超過，則停止copy data，休息--check-interval秒後，再重新開始copy資料
    • 檢視通過延遲時間，是通過從庫show slave status，檢視Seconds_Behind_Master
    • 如果指定--check-slave-lag，該工具隻檢查該伺服器的延遲，而不是所有伺服器。
  • --check-interval
    • 從庫延遲超過指定的--max-lag，中斷copy data休息的時間
    • 預設為1s
  • --max-load
    • copy data的過程，監控資料庫目前正在運作的thread，如果超過指定的Threads_running值，則停止拷貝資料，會在輸出的内容中答應 Pausing because Threads_runing=15，直到運作的線程數小于給定的值，恢複copy data，如此循環，知道拷貝資料結束。
    • Threads_runing預設為25
    • 舉例：--max-load=Threads_running=15
  • --password
    • 資料庫使用者名的密碼
  • --port
    • 資料庫端口号
  • --socket
    • 資料庫socket檔案
  • --user
    • 資料庫使用者名
  • --recursion-method
    • MASTER尋找SLAVE的方式（這個選項基本在涉及主從問題的pt工具中廣泛應用到）
    • 有4個選項
      • pt-online-schema-change 使用解析1 原理介紹2 環境準備3 文法說明4 使用舉例5 pt-osc還是online DDL？
    • processlist，使用show processlist查找從庫
    • hosts，如果不是使用預設端口号3306，那麼使用hosts方式來查找從庫會更可靠
    • dsn，使用表格 tdsn存儲從庫資訊（DSN的具體參數選項可以詳細檢視 3.3 DSN選線）
      • 需要手動在需要DDL的資料庫内，建立 dsns 表格
      • CREATE TABLE `dsns` (`id` int(11) NOT NULL AUTO_INCREMENT,`parent_id` int(11) DEFAULT NULL,`dsn` varchar(255) NOT NULL, PRIMARY KEY (`id`));
      • 存儲從庫資訊
      • insert into dsns(dsn) values(h=slave_host,u=repl_user,p=repl_password,P=port );
      • 該參數使用的時候，按以下格式（假設 dsns表格建立在資料庫 dbosc）
      • --recursion-method dsn=D=dbosc,t=dsns
    • none，不查找從庫

3.2 輸出 

  • --statistics 增加影響行數列印，可以檢視copy進度
  • --print 詳細列印alter過程，不指定的時候，簡略列印

3.3 DSN選項

  • A
    • 字元集設定
  • D
    • 需要Alter的表格是在哪個資料庫，指定資料庫
  • F
    • mysql read default file，如果資料源的相關選項存儲在檔案中，則通過 F 來指定
  • h
    • host，資料庫主機名或IP
  • p
    • password，資料庫使用者的密碼
  • P
    • port，資料庫執行個體端口号
  • S
    • socket，執行個體socket檔案
  • t
    • 表格名
  • u
    • 使用者名

4 使用舉例

4.1 正常DDL

    正常情況，如果有被外鍵引用的表格，注意對--alter-foreign-keys-method的設定，本小節不考慮從庫延遲情況及外鍵情況。

    pt-online-schema-change也支援同個表格的多個SQL合并為一個SQL，由于所有DDL都是采用COPY TABLE TO NEW TABLE方式，是以使用的時候，不需要對DDL SQL做分類。

1 CREATE TABLE `tbosc` (
 2   `id` int(11) NOT NULL AUTO_INCREMENT,
 3   `name` varchar(100) DEFAULT NULL,
 4   `age` int(11) DEFAULT NULL,
 5   PRIMARY KEY (`id`)
 6 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
 7  
 8 #添加列
 9 pt-online-schema-change --socket=/tmp/mysql3310.sock --user=root --password=ycf.com  D=dbosc,t=tbosc --alter "add column stunum int "  --recursion-method=none --no-check-replication-filters --alter-foreign-keys-method auto --print --execute
10  
11 #删除列
12 pt-online-schema-change --socket=/tmp/mysql3310.sock --user=root --password=ycf.com  D=dbosc,t=tbosc --alter "drop column stunum "  --recursion-method=none --no-check-replication-filters --alter-foreign-keys-method auto --print --execute
13  
14 #修改列資料類型
15 pt-online-schema-change --socket=/tmp/mysql3310.sock --user=root --password=ycf.com  D=dbosc,t=tbosc --alter "modify column age varchar(10)"  --recursion-method=none --no-check-replication-filters --alter-foreign-keys-method auto --print --execute
16  
17 #加大列長度
18 pt-online-schema-change --socket=/tmp/mysql3310.sock --user=root --password=ycf.com  D=dbosc,t=tbosc --alter "modify column age varchar(100)"  --recursion-method=none --no-check-replication-filters --alter-foreign-keys-method auto --print --execute
19  
20 #建立索引
21 pt-online-schema-change --socket=/tmp/mysql3310.sock --user=root --password=ycf.com  D=dbosc,t=tbosc --alter "ADD INDEX IX_AGE(AGE)"  --recursion-method=none --no-check-replication-filters --alter-foreign-keys-method auto --print --execute
22  
23 #删除索引
24 pt-online-schema-change --socket=/tmp/mysql3310.sock --user=root --password=ycf.com  D=dbosc,t=tbosc --alter "DROP INDEX IX_AGE"  --recursion-method=none --no-check-replication-filters --alter-foreign-keys-method auto --print --execute
25  
26 #設定預設值
27 pt-online-schema-change --socket=/tmp/mysql3310.sock --user=root --password=ycf.com  D=dbosc,t=tbosc --alter "ALTER column age SET DEFAULT 100"  --recursion-method=none --no-check-replication-filters --alter-foreign-keys-method auto --print --execute
28  
29 #能否多個合成一個
30 pt-online-schema-change --socket=/tmp/mysql3310.sock --user=root --password=ycf.com  D=dbosc,t=tbosc --alter "ADD COLUMN onecol int ,add column twocol varchar(100),add index ix_onecol(onecol),alter column name set default ‘xinysu‘ "  --recursion-method=none --no-check-replication-filters --alter-foreign-keys-method auto --print --execute

4.2 考慮從庫延遲情況

    考慮從庫延遲情況 ，意味這要注意這幾個選項的設定

  • --max-lag
  • --check-interval
  • --recursion-method
  • --check-slave-lag

    從庫延遲超過max-lag則停止copy data，等待 check-interval 秒後再開始copy data。check-slave-lag指定slave的機器，隻會對比這台slave的延遲情況。recursion-method是主庫尋找從庫的方法，有四個方法：processlist，hosts，dsn，none，具體檢視上部分選項詳細說明，本節較長的描述dsn及check-slave-lag的使用。

     假設需要在dbosc庫上的表格tbddl添加一列：hobby varchar(100) ，需要考慮從庫的延遲情況

#建立表格dsns，記錄從庫資訊

CREATE TABLE `dsns` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `parent_id` int(11) DEFAULT NULL,

  `dsn` varchar(255) NOT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8

#insert從庫資訊，有2個從庫，分别為242伺服器上的 3310跟3320

insert into dsns(dsn) select "h=192.168.9.244,u=repl,p=****,P=3310";

insert into dsns(dsn) select "h=192.168.9.244,u=repl,p=****,P=3320";

    如果需要考慮多個從庫的延遲情況，則可以考慮使用 dsns表格來記錄從庫資訊，如果隻需要考慮某一台從庫的延遲情況，則既可以使用dsns表格也可以使用參數--check-slave-lag指定從庫。

    不考慮外鍵關系，考慮從庫影響程度，檢查到從庫延遲超過1s，則休息5s，具體指令如下

pt-online-schema-change -P3310 --user=root --password=ycf.com  D=dbosc,t=tbddl --max-lag=1s --check-interval=10s --alter "ADD hobby varchar(100) NOT NULL DEFAULT ‘sleep‘ "  --recursion-method dsn=D=dbosc,t=dsns  --alter-foreign-keys-method auto --execute

     如果檢測到slave的 Seconds_Behind_Master超過1s，則會休息10s後再監測，這個過程會在輸出檔案中列印出：

    Replica lag is 395 seconds on sutest244.  Waiting.

   Replica lag is 425 seconds on sutest244.  Waiting.

   Replica lag is 456 seconds on sutest244.  Waiting.

    說明現在主從延時了多少秒，現在copy線程停止，正在等待中。

     如果是僅指定一個從庫檢視延遲情況，使用--check-slave-lag的指令如下：

pt-online-schema-change -P3310 --user=root --password=ycf.com  D=dbosc,t=tbddl --max-lag=1 --check-interval=10 --check-slave-lag=h=192.168.9.244,u=root,p=ycf.com,P=3310  --alter "ADD hobby varchar(100) NOT NULL DEFAULT ‘sleep‘ "  --recursion-method  --alter-foreign-keys-method auto --print --execute

5 pt-osc還是online DDL？

    經過這一篇介紹pt-online-schema-change的原理說明及測試，以及上篇的online ddl說明，可明白pt-osc無論是什麼DDL SQL，都會建立新表來替換，不分DDL類型，但是執行期間允許DDL操作，而ONLINE DDL則分為了好幾類DDL，有的DDL僅需修改中繼資料，有的DDL僅需在本身ibd檔案上建立索引頁，有的需要rebuild table，這三種類型執行期間支援DML操作，但是COPY TABLE 類型不支援DML操作。

是以，可以有以下幾個判斷：

  • 如果MySQL版本是5.6之前，不支援online ddl操作的，pt-online-schema-change是一個非常好的選擇；
  • 如果MySQL的版本是5.6以上的，支援online-ddl的，優先考慮使用online ddl，但是如果是ddl SQL 在online DDL中 需要copy table to tmp table，則建議使用pt-online-schema-change來處理，比如修改列資料類型的DDL，online DDL則是需要copy table to tmp table，期間僅支援查詢，不支援DML操作，這個時候，就可以使用pt-online-schema-change來處理，因為它也是拷貝臨時表格，并且執行期間支援DML操作；
  • 如果執行Online DDL，但是對從庫的延遲非常敏感，針對需要copy table 跟rebuild table這兩類DDL SQL，需要考慮是否可以在從庫設定并行複制，如果不行，則優先選擇pt-online-schema-change。
mysql pt-online-schema-change
上一篇: Box2D v2.3.0 使用者指南（第十章）
下一篇: java jdbc連接配接mysql 查詢資料查找不到

繼續閱讀