关于压力测试（转）

关于压力测试

1. 一个准客户在做一个系统的性能测试，前面打电话聊过，他从metalink，bbs等了解到rac的insert 比单节点性能低，并希望能协助调整。
2. 首先感觉他的思路有问题， 对一个系统的性能测试，应该用专门的压力测试工具，用TPC指标进行测试，看能否达到应用的需求。对单独的insert等操作，有什么说服力呢？
3. 根据他的要求， 对rac（连接到某个节点）， 单节点（cluster_database=false)分别进行插入20000条记录测试（1.sql,内容见下面）， 用set timing on 记录执行时间。 测试发现，2个时间基本是一样的。详细情况参看下面的测试记录
4. 后来，使用swingbench进行压力测试。因为2个node配置差别很大，一个2cpu，4GRAM（node1），一个4cpu, 8GRAM (node2) ,负载均衡时， 一个node已经满复合，另外一个还只有20％，总不能达到整个系统的峰值。后修改客户端的tnsnames.ora （见下面附录），在ADDRESS列表中增加2个node2,负载均衡时， node2将得到3/4的连接， 压力也增加3/4, 这样测试，基本达到了系统的峰值。

l         single node.

$ sqlplus cqtest/cqtest

 

SQL*Plus: Release 9.2.0.6.0 – Production on Mon Oct 9 11:31:00 2006

 

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.6.0 – 64bit Production

With the Partitioning, Real Application Clusters, OLAP and Oracle Data Mining options

JServer Release 9.2.0.6.0 – Production

 

SQL> set time on

11:31:03 SQL> set timing on

11:31:05 SQL> alter session set events ‘10046 trace name context forever, level 12′;

 

Session altered.

 

Elapsed: 00:00:00.00

11:32:05 SQL> @1

11:32:07   2  /

 

PL/SQL procedure successfully completed.

 

Elapsed: 00:00:07.69

11:32:16 SQL> alter session set events ‘10046 trace name context off’;

 

Session altered.

 

Elapsed: 00:00:00.00

11:32:30 SQL>

 

l         rac

 

11:34:13 SQL> conn cqtest/cqtest

Connected.

11:34:47 SQL> truncate table free_item_no;

 

Table truncated.

 

Elapsed: 00:00:00.35

11:34:56 SQL> alter session set events ‘10046 trace name context forever, level 12′;

 

Session altered.

 

Elapsed: 00:00:00.01

11:35:12 SQL> @1

11:35:15   2  /

 

PL/SQL procedure successfully completed.

 

Elapsed: 00:00:07.98

11:35:24 SQL> alter session set events ‘10046 trace name context off’;

 

Session altered.

 

Elapsed: 00:00:00.00

11:35:34 SQL>

可见，时间基本一样。

 后又在vmware-linux的rac (10.2.0.1)上测试， 测试结果：

linux-rac

 

l         rac

SQL> conn lyf/lyf

Connected.

SQL> create table te1 (id number);

 

Table created.

 

SQL> alter session set events ‘10046 trace name context forever, level 12′;

 

Session altered.

 

SQL> set timing on

SQL> set time on

01:32:23 SQL> begin  for no in 1..20000 loop    insert into te1 values (no*11);  end loop;  commit;end;

01:32:35   2  /

 

PL/SQL procedure successfully completed.

 

Elapsed: 00:00:04.49

01:32:41 SQL> alter session set events ‘10046 trace name context off’;

 

Session altered.

 

Elapsed: 00:00:00.01

01:33:12 SQL>

 

l         node

 

SQL> conn lyf/lyf

Connected.

SQL> set timing on

SQL> alter session set events ‘10046 trace name context forever, level 12′;

 

Session altered.

 

Elapsed: 00:00:00.02

SQL> begin  for no in 1..20000 loop    insert into te1 values (no*11);  end loop;  commit;end;

  2  /

 

PL/SQL procedure successfully completed.

 

Elapsed: 00:00:04.73

SQL> alter session set events ‘10046 trace name context off’;

 

Session altered.

这里，node比rac时间还长。

 通过2个平台， 2个版本比较， 感觉rac和单节点在insert上速度可以认为基本一样。

tnsnames.ora

ORCLnew =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.102)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.102)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.102)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.101)(PORT = 1521))
      (LOAD_BALANCE = yes)
      (FAILOVER = on)
    )
     (CONNECT_DATA =
       (SERVER = DEDICATED)
       (SERVICE_NAME = orcl)
       (FAILOVER_MODE=
           (TYPE=session)
            (METHOD=basic)
       )
     )
   )

1.sql

begin  for no in 1..20000 loop    insert into te1 values (no*11);  end loop;