天天看點
傳回

MySQL Online DDL的改進與應用

    本文簡析Online DDL的實作原理與使用過程注意事項。

    任何DDL操作，執行者都需要預先測試或者清晰了解這個操作會給資料庫帶來的影響是否是在業務期間資料庫的可承受範圍内，尤其是對大表的DDL操作中，需要密切留意伺服器的IO，記憶體及CPU使用情況（每個DBA總有那麼一段被大表的DDL語句坑到的血淚史）。

    如果轉載，請注明博文來源： www.cnblogs.com/xinysu/   ，版權歸 部落格園 蘇家小蘿蔔 所有。望各位支援！

1 早期DDL實作原理（5.6.7之前 ）

    Innodb早期支援通過copy table跟inplace的方式來執行DDL語句，其原理如下：

  • copy table方式
    • 建立跟原表格一緻的臨時表，并在該臨時表上執行DDL語句
    • 鎖原表，不允許DML，允許查詢
    • 逐行資料從原表拷貝到臨時表中（這個過程是沒有排序的）
    • 拷貝結束後，原表禁止讀操作，也就是原表此時不提供讀寫服務
    • 進行rename操作，完成DDL過程
  • inplace方式（fast index creation，僅針對索引的建立跟删除）
    • 建立frm臨時檔案
    • 按照聚集索引的順序，查詢資料，找到需要的索引列資料，排序後插入到新的索引頁中
    • 原表禁止讀操作，也就是原表此時不提供讀寫服務
    • 進行rename操作，替換frm檔案，完成DDL過程

    inplace在copy table的基礎上做了一個較大的改進，則是不需要copy整個表格，隻需要在原來的ibd檔案上，建立所需要的索引頁，這個過程比copy table節約極大的IO資源占用 且 DDL SQL執行速度大大提高，減少了該表格不提供寫服務的時長。但是inplace僅支援索引的建立于删除，不支援其他的DDL操作，其他的DDL操作，仍然是copy table方式執行。

    對于一個線上業務資料庫，無論是copy table方式還是inplace方式，這裡仍然有一個明顯的弊端：操作期間涉及表格不提供寫服務！無法對涉及到表格至下INSERT,UPDATE,DELETE操作，僅支援SELECT。

2 Online DDL實作原理

    當表格發生DDL操作，可能會出現該表格數分鐘甚至數小時不可通路，性能及響應異常，為了有效改善這個情況，MySQL 在5.6.7版本推出了Online DDL。（本文參考官網5.7版本的文檔整理及測試）。

    在online DDL中，也包含了copy跟inplace兩種方式，對于不支援Online DDL的DDL SQL，則采用COPY方式；對于支援Online DDL的DDL SQL，則采用Inplace方式，這裡的Inplace又區分為2類：是否需要rebuild表格，判斷标準為：是否修改行記錄格式。如果修改了行記錄格式，則需要rebuild表格，比如修改列類型、增減列等；如果沒有修改行記錄格式，僅修改表的中繼資料，則不需要rebuild表格，僅修改中繼資料 metadata，比如删除索引、設定預設值及重命名列名等。詳細可見下圖，具體文法情況見`第4部分`。

