DB2 V9自动自调整内存详解

核心提示： 启用自调整内存功能 首先我们创建一个单一分区数据库MYDB1,由于默认情况下，单一分区数据库启用自调整内存功能，DB2 V9自动自调整内存详解(3)，所以我们创建的示例数据库MYDB1就启动了自动自调整内存功能，其数据库参数self_tuning_mem被自动设置成了ON,并把程序包高速缓存、锁定内存、排序内存和

启用自调整内存功能 

　  首先我们创建一个单一分区数据库MYDB1,由于默认情况下，单一分区数据库启用自调整内存功能，所以我们创建的示例数据库MYDB1就启动了自动自调整内存功能，其数据库参数self_tuning_mem被自动设置成了ON,并把程序包高速缓存、锁定内存、排序内存和数据库共享内存等内存使用者默认启动了自动自调整内存功能，默认创建的缓冲池IBMDEFAULTBP也启用了自调整内存功能。我们在DB2CMD窗口中，发出CREATE DB 命令，创建示例数据库MYDB1具体如清单1所示：

--清单 1. 创建启用自动自调整内存的示例数据库MYDB1C:\>DB2 CREATE DATABASE MYDB1DB20000I  CREATE DATABASE命令成功完成。

　  我们通过GET DB CFG命令查看示例数据库MYDB1的配置参数，会发现其自调整内存参数（self_tuning_mem）被设置成了ON，数据库共享内存大小(DATABASE_MEMORY) 、 锁定列表的最大存储量参数(LOCKLIST)、 每个应用程序的锁定百分比列表参数(MAXLOCKS)、 程序包高速缓存大小参数(PCKCACHESZ)、 共享排序的排序堆域值参数(SHEAPTHRES_SHR)和 排序列表堆参数(SORTHEAP)都被设置成了AUTOMATIC，表示这些参数启用自动自调整内存，另外数据库内存阈值参数(DB_MEM_THRESH)被设置成了10。 

　  DB2 V9中新增了数据库配置参数 db_mem_thresh，以控制有多少物理 RAM 被 database_memory 参数的未使用部分占用。此数据库配置参数指定数据库管理器如何处理未用数据库共享内存过多这一问题。通常，当进程访问内存页时，内存页处于已落实状态，这表示操作系统已分配了该内存页，并且该内存页占用物理内存空间或磁盘上的页文件空间。根据数据库的工作负载，数据库共享内存需求在一天中的某些时间可能会达到峰值。一旦操作系统有足够的已落实内存来满足那些峰值需求后，该内存就会一直处于已落实状态，即使内存需求从峰值回落亦如此。此数据库配置参数表示数据库管理器允许的已落实但当前未使用的数据库共享内存最大百分比，达到此百分比后，数据库管理器将开始释放已落实的内存页以将它们返回给操作系统。可接受的值是整数 0（立即释放任何未使用的数据库共享内存）到 100（永远不释放任何未使用的数据库共享内存）。缺省值是 10（仅当当前未使用的数据库共享内存超过 10% 时，才释放那些内存）。 

　  具体情况如清单2所示：

--清单 2. 查看示例数据库MYDB1配置参数C:\>DB2 GET DB CFG FOR MYDB1数据库 MYDB1 的数据库配置数据库配置发行版级别                                    = 0x0b00数据库发行版级别                                        = 0x0b00数据库地域                                              = CN数据库代码页                                            = 1386数据库代码集                                            = GBK数据库国家／地区代码                                    = 86数据库整理顺序                                          = UNIQUE备用整理顺序                              (ALT_COLLATE) =数据库页大小                                            = 4096.......自调整内存                            (SELF_TUNING_MEM) = ON数据库共享内存大小（4KB）             (DATABASE_MEMORY) = AUTOMATIC数据库内存阈值                          (DB_MEM_THRESH) = 10锁定列表的最大存储量（4KB）                   (LOCKLIST) = AUTOMATIC每个应用程序的锁定百分比列表                 (MAXLOCKS) = AUTOMATIC程序包高速缓存大小（4KB）                  (PCKCACHESZ) = AUTOMATIC共享排序的排序堆域值（4KB）            (SHEAPTHRES_SHR) = AUTOMATIC排序列表堆（4KB）                            (SORTHEAP) = AUTOMATIC........

