某中央部委大型DB2数据库性能优化实战案例

2013年7月，突然接到一个性能优化的案例，客户非常着急，在一次即将上线的性能测试中，发现并发用户一旦达到500，CPU利用率将会达到2400%（共有24个内核，平均下来每个内核的利用率为100%）。于是，在接下来的2周内，通过到客户那驻场的方式，按照《DB2设计、管理与性能优化艺术》书中有关方法和最佳实践，成功实施了性能优化工作，获得了客户和开发商的好评。为了交流技术，下面分享出来，供大家学习。

有意向需要咨询优化或者培训的个人或企业：

欢迎来电来信咨询：

联系人：王飞鹏
联系电话：13811817203
邮箱：13811817203@163.com
QQ号：16198686
QQ群：186465431     

1. 存在的问题

现场捕获了数据库快照信息和各种配置参数，并观察了load runner的运行结果，结合db2look导出的DDL语句和诊断日志等信息，仔细调研后发现该系统存在如下问题：

1.  由于服务器和存储之间使用IP网络连接，这导致存储的读写速度低下，峰值读写速度仅150M/s，这个速度小于内置盘的读写速度。

2.  金蝶APUSI应用服务器和DB2服务器在500高并发用户情况下，存在互相抢占CPU资源的情况，导致系统吞吐量无法得到最大化，导致响应时间变慢。

3.  Linux操作系统内核参数没有针对db2进行调优。

4.  从数据库层面看，参数设置和设计上有一些不合理的地方，存在隐患

l  数据库参数没有为高并发应用进行针对性调优；

l  缓冲池配置：目前仅有一个缓冲池，没有针对性设计；

l  表空间设计不合理，包括页大小，表空间类型等不符合生产系统的要求；

l  索引设计不合理，有的表，索引使用过多过滥；

l  存在排序溢出；

l  日志文件大小、日志缓冲池大小以及日志文件个数等配置不合理；

l  存在一定程度的锁等待，影响了性能。

5.  合理的测试用例设计很重要，需要对其进行必要走查。

    数据库性能调优是一个持续的过程，在本期调优中，针对上述问题，提供下述解决方案。

1.  建议服务器和存储之间使用SAN交换机连接，或者两者直连，这样可以大幅提升高并发时数据库对I/O高吞吐量的需要。

 已做。目前使用三根网线连接，以提升主机和存储之间的I/O通讯带宽。

2.  建议金蝶APUSI应用服务器和DB2服务器部署到不同的linux服务器上，以解决高并发时两者抢占CPU的弊端，从而减少业务处理的响应时间。

已做。在系统繁忙时，如下所示，金蝶APUSIC应用服务器CPU较空闲，24个内核，CPU利用率只有113.2%，主要压力在数据库服务器。针对这种情况，有两个建议：

1，应用服务器和数据库服务器的CPU内核比例可以为1:4，这是一个比较合适的比例。

2，在应用开发中，应用逻辑多缓存一些静态数据，减少对数据库的访问，以发挥应用服务器的处理能力。

Tasks: 356 total,   1 running, 355 sleeping,   0 stopped,   0 zombie

Cpu(s):  6.4%us,  0.5%sy,  0.0%ni, 92.8%id,  0.0%wa,  0.0%hi,  0.4%si,  0.0%st

Mem:  32916504k total,  2609972k used, 30306532k free,    55824k buffers

Swap: 34963448k total,        0k used, 34963448k free,   348384k cached

PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND

23785 root      20   0 3243m 1.9g  21m S 113.2  6.1  34:00.43 java

 6499 root      15   0  254m  23m  13m S 55.1  0.1  11:17.90 konsole

 6379 root      15   0 47848 9028 2440 S  0.3  0.0   3:17.26 Xvnc

    1 root      15   0 10348  688  576 S  0.0  0.0   0:03.57 init

    2 root      RT  -5     0    0    0 S  0.0  0.0   0:00.11 migration/0

    3 root      34  19     0    0    0 S  0.0  0.0   0:00.00 ksoftirqd/0

    4 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 watchdog/0

    5 root      RT  -5     0    0    0 S  0.0  0.0   0:00.28 migration/1

    6 root      34  19     0    0    0 S  0.0  0.0   0:00.03 ksoftirqd/1

    7 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 watchdog/1

    8 root      RT  -5     0    0    0 S  0.0  0.0   0:00.06 migration/2

    9 root      34  19     0    0    0 S  0.0  0.0   0:00.00 ksoftirqd/2

   10 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 watchdog/2

   11 root      RT  -5     0    0    0 S  0.0  0.0   0:00.21 migration/3

   12 root      34  19     0    0    0 S  0.0  0.0   0:00.00 ksoftirqd/3

   13 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 watchdog/3

   14 root      RT  -5     0    0    0 S  0.0  0.0   0:00.05 migration/4

   15 root      34  19     0    0    0 S  0.0  0.0   0:00.00 ksoftirqd/4

   16 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 watchdog/4

   17 root      RT  -5     0    0    0 S  0.0  0.0   0:00.14 migration/5

   18 root      34  19     0    0    0 S  0.0  0.0   0:00.00 ksoftirqd/5

   19 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 watchdog/5

   20 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 migration/6

   21 root      34  19     0    0    0 S  0.0  0.0   0:00.00 ksoftirqd/6

   22 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 watchdog/6

   23 root      RT  -5     0    0    0 S  0.0  0.0   0:00.13 migration/7

   24 root      34  19     0    0    0 S  0.0  0.0   0:00.00 ksoftirqd/7

   25 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 watchdog/7

   26 root      RT  -5     0    0    0 S  0.0  0.0   0:00.07 migration/8

   27 root      34  19     0    0    0 S  0.0  0.0   0:00.00 ksoftirqd/8

   28 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 watchdog/8

3.  对Linux操作系统内核参数进行必要调整，以更好的运行db2。

首先，修改sysctl.conf文件：

# cat /etc/sysctl.conf

--下面是内存为16GB的配置例子

--如果内存多于16GB, SEMMSL、SEMMNS、SEMOPM、msgmax和msgmnb保持不变

--但是，shmmni、shmmax、shmall、SEMMNI和msgmni的设置成比例增大，见下面加黑内容：

kernel.shmmni=4096

kernel.shmmax=17179869184

kernel.shmall=8388608

--kernel.sem=<SEMMSL> <SEMMNS> <SEMOPM> <SEMMNI>

kernel.sem=250 256000 32 4096

kernel.msgmni=16384

kernel.msgmax=65536

kernel.msgmnb=65536

其次，修改limits.conf文件：

# cat /etc/security/limits.conf:

...

db2inst1 soft nproc 2047

db2inst1 hard nproc 16384

db2inst1 soft nofile 4096

db2inst1 hard nofile 65536

db2inst1 soft stack 10240

-- -1表示最大数据大小没有限制

db2inst1 hard data -1

-- -1表示最大文件大小没有限制

db2inst1 hard fsize -1

4.  对数据库进行参数调整，设计重整：

l  调整数据库配置参数，以满足高并发应用的需要；

update db cfg using MAXLOCKS 80;

update db cfg using LOCKLIST 40960;

update db cfg using LOCKTIMEOUT 30;

update db cfg using CATALOGCACHE_SZ 102400;

update db cfg using LOGBUFSZ 10240;

update db cfg using LOGPRIMARY 50;

update db cfg using LOGSECOND 30;

update db cfg using LOGFILSIZ 10240;

update db cfg using PCKCACHESZ 102400;

update db cfg using sheapthres_shr 1638400;

update dbm cfg using SHEAPTHRES 0;

update db cfg using sortheap 1638400;

update db cfg using STMT_CONC LITERALS;

update dbm cfg using DIAGSIZE 1024;

