阿里云RDS数据库RDS SQL Server I/O高问题-云淘科技

背景信息

I/O处理能力主要体现在IOPS和I/O吞吐量两个方面，IOPS一般不会成为实例的性能瓶颈，而I/O吞吐量达到上限导致出现性能瓶颈的情况较多。

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吞吐量受相应存储类型和容量限制。更多信息，请参见块存储性能。

查看I/O吞吐情况

前提条件

实例不能是RDS SQL Server 2008 R2云盘版。

1. 访问RDS实例列表，在上方选择地域，然后单击目标实例ID。

2. 在左侧导航栏单击自治服务 > 性能优化，单击性能洞察页签。

3. 在右侧单击自定义指标，选中IO吞吐量相关性能指标，单击确定。

   说明

   IO吞吐量的性能指标说明如下：

   • IO_Throughput_Read_Kb：每秒磁盘读I/O吞吐量。

   • IO_Throughput_Write_kb：每秒磁盘写I/O吞吐量。

   • IO_Throughput_Total_Kb：每秒磁盘读I/O吞吐量和写I/O吞吐量之和。

   

分析与优化I/O吞吐

RDS SQL Server实例的I/O负载主要由数据文件的读请求和事务日志文件的读写请求组成。数据文件的读请求操作主要包括查询请求产生的数据页读取和数据库备份产生的数据页读取，事务日志文件在做备份时会有较多的读I/O负载，其他情况主要是写I/O负载。

当发现实例的I/O吞吐较高时，您可以在自定义指标中增加以下性能指标，然后分析是哪种负载导致的I/O吞吐升高。

说明

每个数据页的大小为8 KB。

分析案例

从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以上。

数据页读取I/O吞吐高

数据页读取（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（秒）

说明

更多信息，请参见Page Life Expectancy (PLE) in SQL Server。

如果是数据页读取I/O高导致的实例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操作的执行时间。

备份I/O吞吐高

目前RDS SQL Server仅支持在主实例上进行数据备份，会导致主实例的磁盘I/O吞吐增高，其中数据备份（尤其是全量备份）的影响最大，日志备份的影响较小。

由于备份操作是保障RDS数据安全和可靠性必不可少的，因此对于备份导致的I/O吞吐高，建议设置合适的备份时间来降低对业务的影响。

您可以在实例的备份恢复页面内查看数据备份的耗时，然后避开业务高峰期，选择合适的备份时间。

• 如果一次全量备份大约需要6小时，每天9点到21点是业务高峰期，22点到1点有后台数据处理任务，您可以把备份时间设为01:00-02:00，这样8点之前全量备份就可以完成。备份周期可以设置为每天都备份，需要执行还原操作时，效率也会提高。

• 如果一次全量备份大约需要15小时，工作日任何时间段进行备份都会影响业务，建议将备份周期设置为周六和周日，缺点是如果要通过备份指定时间点还原数据，还原操作的耗时会较长。

如果调整备份时间无法避免全量备份操作和业务的冲突，建议升级实例的磁盘性能等级，或者拆分数据，减少单个实例上的数据量，缩短全量备份所需的时间。