參數解析如(rú)下(xià):
innodb_flush_log_at_trx_commit = N:
N=0 每隔一(yī)秒(miǎo),把事(shì)務(wù)日志緩存區的數據寫到日志文件中,以及把日志文件的數據刷新(xīn)到磁盤上(shàng);
log buffer 會 每秒(miǎo)寫入到日志文件并刷寫(flush)到磁盤。但(dàn)每次事(shì)務(wù)提交不會有任何影響,也(yě)就(jiù)是 log buffer 的刷寫操作(zuò)和事(shì)務(wù)提交操作(zuò)沒有關(guān)系。在這(zhè)種情況下(xià),MySQL性能(néng)最好(hǎo)(hǎo),但(dàn)如(rú)果 mysqld 進程崩潰,通常會導緻最後 1s 的日志丢失。
N=1 每個(gè)事(shì)務(wù)提交時(shí)候,把事(shì)務(wù)日志從緩存區寫到日志文件中,并且刷新(xīn)日志文件的數據到磁盤上(shàng);
當取值為(wèi) 1 時(shí),每次事(shì)務(wù)提交時(shí),log buffer 會被寫入到日志文件并刷寫到磁盤。這(zhè)也(yě)是默認值。這(zhè)是最安全的配置,但(dàn)由于每次事(shì)務(wù)都需要進行磁盤I/O,所以也(yě)最慢(màn)。
N=2 每事(shì)務(wù)提交的時(shí)候,把事(shì)務(wù)日志數據從緩存區寫到日志文件中;每隔一(yī)秒(miǎo),刷新(xīn)一(yī)次日志文件,但(dàn)不一(yī)定刷新(xīn)到磁盤上(shàng),而是取決于操作(zuò)系統的調度;
當取值為(wèi) 2 時(shí),每次事(shì)務(wù)提交會寫入日志文件,但(dàn)并不會立即刷寫到磁盤,日志文件會每秒(miǎo)刷寫一(yī)次到磁盤。這(zhè)時(shí)如(rú)果 mysqld 進程崩潰,由于日志已經寫入到系統緩存,所以并不會丢失數據;在操作(zuò)系統崩潰的情況下(xià),通常會導緻最後 1s 的日志丢失。
上(shàng)面說(shuō)到的「最後 1s」并不是絕對的,有的時(shí)候會丢失 更多數據。有時(shí)候由于調度的問題,每秒(miǎo)刷寫(once-per-second flushing)并不能(néng)保證 100% 執行。對于一(yī)些(xiē)數據一(yī)緻性和完整性要求不高的應用,配置為(wèi) 2 就(jiù)足夠了(le);如(rú)果為(wèi)了(le)最高性能(néng),可以設置為(wèi) 0。有些(xiē)應用,如(rú)支付服務(wù),對一(yī)緻性和完整性要求很高,所以即使最慢(màn),也(yě)最好(hǎo)(hǎo)設置為(wèi) 1.
當我們設置為(wèi)2 的時(shí)候,Log Thread 會在我們每次事(shì)務(wù)結束的時(shí)候将數據寫入事(shì)務(wù)日志,但(dàn)是這(zhè)裏的寫入僅僅是調用了(le)文件系統的文件寫入操作(zuò)。而我們的文件系統都是有緩存機制的,所以Log Thread 的這(zhè)個(gè)寫入并不能(néng)保證内容真的已經寫入到物理(lǐ)磁盤上(shàng)面完成持久化(huà)的動作(zuò)。文件系統什(shén)麽時(shí)候會将緩存中的這(zhè)個(gè)數據同步到物理(lǐ)磁盤文件Log Thread 就(jiù)完全不知道了(le)。所以,當設置為(wèi)2 的時(shí)候,MySQL Crash 并不會造成數據的丢失,但(dàn)是OS Crash 或者是主機斷電後可能(néng)丢失的數據量就(jiù)完全控制在文件系統上(shàng)了(le)。各種文件系統對于自己緩存的刷新(xīn)機制各不一(yī)樣,大家可以自行參閱相關(guān)的手冊。
sync_binlog = N:
N>0 每向二進制日志文件寫入N條SQL或N個(gè)事(shì)務(wù)後,則把二進制日志文件的數據刷新(xīn)到磁盤上(shàng);
N=0 不主動刷新(xīn)二進制日志文件的數據到磁盤上(shàng),而是由操作(zuò)系統決定;
推薦配置組合:
N=1,1 — 适合數據安全性要求非常高,而且磁盤IO寫能(néng)力足夠支持業務(wù),比如(rú)充值消費系統;
N=1,0 — 适合數據安全性要求高,磁盤IO寫能(néng)力支持業務(wù)不富餘,允許備庫落後或無複制;
N=2,0或2,m(0<m<100) — 适合數據安全性有要求,允許丢失一(yī)點事(shì)務(wù)日志,複制架構的延遲也(yě)能(néng)接受;
N=0,0 — 磁盤IO寫能(néng)力有限,無複制或允許複制延遲稍微長點能(néng)接受,例如(rú):日志性登記業務(wù);
當兩個(gè)參數設置為(wèi)雙1的時(shí)候,寫入性能(néng)最差,sync_binlog=N (N>1 ) innodb_flush_log_at_trx_commit=2 時(shí),(在當前模式下(xià))MySQL的寫操作(zuò)才能(néng)達到最高性能(néng)。
數據安全性
當innodb_flush_log_at_trx_commit和sync_binlog 都為(wèi)1時(shí)是最安全的,在mysqld 服務(wù)崩潰或者服務(wù)器(qì)主機crash的情況下(xià),binary log 隻有可能(néng)丢失最多一(yī)個(gè)語句或者一(yī)個(gè)事(shì)務(wù)。但(dàn)是魚與熊掌不可兼得,都為(wèi)1會導緻頻繁的IO操作(zuò),因此該模式也(yě)是最慢(màn)的一(yī)種方式。
當innodb_flush_log_at_trx_commit設置為(wèi)0,mysqld進程的崩潰會導緻上(shàng)一(yī)秒(miǎo)鍾所有事(shì)務(wù)數據的丢失。
當innodb_flush_log_at_trx_commit設置為(wèi)2,隻有在操作(zuò)系統崩潰或者系統掉電的情況下(xià),上(shàng)一(yī)秒(miǎo)鍾所有事(shì)務(wù)數據才可能(néng)丢失。
雙1适合數據安全性要求非常高,而且磁盤IO寫能(néng)力足夠支持業務(wù),比如(rú)訂單,交易,充值,支付消費系統。雙1模式下(xià),當磁盤IO無法滿足業務(wù)需求時(shí),推薦的做法是innodb_flush_log_at_trx_commit=2 ,sync_binlog=N (N為(wèi)500 或1000) 且使用帶蓄電池後備電源的緩存cache,防止系統斷電異常。