IBM决策支持系统(DSS)应用程序处理

IBM 决策支持系统 决策支持系统 DSS 应用程序处理 应用程序处理 Jack Parker Arten Technology Group 2002 年 4 月 随着相对较新的决策支持系统 DSS 在 UNIX RDBMS 领域中的出现 数据库专业人士面临着数十年 来大型机编程员一直在解决的问题 我们编写 OLTP 应用程序已经有很长时间了 以致于我们在进入 DSS 领域时头脑里还保留着 OLTP 的那种思维方式和方法 这对于硬件和数据库 不公平 因为它 们有能力把事情做得好得多 在 OLTP 领域中 我们可能会关心单个用户输入客户订单 我们想确保产品表 订单表以及可能的库存 和客户表都被一致更新 如果产品表更新失败 那么只更新订单表以反映产品价格对我们没有任何好处 我们能够知道客户订购了 5 00 美元的商品 但我们不知道是什么货 为了保证正确处理这些更新 我们 使用事务来确保一致地执行 或不执行 所有操作 因此我们要特别在意事务日志 以便我们可以重做或 撤销工作 我们的数据库软件有专用于支持这种处理方法的完整开销层 我们的思考模式也局限于 事务 性方法 在 DSS 领域中 我们需要对大量数据执行操作 我们不需要个别用户针对单独行或行集合发出更新操作 而是执行数百万次的插入或更新 或者是甚至几百万次的删除 如果我们在 DSS 领域中使用事务性方法 那么我们就会作茧自缚 本文中介绍的技术的最初测试是在带有 16 个处理器 RAM 为 16GB 以及磁盘为 1 2TB 的 Sun 6500 上完成的 最后测试是在 RAM 为 500MB 和磁盘为 40GB 的 Sony 133Mhz PentiumII 上完成的 有 趣的是 在排除规模因素后 这两台机器的运行情况类似 读者将在本文中找到有关对记时采用外推法的 相应参考资料 可以用算术方法得出这些值 这些值反映了这两台机器的性能 示例示例 1 DSS 插入插入 在最近的一个案例中 我处理了一个用新数据装入 10 亿行表的过程 插入 由于不希望有重复数据 开 发人员对该表附上了一个唯一性索引 并以高级方式 在这种方式中 装入的行遵守所有验证标准 使用 IBM Informix R Extended Parallel Server TM XPS 并行装入器来拒绝重复 装入过程大约以每小时 装入 1 百万行的速度运行 当过程在每个月要插入 1 千多万行时 逻辑日志将溢出并且引擎开始回滚所 有插入操作 因为回滚花费在撤销操作上的时间远比原始操作多 所以这意味着回滚要花费另外的 20 至 30 小时 换言之 如果该过程失败 将有 30 到 40 个小时专门花费在这上面 为什么该方法无效为什么该方法无效 为了理解为什么该方法很糟 让我们仔细研究一下引擎做些什么 1 装入器从输入文件 或多个文件 读取记录 2 记录被转换成内部格式 现在我们称之为行 3 执行索引查找操作 以查看该行的索引是否已经存在 因为这是一个大型表而且索引的深度为 6 级 所以这可能意味着每个索引查找操作要读 6 次 4 如果未找到索引 则该行被附加到一个有空间的页面上 因此该页面被读取 或者仍可以驻留在 上一次插入的缓冲区中 5 这也许意味着对该表分配更多的数据块 6 该页面 以前的 映象保留在物理日志中 在任何更改之前 7 插入被记录在事务日志中 8 通过检查点或者通过前台写操作 定期将这些数据页面存储到磁盘上 在数据极少的开发环境中 该过程决不会使日志溢出 索引查找操作将快很多而且要插入的行数会比较少 整个过程可能运行 20 到 30 分钟 这是完全可以接受的 直到我们碰到大型表时 才会开始有麻烦 在事务性方法中 我们想将最新读写的数据保留在缓冲区中 这样不仅可以把写操作组合在一起 从而使 写操作更有效 而且被请求的数据通常在该缓冲区中 至此 我们将大量内存分配给这些缓冲区 我们担 心最近最少使用 LRU 队列来管理它们 我们正在探索使缓冲区读命中率大于 90 使高速缓存写比 率高于 80 当我们处理一个很小的行集合时 这很有效 然而 这产生了一层开销 当处理大量行时 开销会使速度慢下来 这层开销有时被称为 缓冲区墙 避免避免 缓冲区墙缓冲区墙 轻型扫描和轻型添加 轻型扫描和轻型添加 插入的选通因素是 我们正设法通过该缓冲区墙插入数据 我们正在为打算插入的每一行进行索引查找操作 如果我们能够以某种方式使这些操作加速或者避免这些操作 则可以更快地移动数据 幸运的是 有轻型扫描和轻型添加特性 在轻型扫描中 从表读取的数据存储在其自己的专用缓冲池中 根据这个条件 将不需要为进一步的操作保留该数据 有一个内在含意 将读取整个表或者至少表的一整 段 使用轻型扫描 引擎不会使缓冲池中填满大型表内容 避免了缓冲区管理的开销 轻型扫描的速度明 显快于传统扫描 轻型添加以相似的方式起作用 但它是个逆向操作 它不会试图将数据缓冲到缓冲池中 而是将数据直接 写到新的页面 在一次成功的装入操作结束时 新页面被添加到现有表 使用轻型添加 预期可以达到的 最低负载率为每小时每个 CPU 为 2 GB 为了利用这些特性达到最佳性能 必须在多个 dbspace 之间仔细地对您的表进行分段 在这第一个示例 中 10 亿行的表被分段在 32 个 dbspace 上 也可以预创建临时表 并根据所需的大小 在多个 dbspace 之间对表进行分段 最好将任何给定的表保持在 500 MB dbspace 以下 然而这并非总是可 行的 在这个特殊情况下 一个表在每个 dbspace 上就消耗掉 2 GB 因此 扫描时间是 15 分钟 而 不是 4 分钟 不同的实例有不同的业务需求 因而有不同的准则 在这个情况下 我们用与处理器数匹 配的 dbspace 设置 dbslice 在 IBM Informix Extended Parallel Server 下 dbspace 被收集成称为 dbslice 的实体 这提供了更大的管理单位 dbslice 它还允许 DBA 在 dbslice1 中以散列 列 进行分段的形式将分段指定为更独立级别 所以 16 个处理器的机器有一个包含 32 个 dbspace 的 dbslice 一个包含 16 个 dbspace 的 dbslice 一个包含 8 个 dbspace 的 dbslice 和一个包含 4 个 dbspace 的 dbslice 在包含 16 个 dbspace 的 dbslice 之上散布着临时空间 目标是达到最大比例为 每处理器 3 个 dbspace 如果我们有磁盘 就可以把较大的 dbslice 增加到 48 个 dbspace 这样就 可以减少 1 3 的扫描时间 通过使用散列连接避免索引查找通过使用散列连接避免索引查找 快速读写数据的能力表示我们可以重新设计我们的应用程序来利用这个速度并获得显著的性能改进 如果 您阅读另一篇相关的文章 Tuning DSS Queries 您将注意到它广泛地应用了散列连接 散列连接可以 连接两个表中的数据 比建立索引的连接快大约 1 到 3 个数量级 它是一个功能强大的工具 但是应该 仅当期望连接整个表或一个表的大部分内容时 才使用该工具 所以让我们用散列连接方法再次处理上面的问题 这次避免索引和缓冲区墙 我们将使用散列连接来确定新数据和目标数据之间是否有任何匹配 重复的数据 而不是为要插入每一 行探测目标表索引页 然后 我们将按照这一知识来操作 1 要做的第一件事情是除去目标表上的索引 如果索引存在 则优化器会选择使用它 而不是使用我们 首选的散列连接方法 2 然后 让我们将新数据装入优化器可以看到的表中 我们创建一个原始表 它复制最后的目标表 还 创建了一个与原始表 语法相同 的外部表 将 1 千万行数据装入该表大约在 60 秒内 从逻辑上我们 可以说并没有装入新数据 但 XPS 将外部表看作真正的表 然而 优化器可能认为该文件中有 20 亿 MAXINT 行 最后才扫描它 从这个更大型的表 该表太大而无法放入内存 构建散列表 这将引起 内存溢出情况 并需要一个小时或更多时间来与临时磁盘相互交换数据 3 我们更新新原始表的统计信息 以便优化器可以理解其中有多少行 LOW 足够满足我们的需要了 为 了使本文简短些 这里每次表装入或表插入之后 我们将不重复 UPDATE STATISTICS 但建议在真正 的操作中添加该步骤 4 下一步 将新表与旧表匹配 selectraw table fromdestination table raw table wheredestination table key raw table key into temp t1 with no log 通过预创建这个 t1 表并在多个 dbspace 之间对它进行分段 又可以省去几秒钟时间 在整篇文章中我 都没有这样做 只是为了使之保持简单 扫描整个目标表似乎开销太惊人 但我们将表分段在 32 个 dbspace 由于轻型扫描 所以该散列连接 花了 15 到 17 分钟 任何重复的行都将被插入临时表 t1 现在 我们的第一个问题出现了 我们需要知道任何行是否已插 入该表 t1 并根据插入的行数这一条件信息采取了不同操作 SQL 无法很好地支持我们所需要的操作 然而 我们可以容易地将该 sql 放入脚本 在那里执行检查并相应地进行分支处理 如果没有任何行被插入到临时表中 那么我们的源数据是 纯 的 我们只要 insertinto destination table select from raw table 反之 如果行被插入到 t1 中 那么我们必须找出哪些行是重复的 然后不插入它们 所以 我们 insertinto t1 select from raw table 现在 我们将两个数据集中较小的那一个 要插入的部分 添加到了 t1 表 这创建了重复的行 很容易 找到它们 selectkey fromt1 groupby key havingcount 1 intotemp t2 with no log 现在 在 t2 中 有了不错的唯一性数据 我们可以从那里连接回 raw table 以直接装入到 destination table 中 insertinto destination table selectraw table fromt2 raw table wheret2 key raw table key 我们可以合并这两个步骤 按整个行分组 然后插入到 destination table 中 这可以避免散列连接 在 测试中 这省去了 30 秒 但这将因您的表而异 如果我们想更进一步 用重复的行更新目标表 那么我们还要 selectkey fromt1 groupby key havingcount 2 intotemp t3 with no log selectraw table fromt3 raw table wheret3 key raw table key intotemp t4 with no log 我们也可以合并这两个步骤 按整个行分组 然后插入到 t4 表中 这避免了散列连接 在测试中 这省 去了 1 秒 这将因您的表而异 在那里 我们就可以直接从临时表 t4 更新目标表 表 a 显示了通过使用这种方法实际花在插入行上的时间 表 a 将 1 千万行插入 10 亿行表所需的时间 数据的初始装入 让我们大方点 60 秒 原始和目标表之间的散列连接16 分钟 15 到 17 是平均数 不插入重复的行20 秒 解决重复问题90 秒 用重复的行更新17 分钟 更新连接是随后有更多更新的散列连接 简而言之 与有时运行 10 小时 以及有时因出现故障而运行 30 至 40 小时相比 现在我们的过程成 功运行的时间不到 20 分钟 如果我们还想要进行更新 则仍可以指望总计时间在 40 分钟之内 最妙 的是 即使要装入 3 千万行 时间也不会变化多少 选通因素是 大约 15 分钟的目标表扫描 而不 是传入数据 这种算法几乎可以应用于任何情况 只要您理解表 b 中所示的规则 表 b 考虑 DSS 处理的规则 不要 要 不要根据行来考虑问题 如果想要读记录 读行 进行检查和插入 更新行 您就注定要使用事务性 方法 将行视为一组数据 一次处理所有行 不要使用事务日志记录功能 事务日志记录引入了一层代价很大的开销 它不仅 会使速度慢下来 而且它还对在溢出日志之前可以 做的事情强加了限制 另外 如果过程半途失败 那么我们需要做的最后一件事情是弄清楚过程中的 什么地方出了问题 然后重新启动它 通常 重新 启动一个过程花费不是很大 我们认为在上述情况 中恢复和重新应用更新将花去 10 到 11 个小时 这种情况很少发生 在下面的示例中 我们将使用 一些重写技术 如果发生问题 这些技术可以为我 们提供多次机会来回到前一状态并再试一次 扔掉 所有索引 稍后我们将详细讨论它 不要使用数据库级参照完整性 在上面的情况中 我们使用了散列连接 而不是唯一性索引 来防止 重复数据输入我们的表中 我们可以用相同的方式 处理大多数参照完整性问题 以达到同样的效果 将数据合并到单个表中 连接花费很大 如果您 需要的所有数据都在单个表中 那么它会变得很容 易处理 不要针对表打开游标 这样做的话 您就运用了 引擎的所有能力 并通过少量管道将它传递到过程 中 在引擎中完成所有工作 可能会碰巧遇到游标 是可用来处理情况的唯一工具 那么在使用游标之 前 先理解它的含意 抽取要使用的行集合 使用它们 然后将它们放 回或放入新的表副本中 可以将其它未更改的行集 合添加到该副本 示例示例 2 DSS 更新更新 通过使用 DSS 方法 可以有许多不同的方法来解决更新问题 OLTP 领域中的选项较少 在 OLTP 领 域中 我们将从某处读取输入 在目标表中查找匹配行 对该行进行更改 并将它写回数据库中 该方法 往往很慢 您可以获得每小时几万行的执行速率 针对上面提到的十亿行表 如果每天晚上要将 2 千万 个更新应用于该表 则对该过程使用 OLTP 方法将永远赶不上要更新的数据量 备用方案备用方案 1 更新连接 更新连接 幸运的是 IBM Informix Extended Parallel Server 提供了 更新连接 updateDestination setdestination col1 input col1 fromdestination input wheredestination key input key 备用方案备用方案 2 添加更新 添加更新 Additive update 让我们假设 我们没有 更新连接 但可以采用不同的方法来处理它 与前面不同的是 让我们将 2 千万个更新应用于 1 亿行表 第一步是将 更新行 或输入转变成看上去与目标行完全相同的行 在将 这些数据装入临时表时完成这一步 然后 我们可以做许多事情 这些事情都包括新的表副本的写操作 我将简化语法 只显示感兴趣的部分 尤其因为我不知道我们在这里更新什么 我避免使用别名 使内容 变得更加易读 如果更新是添加型的 更新目标集 col1 col1 foo col1 那么我们可以 createraw table new dest createraw table load table sameas destination insertinto load table select from input files insertinto new dest selectkeys destination col1 load table col1 fromold destination outer load table whereold destination key load table key 注 在创建原始表时 不能使用 sameas 语法 该语法是为外部表而保留的 这似乎与我们的直觉相反 外连接怎么会比更新有效呢 它或许比更新连接慢 请参阅表 c 但我们 用使用读写的策略替换索引策略 正如前面提到的那样 IBM Informix 引擎拥有重写整个表的强大功能 它比传统数据库 包括 IBM Informix 数据库 处理这种规模的索引更新的速度快 备用方案备用方案 3 替换更新 替换更新 如果更新是替换型的 replacement 那么我们的策略会稍做变化 我们为每一行附加一个指示符 用 来指示是否应该用新数据来替换它 1 首先 让我们构建一个临时表 用指示符 0 标记所有旧行 createtable temp table keys indicator smallint insertinto temp table selectkeys 0 indicator fromdestination 2 接下来 将所有新键和指示符 1 插入同一个表中 insertinto temp table selectkeys 1 indicator frominput files 3 现在 我们将最终目标表的所有键放入新的临时表 t2 中 指示符为 0 的那些行都来自目标表 指示 符为 1 的那些行都来自 input files 表 未更改过 indicator 0 的那些行可以直接放入新的目标表副本 中 selectkey max indicator fromtemp table groupby key intotemp t2 with no log insertinto new destination selectdestination fromdestination t2 wherekey key andt2 indicator 0 4 现在 可以直接从 input files 外部 表添加替换数据 insertinto new destination select from input files 5 最后 删除旧表 重命名新表 droptable destination renametable new destination to destination 该方法似乎有点复杂 我们必须对目标表扫描两次 第一次获得所有键 第二次获得所有数据 我们还构 建了可能非常大的临时表 扫描该表同样也需要很大的花费 这里要指出一点 全表扫描和重写之间的速 度存在差别 但仍比索引更新快许多 表 c 显示了使用这些方法 用 2 千万行输入表更新 1 亿行所花费的时间 表 c 更新 1 亿行所花费的时间 OLTP 更新超过 100 小时 推断 更新连接30 分钟 48 秒 添加更新17 分钟 54 秒 新表更新67 分钟 19 秒 请注意 添加更新实际上比更新连接快 这是因为它不必重写页面 它用轻型扫描和轻型添加来完成其所 有工作 实际更新时间只比扫描两个表的时间长一点 该方法有一些变化 其关键是实际上不发生任何更新 我们避开缓冲区墙而执行所有这些操作 结束时 如果该过程的所有步骤都成功 则可以删除原始表 重命名 new destination 表 然而 有一个前提 就是当我们要更新表的很大一部分时 才会显出其优点 如果要应用的更新数很小 那么当您没有 XPS 和更新连接时 一定要坚持使用 OLTP 方法 您会从上面注意到 XPS 更新连接没有预想的那样快 这两个小表的实际扫描和连接应该在 20 分钟内 完成 重写被更新的页面花去了我们大量的时间 您还会注意到 通过象上面那样用新的更新重写表 我们没有丢失数据 如果我们对在更新之前和更新之 后预计的行数进行计数 则可以检查正在讨论的两个表 并在删除旧表之前确定它们是否一切正常 因此 我们不需要记录日志就可以从问题中恢复 新表更新应该运行得更快 时限为 45 分钟 只可惜它在执行较大的散列连接时发生的内存溢出 这是 DSS 方法的严重缺点 我们必须了解这个缺点 内存内存 分配给散列连接的内存来自 DS TOT MEMORY 分配的内存数量与 PDQPRIORITY 设置直接相关 如果 PDQPRIORITY 设置为 80 而且 MAXPDQPRIORITY 100 那么您将获得 80 的可用内存 MAXPDQPRIORITY 也是一个百分比 所以有效的 PDQPRIORITY 设置是 MAXPDQPRIORITY 100 PDQPRIORITY 100 DS TOT MEMORY 在 XPS 8 31 中 PDQPRIORITY 稍有变化 因而 可以按需动态分配内存 然而 这种动态分配仍不 完美 引擎在内存中根据参加连接操作的两个表中的较小一个构建散列表 然后用两个表的较大表探测该 表 如果没有足够的内存来构建散列表 那么该表的一部分被交换到临时磁盘上 虽然查询溢出的可能性 超过 200 内存中 100 磁盘上 100 而交换需求将使速度进一步慢下来 但是使用临时磁盘 通常还是不错的 您可以用下面的公式算出散列表将需要多少内存 散列表项是 32 键大小 较小表的 行大小 因而 如果可以使较小的表变 瘦 对行大小使用较小的值 和 或 连接小的键 那么连接将需要较少的内 存并将更快地运行 了解这一点并密切关注它是调优 DSS 过程的关键 索引索引 当期望使用索引而创建它时 设法做以下两件事情之一 提供对个别行的快速访问 为索引本身中的操作提供所有必需的数据 当使用 DSS 方法时 索引可以欺骗优化器使用嵌套循环连接而不是散列连接 这一点对于大型表尤为明 显 因为查找行意味着根据索引深度遍历多个索引页面 如果您正在处理许多数据 则会用从其中获得一 行或两行的页面填充缓冲区 您可能必须多次读取某些页面 从而使要执行的工作加倍 如果正在使用索 引 则预期的吞吐量为每小时几万行 在 DSS 方法中 不需要索引 当然 在某些情况下 索引是很重要的 如果我们需要从大型表中删除 200 行 则对表构建一个索引 然后发出 200 条个别更新或删除语句 这样做要比使用 DSS 方法快得多 示例示例 3 DSS 删除删除 备用方案备用方案 1 删除连接 删除连接 IBM Informix Extended Parallel Server 为删除操作提供了与更新连接类似的连接 称为删除连接 delete join 在 V8 30 中 不能为表取别名 在 V8 31 清除了这一限制 语法是 deletefrom destination usingdestination input wheredestination key input key 这是对表应用多个删除操作非常有效的方法 而且 这是一个散列连接 在该散列连接的后台 更新的 应用了删除操作的 页面被重写回磁盘 然而 在运行这样一个命令之前 应该考虑您正在做什么 删 除在页面中 开了一个洞 可以通过 OLTP 环境中的插入语句或更新语句 某些情况下 来重新填充 这个 洞 然而 我们已经努力避免这种语句 对 DSS 表应用多个删除操作会使它到处是 洞 这 些 洞 将不能再使用 如果这种删除太多 我们将要重新构建该表 备用方案备用方案 2 重写方法 重写方法 让我们通过使用重写方法而不是使用行删除方法来研究一下删除操作 基本上 我们要做的就是编写一个新表 它不包括我们不想保留的行 在逻辑上可以把它看作 insertinto new table select from old table wherekey not in select key from delete table 用这个方法处理问题极为糟糕 因为 NOT IN 条件将会使两个表之间产生嵌套循环连接 更佳的方法是 insertinto new table select from old table w