　  清单2中所示的程序包高速缓存大小(PCKCACHESZ)参数，是在数据库共享内存之外分配的，并且用于高速缓存数据库上的静态和动态 SQL 和 XQuery 语句的部分。高速缓存程序包使数据库管理器在重新装入程序包时可以不访问系统目录；或者对于动态 SQL 或 XQuery 语句，可以免去编译这一步，从而减少其内部开销。将这些段保存在程序包高速缓存中，直到发生下列其中一种情况：

?数据库关闭 。

?程序包或动态 SQL 或 XQuery 语句无效 。

?高速缓存空间用完。 

　  静态或动态 SQL 或 XQuery 语句节的高速缓存可提供性能，尤其是在与数据库连接的应用程序多次使用同一个语句时。这在事务处理应用程序中特别重要。当此参数设置为 AUTOMATIC 时，就启用了自调整功能。当 self_tuning_mem 设置为 ON 时，内存调整器将在工作负载需求更改时动态调整 pckcachesz 控制的内存区的大小。由于内存调整器在不同内存使用者之间交换内存资源，所以，必须至少有两个内存使用者启用自调整功能才能使自调整功能有效。需要注意的是，程序包高速缓存是工作高速缓存，所以不能将此参数设置为零。此高速缓存中必须分配有足够的内存以保存当前执行的 SQL 或 XQuery 语句的所有节。如果分配的空间比当前需要的空间多，则各节被高速缓存。下一次需要这些部分时，只需执行它们而不必将其装入或进行编译。由 pckcachese 参数指定的限制是软限制。如果数据库共享集合中还有可用的内存，如果有必要的话，可以超过该限制。 

　  清单2中所示的锁定列表的最大存储量 (LOCKLIST)参数，是用来指示分配给锁定列表的内存量。每个数据库有一个锁定列表，锁定列表包含由同时连接至数据库的所有应用程序挂起的锁定。锁定是数据库管理器用来控制多个应用程序并发访问数据库中的数据的机制。行和表都可以锁定。数据库管理器还可以获取锁定来供内部使用。当此参数设置为 AUTOMATIC 时，就启用了自调整功能。这允许内存调整器根据工作负载需求变化动态地调整此参数控制的内存区大小。由于内存调整器在不同内存使用者之间交换内存资源，所以，必须至少有两个内存使用者启用自调整功能才能使自调整功能有效。locklist 值是与 maxlocks 参数一起调整的，因此，如果禁用 locklist 参数自调整功能，也将自动禁用 maxlocks 参数自调整功能。如果启用 locklist 参数自调整功能，也将自动启用 maxlocks 参数自调整功能。当一个应用程序使用的锁定列表百分比达到 maxlocks 时，数据库管理器就会对该应用程序挂起的锁定执行从行到表的锁定升级。虽然升级过程本身花不了多少时间，但是锁定整个表（与个别行比较）降低了并发性，并且可能因对受影响的表进行后续访问而降低整个数据库性能。关于如何控制锁定列表大小的建议是：

?经常执行 COMMIT 以释放锁定。

?当执行很多更新时，在更新前锁定整个表（使用 SQL LOCK TABLE 语句）。这样将只使用一个锁定，防止其他锁定干扰更新，但却降低了数据的并发性。

?还可以使用 ALTER TABLE 语句的 LOCKSIZE 选项来控制如何对特定表进行锁定。

?使用“可重复读”隔离级别可能会导致自动表锁定。 

