MySQL實戰(zhàn)45講Day22----如何在臨時提升性能的同時保持數(shù)據(jù)的可靠性

一、WAL機制:

1捻爷、意義:

??只要redo log和binlog保證持久化到磁盤,就能確保MySQL異常重啟后份企,數(shù)據(jù)可以恢復(fù)也榄。

2、WAL機制主要得益于兩個方面:

?①司志、redo log 和 binlog都是順序?qū)懱鹱希疟P的順序?qū)懕入S機寫速度要快;
?②骂远、組提交機制囚霸,可以大幅度降低磁盤的IOPS消耗。

二激才、binlog的寫入機制:

1拓型、寫入過程:

??事務(wù)執(zhí)行過程中,先把日志寫到binlog cache瘸恼,事務(wù)提交的時候劣挫,再把binlog cache寫到binlog文件中。一個事務(wù)的binlog是不能被拆開的东帅,因此不論這個事務(wù)多大压固,也要確保一次性寫入。這就涉及到了binlog cache的保存問題靠闭。系統(tǒng)給binlog cache分配了一片內(nèi)存帐我,每個線程一個坎炼,參數(shù) binlog_cache_size用于控制單個線程內(nèi)binlog cache所占內(nèi)存的大小。如果超過了這個參數(shù)規(guī)定的大小焚刚,就要暫存到磁盤点弯。事務(wù)提交的時候,執(zhí)行器把binlog cache里的完整事務(wù)寫入到binlog中矿咕,并清空binlog cache抢肛。

binlog寫盤狀態(tài)

由圖可以看出:
?①、每個線程有自己binlog cache碳柱,但是共用同一份binlog文件捡絮。
?②、圖中的write莲镣,指的就是指把日志寫入到文件系統(tǒng)的page cache福稳,并沒有把數(shù)據(jù)持久化到磁盤,所以速度比較快瑞侮。
?③的圆、圖中的fsync,才是將數(shù)據(jù)持久化到磁盤的操作半火。一般情況下越妈,我們認為fsync才占磁盤的IOPS

2钮糖、write 和fsync的時機:

是由參數(shù)sync_binlog控制的:
?sync_binlog=0的時候梅掠,表示每次提交事務(wù)都只write,不fsync店归;
?sync_binlog=1的時候阎抒,表示每次提交事務(wù)都會執(zhí)行fsync;
?sync_binlog=N(N>1)的時候消痛,表示每次提交事務(wù)都write且叁,但累積N個事務(wù)后才fsync。
因此秩伞,在出現(xiàn)IO瓶頸的場景里谴古,將sync_binlog設(shè)置成一個比較大的值,可以提升性能稠歉。在實際的業(yè)務(wù)場景中掰担,考慮到丟失日志量的可控性,一般不建議將這個參數(shù)設(shè)成0怒炸,比較常見的是將其設(shè)置為100~1000中的某個數(shù)值带饱。但是,將sync_binlog設(shè)置為N,對應(yīng)的風(fēng)險是:如果主機發(fā)生異常重啟勺疼,會丟失最近N個事務(wù)的binlog日志教寂。

三、redo log的寫入機制:

1执庐、寫入過程:

??事務(wù)在執(zhí)行過程中酪耕,生成的redo log先寫到redo log buffer。

2轨淌、redo log buffer里面的內(nèi)容迂烁,是不是每次生成后都要直接持久化到磁盤?

??不是递鹉。如果事務(wù)執(zhí)行期間MySQL發(fā)生異常重啟盟步,那這部分日志就丟了。由于事務(wù)并沒有提交躏结,所以這時日志丟了也不會有損失却盘。

3、事務(wù)還沒提交的時候媳拴,redo log buffer中的部分日志有沒有可能被持久化到磁盤呢黄橘?

redo log存儲狀態(tài)

??redo log可能存在的三種狀態(tài):
?①、存在redo log buffer中屈溉,物理上是在MySQL進程內(nèi)存中旬陡,就是圖中的紅色部分;
?②语婴、寫到磁盤(write),但是沒有持久化(fsync)驶睦,物理上是在文件系統(tǒng)的page cache里面砰左,也就是圖中的黃色部分;
?③场航、持久化到磁盤缠导,對應(yīng)的是hard disk,也就是圖中的綠色部分溉痢。
??日志寫到redo log buffer是很快的僻造,wirte到page cache也差不多,但是持久化到磁盤的速度就慢多了孩饼。為了控制redo log的寫入策略髓削,InnoDB提供了innodb_flush_log_at_trx_commit參數(shù),它有三種可能取值:
?設(shè)置為0的時候镀娶,表示每次事務(wù)提交時都只是把redo log留在redo log buffer中;
?設(shè)置為1的時候立膛,表示每次事務(wù)提交時都將redo log直接持久化到磁盤;
?設(shè)置為2的時候,表示每次事務(wù)提交時都只是把redo log寫到page cache宝泵。

