在DB2中提高INSERT性能(2)

出处:数据库用户　 

 

 

3. 找到存储行的地方
DB2 使用三种算法中的一种来确定将行插入到哪里。（如果使用了多维群集（Multi-dimensional Clustering，MDC），则另当别论，我们在这里不予讨论。）

缺省模式是，DB2 搜索散布在表的各页上的自由空间控制记录（Free Space Control Records，FSCR），以找到有足够自由空间存放新行的页。显然，如果每页上的自由空间都比较少的话，就要浪费很多的搜索时间。为了应付这一点， DB2 提供了 DB2MAXFSCRSEARCH 注册表变量，以便允许将搜索范围限制为少于缺省的 5 页。

当表是通过 ALTER TABLE 以 APPEND 模式放置时，就要使用第二种算法。这样就完全避免了 FSCR 搜索，因为只需简单地将行直接放到表的末尾。

当表有群集索引（clustering index）时，就要用到最后一种算法。在这种情况下，DB2 试图将每一行插入到有相似键值的一页中。如果那一页没有空间了，DB2 就会尝试附近的页，如果附近的页也没有空间，DB2 就进行 FSCR 搜索。

如果只考虑插入时间的优化，那么使用 APPEND 模式对于批量插入是最快的一种方法，但是这种方法的效果远不如我们这里讨论的很多其他方法那么成效显著。第二好的方法应该是采用缺省算法，但是，如果在最佳环境中，更改 DB2MAXFSCRSEARCH 的值影响很小，而在一个 I/O 约束较少的环境中，这种更改所造成的影响就比较可观了。

如果有群集索引，则对 insert 的性能会有很大的负面影响，这一点也不惊奇，因为使用群集索引的目的就是通过在插入时做额外的工作来提高查询（即 select）性能的。如果的确需要群集索引，那么可以通过确保有足够的自由空间来使其对插入的影响降至最小：使用 ALTER TABLE 增加 PCTFREE，然后使用 REORG 预留自由空间。不过，如果允许太多自由空间的存在，则可能导致查询时需要读取额外的页，这反而大大违反了使用群集索引的本意。另一种选择是，在批量插入之前先删除群集索引，而后再重新创建群集索引，也许这是最优的方法（创建群集索引的开销跟创建常规索引的开销差不多，都不是很大，只是在插入时有额外的开销）。

4. 缓冲池、I/O 和页清除
每一条 insert 在执行时，都是先将新行存储在一个页中，并最终将那个页写到磁盘上。一旦像前面讨论的那样指定了页，那么在将行添加到该页之前，该页必须已经在缓冲池中。对于批量插入，大部分页都是最新指派给表的，因此让我们关注一下对新页的处理。

如果表在系统管理存储的（System Managed Storage，SMS）表空间中，当需要新页时，缺省情况下是从文件系统中分别为每一页分配空间。但是，如果对数据库运行了 db2empfa 命令，那么每个 SMS 表空间就会为新页一次性分配一个区段。我们建议运行 db2empfa 命令，并使用 32 页的区段。

对于数据库管理的存储（Database Managed Storage，DMS）表空间，空间是在创建表空间时就预先分配的，但是页的区段则是在插入处理过程中指派给表的。与 SMS 相比，DMS 对空间的预分配可以提高大约 20% 的性能 -- 使用 DMS 时，更改区段大小并没有明显的效果。

如果表上有索引，则对于每个插入的行，都要添加一个条目到每条索引。这要求在缓冲池中存在适当的索引页。晚些时候我们将讨论索引的维护，但是现在只需记住，插入时对缓冲池和 I/O 的考虑也类似地适用于索引页，对于数据页也是一样。

随着插入的进行，越来越多的页中将填入被插入的行，但是，DB2 不要求在 insert 或 Commit 后将任何新插入的或更新后的数据或索引写入到磁盘。（这是由于 DB2 的 writeahead 日志记录算法。但是有一个例外，这将在关于日志记录的小节中论述到。）然而，这些页需要在某一时刻写到磁盘上，这个时刻可能会在数据库关闭时才会轮到。

一般来说，对于批量插入，您会希望积极地进行 异步页清除（asynchronous page cleaning），这样在缓冲池中就总有可用于新页的空余位置。页清除率，或者说总缺页率，可能导致计时上的很大不同，使得性能比较容易产生误解。例如，如果使用 100,000 页的缓冲池，并且不存在页清除，则批量插入在结束前不会有任何新的或更改过的（“脏的”）页写到磁盘上，但是随后的操作（例如选择，甚至乎关闭数据库）都将被大大推迟，因为这时有至多 100,000 个在插入时产生的脏页要写到磁盘上。另一方面，如果在同一情况下进行了积极的页清除，则批量插入过程可能要花更长的时间，但是此后缓冲池中的脏页要少一些，从而使得随后的任务执行起来性能更佳。至于那些结果中到底哪个要更好些，我们并不是总能分得清，但是通常来说，将所有脏页都存储在缓冲池中是不可能的，所以为了取得最佳性能，采取有效的页清除是有必要的。

