DB2V9内存分配与使用策略

1、一、 简介理解 DB2 如何使用内存，可以防止过度分配内存，并有助于对内存的使用进行调优，从而获得更好的性能。DB2 V9 引入了自动自调整内存管理 ，自适应的自调整内存功能通过自动设置内存配置参数值以及调整缓冲池大小来简化内存配置任务。启用此功能后，内存调整器就会在几个内存使用者（包括：排序、程序包高速缓存、锁定列表和缓冲池）之间动态地分配可用内存资源，从而简化了内存配置参数调整的工作量。在DB2 V9之前，数据库 的内存配置参数和缓冲池都是由管理员手工设置的。而数据库的工作负载很少是静态的，而是动态变化的。比如，当工作负载类型发生变化（由事务型变成了混合型等）、用户增加

2、、维护任务的运行或者其它应用程序消耗的资源变化等，都会造成工作负载在不停的变化。因此，即便是资深的数据库管理员调优过的系统，在另一个时候也未必是最优的。数据库管理员需要根据变化随时做出调整，工作量大而效果也不明显。变化可能在很短的时间内发生，因此留给数据库管理员作出响应的时间很短。数据库内存设置尤其容易受这些变化的影响，因而会严重影响响应时间。 二、 DB2 内存结构DB2在4种不同的内存集（memory set）内拆分和管理内存。这4种内存集分别是：n 实例共享内存（instance shared memory） n 数据库共享内存

3、（database shared memory） n 应用程序组共享内存（application group shared memory） （略）n 代理私有内存（agent private memory）1. 实例共享内存每个DB2实例都有一个实例共享内存。实例共享内存是在数据库管理器启动（db2start）时分配的，并随着数据库管理器的停止（db2stop）而释放。这种内存集用于实例级的任务，例如监控、审计和节点间通信。获取配置命令： db2 get dbm cfginstance_memory

4、60;默认值是AUTOMATIC。可以使用下面的命令来确定它的值： db2 attach to instance_name（其中 instance_name是实例的名称） db2 get dbm cfg show detail上面的输出表明有13.6GB的内存被预留给实例共享内存集（3433509页*4096字节/页）： instance_memory 参数只是设置了实例共享内存的限制，它并没有说出当前使用了多少内存。要查明一个实例的内存使用情况，可以使用DB2内存

5、跟踪器工具db2mtrk。例如，  db2mtrk -i -v上面的例子表明，实例内存只用到了大约14MB。那么还有很大一部分内存哪去了？答案是instance_memory包含了database_memory总和的限制。2. 数据库共享内存每个数据库有一个数据库共享内存集。数据库共享内存是在数据库被激活或者第一次被连接上的时候分配的,在数据库处于非激活状态时释放（如果数据库先前是处于激活状态）或者最后一个连接被断开的时候释放。这种内存用于数据库级的任务，例如备份/恢复、锁定和SQL的执行。完整的绿色方框意味着，在数据库启动的时候，该内存池是完全分配的，

6、否则，就只分配部分的内存。获取配置命令： db2 get db cfg for TSO1) 主缓冲池(Main Bufferpools)数据库缓冲池通常是数据库共享内存中最大的一块内存。DB2 在其中操纵所有常规数据和索引数据。一个数据库必须至少有一个缓冲池，并且可以有多个缓冲池，这要视工作负载的特征、数据库中使用的数据库页面大小等因素而定。例如，页面大小为 8KB 的表空间只能使用页面大小为 8KB 的缓冲池。查看主缓冲池的大小有多少，可以发出： SELECT * FROM SYSCAT.BUFFERP

7、OOLS;NPAGE列代表页数量，PAGESIZE为页大小。NPAGE=-2，代表缓冲池大小是自动调整的，非固定。注：NPAGE=-1 ，表示缓冲池大小由db cfg的buffer_page大小控制，默认是1000页。如何创建自动调整内存大小的缓冲池：create bufferpool BP4K immediate size 100000 automatic pagesize 4K2) 隐藏的缓冲池   当数据库启动时，要分配4个页宽分别为4K、8K、16K和32K的小型缓冲池。这些缓冲池是“隐藏”的，因为在系统编目中看不到它们（通过 SELECT