InnoDB有一個后臺線程好啰,每隔1秒,就會把redo log buffer中的日志儿奶,調(diào)用write寫到文件系統(tǒng)的page cache框往,然后調(diào)用fsync持久化到磁盤。

注意:事務(wù)執(zhí)行中間過程的redo log也是直接寫在redo log buffer中的闯捎,這些redo log也會被后臺線程一起持久化到磁盤椰弊。也就是說,一個沒有提交的事務(wù)的redo log隙券,也是可能已經(jīng)持久化到磁盤的男应。

4、讓一個沒有提交的事務(wù)的redo log寫入到磁盤中的三種場景:

?①娱仔、后臺線程每秒一次的輪詢操作沐飘。
?②、redo log buffer占用的空間即將達到 innodb_log_buffer_size一半的時候牲迫,后臺線程會主動寫盤耐朴。注意,由于這個事務(wù)并沒有提交盹憎,所以這個寫盤動作只是write筛峭,而沒有調(diào)用fsync,也就是只留在了文件系統(tǒng)的page cache陪每。
?③影晓、并行的事務(wù)提交的時候,順帶將這個事務(wù)的redo log buffer持久化到磁盤檩禾。假設(shè)一個事務(wù)A執(zhí)行到一半挂签,已經(jīng)寫了一些redo log到buffer中,這時候有另外一個線程的事務(wù)B提交盼产,如果innodb_flush_log_at_trx_commit設(shè)置的是1饵婆,那么按照這個參數(shù)的邏輯,事務(wù)B要把redo log buffer里的日志全部持久化到磁盤戏售。這時候侨核,就會帶上事務(wù)A在redo log buffer里的日志一起持久化到磁盤。

四灌灾、MySQL的“雙1”配置:

1搓译、概念:

??指的是sync_binlog和innodb_flush_log_at_trx_commit都設(shè)置成 1。也就是說锋喜,一個事務(wù)完整提交前侥衬,需要等待兩次刷盤,一次是redo log(prepare 階段),一次是binlog轴总。

2直颅、組提交(group commit)機制:

<1>、日志邏輯序列號(log sequence number怀樟,LSN):

??LSN是單調(diào)遞增的功偿,用來對應(yīng)redo log的一個個寫入點。每次寫入長度為length的redo log往堡, LSN的值就會加上length械荷。LSN也會寫到InnoDB的數(shù)據(jù)頁中,來確保數(shù)據(jù)頁不會被多次執(zhí)行重復(fù)的redo log虑灰。

<2>吨瞎、組提交的過程:

??如圖所示,是三個并發(fā)事務(wù)(trx1, trx2, trx3)在prepare 階段穆咐,都寫完redo log buffer颤诀,持久化到磁盤的過程,對應(yīng)的LSN分別是50对湃、120 和160崖叫。

redo log 組提交

從圖中可以看到:
?①、trx1是第一個到達的拍柒,會被選為這組的 leader心傀;
?②、等trx1要開始寫盤的時候拆讯,這個組里面已經(jīng)有了三個事務(wù)脂男,這時候LSN也變成了160;
?③种呐、trx1去寫盤時宰翅,帶的是LSN=160,因此等trx1返回時陕贮,所有LSN小于等于160的redo log,都已經(jīng)被持久化到磁盤潘飘;
?④肮之、這時候trx2和trx3就可以直接返回了。
??所以卜录,一次組提交里面戈擒,組員越多,節(jié)約磁盤IOPS的效果越好艰毒。但如果只有單線程壓測筐高,那就只能老老實實地一個事務(wù)對應(yīng)一次持久化操作了。在并發(fā)更新場景下,第一個事務(wù)寫完redo log buffer以后柑土,接下來這個fsync越晚調(diào)用蜀肘,組員可能越多,節(jié)約IOPS的效果就越好稽屏。為了讓一次fsync帶的組員更多扮宠,MySQL會優(yōu)化——拖時間。也就是在兩階段提交的時候狐榔,先把binlog從binlog cache中寫到磁盤上的binlog文件坛增;再調(diào)用fsync持久化。
兩階段提交細化

<3>薄腻、提升binlog組提交的效果的方法:

??通過設(shè)置 binlog_group_commit_sync_delay 和 binlog_group_commit_sync_no_delay_count來實現(xiàn)收捣。
?①、binlog_group_commit_sync_delay參數(shù)庵楷,表示延遲多少微秒后才調(diào)用fsync;
?②罢艾、binlog_group_commit_sync_no_delay_count參數(shù),表示累積多少次以后才調(diào)用fsync嫁乘。
??這兩個條件是或的關(guān)系昆婿,也就是說只要有一個滿足條件就會調(diào)用fsync。所以當binlog_group_commit_sync_delay設(shè)置為0時蜓斧,binlog_group_commit_sync_no_delay_count就無效了仓蛆。

五、如果MySQL現(xiàn)在出現(xiàn)了性能瓶頸且瓶頸在IO上挎春,此時的三種改進方法:

?①看疙、設(shè)置 binlog_group_commit_sync_delay 和 binlog_group_commit_sync_no_delay_count參數(shù),減少binlog的寫盤次數(shù)直奋。這個方法是基于“額外的故意等待”來實現(xiàn)的能庆,因此可能會增加語句的響應(yīng)時間,但沒有丟失數(shù)據(jù)的風(fēng)險脚线。
?②搁胆、將sync_binlog 設(shè)置為大于1的值(比較常見是100~1000)。這樣做的風(fēng)險是邮绿,主機掉電時會丟binlog日志渠旁。
?③、將innodb_flush_log_at_trx_commit設(shè)置為2船逮。這樣做的風(fēng)險是顾腊,主機掉電的時候會丟數(shù)據(jù)。

注意:不建議把innodb_flush_log_at_trx_commit 設(shè)置成0挖胃。因為把這個參數(shù)設(shè)置成0杂靶,表示redo log只保存在內(nèi)存中梆惯,這樣的話MySQL本身異常重啟也會丟數(shù)據(jù),風(fēng)險太大吗垮。而redo log寫到文件系統(tǒng)的page cache的速度也是很快的垛吗,所以將這個參數(shù)設(shè)置成2跟設(shè)置成0其實性能差不多,但這樣做MySQL異常重啟時就不會丟數(shù)據(jù)了抱既,相比之下風(fēng)險會更小职烧。

六、相關(guān)問題:

問題一:執(zhí)行一個update語句以后防泵,再去執(zhí)行hexdump命令直接查看ibd文件內(nèi)容蚀之,為什么沒有看到數(shù)據(jù)有改變呢?

回答:這可能是因為WAL機制的原因捷泞。update語句執(zhí)行完成后足删,InnoDB只保證寫完了redo log、內(nèi)存锁右,可能還沒來得及將數(shù)據(jù)寫到磁盤失受。

問題2:為什么binlog cache是每個線程自己維護的,而redo log buffer是全局共用的咏瑟?

回答:MySQL這么設(shè)計的主要原因是拂到,binlog是不能“被打斷的”。一個事務(wù)的binlog必須連續(xù)寫码泞,因此要整個事務(wù)完成后兄旬,再一起寫到文件里。而redo log并沒有這個要求余寥,中間有生成的日志可以寫到redo log buffer中领铐。redo log buffer中的內(nèi)容還能“搭便車”,其他事務(wù)提交的時候可以被一起寫到磁盤中宋舷。

問題3:事務(wù)執(zhí)行期間绪撵,還沒到提交階段,如果發(fā)生crash的話祝蝠,redo log肯定丟了音诈,這會不會導(dǎo)致主備不一致呢?

回答:不會绎狭。因為這時候binlog 也還在binlog cache里细溅,沒發(fā)給備庫。crash以后redo log和binlog都沒有了坟岔,從業(yè)務(wù)角度看這個事務(wù)也沒有提交谒兄,所以數(shù)據(jù)是一致的摔桦。

問題4:如果binlog寫完盤以后發(fā)生crash社付,這時候還沒給客戶端答復(fù)就重啟了承疲。等客戶端再重連進來,發(fā)現(xiàn)事務(wù)已經(jīng)提交成功了鸥咖,這是不是bug燕鸽?

