IBM DB2 基本性能调整3

基本性能调整 3

Roger Sanders 著

笑熬浆糊译

原文出处：《DB2 Magazine》 Quarter 1, 2004 · Vol. 9, Issue 1

英文原文（由于文章翻译未经授权，请在转载时保留原文链接）

为了寻求最佳的性能，将数据库对象作为你关注的目标。在我早先的专栏中，我展示了怎么样去明确那些影响DB2 UDB For Linux、Unix，和Windows性能的注册变量和配置参数。在这个专栏中，我将围绕可能有对数据库性能的重大影响的另外一个因素：数据库设计来进行说明。就像你想象中的那样，DB2 UDB 数据库是由一些对象组成的，这些对象是怎么被创建和他们怎么较好的在一起工作会对一个数据库执行活动的优劣产生重大的影响。能对性能产生重大影响的对象包括缓冲池、表空间和索引；我将带你们去遍历每个需要考虑的因素。

缓冲池如果你有使用DB2 UDB的经验，你应该知道, 缓冲池是内存中的一些单独分配给DB2 数据库管理器的空间，让它去做为数据库添加新数据或者去响应一个查询从磁盘返回一些数据数据页的缓冲区。由于从内存中访问数据势必比从磁盘上读取要快的多,因此通过减少磁盘的I/O操作缓冲池改善了数据库的整体性能。实际上，研究缓冲池是怎样被创建和被使用在调整优化的数据库性能是最重要的一步。

由于缓冲区的重要性地位，每个DBA 都应该了解DB2 UDB怎么使用他们。这里是关于它的快速回顾课程。当新数据增加到数据库时，它首先在缓冲区中增加新页。最终这个页将被具体化到数据库存储空间中。另一方面，为了响应查询当数据从数据库中被检索出来的时候，DB2数据库管理器首先将会将这些包含数据的页存放在缓冲池中，然后才会把它传递给需要它的应用程序或者用户。每次执行新的查询时，将会在每个可以利用的缓冲池中搜寻是否已经有所需要数据的页驻留在内存中。如果那样，就会立刻将它传递给对应的的应用或用户。但是，如果不能在这些缓冲池找到需要的数据的话，DB2 数据库管理器将会讲这些数据从存储器中检索出来并且在传递数据之前将它复制到缓冲区中。一旦页被复制到缓冲池，那么这个页将会一直驻留在缓冲区中直到数据库被关闭或直到它所在的空间需要存储其他页为止。(由于所有数据的加载和修改首先发生在缓冲区——修改过的页最终会去刷新磁盘存储——因此存放在缓冲池中的数据总是最新的)当缓冲池满了之后，DB2 数据库管理器将通过检测页的最后引用时间，页类型，或者页的修改不会影响磁盘内容的改变来选择去除哪些页，这些页可能再次被引用。例如，在30 分钟前被检索以响应查询的页会比包含更新操作而没有落实更新的页更容易被覆盖。

DB2 UDB缺省创建了一个缓冲池（IBMDEFAULTBP）作为数据库创建过程的一部分。在Linux和Unix平台，该缓冲池从内存中被分配了1,000个4KB页；在Windows平台，该缓冲池从内存中被分配了250个4KB页。你可以通过在控制中心找到缓冲池菜单并且选择适当的操作或者执行ALTER BUFFERPOOL语句来增加或减少这个缓冲池的4KB 页的数量。你也可以通过在控制中心同样的方法或者执行CREATE BUFFERPOOL语句来创建另外的缓冲区。

由于缓冲区的重要性，你应该仔细考虑使用多少个缓冲池来适应你的实施需要；每一个究竟需要多大；以及每个缓冲池怎么样能被充分利用。在多数环境里，能被有效使用的缓冲区个数取决于可利用的系统内存的大小。如果可利用的内存可以保留10,000个4k 页(或更少)，那么通常的使用单独的大缓冲池比去使用多个小缓冲池要好。使用多个小缓冲区将导致频繁地访问页来经常与内存进行进出交换，反过来会导致为存储对象比如编目表的I/O竞争或者重复的访问用户表和索引。但是，如果有比较多的内存，应该考虑创兼各自的缓冲区为以下：

· 每一种临时表空间被定义

· 包含着被一些短期的更新事务一直或者重复访问表的表空间

· 包含着表和索引频繁地被更新的表空间

· 包含着表和索引频繁地被查询但很少被更新的表空间

· 包含着表频繁地被使用于随意的查询的表空间

· 包含着很少被应用程序访问的数据的表空间

· 包含着一些你想要使用的数据和索引的表空间。

在许多情况下，大一点的缓冲池要优于较小的缓冲池。但是，考虑到可以使用的内存总额以及缓冲池将怎么被使用。如果你拥有一个要从一个非常大的表中执行许多随机存取操作的应用，那么你应该为这个特殊表创造和使用一个小缓冲池。在这种情况下，没有必要在缓冲池内存中保留数据页一旦他们被用于去执行一次单独的查询。另一方面，如果你拥有一个要从几个看似很小的表中频繁地检索数据的应用，你应该考虑创建一个足够大缓冲池来存放所有在这些表里免得数据。采用这个设计方案，数据能一次装入内存，并且允许它反复的被获取而没有必要额外的磁盘I/O 。

