次の方法で共有

SQL server每个日志写(log write)究竟有多大？

  • [アーティクル]

 

我曾经有个客户问我，如果我的磁盘专门放日志，那么格式化磁盘的时候，分配单元大小应该选多大呢？这个问题，其实和SQL server 的日志I/O操作有关。SQL server在写日志的时候，会发出多大的I/O请求呢？

 

先来看看和这个问题相关的Allocation Unit。我们在格式化磁盘的时候, 会有一个选项叫做分配单元大小(Allocation Unit),如下所示：

 

 

这个“Allocation Unit(后门简称为AU)”代表什么意思呢？AU 也叫cluster (簇)，它代表了一个文件存放在磁盘上的最小分配单位。每个分配单元只能被一个文件使用。文件就是按照这个分配单元的大小被分成若干块存储在磁盘上的。即使文件大小小于这个分配单位，它最小也要占用分配单元大小的磁盘空间。比如一个100 bytes大的文件，当分配单元为512字节时，它占用一个分配单元即512字节的存储空间。如果文件大小为513 字节到1024字节，当分配单元为512字节时，它占用两个分配单元也就是1024字节的存储空间。

 

我下面图显示所以我的文件test.txt大小是26字节，但是占用空间是4kb。这是因为我磁盘的AU是4kb的缘故。

 

 

一般来说，似乎分配单元越小越节约空间，分配单元越大越节约读取时间，但浪费空间。但是如果一个文件被分成的块数越多，那么这些块数在磁盘上分散存放的可能就越大（就是碎片），按么读取数据时会浪费一些时间。

 

上面介绍了Allocation Unit 的概念。这里再介绍另外一个概念，sector，即扇区。磁盘的每一面被分为很多条同心圆磁道，越接近中心，圆就越小。而每一个磁道又按512个字节为单位划分为等分，叫做扇区。扇区大小可能随系统的不同而异。你甚至可以改变扇区的大小。如果不是默认的512字节，需要注意下面的问题：

 

https://blogs.msdn.com/b/psssql/archive/2011/01/13/sql-server-new-drives-use-4k-sector-size.aspx

https://support.microsoft.com/kb/926930

 

磁盘驱动器在向磁盘读取和写入数据时，要以扇区为单位。连续的sector（扇区）就组成了一个Allocation Unit。

 

那么如何查看磁盘的AU，sector等信息呢？可以使用fsutil工具。

 

C:\Windows\system32>fsutil fsinfo ntfsinfo e:

输出如下：

NTFS 卷序列号 :                  0x3c5e6e515e6e03cc

版本 :                            3.1

扇区数量 :                        0x00000000061a77ff

簇总数 :                          0x0000000000c34eff

可用簇 :                          0x0000000000c2f6bd

保留总数 :                        0x0000000000000000

每个扇区字节数 :                  512

每个物理扇区字节数 : 512 ß--------sector size

每个簇字节数 : 4096ß--------Allocation Unit size

每个 FileRecord 段的字节数 :      1024

每个 FileRecord 段的簇数 :        0

Mft 有效数据长度 :                0x0000000000040000

Mft 起始 Lcn :                    0x00000000000c0000

Mft2 起始 Lcn :                   0x0000000000000002

Mft 区域起始 :                    0x00000000000c0000

Mft 区域结尾 :                    0x00000000000cc820

RM 标识符:        01D0688A-C349-11E2-A1B3-D4BED98EB542

 

可以看到，我磁盘的扇区大小为512字节，而分配单元为4kb。

 

有了上面的知识，现在回到我文章头提到的问题。SQL server 日志写(log write)的最小大小是多少呢？为此我做了个试验（Windows 7+SQL server 2012）

 

1)我把磁盘格式化，最小分配单元为4kb

2)我把log 放到磁盘上

3) 我commit一个非常小的事务

 

--Create table t1 (c1 int)

begintran

insertinto t1 values(1)

commit

 

4)我使用Process Monitor 来观察磁盘的读写。看看下图：

 

 

我反复试用了多次，我发现SQL server 日志写的最小大小都是512bytes，就是一个扇区的大小。事实上我其实知道，SQL server的日志写就是以扇区大小为单位的。原因如下：

 

https://technet.microsoft.com/en-us/library/cc966500.aspx

Hardware manufacturers guarantee sector-size writes so SQL Server 2000 transaction log files are always written with sector-size alignment. Each sector of the transaction log contains a parity flag. This flag can be used to determine the last sector that was correctly written.

注意 SQL server的torn page detection和扇区大小密切相关。

 

注意日志写最小单位512bytes并不意味着总是以这个大小进行写操作。如果事务够大，那么SQL server 会以大于512bytes的大小进行写。比如：

 

 

那么最大能够到多大？

 

让我们试验一下。我使用了下面的脚本，同时开2个窗口运行：

 

createtable t2( c1 int, c2 char(7000))

go

begintran

declare @i  integer

SEt @i=0

while (@i<100000)

begin

insertinto t2 values(@i,'dadf')

set @i=@i+1

end

checkpoint

commit

deletefrom t2

 

我发现大部分的日志写都是59kb大小：

但是也有其他的大小，如下面的8MB和4MB：

 

但是，会不会有比8MB更大的写操作呢？我不知道。

 

至此，有关磁盘格式化的分配单元问题，答案已经清楚了。SQL server 日志写和扇区大小有关系。SQL server 的日志写的最小单位就是扇区的大小。但是SQL 的每个日志写似乎和磁盘的分配单元无直接关系。尽管如此，我上面试验里面大部分的日志写接近60kb，所以分配单元为64kb似乎比较好些。如果有时间，我下次再写篇文章，做下压力测试，看看分配单元和日志写，或者数据写，究竟有没有性能上的关系。

 

参考文档：

SQL Server Best Practices Article

https://technet.microsoft.com/en-us/library/cc966412.aspx

Comments

  • Anonymous
    July 14, 2013
    我对这个测试还是有一些疑问。根据Paul Randal的说法，不会出现大于60K的log block,也就是每次写不会超过60K而我自己的测试也是这样只有在日志自动填零增长时，才会出现超过60K的log write。上面那些8M和4M的磁盘写，应该是日志增长所导致的，所以我觉的不能和log write混淆吧？
  • Anonymous
    December 02, 2013
    我的测试结果跟宋大侠一样都是60K左右的log write