l  重新配置缓冲池；

缓冲池的设计更加合理，共包括下面4个缓冲池：

---非LOB数据缓冲池

CREATE BUFFERPOOL "XFDBP"  SIZE 128000 PAGESIZE 32768;

---非LOB索引缓冲池

CREATE BUFFERPOOL "XFIBP"  SIZE 64000 PAGESIZE 32768;

---LOB数据缓冲池

CREATE BUFFERPOOL "XFXDBP"  SIZE 64000 PAGESIZE 32768;

---LOB索引缓冲池

CREATE BUFFERPOOL "XFXIBP"  SIZE 32000 PAGESIZE 32768;

l  重新设计表空间；

与缓冲池相匹配，包括下面4个表空间：

---非LOB数据表空间

CREATE LARGE TABLESPACE "XFDTB" IN DATABASE PARTITION GROUP IBMDEFAULTGROUP

     PAGESIZE 32768 MANAGED BY AUTOMATIC STORAGE

     AUTORESIZE YES

     INITIALSIZE 32 M

     MAXSIZE NONE

     EXTENTSIZE 32

     PREFETCHSIZE 64

     BUFFERPOOL XFDBP

     OVERHEAD 7.500000

     TRANSFERRATE 0.060000

     NO FILE SYSTEM CACHING 

     DROPPED TABLE RECOVERY ON;

---非LOB索引表空间

CREATE LARGE TABLESPACE "XFITB" IN DATABASE PARTITION GROUP IBMDEFAULTGROUP

     PAGESIZE 32768 MANAGED BY AUTOMATIC STORAGE

     AUTORESIZE YES

     INITIALSIZE 32 M

     MAXSIZE NONE

     EXTENTSIZE 32

     PREFETCHSIZE 64

     BUFFERPOOL XFIBP

     OVERHEAD 7.500000

     TRANSFERRATE 0.060000

     NO FILE SYSTEM CACHING 

     DROPPED TABLE RECOVERY ON;

---LOB数据表空间：

CREATE LARGE TABLESPACE "XFXDTB" IN DATABASE PARTITION GROUP IBMDEFAULTGROUP

     PAGESIZE 32768 MANAGED BY AUTOMATIC STORAGE

     AUTORESIZE YES

     INITIALSIZE 32 M

     MAXSIZE NONE

     EXTENTSIZE 32

     PREFETCHSIZE 64

     BUFFERPOOL XFXDBP

     OVERHEAD 7.500000

     TRANSFERRATE 0.060000

     FILE SYSTEM CACHING 

     DROPPED TABLE RECOVERY ON;

---LOB索引表空间：

CREATE LARGE TABLESPACE "XFXITB" IN DATABASE PARTITION GROUP IBMDEFAULTGROUP

     PAGESIZE 32768 MANAGED BY AUTOMATIC STORAGE

     AUTORESIZE YES

     INITIALSIZE 32 M

     MAXSIZE NONE

     EXTENTSIZE 32

     PREFETCHSIZE 64

     BUFFERPOOL XFXIBP

     OVERHEAD 7.500000

     TRANSFERRATE 0.060000

     NO FILE SYSTEM CACHING 

     DROPPED TABLE RECOVERY ON;

l  解决排序溢出；

使用共享排序，性能更好。

update db cfg using sheapthres_shr 1638400;

update dbm cfg using SHEAPTHRES 0;

update db cfg using sortheap 1638400;

l  日志文件大小、日志缓冲池大小以及日志文件个数等配置不合理；

update db cfg using LOGBUFSZ 10240;

update db cfg using LOGPRIMARY 50;

update db cfg using LOGSECOND 30;

update db cfg using LOGFILSIZ 10240;

l  存在一定程度的锁等待，影响了性能。

update db cfg using MAXLOCKS 80;

update db cfg using LOCKLIST 40960;

update db cfg using LOCKTIMEOUT 30;

