管理 XPS 数据库服务器上的混合查询工作负载

　　管理 Informix Extended Parallel 上的混合查询工作负载

　　本文描述了 Informix® Extended Parallel Server™ 版本 8.3 中的新特性，这些特性让您定制每个用户会话的环境以获得更佳性能并更有效地使用数据库服务器资源。您可以通过使用下面列出的这些新特性来帮助用户和控制资源。

　　要指定初始会话环境，创建在每个会话打开数据库时运行的 sysdbopen()过程，并合并 SQL 和 SPL 语句以定制由每个会话提交的查询行为。除了使用适当的 SPL 语句之外，您还可以在用户每次打开数据库时为他们指定下列新的 SET ENVIRONMENT 特性。

　　COMPUTE_QUOTA 参数有助于那些运行多个小型或中型特殊查询的用户。

　　IMPLICIT_PDQ 参数根据每个查询和每个查询操作符的需求分配内存。

　　TMPSPACE_LIMIT 参数防止任何查询独占 TEMP 空间。

　　以后要调整会话环境，使用新的 onmode命令。

　　要更详细地管理和分析查询，使用 Informix® I-Spy™ 版本 2.0。

　　将这些特性与您数据库设计和数据库服务器管理方面的标准好实践结合起来，将使管理日益变化的工作负载变得更容易。

　　数据仓库和数据集市的变化用法

　　以前，设计和调整数据仓库数据库是用来运行大型、易于理解的查询，但数据仓库用法正在迅速变化。现在，用户以多种方法、使用许多不同的工具（包括用于统计报表的 SAS、用于预言式数据挖掘的 Brio 和 Cognos 和用于多种类型的数据分析的第三方 OLAP 工具）来访问大型数据库。

　　当用户运行允许他们编写特殊查询的应用程序时，您就不能再假设自己能够调整在您的数据仓库数据库上运行的查询了。特殊查询的范围从简单的指定会计年度统计分析到旨在预测客户行为或检测欺诈行为的复杂算法。并且这些查询可能同时和您为数据库设计的大型查询一起运行。随着商业智能定义的扩展，将需要数据仓库的所有这些用法。

　　在这种环境中 DBA 和 DBSA 的主要问题是确保运行各种查询的用户能有效地使用数据库服务器资源。当为大型 DSS 查询调整环境变量和配置参数的单个集合后，数据库服务器不能有效地运行多种其它类型的查询。这个问题引起了其它问题：

　　某些查询可能得到了比其需要更多并行处理能力，并因此妨碍了其它查询的执行。

　　其它查询可能得到比其需要更多（或者可能更糟 — 更少）内存。

　　如果查询得到的内存比它们需要的少，它们就溢出至 TEMP 空间，这会降低性能。而且，溢出查询可能使用过多的 TEMP 空间，以至其它溢出查询无法得到 TEMP 空间来完成其执行并因此异常终止。

　　Informix Extended Parallel Server 版本 8.31 引入了几个新特性，它们允许您调整每个会话的环境以便适当地管理其需求。新特性允许您管理特定会话或所有会话的内存使用、CPU VP 使用和 TEMP 空间使用。

　　此外，您可以使用 I-Spy（一种在将查询发送到数据库服务器之前截取它们的守护程序）更精确地分析数据库服务器使用以及管理用户和查询。I-Spy 收集关于查询的信息，并通过强制执行您指定的规则控制查询的行为。

　　管理特定会话的资源使用

　　管理混合查询工作负载的 XPS 方法是：提供让 DBA 设置特定会话或所有会话的初始环境的特性，然后更改会话环境以按需调整性能。

　　大多数新的工作负载管理特性属于下列两类：

　　指定特定会话资源使用的 SET ENVIRONMENT 选项。

　　您从 SQL 命令行或在新的 sysdbopen()SPL 过程中设置这些选项，当用户打开数据库时设置会话环境。在会话启动时，SPL 过程还能执行任何其它正确的 SPL 或 SQL 命令。后面的“用 SPL 例程设置初始会话环境”一节描述了这些过程。

　　一组影响特定会话的 onmode 命令。