8、60;* FROM SYSCAT.BUFFERPOOLS 显示不出）。3) util_heap_sz指示可由BACKUP、RESTORE、LOAD实用程序同时使用的内存量。4) pckcachesz用于高速缓存数据库上的静态和动态SQL以及Xquery语句的部分。5) dbheap包含表、索引、表空间和缓冲池的控制块信息，还包括日志缓冲区的空间(logbufsz)和实用程序使用的临时内存。因此，堆大小将取决于许多变量。6) logbufsz在将日志记录写入磁盘之前，此参数允许您指定用作这些记录的缓冲区的数据库堆大小（由dbheap参数定义）。当发生下列一种情况时会

9、将日志记录写入磁盘：l 一个事务落实或一组事务落实，由mincommit配置参数定义l 日志缓冲区已满l 发生了某些其他内部数据库管理器事件此参数也必须小于或等于dbheap参数。缓冲日志记录将使日志记录文件I/O更有效，因为将日志记录写入磁盘的频率越低，每次可写入的日志记录就越多。可以使用数据库系统监视器来确定将多少日志缓冲区空间用于特定事务（或工作单元）。7) catalogcache_sz此参数指定目录高速缓存可以使用的数据库堆中的最大空间（以页计）。高速缓存各个数据库分区中的目录信息允许数据库管理器不需要访问系统目录（或分区数据库环境中的目录节点）即可获取先前检测的信息，从而降低其内部

10、开销。使用目录高速缓存可以帮助提高下列各项的整体性能：l 绑定程序包以及编译SQL和Xquery语句l 涉及检查数据库级别特权、例程特权、全局变量特权和角色权限的操作l 连接至分区数据库环境中的非目录节点的应用程序8) locklist此参数指示分配给锁定列表的内存量。每个数据库都有一个锁定列表，锁定列表包含由同时连接至数据库的所有应用程序挂起的锁定。在所有平台上，每个锁定需要128或256字节的锁定列表，这取决于是否对该对象挂起了其他锁定：对于未挂起其他锁定的对象，挂起一个锁定需要256个字节。对于存在挂起的锁定的对象，记录一个锁定需要128个字节。一旦锁定列表已满，性能就可能会降低，因为锁

11、定升级将生成更多的表锁定和更少的行锁定，从而降低数据库中共享对象的并行性。9) 排序堆的阈值（sheapthres，sheapthres_shr）   如果没有索引满足所取的行的要求顺序，或者优化器断定排序的代价低于索引扫描，那么就需要进行排序。DB2 中有两种排序，一种是私有排序，一种是共享排序。私有排序发生在代理的私有代理内存中，而共享排序发生在数据库的数据库共享内存中。  对于私有排序，数据库管理器配置参数 sheapthres 指定了私有排序在任何时刻可以消耗的内存总量在实例范围内的 软限制。如果

12、一个实例总共消耗的私有排序内存达到了这一限制，那么为额外传入的私有排序请求所分配的内存将大大减少。如果启用了内部分区并行性（intra-partition parallelism）或者集中器（concentrator），那么当 DB2 断定共享排序比私有排序更有效时，DB2 就会选择执行共享排序。如果执行共享排序，那么就会在数据库共享内存中分配用于这种排序的排序堆。用于共享排序的最大内存量是由 sheapthres_shr 数据库参数指定的。这是对共享排序在任何时刻可以消耗的内存总量在数据库范围内的 硬限制。当达到这个限制时

13、，请求排序的应用程序将收到错误 SQL0955 (rc2)。之后，在共享内存总消耗量回落到低于由 sheapthres_shr 指定的限制之前，任何共享排序内存的请求都得不到允许。 10) 查看内存大小：查看数据库详细配置信息：db2inst3perf233 $ db2 connect to TSOJX user tso using tsodb2inst3perf233 $ db2 get db cfg for TSOJX show detail使用db2mtrk工具显示当前使用的内存量： db2mtrk -i -d -v3. 应用程

