還在摸魚發文的我，自己之前寫的SQL也都測過還是挺快的，突然領導在緊急群里艾特我！

看了下監控圖，發現總有一些時刻看起來隨機持續時間又短，難以復現，

WAL 機制：InnoDB在處理更新陳述句時，只做寫日志這個磁盤操作（redo log），在更新記憶體寫完redo log后，就回傳給客戶端，本次更新成功，

想象你是一個店老板：

資料檔案 - 用來記賬的賬本
redo log - 記賬用的粉板
記憶體 - 你的記憶

你總要找時間把賬本更新下，即把記憶體里的資料寫盤（flush），在flush前，撕蔥的賒賬總額，其實跟你手中賬本的記錄不一致，因為撕蔥今天的賒賬金額還只在粉板上，而賬本里的記錄是老的，還沒把今天的賒賬算進去，

記憶體資料頁跟磁盤資料頁內容不一致，該記憶體頁為“臟頁”
記憶體資料寫盤后，記憶體資料頁和磁盤資料頁內容一致了，稱為“干凈頁”

無論臟頁 or 干凈頁，都指記憶體資料頁，

撕蔥賒賬示意圖

假設原來撕蔥欠賬10元，這次又要賒10元，

所以平時執行很快的更新操作，其實就是在寫記憶體和日志，而MySQL抖擻地那一下，可能就是在刷臟頁（flush），

領導又開始懵逼了

那何時會觸發MySQL的flush呀？
想想店老板何時會把賒賬記錄落實到賬本？

1 redo log寫滿

粉板寫滿了，這時再有更多的撕蔥來賒賬，老板必須放下手活，將粉板上的記錄擦掉一些，騰空間以繼續記賬，當然了，擦掉前也必須先將正確賬目記錄到賬本，
用技識訓語描述，就是InnoDB的redo log寫滿了，這時系統會停止所有更新操作，把checkpoint往前推進，redo log留出空間可以繼續寫，

redo log狀態圖

checkpoint 不是隨便往前修改一下位置的，比如上圖，把checkpoint位置從CP ~ CP’，就需要將兩個點之間的日志（淺綠色部分），對應的所有臟頁都flush，之后，write pos ~ CP’就可再寫redo log，

2 系統記憶體不足

可以這么理解，這天生意太好，要記的事太多，老板發現自己快記不住了，趕緊找出賬本把撕蔥這筆賬先加進去，
當需要新的記憶體頁，而記憶體不夠用的時候，就要淘汰一些資料頁，空出記憶體給別的資料頁用，若淘汰的是“臟頁”，就要先將臟頁寫到磁盤，
領導又開始好奇了

這時難道不能直接把記憶體淘汰掉，下次需要請求時，從磁盤讀入資料頁，然后拿redo log出來應用不就行了？
這里其實是從性能考慮，若刷臟頁一定會寫盤，就保證了每個資料頁有兩種狀態：

記憶體里存在，記憶體里就肯定是正確的結果，直接回傳
記憶體里無資料，就可以肯定資料檔案上是正確的結果，讀入記憶體后回傳，
這樣的效率最高，

3 MySQL認為系統“空閑”時

生意不忙時自然沒啥忙的，店老板也閑著，不如更新下賬本，
MySQL空閑機會總是少的，可能很快把日志寫滿，所以MySQL要合理安排時間，即使很忙，也要見縫插針地找時間，只要有機會就刷一點“臟頁”，

4 MySQL正常關閉

年底要關門回家過年了，清算賬目，
這時候掌柜要把所有賬都記到賬本上，這樣過完年重新開張的時候，就能就著賬本明確賬目情況了，
這時MySQL會把記憶體的臟頁都flush，下次啟動時，就能直接從磁盤讀資料，啟動速度會很快，

領導開始犯難了，這么多場景都會觸發，它們會對線上穩定性有啥影響嗎？

各場景的性能開銷

第3種MySQL空閑時的操作，系統本就無壓力，第4種MySQL還本來就要關閉了，所以這兩種情況你也都不會去關注性能，

第1種：“redo log寫滿，要flush臟頁”，這種情況是InnoDB要盡量避免的，因為此時，整個系統就不能再接受更新，所有更新都必須堵住，
從監控上看，這時更新數會跌為0，

第2種：“記憶體不夠了，要先將臟頁flush”，這種情況其實就是常態，InnoDB用緩沖池（buffer pool）管理記憶體，緩沖池中的記憶體頁有如下狀態：

還沒有使用的
使用了并且是干凈頁
使用了并且是臟頁，

InnoDB的策略是盡量使用記憶體，因此對于一個長時間運行的庫來說，未被使用的頁面很少，

當要讀入的資料頁不在記憶體時，就必須到緩沖池中申請一個資料頁，這時只能把 最久不使用 的資料頁從記憶體中淘汰，視淘汰的物件不同：

干凈頁
直接釋放出來復用
臟頁
必須將臟頁先刷盤，變干凈后才能復用

所以，刷臟頁雖然是常態，但出現以下這兩種情況，都會明顯影響性能：

一個查詢要淘汰的臟頁個數太多，會導致查詢的回應時間明顯變長
日志寫滿，更新全部堵住，寫性能跌為0，這種情況對敏感業務來說，是不能接受的，