　　当您监控数据库服务器活动或当用户报告问题时，您可以执行这些 onmode命令来调整特定会话的资源使用，或终止失控的查询而不影响已提交查询的会话。

　　控制会话环境

　　要在 XPS 版本 8.31 中控制会话使用数据库服务器资源的方式，使用新的 SQL 命令 SET ENVIRONMENT 及其参数。这些参数不是环境变量。不能在会话概要文件中或从操作系统命令行设置它们，但必须将其作为 SQL 语句 SET ENVIRONMENT 的参数执行。客户机本身能够执行 SET ENVIRONMENT 语句来控制如何执行特定查询。或者，当客户机打开数据库时运行的 SPL 例程能够执行 SET ENVIRONMENT 语句以设置初始环境，如后面的“用 SPL 例程设置初始会话环境”一节所述。

　　新的环境选项让您控制每个客户机会话使用下列数据库服务器资源的方式：

　　CPU VP

　　内存

　　TEMP 空间

　　用 SPL 例程设置初始会话环境

　　某些会话使用一些工具连接到数据库，而您对这些工具只有很小的控制权或根本没有控制权。要设置这些会话的启动环境，现在，您可以在当会话连接到数据库时执行的 SPL 例程中设置初始会话环境。SPL 例程有两个变体：名为 user.sysdbopen() 的一个或多个私有例程（其中 user是特定用户的登录名），以及单个 public.sysdbopen() 例程，所有没有私有 SPL 例程的用户都可执行这个例程。您为用户访问的每个数据库安装公用例程和私有例程。这些例程不接受任何参数，或不返回任何值。

　　在 SPL 启动例程中，您可以执行 SET ENVIRONMENT 语句及其参数（以及其它语句）来设置查询的隔离级别并建立其它查询执行参数。

　　例如，您可以执行下列 public.sysdbopen()例程以执行几个新的 SET ENVIRONMENT 选项并将所有查询的隔离级别设置为 Dirty Read：

create procedure public.sysdbopen()
　　set isolation to dirty read ;
　　set environment implicit_pdq on;
　　set environment compute_quota on;
　　set environment tempspace_limit on;
　　end procedure

　　对于公司中有特殊需求或高优先级的特定用户，您可以创建下列私有 sysdbopen()例程以将隔离级别设置成“dirty read”并启用 IMPLICIT_PDQ，但不限制并行或分配 TEMP 空间的使用：

create procedure ceo.sysdbopen()
　　set isolation level to dirty read ;
　　set environment implicit_pdq on;
　　end procedure

　　当私有 sysdbopen()例程为用户运行时， public.sysdbopen()例程将不为该用户运行。

　　因为 sysdbopen()例程仅当用户连接到数据库时才运行，所以它们的开销很低。另一方面，某些应用程序和老练的用户可能通过重新设置选项更改环境。如果您的系统能容忍更高的开销，则可以使用 I-Spy（如后面的“用 I-Spy 进行严格的会话和查询管理”一节所述）来对用户执行的 SQL 语句进行更多的控制。

　　注：您也可以创建当会话从数据库断开时执行的公用和私有 sysdbclose()例程。

　　控制并行处理带宽

　　缺省情况下，在 XPS 数据库服务器中，由资源授权管理器（Resource Grant Manager (RGM)）管理的查询在所有 CPU VP 上的协同服务器（coserver）中和跨协同服务器并行地执行。这个并行处理提供了使 XPS 成为大型 DSS 查询、数据挖掘以及类似使用的最佳平台的大部分能力。

　　但是，如果许多用户在您的数据库服务器上提交查询，那么，您应该通过将每个查询限制为：每个协同服务器上，每个查询操作符只有单个 CPU VP，来改进总体性能。如果每个查询只将一个 CPU VP 用于每个查询操作符，则可以同时运行多个查询。对 SET ENVIRONMENT 语句启用 COMPUTE_QUOTA 参数以限制每个查询的计算能力。

　　注：并非所有查询都由 RGM 管理。如果查询访问连接协同服务器上单个分段表中的数据，并且优化器确定该查询的每个线程仅需要缺省的 128KB 内存，则不由 RGM 来管理它。此类查询称为“串行查询”。串行查询仅在其访问数据的协同服务器上快速执行。