14、序组共享内存 这种共享内存集仅适用于以下环境。（对于其他环境，这种内存集不存在。）多分区（multi-partitioned）数据库。 启用了内部并行（intra-parallel）处理的未分区（non-partitioned）数据库。支持连接集中器的数据库。                注意：当 max_connections的值大于 max_coordagents的值时，连接集中器便被启用。

15、这两个参数可以在数据库管理器配置中找到。（使用GET DBM CFG显示数据库管理器配置。） 4. 代理私有内存        每个DB2代理进程都需要获得内存，以执行其任务。代理进程将代表应用程序使用内存来优化、构建和执行访问计划，执行排序，记录游标信息（例如位置和状态），收集统计信息，等等。为响应并行环境中的一个连接请求或一个新的SQL请求，要为一个DB2代理分配代理私有内存。  代理的数量受下面两者中的较低者限制：所有活动数据库的数据库配置参数maxappls

16、的总和。数据库管理器配置参数maxagents的值，这指定了允许的最大代理数。代理私有内存集由以下内存池组成。Application Heap （ applheapsz）         Sort Heap （ sortheap）         Statement Heap （ stmtheap）  

17、60;      Statistics Heap （ stat_heap_sz）         Query Heap （ query_heap_sz）         Java Interpreter Heap （ java_heap_sz） &#

18、160;       Agent Stack Size （ agent_stack_sz） （仅适用于 Windows） 私有内存何时释放呢？答案取决于 dbm cfg 参数 num_poolagents的值。该参数的值指定任何时候可以保留的闲置代理的最大数目。如果该值为 0，那么就不允许有闲置代理。只要一个代理完成了它的工作，这个代理就要被销毁，它的内存也要返回给操作系统。如果该参数被设为一个非零值，那么

19、一个代理在完成其工作后不会被销毁。相反，它将被返回到闲置代理池，直到闲置代理的数目到达 num_poolagents指定的最大值。当传入一个新的请求时，就要调用这些闲置代理来服务该新请求。这样就减少了创建和销毁代理的开销。 三、 自动调整内存从 DB2® V9 开始，内存调整功能通过自动设置一些内存配置参数的值来简化内存配置任务。启用此功能之后，内存调整器将在下列内存使用者之间动态分配可用的内存资源：缓冲池、锁定内存、程序包高速缓存和排序内存。调整器在 database_memory 配置参数所定义的内存限制范围内工作。此参数的值也可以自动调整

20、。启用自调整功能（将 database_memory 的值设置为 AUTOMATIC）之后，调整器将确定数据库的整体内存需求并根据当前数据库需求来增加或减少分配给数据库共享内存的内存量。例如，如果当前数据库需求很高，并且系统上有足够的可用内存，那么将为数据库共享内存分配较多的内存。如果数据库内存需求下降，或者系统上的可用内存量变得过低，那么将释放一些数据库共享内存。默认配置如下：可以对下列内存使用者启用自调整(把相应的参数设置为 AUTOMATIC)：l 缓冲池（由 ALTER BUFFERPOOL 和 CREATE BUFFERPOOL 语句指定AUTOMATIC属性）。

21、l 程序包高速缓存（由 pckcachesz 配置参数控制）。l 锁定内存（由 locklist 和 maxlocks 配置参数控制）。l 排序内存（由 sheapthres_shr 和 sortheap 配置参数控制）。l 数据库共享内存（由 database_memory 配置参数控制）。 注：DB_MEM_THRESH参数此数据库配置参数表示数据库管理器允许的已落实但当前未使用的数据库共享内存最大百分比，达到此百分比后，数据库管理器将开始释放已落实的内存页以将它们返回给操作系统。可接受的值是整数 0（立即释放任何未使用的数据库共享内存）到 100（永远不释放任何未使用的数据库共

22、享内存）。缺省值是 10（仅当当前未使用的数据库共享内存超过 10% 时，才释放那些内存）。四、 优化建议1. 使用合适的缓冲池大型缓冲池具有下列优点：· 它们使频繁被请求的数据页能够保留在缓冲池中，从而提高访问速度。较少的 I/O 操作可以减少 I/O 争用情况，从而缩短响应时间并减少 I/O 操作所需的处理器资源。· 它们提供了在保持响应时间不变的前提下实现更高事务处理速率的机会。· 它们能够防止频繁使用的磁盘存储设备（例如用于存储目录表以及频繁引用的用户表和索引的设备）发生 I/O 争用情况。由于包含临时表空间的磁盘存储设备上的 I/O 争用情况减少，因此查

