内容


使用 DB2 V8.2 Design Advisor

第 1 部分,在专家的帮助下设计 OLTP 数据库

Comments

系列内容:

此内容是该系列 # 部分中的第 # 部分: 使用 DB2 V8.2 Design Advisor

敬请期待该系列的后续内容。

此内容是该系列的一部分:使用 DB2 V8.2 Design Advisor

敬请期待该系列的后续内容。

简介

DB2 UDB V8.2 引入了一种名为 Design Advisor 的新工具,该工具使用范围更广,可用来替代 Index Advisor。除了索引外,Design Advisor 还提供关于物化查询表(MQT)、多维集群表(MDC)和数据分区功能(Data Partitioning Feature,DPF)分区键的建议。 即使对于那些只对索引感兴趣的 DBA,Design Advisor 也好于先前版本的 Index Advisor。本文是由两部分组成的一个系列的第 1 部分,旨在探索 Design Advisor 在在线事务处理(online transaction processing,OLTP)数据库方面的使用,主要关注的是如何设计适当的索引,以取得较高的性能。

通过介绍一些特定的例子、考察一些陷阱和常见问题,并突出强调 V8.2 的增强,我们将演示该工具的效用。第 2 部分将介绍关于 MQT、MDC 和 DPF 分区键的更多细节,重点分析数据仓库环境。

Design Advisor 的价值

Design Advisor 所提供的建议能与数据库调优专家的建议相媲美。对于非专家来说,该工具的好处是可以获得更好的设计。对于专家来说,Design Advisor 可以节省他们宝贵的时间,因为 Design Advisor 可以提供一个初始的设计,然后由专家进一步改进设计。Design Advisor 还可以提供对专家的设计的独立确认。

Design Advisor 可以使性能提高多少百分比呢?这取决于初始设计的质量。然而,提高的幅度是很明显的。题为 DB2 UDB: The Autonomic Computing AdvantageDB2 Magazine 电子书中包含一个完整的案例研究,该研究显示,性能提升了 84%;也就是说,设计经过改进之后,对于相同的工作负载,现在的运行时间是原来的 15%。

Design Advisor 何时有帮助

在很多情况下,DBA 都希望使用 Design Advisor。其中两种重要的情况是,在生产之前使用 Design Advisor,以及在将一个系统投入生产之后使用它。关于在生产之前何时使用 Design Advisor 的例子包括:

  • 在应用程序开发的初始阶段创建一组新的索引。
  • 在应用程序系统测试期间改善性能不佳的查询。

还可以将 Design Advisor 用于生产系统,根据以下情况更改索引:

  • 实际生产情况与开发期间的估计有差别。例如,某一列被访问的频率超过预期;某个表远远大于预期;某些查询比预期执行得更频繁。
  • 在系统的运行生命周期中模式设计发生了改变。应用程序在其运行生命周期中常常会得到调整,例如为生成报告而添加一个新的列或新的查询。
  • 数据库老化。随着数据库的老化,性能将逐渐下降,因为表会越来越大,使用模式也发生了变化,并且工作量也有所增加。

Design Advisor 经常与其他 DB2 特性一起使用。例如:

  • Health Monitor。您可以设置 Health Monitor,使之在出现排序溢出之类的情况时发出警报。然后采用 Design Advisor 来帮助发现有助于缓解这种性能下降的索引。
  • Visual Explain。您可能正在使用 Visual Explain 或相关特性来检查查询访问计划。您也许注意到被扫描的表过多。那么这时可以使用 Design Advisor,看看索引是否能导致不需要扫描那么多表的访问计划。

Design Advisor 概述以及 V8.2 中的新特性

您可以将 Design Advisor 作为一种 GUI 工具,或者通过命令行来使用它。在 V8.2 中,您可以从一个新的地方来访问这个 GUI。在 Control Center 中,可以右击您想要处理的数据库。在弹出的菜单中,选择 Design Advisor...

命令行命令仍然是: db2advis。是使用 GUI 还是使用命令行?这基本上取决于个人偏好。两者在功能上差别很小。

确定需要使用 Design Advisor 之后,请遵循下面列出的步骤来使用该工具。对于每一步,我们列出了 V8.2 中适用于 OLTP 数据库(其中最相关的就是索引)的一些关键的增强。

