互联网服务数据库

DB2 并行查询的性能问题，急！

DB2 V8.2 Fix15；一张基础表有420万条记录，其中索引使用时间戳做主键，查询精确定位；一个进程查询的时候，耗时基本在1ms左右，当多个进程如160个时候，查询很多大于20ms，并且存在11几秒的情况，或者更糟糕的情况等待几十秒；
如代码：

#include
#include
#include
#include
#include "stdio.h"
EXEC SQL INCLUDE SQLCA;
int main()
{
int i,j,k=0;
struct tm *pt;
time_t t1;
int timetmp=0;
      EXEC SQL BEGIN DECLARE SECTION;
      char cust_no[11]={0};
      char other_no[17]={0};
      EXEC SQL END DECLARE SECTION;

EXEC SQL CONNECT TO ksdbs;
t1 = time(NULL);
pt = localtime(&t1);
printf("%4d%02d%02d", pt->tm_year+1900, pt->tm_mon+1, pt->tm_mday);
printf("%02d:%02d:%02dn",pt->tm_hour,pt->tm_min,pt->tm_sec);
struct timeval CurrentTime;
i=0;
while (i<10000)
{
      gettimeofday(&CurrentTime, NULL);
      timetmp=CurrentTime.tv_sec * 1000+CurrentTime.tv_usec / 1000;
      j=0;
      while (j<20)
      {
      EXEC SQL select CUST_NO,HOLDER_ACC_NO into :cust_no,:other_no from ks.entrust_test1
            where  CUST_NO='161' and ENTRUST_TIMESTAMP='2010-11-12-08.26.18.878489' with ur ;
      if (SQLCODE)
         {
            printf("SQLCODE[%d]",SQLCODE);
         }
            if(SQLCODE)
         {
            printf("SQLCODE[%d]",SQLCODE);
         }
         j++;
      }
         i++;
         gettimeofday(&CurrentTime, NULL);
         if (CurrentTime.tv_sec * 1000+CurrentTime.tv_usec / 1000-timetmp>1)
      {
printf("%4d%02d%02d", pt->tm_year+1900, pt->tm_mon+1, pt->tm_mday);
printf("%02d:%02d:%02dn",pt->tm_hour,pt->tm_min,pt->tm_sec);
struct timeval CurrentTime;
i=0;
while (i<10000)
{
      gettimeofday(&CurrentTime, NULL);
      timetmp=CurrentTime.tv_sec * 1000+CurrentTime.tv_usec / 1000;
      j=0;
      while (j<20)
      {
      EXEC SQL select CUST_NO,HOLDER_ACC_NO into :cust_no,:other_no from t1.entrust_test1
            where  CUST_NO='161' and ENTRUST_TIMESTAMP='2010-11-12-08.26.18.878489' with ur ;
      if (SQLCODE)
         {
            printf("SQLCODE[%d]",SQLCODE);
         }
            if(SQLCODE)
         {
            printf("SQLCODE[%d]",SQLCODE);
         }
         j++;
      }
         i++;
         gettimeofday(&CurrentTime, NULL);
         if (CurrentTime.tv_sec * 1000+CurrentTime.tv_usec / 1000-timetmp>1)
      {
         k++;
         printf("PID[%d],usedtime[%d],k[%d]n",getpid(),CurrentTime.tv_sec * 1000+CurrentTime.tv_usec / 1000-timetmp,k);
      }
      EXEC SQL COMMIT;
      if(SQLCODE)
         {
            printf("SQLCODE[%d]",SQLCODE);
         }
         gettimeofday(&CurrentTime, NULL);
         if (CurrentTime.tv_sec * 1000+CurrentTime.tv_usec / 1000-timetmp>20)
      {
         printf("******PID[%d],usedtime[%d],k[%d]n",getpid(),CurrentTime.tv_sec * 1000+CurrentTime.tv_usec / 1000-timetmp,k);
      }
}

EXEC SQL CONNECT RESET;
return 1;
}
其中执行计划如：
Section = 1

SQL Statement:

  select CUST_NO, HOLDER_ACC_NO into :H00001 , :H00002
  from ks.entrust_test1
  where CUST_NO='161'and ENTRUST_TIMESTAMP='
      2010-11-12-08.26.18.878489'
  with ur
Statement Isolation Level = Uncommitted Read
Section Code Page = 1386
Estimated Cost = 38.589993
Estimated Cardinality = 0.000010
Access Table Name = KS.ENTRUST_TEST1  ID = 6,3080
|  Index Scan:  Name = SYSIBM.SQL101105164401310  ID = 1
|  |  Regular Index (Not Clustered)
|  |  Index Columns:
|  |  |  1: ENTRUST_TIMESTAMP (Ascending)
|  #Columns = 1
|  Single Record
|  Fully Qualified Unique Key
|  Skip Deleted Keys
|  Skip Deleted Rows
|  #Key Columns = 1
|  |  Start Key: Inclusive Value
|  |  |  |  1: 2010-11-12 08:26:18.878489
|  |  Stop Key: Inclusive Value
|  |  |  |  1: 2010-11-12 08:26:18.878489
|  Data Prefetch: None
|  Index Prefetch: None
|  Isolation Level: Uncommitted Read
|  Lock Intents
|  |  Table: Intent None
|  |  Row  : None
|  Sargable Predicate(s)
|  |  #Predicates = 1
|  |  Return Data to Application
|  |  |  #Columns = 2
Return Data Completion
End of section

Optimizer Plan:
               RETURN
               ( 1)
                  |
               FETCH
               ( 2)
         /---/
   IXSCAN       Table:
   ( 2)       KS
      |          ENTRUST_TEST1
Index:
SYSIBM
SQL101105164401310

为什么多个进程同时去查询的时候，会有很大影响。收起

关注1

参与5

查看其它 3 个回答jameshd的回答

jameshd技术总监胜科金仕达数据系统(中国)有限公司

此问题，可能与操作系统的进程优先级有关系，当进程多过cpu个数时，db2进程的优先级被降低，得到cpu的时间片会切换，造成响应延时，可以调整db2参数agentpri，固定优先级别，数据库表现较好。:)

互联网服务 · 2012-01-31

DB2 并行查询的性能问题，急！

查看其它 3 个回答jameshd的回答

回答者

jameshd 最近回答过的问题

回答状态