任何DDL操作,執(zhí)行者都需要預先測試或者清晰了解這個操作會給數(shù)據(jù)庫帶來的影響是否是在業(yè)務期間數(shù)據(jù)庫的可承受范圍內(nèi),尤其是對大表的DDL操作中,需要密切留意服務器的IO,內(nèi)存及CPU使用情況(每個DBA總有那么一段被大表的DDL語句坑到的血淚史)。
如果轉(zhuǎn)載,請注明博文來源: www.cnblogs.com/xinysu/ ,版權(quán)歸 博客園 蘇家小蘿卜 所有。望各位支持!
1 早期DDL實現(xiàn)原理(5.6.7之前 )
Innodb早期支持通過copy table跟inplace的方式來執(zhí)行DDL語句,其原理如下:
copy table方式
新建跟原表格一致的臨時表,并在該臨時表上執(zhí)行DDL語句
鎖原表,不允許DML,允許查詢
逐行數(shù)據(jù)從原表拷貝到臨時表中(這個過程是沒有排序的)
拷貝結(jié)束后,原表禁止讀操作,也就是原表此時不提供讀寫服務
進行rename操作,完成DDL過程
inplace方式(fast index creation,僅針對索引的創(chuàng)建跟刪除)
新建frm臨時文件
鎖原表,不允許DML,允許查詢
按照聚集索引的順序,查詢數(shù)據(jù),找到需要的索引列數(shù)據(jù),排序后插入到新的索引頁中
原表禁止讀操作,也就是原表此時不提供讀寫服務
進行rename操作,替換frm文件,完成DDL過程
inplace在copy table的基礎上做了一個較大的改進,則是不需要copy整個表格,只需要在原來的ibd文件上,新建所需要的索引頁,這個過程比copy table節(jié)約極大的IO資源占用 且 DDL SQL執(zhí)行速度大大提高,減少了該表格不提供寫服務的時長。但是inplace僅支持索引的創(chuàng)建于刪除,不支持其他的DDL操作,其他的DDL操作,仍然是copy table方式執(zhí)行。
對于一個線上業(yè)務數(shù)據(jù)庫,無論是copy table方式還是inplace方式,這里仍然有一個明顯的弊端:操作期間涉及表格不提供寫服務!無法對涉及到表格至下INSERT,UPDATE,DELETE操作,僅支持SELECT。
2 Online DDL實現(xiàn)原理
當表格發(fā)生DDL操作,可能會出現(xiàn)該表格數(shù)分鐘甚至數(shù)小時不可訪問,性能及響應異常,為了有效改善這個情況,MySQL 在5.6.7版本推出了Online DDL。(本文參考官網(wǎng)5.7版本的文檔整理及測試)。
在online DDL中,也包含了copy跟inplace兩種方式,對于不支持Online DDL的DDL SQL,則采用COPY方式;對于支持Online DDL的DDL SQL,則采用Inplace方式,這里的Inplace又區(qū)分為2類:是否需要rebuild表格,判斷標準為:是否修改行記錄格式。如果修改了行記錄格式,則需要rebuild表格,比如修改列類型、增減列等;如果沒有修改行記錄格式,僅修改表的元數(shù)據(jù),則不需要rebuild表格,僅修改元數(shù)據(jù) metadata,比如刪除索引、設置默認值及重命名列名等。詳細可見下圖,具體語法情況見`第4部分`。
那么,新增的Online DDL內(nèi)部是怎樣一個實現(xiàn)原理呢?(此處參考:http://blog.itpub.net/22664653/viewspace-2056953/)
有3個階段:prepare、execute、commit。
PREPARE
創(chuàng)建新的臨時frm文件
持有EXCLUSIVE_MDL鎖,禁止讀寫
根據(jù)alter類型,確定執(zhí)行方式(copy,rebuild,no-rebuild)
更新數(shù)據(jù)字典的內(nèi)存對象
若是需要rebuild,分配row_log對象記錄的增量
若是需要rebuild, 生成新的臨時ibd文件
EXECUTE
降低EXCLUSIVE-MDL鎖,允許讀寫(copy 不允許寫)
記錄ddl執(zhí)行過程中產(chǎn)生的增量row-log(僅rebuild類型需要)
掃描old_table的聚集索引每一條記錄record
遍歷新表的聚集索引和二級索引,逐一處理
根據(jù)record構(gòu)造對應的索引項
將構(gòu)造索引項插入sort_buffer塊
將sort_buffer塊插入新的索引
把row-log中的操作應用到新臨時表中,應用到最后一個Block
這部分無操作
如果是僅修改元數(shù)據(jù):
其他,則是:
COMMIT
升級到EXECLUSIVE-MDL鎖,禁止讀寫
重做最后一部分的row_log增量
更新innodb的數(shù)據(jù)字典表
提交事務,寫redo日志
修改統(tǒng)計信息
rename 臨時的ibd文件、frm文件
DDL完成
這里注意下row-log,它是記錄 DDL在執(zhí)行過程中表格發(fā)生數(shù)據(jù)變更的操作,這樣就可以保證執(zhí)行DDL表格的并發(fā)性,在EXCUTE階段可以正常提供寫服務,不發(fā)生堵塞,最后把row-log應用到新的表格上即可。
在5.7.17版本上測試的時候,發(fā)現(xiàn),支持inplace且需要rebuild的DDL,在DDL期間,如果數(shù)據(jù)發(fā)生修改,都是直接刷新到原來的ibd文件上,在測試環(huán)境中,給大表testddl刪除一個字段,這個過程中INSERT 100w行記錄,可以看到原有ibd文件增長了1G左右。
這里有個疑問,未確定:row-log應該不是記錄行記錄的修改格式,因為這樣效率過慢,初步推測應該是僅記錄主鍵,然后根據(jù)主鍵查找應用到新表上。
Online DDL可以有效改善DDL期間對數(shù)據(jù)庫的影響:
Online DDL期間,查詢和DML操作在多數(shù)情況下可以正常執(zhí)行,對表格的鎖時間也會大大減少,盡可能的保證數(shù)據(jù)庫的可擴展性;
允許 in-place 操作的 DDL,避免重建表格占用過多磁盤IO及CPU資源,減少對數(shù)據(jù)庫的整體負荷,使得在DDL期間,能夠維持數(shù)據(jù)庫的高性能及高吞吐量;
允許 in-place 操作的 DDL,比需要COPY到臨時文件的操作要更少占用buffer pool,避免以往DDL過程中性能的臨時下降,因為以前需要拷貝數(shù)據(jù)到臨時表,這個過程會占用到buffer pool ,導致內(nèi)存中的部分頻繁訪問的數(shù)據(jù)會被清理出去。
3 Online DDL涉及參數(shù)及選項
3.1 innodb_online_alter_log_max_size
online ddl過程中發(fā)生DML時,會把數(shù)據(jù)修改情況記錄到row-log中,而row-log的大小,則由 innodb_online_alter_log_max_size設定,默認為128M,當表格較大且操作頻繁時,做DDL過程,可調(diào)大該參數(shù),避免出現(xiàn)1799錯誤:
3.2 Online DDL語法
Alter table …. , ALGORITHM [=] {DEFAULT|INPLACE|COPY}, LOCK [=] { DEFAULT| NONE| SHARED| EXCLUSIVE }
3.3 lock 選項
該選項用于調(diào)整DDL加鎖的方式,一共有4個選項。
LOCK=EXCLUSIVE
對整個表格添加獨占鎖(x鎖),不允許查詢跟修改操作
LOCK=SHARED
對整個表格添加(s鎖),允許查詢操作,但是不支持數(shù)據(jù)變更操作
LOCK=NONE
不添加鎖,既允許查詢操作,也支持數(shù)據(jù)庫變更操作,該模式下并發(fā)最好
LOCK=DEFAULT
沒有指定LOCK的時候,則是默認為這個選項
根據(jù)DDL的操作類型,最小程度的加鎖,盡可能支持查詢及0DML操作
首先判斷當前操作是否可以使用NONE模式,如果不能,判斷是否可以使用SHARED模式,如果不能,判斷是否可以使用
EXCLUSIVE模式
3.4 ALGORITHM選項
DDL對數(shù)據(jù)庫性能的影響,很大程度受操作方式影響,比如是否是允許in-place,是否請求COPY操作,是否重建整個表格。比如某個表格,修改或者添加默認值,并不會影響到表格內(nèi)部的數(shù)據(jù),所以1s內(nèi)就可以完成;添加1個索引,需要幾十秒,應為需要新增索引數(shù)據(jù)頁跟修改frm文件,但是不用rebuild表格數(shù)據(jù);而修改列的數(shù)據(jù)類型是,可能需要幾分鐘甚至更多時間,因為其需要重新Rebuild整個表格,執(zhí)行期間對CPU,IO及buffer pool大量申請資源。
由DDL引起的INPLACE,COPY,REBUILD,可以通過指定ALGORITHM來選擇(注意并非所有DDL都支持in-place,詳見第4部分)
ALGORITHM=INPLACE
ALGORITHM=COPY
這兩個選項中,INPLACE要比COPY性能好,因為INPLACE既不會記錄UNDO LOG,也不寫REDO LOG,同時執(zhí)行期間提供DML操作。
4 Online DDL支持語法情況
Online DDL對不同的DDL語句具有不同的執(zhí)行規(guī)則,下面的表格將詳細描述各個語法對Online DDL的支持情況。
列說明:
In-Place?
說明: 是否支持
ALGORITHM=INPLACE
Rebuilds Table?
說明:是否會重建表格
重建表格分為兩種方式:INPLACE跟COPY (原地修改或者復制到臨時文件修改)
如果支持
ALGORITHM=INPLACE
,那么則是原地修改 INPLACE(淡黃色標記)如果不支持
ALGORITHM=INPLACE
,那么則是COPY,拷貝到臨時文件修改,并且不支持UPDATE DELETE INSERT操作(深褐色標記)Permits Concurrent DM
說明: 是否支持在DDL期間并發(fā)對該表格操作DML SQL
新增空間索引及全文索引時,不支持DML操作
當允許時,可以通過LOCK選項來控制是否要提供查詢或者修改操作
LOCK=NONE,支持查詢跟UPDATE INSERT DELETE操作
LOCK=SHARED,僅支持查詢
Only Modifies Metadata?
是否只修改元數(shù)據(jù)
5 測試記錄
5.1 4個典型DDL操作分析
針對是否支持INPLACE、是否需要REBUILD及是否僅修改metadata來分類,選取每類一個DDL SQL來測試,見下圖:
考慮到varchar變化長度的問題,這里加測多這一項。
5.1.1 DDL測試內(nèi)容
測試DB環(huán)境:表格名 tbddl,表格大?。?G ,500W行記錄
測試流程:開啟事務查詢,不提交 => 執(zhí)行DDL => 提交查詢事務 => 執(zhí)行DML =>開啟事務,執(zhí)行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個字節(jié)一個字符,50個字符則是150個字節(jié),小于256bytes
ALTER TABLE tbddl ALTER COLUMN ItemId VARCHAR(100); #UTF8字符集,3個字節(jié)一個字符,100個字符則是300個字節(jié),大于256bytes
測試關(guān)注點
啟動與關(guān)閉 old_alter_table
prepare,commit階段的鎖是怎么樣的
excute階段的鎖是怎么樣的
執(zhí)行期間服務器的性能情況(zabbix監(jiān)控)
執(zhí)行期間數(shù)據(jù)庫的并發(fā)情況(sysbench壓測)
5.1.2 DDL測試結(jié)論
測試過程中的截圖,不在此描述,直接粘貼測試結(jié)果,感興趣的筒子們,可以自行測試。
VARCHAR按字符存儲,每個字符按照字符集來計算字節(jié),UTF8是3個字節(jié)一個字符,當VARCHAR的字節(jié)數(shù)<256byte時,只需要1個byte來存儲實際長度,當VARCHAR字節(jié)數(shù)>=256時,則需要2個byte來存儲實際長度。舉例,UTF8字符集下的VARCHAR(10),假設存儲 N (0<=N<=10),則其占用的字節(jié)數(shù)為:N*3+1;UTF8字符集下的VARCHAR(100),假設存儲 N (0<=N<=100),則其占用的字節(jié)數(shù)為:N*3+2。
理解了這一點后,就可以理解 增長或縮短列的長度這列DDL的處理方式,假設列 VARCHAR(M)需要增大或縮小到VARCHAR(N),字符集是UTF8:
當 3M<256,3N<256,存儲長度的字節(jié)不需要變化,都為1,則不需要變動行記錄,僅需要修改元數(shù)據(jù);
當 3M>256,3N>256,存儲長度的字節(jié)不需要變化,都為2, 則不需要變動行記錄,僅需要修改元數(shù)據(jù);
當 3M<256,3N>256,存儲長度的字節(jié)需要變化,由1變2, 則需要變動行記錄,Online DDL使用COPY TABLE方式;
當 3M>256,3N>256,存儲長度的字節(jié)需要變化,由2變1,則需要變動行記錄,Online DDL使用COPY TABLE方式
5.2 同表格多個DDL處理
在Online DDL之前,都會習慣性的把同個表格的所有DDL語句合并為一個SQL語句,避免重復Rebuild、多次加鎖導致不提供DML時長增加等弊端。
但是,引入Online DDL后,需要有2點改觀:
除了個別不支持inplace的DDL語句,其他DDL語句在執(zhí)行期間是不會加X鎖的,也就是表格仍然提供DML操作
鎖的粒度,同個DDL語句中,按照最高級別的鎖處理
維護的方便性
這里建議按照3類來處理(測試后的個人建議,僅供參考),見下圖。
為啥copy table單獨出來呢?
因為這一類操作過程中是不允許DML操作的,建議把這一類的合成單獨一條DDL SQL執(zhí)行,不與IPLACE的DDL SQL合并;
為啥iplace的要分為2類呢?
方便維護
僅元數(shù)據(jù)修改的DDL較快執(zhí)行結(jié)束,為了方便管理維護,不至于所有SQL貼一堆,僅元數(shù)據(jù)修改的DDL語句歸一類
需要REBUILD的歸一類,避免重復rebuild,浪費磁盤IO跟CPU資源。
舉個例子,現(xiàn)在上線項目,需要對表格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);
測試建議以下執(zhí)行方式:
alter table tbddl alter column ItemId varchar(20);
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執(zhí)行期間數(shù)據(jù)庫性能異常處理
執(zhí)行DDL期間,需要密切留意數(shù)據(jù)庫服務器的CPU及IO情況,查看數(shù)據(jù)庫的連接池、慢查詢情況,如果期間發(fā)生了異常,應該如何處理呢?
假設現(xiàn)在給大表tbddl新增一列,新增的過程中,發(fā)現(xiàn)影響到線上業(yè)務,需要緊急停止,可以通過以下步驟操作:
show processlist;
kill 進程id;
具體見截圖。
5.4 DDL執(zhí)行期間數(shù)據(jù)庫宕機
DDL期間,如果發(fā)生宕機情況,會對數(shù)據(jù)庫的恢復啟動造成什么影響呢?臨時文件還存在嗎? 恢復過程中會自動執(zhí)行未完成的DDL操作嗎?如果會,是怎么處理?如果不會,再次手動創(chuàng)建會有影響嗎?
在5.7.17版本上,測試了4類DDL SQL,當DDL執(zhí)行過程中,數(shù)據(jù)庫發(fā)生宕機,該DDL不會影響到數(shù)據(jù)庫的恢復啟動,同時,這個未完成的DDL語句不回自動執(zhí)行,由于宕機過程中來不及清理臨時文件,所以數(shù)據(jù)庫恢復后,臨時文件依舊存在。DDL沒有commit,也就以為這數(shù)據(jù)庫的數(shù)據(jù)字典和表格的元數(shù)據(jù)沒有發(fā)生修改,再次手動執(zhí)行DDL語句,并不會報沖突。(這點跟部分博文的分析有些出入,本次測試版本是5.7.17版本)
測試過程,這里不做過多描述,直接貼上結(jié)論,感興趣的筒子們可以自行測試,歡迎討論。
5.5 DDL對主從的影響
DDL期間,假設該SQL執(zhí)行的時間需要10h,除去waitting metadata lock的時間,rebuild或者inplace的時間需要5小時,那么在從庫是單線程SQL THREAD應用relay log的情況,需要考慮從庫滯后的影響。
DDL在主庫執(zhí)行情況,由于DDL語句沒有提交,所以不會同步到從庫上,從庫可以正常同步其他數(shù)據(jù)修改操作,這個環(huán)節(jié)沒有問題,但是當DDL在主庫提交后,該binlog日志通過IO_THREAD傳送到從庫的RELAY LOG上,從庫的SQL_Thread是單線程工作,應用RELAY log的時候,至少需要5個小時,也就是這5個小時都用來執(zhí)行RELAY LOG,無法同步主庫幾個小時內(nèi)產(chǎn)生的BIN LOG,那么,從庫就會發(fā)生嚴重的滯后情況,這個問題是否在可接受范圍內(nèi),需要納入到DDL執(zhí)行造成的影響范圍內(nèi)。
如果不能接受從庫這么大的滯后,有什么法子可以處理呢?
可以通過這個思路來,從庫啟動并行復制。啟動并行復制,需要注意這幾個問題:
使用注意
在從庫嚴重落后主庫的情況下,可以開啟該參數(shù)實現(xiàn)多線程并行執(zhí)行
在業(yè)務量低的數(shù)據(jù)庫,不建議開啟,從庫同步性能反而會比拖累
配置注意
database,不同庫的事務,觸發(fā)從庫并行回放
logical_clock,組提交事務,按照組提交設置,從庫并行回放,如果是為了改善DDL的滯后情況,應使用這個配置。
注意 master_info_repository relay_log_info_repository 設置為 table,默認是寫入mater_info.log 及 relay_info.log ,刷下這兩個文件的頻率帶來的性能影響比較大,據(jù) 姜承驍姜老師 壓測,性能相差 20-50%間
slave_parallel_workers 建議設置為從庫 核心 數(shù)
slave_parallel_type
6 Online DDL注意事項
磁盤空間
rebuild過程中,產(chǎn)生的DML涉及到行記錄變更日志,是否足夠存儲
因為會拷貝ibd文件,所以要確??臻g足夠
rebuild 的時候,datadir空間是否足夠
rebuild 的時候,innodb_online_alter_log_max_size是否足夠
inplace的時候,考慮tmpdir空間是否足夠
ddl對從庫延遲的影響是否可以接受
主庫online DDL的過程中,由于沒有commit,所以其他并發(fā)操作可以正常同步到從庫
主庫commit后,DDL同步到從庫
由于從庫是單線程執(zhí)行SQL_THREAD,假設DDL執(zhí)行過程需要1個小時,那么從庫將會滯后1小時+
是否允許從庫的滯后,如果不允許,可以通過并行復制來優(yōu)化處理
row-log會檢查重復值或者修改沖突嗎?
會根據(jù)主鍵及唯一約束來檢查
copy table ,inplace下如何暫停DDL操作
show full processlist;
kill id; #( DDL SQL的id號)
這里kill完后,仍然可以再次正常執(zhí)行DDL,不會存在沖突,其創(chuàng)建的臨時ibd及frm文件會自動刪除
copy table ,inplace下宕機
這兩種情況下宕機后,沒有完成的DDL語句不會繼續(xù)執(zhí)行
但是,其生成的frm跟 ibd 臨時文件不會被刪除,可以手動刪除,也可以不手動刪除,即使不刪除,也不會影響再次執(zhí)行DDL
但建議mysql服務后,刪除無用的臨時文件
同個表格多個DDL語句,不要一個個執(zhí)行
請按照是否支持inplace及是否需要rebuild分類合并執(zhí)行
如何查看ddl進度(未解決)
如果有rebuild,則是通過ibd文件的增長來評估;但是如果是inplace,如何查看呢?有沒有什么比較好的方式查看?performance_schema是否有提供相應的查詢方式?
如果轉(zhuǎn)載,請注明博文來源: www.cnblogs.com/xinysu/ ,版權(quán)歸 博客園 蘇家小蘿卜 所有。望各位支持!
http://www.cnblogs.com/xinysu/p/6732646.html