12 | 為什么我的MySQL會“抖”一下？
一條 SQL 陳述句，正常執行的時候特別快，但是有時也不知道怎么回事，它就會變得特別慢，并且這樣的場景很難復現，它不只隨機，而且持續時間還很短，
你的 SQL 陳述句為什么變“慢”了
記憶體里的資料寫入磁盤的程序，叫做 flush，在這個 flush 操作執行之前，記憶體里的資料和磁盤里的是不一致的，
當記憶體資料頁跟磁盤資料頁內容不一致的時候，我們稱這個記憶體頁為“臟頁”，記憶體資料寫入到磁盤后，記憶體和磁盤上的資料頁的內容就一致了，稱為“干凈頁”，
平時執行很快的更新操作，其實就是在寫記憶體和日志，而 MySQL 偶爾“抖”一下的那個瞬間，可能就是在刷臟頁（flush）
什么情況會引發資料庫的 flush 程序
第一種場景是InnoDB 的redo log 寫滿了，這時候系統會停止所有更新操作，把 checkpoint 往前推進，redo log 留出空間可以繼續寫，
把 checkpoint 位置從 CP 推進到 CP’，就需要將兩個點之間的日志（淺綠色部分），對應的所有臟頁都 flush 到磁盤上，之后，圖中從 write pos 到 CP’之間就是可以再寫入的 redo log 的區域，
第二種場景是系統記憶體不足，當需要新的記憶體頁，而記憶體不夠用的時候，就要淘汰一些資料頁，空出記憶體給別的資料頁使用，如果淘汰的是“臟頁”，就要先將臟頁寫到磁盤，
Q：如果直接淘汰記憶體，下次需要請求該頁時，直接從磁盤讀入資料頁，然后拿 redo log 出來應用效率不好嘛？
A：
如果刷臟頁一定會寫盤，就保證了每個資料頁有兩種狀態：
-
一種是記憶體里存在，記憶體里就肯定是正確的結果，直接回傳；
-
另一種是記憶體里沒有資料，就可以肯定資料檔案上是正確的結果，讀入記憶體后回傳，
這樣的效率最高，
第三種場景是 MySQL 認為系統“空閑”的時候
第四種場景是 MySQL 正常關閉的情況，這時候，MySQL 會把記憶體的臟頁都 flush 到磁盤上，這樣下次 MySQL 啟動的時候，就可以直接從磁盤上讀資料，啟動速度會很快，
上面四種場景對性能的影響
后兩種情景不會太關注“性能”問題，主要來分析前兩種場景下的性能問題，
第一種是“redo log 寫滿了，要 flush 臟頁”，這種情況是 InnoDB 要盡量避免的，因為出現這種情況的時候，整個系統就不能再接受更新了，所有的更新都必須堵住，如果你從監控上看，這時候更新數會跌為 0，
第二種是“記憶體不夠用了，要先將臟頁寫到磁盤”，這種情況其實是常態，
InnoDB 用緩沖池（buffer pool）管理記憶體，緩沖池中的記憶體頁有三種狀態：
- 第一種是，還沒有使用的；
- 第二種是，使用了并且是干凈頁；
- 第三種是，使用了并且是臟頁，
InnoDB 的策略是盡量使用記憶體，因此對于一個長時間運行的庫來說，未被使用的頁面很少，
而當要讀入的資料頁沒有在記憶體的時候，就必須到緩沖池中申請一個資料頁，這時候只能把最久不使用的資料頁從記憶體中淘汰掉：
如果要淘汰的是一個干凈頁，就直接釋放出來復用；
但如果是臟頁，就必須將臟頁先刷到磁盤，變成干凈頁后才能復用，
所以，刷臟頁雖然是常態，但是出現一個查詢要淘汰的臟頁個數太多，會導致查詢的回應時間明顯變長，明顯影響性能
所以，InnoDB 需要有控制臟頁比例的機制，來盡量避免上面的這兩種情況，
InnoDB 刷臟頁的控制策略
首先，要正確地告訴 InnoDB 所在主機的 IO 能力，這樣 InnoDB 才能知道需要全力刷臟頁的時候，可以刷多快，通過設定 innodb_io_capacity 這個引數為磁盤的 IOPS 來避免浪費磁盤性能，
畢竟磁盤能力不能只用來刷臟頁，還需要服務用戶請求，所以接下來，我們就一起看看 InnoDB 怎么控制引擎按照“全力”的百分比來刷臟頁，
Q：如果你來設計策略控制刷臟頁的速度，會參考哪些因素呢？
A：
如果刷太慢，會出現什么情況？
首先是記憶體臟頁太多，其次是 redo log 寫滿，
所以，InnoDB 的刷盤速度就是要參考這兩個因素：一個是臟頁比例，一個是 redo log 寫盤速度，
引數 innodb_max_dirty_pages_pct 是臟頁比例上限，默認值是 75%，
F1：InnoDB 會根據當前的臟頁比例（假設為 M），算出一個范圍在 0 到 100 之間的F1
F2：InnoDB 每次寫入的日志都有一個序號，當前寫入的序號跟 checkpoint 對應的序號之間的差值為 N，InnoDB 會根據這個 N 算出一個范圍在 0 到 100 之間的數字F2
根據上述算得的 F1 和 F2兩個值，取其中較大的值記為 R，之后引擎就可以按照 innodb_io_capacity 定義的能力乘以 R% 來控制刷臟頁的速度，
總結：
合理地設定 innodb_io_capacity 的值，并且平時要多關注臟頁比例，不要讓它經常接近 75%，
一個有趣的策略
一旦一個查詢請求需要在執行程序中先 flush 掉一個臟頁時，這個查詢就可能要比平時慢了，而 MySQL 中的一個機制，可能讓你的查詢會更慢：
在準備刷一個臟頁的時候，如果這個資料頁旁邊的資料頁剛好是臟頁，就會把這個“鄰居”也帶著一起刷掉；而且這個把“鄰居”拖下水的邏輯還可以繼續蔓延，也就是對于每個鄰居資料頁，如果跟它相鄰的資料頁也還是臟頁的話，也會被放到一起刷，
在 InnoDB 中，innodb_flush_neighbors 引數就是用來控制這個行為的，值為 1 的時候會有上述的“連坐”機制，值為 0 時表示不找鄰居，自己刷自己的，
在 MySQL 8.0 中，innodb_flush_neighbors 引數的默認值是 0
Q：一個記憶體配置為 128GB、innodb_io_capacity 設定為 20000 的大規格實體，正常會建議你將 redo log 設定成 4 個 1GB 的檔案，
但如果你在配置的時候不慎將 redo log 設定成了 1 個 100M 的檔案，會發生什么情況呢？又為什么會出現這樣的情況呢？
A：
每次事務提交都要寫 redo log，如果設定太小，很快就會被寫滿，也就是下面這個圖的狀態，這個“環”將很快被寫滿，write pos 一直追著 CP，
這時候系統不得不停止所有更新，去推進 checkpoint，
這時，看到的現象是磁盤壓力很小，但是資料庫出現間歇性的性能下跌，
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/500216.html
標籤：其他
上一篇：SQL函式-聚合函式