资深DB2咨询顾问 徐明伟

1.     概述

当前，国内很多通信运营商、金融行业、电力行业等客户采用了DB2 多分区数据库，构建他们的数据仓库和经营分析系统。与其它关系数据库相比，DB2多分区由于其良好的可扩展性和并发性能，在海量数据分析和处理方面具有突出的优势。

       在多年的DB2咨询和服务经历中，笔者有幸参与了一些大型DB2数据仓库的部署架构设计、实施，以及性能优化和问题诊断处理工作。其中印象比较深刻的是每天晚上的数据加载效率问题，当数据量较大时，加载效率几乎是每个客户都头疼的问题。

       由于很多客户使用Load命令作为数据加载的方法，本文将结合笔者近期处理的一个实际客户场景，向大家介绍多分区数据库Load的性能优化方法，以及2个Load问题的处理过程，希望对各位朋友有所帮助。

2.     环境描述

图1是该客户数据仓库的部署结构图，规划的数据量大概为200T。基本信息如下：

（1）该DB2数据仓库由2台IBM Power7 780服务器组成，每台机器划了4个LPAR，A机包含A0-A3 4个LPAR，B机包含B0-B3 4个LPAR。

（2）存储采用EMC高端存储设备DMX4。

（3）操作系统为AIX6.1。

（4）DB2版本为9.7 fp5。

（5）DB2逻辑分区为

A0: 0节点

A1: 1-8

A2: 9-16

A3: 17-24

B0: 901-906

B1:  25-32

B2: 33-40

B3: 41-48

A0作为catalog节点和协调节点；A1-A3和B1-B3 作为数据LPAR，每个LPAR包含8个DB2逻辑节点；B0作为数据分发LPAR，不存放数据，只用作Load数据分发。为什么将B0作为单独的数据分发节点呢？下文会给出答案。

（6）SQLLIB实例目录采用nfs共享，nfs服务器位于A0节点。

（7）对于该仓库系统，每天晚上都要通过load命令从其他交易系统追加大量的增量数据，并且要求在指定的时间窗口内完成新增数据的追加，当前的Load并发数是10个。

3.     多分区Load过程及优化

DB2多分区Load命令主要分为2个阶段：Setup和Load阶段，Setup阶段主要分为Pre-partition和Partition。以下是各阶段的主要任务：

A.       Pre-partition阶段处理数据输入文件，并从系统视图获取分区、分区组和分区键等信息；

B.       Partition阶段基于分区键进行分区，并将分区数据输出到Load阶段；

C.       在Load阶段，每个分区读取Partition阶段产生的输出，并将其加载到对应的分区中。

对于数据仓库系统来说，一般采用T+1的方式，这就要求每天产生的增量数据必须在第二天早上前加载到仓库中，换句话说，数据加载必须在指定的时间窗口前完成，因此Load加载的性能直接影响到数据的后续处理。那么如何提高Load的性能，也成为很多客户迫切需要解决的问题。

根据经验，影响Load的性能因素有：

(1)     良好的表空间存储规划设计，存储规划不好，会大大降低Load处理的速度；

(2)     物理节点间网络带宽影响。因为分区间FCM通讯是通过网络进行数据传输，如果网络带宽受限，将成为瓶颈。一个最佳实践是将几块网卡形成EtherChannel绑定为一个网卡，作为FCM通讯的通道；

(3)     在Load命令中，指定PARTITIONING_DBPARTNUMS选项。缺省情况下，Load 分区过程发生在0节点，在超过1台机器的分区环境中，可考虑指定多个节点作为分区节点，根据测试结果，这种方案会有很大的性能提升。如：

db2 "load from /data/t1.del of del messages 2.msginsert into test.t1 PARTITIONED DB CONFIGPARTITIONING_DBPARTNUMS (901,902,903,904,905,906) "

     现在大家应该可以理解，为什么在系统部署结构中指定B0的6个节点作为partition节点了。

(4)  Load命令选项。

Modified byANYORDER：此文件修饰符可以使用输入文件中预设好的排序结果来提高性能。缺省情况下，load会保留源数据的记录顺序，在SMP环境下，就需要在各个并行的任务之间保持同步以保留记录顺序，而ANYORDER选项会避免Load的各个任务之间保留记录顺序，因此可以提高性能。此文件修饰符可以用于各种输入的文件格式。