表 1. V8.2 中的新特性
步骤V8.2 中的新特性
1. 收集和描述提供给 Design Advisor 的工作负载该工具支持其他提供工作负载的方法:(1) 在 GUI 中从一个文件装载工作负载,或者 (2) 从 Event Monitor 获得工作负载
2. 决定何时以及如何运行该工具由于采用了新的工作负载压缩技术,所以运行该工具时所需的资源更少
3. 回顾建议(1) Design Advisor 现在考虑更大范围的索引类型,包括:双向索引和带 INCLUDE 列的 UNIQUE 索引。(2) 有更多关于建议的详细信息:例如,与各条 SQL 语句相关的改进
4. 后续步骤您可以生成关于建议的一个报告,并将其保存到一个文件中

V8.2 中有范围更广的一组自主计算(Autonomic Computing)改进,Design Advisor 是其中的一部分。此领域中的其他改进包括:

  • 工作负载管理
  • 用于调整它们自身的实用程序
  • 快速性能调优
  • 连续系统健康状况检查
  • 简化内存管理
  • 以及其他改进

请参阅 参考资料 一节,以获得更多细节。

案例研究简介

下面的案例研究演示了如何使用 Design Advisor:在一个新的应用程序的系统测试阶段,您会发现,有两个特殊的事务运行速度比预期的要慢很多。经过初步的调查研究之后,您决定看看索引是否能解决这个性能问题。

该案例研究分为以下几个步骤:

  1. 收集和描述提供给 Design Advisor 的工作负载。
  2. 判断何时以及如何运行 Design Advisor。
  3. 查看建议。
  4. 后续步骤。

这些步骤将通过 GUI 进行说明。下一节将说明此工具的命令行版本。

步骤 1 - 收集和描述提供给 Design Advisor 的工作负载

从图 1 中可以看到,Design Advisor 允许我们选择 Design Advisor 将要考虑的特性,其中包括索引、MQT、MDC 和分区。我们只选择索引,因为这正是我们的兴趣所在。

图 1. 选择性能特性
图 1. 选择性能特性
图 1. 选择性能特性

接下来,我们需要为 Design Advisor 提供工作负载。Design Advisor 有一些选项来输入和收集工作负载。在这个案例中,我们让 Design Advisor 从一个包含事务的文件中读取工作负载。结果显示在图 2 中。其他收集工作负载的选项包括:

  • 最近的 SQL 语句(来自动态 SQL 快照)。
  • Query Patroller 语句(更适用于数据仓库数据库)
  • 静态 SQL 语句(来自应用程序包)
  • 解释后的 SQL 语句
  • Event Monitor 语句

最后两个选项只有在 Design Advisor GUI 中才能使用,而命令行版本中没有提供这两个选项。

图 2. 定义工作负载
图 2. 定义工作负载
图 2. 定义工作负载

整个应用程序由 40 个事务和报告查询组成。在我们的文件中,我们选择提供 29 个事务,其中包括那两个引起生产问题的事务。为了避免麻烦,我们没有包括某些临时报告查询。

为获得最佳结果,识别出应用程序将在 DB 上运行的所有 SQL 语句。这样一来,Design Advisor 就可以提供全局最优的建议。下面将按照推荐程度,由强到弱地列出各个选项:

  1. 列出一个完整的工作负载,其中包括与性能问题无关的事务。
  2. 包括所有有问题的事务。
  3. 分别处理每个有问题的事务。也就是说,运行两次 Design Advisor,每个事务一次。

为了达到忽略提供给 Design Advisor 的工作负载中的 SQL 的程度,在解释建议时应该记住这一点。换句话说,应确保建议对于您所知的没有包括在提供给 Design Advisor 的工作负载中的其他事务有效。

在我们的例子中,我们为每个事务赋予 1、10、100 或 1000 的频率。这将导致 Design Advisor 对频率值为 10 的事务的重视程度是对频率值为 1 的事务的重视程度的 10 倍。

步骤 2. 判断何时以及如何运行 Design Advisor

在这个案例研究中,我们在一台测试机器上运行 Design Advisor,因为应用程序还没有投入生产。在这种情况下,我们主要关心的是确保这台非生产机器能反映预期的生产环境,从而使得 Design Advisor 的建议在生产系统中有效。特别要注意的是,需要更新编目统计信息,使它们能表示生产系统。这可以用其他 DB2 工具,即 db2look 来完成。数据库不需要已经装载好数据。(注意:如果您还想要 MDC 建议,那么在表中还需要代表性的样本数据)。

而且,我们希望限制用于建议的索引的磁盘空间。图 3 显示了适用的向导面板。

图 3. 设置最大磁盘空间
图 3.  设置最大磁盘空间
图 3. 设置最大磁盘空间