　  只要有可能，使用“游标稳定性”隔离级别以减少所挂起的共享锁定数。如果不会影响到应用程序的完整性需求，则使用“未落实的读”代替“游标稳定性”以进一步减少锁定量。 将 locklist 设置为 AUTOMATIC。锁定列表将同步地增大以避免发生锁定升级或锁定列表满的情况。 一旦锁定列表已满，性能就可能会降低，因为锁定升级将生成更多的表锁定和更少的行锁定，从而降低数据库中共享对象的并发性。另外，应用程序间可能有更多的死锁（因为这些应用程序都在等待有限数目的表锁定），这样将导致事务回滚。当数据库的锁定请求数达到最大值时，应用程序将接收到值为 -912 的 SQLCODE。 

　  清单2中所示的每个应用程序的锁定百分比列表(MAXLOCKS)参数，是用来定义应用程序所挂起的锁定列表的百分比，和locklist一起决定了每个应用程序可以使用的锁定数量，超过了将引发锁升级。当此参数设置为 AUTOMATIC 时，就启用了自调整功能。这允许内存调整器根据工作负载需求变化动态地调整此参数控制的内存区大小。由于内存调整器在不同内存使用者之间交换内存资源，所以，必须至少有两个内存使用者启用自调整功能才能使自调整功能有效。 

　  清单2中所示的共享排序的排序堆域值 (SHEAPTHRES_SHR)参数，是表示对排序内存使用者每次可使用的数据库共享内存总量的软限制。除排序以外，还有其他排序内存使用者（例如，散列连接、索引 AND 运算、块索引 AND 运算、合并连接和内存表）。当共享排序内存使用者的共享内存总量达到 sheapthres_shr 限制时，就会激活内存调节机制，将来的共享排序内存使用者请求得到的内存量将少于请求的内存量，但始终多于完成任务所需的最低内存量。一旦达到 sheapthres_shr 限制，排序内存使用者的所有共享排序内存请求都将获得完成任务所必需的最低内存量。当活动共享排序内存使用者的共享内存总量达到此限制时，后续排序可能会失败（SQL0955C）。当数据库管理器配置参数 sheapthres 值为 0 时，数据库的所有排序内存使用者都将使用 sheapthres_shr 控制的数据库共享内存，而不是使用专用排序内存。当 sheapthres_shr 设置为 AUTOMATIC 时，就启用了自调整功能。这允许内存调整器根据工作负载需求变化动态地调整此参数控制的内存区大小。由于内存调整器在不同内存使用者之间交换内存资源，所以，必须至少有两个内存使用者启用自调整功能才能使自调整功能有效。内存使用者包括 SHEAPTHRES_SHR、PCKCACHESZ、BUFFER POOL（每个缓冲池计数为一个）、LOCKLIST 和 DATABASE_MEMORY。仅当数据库管理器配置参数 sheapthres 设置为 0 时，才允许自动调整 sheapthres_shr。sortheap 值是与 sheapthres_shr 参数一起调整的，因此，如果禁用 sortheap 参数自调整功能，也将自动禁用 sheapthres_shr 参数自调整功能。如果启用 sheapthres_shr 参数自调整功能，也将自动启用 sortheap 参数自调整功能。 

　  清单2中所示的排序列表堆(SORTHEAP) 参数，是定义要用于专用排序的专用内存页的最大数目或要用于共享排序的共享内存页的最大数目。当排序完成时，如果排序为专用排序，那么此参数将影响代理程序专用内存。如果排序为共享排序，那么此参数将影响数据库共享内存。每个排序都有一个独立的排序堆，该排序堆由数据库管理器根据需要分配。此排序堆是将数据排序的区域。如果由优化器定向，那么将使用优化器提供的信息分配一个比此参数指定的排序堆小的排序堆。当此参数设置为 AUTOMATIC 时，就启用了自调整功能。这允许内存调整器根据工作负载需求变化动态地调整此参数控制的内存区大小。当使用排序堆时，应该考虑下列事项：
?适当的索引可使排序堆的使用减至最小程度。

