Sybase SQL Server 11的内存缓冲区管理和性能调整
Sybase SQL Server 11的内存缓冲区管理和性能调整 2@Gn[(9bR3
k{'4@xDX_
由于64位编址的实现和对称式多处理大组装盒的生产，计算机硬件具有越来越大的内存和日趋增长的强大计算处理能力，这为高性能数据库系统提供了令人鼓舞的新机遇。Sybase SQL Server 11能在这种环境下，帮助广大用户充分发挥现有系统的潜力和为将来创建开放的架构。其最显著的特性是支持多用户内存缓冲区结构，使用大块I/O的顺序预存取，和它们同查询优化器的紧密集成，新内存缓冲区管理能极大的改善系统性能。它提供许多工具和手段分析和配置内存缓冲区，以便能获得优良的应用性能。根据用户提供的系统配置，查询优化器能选择最佳的访问计划。这篇文章将介绍这些特性和如何使用它们来提高服务器性能。 lVT9Rr#Gf$
U U-X7
一、 引言 :lZ p >`
)yvVmmj L
32位编址障碍被突破，带来计算机采用超大内存的发展趋势。现在，把大的关键表存放在内存，已经是很现实的事。另外，使用大对称式多处理器计算机，将多个应用运行在同一系统上，可能更经济，合算。而这些应用的要求是各种各样，从可能修改一条记录的联机事务到读取整张表的报表生成，千变万化。SQL Server 11提供新功能，用于配置和优化这些应用，以便充分利用系统资源。 c(Meg
Sbb}aY 4FQ
一个典型的数据库系统由存储在磁盘上的永久性数据构成，什么时候要处理这些数据，必须首先将它读到主存，也就是通常所说的数据缓冲区。通常是调用底层操作系统的块I/O 来实现。数据库管理系统需要组织好这些读到内存缓冲区的数据页面，以备将来使用。以后，假如服务器在内存能找到所需页面，就不必做物理I/O，只是进行逻辑I/O，我们称它为命中缓冲区。数据缓冲区就是用于服务器为用户进行数据库各种数据操作。我们知道这两种I/O在访问速度方面有很大的差距，所以，内存缓冲区的性能是影响数据库应用系统的关键因素之一。 /mSTkK7yy
CL9j7N2
数据库系统的内存管理由缓冲区管理程序实现。对于一个用户查询，它通常需要访问一定数量的逻辑页面，页面多少和使用的索引有关。而优化器的主要任务是选择涉及逻辑页面数量最小的访问路径。缓冲区管理程序的目标是为实现所需的逻辑I/O尽可能地减少物理I/O。有些访问路径可能有很多的逻辑I/O,但是物理I/O很少。SQL Server 11的优化器与缓冲区管理程序配合，能识别这种情况，挑选能最佳利用数据缓冲区的索引。 a`-z`<V'?
`+MGS.
缓冲区管理程序的效率是用“缓冲区命中率”来衡量，它是申请访问的页面在内存缓 冲区中的比例。另一个衡量手段是访问竞争。因为缓冲区管理程序是共享资源，对它的并发访问是由“螺旋锁”(spinlock)机制来控制。在多对称式多处理器结构中，要取得最优的性能，那么减少多处理器为了访问这些区享资源而带来的竞争就特别重要。SQL Server 11 就能减少这种竞争，提高系统的可伸缩性。 :W V-IDU"
ePxEA7
SQL Server 11增加了许多新特性，它们能极大地帮助数据库管理员支持决策支持和混合负载应用。他们面临的挑战是如何使运行在服务器上的各种应用能获得预料的性能。增加内存通常能提高系统的性能，但是对它们进行有效地管理可以产生最佳效果。SQL Server 11 的缓冲区管理程序，与查询优化器一道，提供新的缓冲区管理策略。这些策略有助于联机事务处理，决策支持和混合负载型应用。 o yvye )
\_VPJ L,nL
在内存管理方面，SQL Server 11现在提供以下新特点：多数据缓冲区，顺序预取数据页面(使用大块I/O)，新添的即取即弃数据缓冲策略，用户对每个缓冲配置的完全控制，和全自动的查询优化器支持，下面我们来详解它们。 + ,5x#
C$e)$VFy'
二、 多数据缓冲区 T[]vN0q~b
%IV_IYxB
SQL Server 11允许用户在内存建立多个缓冲区，换句话说，用于数据缓冲区的内存能被分成许多个命名缓冲区。每个命令缓冲区可以服务于特定的应用(即数据库或者对象)，而且每个缓冲区可以按有利于特定应用独立配置，例如，I/O的块大小，缓冲区大小，和涮新标识等等。被捆绑到缓冲区应用或者对象的所有I/O将在该缓冲区里进行。因此如果只配置一个数据缓冲区，将会有下图所示的“螺旋锁”竞争热点： tfSv{Z6-&
yke$d!
7-pJo%_
d|}3,9yRC
图1：单MRU－LRU缓冲区 wP|.}!D'&n
t Q}@[_6Pe
每个缓冲区功能上是完备的，类似于SQL Server 10的数据缓冲区。被捆绑到不同缓冲区的两个应用彼此之间没有对内存的竞争或者是SMP的同步，例如没有螺旋锁竞争。因此计算机处理能力增加，应用处理效率相应增长。 'D Y p 7n
\Fb5r_
iX1dTN.'
vp^~;_@ #p#分页标题#e#
图2：多数据缓冲区 c "=(Z H
~EZ6yi
每个缓冲区可以单独配置和调整。缓冲区的分配和调整可以动态地进行，无须重启服务器，这就给数据库管理员提供一个强大的工具，用于随着系统负载的变化，重新分配内存资源。例如，在建立索引或者批量载数据时，可以将表捆绑到大缓冲区，然后，进行联机事务处理时，把它切换到普通缓冲区。查询优化器能识别表捆绑变化，并重新编译受影响的查询计划。 j94u;vR
C+@fxCP[
命名缓冲区也能用于调整应用，进一步提高性能。例如，把经常访问的索引页面捆绑到不同的缓冲区。将text和image页面保留在不同的缓冲区，防止它们把普通数据缓冲区填满。对于联机事务处理，由于它们产生大量的日志，因此可以把Syslogs表捆绑到为它特殊配置的缓冲区。多个缓冲区也有利于单处理器系统。 bcOau@m
pP?"*
三、 大I/O和顺序预取（sequential pre-fetch） -xoFImpWv
S]kI&d
SQL Server 11能执行多种大小的I/O－2K，4K，8K和16K。多个页表可以在一次I/O中读取到，这样可以减少物理I/O的总次数。查询优化中能为扫描大量数据的查询选择顺序预取策略。经多种测试，能提高四成效率。 v4WQj,
O_F8}"u]k
值得我们提到的是SQL Server 11能对同一对象以不同块大小做I/O。例如，用比读普通数据块大的方式读取正文/图像数据。这样使用不同I/O块大小就允许缓冲区管理充分利用内存。这种策略在混合负载应用中十分有用。 %2! DaO
Ds2?,{d_
要对表进行大块I/O，首先必须将数据缓冲区配置成大I/O(称为缓冲池),然后将表捆绑到该缓冲区上。16K的缓冲池通常有几个16K缓冲区组成，它们自身形成独立的LRU链，并有自己的涮新点。大I/O不是适合于所有应用。在缓冲区内的所有页面一起移动，它们可能在同时被交换出内存缓冲区，这可能对一些应用有不良影响。所以，为了防止这种情况，SQL Server 11查询优化器为每个查询选择最佳的块大小。 :&j=j szQ
]5nG@)-09O
日志表Syslogs也可以捆绑到自己的缓冲区，并能配置最合适的I/O大小。产生多日志的应用可以考虑将I/O大小置为4K或者更大一些。同日志缓冲区一道，这种策略能极大地提高联机事务处理应用的性能。 mOK$p27%
?&*l8v |
四、 即取即弃替换策略（MRU） eJmRV/
N pe*S>
通常，在缓冲池中的缓 冲块是采用基于MRU－LRU键结构的先进先出策略，所有块在被替换之前在内存停留相同的时间。这种方法适用于大多数情况，但在某一些情况下，不会重用的页面可能充乱数据缓冲区，打扰其他用户的扫描。为了防止这种情况，SQL Server 11提供即取即弃替换策略，查询优化器定义一些缓冲块可能为重用的，另一些缓冲块为不可能重用的，缓冲区管理器将在内存保留可能重用的数据页面比不可能重用的页面时间长一些。采用这种策略，能提高一部分应用的缓冲区命中率。 KL~x?LWj
B= No
例如，SQL Server 11只使用几个缓冲块扫描一张非常大的表。这种扫描就不会打乱内存缓冲区的基本组合，其他并发应用不会受到影响。 0yIfR3>#.y
-;KeL8 2;
6Yip `LU0
Rv Tmo
图3：不同的缓冲区替换策略 O<MPTn z
X:'2a:r
五、混合负载 `6@U1 l^H
~]zI :eNI
在客户/服务器结构上，使用计算机能力越来越强的SMP机器，运行负载大小不同的应用，就越来越经济合算。支持混合负载对于任何数据库管理员来说都是一个巨大的挑战。联机事务处理（OLTP）系统要求具有不断增长的吞吐能力，去处理越来越多的小事务，而对于决策支持型（DSS）则要求在分配的时间内完成大作业。系统管理员有许多种不同的策略去支持混合负载，通常，他们把不同的SQL Server分配给不同的应用。在这里，我们是假设在同一个服务器上，同一个数据库中运行在同一台机器上的负载。 (4 Q{b
< .u )Wa
六、 联机事务处理（OLTP）和决策支持（DSS）负载 Z8O4 _' 1
U~qR L;
联机事务处理负载的特点是小事务，涉及少数量的表，没有什么联接，每次修改几条记录。它的关键因素是事务吞吐量。相反，决策支持型应用和报表生成的负载往往涉及全表扫描，经常是多张表联接用去产生排序的求和统计或者是范围统计信息，这些作业通常要求在短时间内完成。它们的要求是响应时间短。 ]G~^ 6h
4 [mH9kYiQ
通常，决策支持查询要读取大量的数据。最简单的处理混合负载的办法是两种应用在一天的不同时间运行，很容易安排配置一些缓冲区，并将来自OLTP和DSS应用的对象捆绑分别到这些缓冲区上。一旦OLTP应用处理结束，DSS应用便启动，并使用它们的对象替换内存缓冲区，这些都是动态的，不必重新启动服务器。 #p#分页标题#e#$t. [|\=H
<NjlxaH
但是，如果两种负载要同时并发运行，但是在不同的数据库上，我们可以把两个数据库捆绑到不同的内存缓冲。 B'h! ~'/K8
&:Ldbm
七、 查询优化器 ZTR='` 0Y@
x85 48)?U,
基于成本的查询优化与SQL Server 11的特性结合完美无暇。例如，优化器现在可能为某些查询选择大I/O的全表扫描。用户配置缓冲区，优化器为每个查询选择合适的I/O大小和理想的缓冲区替换策略，这些计划可以使用“SET SHOWPLAN ON”来查看。根据应用，优化器可能会对同一张表选择不同I/O大小，和分配缓冲区。例如，它为TEXT和 IMAGE字段选用大I/O，而为它们所基于的表选用2KI/O。优化器也有能力识别这种情况，对于某张表不涉及物理I/O，或者表报描会用去大半内存缓冲区。从而进行明智决策。 MX\d~ _}
g{~M) zq
SQL Server 11有配置选项用于在不同级别调整内存缓冲区的行为。系统增加了新的命令，每条查询语句可以覆盖优化器策略──用户能自己指定查询所使用的索引，I/O大小和缓冲区替换策略。一旦内存缓冲区有所变动，优化器便重新编译受到影响的存储过程。 Ja"xm+''c
j1B.1JHG:
八、基于成本的缓冲模型 9 1AFh,mL
A`{K*
SQL Server 11优化器有一个新模型为查询估算物理I/O数量。这个模型考虑了缓冲区捆绑，I/O大小和缓冲区替换策略。例如，在数据缓冲区比较小的情况下，采用大I/O做全表扫描比只有10％选择度的非集簇索引要快。 V)vDl.]
f gJ<?p%+;
对于连接处理，缓冲区管理特别重要。做连接时，内表需要扫描多次，因此，如果把内表放在内存缓冲区里，物理I/O次数将会减少。优化器会考虑这种情况。优化器也可以为每个查询考虑多达八张表的缓冲区利用情况 4Ns\"u d
:4(v47
]-r,3<f
U5}gay
图4：混合负载的优化 i~tPhEe9
Wc B34b
九、结束语 mTG6& NOG
/x{,rn
最近，我们为一家大电信公司做了一次测试。所有试验在Turbolaser,DEC Alpha UNIX 上进行，而且所有资源和参数完全相同。测试结果如下： ^6>3c}{
o"` R-O
表1： 系统11加快BCP速度 ##"vl* ~
e)hoRx.A
Table RowSize(bytes) #Rows #Pages SQL Server 10 SQL Server 11 h*e &J*GVF
rows/sec MB/hour rows/sec MB/hour Nm:{yy@#;
acnt 507 672,708 127,665 410 475.37 484 560.93 FDmLA],k(Y
acnt_age 83 727,622 31,637 1,940 400.65 4,737 1,391.16 j 3o]XgR
acnt_hstry_cmnt 285 541,180,042 243,894 1,835 530.12 7,020 2,028.34 c tP'\79]
acnt_blncfwd 438 3,285,594 46,961 482 475.62 1,920 1,892.77 > /zA]\^X
4R!W-B5
表2：系统11使用大IO提高全表扫描的性能 bQaDIry'u
4|dT`b"
Table row size(bytes) LIOs(#pages) PIOs(with 16k) SQL Server 10 2K (sec.) SQL Server 11 2K (sec.) SQL Server 11 16K (sec.) xO"F!83O/
uncached cached uncached cached uncached cached 'X5E-Cb
acnt 507 127665 19572 282 7.7 245 8.2 81 9.4 02btp&T4B
acnt_age 83 31637 4849 72 3.9 74 4.4 33 4.5 OVMnw5&]
acnt_hstry_cmnt 285 243894 37402 572 31.0 587 36.5 271 39.3 8cTVBE
acnt_blncfwd 438 46961 72010 1051 34.5 1060 41.0 490 37.8 .KG;V|4g>
lZkt+4_Q!
标识列（Identity） 8TK` fy
6_:E6#$*S
SQL Server 10.0在表中提供了自动递增的唯一键列。如果定义一列为 IDENTITY 类型，它会被自动修改。 /{41["6<6
5 vu{J
IDENTITY有如下一些特性： P=1d:
#p#分页标题#e#nBXKr6VFg
只允许正整数，不允许有零（0），空（NULL），及任何分数。 E&F8Z/nVA
这列的值从1开始，总是递增1，直到最大值。 |c<<Hb
当达到最大值时，不能折返，如再想插入值则返回出错信息。 q:uy"^G*
这个标识列不能修改，对一行数据修改时，这列上的值不变。 ` k74--E
当插入一行时，不用插入标识列；标识列值自动产生并插入值。 M!b^Il'2
如果需要唯一标识列值，那么要在这列上建立唯一索引。 VA nw{z_>U
发生系统故障或使用 shutdown with nowait 时，可能会产生不连续的值。 aq- B7
进行 rollback 或 delete 操作时，也会产生不连续值；标识值不重用。 Uow`-f9
建立标识列 (HHW3zJ
G~bdcCU/+
表的标识列是带有 IDENTITY 属性的 numeric 类型。由于 numeric 类型允许有小数，但是标识列不允许，所以必须定义小数位为零，例如： [&Qr nYFXk
#65MoZ0
create table sales_daily \W$J;;n6bP
(sto_id char(4) not null, [_ATAWx
ord_num numeric(10,0) identity, 3UyzsJh"`Q
ord_ant money null) t}B~
$Z#<dK6bzY
标识列允许的最大位数为38。 w1bZ7 9-3
u^KXG_^w.
预置（preburn）问题 {*bH h/8
+{N\~};
SQL Server 如何为标识列产生下一个值？ K*r>S{zbI_
{MHIap9(
SQL Server 有一个用 sp_configure 设置的配置值，叫 identity burning set factor，产生如下作用： R+Ir^jf
GA[i!O m
初始时，一组 n 个值预置或存储在内存中。这些值为随后的 n 个插入操作保留。 ,q qXz
9o5x ):F
SQL Server 上的每个标识列有自己的预置值组。 sx{U { Zm
y;Eb25t)
每个插入操作从预置值组中取值。 XM5/c([
sKUe8$~
当这组值用完后，产生另外一组 n 个值用于随后的 n 个插入操作。 y" M :f1
%vEQjp&3
identity burning set factor 是基于一个标识列的最大值的百分比。缺省的百分比是 0.05% 或 0.0005；然而 sp_configure 的值必须是整数而不能带小数。因此为 sp_configure把系数乘 10 power 7 转变为整数。计算缺省系数： >JLpVI!
):liOV]e
0.0005 * 10 power 7 = 5000 U+H*TGb[
Au[.9L
sp_configure 把值 5000 存储起来。sp_configure identity burning set factor 的值在 1 和 9,999,999 之间。要确定基于希望预置的值的 sp_configure 值，可使用下面的公式： +3pP}0Dk
Ht}@'HaP
preburned_set / (maximum + 1) = percentage 7EU~i}
percentage * 10 power 7 = identity burning set factor .)\Eq{|
i;a! .)6
例如，一个表的标识列最大为 4 位数，即最大值为 9999。如果想为这列预置 10 个值，利用下面公式： r"nJedLAp
r* C?w-
10 / (9999 + 1) = 0.001 (0.01%) )L?=u{
0.001 * 10 power 7 = 10000 vA% MUSfO
u>Q?_/{U:
所以如下配置 identity burning set factor: |K/Gj'Vv
t>e7LEj4
sp_configure "identity burning set factor", 1000 pEe`+g
reconfigure wT=KUc@vS
ZGY sPO
有两点需要注意： 4LiwZS^2
6sS|[IS #p#分页标题#e#
因为是用 sp_configure 设置的值，所以同一个预置系数在服务器范围内适用于所有的表。 =wq a0A%
如果发生系统故障，大的预置值会引起大的断带。 |u>VQi%
预置系数在服务器范围内起作用 Wofgo9i
{W&L`n
在服务器上不同的标识列可能大小不一样。对于一个给定的预置系数，要想知道为一个指定的表分配了多少个值，使用下面的公式： Y[U^3D
nV<cdWK
preburned set size = (maximum + 1) * (preburning factor * 10 power -7) (>[ zz6
|g#pC5THh
例如缺省的预置系数为 5000 +s\Bet%h
zLN_h$.g
一个表的标识列定义为 numeric(4,0)，将指定(9999 + 1) * (5000 * 10 power -7) = 5 个值，在 5 个插入操作之后，新的一组，紧接着的 5 个稍大的值为后面的插入操作保留。 W)v8+9'd
s4o(lS;n
一个表的标识列定义为 numeric(6,0)，将指定(999999 + 1) * ( 5000 * 10 power -7)=500个值。 可以通过使用用户自定义类型，使这个范围标准化。例如： G .TzQ@h
mgq $<<
sp_addtype sitekey, numeric(6,0), "identity" R8]i
MT8' HSj+
把这个类型用于相应的地方，前面的例子中的建表语句将改为如下方式： )m Um yX[
W I^d'Z
create table sales_daily v;pFO%N
(stor_id char(4) not null, ~x}Zs&>
ord_num sitekey, ]qs)KX~{
ord_amt money null) SORoy/ =
xcwhH (r
大的预置数集合可能导致大的断带 k :X0Tl
zBDy0f2
当发生系统故障，如果预置的系数越大，则标识值的断带越大，因为在内存中所有没被使用的预置数都将丢失。例如：如果定义标识列的最大值为 numeric(38,0)，即使使用最小的预置系数，系统故障时可能丢失几百万个值。shutdown with nowait 会产生和系统故障同样的断带。 %[J Dn;-
N=L1 |K@@
在某些环境中 dump/load 可能引起标识值的丢失。对象分配管理程序(OAM)的页面上为每一个对象存储了已使用的页面的页号，或已分配但未使用的页面的页号。Backup Server 直接从硬盘读取数据；OAM 页面存有最大的保留的标识值，而已用的最大值和保留的最大值在这个表的内存结构中保持跟踪，当清空内存结构或服务器用不带 nowait 的 shutdown 关掉时，SQL Server 把最大的已用值写回 OAM 页，如果在dump时，最大的保留值和最大的已用值不一样，那么最大的保留值被写出，并在 load 时恢复，导致标识值的丢失。Sybase 正在着手解决这些问题。 [U9uvW@n
gY)7x#
hhX `X~}
5tFXU7S