RDS MySQL I/O高問題 - ApsaraDB RDS

RDS MySQL的I/O效能受硬體層儲存介質、軟體層資料庫核心架構和具體SQL語句（掃描或修改資料量）的影響。本文介紹執行個體I/O高的原因和解決方案。

高吞吐導致執行個體I/O高

現象
如果表上有很多索引或大欄位，頻繁地更新、刪除、插入，讀取資料和重新整理髒頁時會有大量的I/O。
您可以在控制台的自治服務 > 效能趨勢頁面，單擊效能趨勢頁簽，查看讀寫負載情況。
解決方案
建議降低讀寫頻率或升級執行個體規格、最佳化重新整理髒頁相關的參數來解決高吞吐問題。和重新整理髒頁相關的參數如下：
- innodb_max_dirty_pages_pct：緩衝池中允許的髒頁百分比，預設值為75。
- innodb_max_dirty_pages_pct_lwm：髒頁比例的低水位線。當緩衝池裡的髒頁比例超過這個低水位線時，能夠觸發髒頁預刷功能，逐步控制髒頁比例。預設值為0，表示禁用該功能。
  說明
  innodb_max_dirty_pages_pct_lwm的值不能大於innodb_max_dirty_pages_pct的值，否則會強制修改為與innodb_max_dirty_pages_pct相同。
- innodb_io_capacity：設定InnoDB背景工作每秒執行的I/O運算元的上限，影響重新整理髒頁和寫入緩衝池的速率。預設值為20000。
- innodb_io_capacity_max：如果重新整理操作過於落後，InnoDB可以超過innodb_io_capacity的限制進行重新整理，但是不能超過本參數的值。預設值為40000。

現象
如果臨時目錄很大，可能存在慢SQL排序、去重等操作導致建立很大的暫存資料表。暫存資料表寫入也會造成I/O增加。
您可以在控制台的自治服務 > 效能趨勢頁面，單擊效能趨勢頁簽，查看tmp或other目錄大小。
解決方案
建議進行SQL最佳化，避免慢SQL。資料庫自治服務DAS提供自助SQL最佳化功能，具體操作，請參見SQL最佳化。

現象
如果SQL查詢或修改的資料不在緩衝池（Buffer Pool），則需要從儲存中讀取，可能會產生大量的I/O吞吐。
您可以在控制台的自治服務 > 效能趨勢頁面，單擊效能趨勢頁簽，查看Buffer Pool命中率。
解決方案
根據業務情境重新設計緩衝策略，或者升級執行個體規格。

現象
DDL語句可能會重建資料表空間，期間會掃描全表資料、建立索引排序、重新整理新表產生的髒頁，這些都會導致大量的I/O吞吐。另外一種情境是刪除大表造成的I/O抖動。
您可以在控制台的監控與警示頁面，單擊標準監控頁簽內的標準視圖，可以查看執行個體的磁碟空間和IOPS資訊。
解決方案
可以使用阿里雲自研核心AliSQL提供的非同步刪除大檔案功能解決問題，更多資訊，請參見Purge Large File Asynchronously。

現象
事務只有在提交時才會寫Binlog檔案，如果存在大事務，例如一條Delete語句刪除大量的行，可能會產生幾十GB的Binlog檔案，Binlog檔案重新整理到磁碟時，會造成很高的I/O吞吐。
解決方案
建議盡量將事務拆分，避免大事務和降低重新整理磁碟頻率。

InnoDB通過一套獨立的I/O系統來處理資料頁的讀取和寫入，如果SQL請求的資料頁不在Buffer Pool中，會產生物理I/O，需要讀寫底層儲存的資料：

除了對普通資料檔案的讀寫I/O操作，寫Redo日誌、寫Undo日誌、寫Binlog日誌、排序暫存資料表、重建DDL資料表空間等也會造成大量I/O。