23、询所需执行的排序操作也能受益。多个缓冲池的优点：如果系统中存在下列任何一种情况，那么应该只使用一个缓冲池：· 缓冲池总空间量小于 10000 个 4 KB 页· 找不到具备该应用程序知识来执行专门调试的人员· 您正在测试系统中工作在所有其他情况下，请考虑使用多个缓冲池，原因如下：· 可以将临时表空间指定到独立的缓冲池，以便提高需要临时存储器的查询（尤其是执行大量排序操作的查询）的性能。· 如果数据必须由很多小型的更新事务应用程序反复并快速地访问，那么请考虑将包含该数据的表空间指定到独立的缓冲池。如果此缓冲池的大小合适，那么它的页被找到的机会将更

24、大，这有利于缩短响应时间和降低事务成本。· 可以将数据隔离到不同的缓冲池中，以利于特定的应用程序、数据和索引。例如，您可能想将频繁更新的表和索引放在单独的缓冲池中，以便与那些频繁查询但很少更新的表和索引分开。· 对于很少使用的应用程序（尤其是需要对一个很大的表非常随机地进行访问的应用程序）所访问的数据，可使用较小的缓冲池。在此情况下，不需要使数据保存在缓冲池中的时间超出单个查询的运行时间。最好为此类数据保留一个小缓冲池，以便腾出额外的内存供其他缓冲池使用。将数据分到不同的缓冲池之后，可以根据统计信息和记帐跟踪来生成良好并且成本相对较低的性能诊断数据。增加缓冲池大小一般会改进

25、命中率，但您会达到一个最优状态，而无法继续改进。从理论上说，如果能够分配大到足以存储整个数据库的缓冲池，则系统启动并运行后你可以得到 100% 的命中率。但在许多情况下这是不现实的。命中率的高低实际上取决于数据的大小以及访问数据的方式。如果数据库很大并且数据访问比较平均，则命中率将会很低。对于非常大的表来说，您几乎无能为力。在此情况下，应该将重点放在较小并且访问较为频繁的表以及索引上。如果可能的话，将它们指定给希望获取高命中率的各个缓冲池。如果采用多缓冲池的方法，对于初学者来说使用 4 个缓冲池比较合适：· 一个中等大小的缓冲池，用于临时表空间。 · 一个大型的缓冲池，用于

26、索引表空间。 · 一个大型的缓冲池，用于那些包含经常要访问的表的表空间。 · 一个小型的缓冲池，用于那些包含访问不多的表、随机访问的表或顺序访问的表的表空间。 对于 DMS 只包含 LOB 数据的表空间，可以为其分配任何缓冲池，因为 LOB 不占用缓冲池空间。2. 数据预取1) 顺序预取使用单一 I/O 操作将多个连续的页读入缓冲池可以大大降低应用程序开销。当数据库管理器确定顺序 I/O 合适并且预取操作有助于提高性能时，预取操作就会启动。对于表扫描和表排序这类情况，数据库管理器将自动选择顺序预取。以下示例（可能要求执行表扫描）适合于顺序预取：SELECT NAME FRO

27、M EMPLOYEE顺序检测有时，并不能立即确定顺序预取能否提高性能。在此类情况下，数据库管理器可以监视 I/O 并在顺序页读操作发生时激活预取。这种类型的顺序预取称为顺序检测，并且同时适用于索引页和数据页。您可以使用 seqdetect 数据库配置参数来控制数据库管理器是否执行顺序检测。例如，如果启用顺序检测，那么顺序预取有可能使以下 SQL 语句受益：SELECT NAME FROM EMPLOYEE WHERE EMPNO BETWEEN 100 AND 3000在本示例中，优化器可能已开始使用基于 EMPNO 列的索引来扫描表。如果该表相对于此索引而言高度集群，那么

