级别： 初级 Scott HayesDGI
2004 年 2 月 01 日 DB2 Magazine 专注于 DB2，面向的读者有数据库管理员、分析人员、程序员、设计人员、顾问和 MIS/DP 经理。DB2 Magazine 分季发行，涵盖了所有 DB2 平台（包括 IBM AIX、Hewlett-Packard HP-UX、Sun Solaris、SCO UnixWare、Linux、Microsoft Windows NT、Microsoft Windows 95、Microsoft Windows 98、IBM OS/2、IBM OS/400、IBM MVS、IBM OS/390、IBM VM 和 IBM VSE）上的各种主题。
在一次针对 VLDB 查询的物理 I/O 和内存调优的实情调查中，得出的结果非常令人满意。
在为最近一次用户群会议准备报告的过程当中，为了探索和验证物理 I/O 和内存调优的最佳方法，我们小组在 DGI 进行了很多性能实验。通过这些实验，我们提取出了 10 条法则（虽然有些法则令人惊讶），基于这些法则进行适当的配置可以获取最大的查询吞吐量。我们只是更改一些配置参数、缓冲池定义和表空间定义及参数，便成功地将基准查询的历时减少了 50％ 以上。如果您想让数据仓库更快、更好地交付业务结果，就需要仔细阅读本文了。任何一处更改都可能带来一些性能提升，但是最能给人留下印象的性能改善来自于以下整个过程。
在开始之前，先讲一点背景知识。为了进行实验，我们需要一个大的表。当然，这里所说的“大”是相对而言的。许多 DB2 UDB 用户都拥有相当健壮和可伸缩性很好的 IBM RS/6000 或 Sun Microsystems Unix 设备，这些设备通常具有多个 CPU（SMP 4、6、8、12、16 或者达到最多 24 路）。DB2 Enterprise Edition 最常用于这些环境中，因为它可以扩展到几百 GB。当仓库大小从上述几百 GB 增加到几 TB 时，又有 DB2 Enterprise-Extended Edition 足以胜任，这正是得益于 DB2 几乎无限的线性伸缩能力。在我们的实验室中，有一台两路 SMP 的 RS/6000 F40，它具有 512MB 内存和 34GB 磁盘空间。考虑到该硬件的容量，我们决定创建一个大小为 556MB（大约是 1/2GB） 的表。对于这么大的一个表，我们的物理 I/O 需求应该是稳定的，并且能够很有信心地保证它足以度量 I/O 配置的变化。这个表包含 470 万行来自 www.breakthroughdb2.com Web 服务器的命中数据（hit data）。
现在，我们就转移到这些发现上来吧。
开箱即用，DB2 UDB 充满机遇
我曾经与很多公司打过交道，它们安装了 DB2，使用了简单的
CREATE DATABASE OURDB 语句，创建和装载了表，并且利用缺省配置投入了生产。
因此，为了建立一条性能基线，并演示如何调优缺省配置，我们首先以一个缺省配置实例中的缺省配置数据库来开始性能测试。我们的数据库使用在实例拥有者的本地文件系统中创建的缺省 SMS 表空间定义（USERSPACE1、TEMPSPACE 和 SYSCATSPACE），这是 4GB SCSI 磁盘上的一个 2GB 的文件系统。
在性能测试中所使用的 SQL 查询主要是选择大部分的表列，然后请求将结果按第三列降序排序和按第四列升序排序。这里没有可用的群集索引（clustering index），选择语句中也没有 WHERE 谓词。访问计划很简单：扫描表，排序扫描结果，扫描排序结果，然后返回。（请参阅
清单 1
中的 DB2 Explain 输出。）
| 清单 1：基准查询的 DB2 Explain 计划 |
SQL Statement:
select AUTHID, ORIG_HOST_DOMAIN, HIT_TIMESTAMP,
OPERATION, BYTES, STATUS
from v7i1.weblogs_tb
order by AUTHID DESC, BYTES DESC
Estimated Cost = 2511669
Estimated Cardinality = 4759664
Access Table Name = V7I1.WEBLOGS_TB ID = 1,4
| #Columns = 6
| Relation Scan
| | Prefetch: Eligible
| Lock Intents
| | Table: Intent Share
| |
Row : Next Key Share
| Insert Into Sorted Temp Table ID = t1
| | #Columns = 6
| | #Sort Key Columns = 2
| | | Key 1: AUTHID (Descending)
| | | Key 2: BYTES (Descending)
| | Sortheap Allocation Parameters:
| | | #Rows = 4759664
| | | Row Width = 60
| | Piped
Sorted Temp Table Completion ID = t1
Access Temp Table ID = t1
| #Columns = 6
| Relation Scan
| | Prefetch: Eligible
| Return Data to Application
| | #Columns = 6
Return Data Completion
End of section
Optimizer
Plan:
RETURN
( 1)
|
TBSCAN
( 2)
|
SORT
( 3)
|
TBSCAN
( 4)
|
Table:
V7I1
WEBLOGS_TB
|
|
对于每一次测试，我们都测量了查询历时、总排序时间，以及所花费的 CPU 时间。正如预料的那样，开箱即用配置的性能结果波澜不惊：
- 历时： 800 秒
- 排序时间： 504 秒
- CPU 时间： 311 秒
所以我们开始去发现如何更改配置会产生更好的结果。
TEMPSPACE - 只是移动了一下
在我们的第 2 次性能测试中，我们使用不同的文件系统上的一个 SMS 容器创建了一个名为 TEMPSPACE2 的临时表空间，该文件系统存在于一个不同的物理磁盘上。创建好 TEMPSPACE2 之后，我们撤销了 TEMPSPACE1，并再次运行基准查询。
结果令人震惊：
- 历时： 610 秒
- 排序时间： 386 秒
- CPU 时间： 324
秒
我们只是将临时表空间移到了一个不同的物理磁盘上，就将查询所经历的时间缩短了大约 25％。
为并行 I/O 添加 TEMPSPACE 容器
在第 3 次和第 4 次性能测试中，我们使用了一个 TEMPSPACE 表空间，该表空间在这两次测试中分别具有两个和四个容器目录。我们小心地将容器放到数据库本来不使用的磁盘上。尽管在这两次连续的测试当中我们都测到了一些性能提升，如
表 1所示，但是结果并没有预期的那样显著，这是由其他的配置缺陷（请参阅有关足够内存的那一节）所致。在第 4 次测试的最后，我们将基准查询的历时缩短了 27.5％。
表 1:
TEMPSPACE 容器
作好准备 - PREFETCH
至此，TEMPSPACE 的 EXTENTSIZE 和 PREFETCHSIZE 已经有 32 4K 页。DB2 能够在预见 CPU 处理能力的情况下异步地预取数据，从而最小化 I/O 服务延时所带来的影响。PREFETCHSIZE 指出每次预取请求需异步检索的数据量。通常来讲，PREFETCHSIZE 应该是 EXTENTSIZE 的倍数，并且该倍数应该等于容器的数量。在第 5 次性能测试中，我们改变了 TEMPSPACE 表空间，并将 PREFETCHSIZE 设置为 128 （4 个容器与 EXTENTSIZE 32 相乘）。
表 2演示了由于加大预取的数据量而导致历时缩短 4％。总的说来，基准查询的历时比起最初的测试来要减少大约 30％。
表 2: 预取的值
并行喜忧参半
由于在四个不同磁盘上使用四个容器，并使用了 PREFETCHSIZE 的适当值 128，我们对提高吞吐量的期望值很高。在接下来的测试中，我们启用了分区内并行（
db2 update dbm cfg using INTRA_PARALLEL YES ）并将数据库缺省的并行度设置为
4（
db2 update db cfg for DBNAME using DFT_DEGREE 4 ）。
第 5A 次测试是一场灾难。查询的历时增加到了 638 秒（增加了 14％），CPU 时间上升到 570 秒（增加了 83％），排序时间窜升到 1,234 秒（增加了 400％ 之多），图表中都容不下了。可以说这些结果令人失望。通常，一个数据库的缺省并行度被设为 ANY （或 -1），意即缺省的并行度应该由可用的 CPU 数量得出。假定存在 CPU 资源冲突，我们将
DFT_DEGREE 更改为
ANY ，之后运行第 5B 次测试就要成功一些。
第 5B 次测试最终的查询历时是 533 秒，比第 4 次测试快了 5％，但这是有代价的，CPU 时间增加到了 508 秒（增加了 63％），排序时间增加到 493 秒（增加了 61％）。
从第 1 次测试到第 5B 次测试，我们将基准查询的历时减少了 33.3％，而 CPU 耗时增加了 63％。因此，虽然分区内并行（intrapartition parallelism）可以缩短查询历时，但这是以实实在在的 CPU 开销为代价的。而且，我们已经了解，如果将并行度设置得过高（强制使用高于缺省情况下一般会选择的程度），就会产生恐怖的性能后果。这一发现表明，将
DBM CFG MAX_QUERYDEGREE 设置为等于物理分区上可用的 CPU 数量，以防用户不恰当地请求过多的并行度，这一点很重要。
有了四个容器，适当的 PREFETCHSIZE 值，加上分区内并行，性能提升就有望高于现有的结果。因此，我们查找瓶颈所在。或许瓶颈就在于我们拥有的 NUM_IOSERVERS 或 NUM_IOCLEANERS 值太小。
不要过度分配 IOSERVERS 和 IOCLEANERS
测试机器有 6 个不同的物理磁盘，其中 5 个是由数据库使用的。在接下来的测试当中，我们将 NUM_IOSERVERS 从缺省值 3 增加到 7。假定附加代理会改善并行 I/O，并且在以额外的 CPU 开销为代价的情况下，历时将有所缩短。
令我们惊讶的是，第 6A 次测试的历时增加了大约 20％，排序时间窜升了 39％，CPU 时间增加了大约 6％。附加代理导致过多的 I/O 争用，使得查询历时有所延长，产生了我们不愿看到的影响。
下一步就是要将 NUM_IOSERVERS 减少至 4，而将 NUM_IOCLEANERS 从 1 增至 2。与第 6A 次测试相比，第 6B 次测试运行得更好，但是比第 5B 次测试要稍微差些（参见
表 3）。当增加更多的 NUM_IOSERVER 和 NUM_IOCLEANER 代理时，我们是期望缩短历时。但是，添加这些一般情况下有益的代理实际上却降慢了响应速度。还是存在某个瓶颈，因此我们将注意力转移到缓冲池的性能上。
表 3:NUM_IOSERVERS 和 NUM_IOCLEANERS
并行要求有足够内存
在上一次测试中，我们记录了 122,839 次预取读和 122,070 次 PREFETCH 请求。我们计算缓冲池和表空间性能时所使用的一种度量是每次请求的异步页读取数（asynchronous pages read per request，APPR）。APPR 是对预取有效性（effectiveness）的度量，122,839/122,070 相当于为每次预取请求只预取了一页。我们本来是要 DB2 为每次预取请求预取 128 页的，因此 122,839/122,070 是一个糟糕之极的值。如果 APPR 大大小于 PREFETCHSIZE，那么就意味着缓冲池太小或者过度拥挤，不足以适应预取 I/O 请求。 最后，我们发现了瓶颈。
在第 7 次测试中，我们将 IBMDEFAULTBP 缓冲池的大小从 3.9MB 增至 64MB。消除了这个内存瓶颈之后，所有的配置参数都能和谐地工作，并产生非常令人满意的性能结果。这次 APPR 上升到了 16，而历时则缩短了 15％，从 542 秒变为 464 秒。CPU 时间缩短了 8％，从 512 秒变为 469 秒；排序时间缩短了 24％，从 518 秒变为 396 秒。到第 7 次测试为止，我们已经将历时从最初的 800 秒缩短了 42％。
对于数据仓库，一个缓冲池最好
我这么写过吗？我从未想过我会说或者写这样一句话，但是接下来的测试却分明演示了在一个数据仓库环境中使用单个缓冲池的好处。
在第 8A 次测试中，我们将 TEMPSPACE 表空间指派给它自己的 TEMPBP 缓冲池。IBMDEFAULTBP 和 TEMPBP
都设为 64MB。很令我们惊讶的是，这次历时竟然增加了 4.5％，CPU 时间增加了 3.6％，而排序时间则增加了 1.2％。
因为我们愿意贡献 128MB 内存给 DB2 缓冲池，所以在第 8B 次测试中做了以下事情：
- 将缓冲池
IBMDEFAULTBP 大小增至 128MB。
- 将 TEMPSPACE 表空间指派给 IBMDEFAULTBP。
- 撤销 TEMPBP 缓冲池。
- 重新启动数据库。
与第 7 次测试（一个 64MB 缓冲池）的结果相比，将仅有的缓冲池大小翻倍至 128MB 获得了丰厚的回报。这次历时从 464 秒缩短至 416 秒（回落了 11％），CPU 时间缩短了 4％，而排序时间也从 396 秒骤减至 274 秒 —— 缩短了 30％ 之多！到第 8B 次测试为止， 基准查询的历时已从最初的 800 秒缩短了 48％。
图 1总结了缓冲池配置结果。
图 1:
缓冲池配置结果
尽管这些发现倾向于为数据仓库环境使用单个缓冲池，但我们仍然建议您：
- 在任意给定环境中，将 SYSCATSPACE 表空间放入它自己的缓冲池中。
- 在事务处理环境中，将 TEMPSPACE 表空间与它自己的缓冲池隔离开来，使用多个已经定义了其目的的缓冲池，并根据当前主要执行的 I/O 类型（同步或异步）小心地将表空间指派给缓冲池。
并行 I/O 法则：明智地设置多个容器
至此，我们一直都强调 TEMPSPACE 表空间 I/O 路径的优化。然而，我们那个 556MB 的表却仍然驻留在仅有一个容器的 SMS
表空间中。在第 9 次测试中，我们将这个表放入在两个不同磁盘上有两个容器的一个 SMS 表空间中。在第 10 次测试中，我们将这个表移到在两个不同磁盘上有两个裸设备（raw device）容器的一个 DMS 表空间中。在这两次测试中，表数据容器所在的磁盘与 TEMPSPACE 所使用的磁盘是分隔开的。
正如我们所期望的，添加容器之后我们得到了回报，不管 SMS 还是 DMS，这次查询历时又缩短了 5％。不过，DMS 情况下 CPU 时间少了 2％，排序时间少了 12％。
图 2将只有一个容器的 SMS、有两个容器的 SMS 以及有两个容器的 DMS 它们的结果作了比较。与最初的基准测试相比，历时已经从 800 秒缩短至 395 秒，大约缩短了 50％（参见
表 4）。在没有添加、修改或删除索引，没有更改群集顺序，也没有以任何方式更改 SQL 的情况下，我们成功地将查询历时缩短了一半多。
图 2:SMS 与 DMS
表 4:
性能测试结果
排序溢出代价不菲
至此，我们面临的挑战已转变为尽可能快地排序并返回 556MB 的数据。并不是所有的排序都有这么大。实际上，对于事务处理应用程序，大多数排序都应该相当小 —— 远远小于 1 MB（256 4K 页，即缺省 SORTHEAP 大小）。当排序大于 SORTHEAP 大小时，就会发生排序溢出，这时，DB2 就会通过指派给临时表空间的缓冲池，将排序数据从 SORTHEAP 内存移出到 TEMPSPACE 表空间。
为了理解排序溢出的性能代价，我们使用一个基准 SQL 查询，该查询要求 1.5MB 的 SORTHEAP 内存。我们使用大小分别为 128、256 和 512 4K 页的 SORTHEAP 来运行这个查询。我们预计让前两次测试发生排序溢出，第 3 次测试则不发生溢出，直接在 SORTHEAP 内完成。
在解释计划中，对于第 4 次测试，我们将 SQL 语句中的 ORDER BY 子句去掉，只留下一个非常小的索引 RID 排序。
图 3显示了结果。
图 3:
排序溢出的代价
显然，您可以研究这些结果，并得出结论：溢出不管是大是小，都是溢出，因为 128 和 256 4K 页之间的结果实质上是一致的。不过，如果提供了足够的 SORTHEAP 内存（512 4K 页）以避免排序溢出，则历时缩短了 10％，排序时间减少了 20％，而 CPU 耗时也减少了 15％。总之，排序溢出的代价的确是很高的。通过将 SQL 语句中的 ORDER BY 子句去掉，可得到最低资源消耗情况下的最快响应速度。
在 RAID 设备上创建表空间之前，使用 db2set 命令设置环境变量
DB2_STRIPED_CONTAINERS=ON
。通过这样做，您可以将物理 I/O 减少 50％ 之多。
对于在 RAID 设备上创建的表空间，表空间 ID 必须要么在环境变量 DB2_PARALLEL_IO 中列出，要么使用一个星号（*）表明所有表空间都定义在
RAID 设备上。通过使用命令
db2 list tablespaces 您可以发现 Tablespace ID。如果表空间
3、4、5、6 和 7 在 RAID 上，那么应该使用的命令是
db2set DB2_PARALLEL_IO=3,4,5,6,7
。
通过设置 DB2_PARALLEL_IO，表空间 PREFETCHSIZE 带有特殊意义。PREFETCHSIZE 按照 EXTENTSIZE 被分成多块，以达到 I/O 并行度。
如果没有设置这个环境变量，I/O 并行度通常由容器数量得出。因为 RAID 常常只有一个容器，因此将 PREFETCHSIZE 设为 EXTENTSIZE 的倍数就很重要，这样可以提供足够数量的 IO_SERVERS（至少每个物理磁盘有一个），并可以将表空间指派给大到足以适应预取请求的缓冲池。
如果数据库支持 OLTP 应用程序，因而不需要预取 I/O，那么不要将表空间 ID 号包括到 DB2_PARALLEL_IO 环境变量定义中，也不要将 PREFETCHSIZE 设为等于 EXTENTSIZE。
结束语
正如我们的实验所展示的，如果小心地调优物理 I/O，查询历时可以减半。这些调优包括：
- 提供足够的缓冲池内存，以适应预取 I/O 需求。
- 提供多个容器，以允许并行 I/O，并将这些容器分放到多个磁盘上。
- 允许分区内并行（但不要允许 DEGREE 超过 CPU 数量）。
- 对于有不止一个容器的表空间，将 PREFETCHSIZE 设置为 EXTENTSIZE 的倍数。
- 为 NUM_IOSERVERS 和 NUM_IOCLEANERS 提供足够的代理数，但不要将这些代理数设置得过高。
对于较小的排序，排序溢出的代价很高。可以明智地增加 SORTHEAP 内存，以减少会发生溢出的排序，但是首先要寻求减小排序的大小（提高 WHERE 谓词的过滤功效），或者，如果可能的话就一起解决这两个问题。在 SORTHEAP 内完成的排序最后通常会导致计算机达到 CPU 超负荷（CPU bound）状态，并限制了应用程序的伸缩能力。所以，只有在必需的时候才排序。
参考资料
"Advanced Bufferpool Tuning,"
IDUG Solutions Journal,
Spring 2000
"
10 佳性能技巧
,"
DB2 Magazine,
Quarter 1, 2001
关于作者  | | | Scott Hayes是 DGI 的总裁，DGI 是一家性能管理工具和咨询公司，专注于 UNIX、Windows NT 和 Linux 平台上的 DB2 UDB。他还是一名 IBM DB2 Gold Consultant，同时也是一名 DB2 UDB Advanced Certified Technical Expert。
|
对本文的评价
| 