Modified byFASTPARSE：此文件修饰符通过降低对输入数据的检查来提升性能，如果输入的数据与目标表的结构、类型上并无差别，那么可以通过设置此选项来提升性能。此文件修饰符只能用于 ASC 或 DEL 格式的文件。

4.     Load Hang案例分析

问题现象描述：

该系统在刚部署后的一个月内，Load速度性能非常好。但随后的一段时间内，系统每运行几天时间，速度就会慢下来，随后Load可能Hang住，而必须重启DB2。

问题分析诊断：

 对于多分区系统的分析思路，最主要是把问题不断细化到某一个分区，进而对这个分区做进一步分析处理。那么，对于Loadhang问题，我们要看的是出现该问题时，各个节点的Load状态是否正常，这时自然想到 db2 list utilities show detail。但该命令发起后，也Hang住，没有任何结果返回。

这时，我们在其中一个数据节点使用另外一个监控任务的工具，db2pd –utilities，因为db2pd不对DB2内部引擎加锁。果然，发现了Hang住的Load命令的执行进度仍然为SETUP。前面我们已经介绍，SETUP是Load的前一个阶段，说明还没到Load阶段，DB2就挂起了。

$ db2pd -utilities

Database Partition 1 -- Active -- Up 2 days 20:21:34 -- Date 12/25/2011 17:26:12

Utilities:

Address            ID         Type                   State      Invoker   Priority   StartTime           DBName   NumPhases CurPhase   Description        

0x078000000129ED404768       LOAD                   0          0          0          Sun Dec 25 15:03:37 DW  1          1           [LOADID:30955.2011-12-25-15.03.35.837677.901 (11;4688)] [*N901.dwinst.111225070324]OFFLINE LOAD DEL AUTOMATIC INDEXING INSERT NON-RECOVERABLE DWPMID  .TP_NEW_USER_MON

0x078000000129E8604767       LOAD                   0         0          0          Sun Dec 25 15:03:37 DW  1          1           [LOADID:32749.2011-12-25-15.03.35.813661.901 (65530;34416)] [*N901.dwinst.111225070321]OFFLINE LOAD DEL AUTOMATIC INDEXING INSERT NON-RECOVERABLE DWPMID  .TP_MSISDN_IMEI_LIST_MON

Progress:

Address            ID         PhaseNum   CompletedWork                TotalWork                    StartTime           Description          

0x078000000129F0684768       1          0 bytes                      Unknown                      Sun Dec 25 15:03:37 SETUP                

0x078000000129EB884767       1          0 bytes                      Unknown                      Sun Dec 25 15:03:37 SETUP

既然是SETUP阶段出了问题，那么，首选要找到负责Pre-partition和Partition的节点。根据我们前面的部署方案，Pre-partition是901节点负责，901-906节点负责将数据Partition到其它数据节点。因此我们将嫌疑放在901-906节点上。

对901-906节点的分析，首先检查应用程序的状态，采用的工具仍然是db2pd，发现对应的Load应用状态为PendingRemoteRequest。

Database Partition 901-- Database DW -- Active -- Up 2 days 20:23:45 -- Date 12/25/2011 19:51:06

Applications:

AppHandl [nod-index]NumAgents  CoorEDUID  Status C-AnchID C-StmtUID  L-AnchIDL-StmtUID  Appid                                                           

59054733 [901-06797]1          30955      PendingRemoteRequest    0       0          672      1         *N901.dwinst.111225070324

     PendingRemoteQuest表示等待其它节点返回结果，如果长时间处于该状态，说明一定有某个节点出现了问题。

     按照这个思路，我们去其他节点查看该应用程序的状态，发现其他节点的状态均为 “Decoupled”，只有903节点比较怪异，该节点竟然没有59054733应用程序。

为了验证903节点是否有问题，我们连接该节点数据库(export DB2NODE=903；db2 terminate；db2 connect to dw)，却无法连接，执行snapshot时也会hang

住。而其它节点连接和snapshot均无问题。更加剧了对该节点的怀疑。

    那么903节点是不是罪魁祸首呢？这就需要看该节点当前agent都在干什么，是否有异常等。检查的方法是看903节点的stack：