5.  对一些常用的大表做了表分区：

        这些大表包括PETITION_BASIC_INFO、PETITION_ACCUSER_INFO、    PETITION_ACCUSED_INFO、PETITION_ISSUE_INFO、PETITION_CIRCULATION_STATE_INFO、PETITION_CIRCULATION_STATE_TRACE_INFO、PETITION_DEAL_INFO。这些大表以CREATE_DATE作为分区键，以年为单位作为分区，例如表PETITION_BASIC_INFO的分区键定义如下：

    CREATE TABLE "DB2INST1"."PETITION_BASIC_INFO"  (

         "OID" CHAR(20) NOT NULL ,

         "PETITION_NO" CHAR(26) ,

         "REGION_CODE" CHAR(12) ,

           ...

         "MODIFY_DATE" TIMESTAMP ,

         "FIRST_ACCUSER" VARCHAR(120) ,

         "FIRST_ACCUSED" VARCHAR(120) ,

         "REMARK" VARCHAR(2000) ) 

          IN "XFDTB" INDEX IN "XFITB" PARTITION BY RANGE(CREATE_DATE NULLS FIRST)

                 (starting minvalue,STARTING '2000-01-01' ENDING '2030-01-01' exclusive EVERY 1 year);

6.  重新梳理索引，确保索引合理使用：

     使用Toad Spotlight工具对DB2进行了监控，发现了多条最耗费CPU的SQL语句，其中下面两条是CPU杀手语句：

语句 1：

select  count(0) from petition_basic_info  left join petition_accuser_info on  petition_basic_info.oid=petition_accuser_info.petition_basic_info_oid and

petition_accuser_info.accuser_num=:L1 left join  petition_accused_info on

petition_basic_info.oid=petition_accused_info.petition_basic_info_oid  and

petition_accused_info.accused_num=:L2 ,petition_circulation_state_info where

petition_basic_info.oid=petition_circulation_state_info.petition_basic_info_oid

and petition_basic_info.repeat_Flag!=:L3 and

petition_basic_info.region_code=:L4 and petition_basic_info.petition_date >=

:L5 and (  (  PETITION_BASIC_INFO.SUB_ISSUE_REGION_CODE in (:L6 ,:L7 ,:L8 ,:L9

,:L10 ,:L11 ,:L12 ,:L13 ,:L14 ,:L15 ,:L16 ,:L17 ,:L18 ,:L19 ,:L20 ,:L21 ,:L22

,:L23 ,:L24 ,:L25 ,:L26 ,:L27 ,:L28 ,:L29 ,:L30 ,:L31 ,:L32 ,:L33 ,:L34 ,:L35

,:L36 ,:L37 ,:L38 ,:L39 ,:L40 ,:L41 ,:L42 ,:L43 ,:L44 ,:L45 ) ) );

语句 2：

select * from ( select rownumber() over() as rownumber_, row_.* from ( select

petition_basic_info.oid,petition_basic_info.petition_title,petition_basic_info.petition_no,petition_basic_info.curr_petition_no,petition_basic_info.region_code,petition_basic_info.petition_date,petition_basic_info.petition_source_code,petition_basic_info.deal_type_code,petition_basic_info.deal_type_name,

petition_basic_info.petition_type_code,petition_basic_info.petition_class_code,petition_accuser_info.accuser_work_unit,petition_accused_info.accused_work_unit

,petition_circulation_state_info.activity_no,petition_circulation_state_info.activity_name

from petition_basic_info  left join petition_accuser_info  on

petition_basic_info.oid=petition_accuser_info.petition_basic_info_oid and

petition_accuser_info.accuser_num=:L0 left join  petition_accused_info on

petition_basic_info.oid=petition_accused_info.petition_basic_info_oid  and

petition_accused_info.accused_num=:L1 ,petition_circulation_state_info where

petition_basic_info.oid=petition_circulation_state_info.petition_basic_info_oid

and petition_basic_info.repeat_Flag!=:L2 and

petition_basic_info.region_code=:L3 and petition_basic_info.petition_date >=

:L4 and (  (  PETITION_BASIC_INFO.SUB_ISSUE_REGION_CODE in (:L5 ,:L6 ,:L7 ,:L8

,:L9 ,:L10 ,:L11 ,:L12 ,:L13 ,:L14 ,:L15 ,:L16 ,:L17 ,:L18 ,:L19 ,:L20 ,:L21

,:L22 ,:L23 ,:L24 ,:L25 ,:L26 ,:L27 ,:L28 ,:L29 ,:L30 ,:L31 ,:L32 ,:L33 ,:L34

,:L35 ,:L36 ,:L37 ,:L38 ,:L39 ,:L40 ,:L41 ,:L42 ,:L43 ,:L44 ) ) ) order by

petition_basic_info.petition_date DESC , petition_basic_info.curr_petition_no

DESC ) as row_ ) as temp_  where rownumber_ > :L45 and rownumber_ <= :L46