?散列连接缓冲区、块索引 AND 运算、合并连接、内存中的表以及动态位映射（用于索引 AND 运算和星型连接）使用排序堆内存。在使用这些技术时，增大此参数的大小。

?当需要进行频繁的大型排序时，增大此参数的大小。

?当增大此参数的值时，应该检查是否还需要调整数据库管理器配置文件中的 sheapthres 和 sheapthres_shr 参数。

?排序堆大小由优化器在确定访问路径时使用。在更改此参数后，应考虑重新绑定应用程序（使用 REBIND 命令）。 

　  清单2中所示的数据库共享内存大小 (DATABASE_MEMORY)参数，是用来设置对每个数据库将使用的内存量的限制。在 DB2? V9.1 中，database_memory 配置参数的 COMPUTED 设置等价于 DB2 UDB V8 AUTOMATIC 设置。如果要保留 DB2 V8 行为，则将 database_memory 参数设置为 COMPUTED。在 DB2 V9.1 中，将 database_memory 参数设置为 AUTOMATIC 会启用新增的自调整内存管理功能部件，它可自动调整数据库内存使用。调整器在 database_memory 配置参数定义的内存限制范围内工作。在 Windows? 和 AIX? 上，database_memory 值本身可以自动调整。database_memory 启用自调整（设置为 AUTOMATIC）后，调整器确定数据库的整体内存需求并根据当前数据库需求增大或减小分配给数据库共享内存的内存量。例如，如果当前数据库需求很高，并且系统上有足够的可用内存，则数据库共享内存将消耗较多的内存。当数据库内存需求下降时，或者当系统上的可用内存量降至过低水平时，就会释放一些数据库共享内存。database_memory 参数未启用自调整（未设置为 AUTOMATIC）时，整个数据库都将使用指定的内存量，从而根据需要在数据库内存使用者之间分配内存。database_memory 未启用自调整时，可以通过两种方法指定数据库使用的内存量：将 database_memory 设置为数值或者将其设置为 COMPUTED。在第二种情况下，总内存量是根据数据库启动时的数据库内存堆初始值总计而计算的。一定要注意：database_memory 的 AUTOMATIC 设置仅在 AIX? 和 Windows? 操作系统上受支持。在其他平台上，database_memory 的缺省值是 COMPUTED。 

　  接下来我们查看一下默认创建的4K页缓冲池IBMDEFAULTBP的自调整设置，具体如清单3所示：

--清单 3. 查看默认创建的4K页缓冲池IBMDEFAULTBP的自调整设置C:\>db2 connect to mydb1数据库连接信息数据库服务器         = DB2/NT 9.1.0SQL 授权标识         = RHETTE本地数据库别名       = MYDB1C:\>db2 "select BPNAME, NPAGES from sysibm.sysbufferpools"BPNAME                                      NPAGES------------------------------------------------               -----------IBMDEFAULTBP                                  -21 条记录已选择。

　  当缓冲池启用了自调整功能时，该特定缓冲池的 sysibm.sysbufferpools 表中的 NPAGES 字段将设置为 -2。当自调整功能处于禁用状态时，NPAGES 字段将设置为缓冲池的当前大小。我们可以在清单2中看到，IBMDEFAULTBP的NPAGES 字段将设置为 -2，由此我们可以确定，IBMDEFAULTBP启用了自调整功能。 

　  下面我们创建示例缓冲池MYBP1，其使用自调整功能（注意其create bufferpool语句使用了automatic），初始大小为400K，具体如清单4所示：

--清单 4. 创建使用自动自调整功能的示例缓冲池MYBP1C:\>db2 create bufferpool mybp1 immediate size 100  automatic  pagesize 4kDB20000I  SQL命令成功完成。

　  接下来创建示例缓冲池MYBP2，其不使用自调整功能，，初始大小为400K，具体如清单5所示：