db2pd –stack all –dbp903

通过db2pd –stack命令在db2dump目录下产生了一些栈文件。对这些栈文件的分析，最笨的方法当然是一个一个打开看是否有可疑动作。另外一个方法就是根据一些关键字搜索，根据经验，有一些Hang住是由于DB2的latch引起，在stack中会有“Waiting on latch type”或/和“Holding Latch type” 关键字。因此，按照这些关键字搜索stack文件，果然发现几个文件中包含了该信息。

6619710.1800.903.stack

6619710.2314.903.stack 

6619710.31532.903.stack

6619710.31786.903.stack

6619710.32043.903.stack

6619710.34360.903.stack

打开这些文件，一个一个排查，发现6619710.31786.903.stack包含下列latch信息：

<LatchInformation>

Waiting on latch type:(SQLO_LT_sqeApplication__masterAppLatch) - Address: (780000000da01c4), Line:331, File: /view/db2_v97fp5_aix64_s111017/vbs/engn/include/sqle_app_services.h

Holding Latch type:(SQLO_LT_sqeAppServices__m_appServLatch) - Address: (7800000002f04f8), Line:255, File: sqlmdj.C HoldCount: 1

Holding Latch type: (SQLO_LT_sqlmon_dbcb__xlatch_remote_list)- Address: (7800000002eed44), Line: 264, File: sqlmdj.C HoldCount: 1

</LatchInformation>

而6619710.32043.903.stack包含以下latch信息：

<LatchInformation>

Waiting on latch type:(SQLO_LT_sqlmon_dbcb__xlatch_remote_list) - Address: (7800000002eed44), Line:849, File: sqlmdj.C

Holding Latch type:(SQLO_LT_sqeLocalDatabase__dblatch) - Address: (7800000002ea648), Line: 411,File: sqlmutil.C HoldCount: 1

Holding Latch type:(SQLO_LT_sqeAppServices__m_appServLatch) - Address: (7800000002f04f8), Line:1495, File: sqlmonss.C HoldCount: 1

Holding Latch type:(SQLO_LT_sqeApplication__masterAppLatch) - Address: (780000000da01c4), Line:331, File: /view/db2_v97fp5_aix64_s111017/vbs/engn/include/sqle_app_services.hHoldCount: 1

</LatchInformation>

其中一个拿到了SQLO_LT_sqlmon_dbcb__xlatch_remote_lis，而另外一个应用去获得该latch时无法拿到，因此Hang住。

那么SQLO_LT_sqlmon_dbcb__xlatch_remote_list是什么呢？根据sqlmon猜测，应该是与monitor有关。而DB2 monitor主要分为event monitor和snapshot monitor，可以确认event monitor没有打开过，因此，问题可以定位为snapshot monitor了。

但出问题前我们并没有执行snapshot命令啊，说到这里，有经验的用户可能已经猜到了，是不是有第三方的监控软件在监控DB2？没错，不过不是第三方的，而是DB2自带的db2top工具。因为db2top底层调用的仍然是snapshot api，在多分区环境下，缺省是global snapshot，也就是抓取所有分区的快照数据做汇总（类似get snapshot … global命令）。由于目前刚刚是上线阶段，很多人在使用db2top进行一些功能和流程的监控，再加上缺省的db2top每隔2s刷一次，因此导致了db2的latch，进而hang住。

解决方案：

知道了问题的根源，解决起来就容易了，那就是尽量减少global snapshot，特别是在分区很多的情况下，更要小心。另外，在使用db2top时，强烈建议增加refresh interval间隔，并尽量避免同时打开db2top，以减少hang住的几率。

注：关于DB2 latch，《DB2数据库管理最佳实践》一书中有详细的解释。

5.     Load 速度慢案例分析

问题现象描述：

     在解决完上述Load Hang问题后，本以为可以睡个安稳觉了。没想到，还不到一周时间，电话又打过来了。这次的问题虽然不是Hang住，但也好不到哪里去，Load数据的速度跟老牛一样，奇慢无比，以前几十秒完成的任务，现在需要3-4分钟。一个Load，折腾得客户死去活来，客户忍不住要发飙了，俺以第一时间赶到客户现场。

