MySQL性能下降的原因有哪些

本篇文章為大家展示了MySQL性能下降的原因有哪些，內容簡明扼要并且容易理解，絕對能使你眼前一亮，通過這篇文章的詳細介紹希望你能有所收獲。

SQL 執行突然變慢的原因

在之前講解 MySQL Redo log 時，說到了 WAL 機制，為了保證 MySQL 更新的速度，在進行更新操作時，先將更新內容寫入 redo log，后續系統空閑時，再將 redo log 的內容應用到磁盤。

當內存數據頁（redo log）和磁盤數據頁內容不一致時，將該內存也稱為 “臟頁”。將內存數據寫入到磁盤后，數據一致，內存頁稱為 "干凈頁"。

在內存數據寫入磁盤時，這個過程稱為 flush 過程。SQL 突然執行變得很慢，性能下降。原因就可能和 flush 操作有關。

因為在進行 flush 操作時，更新操作會等待 redo log 的寫入。

引起 flush 操作的原因

場景一：redo log 日志已經記滿。這時系統會停止更新操作，將 check point 向前推進，讓 redo log 留出空間可以繼續寫。

這里假設 CP 到 CP‘ 間隙已經寫入到磁盤，這部分就變成了干凈頁，此時 write pos 就可以寫入這部分區域了。

場景二：系統內存不足，需要新的內存頁時，發現內存不夠用了，就需要淘汰一些數據頁。如果淘汰時，這時數據頁時臟頁，就要將臟頁寫到磁盤。

這時有個問題是，命名 redo log 中的內容已經被記錄到日志中了，假如內存滿了，直接刪除不就可以嗎？下次讀入時，再把 redo log 日志中的內容應用到磁盤。

沒有選擇直接清空內存，是從性能考慮的，因為在查詢數據時，有兩種情況：

• 首先數據頁在內存中，內存是就是正確的結果，直接返回
• 內存里沒有數據，從數據文件上讀入內存。

所以這樣效率比較高。

場景三：MySQL 會在系統空閑時，進入 flush 操作。

場景四：在 MySQL 正常關閉時，會把內存臟頁 flush 到磁盤上。

引起 flush 對性能的影響

對于第三，四場景來說，是比較正常的情況，不需要考慮性能問題。

對于第一種場景，InnoDB 會盡量避免，因為在這種情況下，整個系統不再接受更新。

但有時出現人為的配置錯誤，比如內存為 128 GB，innodb_io_capacity 設置為 20000 的實例。通常建議將 redo log 設置成 4 個 1GB 的文件。但由于配置錯誤，設置成 100M 的文件。

這里由于 redo log 設置的太小，很快就會被寫滿。write pos 一直追著 check point. 這時，系統只能停止所有更新，推進 checkpoint.

表現就是，磁盤 IO 很小，但是出現間歇性的性能下降。

對于第二種場景，內存不夠用的情況，InnoDB 會用緩沖池（buffer pool）管理內存

內存頁在緩沖池中會有三種狀態：

• 沒用使用的數據頁
• 使用了，但是是干凈頁
• 使用了，是臟頁
   

每個數據頁頭部有LSN，8字節，每次修改都會變大。

對比這個 LSN 跟 checkpoint 的 LSN，比checkpoint小的一定是干凈頁

由于 InnoDB 的策略是盡可能使用內存，所以對于長時間運行的庫來說，未被使用的頁面很少。

當發現想讀入的數據頁沒有在內存中時，必須到緩沖池申請數據頁。并會把最久不用得數據頁從內存中淘汰

如果是干凈頁，直接釋放使用
如果是臟頁，必須先刷盤，變成干凈頁才能復用
當時，如果在下面的情況進行刷臟頁，會明顯影響性能：

要淘汰的臟頁太多，導致查詢響應時間較長。
日志寫滿，更新被阻塞。
為了解決這個問題，InnoDB 使用控制臟頁比例的機制，來避免上面的情況。

InooDB 控制刷臟頁的策略

在 InnoDB 中，通過 innodb_io_capacity 參數，來告訴 InnoDB 目前主機的磁盤能力是多少，這個值建議設置成磁盤的 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 導致的性能問題很常見，比如有時系統吞吐量（TPS）很低，寫入很慢，但是磁盤 IO 并不高。就有可能是該參數設置的不正確。例如，innodb_io_capacity 的值設置的很低，但是磁盤用的 SSD，導致 InooDB 認為系統能力很差，所以刷臟頁特別慢。造成臟頁累計，影響查詢和更新性能。

InnoDB 在刷盤時主要考慮兩個因素：

1. 臟頁的比例
    
2. redo log 寫盤速度
    

會通過這兩個因素單獨先算出兩個數字。

innodb_max_dirty_pages_pct 臟頁比例上限，默認 75%.

InnoDB 會根據臟頁的比例（M），算出范圍在 0 - 100 的數字。，過程稱為 F1（M）

# M 臟頁比例
F1(M)
{
 if M>=innodb_max_dirty_pages_pct then
   return 100;
 return 100*M/innodb_max_dirty_pages_pct;
}

除此之外，InnoDB 每次寫入日志都會有一個序號 N. 然后根據 N 再算出一個 0 到 100 的數字，這個計算過程稱為 F2（N）

N: 當前寫入的序號和 checkpoint 對應序號之間的差值。

最后，根據 F1（M）和 F2（N）兩個值，取其中較大的值為 R，之后引擎就可以按照 innodb_io_capacity * R 來控制刷臟頁的速度。

所以無論是在查詢，需要加載數據到內存數據頁，而淘汰臟頁。還是更新時，導致刷盤操作都有可能造成 MySQL 的性能下降。

為了避免這種情況，要合理的設置 innodb_io_capacity 的值，平時要多關注臟頁比例，不讓其接近 75%.

其中臟頁比例可以通過下面的方式獲取：

mysql> use performance_schema;
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 臟頁的話，如果這個該臟頁的鄰居也是臟頁的話，就會把這個鄰居一起刷掉，如果恰好旁邊還是臟頁的話，就會一直連坐。這時導致 flush 過慢的原因。

可以通過 innodb_flush_neighbors 來控制該行為，值為 1 打開上述機制，為 0 則關閉。

對于機械硬盤來說，是可以減少很多隨機 IO ，因為機械硬盤 IOPS 一般就幾百，減少隨機 IO 就意味著性能提升。

但如果用 SSD 這類 IOPS 較高的設備，IOPS 往往不是瓶頸，關閉就好，減少 SQL 語句的響應時間。

在 8.0 中，已經默認是 0 了.

總結

這篇中，主要介紹了 WAL 時的 flush 操作可能會造成 MySQL 突然的性能下降。

引起的原因一般是由于內存不夠導致的，進而可以通過設置合適的 innodb_io_capacity 參數，來控制 InnoDB flush 的過程。

上述內容就是MySQL性能下降的原因有哪些，你們學到知識或技能了嗎？如果還想學到更多技能或者豐富自己的知識儲備，歡迎關注億速云行業資訊頻道。