DB2并发连接时的性能考虑

　　在上一篇文章中我们看了DB2实用程序的性能优化，这次我们来关注一下DB2并发连接时要做的性能考虑  。

　　一般来说在连接数较少情况下，db2 的性能会比较稳定  。因为这时连接的应用所产生的请求比 db2 代理池中所能产生的协调代理少，这时基本上能够满足每一个请求都能够被及时的协调代理所响应处理  。 在连接集中器激活(MAX_CONNECTIONS > MAX_COORDAGENTS)的情况下，如果连接数超过了协调代理，这时连接所过来的请求就会进入队列等候协调代理服务，并发的连接数提高了，但是某些连接的性能就会显著下降  。此时应当考虑激活分区间并行 (SMP) 或多分区(MPP)特性来增加 I/O 的并行性以及多个 CPU 的并行运算  。

　　案例分析

　　查询优化案例

　　接下来这里从一个试验来看一下 DML 操作过程中优化的详细步骤和具体数据  。首先看一个查询优化的例子，下面是试验中的建表语句：

CREATE TABLE MCLAIM.T1_DMS (  
C11 VARCHAR (10) NOT NULL ,  
C12 VARCHAR (15) NOT NULL ,  
C13 VARCHAR (20) NOT NULL ,  
CONSTRAINT C11_PK PRIMARY KEY ( C11) ) IN DMS_Space;  
CREATE TABLE MCLAIM.T2_DMS (  
C21 VARCHAR (15) NOT NULL ,  
C22 VARCHAR (25) NOT NULL ,  
C23 VARCHAR (30) NOT NULL ,  
CONSTRAINT C21_PK PRIMARY KEY ( C21) ) IN DMS_Space;  
CREATE TABLE MCLAIM.T3_DMS (  
C31 VARCHAR (10) NOT NULL ,  
C32 VARCHAR (25) NOT NULL ,  
C33 VARCHAR (35) NOT NULL ,  
CONSTRAINT C31_PK PRIMARY KEY ( C31) ) IN DMS_Space; 

　　最初的环境没有优化，表空间类型 SMS 表空间，查询的表中没有索引，sortheap 过小等等  。在这种情况下执行下列查询语句：

select C12 from TESTOPT.T1_SMS,%SCHEMA%.T2_SMS,%SCHEMA%.T3_SMS  
where substr(C12,1,10)=substr(C21,1,10) and C22=C32  
order by C12 asc 

　　在没有优化的情况下得到的总的执行时间是 653 秒，而经过优化后得到总的执行时间是大概是 15 秒左右  。在优化中采用了如下优化步骤：

　　选择 DMS 表空间  。

　　添加索引：

CREATE UNIQUE INDEX INDEX_C12 on T1_DMS (C12 ASC);  
CREATE UNIQUE INDEX INDEX_C22 on T2_DMS (C22 ASC);  
CREATE UNIQUE INDEX INDEX_C32 on T2 _DMS (C32 ASC); 

　　增大 sortheap 的大小

　　执行 runstats

　　选择适当的优化级别

　　改进表结构，增加冗余字段  。以空间换时间：

ALTER TABLE T1 ADD C12_Red VARCHAR(10);  
ALTER TABLE T2 ADD C21_Red VARCHAR(10);  
UPDATE T1 SET C12_Red=SUBSTR(C12,1,10);  
UPDATE T2 SET C21_Red=SUBSTR(C21,1,10); 

　　查询语句变成：

select C12 from TESTOPT.T1_DMS, TESTOPT.T2_DMS, TESTOPT.T3_DMS  
where C12_Red=C21_Red and C22=C32 order by C12 asc 

　　图 1. 查询操作优化示意图

　　从图中可以看出选择好的表空间类型 ( 数据库管理表空间 ) 和添加索引会对性能有很大的改善作用  。而添加冗余字段对性能的改进作用最大  。当然这会涉及表结构的变化，是需要在数据库设计阶段考虑的因素  。同时代价是增加磁盘的占用空间  。

　　写入操作优化

　　接下来是一个写操作的例子(插入)  。下面是试验的脚本：

CONNECT TO FFTEST;  
CREATE SCHEMA TESTOPT;  
DROP TABLE TESTOPT.T3;  
CREATE TABLE TESTOPT.T3 (  
C31 VARCHAR (10) NOT NULL ,  
C32 VARCHAR (15) NOT NULL ,  
CONSTRAINT C31_A CHECK ( C31 LIKE A% or C31 LIKE a%));  
CREATE INDEX TESTOPT.INDEX_C31 on TESTOPT.T3 (C31 ASC);  
ALTER TABLE TESTOPT.T3 ADD CONSTRAINT C31_A CHECK (substr(C31,1,1)= ’ a ’  
or substr(C31,1,1)= ’ A ’ )  
ALTER TABLE TESTOPT.T3 APPEND OFF;  
CONNECT RESET; 