回答:不是。實際上數(shù)據(jù)庫的crash-safe保證的是:
?①啼辣、如果客戶端收到事務(wù)成功的消息啊研,事務(wù)就一定持久化了;
?②鸥拧、如果客戶端收到事務(wù)失數吃丁(比如主鍵沖突、回滾等)的消息富弦,事務(wù)就一定失敗了沟娱;
?③、如果客戶端收到“執(zhí)行異惩蠊瘢”的消息济似,應(yīng)用需要重連后通過查詢當前狀態(tài)來繼續(xù)后續(xù)的邏輯。此時數(shù)據(jù)庫只需要保證內(nèi)部(數(shù)據(jù)和日志之間盏缤,主庫和備庫之間)一致就可以了砰蠢。

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
  • 序言:七十年代末,一起剝皮案震驚了整個濱河市唉铜,隨后出現(xiàn)的幾起案子台舱,更是在濱河造成了極大的恐慌,老刑警劉巖打毛,帶你破解...
    沈念sama閱讀 211,743評論 6 492
  • 序言:濱河連續(xù)發(fā)生了三起死亡事件柿赊,死亡現(xiàn)場離奇詭異,居然都是意外死亡幻枉,警方通過查閱死者的電腦和手機碰声,發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 90,296評論 3 385
  • 文/潘曉璐 我一進店門,熙熙樓的掌柜王于貴愁眉苦臉地迎上來熬甫,“玉大人胰挑,你說我怎么就攤上這事〈患纾” “怎么了瞻颂?”我有些...
    開封第一講書人閱讀 157,285評論 0 348
  • 文/不壞的土叔 我叫張陵,是天一觀的道長郑象。 經(jīng)常有香客問我贡这,道長,這世上最難降的妖魔是什么厂榛? 我笑而不...
    開封第一講書人閱讀 56,485評論 1 283
  • 正文 為了忘掉前任盖矫,我火速辦了婚禮丽惭,結(jié)果婚禮上,老公的妹妹穿的比我還像新娘辈双。我一直安慰自己责掏,他們只是感情好,可當我...
    茶點故事閱讀 65,581評論 6 386
  • 文/花漫 我一把揭開白布湃望。 她就那樣靜靜地躺著换衬,像睡著了一般。 火紅的嫁衣襯著肌膚如雪证芭。 梳的紋絲不亂的頭發(fā)上瞳浦,一...
    開封第一講書人閱讀 49,821評論 1 290
  • 那天,我揣著相機與錄音废士,去河邊找鬼术幔。 笑死,一個胖子當著我的面吹牛湃密,可吹牛的內(nèi)容都是我干的诅挑。 我是一名探鬼主播,決...
    沈念sama閱讀 38,960評論 3 408
  • 文/蒼蘭香墨 我猛地睜開眼泛源,長吁一口氣:“原來是場噩夢啊……” “哼拔妥!你這毒婦竟也來了?” 一聲冷哼從身側(cè)響起达箍,我...
    開封第一講書人閱讀 37,719評論 0 266
  • 序言:老撾萬榮一對情侶失蹤没龙,失蹤者是張志新(化名)和其女友劉穎,沒想到半個月后缎玫,有當?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體硬纤,經(jīng)...
    沈念sama閱讀 44,186評論 1 303
  • 正文 獨居荒郊野嶺守林人離奇死亡,尸身上長有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點故事閱讀 36,516評論 2 327
  • 正文 我和宋清朗相戀三年赃磨,在試婚紗的時候發(fā)現(xiàn)自己被綠了筝家。 大學(xué)時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片。...
    茶點故事閱讀 38,650評論 1 340
  • 序言:一個原本活蹦亂跳的男人離奇死亡邻辉,死狀恐怖溪王,靈堂內(nèi)的尸體忽然破棺而出,到底是詐尸還是另有隱情值骇,我是刑警寧澤莹菱,帶...
    沈念sama閱讀 34,329評論 4 330
  • 正文 年R本政府宣布,位于F島的核電站吱瘩,受9級特大地震影響道伟,放射性物質(zhì)發(fā)生泄漏。R本人自食惡果不足惜使碾,卻給世界環(huán)境...
    茶點故事閱讀 39,936評論 3 313
  • 文/蒙蒙 一蜜徽、第九天 我趴在偏房一處隱蔽的房頂上張望裹芝。 院中可真熱鬧,春花似錦娜汁、人聲如沸。這莊子的主人今日做“春日...
    開封第一講書人閱讀 30,757評論 0 21
  • 文/蒼蘭香墨 我抬頭看了看天上的太陽。三九已至颅和,卻和暖如春傅事,著一層夾襖步出監(jiān)牢的瞬間,已是汗流浹背峡扩。 一陣腳步聲響...
    開封第一講書人閱讀 31,991評論 1 266
  • 我被黑心中介騙來泰國打工蹭越, 沒想到剛下飛機就差點兒被人妖公主榨干…… 1. 我叫王不留,地道東北人教届。 一個月前我還...
    沈念sama閱讀 46,370評論 2 360
  • 正文 我出身青樓响鹃,卻偏偏與公主長得像,于是被迫代替她去往敵國和親案训。 傳聞我的和親對象是個殘疾皇子买置,可洞房花燭夜當晚...
    茶點故事閱讀 43,527評論 2 349

推薦閱讀更多精彩內(nèi)容