DB2培训讲义-DB2性能优化入门.ppt

1、DB2培训讲义性能优化入门第1页，共31页。有关的概念第2页，共31页。DB2 性能优化的三个方面n 内存n CPUn I/O第3页，共31页。内存因素n 在内存方面，主要是考虑缓冲池(BUFFERPOOL)的使用。缓冲池是一片用来缓冲从磁盘上读取的数据和索引的内存区域，这些数据和索引信息在缓冲池中进行运算后最终还要写回磁盘。缓冲池的页面大小有四种(4K,8K,16K,32K)，分别对应四种不同页面大小的表空间。缓冲池的大小决定了能够从磁盘上缓冲数据的容量大小。当然缓冲池也不是越大越好，缓冲池过大可能会导致连接数据库的时间过长，因为在连接数据库时要为数据库的缓冲池分配内存空间。可以通过计算缓冲

2、池的命中率来评估缓冲池的使用效率：缓冲池命中率=(1-(数据物理读+索引物理读)/(数据逻辑读+索引逻辑读)*100%，缓冲池命中率越大说明缓冲池的使用效率高。缓冲池命中率太小说明缓冲池太小应当调大。其中的数据物理读，索引物理读以及数据逻辑读和索引逻辑读都可以从缓冲池的快照中获取。第4页，共31页。内存因素n 在内存方面要考虑的另外几个重要因素是排序堆(SORTHEAP)，锁列表(LOCKLIST),日志缓冲区(LOGBUFSZ)。排序堆在查询结果带有排序选项而没有相关索引对应时将会被使用，排序堆太小会产生排序溢出(Overflowed),那些在排序堆中装不下的排序数据将会溢出到一个临时表中，

3、这会使性能下降。与 SORTHEAP 参数相关的是 SHEAPTHRES_SHR 和 SHEAPTHRES，SHEAPTHRES_SHR 限制了一个数据库中共享排序的最大内存，SHEAPTHRES 限制了私有排序的最大内存。LOCKLIST 指的是一个数据库中用来存放锁的内存空间，当这个参数设得过小会导致在锁用光这部分资源后导致锁升级（即多个行锁转化为一个表锁来释放出更多的资源）。这会导致系统的并行性下降，很多应用连接出现挂起，使得系统的性能衰退。第5页，共31页。内存因素n 所以尽可能调大 LOCKLIST 参数，这里需要指出：LOCKLIST 指的并不是锁的个数，而是以数据库页为单位的一片

4、内存区域（在 32 位系统中每个锁需要 96 个字节，在 64 位系统中每个锁需要 128 个字节）。与 LOCKLIST 参数对应的是 MAXLOCKS 参数，MAXLOCKS 定义的是一个百分数，它指定了一个应用程序所能占用的最大的锁空间占 LOCKLIST 的比例。日志缓冲区(LOGBUFSZ)指的是日志在写到磁盘以前用于缓冲的一片内存空间，这样可以减少写日志带来的过多的 I/O。第6页，共31页。内存因素n 从版本 9 以后 DB2 推出了一个新特性自调节内存管理器(STMM:Self Tuning Memory Manager),这个特性使得很多内存参数如前面所述的 SORTHEAP

5、，LOCKLIST，LOGBUFSZ 等进行自动调节，当数据库参数 SELF_TUNING_MEM 设为 ON,这些参数设为 AUTOMATIC 即可以进行自动调整。这样可以节省很多人工调整的时间。第7页，共31页。CPU 因素n 关于 CPU 因素首先是考虑 DB2 优化器(OPTIMIZER)对访问计划(ACCESS PLAN)的分析与优化。一般来说，一条 SQL 在执行时首先会被解析，然后进行语义分析，进而重写 SQL,优化器会对重写过的 SQL 进行基于成本的分析最终选择最有效的访问计划。最终生成可执行代码（执行计划）来执行这条语句。查询访问计划的工具有很多，既有图形化工具 Visua

6、l Explain，也有命令 db2exfmt 来格式化解释表(Explain tables)中的数据生成 ACCESS PLAN。还有命令 db2expln 查询 ACCESS PLAN。第8页，共31页。CPU 因素n 在 DB2 里的优化级别分为九级，缺省是第五级，级别越高优化器分析得程度越深。这个级别有数据库配置参数 DFT_QUERYOPT 决定。并不是级别设得越高性能越好，因为对于一些较为简单的 SQL 语句，如果优化级别过高那么花在优化 SQL 上的时间就会过长，而执行时间相对来说很短，有些得不偿失。在选择访问计划时，索引扫描的效率往往会比表扫描要高，所以索引的优化也是值得注意的

7、。正确的建立索引会使查询性能大幅度的提高。第9页，共31页。CPU 因素n 在 DB2 中连接(JOIN)分为三种：嵌套循环连接(nest-loop join),合并连接(merge-join),散列表连接(hash-join)。一般来说效率最低的是嵌套循环连接，这种连接采用的是笛卡儿集，进行多次循环遍历得到结果。而合并连接和散列表连接只进行一次循环遍历，相对来说效率较高。其中散列表连接可以采用多个等式做为条件而合并连接只能采用单个等式作为条件。但是在有索引扫描的情况下嵌套循环连接效率则更高。当优化级别等于零时，连接只能采用嵌套循环连接，当优化级别大于等于 1 时，连接可以采用合并连接。当优化

8、级别大于 5 时连接可以采用散列表连接。散列表连接要求 SORTHEAP 比较大，因为要为生成散列表准备空间。第10页，共31页。CPU 因素n 在考虑 CPU 因素时还要考虑 CPUSPEED 这个参数，这个参数标明了 CPU 的运行速度，它会帮助优化器评估最好的访问计划。一般来说这个参数设为-1，优化器将自动计算 CPU 的速度。另外运用多分区的特性可以把一个数据库分布到多台机器上，这样可以充分利用多台机器的 CPU 的资源对应用程序的事务进行并行处理，从而提高数据库的性能。第11页，共31页。I/O 因素n 关于 I/O 因素要考虑以下几个方面：首先是磁盘的 I/O,为了能够最大化磁盘的

9、 I/O 可以把数据，索引以及日志分别放在不同的硬盘上。因为在一个事务中数据和索引可能需要同时访问，而在事务提交时，数据和日志要同时写入磁盘，而且有可能索引也要同步维护，所以将它们放在不同的硬盘上可以使它们的读写并行运行，从而不致使磁盘成为瓶颈。同时选择数据库管理表空间(DMS)要比系统管理表空间(SMS)性能要好，因为读写 SMS 需要经过操作系统的 cache 再到缓冲池，而可以采用裸设备的 DMS 则不需要。但是 DMS 相对 SMS 来说维护起来较麻烦。第12页，共31页。I/O 因素n 其次要考虑的是日志文件的大小，当数据库在写事务日志时当一个日志文件写满后会转向另外一个日志文件，这

10、种日志文件的切换会造成操作系统上的开销。所以应当尽量将日志文件大小（LOGFILSIZ）设得大一些，这样可以减少日志文件切换的次数。但是日志文件过大难免会造成一些空间的浪费。第13页，共31页。I/O 因素n 同时也要考虑到隔离级别的因素，在 DB2 中隔离级别分成 4 级：可重复的读，读稳定性，游标稳定性和未提交的读。这四种级别逐个降低。越高的隔离级别越能保证数据完整性，但却会降低并发性，所以应当综合权衡后做出决定。隔离级别可以通过如下命令来改变：n CHANGE ISOLATION TO=CS|RR|RS|URn 在连接方面还要考虑到代理和连接的关系，这也会影响到数据库的并发性。n 最后要

11、考虑的还是关于多分区的特性。在多分区数据库中，一个请求首先传到协调分区，然后由协调分区将请求细分成多个部分发送到其他分区，这样数据可以在各个分区进行并行读写，实现 I/O 最大化。第14页，共31页。性能优化工具n 在 DB2 中有很多和性能优化相关的工具和命令，下面简单地介绍几种：SNAPSHOT DB2PD RUNSTATS REORG DB2DART DB2SUPPORT第15页，共31页。性能优化工具n SNAPSHOT:这是 DB2 获取数据库信息快照的一种方法。它能够获取在数据库中关于缓冲池，锁，排序以及 SQL 等等信息。DBA 可以通过获取这些信息来对数据库中的各组件进行评估来

12、分析问题的瓶颈。n DB2PD:这个命令是用来分析数据库的当前状态，它带有很多参数。可以用来分析应用程序，代理，内存块，缓冲池，日志及锁状态等信息。n RUNSTATS:这个命令是用来收集数据库中数据的最新统计信息，并更新到系统表中。更新统计信息将会促使优化器选择更加符合实际的高效的访问计划，从而提高工作效率。n REORG:这个命令用来重新整理数据库中数据和索引的碎片，使其在物理上可以得以按一定规则排列，这样可以加快检索的速度。第16页，共31页。性能优化工具n DB2DART:这个命令是一个数据库的分析和报告工具，它用来检查表空间，索引以及数据库结构的正确性，分析在性能问题上的一些原因。n

13、 DB2SUPPORT:这个命令用来收集 DB2 和操作系统的所有相关信息并生成一个压缩文件，可传送给优化人员进行分析。第17页，共31页。DB2 性能问题分类与分析思路 第18页，共31页。DML性能问题：查询优化n DML(Data Manipulation Language)包括了查询，增加，删除和更新纪录等操作。首先看一下查询的性能问题，在查询一张表或多张表的联合查询时有时反应时间会比较长，这使得用户难以忍受。针对这种问题，可以通过下述方法来分析：n 在查询的连接或条件子句中的相关字段是否加了索引。n 察看缓冲池的大小，缓冲池太小会造成很多数据不能读到缓冲池而直接从硬盘上读取，造成很大

14、的瓶颈。另一方面关于缓冲池预取的设置，一般能将预取大小(PREFETCHSIZE)设定为区段大小与容器个数的积，这样可以最大利用到预取的并行性。n 在查询中涉及到 order by 字句时，如果排序的字段没有设置索引那么排序将会用到内存中的排序堆(sortheap)。如果排序堆过小会造成排序溢出到硬盘上(Overflowed)造成性能衰退。第19页，共31页。DML性能问题：查询优化n 同时还要考虑到 RUNSTATS/REORG 因素。RUNSTATS 命令可以更新表中的统计信息。当表中的数据经过频繁的增删改后其相应的统计信息会发生变化，而优化器选择执行计划的时候是根据这种统计信息来计算的，

15、所以运行 RUNSTATS 此时显得尤为重要。REORG 可以整理数据存储的物理结构，也能减少数据扫描的时间，提高查询的性能。n 从存储方面应当注意的是选取裸设备的 DMS 要比 SMS 性能要好，因为它少了一层文件系统的缓冲而直接访问缓冲池。n 学会使用 optimize for n rows 子句，它可以提高前面 n 条记录的显示速度。这样可以使用户能够先快速查看这 n 条记录，然后再看其他纪录。减少了用户的等待时间。第20页，共31页。DML性能问题：查询优化n 针对复杂查询时可以将数据库配置参数 DFT_QUERYOPT(缺省查询优化类)的值设得高一些（7 或 9），针对简单查询可以将

16、它设得低一些(3 或 5)，因为设置越高优化器所作的分析就越深入，耗费在生成计划上的时间就越多。n 针对 C/S 结构的查询可以将查询语句写在服务器端生成存储过程来减少数据的网络传输以及客户端的压力。而经过编译的存储过程执行得更加高效。第21页，共31页。DML性能问题：查询优化n 还要考虑到隔离级别与锁的因素，隔离级别越高越能保证数据的完整性，但同时会减弱并发性。这一点需要权衡需求而定。n 网络因素也不可忽视，将数据库服务器参数 RQRIOBLK 设为 65534 可以相应地提高网络吞吐量。（缺省值 32767）n 最后需要考虑的是数据库的结构，在某些情况下，在某些表中增加一些冗余字段虽然牺

17、牲了一些空间和维护成本，但是在查询时可以减少很多连接操作，这样可以大大提高查询性能。就是用空间换取时间。第22页，共31页。DML性能问题：增删改优化n 首先是索引因素，在做增删改时数据库会对表中的索引做相应的修改。这会消耗一定的资源，所以在保证数据完整性的前提下可以先将索引删除，待到增删改结束后再重建这些索引。这也会节省一些时间。将索引和数据放在不同的硬盘上也可以增加写操作的并行性。n 其次要考虑日志因素，在数据写操作的同时，数据库系统也在维护着事务日志，所以应尽量减少日志维护的代价。将 auto commit 设为 false，可以减少提交的次数（同时也减少了写日志的次数）。增大 LOGB

18、UFSZ,LOGFILSZ 可以减少刷新日志的次数以及日志文件切换的次数。或者将表的属性改为”ACTIVATE NOT LOGGED INITIALLY”,这样可以屏蔽表的日志操作，以提高写操作的性能，但是失去事务日志的表的数据很难修复，这一点需要权衡。第23页，共31页。DML性能问题：增删改优化n 将日志和数据分别放在不同的硬盘上也可以增加写操作的并行性。n 在插入记录时采用 APPEND MODE 可以消除 DB2 寻找表中间的空余空间的时间而直接插到表尾，从而提高插入的性能。n 还要考虑到约束和触发器的影响，在写操作时应当尽量避免表中有约束和触发器。在保证数据完整性的前提下可在频繁大批

19、量写操作时先将约束或触发器去除，完毕后重建。n 和查询一样，写操作同样要考虑到隔离级别和锁的因素。第24页，共31页。DB2 实用程序的性能优化 第25页，共31页。如何提高备份操作的性能？n 提高数据库配置参数 UTIL_HEAP_SZ 的大小，这个内存区域用来为备份和恢复操作提供缓冲。n 减少整库备份，多采用表空间备份需要的表空间。n 减少完全备份，多采用增量备份或 DELTA 备份。n 增加备份命令中的 PARALLELISM 参数来增加备份的并行性（增加线程或进程）。n 增加备份命令中的 BUFFER 参数值。n 增加备份的目标目录，最好能将多个目录放在不同的硬盘上，这样可以增加备份的

20、并行程度。第26页，共31页。如何提高恢复操作的性能？n 和备份操作一样，需要增大数据库配置参数 UTIL_HEAP_SZ 的大小。n 增加恢复命令中的 BUFFERS 参数值。n 增加恢复命令中的 PARALLELISM 参数来增加备份的并行性（增加线程或进程）。n 容器分布于不同的硬盘上也可以使恢复操作加快（提高并行性）。n 采用 SMP 模式来激活多代理来增加恢复操作的并行性。第27页，共31页。提高导入操作（import）的性能？n import 操作类似 insert 操作，因此很多方法可以参见 insert 的调优步骤。n 添加 compound=x 选项可使导入操作批量进行而减少

21、了网络的通信量。n 增加 COMMITCOUNT 的值已减少 LOG 的 I/O 次数。n 启用缓冲区插入，对 db2uimpm 程序包使用 INSERT BUF 选项重新绑定到数据库。在 import 以前执行命令:db2 bind db2uimpm.bnd insert buf 第28页，共31页。提高导出操作(export)的性能？n Export 操作类似 select 操作，因此很多方法可以参见 select 的调优步骤。n 将 export 操作导出的文件放在与数据和日志不同的硬盘上以减少 I/O 的竞争。n 提高载入操作(load)的性能：load 操作中日志的写操作比 import 要少，所以 load 的性能比 import 要好很多，下面还是看看如何更好地提高 load 的性能。第29页，共31页。Thanks第30页，共31页。演讲完毕，谢谢观看！第31页，共31页。