使用 DB2 影子表提高混合 OLTAP 工作负载的性能

对 OLTAP 系统的不断增长的需求

业务组织越来越需要将不同的事务和分析处理系统汇集起来，获得实时运营数据的增强报告。在线事务处理 (OLTP) 与在线分析处理 (OLAP) 系统之间不同的性能和存储要求，使得拥有一个能在两方面都有优秀表现的系统变得很难。我们需要 在线事务分析处理 (OLTAP) 系统，它们要能够以最小的代价，在实时事务数据上高效地执行复杂分析。

下载 IBM DB2 with BLU Acceleration 的试用版 （包含影子表需要的所有 InfoSphere CDC 软件组件）

DB2 with BLU Acceleration 改进了复杂分析处理。内存型、CPU 和 I/O 优化的列结构处理的引入，显著增强了分析工作负载。DB2 引入了 影子表 ，它们将这些技术汇集在一起，对数据执行混合 OLTAP 处理。无需将事务数据卸载到某个单独的 OLAP 系统来执行进一步数据处理，就能处理比 OLAP 工作负载更高比例的 OLTP 工作负载的系统，现在，我们能够使用影子表来利用 DB2 BLU 的列处理威力和它们的行结构数据。

回页首

影子表使 OLTAP 变得更简单

影子表特性利用 BLU Acceleration 技术消除了传统 OLTAP 工作负载处理中涉及的开销，使处理变得更快、更简单和更容易。影子表消除了使用多个索引来支持主导性 OLTP 系统中的 OLAP 或混合工作负载的需求。

影子表是行结构来源表的最新的列结构副本。影子表可以使用它们关联的列结构表来创建现有的行结构（来源）表的 “影子”，使 DB2 优化器能够为 OLAP 和 OLTP 查询选择最佳的执行环境。副本通过复制来维护，不会对应用程序对数据的查询产生任何影响。应用程序会像平常一样继续查询来源表，无需更改它们的查询结构。被确定能从列结构中获益的查询，会被透明地重新路由，以便对影子表运行这些查询，确保传入的查询始终使用最佳的执行环境来执行，无论它们的性质是什么（OLTP 还是 OLAP）。图 1 显示了 DB2 优化器基于查询的性质和执行计划，将它们路由到行结构表和对应的影子表的总体视图。

图 1. 影子表概述

对典型的行结构表发出的查询，会自动重新路由到影子表来利用 BLU Acceleration。DB2 优化器可根据来源表与影子表之间的延迟（在用户定义的限制内）来制定查询路由决策，得到一个针对混合 OTLAP 工作负载而进行优化的灵活环境。

回页首

影子表的架构

影子表在 DB2 内是以 MQT 形式来实现的，通过使用 InfoSphere® Data Replication Change Data Capture (CDC) 的持续复制来维护。复制解决方案包含 InfoSphere CDC with DB2，以确保影子表相对于其关联的来源表而言是最新的。影子表特性使用了 InfoSphere CDC 10.2.1。

InfoSphere CDC 负责捕获对来源表的更改，并将这些更改应用于同一个数据库系统内关联的列结构表中。对来源表的更改通过读取数据库事务日志流来捕获。InfoSphere CDC 还负责将延迟信息返回给 DB2。在根据影子表数据有多新来制定路由决策时，DB2 会使用 InfoSphere CDC 共享的信息。

图 2. DB2 影子表复制的架构

图 2 更详细地显示了完整的复制解决方案。它包含一个 CDC 实例和 DB2 实例。InfoSphere CDC Access Server 维护了通信和 InfoSphere CDC Replication Engine for DB2 Linux®, UNIX®, and Windows® (LUW) 所使用的元数据。InfoSphere CDC Replication Engine 读取数据库事务日志，以识别对来源表所做的需要由应用代理应用到目标影子表的数据操作 (DML) 更改。

回页首

设置影子表

