MySQL事務(wù)已提交,數(shù)據(jù)卻丟了,趕緊檢查下這個(gè)配置!??!
有個(gè)水友提問(wèn):
沈老師,我們有一次MySQL崩潰,重啟后發(fā)現(xiàn)有些已經(jīng)提交的事務(wù)對(duì)數(shù)據(jù)的修改丟失了,不是說(shuō)事務(wù)能保證ACID特性么,想問(wèn)下什么情況下可能導(dǎo)致“事務(wù)已經(jīng)提交,數(shù)據(jù)卻丟失”呢?
這個(gè)問(wèn)題有點(diǎn)復(fù)雜,得先從redo log說(shuō)起。
為什么要有redo log?
事務(wù)提交后,必須將事務(wù)對(duì)數(shù)據(jù)頁(yè)的修改刷(fsync)到磁盤(pán)上,才能保證事務(wù)的ACID特性。
這個(gè)刷盤(pán),是一個(gè)隨機(jī)寫(xiě),隨機(jī)寫(xiě)性能較低,如果每次事務(wù)提交都刷盤(pán),會(huì)極大影響數(shù)據(jù)庫(kù)的性能。
隨機(jī)寫(xiě)性能差,有什么優(yōu)化方法呢?
架構(gòu)設(shè)計(jì)中有兩個(gè)常見(jiàn)的優(yōu)化方法:
- 先寫(xiě)日志(write log first),將隨機(jī)寫(xiě)優(yōu)化為順序?qū)懀?/li>
- 將每次寫(xiě)優(yōu)化為批量寫(xiě);
這兩個(gè)優(yōu)化,數(shù)據(jù)庫(kù)都用上了。
先說(shuō)第一個(gè)優(yōu)化,將對(duì)數(shù)據(jù)的修改先順序?qū)懙饺罩纠?,這個(gè)日志就是redo log。
假如某一時(shí)刻,數(shù)據(jù)庫(kù)崩潰,還沒(méi)來(lái)得及將數(shù)據(jù)頁(yè)刷盤(pán),數(shù)據(jù)庫(kù)重啟時(shí),會(huì)重做redo log里的內(nèi)容,以保證已提交事務(wù)對(duì)數(shù)據(jù)的影響被刷到磁盤(pán)上。
一句話,redo log是為了保證已提交事務(wù)的ACID特性,同時(shí)能夠提高數(shù)據(jù)庫(kù)性能的技術(shù)。
既然redo log能保證事務(wù)的ACID特性,那為什么還會(huì)出現(xiàn),水友提問(wèn)中出現(xiàn)的“數(shù)據(jù)庫(kù)崩潰,丟數(shù)據(jù)”的問(wèn)題呢?一起看下redo log的實(shí)現(xiàn)細(xì)節(jié)。
redo log的三層架構(gòu)?
畫(huà)了一個(gè)丑圖,簡(jiǎn)單說(shuō)明下redo log的三層架構(gòu):
- 粉色,是InnoDB的一項(xiàng)很重要的內(nèi)存結(jié)構(gòu)(In-Memory Structure),日志緩沖區(qū)(Log Buffer),這一層,是MySQL應(yīng)用程序用戶態(tài);
- 屎黃色,是操作系統(tǒng)的緩沖區(qū)(OS cache),這一層,是OS內(nèi)核態(tài);
- 藍(lán)色,是落盤(pán)的日志文件。
redo log最終落盤(pán)的步驟如何?
首先,事務(wù)提交的時(shí)候,會(huì)寫(xiě)入Log Buffer,這里調(diào)用的是MySQL自己的函數(shù)WriteRedoLog;
接著,只有當(dāng)MySQL發(fā)起系統(tǒng)調(diào)用寫(xiě)文件write時(shí),Log Buffer里的數(shù)據(jù),才會(huì)寫(xiě)到OS cache。注意,MySQL系統(tǒng)調(diào)用完write之后,就認(rèn)為文件已經(jīng)寫(xiě)完,如果不flush,什么時(shí)候落盤(pán),是操作系統(tǒng)決定的;
畫(huà)外音:有時(shí)候打日志,明明printf了,tail -f卻看不到,就是這個(gè)原因,操作系統(tǒng)還沒(méi)有刷盤(pán)。
最后,由操作系統(tǒng)(當(dāng)然,MySQL也可以主動(dòng)flush)將OS cache里的數(shù)據(jù),最終fsync到磁盤(pán)上。
操作系統(tǒng)為什么要緩沖數(shù)據(jù)到OS cache里,而不直接刷盤(pán)呢?
這里就是將“每次寫(xiě)”優(yōu)化為“批量寫(xiě)”,以提高操作系統(tǒng)性能。
數(shù)據(jù)庫(kù)為什么要緩沖數(shù)據(jù)到Log Buffer里,而不是直接write呢?
這也是“每次寫(xiě)”優(yōu)化為“批量寫(xiě)”思路的體現(xiàn),以提高數(shù)據(jù)庫(kù)性能。
畫(huà)外音:這個(gè)優(yōu)化思路,非常常見(jiàn),高并發(fā)的MQ落盤(pán),高并發(fā)的業(yè)務(wù)數(shù)據(jù)落盤(pán),都可以使用。 redo log的三層架構(gòu),MySQL做了一次批量寫(xiě)優(yōu)化,OS做了一次批量寫(xiě)優(yōu)化,確實(shí)能極大提升性能,但有什么副作用嗎?
畫(huà)外音:有優(yōu)點(diǎn),必有缺點(diǎn)。
這個(gè)副作用,就是可能丟失數(shù)據(jù):
- 事務(wù)提交時(shí),將redo log寫(xiě)入Log Buffer,就會(huì)認(rèn)為事務(wù)提交成功;
- 如果寫(xiě)入Log Buffer的數(shù)據(jù),write入OS cache之前,數(shù)據(jù)庫(kù)崩潰,就會(huì)出現(xiàn)數(shù)據(jù)丟失;
- 如果寫(xiě)入OS cache的數(shù)據(jù),fsync入磁盤(pán)之前,操作系統(tǒng)崩潰,也可能出現(xiàn)數(shù)據(jù)丟失;
畫(huà)外音:如上文所說(shuō),應(yīng)用程序系統(tǒng)調(diào)用完write之后(不可能每次write后都立刻flush,這樣寫(xiě)日志很蠢),就認(rèn)為寫(xiě)成功了,操作系統(tǒng)何時(shí)fsync,應(yīng)用程序并不知道,如果操作系統(tǒng)崩潰,數(shù)據(jù)可能丟失。
任何脫離業(yè)務(wù)的技術(shù)方案都是耍流氓:
- 有些業(yè)務(wù)允許低效,但不允許一丁點(diǎn)數(shù)據(jù)丟失;
- 有些業(yè)務(wù)必須高性能高吞吐,能夠容忍少量數(shù)據(jù)丟失。
MySQL是如何折衷的呢?
MySQL有一個(gè)參數(shù):
innodb_flush_log_at_trx_commit
能夠控制事務(wù)提交時(shí),刷redo log的策略。
目前有三種策略:
策略一:最佳性能(innodb_flush_log_at_trx_commit=0)
- 每隔一秒,才將Log Buffer中的數(shù)據(jù)批量write入OS cache,同時(shí)MySQL主動(dòng)fsync。
- 這種策略,如果數(shù)據(jù)庫(kù)崩潰,有一秒的數(shù)據(jù)丟失。
策略二:強(qiáng)一致(innodb_flush_log_at_trx_commit=1)
- 每次事務(wù)提交,都將Log Buffer中的數(shù)據(jù)write入OS cache,同時(shí)MySQL主動(dòng)fsync。
- 這種策略,是InnoDB的默認(rèn)配置,為的是保證事務(wù)ACID特性。
策略三:折衷(innodb_flush_log_at_trx_commit=2)
- 每次事務(wù)提交,都將Log Buffer中的數(shù)據(jù)write入OS cache;
- 每隔一秒,MySQL主動(dòng)將OS cache中的數(shù)據(jù)批量fsync。
畫(huà)外音:磁盤(pán)IO次數(shù)不確定,因?yàn)椴僮飨到y(tǒng)的fsync頻率并不是MySQL能控制的。
- 這種策略,如果操作系統(tǒng)崩潰,最多有一秒的數(shù)據(jù)丟失。
畫(huà)外音:因?yàn)镺S也會(huì)fsync,MySQL主動(dòng)fsync的周期是一秒,所以最多丟一秒數(shù)據(jù)。
講了這么多,回到水友的提問(wèn)上來(lái),數(shù)據(jù)庫(kù)崩潰,重啟后丟失了數(shù)據(jù),有很大的可能,是將innodb_flush_log_at_trx_commit參數(shù)設(shè)置為0了,這位水友最好和DBA一起檢查一下InnoDB的配置。
可能有水友要問(wèn),高并發(fā)的業(yè)務(wù),InnoDB運(yùn)用哪種刷盤(pán)策略最合適?
高并發(fā)業(yè)務(wù),行業(yè)最佳實(shí)踐,是使用第三種折衷配置(=2),這是因?yàn)椋?/p>
- 配置為2和配置為0,性能差異并不大,因?yàn)閷?shù)據(jù)從Log Buffer拷貝到OS cache,雖然跨越用戶態(tài)與內(nèi)核態(tài),但畢竟只是內(nèi)存的數(shù)據(jù)拷貝,速度很快;
- 配置為2和配置為0,安全性差異巨大,操作系統(tǒng)崩潰的概率相比MySQL應(yīng)用程序崩潰的概率,小很多,設(shè)置為2,只要操作系統(tǒng)不崩潰,也絕對(duì)不會(huì)丟數(shù)據(jù)。
總結(jié)
(1) 為了保證事務(wù)的ACID特性,理論上每次事務(wù)提交都應(yīng)該刷盤(pán),但此時(shí)效率很低,有兩種優(yōu)化方向:
- 隨機(jī)寫(xiě)優(yōu)化為順序?qū)?/li>
- 每次寫(xiě)優(yōu)化為批量寫(xiě)
(2) redo log是一種順序?qū)?,它有三層架?gòu):
- MySQL應(yīng)用層:Log Buffer
- OS內(nèi)核層:OS cache
- OS文件:log file
(3) 為了滿足不同業(yè)務(wù)對(duì)于吞吐量與一致性的需求,MySQL事務(wù)提交時(shí)刷redo log有三種策略:
- 0:每秒write一次OS cache,同時(shí)fsync刷磁盤(pán),性能好;
- 1:每次都write入OS cache,同時(shí)fsync刷磁盤(pán),一致性好;
- 2:每次都write入OS cache,每秒fsync刷磁盤(pán),折衷;
(4) 高并發(fā)業(yè)務(wù),行業(yè)內(nèi)的最佳實(shí)踐,是:
innodb_flush_log_at_trx_commit=2
知其然,知其所以然,希望大家有收獲。