28、数据页读操作将几乎按顺序进行，并且预取有助于提高性能。同样，如果必须检查许多索引页，并且数据库管理器检测到正在按顺序读取索引页，那么有可能进行索引页预取。表空间的 PREFETCHSIZE 选项的含义CREATE TABLESPACE 或 ALTER TABLESPACE 语句的 PREFETCHSIZE 子句允许您指定执行数据预取时将从表空间中读取的预取页数。您指定的值（或者“AUTOMATIC”）将存储在 SYSCAT.TABLESPACES 目录视图的 PREFETCHSIZE 列中。您最好将 PREFETCHSIZE 值明确设置为表空间容器数、每个容器下的物理磁盘数（如果使用了 RAI

29、D 设备的话）与表空间的 EXTENTSIZE 值（数据库管理器在使用另一个容器前写入容器的页数）的乘积。例如，如果扩展数据块大小为 16 页，并且表空间包含两个容器，那么可以将预取大小设置为 32 页。如果每个容器有 5 个物理磁盘，那么可以将预取大小设置为 160 页。数据库管理器将监视缓冲池的使用情况，以确保预取不会从缓冲池中除去另一个工作单元所需的页。为了避免引起问题，数据库管理器可将预取的页数限制为小于您对表空间指定的数目。预取大小可能会显著影响性能，对于大型表扫描而言尤其如此。请使用数据库系统监视器和其他系统监视工具来帮助您调整表空间的预取大小。您可以收集有关是否存在下列情况的信息

30、：查询等待 I/O（通过使用适用于操作系统的监视工具来检查这种情况）正在进行预取（通过查看数据库系统监视器提供的 pool_async_data_reads（缓冲池异步数据读取）数据元素来检查这种情况）如果查询预取数据时发生 I/O 等待情况，那么请增大 PREFETCHSIZE 的值。如果预取程序不是这些 I/O 等待情况的原因，那么增大 PREFETCHSIZE 值对提高查询性能没有帮助。对于所有类型的预取而言，如果预取大小是表空间的扩展数据块大小的倍数，并且这些扩展数据块在多个不同的容器中，那么可以并行地执行多个 I/O 操作。为了提高性能，请将容器配置为使用多个不同的物理设备

31、。2) 列表预取列表预取（即列表顺序预取）是一种高效访问数据页的方法，即使那些数据页不连续亦如此。列表预取功能可以与单索引访问或多索引访问结合使用。如果优化器使用索引来访问行，那么它可将数据页读操作推迟到从该索引中获取所有行标识 (RID) 之后进行。例如，优化器可以执行索引扫描以确定要检索的行和数据页。INDEX IX1: NAME ASC,DEPT ASC,MGR DESC,SALARY DESC,YEARS ASC然后，使用以下搜索条件：WHERE NAME BETWEEN 'A' and 'I'如果数据未根据此索引进行集群，那么列表预取过程将包括对索引扫

32、描操作所获取的 RID 列表进行排序的步骤。3. 调整排序性能由于查询通常需要经过排序或分组的结果，因此正确地配置排序堆对于良好的查询性能而言至关重要。在下列情况下，需要进行排序：· 不存在能够满足所请求顺序的索引（例如，使用了 ORDER BY 子句的 SELECT 语句）· 存在索引，但执行排序比使用索引更有效· 创建索引· 删除索引，这将导致对索引页号进行排序影响排序的元素下列因素将影响排序性能：· 下列配置参数的设置：o 排序堆大小（sortheap），此参数指定用于执行每次排序的内存量o 排序堆阈值（sheapthres）和共享排序的

33、排序堆阈值（sheapthres_shr），这些参数控制整个实例中可用于执行排序的内存总量· 工作负载中需要执行大量排序操作的语句数· 是否存在有助于避免执行不必要的排序操作的索引· 使用了未最大程度减少排序需求的应用程序逻辑· 并行排序，这可以提高排序性能，但仅当语句使用分区内并行性时才可行· 排序是否溢出。如果经过排序的数据在排序堆（这是每次执行排序时分配的内存块）中放不下，那么数据将溢出到数据库所拥有的临时表中。· 排序结果是否管道式结果。如果经过排序的数据可直接返回，而不需要一个临时表来存储经过排序的列表，那么这是管道式排序。对于管道式排序，在应用程序关闭与排序相关联的