所以，InnoDB要有控制臟頁比例的機制，來盡量避免上面的這兩種情況，
哦豁，只見領導又放大了眼睛，愿聞其詳，InnoDB自己有哪些刷臟頁的控制策略呢?

不要急，都會和你說的！

首先要正確地告訴InnoDB所在主機的IO能力，這樣InnoDB才能知道需要全力刷臟頁時，可以刷多快，

這就是innodb_io_capacity引數，告訴InnoDB你的磁盤能力，推薦設定成磁盤的IOPS，磁盤的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

由于錯誤設定innodb_io_capacity引數導致的性能問題很多，比如，有時一個庫的性能，可能MySQL的寫入速度很慢，TPS很低，但資料庫主機的IO壓力并不大，
主機磁盤用的SSD，但 innodb_io_capacity 值設定300，于是，InnoDB認為這個系統的能力就這么差，所以刷臟頁特別慢，甚至比臟頁生成速度還慢，就造成臟頁累積，影響了查詢和更新性能，

雖然我們現在已經定義了“全力刷臟頁”的行為，但平時總不能一直是全力刷，畢竟磁盤能力不能只用來刷臟頁，還需要服務用戶請求，

InnoDB怎么控制引擎按照“全力”的百分比來刷臟頁，

如果你來設計策略控制刷臟頁的速度，會參考哪些因素呢？
如果刷太慢，會出現什么情況？

記憶體臟頁太多
redo log寫滿

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

InnoDB會根據這兩個因素先單獨算出兩個數字，

引數innodb_max_dirty_pages_pct是臟頁比例上限，默認75%，InnoDB會根據當前的臟頁比例（假設為M），算出一個范圍在0到100之間的數字，計算這個數字的偽代碼類似這樣：

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

InnoDB每次寫入的日志都有一個序號，當前寫入的序號跟checkpoint對應的序號之間的差值，假設為N，InnoDB會根據這個N算出一個范圍在0到100之間的數字，這個計算公式可以記為F2(N)，N越大，算出來的值越大，

然后，根據上述算得的F1(M)和F2(N)兩個值，取其中較大的值記為R，之后引擎就可以按照innodb_io_capacity定義的能力乘以 R% 控制刷臟頁的速度，

F1、F2就是通過臟頁比例和redo log寫入速度算出來的兩個值，

InnoDB刷臟頁速度策略

InnoDB會在后臺刷臟頁，而刷臟頁程序要將記憶體頁落盤，所以，無論是你的查詢陳述句在需要記憶體時可能要求淘汰一個臟頁，還是由于刷臟頁的邏輯會占用IO資源并可能影響到你的更新陳述句，都可能導致MySQL抖動，

要盡量避免這種情況，就要合理配置innodb_io_capacity，日常也要多關注臟頁比例，不要讓它經常接近75%，

其中，臟頁比例是通過

Innodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_total

得到的，具體命令參考如下代碼：

mysql> select VARIABLE_VALUE into @a from global_status 
where VARIABLE_NAME = 'Innodb_buffer_pool_pages_dirty';

select VARIABLE_VALUE into @b from global_status 
where VARIABLE_NAME = 'Innodb_buffer_pool_pages_total';

select @a/@b;

再看一個策略，

一旦一個查詢請求需要在執行程序中先flush掉一個臟頁，這個查詢就可能要比平時慢，而MySQL中的一個機制，可能讓你的查詢會更慢：在準備刷一個臟頁的時候，如果這個資料頁旁邊的資料頁剛好是臟頁，就會把這個“鄰居”也帶著一起刷掉；而且這個把“鄰居”拖下水的邏輯還可以繼續蔓延，也就是對于每個鄰居資料頁，如果跟它相鄰的資料頁也還是臟頁的話，也會被放到一起刷，

在InnoDB中，innodb_flush_neighbors 引數控制這個行為，值為1時會有上述的“連坐”，值為0時表示不找鄰居，自己刷自己的，

找“鄰居”這個優化在機械硬碟時代是很有意義的，可以減少很多隨機IO，機械硬碟的隨機IOPS一般只有幾百，相同的邏輯操作減少隨機IO就意味著系統性能的大幅度提升，

而如果使用的是SSD這類IOPS比較高的設備的話，建議把innodb_flush_neighbors設成0，因為這時候IOPS往往不是瓶頸，而“只刷自己”，就能更快地執行完必要的刷臟頁操作，減少SQL陳述句回應時間，
MySQL 8.0中，innodb_flush_neighbors引數默認值已是0，

總結

WAL機制后續需要的刷臟頁操作和執行時機，利用WAL技術，資料庫將隨機寫轉換成了順序寫，大大提升了資料庫的性能，

但是，由此也帶來了記憶體臟頁的問題，臟頁會被后臺執行緒自動flush，也會由于資料頁淘汰而觸發flush，而刷臟頁的程序由于會占用資源，可能會讓你的更新和查詢陳述句的回應時間長，

轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/277088.html

標籤：其他

上一篇：2021 mathorcup c題思路

下一篇：五年了，我在 CSDN 的兩個一百萬。