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

網(wǎng)站制作、建網(wǎng)站找專業(yè)的建站公司創(chuàng)新互聯(lián):定制網(wǎng)站、模板網(wǎng)站、仿站、成都小程序開發(fā)、軟件開發(fā)、成都APP應(yīng)用開發(fā)等。做網(wǎng)站價格咨詢創(chuàng)新互聯(lián):服務(wù)完善、十載建站、值得信賴!網(wǎng)站制作電話：028-86922220

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

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

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

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

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

引起 flush 操作的原因

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

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

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

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

沒有選擇直接清空內(nèi)存，是從性能考慮的，因為在查詢數(shù)據(jù)時，有兩種情況：

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

所以這樣效率比較高。

場景三：MySQL 會在系統(tǒng)空閑時，進入 flush 操作。

場景四：在 MySQL 正常關(guān)閉時，會把內(nèi)存臟頁 flush 到磁盤上。

引起 flush 對性能的影響

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

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

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

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

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

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

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

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

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

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

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

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

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

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

InooDB 控制刷臟頁的策略

在 InnoDB 中，通過 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

網(wǎng)站名稱：MySQL性能下降的原因有哪些-創(chuàng)新互聯(lián)
網(wǎng)站鏈接：http://chinadenli.net/article28/eppcp.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站改版、虛擬主機、軟件開發(fā)、網(wǎng)站排名、面包屑導(dǎo)航、網(wǎng)站制作

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)