Max Active 对于 DB2 for i 查询性能的影响

本文将介绍较高的内存池 Max Active 设置会对内存的公平分配产生怎样的影响,而该影响会导致 DB2 for i 查询优化器在某些情况下选择效率低下的访问计划。

Anil K. Madan, 高级管理顾问, IBM

Anil Madan 的照片Anil Madan 是 IBM Lab Services and Training 的一名高级管理顾问。在过去的 18 年中,他一直就职于明尼苏达州罗切斯特市的 IBM 分公司,担任 IBM i 系统和 DB2 for i 的性能顾问。在担任目前职务之前,Anil 有着 10 年的应用程序开发人员工作经验。



2012 年 1 月 16 日

概述

众所周知,IBM i 上受限制的内存池会给 DB2 for i 查询和 SQL 请求的性能造成负面影响。然而,我相信许多人并未意识到内存池的 Max Active 设置也会对查询优化器以及查询的性能产生显著的影响。不久之前,我遇到了一个问题,查询池的 Max Active 值无意中被设置过高,从而导致部分查询所用时间远远超过正常时间。举例来说,一条 SQL 语句现在要运行数个小时,而其正常运行时间仅为 10 到 15 分钟。在深入探讨此查询的细节之前,让我们来具体了解一下 Max Active 的设置,以及它对 DB2 for i 查询优化器会产生怎样的影响。

Max Active 是什么?

Max Active(也称为活动级别)是一个内存调优参数,它用来控制内存池中可同时使用处理器的线程的最大数量。每个共享和专用内存池都有一个相关的 Max Active 值。如果这个值过设置得小,那么线程可能会转入不合格状态。如果这个值设置得过大,就会出现过多的页面错误。可以使用 WRKSYSSTS(处理系统状态)命令或利用 System i Navigator – Work Management – Memory Pool 来查看和更改 Max Active。如果 IBM i 分区上的性能调整器处于启动状态(系统值 QPFRADJ 为 2 或 3),那么 Max Setting 值也可以由 IBM i 操作系统自动调节。

Max Active 对 DB2 for i 查询优化器有怎样的影响?

每一条查询的执行都以访问计划内存储的指令为基础。访问计划是由 DB2 for i 查询优化器在查询运行之前创建的。此计划基于多种不同因素的综合,包括数据库的设计、索引、表的行数、列的统计信息、查询属性等等。在创建访问计划时,优化器要考虑的一个重要环境因素就是查询可以利用的内存份额(内存公平份额)。这种内存公平份额的计算可避免优化器将过多的内存分配给特定的某个查询,并允许优化器合理考虑或者选择使用更高或更低的方法。内存公平份额会有不同的计算值, 这取决于查询是由经典查询引擎 (CQE) 处理的还是由 SQL 查询引擎 (SQE) 处理的。用于计算公平份额值的算法如下

i 7.1 增强

i 7.1 提供了一个全新的 PTF - MF54009,它改变了查询的 SQE 内存公平份额的算法。经过这样的改变,在计算内存公平份额时,优化器大多数情况下都会使用池中的平均活动值,而非 10% 的最小值。经过修改的算法如下:

如果查询程度 = *MAX,则继续使用整个池。否则:SQE 内存公平份额 = 内存池大小 / "Derived_Activity"

如果内存池中的平均活动值至少为 5,则 “Derived_Activity” 的值将与内存池中的平均活动值相同。如果平均活动值小于 5 但大于 Mac Active 值的 10%,则此值应为 5,否则应为 Max Active 值 的 10%。

  1. CQE 内存公平份额 = 内存池大小 / Max Active
    • 如果 Max Active 仅为 1,则使用 1/2 的内存池大小
    • 最小份额为 100 KB
  2. SQE 内存公平份额 = 内存池大小/ min(Max Active, max(Average Active Used, 5))
    Average Active Used 是选以下两个值中较大的一个:
    • 为内存池指定的 Max Active 值的 10%
    • 存储管理报告的内存池中平均活动值*

    * 内存池中的平均活动值定义如下:

    • 分页选项设置为 *CALC 时,15 分钟的用户数量移动平均值
    • 分页选项设置为 *FIXED 时,最后 2 秒钟内存池中的唯一用户数量
  3. 如果作业的查询程度设置为 *MAX,则 CQE 和 SQE 的内存公平份额值均等于整个内存池的大小

如您所见,Max Active 值对查询可用的内存份额有着显著的影响。而可用的内存份额又对查询优化器在为查询创建访问计划时选择的哪个方法有着重大的影响。此外,在可用内存份额较高时(因为 Max Acitve 值较小的结果),优化器可以自由选择任何访问方法(无论其使用内存多少)来建立最有效的访问计划。如果内存公平份额设置较低,则导致优化器应避免使用内存用量较大的访问方法。


运行运行时间较长的查询性能调查