MySQL Online DDL的改進與應用

    那麼，新增的Online DDL内部是怎樣一個實作原理呢？（此處參考：http://blog.itpub.net/22664653/viewspace-2056953/）

    有3個階段：prepare、execute、commit。

  • PREPARE
    • 建立新的臨時frm檔案
    • 持有EXCLUSIVE_MDL鎖，禁止讀寫
    • 根據alter類型，确定執行方式（copy，rebuild，no-rebuild）
    • 更新資料字典的記憶體對象
    • 若是需要rebuild，配置設定row_log對象記錄的增量
    • 若是需要rebuild， 生成新的臨時ibd檔案
  • EXECUTE
    • 如果是僅修改中繼資料：
      • 這部分無操作
    • 其他，則是：
      • 降低EXCLUSIVE-MDL鎖，允許讀寫（copy 不允許寫）
      • 記錄ddl執行過程中産生的增量row-log（僅rebuild類型需要）
      • 掃描old_table的聚集索引每一條記錄record
      • 周遊新表的聚集索引和二級索引，逐一處理
      • 根據record構造對應的索引項
      • 将構造索引項插入sort_buffer塊
      • 将sort_buffer塊插入新的索引
      • 把row-log中的操作應用到新臨時表中，應用到最後一個Block
  • COMMIT
    • 更新到EXECLUSIVE-MDL鎖，禁止讀寫
    • 重做最後一部分的row_log增量
    • 更新innodb的資料字典表
    • 送出事務，寫redo日志
    • 修改統計資訊
    • rename 臨時的ibd檔案、frm檔案
    • DDL完成

    這裡注意下row-log，它是記錄 DDL在執行過程中表格發生資料變更的操作，這樣就可以保證執行DDL表格的并發性，在EXCUTE階段可以正常提供寫服務，不發生堵塞，最後把row-log應用到新的表格上即可。

    在5.7.17版本上測試的時候，發現，支援inplace且需要rebuild的DDL，在DDL期間，如果資料發生修改，都是直接重新整理到原來的ibd檔案上，在測試環境中，給大表testddl删除一個字段，這個過程中INSERT 100w行記錄，可以看到原有ibd檔案增長了1G左右。

    這裡有個疑問，未确定：row-log應該不是記錄行記錄的修改格式，因為這樣效率過慢，初步推測應該是僅記錄主鍵，然後根據主鍵查找應用到新表上。

    Online DDL可以有效改善DDL期間對資料庫的影響：

  • Online DDL期間，查詢和DML操作在多數情況下可以正常執行，對表格的鎖時間也會大大減少，盡可能的保證資料庫的可擴充性；
  • 允許 in-place 操作的 DDL，避免重建表格占用過多磁盤IO及CPU資源，減少對資料庫的整體負荷，使得在DDL期間，能夠維持資料庫的高性能及高吞吐量；
  • 允許 in-place 操作的 DDL，比需要COPY到臨時檔案的操作要更少占用buffer pool，避免以往DDL過程中性能的臨時下降，因為以前需要拷貝資料到臨時表，這個過程會占用到buffer pool ，導緻記憶體中的部分頻繁通路的資料會被清理出去。

3 Online DDL涉及參數及選項

3.1 innodb_online_alter_log_max_size

    online ddl過程中發生DML時，會把資料修改情況記錄到row-log中，而row-log的大小，則由 innodb_online_alter_log_max_size設定，預設為128M，當表格較大且操作頻繁時，做DDL過程，可調大該參數，避免出現1799錯誤：

3.2 Online DDL文法

  •  Alter table …. , ALGORITHM [=] {DEFAULT|INPLACE|COPY}, LOCK [=] { DEFAULT| NONE| SHARED| EXCLUSIVE }

3.3 lock 選項

    該選項用于調整DDL加鎖的方式，一共有4個選項。

  • LOCK=EXCLUSIVE
    • 對整個表格添加獨占鎖（x鎖），不允許查詢跟修改操作

  • LOCK=SHARED
    • 對整個表格添加（s鎖），允許查詢操作，但是不支援資料變更操作

  • LOCK=NONE
    • 不添加鎖，既允許查詢操作，也支援資料庫變更操作，該模式下并發最好

  • LOCK=DEFAULT
    • 沒有指定LOCK的時候，則是預設為這個選項
    • 根據DDL的操作類型，最小程度的加鎖，盡可能支援查詢及0DML操作
    • 首先判斷目前操作是否可以使用NONE模式，如果不能，判斷是否可以使用SHARED模式，如果不能，判斷是否可以使用

      EXCLUSIVE模式

3.4 

ALGORITHM選項

    DDL對資料庫性能的影響，很大程度受操作方式影響，比如是否是允許in-place，是否請求COPY操作，是否重建整個表格。比如某個表格，修改或者添加預設值，并不會影響到表格内部的資料，是以1s内就可以完成；添加1個索引，需要幾十秒，應為需要新增索引資料頁跟修改frm檔案，但是不用rebuild表格資料；而修改列的資料類型是，可能需要幾分鐘甚至更多時間，因為其需要重新Rebuild整個表格，執行期間對CPU，IO及buffer pool大量申請資源。

    由DDL引起的INPLACE,COPY,REBUILD，可以通過指定ALGORITHM來選擇（注意并非所有DDL都支援in-place，詳見第4部分）

  • ALGORITHM=INPLACE

  • ALGORITHM=COPY

   這兩個選項中，INPLACE要比COPY性能好，因為INPLACE既不會記錄UNDO LOG，也不寫REDO LOG，同時執行期間提供DML操作。