这一节将介绍为影子表设置完整的复制解决方案的总体任务，以及使用 InfoSphere CDC 来维护和管理影子表的配置流程。

部署路线图

要成功地部署复制解决方案并开始使用影子表，可执行以下必要的总体步骤：

1. 以根用户身份安装 DB2 10.5 Fix Pack 4，然后：
   1. 创建一个 DB2 实例。
   2. 针对影子表用途来创建和配置该数据库。
2. 安装 InfoSphere CDC并配置影子表的复制：
   1. 安装 InfoSphere CDC Access Server。
   2. 安装 InfoSphere CDC for DB2 for LUW 10.2.1。
   3. 安装 InfoSphere CDC Management Console（可选）。CDC 实例可通过命令行接口 CHCCLP 或使用 Windows 平台上的图形化 CDC 管理控制台来管理。
   4. 创建 InfoSphere CDC Replication Engine 实例 — 每个数据库一个 CDC 实例。
   5. 配置一个数据存储。
   6. 创建 SYSTOOLS.REPL_MQT_LATENCY 表。
3. 创建影子表。
4. 配置并开始对影子表进行复制：
   1. 创建一个 InfoSphere CDC 订阅和表映射。
   2. 启动 InfoSphere CDC 订阅，并将数据复制到一个影子表。
5. 启用将查询路由到影子表的功能：
   1. 在一个 DB2 命令行处理器 (CLP) 会话中启用查询路由。
   2. 在应用程序级别启用查询路由。

产品安装

要使用影子表特性，必须以根用户身份安装 DB2，还必须针对影子表用途来配置该数据库。影子表需要的两个重要的用户帐户：要执行复制的 DB2 实例的用户（例如 db2inst1 ）和具有 DB2 上的 DATAACCESS 和 DBADM 或 SYSADM 授权的 CDC 安装用户（例如 cdc-user ）。CDC 用户应具有 db2-instance-dir/db2-inst/name/NODE0000/sqldbdir 目录的 drwxrwxr-x 权限。

要支持影子表，必须使用相应的支持平台来安装以下软件版本：

• IBM DB2 for LUW Cancun Release 10.5.0.4。DB2 LUW 知识中心中列出了受支持的平台。
• IBM InfoSphere Data Replication 10.2.1 — 带 Interim Fix 12 的 Change Data Capture for DB2 LUW 和更高版本。支持运行 DB2 10.5 FP4 for LUW 的平台。
• IBM InfoSphere CDC Access Server 10.2.1 Interim Fix 5 和更高版本
• IBM InfoSphere Data Replication 10.2.1 — CDC Management Console Interix Fix 5 和更高版本（可选）。

复制解决方案的组件可安装在同一个系统或不同系统上。本教程的建议布局包括 InfoSphere CDC Replication 和作为 DB2 实例安装在同一个服务器上的 InfoSphere CDC Access Server，以及安装一个专用的 Windows 服务器或工作站上的 InfoSphere CDC Management Console。

为影子表配置 DB2

使用影子表时，数据库未针对 BLU Acceleration 而进行配置，因为该系统要用于一种混合 OLTAP 工作负载。BLU 工作负载设置 DB2_WORKLOAD to ANALYTICS 尽管适合 OLAP，但对 OLTP 工作负载而言，不适最佳的设置。对于影子表，可以使用数据库管理器和数据库配置参数设置来利用 BLU Acceleration。

本文将重点介绍以下重要的配置参数。（ 针对影子表的 DB2 服务器配置 包含完整的配置设置列表。）

$ db2 get db cfg for oltpdb | grep -i sortheap Sort list heap (4KB) (SORTHEAP) = 10000 $ db2set DB2_EXTENDED_OPTIMIZATION="OPT_SORTHEAP_EXCEPT_COL 10000"

原因：包含影子表的环境具有比 OLTP 环境中的数据库更高的排序对内存需求。要增加排序堆内存而不影响现有的 OLTP 查询，可为没有引用影子表的查询指定覆盖值。 sortheap 数据库配置参数的现有值可用作覆盖值。

