高峰期臨時提升性能方法朴读。跟可靠性有關屹徘。如何保證binlog 和?redo log 寫入磁盤。

WAL 機制（第 2 篇衅金、第 9 篇噪伊、第 12 篇和第 15 篇）: redo log 和 binlog 持久化磁盤簿煌，異常重啟可恢復。

一鉴吹、binlog 的寫入機制

日志寫到 binlog cache（write）姨伟，binlog cache寫 binlog(提交時，并清空cache)

一個事務binlog 不能被拆開豆励，確保一次性寫入夺荒。給 binlog cache內(nèi)存（每線程一個，共用 binlog）良蒸，binlog_cache_size 控制線程 binlog cache大小技扼，超過暫存磁盤。

圖 1 binlog 寫盤狀態(tài)??

fsync：持久化到磁盤占 IOPS嫩痰。

sync_binlog 控制 write 和 fsync 時機：

（1）0 只 write剿吻，不 fsync； //考慮到丟失日志量可控性串纺，不設 0丽旅，常100~1000 。IO 瓶頸設置大纺棺，提升性能榄笙。

（2）1? fsync；?

（3）=N(N>1)? write祷蝌，累積 N 個事務fsync茅撞。//風險：異常重啟，丟失 N 個事務 binlog 日志杆逗。

二乡翅、redo log 的寫入機制

redo log 寫到 redo log buffer （不需每次持久化磁盤，異常重啟丟罪郊。沒提交沒損失），也可能持久化到磁盤

圖 2 MySQL redo log 存儲狀態(tài)??

1.?紅： redo log buffer 中尚洽，MySQL 進程內(nèi)存（快）

2.?黃：寫到磁盤 (write)悔橄，沒有持久化（fsync)， page cache 里（快）

3.?綠：持久化到磁盤 hard disk（慢）

2.1 innodb_flush_log_at_trx_commit?控制 redo log 寫入：

1.? 0 腺毫，只是把redo log 留 redo log buffer 中;

2.? 1癣疟，將 redo log 持久化磁盤；

3.? 2 只是把 redo log 寫到 page cache

后臺線程每隔 1 秒把 redo log buffer 日志潮酒，write 到 page cache睛挚， fsync到磁盤。

除此還有兩種場景：沒有提交事務redo log急黎，也可到磁盤：

1.?redo log buffer 占用空間快達到innodb_log_buffer_size 一半時扎狱，后臺線程會主動寫盤侧到。只是 write，沒 fsync

2.??并行事務提交淤击，順帶將?redo log??buffer 到磁盤匠抗。事務 A 執(zhí)行一半，已寫一些 redo log 到 buffer 中污抬，另外事務 B 提交汞贸，innodb_flush_log_at_trx_commit =1，B 持久化帶 A?

2.2兩階段提交

先 prepare：寫 binlog印机，最后redo log commit矢腻。

innodb_flush_log_at_trx_commit =1，redo log? prepare 就持久化一次射赛，崩潰恢復依賴于 prepare? redo log多柑，再加 binlog （第 15 篇文章）。

每秒一次后臺輪詢刷盤咒劲，加崩潰恢復顷蟆，commit 時不需 fsync ，只write?

“雙 1”配置 sync_binlog 和innodb_flush_log_at_trx_commit 都 1腐魂。提交前兩次刷盤帐偎，redo log（prepare 階段）、 binlog蛔屹。

2.3組提交（group commit）機制

TPS 每秒兩萬削樊，就會寫四萬次磁盤。用具測磁盤能力兩萬兔毒，怎么能實現(xiàn)兩萬TPS漫贞？

日志序列號（log sequence number，LSN）單調(diào)遞增育叁，對應 redo log 一個個寫入點迅脐。每寫入長度 length 的 redo log， LSN 加上 length豪嗽。

也寫數(shù)據(jù)頁中谴蔑，確保數(shù)據(jù)頁不執(zhí)行重復 redo log

圖 3 redo log 組提交

三個并發(fā)事務 (trx1, trx2, trx3)? prepare ，寫完 redo log buffer持久化到磁盤龟梦，LSN? 50隐锭、120 和 160。

1. trx1 第一到達计贰，選為leader钦睡；

2.? trx1 開始寫盤，已有三個事務躁倒，LSN變160荞怒；

3.? trx1寫盤時 LSN=160洒琢， trx1 返回時，所有 LSN<=160 redo log都持久化到磁盤挣输；trx2 纬凤、 trx3 直接返回

組提交里，組員越多撩嚼，節(jié)約磁盤 IOPS 越好停士。單線程壓測，一個事務對應一次持久化操作了完丽。

拖時間優(yōu)化：redo log buffer 后恋技， fsync 越晚調(diào)用，組員越多

圖 4 兩階段提交

