MySQL的WAL（Write-Ahead Logging）機(jī)制

MySQL 里經(jīng)常說到的 WAL技術(shù)，也就是先寫日志，再寫磁盤。

當(dāng)內(nèi)存數(shù)據(jù)頁跟磁盤數(shù)據(jù)頁內(nèi)容不一致的時候，我們成這個內(nèi)存頁為“臟頁”。內(nèi)存數(shù)據(jù)寫入磁盤后，內(nèi)存和磁盤上的數(shù)據(jù)頁內(nèi)容就一致了，稱為“干凈頁”。

MySQL 從 內(nèi)存更新到磁盤的過程，稱為刷臟頁的過程（flush）。

InnoDB 刷臟頁的時機(jī)：

  1. 內(nèi)存中的redo log 寫滿了，這時系統(tǒng)就會停止所有更新操作，把checkoutpoint 往前推，redo log留出空間可以繼續(xù)寫。


往前推進(jìn)之后，就要把兩個點(diǎn)之間的日志對應(yīng)的所有臟頁都 flush 到磁盤上。

這種情況是 InnoDB 要盡量避免的。因?yàn)槌霈F(xiàn)這種情況，整個系統(tǒng)都不能接受更新。更新數(shù)會跌為0。

  1. 系統(tǒng)中內(nèi)存不足時，當(dāng)這個時候需要新的數(shù)據(jù)頁到內(nèi)存中，就要淘汰掉一些數(shù)據(jù)頁，如果淘汰的是“臟頁”，就要先將“臟頁”寫到磁盤。

那么為什么不能直接淘汰所有的內(nèi)存，下次請求的時候，再從磁盤讀入數(shù)據(jù)頁，然后 拿 redo log 出來應(yīng)用？這其實(shí)也是從性能的角度來考慮的，刷臟頁一定寫盤，就保證了每個數(shù)據(jù)頁只有兩種情況：

  • 數(shù)據(jù)頁直接在內(nèi)存里，內(nèi)存里的肯定是正確的，直接返回
  • 內(nèi)存里沒有數(shù)據(jù)，就可以肯定數(shù)據(jù)文件上是正確的結(jié)果，讀入內(nèi)存后返回。 這樣的效率最高。

這種情況在日常應(yīng)用中其實(shí)是常態(tài)。在InnoDB 中，使用緩沖池 （buffer pool）管理內(nèi)存，緩沖池中的內(nèi)存頁有三種狀態(tài)：

  • 還沒有使用的；
  • 使用了并且是干凈頁
  • 使用了并且是臟頁
  1. 數(shù)據(jù)庫空閑的時候刷臟頁。
  2. 數(shù)據(jù)庫正常關(guān)閉的時候，也要把內(nèi)存中所有的臟頁全都flush 到磁盤上。

對性能的影響

刷臟頁是常態(tài)，所以如果出現(xiàn)以下的情況，都會明明顯影響性能：

  • 一個查詢要淘汰的臟頁太多，會導(dǎo)致查詢的響應(yīng)時間明顯變長；
  • 日志寫滿，更新全部堵住，寫性能跌為0，這種情況對于敏感業(yè)務(wù)來說是不能接受的。

InnoDB 刷臟頁的控制策略

首先，需要讓 InnoDB 正確指導(dǎo)系統(tǒng)的 IO 能力，來控制刷臟頁的快慢。

innodb_io_capacity 這個參數(shù)，它會告訴 InnoDB 你的磁盤能力，所以盡量設(shè)置成磁盤的 IOPS。可以使用 fio 工具來獲取。

fio -filename=$filename -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=16k -size=500M -numjobs=10 -runtime=10 -group_reporting -name=mytest

然后，如果你來設(shè)計(jì)策略控制刷臟頁的速度，會參考哪些因素呢？

這個問題可以這么想，如果刷太慢，會出現(xiàn)什么情況？首先是內(nèi)存臟頁太多，其次是 redo log 寫滿。

所以，InnoDB 的刷盤速度就是要參考這兩個因素：一個是臟頁比例，一個是 redo log 寫盤速度。

參數(shù) innodb_max_dirty_pages_pct 是臟頁比例上限，默認(rèn)是 75%。InnoDB 會根據(jù)當(dāng)前的臟頁比例，計(jì)算出一個數(shù)字 F1。

F1(M)
{
  if M>=innodb_max_dirty_pages_pct then
      return 100;
  return 100*M/innodb_max_dirty_pages_pct;
}

InnoDB 寫入日志都會有一個序號，當(dāng)前寫入序號跟 checkpoint 對應(yīng)的序號之間的差值，假設(shè)為N。InnoDB 會根據(jù)N 計(jì)算出 F2.

根據(jù) F1和F2 取其中較大的值為 R，之后引擎就可以按照 Innodb_io_capacity 定義的能力乘以 R% 來控制刷臟頁的速度。

MySQL 中有一個機(jī)制，刷臟頁的時候如果數(shù)據(jù)頁旁邊的數(shù)據(jù)頁也是臟頁，那么就會一起刷掉，而且這個邏輯是可以蔓延的，所以對于每個相鄰的數(shù)據(jù)頁，都會被一起刷。

在 InnoDB 中，innodb_flush_neighbors 參數(shù)就是用來控制這個行為的，值為 1 的時候會有上述的“連坐”機(jī)制，值為 0 時表示不找鄰居，自己刷自己的。