仔细分析了它们的查询计划，为这两条SQL语句创建了下面的索引：

CREATE INDEX "DB2INST1"."IDX1308160645220" ON "DB2INST1"."PETITION_BASIC_INFO"

   ("REAL_NAME_REPORT" ASC, "PETITION_DATE" ASC, "RETURN_FLAG"

   ASC, "PETITION_NO" ASC, "REGION_CODE" ASC, "SUB_ISSUE_REGION_CODE"

   ASC) NOT PARTITIONED  ALLOW REVERSE SCANS COLLECT SAMPLED DETAILED STATISTICS;

   COMMIT WORK ;

CREATE INDEX "DB2INST1"."IDX1308160645160" ON "DB2INST1"."PETITION_BASIC_INFO"

   ("PETITION_NO" ASC, "REGION_CODE" DESC) NOT PARTITIONED

    ALLOW REVERSE SCANS COLLECT SAMPLED DETAILED STATISTICS;

   COMMIT WORK ;

CREATE INDEX "DB2INST1"."IDX1308160301460" ON "DB2INST1"."PETITION_BASIC_INFO"

   ("REGION_CODE" ASC, "PETITION_DATE" DESC, "CURR_PETITION_NO"

   DESC, "PETITION_CLASS_CODE" ASC, "PETITION_TYPE_CODE"

   ASC, "DEAL_TYPE_NAME" ASC, "DEAL_TYPE_CODE" ASC, "PETITION_SOURCE_CODE"

   ASC, "PETITION_NO" ASC, "PETITION_TITLE" ASC, "SUB_ISSUE_REGION_CODE"

   ASC, "REPEAT_FLAG" ASC, "OID" ASC) NOT PARTITIONED

    ALLOW REVERSE SCANS COLLECT SAMPLED DETAILED STATISTICS;

   COMMIT WORK ; 

CREATE INDEX "DB2INST1"."IDXPETITION_BASIC_INFO001" ON "DB2INST1"."PETITION_BASIC_INFO"

       ("PETITION_DATE" ASC,

        "REGION_CODE" ASC,

        "PETITION_TYPE_CODE" ASC,

        "REAL_NAME_REPORT" ASC)

       NOT PARTITIONED IN "XFITB"     

       COMPRESS NO ALLOW REVERSE SCANS;

CREATE UNIQUE INDEX "DB2INST1"."IDXPETITION_CIRCULATION_STATE_INFO001" ON "DB2INST1"."PETITION_CIRCULATION_STATE_INFO"

       ("OID" ASC,

        "PETITION_BASIC_INFO_OID" ASC)

       INCLUDE ("ACTIVITY_NO" ,

        "ACTIVITY_NAME" )

       NOT PARTITIONED IN "XFITB";