为了尽可能好地进行页清除：

• 将 CHNGPGS_THRESH 数据库配置参数的值从缺省的 60 减少到 5 这么低。这个参数决定缓冲池中脏页的阈值百分比，当脏页达到这个百分比时，就会启动页清除。
• 尝试启用注册表变量 DB2_USE_ALTERNATE_PAGE_CLEANING（在 DB2 V8 FixPak 4 中最新提供）。通过将这个变量设置成 ON，可以为页清除提供一种比缺省方法（基于 CHNGPGS_THRESH 和 LSN 间隙触发器）更积极的方法。我没有评测过其效果。请参阅 FixPak 4 Release Notes 以了解这方面的信息。
• 确保 NUM_IOCLEANERS 数据库配置参数的值至少等于数据库中物理存储设备的数量。

至于 I/O 本身，当需要建立索引时，可以通过使用尽可能大的缓冲池来将 I/O 活动减至最少。如果不存在索引，则使用较大的缓冲池帮助不大，而只是推迟了 I/O。也就是说，它允许所有新页暂时安放在缓冲池中，但是最终仍需要将这些页写到磁盘上。

当发生将页写到磁盘的 I/O 时，通过一些常规的 I/O 调优步骤可以加快这一过程，例如：

• 将表空间分布在多个容器（这些容器映射到不同磁盘）。
• 尽可能使用最快的硬件和存储管理配置，这包括磁盘和通道速度、写缓存以及并行写等因素。
• 避免 RAID5（除非是与像 Shark 这样有效的存储设备一起使用）。

5. 锁
缺省情况下，每一个插入的行之上都有一个 X 锁，这个锁是在该行创建时就开始有的，一直到 insert 被提交。有两个跟 insert 和锁相关的性能问题：

• 为获得和释放锁而产生的 CPU 开销。
• 可能由于锁冲突而导致的并发问题。

对于经过良好优化的批量插入，由获得每一行之上的一个 X 锁以及后来释放该锁引起的 CPU 开销是比较可观的。对于每个新行之上的锁，惟一可以替代的是表锁（DB2 中没有页锁）。当使用表锁时，耗时减少了 3%。有 3 种情况可以导致表锁的使用，在讨论表锁的缺点之前，我们先用一点时间看看这 3 种情况：

• 运行 ALTER TABLE LOCKSIZE TABLE。这将导致 DB2 为随后使用该表的所有 SQL 语句使用一个表锁，直到 locksize 参数改回到 ROW。
• 运行 LOCK TABLE IN EXCLUSIVE MODE。这将导致表上立即上了一个 X 锁。注意，在下一次提交（或回滚）的时候，这个表将被释放，因此，如果您要运行一个测试，测试中每 N 行提交一次，那么就需要在每次提交之后重复执行 LOCK TABLE。
• 使用缺省锁，但是让 LOCKLIST 和 MAXLOCKS 数据库配置参数的值比较小。当获得少量的行锁时，行锁就会自动地逐渐升级为表锁。

当然，所有这些的缺点就在于并发的影响：如果表上有一个 X 锁，那么其他应用程序除非使用了隔离级别 UR（未提交的读），否则都不能访问该表。如果知道独占访问不会导致问题，那么就应该尽量使用表锁。但是，即使您坚持使用行锁，也应记住，在批量插入期间，表中可能存在数千个有 X 锁的新行，所以就可能与其他使用该表的应用程序产生冲突。通过一些方法可以将这些冲突减至最少：

• 确保锁的升级不会无故发生。您可能需要加大 LOCKLIST 和/或 MAXLOCKS 的值，以允许插入应用程序有足够的锁。
• 对于其他的应用程序，使用隔离级别 UR。
• 对于 V8 FixPak 4，或许也可以通过 DB2_EVALUNCOMMITTED 注册表变量来减少锁冲突：如果将该变量设置为 YES，那么在很多情况下，只能获得那些符合某个谓词的行上的锁，而并不是获得被检查的所有行上的锁。
• 发出一个 COMMIT 命令以释放锁，因此如果更频繁地提交的话就足以减轻锁冲突的负担。