RAC负载均衡的简单测试.doc

1、RAC 负载均衡的简单测试（一） = 作者: yangtingkun(http:/)发表于: 2007.04.11 16:07分类: ORACLE 出处: http:/ Rac 环境安装完成之后，打算简单测试一下 Oracle RAC 的负载均衡功能。配置负载均衡，需要在客户端的 tnsnames.ora 中进行配置。TESTRAC =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 172.25.198.224)(PORT = 1521)(ADDRESS = (PROTOCOL = TCP)(HOST = 172.25.198.225)(POR

2、T = 1521)(LOAD_BALANCE = yes)(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = testrac)设置了 LOAD_BALANCE = yes 之后，使用 TESTRAC 服务名连接数据库时就启动了负载均衡功能。登陆 RAC 实例，检查两个实例上的会话连接情况：SQL SELECT INST_ID, COUNT(*) FROM GV$SESSION GROUP BY INST_ID;INST_ID COUNT(*)- -1 312 31下面在客户端连接 RAC 实例，并检查当前连接的是哪个实例：SQL CONN ND

3、MAINTESTRAC 输入口令: *已连接。SQL SELECT INSTANCE_NAME FROM V$INSTANCE;INSTANCE_NAME-testrac1SQL CONN NDMAINTESTRAC 输入口令: *已连接。SQL SELECT INSTANCE_NAME FROM V$INSTANCE;INSTANCE_NAME-testrac2由于两个实例上会话数量相当，所以 Oracle 在分配连接时，分配给每个实例的概率基本相同。这是会话连接两次的情况，下面看看当多个会话连接时，Oracle 是如何分配的。SQL DISC 从 Oracle Database 10g E

4、nterprise Edition Release 10.2.0.2.0 - 64bit ProductionWith the Partitioning, Real Application Clusters, OLAP and Data Mining options 断开首先断开刚才连接的会话，然后启动 4 个新的连接，并检查这些连接分布到哪些实例上：E:SQLPLUS NDMAIN/NDMAINTESTRACSQL*Plus: Release 10.2.0.1.0 - Production on 星期四 3 月 22 19:15:26 2007Copyright (c) 1982, 2005

5、, Oracle. All rights reserved.连接到:Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit ProductionWith the Partitioning, Real Application Clusters, OLAP and Data Mining optionsSQL SELECT INSTANCE_NAME FROM V$INSTANCE;INSTANCE_NAME-testrac1SQL HOSTMicrosoft Windows 2000 Version 5.00.2195(

6、C) 版权所有 1985-2000 Microsoft Corp.E:SQLPLUS NDMAIN/NDMAINTESTRACSQL*Plus: Release 10.2.0.1.0 - Production on 星期四 3 月 22 19:15:46 2007Copyright (c) 1982, 2005, Oracle. All rights reserved.连接到:Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit ProductionWith the Partitioning, Real Applic

7、ation Clusters, OLAP and Data Mining optionsSQL SELECT INSTANCE_NAME FROM V$INSTANCE;INSTANCE_NAME-testrac1SQL HOSTMicrosoft Windows 2000 Version 5.00.2195(C) 版权所有 1985-2000 Microsoft Corp.E:SQLPLUS NDMAIN/NDMAINTESTRACSQL*Plus: Release 10.2.0.1.0 - Production on 星期四 3 月 22 19:16:02 2007Copyright (c

8、) 1982, 2005, Oracle. All rights reserved.连接到:Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit ProductionWith the Partitioning, Real Application Clusters, OLAP and Data Mining optionsSQL SELECT INSTANCE_NAME FROM V$INSTANCE;INSTANCE_NAME-testrac2SQL HOSTMicrosoft Windows 2000 Versio

9、n 5.00.2195(C) 版权所有 1985-2000 Microsoft Corp.E:SQLPLUS NDMAIN/NDMAINTESTRACSQL*Plus: Release 10.2.0.1.0 - Production on 星期四 3 月 22 19:16:16 2007Copyright (c) 1982, 2005, Oracle. All rights reserved.连接到:Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit ProductionWith the Partitioning,

10、 Real Application Clusters, OLAP and Data Mining optionsSQL SELECT INSTANCE_NAME FROM V$INSTANCE;INSTANCE_NAME-testrac2可以看到，由于两个实例负载相当，Oracle 基本上是按照 50%的概率给每个实例分配新的连接。RAC 负载均衡的简单测试（二） = 作者: yangtingkun(http:/)发表于: 2007.04.12 14:32分类: ORACLE 出处: http:/ Rac 环境安装完成之后，打算简单测试一下 Oracle RAC 的负载均衡功能。这篇继续讨论负

11、载均衡的特性。RAC 负载均衡的简单测试（一）：http:/ TESTRAC2 实例的连接数，这里使用的方法是在 racnode2 数据库上使用直接连接的方式：$ sqlplus ndmain SQL*Plus: Release 10.2.0.2.0 - Production on 星期四 3 月 22 19:24:23 2007Copyright (c) 1982, 2005, Oracle. All Rights Reserved.输入口令: 连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Pro

12、ductionWith the Partitioning, Real Application Clusters, OLAP and Data Mining optionsSQL host$ sqlplus ndmainSQL*Plus: Release 10.2.0.2.0 - Production on 星期四 3 月 22 19:24:28 2007Copyright (c) 1982, 2005, Oracle. All Rights Reserved.输入口令: 连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0

13、 - 64bit ProductionWith the Partitioning, Real Application Clusters, OLAP and Data Mining optionsSQL host$ sqlplus ndmainSQL*Plus: Release 10.2.0.2.0 - Production on 星期四 3 月 22 19:24:33 2007Copyright (c) 1982, 2005, Oracle. All Rights Reserved.输入口令: 连接到: Oracle Database 10g Enterprise Edition Releas

14、e 10.2.0.2.0 - 64bit ProductionWith the Partitioning, Real Application Clusters, OLAP and Data Mining optionsSQL host$ sqlplus ndmainSQL*Plus: Release 10.2.0.2.0 - Production on 星期四 3 月 22 19:24:37 2007Copyright (c) 1982, 2005, Oracle. All Rights Reserved.输入口令: 连接到: Oracle Database 10g Enterprise Ed

15、ition Release 10.2.0.2.0 - 64bit ProductionWith the Partitioning, Real Application Clusters, OLAP and Data Mining optionsSQL host$ sqlplus ndmainSQL*Plus: Release 10.2.0.2.0 - Production on 星期四 3 月 22 19:24:44 2007Copyright (c) 1982, 2005, Oracle. All Rights Reserved.输入口令: 连接到: Oracle Database 10g E

16、nterprise Edition Release 10.2.0.2.0 - 64bit ProductionWith the Partitioning, Real Application Clusters, OLAP and Data Mining optionsSQL host$ sqlplus ndmainSQL*Plus: Release 10.2.0.2.0 - Production on 星期四 3 月 22 19:24:48 2007Copyright (c) 1982, 2005, Oracle. All Rights Reserved.输入口令: 连接到: Oracle Da

17、tabase 10g Enterprise Edition Release 10.2.0.2.0 - 64bit ProductionWith the Partitioning, Real Application Clusters, OLAP and Data Mining optionsSQL host$ sqlplus ndmainSQL*Plus: Release 10.2.0.2.0 - Production on 星期四 3 月 22 19:24:54 2007Copyright (c) 1982, 2005, Oracle. All Rights Reserved.输入口令: 连接

18、到: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit ProductionWith the Partitioning, Real Application Clusters, OLAP and Data Mining optionsSQL host$ sqlplus ndmainSQL*Plus: Release 10.2.0.2.0 - Production on 星期四 3 月 22 19:25:00 2007Copyright (c) 1982, 2005, Oracle. All Rights Reser

19、ved.输入口令: 连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit ProductionWith the Partitioning, Real Application Clusters, OLAP and Data Mining optionsSQL 查询目前数据库的连接情况：SQL SELECT INST_ID, COUNT(*) FROM GV$SESSION GROUP BY INST_ID;INST_ID COUNT(*)- -1 312 38下面尝试多次连接，并检查连接的实例：SQL CON

20、N NDMAIN/NDMAINTESTRAC 已连接。SQL SELECT INSTANCE_NAME FROM V$INSTANCE;INSTANCE_NAME-testrac2SQL CONN NDMAIN/NDMAINTESTRAC 已连接。SQL SELECT INSTANCE_NAME FROM V$INSTANCE;INSTANCE_NAME-testrac1SQL CONN NDMAIN/NDMAINTESTRAC 已连接。SQL SELECT INSTANCE_NAME FROM V$INSTANCE;INSTANCE_NAME-testrac2SQL CONN NDMAIN/

21、NDMAINTESTRAC 已连接。SQL SELECT INSTANCE_NAME FROM V$INSTANCE;INSTANCE_NAME-testrac1SQL CONN NDMAIN/NDMAINTESTRAC 已连接。SQL SELECT INSTANCE_NAME FROM V$INSTANCE;INSTANCE_NAME-testrac2发现连接到两个实例的概率仍然是一样的。看看连续启动多个连接的情况：E:SQLPLUS NDMAIN/NDMAINTESTRACSQL*Plus: Release 10.2.0.1.0 - Production on 星期四 3 月 22 19:

22、27:05 2007Copyright (c) 1982, 2005, Oracle. All rights reserved.连接到:Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit ProductionWith the Partitioning, Real Application Clusters, OLAP and Data Mining optionsSQL SELECT INSTANCE_NAME FROM V$INSTANCE;INSTANCE_NAME-testrac1SQL HOSTMicroso

23、ft Windows 2000 Version 5.00.2195(C) 版权所有 1985-2000 Microsoft Corp.E:SQLPLUS NDMAIN/NDMAINTESTRACSQL*Plus: Release 10.2.0.1.0 - Production on 星期四 3 月 22 19:27:18 2007Copyright (c) 1982, 2005, Oracle. All rights reserved.连接到:Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production

24、With the Partitioning, Real Application Clusters, OLAP and Data Mining optionsSQL SELECT INSTANCE_NAME FROM V$INSTANCE;INSTANCE_NAME-testrac2SQL HOSTMicrosoft Windows 2000 Version 5.00.2195(C) 版权所有 1985-2000 Microsoft Corp.E:SQLPLUS NDMAIN/NDMAINTESTRACSQL*Plus: Release 10.2.0.1.0 - Production on 星期

25、四 3 月 22 19:27:31 2007Copyright (c) 1982, 2005, Oracle. All rights reserved.连接到:Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit ProductionWith the Partitioning, Real Application Clusters, OLAP and Data Mining optionsSQL SELECT INSTANCE_NAME FROM V$INSTANCE;INSTANCE_NAME-testrac1SQL

26、 HOSTMicrosoft Windows 2000 Version 5.00.2195(C) 版权所有 1985-2000 Microsoft Corp.E:SQLPLUS NDMAIN/NDMAINTESTRACSQL*Plus: Release 10.2.0.1.0 - Production on 星期四 3 月 22 19:27:40 2007Copyright (c) 1982, 2005, Oracle. All rights reserved.连接到:Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bi

27、t ProductionWith the Partitioning, Real Application Clusters, OLAP and Data Mining optionsSQL SELECT INSTANCE_NAME FROM V$INSTANCE;INSTANCE_NAME-testrac2可以看到，结果仍然是 50%的概率。难道负载均衡不考虑实例本身的负载，只是对新增连接进行平均分配？其实并不是这样，关键在于负载均衡的功能是通过 LISTENER 来实现的。而在上面增加 TESTRAC2 上的连接数的时候，采用的是本地连接方式，恰恰绕过了 LISTENER，对于 LISTENE

28、R 来说，这些新增的会话都是不可见的。因此，得到的结果当然和原来的一致。下面断开刚才连接的所有会话：SQL SELECT INST_ID, COUNT(*) FROM GV$SESSION GROUP BY INST_ID;INST_ID COUNT(*)- -1 312 30然后以 TESTRAC2 为服务名，连接到 RAC 的 TESTRAC2 实例上：$ sqlplus ndmain/ndmaintestrac2SQL*Plus: Release 10.2.0.2.0 - Production on 星期四 3 月 22 21:14:44 2007Copyright (c) 1982,

29、2005, Oracle. All Rights Reserved.连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit ProductionWith the Partitioning, Real Application Clusters, OLAP and Data Mining optionsSQL host$ sqlplus ndmain/ndmaintestrac2SQL*Plus: Release 10.2.0.2.0 - Production on 星期四 3 月 22 21:14:52 200

30、7Copyright (c) 1982, 2005, Oracle. All Rights Reserved.连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit ProductionWith the Partitioning, Real Application Clusters, OLAP and Data Mining optionsSQL host$ sqlplus ndmain/ndmaintestrac2SQL*Plus: Release 10.2.0.2.0 - Production on 星期

31、四 3 月 22 21:14:55 2007Copyright (c) 1982, 2005, Oracle. All Rights Reserved.连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit ProductionWith the Partitioning, Real Application Clusters, OLAP and Data Mining optionsSQL host$ sqlplus ndmain/ndmaintestrac2SQL*Plus: Release 10.2.0.2

32、.0 - Production on 星期四 3 月 22 21:14:59 2007Copyright (c) 1982, 2005, Oracle. All Rights Reserved.连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit ProductionWith the Partitioning, Real Application Clusters, OLAP and Data Mining optionsSQL host$ sqlplus ndmain/ndmaintestrac2SQL*P

33、lus: Release 10.2.0.2.0 - Production on 星期四 3 月 22 21:15:04 2007Copyright (c) 1982, 2005, Oracle. All Rights Reserved.连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit ProductionWith the Partitioning, Real Application Clusters, OLAP and Data Mining optionsSQL host$ sqlplus ndmai

34、n/ndmaintestrac2SQL*Plus: Release 10.2.0.2.0 - Production on 星期四 3 月 22 21:15:14 2007Copyright (c) 1982, 2005, Oracle. All Rights Reserved.连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit ProductionWith the Partitioning, Real Application Clusters, OLAP and Data Mining optionsSQ

35、L 再次测试连接实例的情况：SQL SELECT INST_ID, COUNT(*) FROM GV$SESSION GROUP BY INST_ID;INST_ID COUNT(*)- -1 312 36SQL CONN NDMAIN/NDMAINTESTRAC 已连接。SQL SELECT INSTANCE_NAME FROM V$INSTANCE;INSTANCE_NAME-testrac1SQL CONN NDMAIN/NDMAINTESTRAC 已连接。SQL SELECT INSTANCE_NAME FROM V$INSTANCE;INSTANCE_NAME-testrac1SQL

36、 CONN NDMAIN/NDMAINTESTRAC 已连接。SQL SELECT INSTANCE_NAME FROM V$INSTANCE;INSTANCE_NAME-testrac1SQL DISC 从 Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit ProductionWith the Partitioning, Real Application Clusters, OLAP and Data Mining options 断开可以看到，连接时 Oracle 都会将会话连接到 TESTRAC1 上。下面

37、看看连续启动多个会话，Oracle 如何在实例间分配会话：E:SQLPLUS NDMAIN/NDMAINTESTRACSQL*Plus: Release 10.2.0.1.0 - Production on 星期四 3 月 22 21:19:41 2007Copyright (c) 1982, 2005, Oracle. All rights reserved.连接到:Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit ProductionWith the Partitioning, Real Applicatio

38、n Clusters, OLAP and Data Mining optionsSQL SELECT INSTANCE_NAME FROM V$INSTANCE;INSTANCE_NAME-testrac1SQL HOSTMicrosoft Windows 2000 Version 5.00.2195(C) 版权所有 1985-2000 Microsoft Corp.E:SQLPLUS NDMAIN/NDMAINTESTRACSQL*Plus: Release 10.2.0.1.0 - Production on 星期四 3 月 22 21:19:59 2007Copyright (c) 19

39、82, 2005, Oracle. All rights reserved.连接到:Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit ProductionWith the Partitioning, Real Application Clusters, OLAP and Data Mining optionsSQL SELECT INSTANCE_NAME FROM V$INSTANCE;INSTANCE_NAME-testrac1SQL HOSTMicrosoft Windows 2000 Version 5.

40、00.2195(C) 版权所有 1985-2000 Microsoft Corp.E:SQLPLUS NDMAIN/NDMAINTESTRACSQL*Plus: Release 10.2.0.1.0 - Production on 星期四 3 月 22 21:20:12 2007Copyright (c) 1982, 2005, Oracle. All rights reserved.连接到:Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit ProductionWith the Partitioning, Rea

41、l Application Clusters, OLAP and Data Mining optionsSQL SELECT INSTANCE_NAME FROM V$INSTANCE;INSTANCE_NAME-testrac1SQL根据上面的测试可以看出，Oracle 的负载均衡和实例通过 LISTENER 建立的连接数有关。RAC 负载均衡的简单测试（三）上一篇 / 下一篇 2007-04-13 00:00:00 / 个人分类：ORACLE 查看( 176 ) / 评论 ( 0 ) / 评分( 0 / 0 ) Rac 环境安装完成之后，打算简单测试一下 Oracle RAC 的负载均衡功

42、能。RAC 负载均衡的简单测试（一）：http:/ 负载均衡的简单测试（二）：http:/ 的负载均衡通过 LISTENER 建立的连接数有关。那个是否负载均衡只与这个因素有关，RAC 的负载均衡和实例的忙闲状态是否有关呢？在 TESTRAC2 实例上启动两个做循环操作的 SQL，模拟系统资源被大量占用的情况：SQL DECLARE2 V_JOB NUMBER;3 BEGIN4 DBMS_JOB.SUBMIT(V_JOB, 5 BEGIN6 FOR I IN 11000 LOOP7 FOR J IN 11000000 LOOP8 NULL;9 END LOOP;10 END LOOP;11 E

43、ND;, 12 TO_DATE(2007-3-22 22:00:00, YYYY-MM-DD HH24:MI:SS), 13 INSTANCE = 2);14 COMMIT;15 END;16 /PL/SQL 过程已成功完成。已用时间: 00: 00: 00.53SQL DECLARE2 V_JOB NUMBER;3 BEGIN4 DBMS_JOB.SUBMIT(V_JOB, 5 BEGIN6 FOR I IN 11000 LOOP7 FOR J IN 11000000 LOOP8 NULL;9 END LOOP;10 END LOOP;11 END;, 12 TO_DATE(2007-3-2

44、2 22:00:00, YYYY-MM-DD HH24:MI:SS), 13 INSTANCE = 2);14 COMMIT;15 END;16 /PL/SQL 过程已成功完成。等到两个 JOB 启动之后，检查操作系统的运行情况：$ sar 1 10SunOS racnode2 5.8 Generic_117350-46 sun4u 03/22/0722:00:29 %usr %sys %wio %idle22:00:30 94 6 0 022:00:31 94 6 0 022:00:32 95 5 0 022:00:33 91 9 0 022:00:34 93 7 0 022:00:35 9

45、1 9 0 022:00:36 92 8 0 022:00:37 95 5 0 022:00:38 94 6 0 022:00:39 96 4 0 0Average 94 6 0 0$ 然后进行连接实例的测试：SQL CONN NDMAIN/NDMAINTESTRAC 已连接。SQL SELECT INSTANCE_NAME FROM V$INSTANCE;INSTANCE_NAME-testrac1SQL CONN NDMAIN/NDMAINTESTRAC 已连接。SQL SELECT INSTANCE_NAME FROM V$INSTANCE;INSTANCE_NAME-testrac2S

46、QL CONN NDMAIN/NDMAINTESTRAC 已连接。SQL SELECT INSTANCE_NAME FROM V$INSTANCE;INSTANCE_NAME-testrac1SQL CONN NDMAIN/NDMAINTESTRAC 已连接。SQL SELECT INSTANCE_NAME FROM V$INSTANCE;INSTANCE_NAME-testrac2SQL DISC 从 Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit ProductionWith the Partitioning, Real Application Clusters, OLAP and Data Mining options 断开SQL 多个连接的测试：E:SQLPLUS NDMAIN/N