DB2数据库优化的优化方法及建议

预备—monitorsON

db2"updatemonitorswitchesusing
lockONsortONbufferpoolONuowON
tableONstatementON"

打开监视开关,获取需要的性能信息

最简单而最见成效的—Bufferpool

缓冲池是内存中的一块存储区域,用于临时读入和更改数据库页(包含表行或索引项)。缓冲池的用途是为了提高数据库系统的性能。从内存访问数据要比从磁盘访问数据快得多。因此,数据库管理器需要从磁盘读取或写入磁盘的次数越少,性能就越好。对一个或多个缓冲池进行配置之所以是调优的最重要方面,是因为连接至数据库的应用程序的大多数数据(不包括大对象和长字段数据)操作都在缓冲池中进行。

缺省情况下,应用程序使用缓冲池IBMDEFAULTBP,它是在创建数据库时创建的。当SYSCAT.BUFFERPOOLS目录表中该缓冲池的NPAGES值为-1时,DB2数据库配置参数BUFFPAGE控制着缓冲池的大小。否则会忽略BUFFPAGE参数,并且用NPAGES参数所指定的页数创建缓冲池。

建议对于仅使用一个缓冲池的应用程序,将NPAGES更改成-1,这样BUFFPAGE就可以控制该缓冲池的大小。这使得更新和报告缓冲池大小以及其它DB2数据库配置参数变得更加方便。

确保可以使用数据库配置中的BUFFPAGE参数来控制缓冲池大小之后,将该参数设置成合适的值。根据数据库的大小和应用程序的性质将该参数设置成一个合理的大值,这种做法很安全。通常,该参数的缺省值非常小,可能满足不了要求。

db2"getsnapshotforallbufferpools"

在数据库快照或缓冲池快照的快照输出中,查找下列”logicalreads”和”physicalreads”,这样就可以计算出缓冲池命中率,它可以帮助调优缓冲池:

缓冲池命中率表明数据库管理器不需要从磁盘装入页(即该页已经在缓冲池中)就能处理页请求的时间百分比。缓冲池的命中率越高,使用磁盘I/O的频率就越低。按如下计算缓冲池命中率:

(1-((bufferpooldataphysicalreads+bufferpoolindexphysicalreads)/
(bufferpooldatalogicalreads+poolindexlogicalreads))
)*100%

这个计算考虑了缓冲池高速缓存的所有页(索引和数据)。理想情况下,该比率应当超过95%,并尽可能接近100%。要提高缓冲池命中率,请尝试下面这些方法:

增加缓冲池大小。

考虑分配多个缓冲池,如果可能的话,为每个经常被访问的大表所属的表空间分配一个缓冲池,为一组小表分配一个缓冲池,然后尝试一下使用不同大小的缓冲池以查看哪种组合会提供最佳性能。

如果已分配的内存不能帮助提高性能,那么请避免给缓冲池分配过多的内存。应当根据取自测试环境的快照信息来决定缓冲池的大小。

太小的缓冲池会产生过多的、不必要的物理I/O。太大的缓冲池使系统处在操作系统页面调度的风险中并消耗不必要的CPU周期来管理过度分配的内存。正好合适的缓冲池大小就在”太小”和”太大”之间的某个平衡点上。适当的大小存在于回报将要开始减少的点上。

获得最佳性能的—SQL

一条糟糕的SQL语句会彻底破坏一切。一个相对简单的SQL语句也能够搞糟一个调整得很好的数据库和机器。对于很多这些语句,天底下(或在文件中)没有DB2UDB配置参数能够纠正因错误的SQL语句导致的高成本的情况。

更糟糕的是,DBA常常受到种种束缚:不能更改SQL(可能是因为它是应用程序供应商提供的)。这给DBA只留下三条路可走:

1.更改或添加索引

2.更改群集

3.更改目录统计信息

健壮的应用程序由成千上万条不同的SQL语句组成。这些语句执行的频率随应用程序的功能和日常的业务需要的不同而不同。SQL语句的实际成本是它执行一次的成本乘以它执行的次数。

每个DBA所面临的重大的任务是,识别具有最高”实际成本”的语句的挑战,并且减少这些语句的成本。

通过本机DB2Explain实用程序、一些第三方供应商提供的工具或DB2UDBSQLEventMonitor数据,可以计算出执行一次SQL语句所用的资源成本。但是语句执行频率只能通过仔细和耗时地分析DB2UDBSQLEventMonitor的数据来了解。

最佳性能不仅需要排除高成本SQL语句,而且需要确保相应的物理基础结构是适当的。当所有的调节旋钮都设置得恰到好处、内存被有效地分配到池和堆而且I/O均匀地分配到各个磁盘时,才可得到最佳性能。

不可遗漏的—Lock

这些与锁相关的控制都是数据库配置参数:

LOCKLIST表明分配给锁列表的存储容量。每个数据库都有一个锁列表,锁列表包含了并发连接到该数据库的所有应用程序所持有的锁。锁定是数据库管理器用来控制多个应用程序并发访问数据库中数据的机制。行和表都可以被锁定。根据对象是否还持有其它锁,每把锁需要32个或64个字节的锁列表:

需要64个字节来持有某个对象上的锁,在这个对象上,没有持有其它锁。

需要32个字节来记录某个对象上的锁,在这个对象上,已经持有一个锁。

MAXLOCKS定义了应用程序持有的锁列表的百分比,在数据库管理器执行锁升级之前必须填充该锁列表。当一个应用程序所使用的锁列表百分比达到MAXLOCKS时,数据库管理器会升级这些锁,这意味着用表锁代替行锁,从而减少列表中锁的数量。当任何一个应用程序所持有的锁数量达到整个锁列表大小的这个百分比时,对该应用程序所持有的锁进行锁升级。如果锁列表用完了空间,那么也会发生锁升级。数据库管理器通过查看应用程序的锁列表并查找行锁最多的表,来决定对哪些锁进行升级。如果用一个表锁替换这些行锁,将不再会超出MAXLOCKS值,那么锁升级就会停止。否则,锁升级就会一直进行,直到所持有的锁列表百分比低于MAXLOCKS。MAXLOCKS参数乘以MAXAPPLS参数不能小于100。

虽然升级过程本身并不用花很多时间,但是锁定整个表(相对于锁定个别行)降低了并发性,而且数据库的整体性能可能会由于对受锁升级影响的表的后续访问而降低。

LOCKTIMEOUT的缺省值是-1,这意味着将没有锁超时(对OLTP应用程序,这种情况可能会是灾难性的)。许多DB2用户用LOCKTIMEOUT=-1。将LOCKTIMEOUT设置为很短的时间值,例如10或15秒。在锁上等待过长时间会在锁上产生雪崩效应。

首先,用以下命令检查LOCKTIMEOUT的值:

db2"getdbcfgforDBNAME"

并查找包含以下文本的行:

Locktimeout(sec)(LOCKTIMEOUT)=-1

如果值是-1,考虑使用以下命令将它更改为;15秒(一定要首先询问应用程序开发者或供应商以确保应用程序能够处理锁超时):

db2"updatedbcfgforDBNAMEusingLOCKTIMEOUT15"

同时应该监视锁等待的数量、锁等待时间和正在使用锁列表内存(locklistmemory)的量。请发出以下命令:

db2"getsnapshotfordatabaseonDBNAME"

如果Locklistmemoryinuse(Bytes)超过所定义LOCKLIST大小的50%,那么在LOCKLIST数据库配置中增加4k页的数量。