--清单 5. 创建不使用自动自调整功能的示例缓冲池MYBP2C:\>db2 create bufferpool mybp2 immediate size 100  pagesize 4kDB20000I  SQL命令成功完成。

　  此时我们通过访问sysibm.sysbufferpools可以看到mybp1的NPAGES字段设置成了-2，而mybp2的NPAGES字段都设置成了100，具体如清单6所示：

--清单 6. 查看缓冲池C:\>db2 "select BPNAME, NPAGES from sysibm.sysbufferpools"BPNAME                              NPAGES------------------------------------------------       -----------IBMDEFAULTBP                            -2MYBP1                                    -2MYBP2                                    1003 条记录已选择。

　  现在我们将mybp2改成使用自调整功能，在DB2CMD环境中，执行ALTER BUFFERPOOL命令，具体如清单7所示：

--清单 7. 修改示例缓冲池MYBP2，使其启用自调整功能C:\>db2 alter bufferpool mybp2 immediate size 100 automaticDB20000I  SQL命令成功完成。

　  再次通过访问sysibm.sysbufferpools可以看到mybp1和mybp2的NPAGES字段都设置成了-2，具体如清单8所示：

--清单 8. 查看缓冲池C:\>db2 "select BPNAME, NPAGES from sysibm.sysbufferpools"BPNAME                              NPAGES------------------------------------------------       -----------IBMDEFAULTBP                            -2MYBP1                                    -2MYBP2                                    -23 条记录已选择。

　  如果数据库是从V8迁移过来，或者被手工设置过禁止使用自调整内存功能，又想重新使用自动自调整内存功能，可以按如下步骤进行操作：

?通过将 self_tuning_mem 设置为 ON 来对数据库启用自调整功能。可以使用 UPDATE DATABASE CONFIGURATION 命令、SQLFUPD API 或通过控制中心中的更改数据库配置参数窗口来将 self_tuning_mem 设置为 ON。

?要对由内存配置参数控制的内存区域启用自调整功能，请使用 UPDATE DATABASE CONFIGURATION 命令、SQLFUPD API 或通过控制中心中的更改数据库配置参数窗口将相关配置参数设置为 AUTOMATIC。

?要对缓冲池启用自调整功能，请将缓冲池大小设置为 AUTOMATIC。可以使用 ALTER BUFFER POOL 语句（对于现有缓冲池）或 CREATE BUFFER POOL 语句（对于新缓冲池）来完成此操作。如果在 DPF 环境中将缓冲池大小设置为 AUTOMATIC，就不应该在 sysibm.sysbufferpoolnodes 中为该缓冲池定义任何条目。 

　  需要注意:

?由于自调整功能在不同内存区域之间重新分配内存，所以，必须至少启用两个内存区域（例如锁定内存区域和数据库共享内存区域）才能使自调整功能起作用。唯一的例外是由 sortheap 配置参数控制的内存。当仅将 sortheap 设置为 AUTOMATIC 时，将启用 sortheap 的自调整功能。

?要为自调整功能启用 locklist 配置参数，还必须为自调整功能启用 maxlocks，因此当 locklist 设置为 AUTOMATIC 时，maxlocks 也设置为 AUTOMATIC。要为自调整功能启用 sheapthres_shr 配置参数，还必须为自调整功能启用 sortheap，因此当 sheapthres_shr 设置为 AUTOMATIC 时，sortheap 也设置为 AUTOMATIC。

?仅当数据库管理器配置参数 sheapthres 设置为 0 时，才允许自动调整 sheapthres_shr 或 sortheap。

?自调整内存功能仅在 HADR 主服务器上运行。在 HARD 系统上激活自调整内存功能后，永远不会在辅助服务器上运行此功能，并且，仅当正确地设置配置后，此功能才会在主服务器上运行。如果运行了切换 HADR 数据库角色的命令，自调整内存操作也会切换，从而在新的主服务器上运行。