　　尽管当查询访问跨协同服务器的表分段或需要更多内存时由 RGM 管理，但并非所有此类查询都需要在每个协同服务器上的所有 CPU VP 上运行。要将一个查询计划中的线程限制为每个协同服务器上只有单个 CPU VP，在会话提交任何查询之前对它执行 SET ENVIRONMENT COMPUTE_QUOTA ON。

　　让优化器确定内存分配

　　当您的数据库服务器运行认真调整过的 SQL 查询时，您可以计算出适当的 PDQPRIORITY 设置来为每个查询请求适当数量的内存。但是，对于特殊查询和其它不可预知、未经调整和未经分析的查询，很难实现对 PDQPRIORITY 的合理设置。在 XPS 版本 8.31 中，您可以指示优化器忽略任何显式 PDQPRIORITY 设置，并用数据分布统计信息来确定每个查询需要多少内存。

　　当您对会话执行 SET ENVIRONMENT IMPLICIT_PDQ ON 语句时，优化器以两种方式更有效地管理内存：

　　查询所得到的内存仅和优化器估计这些查询所需要的内存量一样多。

　　显式 PDQPRIORITY 设置通常是对查询所需内存数量的猜测。猜测通常偏高，因此查询请求的内存比所需的多。此外，当您依靠 PDQPRIORITY 设置时，调度程序仅当 PDQPRIORITY 请求的最大内存数量可用时才尝试运行查询。如果为某些查询提供了超过其实际需求的内存，则其它查询可用的内存就更少了。

　　使用 IMPLICIT_PDQ，优化器忽略了 PDQPRIORITY 的显式值并使用由 UPDATE STATISTICS 生成的数据分布和表大小信息来估计查询需要多少内存。然后，当有这个数量的内存可供使用时，调度程序运行查询。

　　分配的内存供所有查询操作符使用，所以每个操作符线程得到的内存仅和优化器估计该线程所需的一样多。

　　在 Informix Extended Parallel Server 先前的版本中，在查询操作符之间平均划分分配给查询的内存，这导致某些操作符得到比其需求更多的内存。在 XPS 版本 8.31 中，优化器使用 UPDATE STATISTICS 信息来估计每个操作符需要多少内存，并在操作符中按比例分配内存。

　　图 1显示了带有三个散列连接的查询在 XPS 版本 8.31 中和以前版本中内存分配的差异。

　　图 1：内存分配比较

　　当对会话启用了 IMPLICIT_PDQ 时，会在所有查询操作符而不仅是在散列连接之间按比例分配内存。

　　注：当对会话启用 IMPLICIT_PDQ 时，其查询的 EXPLAIN 输出显示关于理想的、请求的和实际提供的内存的附加信息。

　　帮助优化器确定内存分配

　　当 IMPLICIT_PDQ 设置为 ON 时，优化器可以分配尽可能多的内存，如同 PDQPRIORITY 设置为 100。但是，这可能限制能够同时运行的查询数。要调整内存分配，以下列方法限制优化器提供的内存数量：

　　设置 IMPL_BOUND_PDQ 以强制优化器在由 PDQPRIORITY 的最近显式值设置的界限之内分配内存。

　　例如，您可以对会话设置下列选项：

　　— 请求 DS_TOTAL_MEMORY 的 75%

SET PDQPRIORITY 75;

　　— 通知优化器估计实际内存需求

SET ENVIRONMENT IMPLICIT_PDQ ON;

　　— 将内存分配限制为 PDQPRIORITY 指定的数量

SET ENVIRONMENT BOUND_IMPL_PDQ_BOUND ON;

　　使用这些设置选项，优化器不会给任何查询分配超过 DS_TOTAL_MEMORY 值 75% 的内存。它仍然在查询操作符之间按比例分配内存。

　　将 IMPLICIT_PDQ 设置为一个百分数，强制优化器按比例调整其内存分配。

　　例如，您可以对会话设置下列选项：

SET ENVIRONMENT IMPLICIT_PDQ 80;

