数据架构师：DB2 数据仓库性能，第 1 部分

把 OLTP 调优技能转换为对基于 DB2 的业务智能化系统的有效性能管理

打印本页

将此页作为电子邮件发送

英文原文

级别： 初级

Robert Catterall, 工程主管, CheckFree

2009 年 6 月 15 日

当前，各个行业越来越关注数据仓库了，因为公司希望从数据资产中找出有意义的业务智能化信息，从而驱动更好的决策。本文我们将介绍熟悉 DB2 在线事务处理 (OLTP) 的老手是否能够同样有效地支持数据仓库，尤其是在性能管理方面。
来自 IBM Database Magazine 中文版。

在去年 10 月在华沙召开的 International DB2 Users Group 大会上，IBM 的 Willie Favero 做了关于数据仓库的演讲，其中引用了 1983 年引入 DB2 数据库管理系统时的公告。这份公告把 DB2 定位为决策支持应用程序的优良基础（当时 “数据仓库” 这个词还没有出现）。

我还记得 DB2 的诞生，因为我当时正在为 IBM 工作。 DB2 确实是业务智能化 (BI) 系统的绝佳选择，但是对于负责构建和维护操作性应用程序的 IT 人员来说，它的编程生产力优势同样很有吸引力，比如与英语相似的数据操纵语言和由 DBMS 选择数据访问路径。应客户的要求，IBM 在各个 DB2 版本中不断改进性能，极大地提高了应用程序吞吐量。 Linux, Unix, and Windows (LUW) 服务器和大型机上的 DB2 作为处理事务性工作负载的数据服务器越来越流行了，其中许多系统的吞吐量超过每秒 1,000 个事务。

当前，各个行业越来越关注数据仓库了，因为公司希望从数据资产中找出有意义的业务智能化信息，从而驱动更好的决策。长期使用 DB2 支持操作性应用程序的企业和政府机构现在正在构建基于 DB2 的 BI 系统。这一趋势让一些 DB2 在线事务处理 (OLTP) 老手想到了一个问题：他们是否能够同样有效地支持数据仓库，尤其是在性能管理方面。本文就是为这些专业人员准备的。由于这个主题很广泛，我将分两期讨论。

数据仓库与 OLTP 性能管理：性质不同

DB2 数据仓库环境中的性能管理与基于 DB2 的 OLTP 应用程序中的监视和调优很不一样。重要的差异包括：

单独的 SQL 语句与事务。在 OLTP 环境中，典型的事务包含多个 SQL 语句，常常可以在一秒内完成。对于 BI 应用程序，“事务”（用户与系统的一次交互）可能只执行一个 SQL 语句，但是这个语句可能要运行几分钟，甚至一小时以上，而且这不被认为是 “慢”。如果一个报告原本要运行 10 小时，而现在可以在一小时内完成，用户会非常高兴。

事实和维。用于 OLTP 工作的 DB2 数据库很可能采用传统 3NF 设计（或相近的设计）。而数据仓库数据库设计常常采用维，按照 “星型模式” 组织相关的表（位于中心的 “事实” 表和相关联的维表）。

连续与夜间数据库更新。对于 OLTP 应用程序，往往随时进行数据库更新。对于 BI 应用程序，尽管对接近实时地更新数据库值的兴趣正在增长，但是数据仓库数据库通常在夜间进行更新，常常要执行大量提取、转换和装载 (ETL) 操作。在 ETL 处理期间通常不能进行查询访问，这要求必须及时地完成数据库更新过程。

小结果集与大结果集。OLTP 事务程序中的 SELECT 语句通常只获取少量数据库行（常常只有一两行）。数据仓库查询（尤其是那些用来生成报告或在线分析处理多维数据集的查询）可能返回成千上万（甚至上百万）行。

复杂查询与简单查询。OLTP 事务程序中的 SELECT 语句常常非常简单：只访问一两个表，很少或不需要构建动态表，很少或不需要动态地转换数据值或类型。与 BI 应用程序相关的查询可能长达几页，包含十几个甚至更多表的联结，包含嵌套的或通用的表表达式、递归的 SQL、数据值不同的 CASE 表达式和数据类型转换（通过 CAST 声明或标量函数）。

不得不使用的 SQL 与自己编写（至少是自己检查过）的 SQL 。在数据仓库环境中，常常由报告或 OLAP 工具生成 SQL，您在执行它之前没有机会修改它。您要负责设置适当的 DB2 环境，让这些查询能够良好地运行。