表空间数据库管理的一个重要部份包括通过使用表空间来完成逻辑数据库设计到物理存储的映射。 DB2 UDB 使用二种类型表空间: 系统管理表空间(SMS) 和数据库管理表空间(DMS)。使用SMS 表空间，操作系统的文件管理器负责分配和处理表空间使用存储空间。使用DMS表空间，表空间创建器(或者，在某些情况下是DB2 数据库管理器) 负责分配空间， DB2 数据库管理器负责管理。性能通常是以DMS 表空间来获得快速的相应。但是，SMS 表空间没有大小限制(16,777,215 页) ，在这点上不同于DMS 表空间。 SMS 表空间还更加容易管理，在许多情况下，因为在需要时系统可以自动地获取额外的存储空间。通常DMS 表空间被用于那些频繁地的表，但增长很慢。SMS 表空间一般被用于那些连续增长的表。

早些时候，我提及过数据是在表空间存储容器之间传送（譬如文件系统目录、文件和裸设备）并且缓冲池是被称之为页的一些分离块组成。DB2 UDB 提供四不同页面大小(4KB 、8KB 、16KB ，和32KB) 。缺省情况下在数据库创建过程期间产生的三个表空间(SYSCATSPACE, USERSPACE1和 TEMPSPACE1) 被分配4KB 页面大小。各个表空间必须与缓冲区结合；一个特殊表空间所使用页面大小必须与它关联的缓冲区的页面大小相匹配。另外，如果你创建可一个页面大小是4KB之外的一个表空间，你应该创建一个使用同样页面大小的系统临时表空间。否则，在执行一个需要临时表空间的操作时性能可能会降低（譬如排序和表重组）。

当表空间横跨多个容器时，数据将会用round-robin方式写入每个容器。所谓扩展长度的属性是控制当数据要写入列表中下一个容器之前在一个容器中写多少页数据。这种方法有助于对属于所给定表空间的所有容器之间的数据平衡。

为了减少查询的相应时间，DB2数据库管理器使用了一种被称之为prefetching的技术去检索(或取得)那些数据库管理器确定用户可能需要在实际执行之前的数据。(数据与需要的实际页一起被复制到缓冲区；表空间的prefetch 大小将决定在响应一个查询时有多少额外的数据页被复制到缓冲池里。)

缺省的，所有表空间被创建成extent和prefetch均为32页的大小。你通常可以通过超过这个缺省的extent和prefetch大小来改进整体性能。以下二个算式将确定适当的extent大小:

Min Extent Size = [Number of physical disks used by the tablespace * 4096 (bytes)] / Tablespace Page Size (in bytes)

Max Extent Size = 524288 (bytes) / Tablespace Page Size (in bytes)

（如果你感觉象你以前从未看了这些等式或者是我提供的prefetch的大小，这都是正常的现象。因为我没有在任何IBM 指南或文献中发现它们。相反的，这些是我作为DB2 UDB 性能组的成员在多伦多IBM 实验室工作期间研究出来的结果。）

最合适的extent大小应该是计算出来的最小值与最大值中间某处的值。切记，对于extent的大小，更多不一定意味着更好。在理想状态下，你在从最小值向最大值过渡的过程中会发现一个合适的extent大小，运行性能测试和评估每次设置的结果。注意，extent大小在表空间创建以后无法修改，所以，在每次测试的时候表空间将必须删除，再创建并且重新计算。

一旦你决定了extent的大小，你就可以通过下面的方程来得到prefetch 大小：

Min Prefetch Size = (Extent Size * Number of Containers Used) * Factor

Factor是一个常量，一般为3。

对于prefetch大小，与extent不同的是它在通常状况下比较好。

分离数据仔细查看在一个DB2UDB数据库里面大多数数据是如何存放你就会发现这里有三种不同的对象：常规用户数据存储为数据对象；索引数据联系了在表中定义了的索引信息存储为索引对象；长字段数据被存储成一个长字段对象 (长字段对象只存在于表包含一个或多个长数据列中——LONG VARCHAR, LONG VARGRAPHIC, BLOB, CLOB, DBCLOB) 。如果采用DMS类型的标空间，这些对象分开地被存放并且每个都被存放在它自己的单独表空间里面。在缺省情况下，这三个对象都被存放在同一个表空间里；但是，性能可能通过将数据分别存放在上述三种类型的表空间中存放时常得到改善。

关于db2empfa 在SMS 表空间，文件系统（而不是DB2 数据库管理器）负责在需要时分配额外的存储空间。并且在缺省情况下，SMS 表空间每次扩展一页。但是，在某些工作负荷下(例如，当进行一次大批量的插入操作)它也许倾向于使用在extent中分配的存储空间而不是页。这就是db2empfa工具起了作用。当db2empfa运行的时候，数据库配置参数multipage_alloc被设置位YES(虽然它是一个只读配置参数)，它会导致 DB2 UDB 每次扩展SMS 表空间一个extent而不是一页。db2empfa工具在DB2安装路径下的SQLLIB/Bin目录里面。