寫 binlog 兩步：從 binlog cache中寫到磁盤上 binlog 文件逻族；fsync 持久化蜻底。

圖 5 兩階段提交細化

多個事務binlog 寫完一起持久化，減少 IOPS 消耗聘鳞。

第 3 步快薄辅，binlog? write 和 fsync 間隔時間短，一起持久化binlog 比較少抠璃，binlog 組提交效果不如 redo log 效果好站楚。

2.4 提升 binlog 組提交

1.? binlog_group_commit_sync_delay 延遲多少秒后調(diào)用fsync;

2.? binlog_group_commit_sync_no_delay_count 累積多少次調(diào)用 fsync。

一個滿足就ok搏嗡；binlog_group_commit_sync_delay? 0 count無效

WAL 減少磁盤寫窿春，每次提交都寫 redo log 和 binlog，讀寫次數(shù)也沒少呀采盒？WAL 機制主要得益于：

1. redo log 和 binlog 都順序寫旧乞，比隨機寫快；

2. 組提交降低磁盤IOPS 消耗

2.5 IO性能瓶頸提升方法

1.? 設置binlog_group_commit_sync_delay 和binlog_group_commit_sync_no_delay_count 減少 binlog 的寫盤次數(shù)磅氨〕咂埽可能會增加語句響應時間，沒有丟失數(shù)據(jù)風險烦租。

2.? sync_binlog 設置>1 的值（比較常見是 100~1000）决瞳。

3.? 將innodb_flush_log_at_trx_commit 設置為 2。和0 性能差不多左权，異常重啟不會丟數(shù)據(jù)，主機掉電會

小結

第 2 篇和第 15 篇：MySQL 如何保證 crash-safe（redo log 和 binlog 完整）痴颊。今天：MySQL 怎么保證 redo log 和 binlog 完整”赏迟。三篇串起對 crash-safe更清晰理解

問題 1：?update 后，hexdump 看 ibd 文件內(nèi)容蠢棱，為什么沒有看到數(shù)據(jù)改變锌杀？

?WAL 機制原因甩栈。update 后，InnoDB 只寫完 redo log糕再、內(nèi)存量没，沒來得及寫磁盤。

問題2：為什么 binlog cache是線程自己維護突想，redo log buffer 全局共用殴蹄？

binlog 不能打斷。完成后一起寫到文件猾担。

問題 3：事務執(zhí)行期間袭灯，沒提交，crash绑嘹，redo log肯定丟稽荧，會不會導致主備不一致？

不會工腋。binlog 也在 binlog cache里姨丈，crash后都沒有

數(shù)據(jù)庫crash-safe 保證：

1. 客戶端收到事務成功消息，事務持久化擅腰；

2.? 客戶端收到事務失敗（主鍵沖突蟋恬、回滾等）的消息，事務失斕韫摹筋现；

3.? 客戶端收到“執(zhí)行異常”消息，重連后查詢當前狀態(tài)來繼續(xù)后續(xù)箱歧。保證內(nèi)部（數(shù)據(jù)和日志之間矾飞，主庫和備庫之間）一致

思考題

生產(chǎn)庫設置是“雙 1”嗎？ 什么場景下改成“非雙 1”呀邢？基于什么決定的洒沦？

sync_binlog?和innodb_flush_log_at_trx_commit?都 1。提交前兩次刷盤价淌，redo log（prepare 階段）申眼、?binlog。

設置可能有損蝉衣，異常止損方案是什么括尸？

1.? 業(yè)務高峰期。

2. 備庫延遲病毡，盡快趕上主庫濒翻。

3.? 備份恢復主庫副本，應用 binlog 過程，這個跟上一種場景類似有送。

4.? 批量導入數(shù)據(jù)時

生產(chǎn)庫innodb_flush_log_at_trx_commit=2淌喻、sync_binlog=1000。

從庫設置binlog_group_commit_sync_delay 和 binlog_group_commit_sync_no_delay_count導致一直延遲雀摘。主庫設置減少 binlog 寫盤壓力裸删。備庫設置受拖累，尤其“快要追上”時阵赠。追主備用“非雙 1”（追上改回）涯塔。

評論1

sync_binlog和binlog_group_commit_sync_no_delay_count區(qū)別

sync_binlog = N

binlog_group_commit_sync_no_delay_count = M

binlog_group_commit_sync_delay = 很大值

fsync什么時候發(fā)生呀，min(N,M)嗎豌注？

sync_binlog搭配binlog_group_commit_sync_delay也可以實現(xiàn)組提交伤塌？

答：N次后刷盤，再進入(sync_delay和no_delay_count)邏輯轧铁；Sync_delay很大達到no_delay_count才刷每聪；

sync_binlog = 0? binlog_group_commit_sync_no_delay_count = 10 累計10個事務fsync一次？答：等待齿风，等完不調(diào)fsync??