当系统投入生产之后,Design Advisor 仍可以提供关于系统的建议,这一点在本案例研究中没有演示。在这种情况下,主要考虑的是运行 Design Advisor 对生产系统的影响。下面是在预测这种影响时值要考虑的两个事项。

  • 决定 Design Advisor 所使用资源多少的主要因素是您提供给它、让它分析的工作负载的大小。如果您只是想要关于少量查询的建议,那么与更大的工作负载相比,这种影响更小一些。
  • 理解 Design Advisor 所使用的资源有助于进一步预测运行 Design Advisor 对生产系统造成的影响。Design Advisor 使用 CPU(例如,它产生供 DB2 优化器计算的查询),并在读写 DB2 解释表的时候消耗 I/O 资源。

如果您选择不在正常值班的时候运行 Design Advisor,那么其他的选项有:

  • 安排 Design Advisor 在休息时间运行。
  • 将数据库复制到一个独立的测试系统中,并在那里运行 Design Advisor。

步骤 3. 查看建议

从图 4 可以看到,Design Advisor 生成了一组建议,这些建议将导致大约 20% 的性能提升。建议包括:(1) 保留 CUSTOMER 表上已有的两个索引,并且(2) 创建一个索引,保留用于 DISTRICT 表的一个索引。由于一开始我们就有了一组设计良好的索引,因此有理由对这里获得的性能提升幅度感到满意。然而,您应该还记得,我要修复两个特定的查询,所以我们仍需要弄清楚目前是否达到了我们所定的目标。

图 4. Design Advisor 建议
图 4.  Design Advisor 建议
图 4. Design Advisor 建议

Design Advisor 有新的详细报告,其中对建议给予了更深入的分析。图 5 中高亮显示的行是有问题的两个事务中的一个。该事务得到了很大的改进。

图 5. 工作负载细节
图 5. 工作负载细节
图 5. 工作负载细节

在图 6 显示的下一个面板中,建议表明有些索引可以删除。通过将第一个索引与图 4 中所建议的索引进行比较,我们认识到,它是已有索引中某一个索引的子集。

图 6. 查看未使用的对象
图 6. 查看未使用的对象
图 6. 查看未使用的对象

对于建议应该这样来理解,如果您实施所有建议,那么可以获得显示的性能好处。也许,由于某些原因,您希望挑选建议;但是您需要弄清楚的是,您所挑选的建议集带来的性能好处是未知的。您应该确保它不会降低性能。

问题: 怎么理解关于宽索引的建议?Design Advisor 有时候建议的索引中包含的列数要多于 DBA 通常在索引中包含的列数,比如多于 4 个列。DBA 在实施建议之前,需要判断是完全接受建议,还是减少索引中的列数。

有些建议涉及到对某个已有索引的细微更改。这种建议表示为两种建议:添加新索引,以及删除旧索引。所以,最好是成对接受那样的索引;也就是说,要么同时添加新索引和删除旧索引,要么什么都不做。不要添加了新索引,又保留已有的索引,或者仅仅是删除了已有的索引。

在我们的例子中,我们要添加一个包含两个列并且列的顺序为 colA、colB 的索引,同时删除包含相同两列但列的顺序相反的索引。Design Advisor 为此提供的建议是 "you'll get better performance if you switch the order of columns in the index." 。关于“添加/删除”对的其他例子有:(1) 添加一个索引,该索引是另一个索引的超集(也就是说,新索引包括更多的列),以及(2) 用一个双向索引替代两个单向索引。

问题:删除还是不删除?除了那些组成添加/删除对的一部分的索引外,Design Advisor 还能识别出可以删除的索引。您应该根据风险和收益来作决定。正如您所知道的,删除这个索引是否会带来影响?保留一个索引的最大理由是,您知道某个事务或查询使用了这个索引,但是您并没有将该事务或查询包括在 Design Advisor 所使用的工作负载中。还应注意的是,默认情况下,Design Advisor 着重考虑工作负载中由代价最昂贵的语句组成的子集。所以,在删除索引之前,有必要检查哪些语句被真正使用。步骤 3 描述了如何在 GUI 工具中查看各条语句。然而,您应该清楚保留不必要索引的陷阱。不必要的索引会对插入/更新/删除动作的性能造成不良影响,并消耗存储和事务日志空间。

步骤 4. 后续步骤

在接受了建议之后,接下来的步骤自然就是作出更改。然而,此时可能还要做些其他事情:

  • 保存建议,以便给其他人看。
  • 用不同的输入重新运行 Design Advisor,看看能否获得更好的建议。