总的来说，DB2 专业人员要通过两方面的工作帮助实现良好的数据仓库性能：

• 设置 DB2 环境，让查询有机会良好地运行。
• 有效地调整运行时间过长的查询（无论是否出色地完成了第一个任务）。

本文主要关注 DB2 环境的设置。在下一期中，将讨论数据仓库 SQL 语句的调优。

适当地设置 DB2 环境

建立有助于提高查询性能的 DB2 数据仓库环境涉及系统级和应用程序级措施。对于 DB2 系统，应该注意以下方面：

使用 64 位寻址。更大的 DB2 缓冲区池总是有助于提高性能，但是它们对于 I/O 密集型的数据仓库工作负载尤其有用。许多有经验的 DB2 专业人员已经习惯了 2GB（大型机）或 4GB（Linux/Unix/Windows）的内存空间限制，他们要花点儿时间适应 64 位程序。

当前的服务器拥有极大的内存：在 IBM System z 大型机、System p 服务器（AIX 或 Linux）或 System x 服务器（Windows 或 Linux）上，可以有 1TB 甚至更多的系统内存。这些平台上的 DB2 支持至少 1TB 的缓冲区池配置。

如果服务器有大量内存资源，就应该好好利用 DB2 缓冲区池。我曾经见过在有 40GB 系统内存的服务器上运行的 DB2 只配置了 800MB 的缓冲区池。这太小了：在这种情况下（至少）10-20GB 要适合得多。

请记住，随着缓冲区池大小的增加，磁盘读 I/O 活动的数量会减少。还可以让 DB2 自己选择缓冲区池大小。自动内存管理是 DB2 9 for LUW 中非常受欢迎的特性，现在 DB2 9 for z/OS（与 z/OS Workload Manager 协作）可以通过 ALTER BUFFERPOOL 命令的 AUTOSIZE(YES) 选项管理缓冲区池大小。

利用查询并行性。DB2 可以把处理查询所需的工作分割为片段，并行地执行这些片段，这会显著减少运行时间。使用这个特性需要通过参数启用它。对于 DB2 for LUW，通过把数据库管理程序参数 intraparallel 的值设置为 YES，就可以启用单一服务器中的并行性。在大型机和 LUW 平台上，给定的动态 SELECT 语句（动态 SQL 是数据仓库环境中常用的特性）的并行性取决于特殊注册表 CURRENT DEGREE 的设置。如果 CURRENT DEGREE 的值（这个值应用于给定的客户机 -DB2 连接，而不是系统范围的值）是 1，那么查询就不会并行执行；如果值是 ANY，查询就可以并行执行（如果优化器认为这会提高性能的话）。由 DB2 决定并行度。

在许多站点上，首选方法是把 CURRENT DEGREE 的默认值设置为 1（非并行），对于特定的查询，使用 SQL 语句 SET CURRENT DEGREE 把它改为 ANY 。如果无法使用 SET CURRENT DEGREE（一些查询生成工具可能不支持使用这个语句），可以通过 DB2 for z/OS 的 CDSSRDEF ZPARM 参数或 DB2 for LUW 的 dft_degree 数据库参数把这个特殊注册表的默认值设置为 ANY 。

除了能够在单一服务器中分割查询之外，DB2 还可以通过 DB2 for z/OS 的 sysplex 查询并行性和 DB2 for LUW 的 Data Partitioning Feature 跨集群配置中的多个服务器分割查询。请记住，DB2 for z/OS 查询并行性是提高 zIIP 引擎利用率的好方法，zIIP 引擎是专用的大型机处理器，有助于为组织节省计算成本。

数据库级性能调优

可以通过下面这些与数据库相关的措施让 DB2 数据仓库环境更适应查询：

对大型表进行范围分区。范围分区在大型机 DB2 平台上已经存在很长时间了，从 DB2 9 开始在 LUW 服务器上也可用了。这个特性根据键值范围把表行存储在几个不同的物理文件中。它对于促进大型机服务器上的查询并行性尤其重要。在 LUW 服务器上，它还可以通过一种称为数据分区消除的查询优化技术提高查询的性能。

对于哪些表应该进行范围分区，并没有绝对的规则，但是可以考虑对包含一百万或更多行的表进行范围分区。为表选择的分区键（可以是单一列，也可 以包含多列）取决于您的需要，但是基于时间的键（例如日期列）对于数据存档过程是非常高效的。注意，范围分区与散列分区算法不同，DB2 for LUW 在使用 Data Partitioning Feature 时使用散列分区算法把表行分布到多服务器集群的节点上。

