当前位置: 代码迷 >> Sql Server >> SQL Server IO 子系统浅究 I解决思路
  详细解决方案

SQL Server IO 子系统浅究 I解决思路

热度:430   发布时间:2016-04-27 22:00:09.0
SQL Server IO 子系统浅究 I
作者:obuntu

很喜欢SQL Server里面4步定位性能问题的理论,具体如下:
1,资源瓶颈
i. 内存 ii. CPU iii. IO
2, Tempdb瓶颈
3,找出执行慢的语句,可以通过三个方面来寻找
i. 统计信息 ii. 缺失索引 iii. 阻塞
4,缓存执行计划分析

更多信息,可以参看此篇文章http://blogs.msdn.com/b/jimmymay/archive/2008/09/01/sql-server-performance-troubleshooting-methodology.aspx

可以看到,一遇到系统性能问题时,第一步是确定资源是否存在瓶颈,在CPU,内存,IO 三者之间,最容易形成瓶颈的是IO子系统。其实IO子系统的内涵是很深的,能够影响IO子系统性能的因素有磁盘的数目,大小,和转速;文件分配单元大小(file allocation unit size);HBA;网络带宽;磁盘缓存;控制器;是否使用SAN(storage area networks);RAID级别;总线速度;IO通道等等。
作为SQL Server的使用者,通常很少会去调整IO子系统的配置,一则重视不够,二是缺少这方面的相关知识和技能。但了解这方面的相关问题还是很有必要的,除了可以更好发挥硬件的作用外,在碰到系统性能问题时,也能很好的进行定位分析。

目录
IO子系统相关概念
SQL Server IO 相关概念
性能监视器里的IO子系统计数器
SQLIO
关于IO的一些最佳实践
小结
参考资料

IO子系统相关概念

A,磁盘

磁盘从过去的几十年里,取得了快速的发展,从ATA,SATA,SAS,到现在的SSD,每次技术的变革都带来了磁盘的性能提升。现在应用最广泛的应该是15K转的SAS盘了,对于这样的盘,一些传统的磁盘概念还是不变,如磁道,扇区。

现在的硬盘,一般是由重叠的一组盘片组成,每个盘片又被划分为数目相等的磁道,同时对这些磁道进行编号。每个磁道被等分为若干个弧段,这些弧段就是扇区,扇区的一般为512bytes,也有1K,2K,4K大小。同时不同盘片上,相同编号的磁盘则组成柱面。柱面数等于磁道数,盘面数等于总的磁头数,因此硬盘上有所谓的CHS概念,即Cylinder(柱面)、Head(磁头)、Sector(扇区)。磁盘的容量等于柱面数*磁头数*扇区数*扇区的大小。

IO的模式有顺序读写和随机读写,磁盘在处理这2种读写方式时,所表现出来的性能是不一样的。一般来说,在顺序读写上,现在10K转的磁盘,能够获取40mb/s~80mb/s的传输速率;15K转的磁盘,能够获取70mb/s~125mb/s的传输速率。对于随机读写来说,其性能取决于磁盘的转速和寻道时间。一个10K转的磁盘完成一个完全的旋转需要6ms(1*60*1000/10000)。硬盘的等待时间,又叫潜伏期(Latency),是指磁头已处于要访问的磁道,等待所要访问的扇区旋转至磁头下方的时间。平均等待时间为盘片旋转一周所需的时间的一半,一般应在4ms以下,所以一般认为在磁盘上的等待时间为3ms,对15K的磁盘则是2ms。

还有一个影响磁盘性能的因素是寻道时间,它是指硬盘在接收到系统指令后,磁头从开始移动到移动至数据所在的磁道所花费时间的平均值。现在10K转的磁盘在读上,平均的寻道时间为4.6ms,在写上,平均的寻道时间为5.2ms。一个15K转的磁盘,读的平均寻道时间为3.5ms,写的寻道时间为4.2ms。

如对于小块的8kb读取,传输的时间大概为0.1ms,忽略其他可以忽略的因素后,结合上述讨论,对于随机读,我们可以得出总的时延大概为8ms(10K磁盘)和5.6ms(15K转磁盘),所以磁盘在一般随机的小块读的性能大概为 125 IOPS(10K磁盘)和175 IOPS(15K磁盘)。