在这个案例中,我们对存储的大小设了一个限制。让我们再次运行 Design Advisor,并去掉这个限制,看是否可以获得更好的结果。首先,我们将单击图 7 中所示的 Generate 按钮来保存这次运行的结果,以便使用新的 Generate Report 特性。

图 7. 生成一个报告
图 7. 生成一个报告
图 7. 生成一个报告

然后,我们可以使用 Back 按钮回到可以从中去除上述限制的面板。重新运行 Design Advisor 之后,我们发现建议是一样的。

现在我们决定将建议付诸实施。在实施建议之前,DBA 通常要做的事情是:

  • 用符合惯例的名称替代生成的索引名称。
  • 查看将要存储索引的表空间。
  • 添加关于索引的注释,指示创建的原因,例如,用于帮助提高事务 XYZ 的性能。

DB2 V8.2 有一些补充的增强,使得这些建议的实施更为容易。包括:

  • 在线索引创建
  • Alter Table 支持

命令行 (db2advis)

本节使用命令行来演示相同的案例研究。我们将讨论高亮显示的命令及输出。请参阅“参考资料”一节,以获得更全面信息的链接。

下面显示了在这个案例研究中使用的命令。我们将运行基本的命令,并接受大多数默认设置。

db2advis –d dtw –i D:\dtw_workload –q tpcc –m I –o D:\report

亮点包括:

  • -m I: 这表明 Design Advisor 应该考虑新的索引。该参数可以不要,因为默认设置就是只考虑索引,这也是我们在这个案例研究中惟一感兴趣的。
  • -o 输出文件。保存脚本,以便在一个文件中创建所建议的对象。

当命令执行时,它描述正在进行的工作,然后输出如下所示的结果。

Optimization finished.
5 indexes in current solution
[1425961] timerons (without recommendations)
[1225904] timerons (with current solution)
[14.03%] improvement

亮点包括:

  • 建议集由 4 个索引组成(可以是当前已有的索引,也可以是新索引)。
  • 该解决方案导致了 14% 的性能提高。

接着,Design Advisor 以 DDL 格式显示建议,它选择性地将这些建议注释掉。这些建议以如下顺序出现:

  • 新的聚类索引(在这个案例中没有)
  • 索引建议:要创建的索引,要保留的索引,然后是未使用的索引。

下面显示了这些建议的摘录。

-- LIST OF RECOMMENDED INDEXES
-- ===========================
--  index[1], 0.087MB 
CREATE UNIQUE INDEX "PAULMCI"."IDX50316201" ON "TPCC  "."DISTRICT" 
("D_W_ID" ASC, "D_ID" ASC)
INCLUDE ("D_YTD", "D_ZIP", …) ALLOW REVERSE SCANS; 
COMMIT WORK; 
RUNSTATS ON TABLE "TPCC"."DISTRICT" FOR INDEX "PAULMCI"."IDX50316201" ; 
COMMIT WORK; 
-- index[2], 0.024MB
CREATE UNIQUE INDEX "PAULMCI"."IDX50316206" ON "TPCC  "."DISTRICT" …. 
…
-- RECOMMENDED EXISTING INDEXES 
-- ============================ 
-- RUNSTATS ON TABLE "TPCC"."DISTRICT" FOR INDEX "TPCC"."DIST_IDX1"; 
-- COMMIT WORK;  
-- RUNSTATS ON TABLE "TPCC"."NEW_ORDER" FOR INDEX "TPCC"."NU_ORD_IDX1"; 
… 
-- UNUSED EXISTING INDEXES 
-- =========================== 
-- DROP INDEX "TPCC"."CUST_IDX3"; 
-- ===========================

最后,Design Advisor 完成并显示以下信息。

1744 solutions were evaluated by the advisor.
DB2 Workload Performance Advisor tool is finished.

结束语

在 DB2 V8.2 中,Design Advisor 作为 Index Advisor 的替代物被引入,以反映它所具有的更广的范围。除了关于索引的建议以外,Design Advisor 现在还可以提供关于 MQT、MDC 和 DPF 分区键的建议。然而,即使是那些只对索引感兴趣的 DBA,Design Advisor 也好于先前版本的 Index Advisor。请继续关注第 2 部分,到时候我们将研究如何在数据仓库环境中使用 Design Advisor。


相关主题


评论

添加或订阅评论,请先登录注册

static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=10
Zone=Information Management
ArticleID=94224
ArticleTitle=使用 DB2 V8.2 Design Advisor: 第 1 部分,在专家的帮助下设计 OLTP 数据库
publish-date=08252005