维持丰富、准确的编目统计信息。在我看来，DB2 具有市场上最好的查询优化器（IBM 首创了基于成本的 SQL 语句优化），但是它必须有足够的信息，才能做出合理的访问路径决策。查询优化的关键输入是 DB2 编目表中的统计信息，所以这些统计信息应该是最新的。最好的实现方法是定期运行 RUNSTATS（DB2 for LUW 系统上的命令，DB2 for z/OS 环境中的实用程序）。注意，DB2 9 for LUW 有一个自动统计信息收集特性。

编目统计信息是否丰富也很重要（DB2 对数据库中的数据了解越全面，查询的性能就可能越好），所以应该收集您能够收集的所有信息：关于索引和表的统计信息，尽可能多的列的基数和分布信息。请记 住，RUNSTATS 收集的信息越多，它消耗的 CPU 时间就越多；因此，如果处理能力不足，可能需要把列统计信息生成限制在查询谓词中使用（或很可能使用）的列。

利用索引。对于支持 OLTP 应用程序的数据库，人们在为表创建索引方面往往非常保守，这有一个很合理的原因：在表上定义的每个索引都会增加每个 INSERT 和 DELETE 操作的成本（如果更新的列是索引键的组成部分，UPDATE 操作的成本也会增加）。

在 BI 环境中，数据获取（而不是数据更改）操作的性能往往更重要；因此，与 OLTP 数据库相比，在数据仓库数据库中创建更多索引通常是有意义的。但是，也不要为数据仓库表创建过多的索引，否则定期的 ETL 数据更新过程可能无法及时完成（这会导致数据仓库向查询 “开放” 的时间推迟，有时候会让用户不满）。

在 OLTP 环境中，我一般会把每个表的平均索引数量限制在四或五个。对于 BI 系统中的数据库，我觉得每个表八到十个索引更合适，但是我不会一开始就创建这么多索引。相反，我会先为每个表创建五或六个索引，如果以后发现需要减少某些 查询的运行时间，那么可以定义更多索引。

明智地使用聚簇。数据聚簇（表行的物理次序）在数据仓库中是个重要的问题，因为常常会大批地获取行（而不是像典型的 OLTP 系统那样获取小结果集）。在连续获取大量行时，引用的局部化（所需的行在目标表中的物理位置相互接近）会对运行时间产生显著的影响。应该尽可能精确地预测 用户希望查询的东西 —— 具有给定的客户 ID 的所有行？关于给定的产品的所有行？某一日期范围内的所有行？

如果两个或更多聚簇键对于某个表都是有意义的，那么可以考虑使用 DB2 9 for LUW 的多维聚簇 (MDC) 特性。大型机 DB2 管理员可以使用 DB2 for z/OS V8 中提供的表分区改进来实现多维聚簇，具体地说，按一个键进行数据分区，然后按另一个键对分区中的行进行聚簇。

接下来要考虑查询

现在，已经设置了 DB2 系统和数据库，为良好的数据仓库性能奠定了基础。然后，用户要执行他们的查询和报告请求，他们很可能会发现一部分查询的运行时间比期望的要长。现在怎么办 呢？现在，要分析这些查询，找到让它们运行得更快的方法。我将在下一期中分享一些有用的经验。

参考资料

• 通过访问 developerWorks 中国 Information Management 专区 的 InfoSphere 产品专题 ，获得更多 InfoSphere 相关的文章、教程和多媒体课件等学习资源。
  
  
• 通过访问 DB2 for z/OS，了解 DB2 for z/OS 产品相关资源。
  
  
• 通过访问 DB2 for Linux, Unix, and Windows，了解 DB2 for Linux, Unix, and Windows 产品相关资源。
  
  
• 通过访问 SQL Reference, Volume 1 (DB2 for Linux, Unix, and Windows V9.5)，了解 DB2 for Linux, Unix, and Windows 中 SQL 相关资源。
  
  

• 下载 IM 软件试用版，体验信息管理软件强大的功能。
  
  

• 通过访问 alphaWorks，获得更多 IBM 的前瞻性技术和资源。
  
  
• 通过访问 IBM Database Magazine 站点 community 专题，获得更多用户体验和交流信息。

关于作者

Robert Catterall 是 Catterall Consulting 公司的总裁，该公司致力于帮助客户使用关系型数据库技术（尤其是 DB2 ）。