4 Online DDL支援文法情況

    Online DDL對不同的DDL語句具有不同的執行規則，下面的表格将較長的描述各個文法對Online DDL的支援情況。

列說明：

  • In-Place? 
    • 說明： 是否支援 

      ALGORITHM=INPLACE 
  • Rebuilds Table?
    • 說明：是否會重建表格
    • 重建表格分為兩種方式：INPLACE跟COPY （原地修改或者複制到臨時檔案修改）
    • 如果支援 

      ALGORITHM=INPLACE

       ，那麼則是原地修改 INPLACE（淡黃色标記）
    • 如果不支援 

      ALGORITHM=INPLACE

       ，那麼則是COPY，拷貝到臨時檔案修改，并且不支援UPDATE DELETE INSERT操作（深褐色标記）
  • Permits Concurrent DM
    • 說明： 是否支援在DDL期間并發對該表格操作DML SQL
    • 新增空間索引及全文索引時，不支援DML操作
    • 當允許時，可以通過LOCK選項來控制是否要提供查詢或者修改操作
    • LOCK=NONE，支援查詢跟UPDATE INSERT DELETE操作
    • LOCK=SHARED，僅支援查詢
    • Only Modifies Metadata? 
    • 是否隻修改中繼資料
MySQL Online DDL的改進與應用

5 測試記錄

5.1 4個典型DDL操作分析

    針對是否支援INPLACE、是否需要REBUILD及是否僅修改metadata來分類，選取每類一個DDL SQL來測試，見下圖：

MySQL Online DDL的改進與應用

    考慮到varchar變化長度的問題，這裡加測多這一項。

5.1.1 DDL測試内容

  • 測試DB環境：表格名 tbddl，表格大小：1G ，500W行記錄
  • 測試流程：開啟事務查詢，不送出 => 執行DDL => 送出查詢事務 => 執行DML =>開啟事務，執行DML不送出 =>送出DML
  • 測試DDL SQL
    • ALTER TABLE tbddl MODIFY COLUMN ItemId VARCHAR(20); 
    • ALTER TABLE tbddl ADD xinysu int;
    • CREATE INDEX IX_PROID ON tbddl (providerid);
    • ALTER TABLE tbddl ALTER COLUMN xinysu SET DEFAULT 123456;
    • ALTER TABLE tbddl ALTER COLUMN ItemId VARCHAR(50); #UTF8字元集,3個位元組一個字元，50個字元則是150個位元組，小于256bytes  
    • ALTER TABLE tbddl ALTER COLUMN ItemId VARCHAR(100);  #UTF8字元集,3個位元組一個字元，100個字元則是300個位元組，大于256bytes
  • 測試關注點
    • 啟動與關閉 old_alter_table
    • prepare，commit階段的鎖是怎麼樣的
    • excute階段的鎖是怎麼樣的
    • 執行期間伺服器的性能情況（zabbix監控）
    • 執行期間資料庫的并發情況（sysbench壓測）

5.1.2 DDL測試結論

    測試過程中的截圖，不在此描述，直接粘貼測試結果，感興趣的筒子們，可以自行測試。

    VARCHAR按字元存儲，每個字元按照字元集來計算位元組，UTF8是3個位元組一個字元，當VARCHAR的位元組數<256byte時，隻需要1個byte來存儲實際長度，當VARCHAR位元組數>=256時，則需要2個byte來存儲實際長度。舉例，UTF8字元集下的VARCHAR(10)，假設存儲 N （0<=N<=10），則其占用的位元組數為：N*3+1；UTF8字元集下的VARCHAR(100)，假設存儲 N （0<=N<=100），則其占用的位元組數為：N*3+2。

    了解了這一點後，就可以了解 增長或縮短列的長度這列DDL的處理方式，假設列 VARCHAR(M)需要增大或縮小到VARCHAR(N)，字元集是UTF8：

  • 當 3M<256，3N<256，存儲長度的位元組不需要變化，都為1，則不需要變動行記錄，僅需要修改中繼資料；
  • 當 3M>256，3N>256，存儲長度的位元組不需要變化，都為2， 則不需要變動行記錄，僅需要修改中繼資料；
  • 當 3M<256，3N>256，存儲長度的位元組需要變化，由1變2， 則需要變動行記錄，Online DDL使用COPY TABLE方式；
  • 當 3M>256，3N>256，存儲長度的位元組需要變化，由2變1，則需要變動行記錄，Online DDL使用COPY TABLE方式
