有時(shí)會(huì)碰到這樣的情況，一條 SQL 在平時(shí)執(zhí)行沒(méi)問(wèn)題，很快。但是突然某個(gè)時(shí)間執(zhí)行的就會(huì)很慢，而且這種場(chǎng)景并不能復(fù)現(xiàn)，只能隨機(jī)發(fā)送的。

SQL 執(zhí)行突然變慢的原因

在之前講解 MySQL Redo log 時(shí)，說(shuō)到了 WAL 機(jī)制，為了保證 MySQL 更新的速度，在進(jìn)行更新操作時(shí)，先將更新內(nèi)容寫入 redo log，后續(xù)系統(tǒng)空閑時(shí)，再將 redo log 的內(nèi)容應(yīng)用到磁盤。

當(dāng)內(nèi)存數(shù)據(jù)頁(yè)（redo log）和磁盤數(shù)據(jù)頁(yè)內(nèi)容不一致時(shí)，將該內(nèi)存也稱為 “臟頁(yè)”。將內(nèi)存數(shù)據(jù)寫入到磁盤后，數(shù)據(jù)一致，內(nèi)存頁(yè)稱為 "干凈頁(yè)"。

在內(nèi)存數(shù)據(jù)寫入磁盤時(shí)，這個(gè)過(guò)程稱為 flush 過(guò)程。SQL 突然執(zhí)行變得很慢，性能下降。原因就可能和 flush 操作有關(guān)。

因?yàn)樵谶M(jìn)行 flush 操作時(shí)，更新操作會(huì)等待 redo log 的寫入。

引起 flush 操作的原因

場(chǎng)景一：redo log 日志已經(jīng)記滿。這時(shí)系統(tǒng)會(huì)停止更新操作，將 check point 向前推進(jìn)，讓 redo log 留出空間可以繼續(xù)寫。

這里假設(shè) CP 到 CP‘ 間隙已經(jīng)寫入到磁盤，這部分就變成了干凈頁(yè)，此時(shí) write pos 就可以寫入這部分區(qū)域了。

場(chǎng)景二：系統(tǒng)內(nèi)存不足，需要新的內(nèi)存頁(yè)時(shí)，發(fā)現(xiàn)內(nèi)存不夠用了，就需要淘汰一些數(shù)據(jù)頁(yè)。如果淘汰時(shí)，這時(shí)數(shù)據(jù)頁(yè)時(shí)臟頁(yè)，就要將臟頁(yè)寫到磁盤。

這時(shí)有個(gè)問(wèn)題是，命名 redo log 中的內(nèi)容已經(jīng)被記錄到日志中了，假如內(nèi)存滿了，直接刪除不就可以嗎？下次讀入時(shí)，再把 redo log 日志中的內(nèi)容應(yīng)用到磁盤。

沒(méi)有選擇直接清空內(nèi)存，是從性能考慮的，因?yàn)樵诓樵償?shù)據(jù)時(shí)，有兩種情況：

• 首先數(shù)據(jù)頁(yè)在內(nèi)存中，內(nèi)存是就是正確的結(jié)果，直接返回
• 內(nèi)存里沒(méi)有數(shù)據(jù)，從數(shù)據(jù)文件上讀入內(nèi)存。

所以這樣效率比較高。

場(chǎng)景三：MySQL 會(huì)在系統(tǒng)空閑時(shí)，進(jìn)入 flush 操作。

場(chǎng)景四：在 MySQL 正常關(guān)閉時(shí)，會(huì)把內(nèi)存臟頁(yè) flush 到磁盤上。

引起 flush 對(duì)性能的影響

對(duì)于第三，四場(chǎng)景來(lái)說(shuō)，是比較正常的情況，不需要考慮性能問(wèn)題。

對(duì)于第一種場(chǎng)景，InnoDB 會(huì)盡量避免，因?yàn)樵谶@種情況下，整個(gè)系統(tǒng)不再接受更新。

但有時(shí)出現(xiàn)人為的配置錯(cuò)誤，比如內(nèi)存為 128 GB，innodb_io_capacity 設(shè)置為 20000 的實(shí)例。通常建議將 redo log 設(shè)置成 4 個(gè) 1GB 的文件。但由于配置錯(cuò)誤，設(shè)置成 100M 的文件。

這里由于 redo log 設(shè)置的太小，很快就會(huì)被寫滿。write pos 一直追著 check point. 這時(shí)，系統(tǒng)只能停止所有更新，推進(jìn) checkpoint.

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

對(duì)于第二種場(chǎng)景，內(nèi)存不夠用的情況，InnoDB 會(huì)用緩沖池（buffer pool）管理內(nèi)存

內(nèi)存頁(yè)在緩沖池中會(huì)有三種狀態(tài)：

• 沒(méi)用使用的數(shù)據(jù)頁(yè)
• 使用了，但是是干凈頁(yè)
• 使用了，是臟頁(yè)
  

每個(gè)數(shù)據(jù)頁(yè)頭部有LSN，8字節(jié)，每次修改都會(huì)變大。

對(duì)比這個(gè) LSN 跟 checkpoint 的 LSN，比checkpoint小的一定是干凈頁(yè)