　　最初的表没有优化，含有索引，约束等因素，插入 4 万条记录大约花了 68 秒钟，而最终优化后插入 4 万条记录只需 6 秒钟  。如下是优化步骤：

1. 去除索引  。
2. 去除约束  。
3. 在 insert 语句中包括多行  。
4. 采用 Append 模式
5. 屏蔽表的日志操作  。
6. 采用并行写操作  。
7. 采用严格的隔离级别  。

　　图 2. 插入操作优化示意图

　　从图中可以看出减少索引和约束可以大幅度提高插入性能，而将多条插入语句合并成一行产生的效果更加明显  。

　　性能调优注意事项

　　为了得到高性能将缓冲池调得过大，导致数据库连不上  。这对没有经验的用户来说可能是个灾难，这意味着数据库可能要重建  。最初我们曾经犯过这样的错误  。现在可以通过调节 DB2 注册参数 DB2_OVERRIDE_BPF 来设置缓冲池的大小，从而能够再次连接数据库  。当然最好将 STMM 激活，使内存能够自动调整  。

　　往往忽视 runstats 和 reorg 的作用，我们发现不止一个的性能问题，都是由于优化器选择了错误的 access plan 导致系统整体性能下降  。而对外显示的则不光是 SQL 执行慢，同时也能会表现出 I/O 瓶颈或系统响应时间长  。这往往会误导我们去分析其他地方  。但究其根源，很多时间是由于优化器的错误  。这些问题往往在重新执行 runstats 和 reorg 之后就解决了  。所以这两个命令也要特别注意  。

　　在进行数据加载的时候往往忽略了索引因素，导致性能加载性能下降  。我们遇到过这样的一个例子，一张表导入 1000 条记录花了 5 分钟，检查了很多配置找不到原因，最后发现这张表上有 1 个主键，还有 4 个外键  。将他们删除后重新导入只花了几秒钟  。所以在进行 load 或者是 insert 的时候尽量将主外键或相关索引删除，加载完成后重建相关索引  。主外键尽量通过加载程序来保证它的数据完整性  。这一点往往会被忽略，所以在加载数据前先检查一下所有表的索引状态及引用关系  。

　　在修改 db2 参数的时候，一次最好修改一个参数，然后看看效果，在调节其他参数  。否则一次多个参数，调好了也没弄清楚是哪个参数起的作用  。下次还得全部来一遍  。还要注意，并非所有参数都是越大越好，有时可能会适得其反  。

　　注意索引的试用，优化好的索引对查询语句性能的提高往往会产生数十倍的性能改进  。所以，调优前可以先察看一下相关语句的索引利用情况  。这可以通过察看 SQL 语句和执行计划，看一下已有索引是否被利用起来了或是否需要建立新的索引  。这往往比 DB2 系统调优更重要  。但切记考虑插入操作，索引也会降低插入的性能  。这一点要综合考虑  。

　　由于 XML 数据可以跨页存储，在设计 XML 数据库时要尽可能的使用较大的数据页，这样可以避免 XML 数据跨页查询，以提高查询性能  。

　　采用表分区：有这样一个例子：客户有一张表的数据量非常大，每天都会产生大约 30 万条记录，同时每天都会删除五天前的记录，所以此表大概有 150 万条记录，现在客户在每天的第一次查询时要重新对表进行索引(因为晚上会产生很多数据，所以新增加的数据都没有建索引)，导致响应非常慢!对于这种问题，后来采用了表分区，用 6 个分区表来分别装载原来 6 天的数据  。所以查询和插入都只涉及一张表，所以响应速度得到大幅度提高  。

　　了解 CHNGPGS_THRESH 参数，是缓冲池写日志的阀值  。有一个例子，在创建索引时比较慢，经过检查发现 CHNGPGS_THRESH 参数过大，造成每次写日志的时候数据量过大，造成 I/O 瓶颈，适当减小这个参数值，可以增加写日志的次数，但数减少每次写日志的数据量，这对于大缓冲池里的大表上创建索引时很有效的  。

　　在导入数据时尽量采用 load, 少用 import, 我们做过统计，用 import 花费 10 分钟的数据，用 load 大概只需要 1 分钟，这大大提高了工作效率  。

　　注意 db2diag.log 的大小，当这个文件很大的时候，数据库的所有操作，包括停启 db2 都会特别的慢，有时甚至挂起  。所以要经常看看这个文件的大小，过大时最好删掉，重启 db2   。当然 DIAGLEVEL 不要设得太高，除非为了诊断某个问题获得更多信息，一般默认的 3 足够了  。