互联网服务数据库
DB2 性能优化快速入门
本文是针对 DB2® Universal Database（DB2 UDB）的初学者在遇到 DB2 的性能问题的时候不知道从何分析，从何入手的这一问题，从 DB2 的体系结构介绍开始，由浅入深的讲述了 DB2 性能优化的一些基础知识，简单地介绍调优的原理和工具。同时针对性能上的一些典型问题，如 DML 性能问题 (...显示全部
查看其它 6 个回答WUJJ0828的回答
DB2 性能问题分类与分析思路
DML 性能问题
DML(Data Manipulation Language) 包括了查询，增加，删除和更新纪录等操作。首先看一下查询的性能问题，在查询一张表或多张表的联合查询时有时反应时间会比较长，这使得用户难以忍受。针对这种问题，可以通过下述方法来分析：
在查询的连接或条件子句中的相关字段是否加了索引。 ( 关于 SQL 的优化可以参见 SQL 优化相关文章，本文不再赘述 ) 。
察看缓冲池的大小，缓冲池太小会造成很多数据不能读到缓冲池而直接从硬盘上读取，造成很大的瓶颈。另一方面关于缓冲池预取的设置，一般能将预取大小 (PREFETCHSIZE) 设定为区段大小与容器个数的积，这样可以最大利用到预取的并行性。
在查询中涉及到 order by 字句时，如果排序的字段没有设置索引那么排序将会用到内存中的排序堆 (sortheap) 。如果排序堆过小会造成排序溢出到硬盘上 (Overflowed) 造成性能衰退。
同时还要考虑到 RUNSTATS/REORG 因素。 RUNSTATS 命令可以更新表中的统计信息。当表中的数据经过频繁的增删改后其相应的统计信息会发生变化，而优化器选择执行计划的时候是根据这种统计信息来计算的，所以运行 RUNSTATS 此时显得尤为重要。 REORG 可以整理数据存储的物理结构，也能减少数据扫描的时间，提高查询的性能。
从存储方面应当注意的是选取裸设备的 DMS 要比 SMS 性能要好，因为它少了一层文件系统的缓冲而直接访问缓冲池。
学会使用 optimize for n rows 子句，它可以提高前面 n 条记录的显示速度。这样可以使用户能够先快速查看这 n 条记录，然后再看其他纪录。减少了用户的等待时间。
物化查询表 (MQT) 也是提高查询性能的一种手段，它可以将经常用到的查询结果集存储到一张中间表中，在查询时减少了数据检索的时间。
在架构上采用 MPP 或 SMP 也是提高查询或写操作性能的手段。
针对复杂查询时可以将数据库配置参数 DFT_QUERYOPT( 缺省查询优化类 ) 的值设得高一些（7 或 9），针对简单查询可以将它设得低一些 (3 或 5)，因为设置越高优化器所作的分析就越深入，耗费在生成计划上的时间就越多。
针对 C/S 结构的查询可以将查询语句写在服务器端生成存储过程来减少数据的网络传输以及客户端的压力。而经过编译的存储过程执行得更加高效。
还要考虑到隔离级别与锁的因素，隔离级别越高越能保证数据的完整性，但同时会减弱并发性。这一点需要权衡需求而定。
网络因素也不可忽视，将数据库服务器参数 RQRIOBLK 设为 65534 可以相应地提高网络吞吐量。（缺省值 32767）
最后需要考虑的是数据库的结构，在某些情况下，在某些表中增加一些冗余字段虽然牺牲了一些空间和维护成本，但是在查询时可以减少很多连接操作，这样可以大大提高查询性能。就是用空间换取时间。
接下来看一下增删改的性能优化方法：
首先是索引因素，在做增删改时数据库会对表中的索引做相应的修改。这会消耗一定的资源，所以在保证数据完整性的前提下可以先将索引删除，待到增删改结束后再重建这些索引。这也会节省一些时间。将索引和数据放在不同的硬盘上也可以增加写操作的并行性。
其次要考虑日志因素，在数据写操作的同时，数据库系统也在维护着事务日志，所以应尽量减少日志维护的代价。将 auto commit 设为 false，可以减少提交的次数（同时也减少了写日志的次数）。增大 LOGBUFSZ,LOGFILSZ 可以减少刷新日志的次数以及日志文件切换的次数。或者将表的属性改为” ACTIVATE NOT LOGGED INITIALLY ” , 这样可以屏蔽表的日志操作，以提高写操作的性能，但是失去事务日志的表的数据很难修复，这一点需要权衡。
将日志和数据分别放在不同的硬盘上也可以增加写操作的并行性。
在插入记录时采用 APPEND MODE 可以消除 DB2 寻找表中间的空余空间的时间而直接插到表尾，从而提高插入的性能。
关于并行性的因素，采用 MPP 模式可以使用并行处理的方式增加写操作的性能。将容器分散在不同的硬盘上也可以增加写操作的性能。
还要考虑到约束和触发器的影响，在写操作时应当尽量避免表中有约束和触发器。在保证数据完整性的前提下可在频繁大批量写操作时先将约束或触发器去除，完毕后重建。
和查询一样，写操作同样要考虑到隔离级别和锁的因素（参见查询优化部分）。
在 insert 语句中包括多行可以减少客户机 - 服务器通信次数，提高插入性能。如：insert into table1 values (1, ’ a ’ ),(2, ’ b ’ ),(3, ’ c ’ ) 。
还有一个需要考虑的因素是 DB2 V95 在 UNIX 上的采用线程模型，在操作系统中的开销变小，使得写操作性能要比之前的 DB2 的版本要好。
DB2 实用程序的性能优化
先来看一下如何提高备份操作的性能：
提高数据库配置参数 UTIL_HEAP_SZ 的大小，这个内存区域用来为备份和恢复操作提供缓冲。
减少整库备份，多采用表空间备份需要的表空间。
减少完全备份，多采用增量备份或 DELTA 备份。
增加备份命令中的 PARALLELISM 参数来增加备份的并行性（增加线程或进程）。
增加备份命令中的 BUFFER 参数值。
增加备份的目标目录，最好能将多个目录放在不同的硬盘上，这样可以增加备份的并行程度。
再来看一下如何提高恢复操作的性能：
和备份操作一样，需要增大数据库配置参数 UTIL_HEAP_SZ 的大小。
增加恢复命令中的 BUFFERS 参数值。
增加恢复命令中的 PARALLELISM 参数来增加备份的并行性（增加线程或进程）。
容器分布于不同的硬盘上也可以使恢复操作加快（提高并行性）。
采用 SMP 模式来激活多代理来增加恢复操作的并行性。
提高导入操作（import）的性能 :
import 操作类似 insert 操作，因此很多方法可以参见 insert 的调优步骤。
添加 compound=x 选项可使导入操作批量进行而减少了网络的通信量。
增加 COMMITCOUNT 的值已减少 LOG 的 I/O 次数。
启用缓冲区插入，对 db2uimpm 程序包使用 INSERT BUF 选项重新绑定到数据库。在 import 以前执行命令: db2 bind db2uimpm.bnd insert buf
提高导出操作 (export) 的性能：
Export 操作类似 select 操作，因此很多方法可以参见 select 的调优步骤。
将 export 操作导出的文件放在与数据和日志不同的硬盘上以减少 I/O 的竞争。
提高载入操作 (load) 的性能：load 操作中日志的写操作比 import 要少，所以 load 的性能比 import 要好很多，下面还是看看如何更好地提高 load 的性能。
在多分区环境下，db2 load 会进行并行装载，性能会大幅度提高。
添加 buffer 参数可以增加装载过程中的缓存空间，提高性能。
并发连接时的性能考虑
一般来说在连接数较少情况下，db2 的性能会比较稳定。因为这时连接的应用所产生的请求比 db2 代理池中所能产生的协调代理少，这时基本上能够满足每一个请求都能够被及时的协调代理所响应处理。 在连接集中器激活（MAX_CONNECTIONS > MAX_COORDAGENTS）的情况下，如果连接数超过了协调代理，这时连接所过来的请求就会进入队列等候协调代理服务，并发的连接数提高了，但是某些连接的性能就会显著下降。此时应当考虑激活分区间并行 (SMP) 或多分区（MPP）特性来增加 I/O 的并行性以及多个 CPU 的并行运算。
DML 性能问题
DML(Data Manipulation Language) 包括了查询，增加，删除和更新纪录等操作。首先看一下查询的性能问题，在查询一张表或多张表的联合查询时有时反应时间会比较长，这使得用户难以忍受。针对这种问题，可以通过下述方法来分析：
在查询的连接或条件子句中的相关字段是否加了索引。 ( 关于 SQL 的优化可以参见 SQL 优化相关文章，本文不再赘述 ) 。
察看缓冲池的大小，缓冲池太小会造成很多数据不能读到缓冲池而直接从硬盘上读取，造成很大的瓶颈。另一方面关于缓冲池预取的设置，一般能将预取大小 (PREFETCHSIZE) 设定为区段大小与容器个数的积，这样可以最大利用到预取的并行性。
在查询中涉及到 order by 字句时，如果排序的字段没有设置索引那么排序将会用到内存中的排序堆 (sortheap) 。如果排序堆过小会造成排序溢出到硬盘上 (Overflowed) 造成性能衰退。
同时还要考虑到 RUNSTATS/REORG 因素。 RUNSTATS 命令可以更新表中的统计信息。当表中的数据经过频繁的增删改后其相应的统计信息会发生变化，而优化器选择执行计划的时候是根据这种统计信息来计算的，所以运行 RUNSTATS 此时显得尤为重要。 REORG 可以整理数据存储的物理结构，也能减少数据扫描的时间，提高查询的性能。
从存储方面应当注意的是选取裸设备的 DMS 要比 SMS 性能要好，因为它少了一层文件系统的缓冲而直接访问缓冲池。
学会使用 optimize for n rows 子句，它可以提高前面 n 条记录的显示速度。这样可以使用户能够先快速查看这 n 条记录，然后再看其他纪录。减少了用户的等待时间。
物化查询表 (MQT) 也是提高查询性能的一种手段，它可以将经常用到的查询结果集存储到一张中间表中，在查询时减少了数据检索的时间。
在架构上采用 MPP 或 SMP 也是提高查询或写操作性能的手段。
针对复杂查询时可以将数据库配置参数 DFT_QUERYOPT( 缺省查询优化类 ) 的值设得高一些（7 或 9），针对简单查询可以将它设得低一些 (3 或 5)，因为设置越高优化器所作的分析就越深入，耗费在生成计划上的时间就越多。
针对 C/S 结构的查询可以将查询语句写在服务器端生成存储过程来减少数据的网络传输以及客户端的压力。而经过编译的存储过程执行得更加高效。
还要考虑到隔离级别与锁的因素，隔离级别越高越能保证数据的完整性，但同时会减弱并发性。这一点需要权衡需求而定。
网络因素也不可忽视，将数据库服务器参数 RQRIOBLK 设为 65534 可以相应地提高网络吞吐量。（缺省值 32767）
最后需要考虑的是数据库的结构，在某些情况下，在某些表中增加一些冗余字段虽然牺牲了一些空间和维护成本，但是在查询时可以减少很多连接操作，这样可以大大提高查询性能。就是用空间换取时间。
接下来看一下增删改的性能优化方法：
首先是索引因素，在做增删改时数据库会对表中的索引做相应的修改。这会消耗一定的资源，所以在保证数据完整性的前提下可以先将索引删除，待到增删改结束后再重建这些索引。这也会节省一些时间。将索引和数据放在不同的硬盘上也可以增加写操作的并行性。
其次要考虑日志因素，在数据写操作的同时，数据库系统也在维护着事务日志，所以应尽量减少日志维护的代价。将 auto commit 设为 false，可以减少提交的次数（同时也减少了写日志的次数）。增大 LOGBUFSZ,LOGFILSZ 可以减少刷新日志的次数以及日志文件切换的次数。或者将表的属性改为” ACTIVATE NOT LOGGED INITIALLY ” , 这样可以屏蔽表的日志操作，以提高写操作的性能，但是失去事务日志的表的数据很难修复，这一点需要权衡。
将日志和数据分别放在不同的硬盘上也可以增加写操作的并行性。
在插入记录时采用 APPEND MODE 可以消除 DB2 寻找表中间的空余空间的时间而直接插到表尾，从而提高插入的性能。
关于并行性的因素，采用 MPP 模式可以使用并行处理的方式增加写操作的性能。将容器分散在不同的硬盘上也可以增加写操作的性能。
还要考虑到约束和触发器的影响，在写操作时应当尽量避免表中有约束和触发器。在保证数据完整性的前提下可在频繁大批量写操作时先将约束或触发器去除，完毕后重建。
和查询一样，写操作同样要考虑到隔离级别和锁的因素（参见查询优化部分）。
在 insert 语句中包括多行可以减少客户机 - 服务器通信次数，提高插入性能。如：insert into table1 values (1, ’ a ’ ),(2, ’ b ’ ),(3, ’ c ’ ) 。
还有一个需要考虑的因素是 DB2 V95 在 UNIX 上的采用线程模型，在操作系统中的开销变小，使得写操作性能要比之前的 DB2 的版本要好。
DB2 实用程序的性能优化
先来看一下如何提高备份操作的性能：
提高数据库配置参数 UTIL_HEAP_SZ 的大小，这个内存区域用来为备份和恢复操作提供缓冲。
减少整库备份，多采用表空间备份需要的表空间。
减少完全备份，多采用增量备份或 DELTA 备份。
增加备份命令中的 PARALLELISM 参数来增加备份的并行性（增加线程或进程）。
增加备份命令中的 BUFFER 参数值。
增加备份的目标目录，最好能将多个目录放在不同的硬盘上，这样可以增加备份的并行程度。
再来看一下如何提高恢复操作的性能：
和备份操作一样，需要增大数据库配置参数 UTIL_HEAP_SZ 的大小。
增加恢复命令中的 BUFFERS 参数值。
增加恢复命令中的 PARALLELISM 参数来增加备份的并行性（增加线程或进程）。
容器分布于不同的硬盘上也可以使恢复操作加快（提高并行性）。
采用 SMP 模式来激活多代理来增加恢复操作的并行性。
提高导入操作（import）的性能 :
import 操作类似 insert 操作，因此很多方法可以参见 insert 的调优步骤。
添加 compound=x 选项可使导入操作批量进行而减少了网络的通信量。
增加 COMMITCOUNT 的值已减少 LOG 的 I/O 次数。
启用缓冲区插入，对 db2uimpm 程序包使用 INSERT BUF 选项重新绑定到数据库。在 import 以前执行命令: db2 bind db2uimpm.bnd insert buf
提高导出操作 (export) 的性能：
Export 操作类似 select 操作，因此很多方法可以参见 select 的调优步骤。
将 export 操作导出的文件放在与数据和日志不同的硬盘上以减少 I/O 的竞争。
提高载入操作 (load) 的性能：load 操作中日志的写操作比 import 要少，所以 load 的性能比 import 要好很多，下面还是看看如何更好地提高 load 的性能。
在多分区环境下，db2 load 会进行并行装载，性能会大幅度提高。
添加 buffer 参数可以增加装载过程中的缓存空间，提高性能。
并发连接时的性能考虑
一般来说在连接数较少情况下，db2 的性能会比较稳定。因为这时连接的应用所产生的请求比 db2 代理池中所能产生的协调代理少，这时基本上能够满足每一个请求都能够被及时的协调代理所响应处理。 在连接集中器激活（MAX_CONNECTIONS > MAX_COORDAGENTS）的情况下，如果连接数超过了协调代理，这时连接所过来的请求就会进入队列等候协调代理服务，并发的连接数提高了，但是某些连接的性能就会显著下降。此时应当考虑激活分区间并行 (SMP) 或多分区（MPP）特性来增加 I/O 的并行性以及多个 CPU 的并行运算。
浏览1301