　　使用这样的 IMPLICIT_PDQ 选项设置，优化器估计查询需要多少内存并分配这些内存量的 80%。查询几乎肯定会溢出到 TEMP 空间。如果系统需要您不惜溢出到 TEMP 空间来保留内存，则您可以使用这个选项。

　　如果您将 IMPLICIT_PDQ 设置成一个百分数，以使优化器估计查询需要多少内存，然后仅分配该内存量指定的百分比，则某些查询所得到的内存可能比需求少得多。尽管每个查询得到的内存都不会少于每线程 128KB 的下限，但您可能希望保证它们得到略多于这个最小值的内存。要同时保证最大和最小的内存分配，将 PDQPRIORITY 设置为下限和上限值，然后将 IMPL_PDQ_BOUND 设置为 ON。

　　作为更复杂的示例，您可以指定一个百分数作为 IMPLICT_PDQ 环境选项的参数，并指定内存分配的上下限：

SET PDQPRIORITY low 10 high 75;
SET ENVIRONMENT IMPLICIT_PDQ 80;
SET ENVIRONMENT IMPL_PDQ_BOUND ON;

　　使用这些设置，优化器以下列步骤分配内存：

　　使用行分布和表大小统计信息来估计查询需要多少内存。

　　请求该内存数量的 80%。

　　至少分配 DS_TOTAL_MEMORY 的 10%，即使所估计内存量的 80% 小于该值。

　　仅分配 DS_TOTAL_MEMORY 的 75%，尽管小于 80%。

　　尽管 PDQPRIORITY、IMPLICIT_PDQ 和 IMPL_PDQ_BOUND 之间的交互看起来可能很复杂，但几分钟的思考就会向您展示管理用户会话的各种组合的能力，从而可以在所有查询之间适当分配内存。

　　控制查询溢出的 TEMP 空间使用

　　在 Informix Extended Parallel Server 先前的版本中，单个急需内存的查询可以溢出到磁盘并使用所有作为 DBSPACETEMP 配置参数的参数指定的 TEMP 空间。尽管在查询所分配的内存不可用的情况下，调度程序不会运行查询，但调度程序不知道有多少 TEMP 空间可用。结果是，如果一个查询使用了所有 TEMP 空间，则其他一些查询可能会异常终止，因为它们不能获得所需要的 TEMP 空间。

　　在 XPS 版本 8.31 中，您可以分配 TEMP 空间的使用，因此没有查询能够使用超过查询操作符溢出指定数量的磁盘空间。使用新的 ONCONFIG 参数 DS_TOTAL_TMPSPACE 以及新的 SET ENVIRONMENT TMPSPACE_LIMIT 语句来指定每个协同服务器上的单个查询可以将多少 TEMP 空间用于溢出。除非 DS_TOTAL_TMPSPACE 是全局设置的，并且对提交该查询的会话设置了 TMPSPACE_LIMIT，否则数据库服务器不会分配 TEMP 空间。

　　分配 TEMP 空间不会阻止查询在 TEMP 空间以外运行。然而，如果您将 DS_TOTAL_TMPSPACE 设置为低于配置的 TEMP 空间的数量，则没有查询能够独占 TEMP 空间。考虑下列示例：

　　将 DS_TOTAL_TMPSPACE 设置为小于您实际配置的 TEMP 空间。

　　例如，您可能在每个协同服务器上配置了 10GB TEMP 空间，但可能在对 DS_TOTAL_TMPSPACE 设置时仅指定为 2GB。

　　当已经对会话指定了 DS_TOTAL_TMPSPACE 并将 SET ENVIRONMENT TMPSPACE_LIMIT 设置为 ON 时，由该会话提交的每个查询就可以在每个协同服务器上使用指定数量的 TEMP 空间了。

　　SET ENVIRONMENT TMPSPACE_LIMIT ON 允许由该会话执行的每个查询使用 DS_TOTAL_TMPSPACE 的全部数量。因此，如果将 DS_TOTAL_TMPSPACE 设置为 2GB，那么每个查询可以使用 2GB TEMP 空间，并且最多五个查询可以共享已配置的 TEMP 空间的全部 10 GB。