$ db2 update db cfg for oltpdb using sheapthres_shr 12000000 sortheap 600000

原因：影子表需要比 OLTP 环境更多的排序堆内存。要确保有内存供 CDC 组件使用，可将以下建议的内存量分配给这两个参数：

• 将 sheapthres_shr 设置为数据库内存的 50%。
• 将 sortheap 设置为 sheapthres_shr 的 5-20%。

例如，96 GB 的 50% 是 48 GB 或 1200 万个 4K 页面，48 GB 的 5% 是 2.4 GB 或 600,000 个 4K 页面。

$ db2 update db cfg for oltpdb using logarchmeth1 logretain

原因：InfoSphere CDC 为数据复制使用了事务日志，而且需要读取非活动日志，所以数据库事务日志需要保留。

$ db2 update db cfg for oltpdb using util_heap_sz 1000000 AUTOMATIC

原因： LOAD 命令用于在影子表上执行刷新操作。将 util_heap_sz 数据库配置参数设置为 Automatic 和一个较大的起始值，以解决 InfoSphere CDC 使用 LOAD 命令的需求。一个不错的起始值是 100 万个 4K 页面。要满足更高的内存需求，比如在运行 LOAD 命令的并发工作负载时，需要增加 util_heap_sz 参数。

为影子表配置 CDC 组件

在配置 InfoSphere CDC 组件之前，您可能希望了解一些在影子表上下文中非常重要的 CDC 概念，如表 1 所示。

表 1. InfoSphere CDC 术语

下一节将讨论 InfoSphere CDC 的配置，这包括设置 InfoSphere CDC Access Server，以及创建和设置影子表所需的 InfoSphere CDC 对象。

创建 CDC 实例和配置 CDC Replication

使用 CDC Replication Engine dmconfigurets 命令来启动交互式的实例配置工具。使用表 2 中的值创建 CDC Replication Engine 的一个新实例。配置工具位于 CDC Replication 安装文件夹 ( cdc-user ) 的 /bin 子文件夹中。

cd [...]/InfoSphereChangeDataCapture/ReplicationEngineforsDB2/bin/ ./dmconfigurets

表 2. CDC 实例参数

设置实例后，使用 dmts64 命令来配置实例的延迟设置并为实例激活复制功能。

cd [...]/InfoSphereChangeDataCapture/ReplicationEngineforDB2/bin/ dmset -I cdcinst1 maintain_replication_mqt_latency_table=true dmset -I cdcinst1 acceptable_latency_in_seconds_for_column_organized_tables=50 dmset -I cdcinst1 mirror_auto_restart_interval_minutes=2 nohup ./dmts64 -I cdcinst1 &

这些参数控制 CDC Replication 解决方案传达给 DB2 的延迟信息。这些参数表明：

• 延迟信息发送到数据库表 SYSTOOLS.REPL_MQT_LATENCY 。
• 将被应用于来自来源表的影子表的更改的可接受延迟被设置为 50 秒。

mirror_auto_restart_interval_minutes 参数设置了在复制功能关闭或重新启动时持久性订阅重新启动前等待的延迟。

延迟信息要通过 DB2 优化器引用，需要手动构建存储此信息的数据库表。下面这个示例显示了在表空间 TBSP4K 中创建该表的存储过程：

CALL SYSPROC.SYSINSTALLOBJECTS('REPL_MQT', 'C', 'TBSP4K', CAST (NULL AS VARCHAR(128)))

创建 CDC Access Server 用户和数据存储

在创建并配置了 CDC 实例后，需要创建 CDC Access Server 的管理用户，并使用 dmcreateuser 命令将该用户指定为 SYSADMIN。这些命令可以使用 cdc-user 身份从 CDC Access Server 安装的 bin 子文件夹中访问：