问题分析诊断：

     那么，当出现此问题的时候，我们应该从哪些角度分析呢？首先，需要回答以下几个问题：

（1）Load慢的问题是发生在单个Load，还是多个并发Load？

（2） Load的时候还有别的操作吗？如果停掉别的应用，Load是否还会那么慢？

（3）Load慢的时候，执行其它语句，速度也慢吗？

（4）手工发起一个Load，速度如何？

（5）系统一启动就开始慢，还是执行一段时间后突然变慢？

（6）在其它节点load数据，是否也存在同样问题？

由于这个问题隔几天就会重现一次，因此这些问题并不难回答。首先，最近一段时间是数据追加阶段，同时启动10个load并发，还有另外一个应用执行一些存储过程，对数据进行加工处理。其次，刚开始Load的一段时间，即使多个并发，速度也很快，然后在某一个时点突然变慢，与应用部门确认，该时点并没有执行其它操作。当load速度变慢时，在命令行手工执行一个load操作，速度也很慢。当在其它数据节点执行该操作时，速度仍然很慢，排除了load partition选项的影响。最后，load慢时，SQL语句查询速度并无异常。

既然SQL语句查询速度并无异常，那么我们将问题重新回到load。思路就是手工执行一个load操作，每隔30秒抓一次load stack（9.7fp5提供了db2pd –utilities –loadid stack –alldbp）。通过stack文件的分析，并没有发现特别异常的地方。

    Stack不管用，只有祭出db2trc了。在load前，获得连接的applicationid和agentid，然后通过trace抓取agentid对应的thread，如：

db2 connect to dw

APPLICATIONID=`db2 -x"values application_id()"`

DB2AGENTID=`db2pd -d dw -app|grep $APPLICATIONID|awk '{printf $5}'`

DB2SYSCPID=`db2pd-edu|grep "db2sysc PID:"|awk '{printf $3}'`

db2trc on -i 128m -p$DB2SYSCPID.$DB2AGENTID -t

db2 "load from/dev/null of del modified by dumpfile=/tmp/t1.err warningcount 10 messages /tmp/t1.msg TEMPFILES PATH /tmp/ insert into test.t1"

db2trc dump db2trc.dmp

db2trc off

db2trc flw -tdb2trc.dmp db2trc.flw

db2trc fmt db2trc.dmpdb2trc.fmt

然后，打开db2trc.flw和db2trc.fmt，发现每个pdLog函数从进入到退出需要接近6秒钟时间。而pdLog函数就是写db2diag.log的过程，为什么记个日志需要这么长时间？

29855         84.265494162   | | | | | | | | | | | | | | | pdLog entry[eduid 30985 eduname db2agent]

…

29973         90.172362216   | | | | | | | | | | | | | | | pdLog exit

30617         90.254041968   | | | | | | | | | | | | | | | pdLog entry[eduid 30985 eduname db2agent]

…

30960         96.435289560   | | | | | | | | | | | | | | | pdLog exit

31223         96.454154253   | | | | | | | | | | | | | | | pdLog entry[eduid 30985 eduname db2agent]

...

31416        103.571663519   | | | | | | | | | | | | | | | pdLog exit

    这时一个很自然的想法就是看db2diag.log的位置和内容，正如我们猜想的那样，db2diag.log放在缺省的实例目录db2dump下，而实例目录采用nfs共享方式，nfs存在的一个明显的诟病就是它的性能。在该环境中，由于分区数很多，并且load并发也很大，而每个逻辑分区load都要向db2diag.log写入特别多的信息，因而造成了对db2diag.log的写竞争，也就不难理解Load速度慢了。

解决方案：

将诊断目录从nfs类型改为本地目录。方法是：在每台机器上创建诊断目录，并更改实例参数DIAGPATH，这就会使得每台机器上的所有逻辑分区写到该机器上的诊断目录中，避免了db2diag.log的共享。      

6.     总结

本文以一个实际客户场景，为大家介绍了2个Load问题的处理思路和方法。多分区与单分区相比，数据量巨大，节点数多，问题处理起来相对复杂，但一个核心的思想是不停的细化问题，定位出问题的节点，然后对这个节点进行深入分析，最终找到答案。