现在,我们来讨论一下特定的 SQL 性能场景。客户抱怨说,这个查询过去可在大约 10 分钟的时间内完成,现在却需要数个小时。希望这个查询是由 SQE 处理的,因为我使用 SQL Plan Cache 工具来寻找运行时间较长的 SQL 语句。为此,我使用 System i Navigator 连接到客户的 IBM i。随后单击导航树中的 Databases 图标,再单击本地数据库的名称,以访问 DB2 性能工具。然后,我右键单击 SQL Plan Cache Snapshot 对象,选择 New->Snapshot 任务。完成上述操作之后,会出现图 1 所示的对话框窗口。我接受了默认参数,提供了 My_SnapShot 作为快照名称。创建一个计划缓存快照使我能够在此后随时执行分析,即便在不同的 IBM i 系统上也可以实现此操作。请注意,SQL Plan Cache 仅存储由 SQE 处理的 SQL 语句的访问计划。如果此查询是由 CQE 运行的,则需要使用 SQL Performance Monitor 工具收集数据库监视跟踪记录。

图 1:创建 SQL 计划缓存
图 1:创建 SQL 计划缓存

识别与分析

为了访问我刚刚创建的快照,我单击了 SQL Plan Cache Snapshots 对象。随后右键单击已经保存的快照 My_SnapShot,并选择 Show Statements 任务。在图 2 所示的对话框窗口中,我为 “minimum run time of the longest execution of the statement” 指定了 5 秒的值作为一个选择过滤,随后单击 Apply 按钮。上述操作会为我提供一个 SQL 语句列表,其中至少包含一个执行时间大于或等于 5 秒的 SQL 语句,如图 2 所示,这些语句从运行时间最长的开始,按降序进行排列。我们关注的查询显示在此列表的顶端,其运行时间为 15,686 秒(超过 4 个小时)。为了执行此语句的具体分析,我右键单击此语句,然后启用了 Visual Explain 工具。Visual Explain 工具提供了此查询所用访问计划的图形表示。我检查了这条显示在 Visual Explain 窗口的下窗格中的SQL 语句。这是一条复杂的语句,总共连接了 8 个表,并使用了跨多个表的选择谓词。

图 2:执行时间至少为 5 秒的语句中执行时间最长的几条语句
图 2:执行时间至少为 5 秒的语句中执行时间最长的几条语句

在 View 选项卡中,我选择了 Estimated Processing Time for the Arrow Labels,随后单击 “Highlight Expensive Icons" - "Estimated processing time"。这项操作突出显示了一个表(称为 T1)的 探针(Table Probe) 访问 方法。此表的预计处理时间占整个查询预计运行时间(这显示在最终结果下方)的 98% 以上。我在 Visual Explain 的右侧窗格中单击此表,我查看了有关其计划的信息。这个表包含超过 200 万个行行数,据预计,该表的探针 (table probe) 有将近 2.46 亿个 IO 请求。

图 3 显示了部分 Visual Explain 输出,重点突出了基本表 T1 的访问。请注意,DB2 优化器选择了一个 Hash Table 探针访问方法。优化器在确定连接的辅助表时通常会考虑采用 Hash Table 探针访问方法。这需要使用与基本表的同等选择或联接标准相符的键列创建一个临时散列表。Hash Table 探针允许优化器在不考虑任何联接标准的前提下选择最有效的方法,以便从基本表中选择行数。构建散列表的目标是使大多数散列表驻留在主内存中,并且保证使用最小与散列探针相关的 I/O。如果可以使用来自基本表的所有必要列填充散列表,那么无需借助其他表探针便可完成此表的处理。

对于此表,需要关注的是4 个联接和选择列。为实现此表的最优联接性能,在此步骤中优化器应使用所需的全部四个联接和选择列来创建一个临时散列表。。通过单击Temporary Hash Table 图标,我可以查看选择用于创建此散列表的列。我注意到,优化器仅仅使用一个列(联接列之一)创建并填充了临时散列表。随后,我通过单击 Table Probe 图标来查看从基本表 T1 访问的列。此方法的详细信息表明,所需的 4 个列是通过利用从临时散列表的散列探针中检索到的数据来探测此基本表 T1而检索到的。

图 3:展示了表 T1 所用访问方法的部分可视化说明
图 3:展示了表 T1 所用访问方法的部分可视化说明

创建单列散列表和后续表探针带来了极为高昂的成本,因为在表探测过程中必须使用大量随机 IO。那么,优化器为什么没有创建包含全部四个列的临时散列表?为了寻求答案,让我们来看看这条 SQL 语句的环境信息:

SQL 语句的环境信息
Memory Pool Size:                  2,454,335,488
Share of Memory Available (bytes): 81,811,184
Average Active Used:               30
Memory Active in the Pool:         300
Average Active in the Pool:        9