由于 InnoDB 的策略是盡可能使用內(nèi)存，所以對(duì)于長(zhǎng)時(shí)間運(yùn)行的庫(kù)來(lái)說(shuō)，未被使用的頁(yè)面很少。

當(dāng)發(fā)現(xiàn)想讀入的數(shù)據(jù)頁(yè)沒(méi)有在內(nèi)存中時(shí)，必須到緩沖池申請(qǐng)數(shù)據(jù)頁(yè)。并會(huì)把最久不用得數(shù)據(jù)頁(yè)從內(nèi)存中淘汰

如果是干凈頁(yè)，直接釋放使用
如果是臟頁(yè)，必須先刷盤，變成干凈頁(yè)才能復(fù)用
當(dāng)時(shí)，如果在下面的情況進(jìn)行刷臟頁(yè)，會(huì)明顯影響性能：

要淘汰的臟頁(yè)太多，導(dǎo)致查詢響應(yīng)時(shí)間較長(zhǎng)。
日志寫滿，更新被阻塞。
為了解決這個(gè)問(wèn)題，InnoDB 使用控制臟頁(yè)比例的機(jī)制，來(lái)避免上面的情況。

InooDB 控制刷臟頁(yè)的策略

在 InnoDB 中，通過(guò) innodb_io_capacity 參數(shù)，來(lái)告訴 InnoDB 目前主機(jī)的磁盤能力是多少，這個(gè)值建議設(shè)置成磁盤的 IOPS.

可以通過(guò) fio 這個(gè)工具來(lái)測(cè)試：

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 導(dǎo)致的性能問(wèn)題很常見，比如有時(shí)系統(tǒng)吞吐量（TPS）很低，寫入很慢，但是磁盤 IO 并不高。就有可能是該參數(shù)設(shè)置的不正確。例如，innodb_io_capacity 的值設(shè)置的很低，但是磁盤用的 SSD，導(dǎo)致 InooDB 認(rèn)為系統(tǒng)能力很差，所以刷臟頁(yè)特別慢。造成臟頁(yè)累計(jì)，影響查詢和更新性能。

InnoDB 在刷盤時(shí)主要考慮兩個(gè)因素：

1. 臟頁(yè)的比例
   
2. redo log 寫盤速度
   

會(huì)通過(guò)這兩個(gè)因素單獨(dú)先算出兩個(gè)數(shù)字。

innodb_max_dirty_pages_pct 臟頁(yè)比例上限，默認(rèn) 75%.

InnoDB 會(huì)根據(jù)臟頁(yè)的比例（M），算出范圍在 0 - 100 的數(shù)字。，過(guò)程稱為 F1（M）

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

除此之外，InnoDB 每次寫入日志都會(huì)有一個(gè)序號(hào) N. 然后根據(jù) N 再算出一個(gè) 0 到 100 的數(shù)字，這個(gè)計(jì)算過(guò)程稱為 F2（N）

N: 當(dāng)前寫入的序號(hào)和 checkpoint 對(duì)應(yīng)序號(hào)之間的差值。

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

所以無(wú)論是在查詢，需要加載數(shù)據(jù)到內(nèi)存數(shù)據(jù)頁(yè)，而淘汰臟頁(yè)。還是更新時(shí)，導(dǎo)致刷盤操作都有可能造成 MySQL 的性能下降。

為了避免這種情況，要合理的設(shè)置 innodb_io_capacity 的值，平時(shí)要多關(guān)注臟頁(yè)比例，不讓其接近 75%.

其中臟頁(yè)比例可以通過(guò)下面的方式獲取：

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;

初次之外，在一個(gè)查詢操作進(jìn)行時(shí)，如果需要 flush 臟頁(yè)的話，如果這個(gè)該臟頁(yè)的鄰居也是臟頁(yè)的話，就會(huì)把這個(gè)鄰居一起刷掉，如果恰好旁邊還是臟頁(yè)的話，就會(huì)一直連坐。這時(shí)導(dǎo)致 flush 過(guò)慢的原因。

可以通過(guò) innodb_flush_neighbors 來(lái)控制該行為，值為 1 打開上述機(jī)制，為 0 則關(guān)閉。

對(duì)于機(jī)械硬盤來(lái)說(shuō)，是可以減少很多隨機(jī) IO ，因?yàn)闄C(jī)械硬盤 IOPS 一般就幾百，減少隨機(jī) IO 就意味著性能提升。

但如果用 SSD 這類 IOPS 較高的設(shè)備，IOPS 往往不是瓶頸，關(guān)閉就好，減少 SQL 語(yǔ)句的響應(yīng)時(shí)間。

在 8.0 中，已經(jīng)默認(rèn)是 0 了.

總結(jié)

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

引起的原因一般是由于內(nèi)存不夠?qū)е碌模M(jìn)而可以通過(guò)設(shè)置合適的 innodb_io_capacity 參數(shù)，來(lái)控制 InnoDB flush 的過(guò)程。

以上就是MySQL性能突然下降的原因的詳細(xì)內(nèi)容，更多關(guān)于MySQL性能下降的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！