索引与性能索引的主要目的就是帮助DB2 数据库管理器快速的从表中查出记录。为表中经常被使用的列创建索引通常有助于数据存取和更新操作性能的改善。此外，索引还考虑到当多重事务处理在同一时间里访问同一个表时候的更好的并发性；这样，行检索更加快速并且锁迅速被获取而且不必担心它长期的挂起。但是这些优势需要成本。索引会占用数据库空间，并且它们可能导致在插入和更新操作执行过程的轻微型能降低。 (所有插入操作和部分更新操作必须发生在表和它对应的索引中。)

那么怎么才能告诉你是否创建索引将改进性能？DB2 UDB 8.1封装了一个工具包来协助你，它可通过控制中心访问。它被称为设计顾问，它会捕获关于数据库的典型工作负荷以及推荐修改的特定信息，譬如根据提供的信息可以去创建新索引或删除未使用的索引。

RUNSTATS工具与性能每当SQL语句被发送到到DB2 数据库管理器中处理时，SQL 优化器会去读取系统编目表来确定被引用的列的特性以及在被引用的表中时候已经定义了索引，同时被语句引用的每个表的大小也包括在内。根据这些得到的信息，优化器可以估算出能满足SQL语句需要的每一种数据存取路径的成本，然后推荐最佳的一个。优化器用于做决策的数据库统计集合数据在系统编目表中是一个关键性的元素。所以，统计的变化可能导致选择存取路径的变化；如果信息丢失或过时，优化器也许选择出来的存取计划将导致SQL语句执行时间比正常的要长。

拥有合法的信息在SQL语句的复杂性增加的时候变得更加关键。当只引用一张表(没有定义索引)时，优化器选择的数量是有限的。但是，当多个表被引用时(每个表都有一个或多个索引) ，那么可供优化器选择的数量会大大加大。但不幸的是，优化器所使用的统计信息是不会自动得保持更新。反而必须阶段性地通过使用运行统计工具(RUNSTATS)重新生成。可以通过控制中心和命令行两种方式执行RUNSTATS工具。语法如下：

RUNSTATS ON TABLE [TableName] < WITH DISTRIBUTION | WITH DISTRIBUTION AND < DETAILED > INDEXES ALL | WITH DISTRIBUTION AND < DETAILED > INDEX [IndexName] > < SHRLEVEL [CHANGE | REFERENCE] >

或者

RUNSTATS ON TABLE [TableName] < [AND | FOR] < DETAILED > INDEXES ALL | [AND | FOR] < DETAILED > INDEX [IndexName] > < SHRLEVEL [CHANGE | REFERENCE] >

TableName 是需要收集(或者更新)统计信息的表的名称。IndexName 是需要收集或者更新统计信息的索引的名称。

注:被显示在角括号里(< > )的参数是可选的；方括号([ ])中的参数是必须的。

例如，更新存储在系统编目表中的关于表DEFAULT.EMPLOYEE的统计信息。你可以执行以下命令：

RUNSTATS ON TABLE DEFAULT.EMPLOYEE WITH DISTRIBUTION AND INDEXES ALL SHRLEVEL CHANGE

运行统计工具不会输出信息。但是，你能通过查询系统编目视图SYSCAT.TABLES的CARD, OVERFLOW, NPAGES, FPAGES列来观看它的结果。(如果这些列的值是21，就意味着统计信息尚未对该行所代表的对象起作用。)

那么应该多久去收集表的统计信息呢?理想状况下，你应该在下面一些事件之后去使用运行统计工具：

· 大量的插入、更新或删除操作

· 导入操作

· 装载操作

· 在现有表中插入一个新的字段

· 创建新索引

· 表重组

每当表的统计信息被收集或更新的时候，所有引用它的程序包都要被重新与它绑定这样优化器就可以利用新统计信息并且在可能的时候，会指出它们所包含的SQL语句的更好的访问计划。如果重新绑定失败或者忘记重新绑定这些程序包可能导致动态sql操作执行起来会比静态sql操作要快（译者注：我对此不太明白，询问别人后得到的解释是：静态可能采取的一个费时间的路线，数据变了但访问的策略没有变；重新绑定就意味着重新改变访问策略），相反也适用。

最后，将它们放到一起对DB2 UDB 系统或任一复杂RDBMS的调优，为了得到最佳的性能将会是一个长的过程。在这一系列专栏我通过对数据库的分析，解释了性能问题是如何典型地出现从一个或更多的下列：

粗劣的系统(环境) 配置粗劣的实例配置粗劣的数据库配置粗劣的数据库设计粗劣的应用设计。系统调优应该从DB2UDB注册变量，DB2 数据库管理器实例配置参量以及可能有对性能产生巨大影响的数据库配置参量开始。接下来再考虑缓冲池如何使用并且确定是否使用附加的缓冲池或不同的缓冲池大小会有所帮助。选择适当的表空间类型，extent大小和prefetch 大小，并且保持系统目录统计最新，最终完成基本性能调整。

《全文完》