单击 Final Result 图标即可获得此信息。查询是在 2,454 MB 的内存池中运行的,该内存池的 Max Active 值为 300 (Memory Active in the Pool)。Average Active Used 计算结果为 30(即 300 的 10%),因此,优化器确定此查询可用的内存公平份额为 81 MB(2454/ 30)。优化器已经十分肯定 81 MB 不足以处理包含全部四个列的散列表。因此,优化器决定创建一个较小的散列表,仅包含表 T1 中的联接列,以便适应可用的内存份额。


为什么降低 Max Active 有助于解决这种问题?

根据上述分析,我认为如果此查询拥有更大的可用内存份额,优化器就会为表 T1 选择更好、更优的访问计划。有以下两个选择可以增加可用内存份额:

  1. 增加内存池的整体大小
  2. 降低内存池的 Max Activity 级别

我们倾向于选择使用第二种做法,将内存池的 Max Activity 设置降低到 100(之前为 300)。然后再次运行 SQL 查询,借助 Visual Explain 观察新的访问计划。根据相同 SQL 语句的环境信息表明,内存公平份额的值约为 245 MB。SQL 语句的新环境信息如下所示:

更改之后 SQL 语句的环境信息
Memory Pool Size:                  2,454,335,488
Share of Memory Available (bytes): 245,433,548
Average Active Used:               10
Memory Active in the Pool:         100
Average Active in the Pool:        9

这一次,优化器确定内存足以处理包含来自表 T1 的全部四个列的散列表。经确定,包含全部四个列的临时散列表的大小约为 235 MB(单击 Hash Probe 图标,即可在 Visual Explain 的 Hash Table Size 窗格中看到此信息),恰好适合可用的内存份额。优化器创建了经过修订的访问计划,用该计划来访问表 T1 中的数据(参见图 4)。对于新计划,通过首先执行 T1 表扫描(而非之前运行中使用的索引扫描),随后执行散列探测创建了包含全部四个列的临时散列表。由于所需的全部列都可在散列表中找到,因此不再需要对基本表进行表探测。消除表探测操作可显著减少 IO 操作的数量,查询将在大约 720 秒的时间内完成,从而查询性能提升了20多倍!

图 4:展示在降低 Max Active 值之后表 T1 所用访问方法的部分可视化说明
图 4:展示在降低 Max Active 值之后表 T1 所用访问方法的部分可视化说明

总结和建议

内存池的 Max Active 值设置的高低对于查询优化器有着显著的影响。内存池的 Max Active 值设置的较高,则可能导致查询运行缓慢;正因如此,为实现最优、一致的查询性能,应保证运行查询的内存池的此值不要过高。另一方面,对于生成多个线程的应用程序(例如基于 Java 的应用程序),或者对于将预期运行大量并发作业的内存池,较低的 Max Active 值可能会造成负面影响。这是因为较低的活动级别可能会导致部分此类线程或作业等待执行。考虑到这种相互矛盾的需求,建议最好不要将线程繁多的应用程序的内存池与数据库服务器作业的内存池(例如 QZDASOINIT 和 QSQSRVR)共享。在这些情况下,应该考虑让应用程序运行于单独的内存池中。


参考资料

学习

讨论

  • IBM developerWorks 中国 IBM i 专区:为 IBM i 的开发人员准备的技术信息和资料。这里提供产品下载、how-to 信息、支持资源以及免费技术库,包含 2000 多份技术文章、教程、最佳实践、IBM Redbook 和在线产品手册。
  • 加入 developerWorks 中文社区,developerWorks 社区是一个面向全球 IT 专业人员,可以提供博客、书签、wiki、群组、联系、共享和协作等社区功能的专业社交网络社区。
  • 加入 IBM i 中国开发团队 Blog,参与在线交流。

条评论

developerWorks: 登录

标有星(*)号的字段是必填字段。


需要一个 IBM ID?
忘记 IBM ID?


忘记密码?
更改您的密码

单击提交则表示您同意developerWorks 的条款和条件。 查看条款和条件

 


在您首次登录 developerWorks 时,会为您创建一份个人概要。您的个人概要中的信息(您的姓名、国家/地区,以及公司名称)是公开显示的,而且会随着您发布的任何内容一起显示,除非您选择隐藏您的公司名称。您可以随时更新您的 IBM 帐户。

所有提交的信息确保安全。

选择您的昵称



当您初次登录到 developerWorks 时,将会为您创建一份概要信息,您需要指定一个昵称。您的昵称将和您在 developerWorks 发布的内容显示在一起。

昵称长度在 3 至 31 个字符之间。 您的昵称在 developerWorks 社区中必须是唯一的,并且出于隐私保护的原因,不能是您的电子邮件地址。

标有星(*)号的字段是必填字段。

(昵称长度在 3 至 31 个字符之间)

单击提交则表示您同意developerWorks 的条款和条件。 查看条款和条件.

 


所有提交的信息确保安全。


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=10
Zone=Information Management, IBM i
ArticleID=787668
ArticleTitle=Max Active 对于 DB2 for i 查询性能的影响
publish-date=01162012