cd [...]InfoSphereChangeDataCapture/AccessServer/bin/

以下命令展示了如何创建 CDC 管理用户、数据存储，以及将它与您的数据库关联的连接。

表 3. 设置管理用户和数据存储

有关这些命令的更多细节，请参阅参考资料中的 InfoSphere CDC 文档。

回页首

创建影子表

现在，已经设置和配置好了 InfoSphere CDC 组件，为创建和设置影子表做好了准备。DB2 提供了工具来帮助选择合适的来源表。可以使用 Optim™ Query Workload Tuner 为影子表提供推荐的有效候选值。创建影子表后，一定要确保所选的来源表定义了一个主键或惟一键约束。两个表中的每一行需要一种 1:1 的映射关系。

要创建影子表，可发出包含 MAINTAINED BY REPLICATION 子句的 CREATE TABLE 语句。此子句将该表识别为影子表。来源表可完整地复制，或者可以选择复制与您的查询工作负载相关的部分列。下面这个示例展示了从来源表 TRADE 创建影子表的过程。确保来源表的主键包含在选择列表中。

create table TRADE_SHADOW as (select * from TRADE) data initially deferred refresh deferred enable query optimization maintained by replication organize by column;

在创建之后，新影子表处于 set integrity pending 状态。发出一个命令让影子表退出挂起状态。修改该表来添加一个主键约束；影子表需要一个与来源表上执行的主键/惟一键约束相匹配的主键。在影子表上发出以下语句：

set integrity for TRADE_SHADOW all immediate unchecked; alter table TRADE_SHADOW add constraint TRADE_SHADOW_PK primary key (trans_id);

影子表现在已设置。您已成功安装和配置了影子表的复制功能，并创建了一个影子表。下一节将讨论如何设置订阅和如何执行复制。

回页首

设置影子表的复制

在使用影子表特性时，所有表映射都被分组到数据库的单个订阅下。用户可以使用图形化的 CDC Management Console 或命令行驱动的 CHCCLP 实用程序来创建订阅。本文将简要展示 CHCCLP 命令，但建议新用户使用 CDC Management Console，因为它容易使用。

下面的命令是一个在数据存储 ds1 内创建持久性订阅 sub1 的实例。要创建订阅，用户需要连接到与包含影子表的数据库关联的访问服务器和数据存储。

$cd [...]InfoSphereChangeDataCapture/AccessServer/bin/ $./chcclp set verbose; connect server hostname prodhost port 10101 username cdc-admin password p@ssw0rd; connect datastore name ds1; add subscription name sub1 persistency true;

成功创建订阅后，会为每对来源和影子表创建一个表映射，并启动镜像。连接到数据库后，在 CHCCLP 中使用下面这条命令。

点击查看代码清单

select subscription name sub1; add table mapping sourceSchema BASE sourceTable TRADE targetSchema BASE targetTable TRADE_SHADOW targetIndexMode index targetIndexName BASE.TRADE_SHADOW_PK type standard method mirror; start mirroring method continuous; disconnect datastore name ds1; disconnect server; exit;

针对新创建的影子表的复制的设置和配置现在已完成。下一步是确认查询已根据您的设置被路由到正确的表类型。要确认已启用查询路由，可以使用 DB2 监视表功能或 EXPLAIN 实用程序。DB2 知识中心包含有关监视影子表和将查询路由到影子表的更多信息。

回页首

启用将查询路由到影子表的功能

要使 DB2 Optimizer 能够将查询重新路由到影子表，必须将某些参数设置为所需的值。除了复制延迟在定义的限制范围内，还需要启用 intra_parallel （设置为 ‘ANY’）来允许在运行时访问影子表。需要设置以下特殊寄存器来允许使用基于延迟的路由：