　　SET ENVIRONMENT TMPSPACE_LIMIT "50" 允许由该会话执行的每个查询仅使用 DS_TOTAL_TMPSPACE 指定数量的 50%。在本例中，最多可以有 10 个查询共享已配置的 TEMP 空间的全部 10 GB。

　　如果查询的需求超过其 TEMP 空间限制，而又没有足够的 TEMP 物理空间可用，则该查询一开始就会异常终止。用户将看到“Tempspace allocation exceeded”出错消息。要调整可用的 TEMP 空间的数量，如本文稍后“更改 TEMP 空间限制”一节所述使用 onmode -q ds_temp_space 命令。

　　注：TEMP 空间限制不影响创建临时表或 TEMP 空间的其它用途。该限制只影响查询操作符溢出。

　　动态地更改会话环境

　　每个数据库服务器实例的 ONCONFIG 文件中设置的全局配置参数提供了数据库服务器行为的通用规则。在 XPS 的以前版本中，使用 onmode 实用程序来动态更改这些参数中几个，而不用关闭和重启数据库服务器。例如，使用带有新的百分数的 onmode -D来更改由 MAX_PDQPRIORITY 设置的 PDQPRIORITY 比例因子。

　　Informix Extended Parallel Server 版本 8.31 引入了附加的 onmode 命令，它以下列方式对指定会话进行操作：

　　重新设置可用的 TEMP 空间数量

　　更改显式 PDQPRIORITY 设置

　　更改调度级别

　　杀死特定会话提交的正在执行和在 Wait 队列中的所有查询

　　这些 onmode命令完成 SET ENVIRONMENT 选项的功能并允许您当会话在数据库服务器上运行时调整选项。与其它 onmode命令类似，要执行这些新命令，您必须是用户 root或 informix。

　　更改会话的 PDQPRIORITY

　　PDQPRIORITY 是一个非常灵活的参数。您可以以下列四种不同方式设置它以指定内存分配：

　　作为 ONCONFIG 文件中的 PDQPRIORITY 参数

　　通过由该会话执行的所有查询的 PDQPRIORITY 环境变量

　　通过对由该会话执行的所有后续查询执行 SQL 语句 SET PDQPRIORITY

　　在 XPS 版本 8.31 中，通过指定会话的 onmode命令

　　如果会话已经同时设置了 IMPLICIT_PDQ 和 BOUND_IMPL_PDQ，您就可以更改 PDQPRIORITY 值，但活动的显式 PDQPRIORITY 值并未正确地指定内存分配的界限。如果会话没有任何新的活动 SET ENVIRONMENT 选项，您还可以更改 PDQPRIORITY 值，并完全依靠 PDQPRIORITY 来确定查询的内存分配。

　　要将会话 1.33 的 PDQPRIORITY 设置更改为最小值 20 和最大值 80，在会话的连接协同服务器上输入下列命令：

onmode -q pdqpriority 1.33 low 20 high 80;

　　要将会话的 PDQPRIORITY 设置成单个最大值，输入下列命令：

onmode -q pdqpriority 1.33 80;

　　不会通知用户进行了更改。

　　更改会话的调度级别

　　要指定一个或一组查询的优先级，用 SQL 语句 SET SCHEDULE LEVEL 将调度级别设置成 0 到 100 之间的数。当 RGM 确定查询运行的顺序时，调度级别提供关于哪些查询更重要的信息。

　　ONCONFIG 参数 DS_ADM_POLICY 确定 RGM 给了调度级别多大的权重。当 DS_ADM_POLICY 设置成 FAIR 时（这是缺省值），RGM 考虑查询已经等待了多久以及调度级别。但是，如果将 DS_ADM_POLICY 设置为 STRICT，则 RGM 仅考虑调度级别。在将 DS_ADM_POLICY 设置成 STRICT 的繁忙的数据库服务器上，调度级别的设置至关重要，因为它可以完全阻止查询的运行。

　　例如，在将 DS_ADM_POLICY 设置成 STRICT 的数据库服务器上，由会话 1.12 提交的查询将永远等待运行，如 图 2所示。