在使用機(jī)械硬盤時，這個優(yōu)化很有意義，可以減少很多隨機(jī) IO。如果使用的是 SSD 這種IOPS 比較高的設(shè)備，可以設(shè)置innodb_flush_neighbors 為0，只刷自己，這個時候 IOPS 往往就不是性能瓶頸了。只刷自己就可以提高刷臟頁的速度，減少 SQL 語句的響應(yīng)時間。

binlog 的寫入機(jī)制

binlog 的寫入機(jī)制比較簡單：事務(wù)執(zhí)行的過程中，先把日志寫到 binlog cache，事務(wù)提交的時候，再把 binlog cache 寫到binlog 文件中。

系統(tǒng)給 binlog cache 分配了一片內(nèi)存，每個線程一個，參數(shù) binglog_cache_size 用于控制單個線程內(nèi) binlog cache 的內(nèi)存大小，超過就要暫存在磁盤。

事務(wù)提交的時候，執(zhí)行器把 binlog cache 里完整事務(wù)寫入到 binlog 中，并清空 binlog cache。

binlog 寫盤狀態(tài)
  • write 指的是把日志寫入到文件系統(tǒng)的 page cache，并沒有吧數(shù)據(jù)持久化到磁盤，所以速度比較快。
  • fsync 是持久化到磁盤的操作，一般情況下， fsync 才會占磁盤的 IOPS。

write 和 fsync 的時機(jī)，是由參數(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è)置成一個比較大的值，可以提升性能。在實(shí)際的業(yè)務(wù)場景中，考慮到丟失日志量的可控性，一般不建議將這個參數(shù)設(shè)成 0，比較常見的是將其設(shè)置為 100~1000 中的某個數(shù)值。但是，將 sync_binlog 設(shè)置為 N，對應(yīng)的風(fēng)險是：如果主機(jī)發(fā)生異常重啟，會丟失最近 N 個事務(wù)的 binlog 日志。

redo log 的寫入機(jī)制

事務(wù)的執(zhí)行過程中，生成的 redo log 是要先寫到 redo log buffer 的。

redo log 三種狀態(tài)：

  • 存在 redo log buffer 中，物理上是在 MySQL 進(jìn)程內(nèi)存中
  • 寫到磁盤（write），但是沒有持久化（fsync），物理上是在文件系統(tǒng)的 page cache 里
  • 持久化磁盤，對應(yīng)的是 hard disk

日志寫到 redo log buffer 是很快的，write 到 page cache 也差不多，但是持久化到磁盤的速度就慢多了。

InnoDB 提供了 innodb_flush_log_at_trx_commit 參數(shù)，取值如下：

  1. 設(shè)置為 0 時，表示每次事務(wù)提交時都只是把 redo log 留在 redo log buffer 中；
  2. 設(shè)置為 1 時，表示每次事務(wù)提交時都將 redo log 直接持久化到磁盤；
  3. 設(shè)置為 2 時，表示每次事務(wù)提交時都只是把 redo log 寫到 page cache。

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

組提交機(jī)制

日志邏輯序列號（log sequence number，LSN）是一個單調(diào)遞增的值，對應(yīng) redo log 的一個個寫入點(diǎn)。每次寫入的長度為 lenght 的 redo log，LSN的值就會加上 length。

LSN 也會寫到 InnoDB 的數(shù)據(jù)頁中，來確保數(shù)據(jù)也不會被多次執(zhí)行重復(fù)的 redo log。
在一組提交里面，組員越多，節(jié)約磁盤 IOPS 的效果越好。在并發(fā)更新的場景下，第一個事務(wù)寫完 redo log buffer 以后，接下來這個 fsync 越晚調(diào)用，組員可能越多，節(jié)約 IOPS 的效果就越好。

  1. binlog_group_commit_sync_delay 參數(shù)，表示延遲多少微秒后才調(diào)用 fsync;
  2. binlog_group_commit_sync_no_delay_count 參數(shù)，表示累積多少次以后才調(diào)用 fsync。

WAL機(jī)制主要得益于兩個方面：

  1. redo log 和binlog 都是順序?qū)懀疟P的順序?qū)懕入S機(jī)寫速度要快；
  2. 組提交機(jī)制，可以大幅度降低磁盤的 IOPS 消耗。

如果你的 MySQL 現(xiàn)在出現(xiàn)了性能瓶頸，而且瓶頸在 IO 上，可以通過哪些方法來提升性能呢？

針對這個問題，可以考慮以下三種方法：

  1. 設(shè)置 binlog_group_commit_sync_delay 和 binlog_group_commit_sync_no_delay_count 參數(shù)，減少 binlog 的寫盤次數(shù)。這個方法是基于“額外的故意等待”來實(shí)現(xiàn)的，因此可能會增加語句的響應(yīng)時間，但沒有丟失數(shù)據(jù)的風(fēng)險。
  2. 將 sync_binlog 設(shè)置為大于 1 的值（比較常見是 100~1000）。這樣做的風(fēng)險是，主機(jī)掉電時會丟 binlog 日志。
  3. 將 innodb_flush_log_at_trx_commit 設(shè)置為 2。這樣做的風(fēng)險是，主機(jī)掉電的時候會丟數(shù)據(jù)。
最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容