MySQL Online DDL的改進與應用

5.2 同表格多個DDL處理

    在Online DDL之前，都會習慣性的把同個表格的所有DDL語句合并為一個SQL語句，避免重複Rebuild、多次加鎖導緻不提供DML時長增加等弊端。

    但是，引入Online DDL後，需要有2點改觀：

  • 除了個别不支援inplace的DDL語句，其他DDL語句在執行期間是不會加X鎖的，也就是表格仍然提供DML操作
  • 鎖的粒度，同個DDL語句中，按照最進階别的鎖處理
  • 維護的友善性

    這裡建議按照3類來處理（測試後的個人建議，僅供參考），見下圖。

  • 為啥copy table單獨出來呢？
    • 因為這一類操作過程中是不允許DML操作的，建議把這一類的合成單獨一條DDL SQL執行，不與IPLACE的DDL SQL合并；
  • 為啥iplace的要分為2類呢？
    • 友善維護
    • 僅中繼資料修改的DDL較快執行結束，為了友善管理維護，不至于所有SQL貼一堆，僅中繼資料修改的DDL語句歸一類
    • 需要REBUILD的歸一類，避免重複rebuild，浪費磁盤IO跟CPU資源。
MySQL Online DDL的改進與應用

    舉個例子，現在上線項目，需要對表格tbddl，1個字段由INT修改為VARCHAR，新增3個字段，2個索引，2個預設值，2個列增長長度，單獨的SQL 為：

alter table tbddl alter column ItemId varchar(20);

ALTER TABLE tbddl ADD  su int;

ALTER TABLE tbddl ADD  xin varchar(40);

ALTER TABLE tbddl ADD  yu int;

CREATE INDEX IX_SU ON tbddl(SU);

CREATE INDEX IX_yu ON tbddl(yu);

ALTER TABLE tbddl ALTER COLUMN CreatedById SET DEFAULT 123456;

ALTER TABLE tbddl ALTER COLUMN ItemID SET DEFAULT 654321;

ALTER TABLE tbddl ALTER COLUMN CreatedByName VARCHAR(70);

ALTER TABLE tbddl ALTER COLUMN ModifiedByName VARCHAR(100);

測試建議以下執行方式：

ALTER TABLE tbddl ADD su int ,ADD xin varchar(40) ,ADD  yu int,ALTER COLUMN ModifiedByName VARCHAR(100),add index ix_su(SU), add index ix_yu(yu);

ALTER TABLE tbddl ALTER COLUMN CreatedById SET DEFAULT 123456,ALTER COLUMN ItemID SET DEFAULT 654321,ALTER COLUMN CreatedByName VARCHAR(70);

5.3 DDL執行期間資料庫性能異常處理

    執行DDL期間，需要密切留意資料庫伺服器的CPU及IO情況，檢視資料庫的連接配接池、慢查詢情況，如果期間發生了異常，應該如何處理呢？

    假設現在給大表tbddl新增一列，新增的過程中，發現影響到線上業務，需要緊急停止，可以通過以下步驟操作：

  • show processlist;
  • kill 程序id;

    具體見截圖。

5.4 DDL執行期間資料庫當機

    DDL期間，如果發生當機情況，會對資料庫的恢複啟動造成什麼影響呢？臨時檔案還存在嗎？ 恢複過程中會自動執行未完成的DDL操作嗎？如果會，是怎麼處理？如果不會，再次手動建立會有影響嗎？

    在5.7.17版本上，測試了4類DDL SQL，當DDL執行過程中，資料庫發生當機，該DDL不會影響到資料庫的恢複啟動，同時，這個未完成的DDL語句不回自動執行，由于當機過程中來不及清理臨時檔案，是以資料庫恢複後，臨時檔案依舊存在。DDL沒有commit，也就以為這資料庫的資料字典和表格的中繼資料沒有發生修改，再次手動執行DDL語句，并不會報沖突。（這點跟部分博文的分析有些出入，本次測試版本是5.7.17版本）

    測試過程，這裡不做過多描述，直接貼上結論，感興趣的筒子們可以自行測試，歡迎讨論。