• CURRENT REFRESH AGE 特殊路由器需要设置为除 0 或 ANY 以外的持续时间。当来源-影子延迟在这个特殊寄存器指定的间隔内时，就会发生基于延迟的查询路由。
• CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION 特殊寄存器被设置为仅包含 REPLICATION 。
• CURRENT QUERY OPTIMIZATION 特殊寄存器应设置为 2 或不小于 5 的值。

要了解这些寄存器的更多信息，可以访问DB2 知识中心。

满足基于延迟的路由的需求后，可执行以下 SQL 语句来对影子表启用查询路由支持。

$ db2 CONNECT TO oltpdb $ db2 "CALL ADMIN_SET_INTRA_PARALLEL ('YES')" $ db2 SET CURRENT DEGREE 'ANY' $ db2 SET CURRENT REFRESH AGE 500 $ db2 SET CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION REPLICATION $ db2 SET CURRENT QUERY OPTIMIZATION 2

示例

下面这个示例演示了 DB2 优化器选择将查询路由到影子表而不是来源表的条件。我们将使用之前定义的 TRADE 和 TRADE_SHADOW 表。我们还将利用一个视图示例，该视图将会检查来源-影子对之间的当前延迟，检查该延迟是否在指定的限制内。有关延迟视图的描述，请查阅DB2 知识中心中的 “启用将查询路由到影子表的功能”。

查询来源表

SET CURRENT REFRESH AGE 500; SELECT COUNT(*) AS N_TRADES, SUM(TRADE_PRICE) AS TRADE_PRICE, YEAR(TRANS_DATE) AS YEAR FROM TRADE GROUP BY YEAR(TRANS_DATE) ORDER BY YEAR(TRANS_DATE)

来自来源-影子对的延迟视图的示例输出

SELECT * FROM REPL_LATENCY;  REFRESH_AGE LATENCY   CUR_TS                     REFRESH_TS                   COMMIT_POINT DELAY_OFFSET  ----------- -------- -------------------------- -------------------------    -----------  ------------  500.000000 3.572204 2014-05-07-14.03.20.572204 2014-05-07-14.03.17.000000    1399485797   0 1 record(s) selected.

延迟视图的结果显示，查询应重新路由到影子表。来源-影子对之间的延迟 (3.57s) 落入了用户定义的 5 分钟限制内。使用 EXPLAIN 实用程序执行进一步确认时，确认了查询重新路由已成功，而且与在设置影子表之前引用的传统的基于行的访问计划相比，成本少 77%。表 4 给出了 EXPLAIN 输出的一部分。

表 4. 查询基于行的表和影子表的访问计划

Access Plan: ----------- Total Cost: 175824 Query Degree: 8 Rows RETURN ( 1) Cost I/O | 12 . . . LMTQ Extended Diagnostic Information: -------------------------------- Diagnostic Identifier: 1 Diagnostic Details: EXP0148W The following MQT or statistical view was considered in query matching:"DTW".TRADE_SHADOW". Diagnostic Identifier: 2 Diagnostic Details: EXP0149W The following MQT was used (from those considered) in query matching:"DTW".TRADE_SHADOW".

数据库操作上存在一些与影子表特性相关的管理性更改，还有一些可帮助您在使用影子表时更好地管理和调优系统的操作性最佳实践。您还可以使用 CDC Management 工具在 CDC Replication 上监视您的订阅。要了解管理性更改、最佳实践和使用 CDC Management 工具的更多信息，请参阅 IBM Redbooks® 出版物 “ 在您的分析环境中搭建和部署 IBM DB2 with BLU Acceleration ” 中的第 3 章。

回页首

结束语

由 BLU Acceleration 技术支持的影子表是 OLTP 工作负载分析的真正推动因素。它们让混合工作负载的处理变得更快、更简单和更容易。通过将传统的行组织格式和列组织格式的数据都放在同一个数据库中，组织能够获得针对 OLTP 和 OLAP 而进行了优化的查询执行环境的组合威力。开始使用影子表来体验您的混合 OLTAP 工作负载的改善性能。