上述的情况是较为理想的。如果磁盘上的数据集中在某块小的区域,会降低磁盘的平均寻道时间,性能还会更好。但如果多个IO请求同时发生的话,磁盘还需要对多个IO进行序列化,排序,从而在越高的吞吐下,其时延会更长。一般来说,如果数据分布于整个磁盘,队列深度(queue depth)越高,时延更长,队列深度为4时,时延会达到20ms,队列深度为32时,时延能达到100ms。队列深度指的是磁盘上能并行运行的IO个数。 因此,对于队列深度的值建议使用2,当然不同的存储,不同的系统也会有不同的建议值,设置的时候可以参考相关的资料。但也有个情况需要注意,那就是如果数据只分布于磁盘上的某一小块区域,如5%,那么时延并不会随着队列深度的增加而大幅增加,一般是队列深度为8时20ms,队列深度为16时40ms,而且随机读的性能也有很大的提升,每个IOPS可以达到400.这个特点在处理强事务的能让你获得很大的弹性空间。

B,RAID

现在真实的企业应用环境很少单独使用一个一个磁盘来存放文件,而是采用RAID技术。RAID技术能带来性能的提升和有效的容错能力。简单的说,RAID是一种把多块独立的硬盘(物理硬盘)按不同的方式组合起来形成一个硬盘组(逻辑硬盘),从而提供比单个硬盘更高的存储性能和提供数据备份技术。组成磁盘阵列的不同方式称为RAID级别(RAID Levels)。

RAID 有很多种级别,也就是说磁盘有多种组合方式。现在比较常用的是RAID10和RAID5,RAID10的整体性能会比RAID5来得高,但其价格也更昂贵。决定使用哪种RAID级别,对系统的性能影响也很大,因此需要经过充分测试,权衡自己的实际情况并作出选择。

C,其他概念

文件分配单元(file allocation unit)大小,也就是簇的大小,一般是扇区大小的整数倍,如簇的大小是4K,扇区的大小为512bytes,那么一个簇就会使用到8个扇区。在进行硬盘格式化时,可以使用format命令的/A:size 选项来指定。一般情况下,在SQL Server数据文件和日志文件上比较合适的大小是64K,但有时候32K也能提供较好的性能,因此设置该值之前,最好也进行充分的测试来决定。下面是一个查看当前文件分配单元大小的例子。
C:\Documents and Settings\Administrator>fsutil fsinfo ntfsinfo d:
NTFS 卷序列号 : 0xde500ef9500ed7e3
版本 : 3.1
区数量 : 0x0000000012c03620
簇总数 : 0x0000000012c03620
可用簇 : 0x000000001098efb6
保留总数 : 0x0000000000000000
每个扇区字节数 : 512
每个簇字节数 : 512
每个 FileRecord 段的字节数 : 1024
每个 FileRecord 段的簇数 : 2
Mft 有效数据长度 : 0x0000000004a68000
Mft 起始 Lcn : 0x0000000000600000
Mft2 起始 Lcn : 0x0000000009601b10
Mft 区域起始 : 0x0000000000625460
Mft 区域结尾 : 0x0000000002b80800

配置RAID的时候,有个可以手工设定的参数:Stripe size. 逻辑驱动器的Stripe size,代表控制器每次写入一块物理磁盘的数据量,以KB为单位。 不同Stripe size的选择直接影响性能,如IOPS和吞吐量。 Stripe size值小,通过多块磁盘响应多个I/O请求,可以增加I/O访问速率(IOPS);Stripe size值大,通过多块磁盘响应一个I/O请求,可以增加数据传输速率(Mbps).为了获得更高的性能,要选择条带的容量等于或小于操作系统的簇的大小。大容量的条带会产生更高的读取性能,尤其在读取连续数据的时候。而读取随机数据的时候,最好设定条带的容量小一点。

因此,可以看到上述值得设定对SQL Server的性能也是有帮助的,但很难有一个合适的推荐,有时候大部分还是保持默认的,如果确实遇到这方面的设置需求,最好请教相关产品的厂商,或者自己进行充分测试。


SQL Server IO 相关概念

SQL Server 引擎有自己的磁盘IO内部管理机制。理解SQL Server的IO处理机制是很有必要的。微软有2部非常好的白皮书,叫《SQL Server I/O Basics Chapter 1》《SQL Server I/O Basics Chapter 2》,对此进行了深入阐述,如果对这方面有兴趣的朋友,是不能错过。但只有英文版,两份加起来有100多页左右。下面对SQL Server IO的一些要点进行简要阐述,更多详情,参考这两份白皮书吧。
  相关解决方案