5.5 DDL對主從的影響

    DDL期間，假設該SQL執行的時間需要10h，除去waitting metadata lock的時間，rebuild或者inplace的時間需要5小時，那麼在從庫是單線程SQL THREAD應用relay log的情況，需要考慮從庫滞後的影響。

    DDL在主庫執行情況，由于DDL語句沒有送出，是以不會同步到從庫上，從庫可以正常同步其他資料修改操作，這個環節沒有問題，但是當DDL在主庫送出後，該binlog日志通過IO_THREAD傳送到從庫的RELAY LOG上，從庫的SQL_Thread是單線程工作，應用RELAY log的時候，至少需要5個小時，也就是這5個小時都用來執行RELAY LOG，無法同步主庫幾個小時内産生的BIN LOG，那麼，從庫就會發生嚴重的滞後情況，這個問題是否在可接受範圍内，需要納入到DDL執行造成的影響範圍内。

    如果不能接受從庫這麼大的滞後，有什麼法子可以處理呢？

    可以通過這個思路來，從庫啟動并行複制。啟動并行複制，需要注意這幾個問題：

  • 使用注意
    • 在從庫嚴重落後主庫的情況下，可以開啟該參數實作多線程并行執行
    • 在業務量低的資料庫，不建議開啟，從庫同步性能反而會比拖累
  • 配置注意
    • 注意 master_info_repository  relay_log_info_repository 設定為 table，預設是寫入mater_info.log 及 relay_info.log ，刷下這兩個檔案的頻率帶來的性能影響比較大，據 姜承骁姜老師 壓測，性能相差 20-50%間
    • slave_parallel_workers 建議設定為從庫 核心 數
    • slave_parallel_type
      • database，不同庫的事務，觸發從庫并行回放
      • logical_clock，組送出事務，按照組送出設定，從庫并行回放，如果是為了改善DDL的滞後情況，應使用這個配置。

6 Online DDL注意事項

  • 磁盤空間
    • rebuild 的時候，datadir空間是否足夠
      • 因為會拷貝ibd檔案，是以要確定空間足夠
    • rebuild 的時候，innodb_online_alter_log_max_size是否足夠
      • rebuild過程中，産生的DML涉及到行記錄變更日志，是否足夠存儲
    • inplace的時候，考慮tmpdir空間是否足夠
  • ddl對從庫延遲的影響是否可以接受
    • 主庫online DDL的過程中，由于沒有commit，是以其他并發操作可以正常同步到從庫
    • 主庫commit後，DDL同步到從庫
    • 由于從庫是單線程執行SQL_THREAD，假設DDL執行過程需要1個小時，那麼從庫将會滞後1小時+
    • 是否允許從庫的滞後，如果不允許，可以通過并行複制來優化處理
  • row-log會檢查重複值或者修改沖突嗎？
    • 會根據主鍵及唯一限制來檢查
  • copy table ，inplace下如何暫停DDL操作
    • show full processlist;
    • kill id; #( DDL SQL的id号)
    • 這裡kill完後，仍然可以再次正常執行DDL，不會存在沖突，其建立的臨時ibd及frm檔案會自動删除
  • copy table ，inplace下當機
    • 這兩種情況下當機後，沒有完成的DDL語句不會繼續執行
    • 但是，其生成的frm跟 ibd 臨時檔案不會被删除，可以手動删除，也可以不手動删除，即使不删除，也不會影響再次執行DDL
    • 但建議mysql服務後，删除無用的臨時檔案
  • 同個表格多個DDL語句，不要一個個執行
    • 請按照是否支援inplace及是否需要rebuild分類合并執行
  • 如何檢視ddl進度（未解決）
    • 如果有rebuild，則是通過ibd檔案的增長來評估；但是如果是inplace，如何檢視呢？有沒有什麼比較好的方式檢視？performance_schema是否有提供相應的查詢方式？

如果轉載，請注明博文來源： www.cnblogs.com/xinysu/ ，版權歸 部落格園 蘇家小蘿蔔 所有。望各位支援！

Online DDL mysql inplace rebuild table
上一篇: 北大肖臻《區塊鍊技術與應用》學習筆記13
下一篇: Nginx+uWSGI或fastcgi部署Django項目

繼續閱讀