RDS SQL Server I/O高問題 - ApsaraDB RDS

執行個體I/O過高時，容易影響查詢效能。本文介紹查看I/O吞吐情況的方式以及如何排查I/O高問題。

背景資訊

I/O處理能力主要體現在IOPS和I/O輸送量兩個方面，IOPS一般不會成為執行個體的效能瓶頸，而I/O輸送量達到上限導致出現效能瓶頸的情況較多。

本地碟執行個體
本地SSD盤執行個體的架構是多個執行個體共用同一物理機的本地SSD盤，只限制了單個執行個體的最大IOPS，沒有限制單個執行個體的I/O輸送量，因此實際上單個執行個體的最大I/O輸送量可以達到1 GB/Sec以上，但同時也存在I/O吞吐資源爭搶問題。如果您需要獨享I/O吞吐資源，建議選擇獨佔物理機規格。
雲端硬碟執行個體
雲端硬碟執行個體的架構是單個執行個體獨立掛載SSD或ESSD雲端硬碟，因此I/O資源是完全隔離和獨享的。單個執行個體的I/O輸送量上限，取決於執行個體的計算規格，以及使用的SSD或ESSD雲端硬碟的規格和容量。說明如下：
- RDS SQL Server雲端硬碟執行個體的計算規格主要是ECS 6代規格，I/O輸送量受相應規格限制。更多資訊，請參見通用型執行個體規格類型系列g6。
- RDS SQL Server雲端硬碟執行個體的儲存類型有SSD和ESSD兩類，I/O輸送量受相應儲存類型和容量限制。更多資訊，請參見Block Storage效能。

前提條件

執行個體不能是RDS SQL Server 2008 R2雲端硬碟版。

RDS SQL Server執行個體的I/O負載主要由資料檔案的讀請求和交易記錄檔的讀寫請求組成。資料檔案的讀請求操作主要包括查詢請求產生的資料頁讀取和Database Backup產生的資料頁讀取，交易記錄檔在做備份時會有較多的讀I/O負載，其他情況主要是寫I/O負載。

當發現執行個體的I/O吞吐較高時，您可以在自訂指標中增加以下效能指標，然後分析是哪種負載導致的I/O吞吐升高。

效能指標	I/O類型	說明
Page_Reads	讀	由於未命中緩衝，每秒從資料檔案中讀取的資料頁的個數。
Page_Write	寫	每秒向資料檔案中寫入的資料頁的個數。
Log_Bytes_Flushed/sec	寫	每秒向記錄檔中寫入的位元組數。
Backup_Restore_Throughput/sec	讀	每秒由備份或還原作業產生的資料和記錄檔讀寫的位元組數。

說明

每個資料頁的大小為8 KB。

分析案例

IO輸送量 page log

從I/O輸送量可以發現，執行個體的I/O吞吐中讀I/O佔比較大，寫I/O佔比較小。其中8點到22點這段時間是I/O負載相對平穩的時段，1點到3點和22點到0點，各有一個明顯的I/O吞吐高峰，具體的分析還需要結合其他效能資料。

結合Page效能可以發現，1點左右的I/O輸送量突增主要是讀取資料頁造成的，峰值達到了50000頁左右，即400 MB/sec。
結合Page效能、Log效能、備份輸送量可以發現，2點到3點左右的I/O吞吐高峰由資料頁讀取（峰值約40 MB/sec）、資料頁寫入（峰值約40 MB/sec）、記錄檔寫入（峰值約30 MB/sec）和記錄備份（峰值約50 MB/sec）共同組成，累積的I/O吞吐峰值約150 MB/sec。
結合Page效能、Log效能可以發現，8點到22點期間的I/O吞吐大部分是資料頁的讀取，約80~100 MB/sec，其次是資料頁的寫入，約30 MB/sec，最後是記錄檔的寫入，約5 MB/sec。
結合備份輸送量可以發現，22點到0點的I/O吞吐高峰完全是備份造成的，持續在220 MB/sec以上。

資料頁讀取（Page Reads）的I/O吞吐高是導致RDS SQL Server執行個體I/O吞吐高的原因中最常見的一種，主要原因是執行個體的緩衝不足，導致查詢請求在執行過程中由於緩衝無法命中（cache miss），從而需要在磁碟中讀取大量的資料頁。

診斷緩衝的一個常用效能指標是頁生命週期（Page Life Expectancy），表示平均每個緩衝的資料頁在記憶體中駐留的時間，單位為秒。時間越短，意味著執行個體的緩衝壓力越大。

通常建議Page Life Expectancy的閾值至少不低於300秒，執行個體的記憶體規格越高，建議閾值也越大，公式如下：

建議閾值 = (緩衝池記憶體大小 / 4 ) x 300

例如16 GB記憶體規格執行個體，可用於緩衝池的記憶體不超過12 GB，建議閾值設定為：(12 / 4) x 300 = 900（秒）

說明

如果是資料頁讀取I/O高導致的執行個體I/O吞吐負載過高，建議升級執行個體的記憶體規格，而不是升級執行個體的磁碟效能等級。

在資料庫層面也可以通過減少資料頁總量來降低資料頁讀取負載，例如歸檔清理歷史資料、啟用表資料壓縮、刪除低價值索引、整理索引片段等。

資料頁寫入和記錄檔寫入I/O吞吐高，可以通過自治服務觀察寫I/O吞吐高期間是否有頻繁的DML類的寫入操作（INSERT、DELETE、UPDATE、MERGE等）或DDL類的寫入操作（CREATE INDEX、ALTER INDEX等），處理建議如下：

DML類寫入操作
首先評估是否為常規業務行為，如果不是，例如為臨時性的資料處理或歸檔操作，請盡量在業務低峰期執行此類操作；如果是，建議升級執行個體的磁碟效能等級，例如從ESSD PL1到PL2。
同時建議最佳化索引結構，刪除不必要的非叢集索引。
DDL類寫入操作
通常是資料維護類或臨時性的行為，請盡量在業務低峰期執行此類操作。
同時建議在建立索引、重建索引等操作時，在SQL語句中使用MAXDOP限制任務執行的並行度，降低執行過程中的磁碟I/O吞吐峰值，但是會延長DDL操作的執行時間。

目前RDS SQL Server僅支援在主執行個體上進行資料備份，會導致主執行個體的磁碟I/O吞吐增高，其中資料備份（尤其是全量備份）的影響最大，記錄備份的影響較小。

由於備份操作是保障RDS資料安全和可靠性必不可少的，因此對於備份導致的I/O吞吐高，建議設定合適的備份時間來降低對業務的影響。

您可以在執行個體的備份恢複頁面內查看資料備份的耗時，然後避開業務高峰期，選擇合適的備份時間。

執行耗時

如果一次全量備份大約需要6小時，每天9點到21點是業務高峰期，22點到1點有後台資料處理任務，您可以把備份時間設為01:00-02:00，這樣8點之前全量備份就可以完成。備份周期可以設定為每天都備份，需要執行還原作業時，效率也會提高。
如果一次全量備份大約需要15小時，工作日任何時間段進行備份都會影響業務，建議將備份周期設定為周六和周日，缺點是如果要通過備份指定還原時間點資料，還原作業的耗時會較長。

如果調整備份時間無法避免全量備份操作和業務的衝突，建議升級執行個體的磁碟效能等級，或者拆分資料，減少單個執行個體上的資料量，縮短全量備份所需的時間。