CREATE INDEX "DB2INST1"."IDX1308160301360" ON "DB2INST1"."PETITION_CIRCULATION_STATE_INFO"

   ("PETITION_BASIC_INFO_OID" ASC, "ACTIVITY_NAME" DESC)

   NOT PARTITIONED  ALLOW REVERSE SCANS COLLECT SAMPLED DETAILED STATISTICS;

   COMMIT WORK ;

CREATE UNIQUE INDEX "DB2INST1"."IDXPETITION_ACCUSER_INFO001" ON "DB2INST1"."PETITION_ACCUSER_INFO"

       ("OID" ASC,

        "ACCUSER_NUM" ASC,

        "PETITION_BASIC_INFO_OID" ASC)

       INCLUDE ("ACCUSER_WORK_UNIT" )

       NOT PARTITIONED IN "XFITB" 

       COMPRESS NO ALLOW REVERSE SCANS;

CREATE INDEX "DB2INST1"."IDXPETITION_ACCUSER_INFO002" ON "DB2INST1"."PETITION_ACCUSER_INFO"

       ("ACCUSER_NUM" ASC,

        "PETITION_BASIC_INFO_OID" ASC)

       NOT PARTITIONED IN "XFITB"     

       COMPRESS NO ALLOW REVERSE SCANS;

CREATE INDEX "DB2INST1"."IDX1308160304000" ON "DB2INST1"."PETITION_ACCUSER_INFO"

   ("PETITION_BASIC_INFO_OID" ASC, "ACCUSER_NUM" ASC,

   "ACCUSER_WORK_UNIT" ASC) NOT PARTITIONED  ALLOW REVERSE

   SCANS COLLECT SAMPLED DETAILED STATISTICS;

   COMMIT WORK ;

CREATE INDEX "DB2INST1"."IDXPETITION_ACCUSER_INFO003" ON "DB2INST1"."PETITION_ACCUSER_INFO"

       ("PETITION_BASIC_INFO_OID" ASC)

       NOT PARTITIONED IN "XFITB"     

       COMPRESS NO ALLOW REVERSE SCANS;

CREATE UNIQUE INDEX "DB2INST1"."IDXPETITION_ACCUSED_INFO001" ON "DB2INST1"."PETITION_ACCUSED_INFO"

       ("OID" ASC,

        "ACCUSED_NUM" ASC,

        "PETITION_BASIC_INFO_OID" ASC)

       INCLUDE ("ACCUSED_WORK_UNIT" )

       NOT PARTITIONED IN "XFITB"     

       COMPRESS NO ALLOW REVERSE SCANS;

CREATE INDEX "DB2INST1"."IDXPETITION_ACCUSED_INFO002" ON "DB2INST1"."PETITION_ACCUSED_INFO"

       ("ACCUSED_NUM" ASC,

        "PETITION_BASIC_INFO_OID" ASC)

       NOT PARTITIONED IN "XFITB"     

       COMPRESS NO ALLOW REVERSE SCANS;

CREATE INDEX "DB2INST1"."IDXPETITION_ACCUSED_INFO003" ON "DB2INST1"."PETITION_ACCUSED_INFO"

       ("PETITION_BASIC_INFO_OID" ASC)

       NOT PARTITIONED IN "XFITB"     

       COMPRESS NO ALLOW REVERSE SCANS;

CREATE INDEX "DB2INST1"."IDX1308160304150" ON "DB2INST1"."PETITION_ACCUSED_INFO"

   ("PETITION_BASIC_INFO_OID" ASC, "ACCUSED_NUM" ASC,

   "ACCUSED_WORK_UNIT" ASC) NOT PARTITIONED  ALLOW REVERSE

   SCANS COLLECT SAMPLED DETAILED STATISTICS;

   COMMIT WORK ;

下表是这两条语句优化前后执行成本的对比，可以发现执行成本下降了7倍~15倍：

7.  对测试用例进行走查，确保满足性能测试的需要。

    在原来运行测试用例的时候，发现有很多错误，这次调优中，修改了测试用例的代码，消除了这些错误。