数据库存储I/O类型分析与配置– SQL Server篇

数据库存储I/O类型分析与配置– SQL Server

 

转载请在文首保留原文出处:EMC中文支持论坛https://community.emc.com/go/chinese

介绍

 

     存储设备作为数据的容器,为应用提供数据存取服务,而存储系统将数据展现给不同的应用后,应用程序对数据访问不尽相同。简要来说,就是读和写,更加细分的话是以不同的传输单元(I/O大小)进行顺序和随机类型的读写。不同应用的I/O访问差异,直接决定了存储系统对它们的服务方式的不同,了解主流应用的I/O访问类型,是存储性能规划和调优的基础。本文作主流数据库系统的存储I/O访问类型与配置首篇,罗列了MS SQL Server相关,作者所能收集到的SQL Server I/O访问类型与特点,具体到不同的操作的I/O与配置文档列表,供读者查阅和参考。

更多信息

 

     SQL Server运行过程中,会不断与存储端做交互,但不是每一个操作都会产生存储I/O。鉴于SQL Server会直接使用主机内存作为缓存,所以在SQL Server中,对数据的读写又分为SQL Server自身缓存中进行的Logical I/O和实际与存储系统交互的Physical I/O。本文所描述的存储I/O,即SQL Server中实际与存储交互的Physical I/O操作。在讨论SQL ServerI/O类型之前,我们先看一下SQL Server在哪些情况下会产生存储I/OPhysical I/O):


  • SQL Server内存中没有缓存的数据,第一次访问数据需要从数据文件中加载的时候。
  • 任何数据库插入、更新、删除提交之前,把日志记录写入日志文件的时候。对于Select类似的读操作,如果数据在主机内存中有缓存,则不会产生存储I/O
  • SQL Server进行Checkpoint的时候,将缓存中的数据写入数据文件时候。
  • SQL Server缓存存在压力,触发Lazy Write将页面刷新到存储设备的时候。
  • 执行特殊操作,例如CHECKDBReindexUpdate Statistics、数据备份的时候。


     SQL Server对存储中I/O操作是以8KB的页面为最小处理单位。因为SQL Server只能运行在Windows服务器上,而WindowsI/O大小与类型直接由应用发起来决定(参考:I/O Request Size)。所以,SQL ServerI/O类型与大小会相对比较固定。下表中我们给出SQL Server不同类型的操作所对应的I/O类型与大小:

                          

SQL Server操作

随机/顺序

/

I/O大小

OLTP – 日志

顺序

写(除了恢复的时候)

512 bytes – 64KB,平均8-9.5KB

OLTP – 数据

随机

/

8KB – 64KB

Bulk Insert

顺序

大多数8KB,最大128KB

Read Ahead DDSIndex Scan

顺序

大多数8KB,最大256KB

备份

顺序

/

1MB

恢复

顺序

/

64KB

Reindex

顺序

/

多数是8KB,最大256KB

创建数据库

顺序

512KB


     由上表可见,SQL Server的主要操作多以8KBI/O大小为主,而某些顺序I/O类型的操作,会使用比较大的I/O大小,原因可想而知:为了提供更大的ThroughputSQL Server的读I/O基本只位于数据文件所在的LUN上面。SQL Server中的写I/O分为两种,数据文件与日志文件,这些写I/O的数目,则完全取决于数据更新的频率。

     SQL Server对于不同类型的I/O,每次进行实际操作I/O大小会存在少量差异。 例如对于表的索引查询,绝大多数以8KB为单位,而像一些整表查询,则可能以64KB甚至更大的单位比较多。至于何时会使用多大的I/O,没有一个绝对的情况。只能说取决于SQL Server的内部机制,作者也没有找到相应的资料来描述这种行为。

     表中的读/写一栏显示了I/O读与写的重点。表中没有列出读/写比例,是因为这个比例则需要根据应用而定,不同的应用之间差别比较大。

 

根据表中的I/O类型与特点,总结几点存储的配置建议:


  • SQL Server的不同数据文件,如果分散存放在不同的LUN上,可以使得SQL Server的读写I/O由若干个LUN共同完成,达到并发处理的效果,提升性能。(参考:SQL Server IO Pre-deployment Best Practices Article
  • 衡量SQL Server的OLTPOLAPDDS)存储性能的指标是不同的,前者看中的IOPS,而后者则是Throughput,所以规划的重点也不尽相同。(参考:论存储IOPS和Throughput吞吐量之间的关系
  • 鉴于SQL Server不同操作,读/写的重点的不同,理解通用RAID的类型的写惩罚机制,对规划数据文件和日志文件存储,选取合适的RAID保护级别会有比较大的帮助。(参考:浅谈RAID写惩罚(Write Penalty)与IOPS计算
  • 根据所使用的存储阵列的特点,参考厂商所提供的存储阵列配置文档,根据情况启用适当的存储功能,例如配置Write Cache/Read Cache的比例,使用FAST等技术实现存储分层都是针对SQL Server存储配置中可以参考的技术实现。(参考:EMC存储SQL Server配置最佳实践
  • 配置存储过程中,建议使用合适的工具对磁盘进行压力测试,根据实际应用情况模拟存储I/O,检验存储的性能是否满足需求。(参考:How to Use the SQLIOStress Utility to Stress a Disk Subsystem

 

综上所述,本文的内容总结为以下几点:


  • 虽然SQL Server主机的内存大小会决定存储I/O的数量,缓存页面的数量越多,就越能减少存储端发生的读I/O数目。但是如果存储读性能够好,同样可以缓解SQL Server端的缓存压力。所以,在SQL Server本身存储I/O数量不能继续优化的情况下,利用存储来提升性能是可选方案。
  • SQL ServerI/O的数目,完全取决于数据的更新频率,如果数据更新频繁,日志文件存储的LUN会受到比较大的压力。 同理,使用写入性能够好的存储,用作日志文件的部署,可以很大程度提升整体SQL Server的存储性能。另外,适当RAID配置,以及启用一些存储端功能,例如调整写缓存的比例,FAST分层存储等等,也不同程度帮助写I/O提高存储响应速度。
  • 对于SQL Server的存储配置没有一劳永逸的做法,而且数据库系统的高定制化特点决定了服务于不同应用系统的存储I/O访问类型会有很大区别。DBA和存储管理员需要根据自身应用的特点,结合观察数据库系统的存储行为,参考文本中给出的数据与配置文档,结合实际应用的存储负载才能更好的完成存储规划与性能调优,从而满足应用与数据库的应用需求。

 

     最后,作为一篇整理与总结的文章。本文试图收集更多的参考资料,用来描述主流数据库系统 MS SQL ServerI/O类型,并给出配置的建议与参考文档。由于信息来源于网络与其他厂商的官方文档。内容整理可能存在缺失与不准确,如果有任何遗留或者错误,希望广大读者指正。

 

参考

 

浅谈RAID写惩罚(Write Penalty)与IOPS计算
论存储IOPS和Throughput吞吐量之间的关系

SQL Server 2008 Analyzing Characterizing and IO Size Considerations

SQL Server IO Pre-deployment Best Practices Article

How to Use the SQLIOStress Utility to Stress a Disk Subsystem

SQL Server I/O Basics

I/O Request Size

EMC存储SQL Server配置最佳实践

应用于

 

Microsoft SQL Server

存储性能分析

存储规划