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

RAC负载均衡的简单测试（一） = 作者: yangtingkun()发表于: 2007.04.11 16:07分类: ORACLE 出处: /post/468/279433- Rac环境安装完成之后，打算简单测试一下Oracle RAC的负载均衡功能。配置负载均衡，需要在客户端的tnsnames.ora中进行配置。TESTRAC =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 24)(PORT = 1521)(ADDRESS = (PROTOCOL = TCP)(HOST = 25)(PORT = 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 NDMAINTESTRAC输入口令: *已连接。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 Enterprise Edition Release .0 - 64bit ProductionWith the Partitioning, Real Application Clusters, OLAP and Data Mining options 断开首先断开刚才连接的会话，然后启动4个新的连接，并检查这些连接分布到哪些实例上：E:SQLPLUS NDMAIN/NDMAINTESTRACSQL*Plus: Release .0 - Production on 星期四 3月 22 19:15:26 2007Copyright (c) 1982, 2005, Oracle. All rights reserved.连接到:Oracle Database 10g Enterprise Edition Release .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(C) 版权所有 1985-2000 Microsoft Corp.E:SQLPLUS NDMAIN/NDMAINTESTRACSQL*Plus: Release .0 - Production on 星期四 3月 22 19:15:46 2007Copyright (c) 1982, 2005, Oracle. All rights reserved.连接到:Oracle Database 10g Enterprise Edition Release .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(C) 版权所有 1985-2000 Microsoft Corp.E:SQLPLUS NDMAIN/NDMAINTESTRACSQL*Plus: Release .0 - Production on 星期四 3月 22 19:16:02 2007Copyright (c) 1982, 2005, Oracle. All rights reserved.连接到:Oracle Database 10g Enterprise Edition Release .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 Version 5.00.2195(C) 版权所有 1985-2000 Microsoft Corp.E:SQLPLUS NDMAIN/NDMAINTESTRACSQL*Plus: Release .0 - Production on 星期四 3月 22 19:16:16 2007Copyright (c) 1982, 2005, Oracle. All rights reserved.连接到:Oracle Database 10g Enterprise Edition Release .0 - 64bit ProductionWith the Partitioning, Real Application Clusters, OLAP and Data Mining optionsSQL SELECT INSTANCE_NAME FROM V$INSTANCE;INSTANCE_NAME-testrac2可以看到，由于两个实例负载相当，Oracle基本上是按照50%的概率给每个实例分配新的连接。RAC负载均衡的简单测试（二） = 作者: yangtingkun()发表于: 2007.04.12 14:32分类: ORACLE 出处: /post/468/279754- Rac环境安装完成之后，打算简单测试一下Oracle RAC的负载均衡功能。这篇继续讨论负载均衡的特性。RAC负载均衡的简单测试（一）：/post/468/279433上一篇的测试中可以看到，当两个实例的负载相当的时候，新连接会平均分配到两个实例上去。下面看看如果其中一个实例的连接数明显大于另一个实例的情况。增加连接TESTRAC2实例的连接数，这里使用的方法是在racnode2数据库上使用直接连接的方式：$ sqlplus ndmain SQL*Plus: Release .0 - Production on 星期四 3月 22 19:24:23 2007Copyright (c) 1982, 2005, Oracle. All Rights Reserved.输入口令: 连接到: Oracle Database 10g Enterprise Edition Release .0 - 64bit ProductionWith the Partitioning, Real Application Clusters, OLAP and Data Mining optionsSQL host$ sqlplus ndmainSQL*Plus: Release .0 - Production on 星期四 3月 22 19:24:28 2007Copyright (c) 1982, 2005, Oracle. All Rights Reserved.输入口令: 连接到: Oracle Database 10g Enterprise Edition Release .0 - 64bit ProductionWith the Partitioning, Real Application Clusters, OLAP and Data Mining optionsSQL host$ sqlplus ndmainSQL*Plus: Release .0 - Production on 星期四 3月 22 19:24:33 2007Copyright (c) 1982, 2005, Oracle. All Rights Reserved.输入口令: 连接到: Oracle Database 10g Enterprise Edition Release .0 - 64bit ProductionWith the Partitioning, Real Application Clusters, OLAP and Data Mining optionsSQL host$ sqlplus ndmainSQL*Plus: Release .0 - Production on 星期四 3月 22 19:24:37 2007Copyright (c) 1982, 2005, Oracle. All Rights Reserved.输入口令: 连接到: Oracle Database 10g Enterprise Edition Release .0 - 64bit ProductionWith the Partitioning, Real Application Clusters, OLAP and Data Mining optionsSQL host$ sqlplus ndmainSQL*Plus: Release .0 - Production on 星期四 3月 22 19:24:44 2007Copyright (c) 1982, 2005, Oracle. All Rights Reserved.输入口令: 连接到: Oracle Database 10g Enterprise Edition Release .0 - 64bit ProductionWith the Partitioning, Real Application Clusters, OLAP and Data Mining optionsSQL host$ sqlplus ndmainSQL*Plus: Release .0 - Production on 星期四 3月 22 19:24:48 2007Copyright (c) 1982, 2005, Oracle. All Rights Reserved.输入口令: 连接到: Oracle Database 10g Enterprise Edition Release .0 - 64bit ProductionWith the Partitioning, Real Application Clusters, OLAP and Data Mining optionsSQL host$ sqlplus ndmainSQL*Plus: Release .0 - Production on 星期四 3月 22 19:24:54 2007Copyright (c) 1982, 2005, Oracle. All Rights Reserved.输入口令: 连接到: Oracle Database 10g Enterprise Edition Release .0 - 64bit ProductionWith the Partitioning, Real Application Clusters, OLAP and Data Mining optionsSQL host$ sqlplus ndmainSQL*Plus: Release .0 - Production on 星期四 3月 22 19:25:00 2007Copyright (c) 1982, 2005, Oracle. All Rights Reserved.输入口令: 连接到: Oracle Database 10g Enterprise Edition Release .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 CONN 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/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 .0 - Production on 星期四 3月 22 19:27:05 2007Copyright (c) 1982, 2005, Oracle. All rights reserved.连接到:Oracle Database 10g Enterprise Edition Release .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(C) 版权所有 1985-2000 Microsoft Corp.E:SQLPLUS NDMAIN/NDMAINTESTRACSQL*Plus: Release .0 - Production on 星期四 3月 22 19:27:18 2007Copyright (c) 1982, 2005, Oracle. All rights reserved.连接到:Oracle Database 10g Enterprise Edition Release .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 Version 5.00.2195(C) 版权所有 1985-2000 Microsoft Corp.E:SQLPLUS NDMAIN/NDMAINTESTRACSQL*Plus: Release .0 - Production on 星期四 3月 22 19:27:31 2007Copyright (c) 1982, 2005, Oracle. All rights reserved.连接到:Oracle Database 10g Enterprise Edition Release .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(C) 版权所有 1985-2000 Microsoft Corp.E:SQLPLUS NDMAIN/NDMAINTESTRACSQL*Plus: Release .0 - Production on 星期四 3月 22 19:27:40 2007Copyright (c) 1982, 2005, Oracle. All rights reserved.连接到:Oracle Database 10g Enterprise Edition Release .0 - 64bit ProductionWith the Partitioning, Real Application Clusters, OLAP and Data Mining optionsSQL SELECT INSTANCE_NAME FROM V$INSTANCE;INSTANCE_NAME-testrac2可以看到，结果仍然是50%的概率。难道负载均衡不考虑实例本身的负载，只是对新增连接进行平均分配？其实并不是这样，关键在于负载均衡的功能是通过LISTENER来实现的。而在上面增加TESTRAC2上的连接数的时候，采用的是本地连接方式，恰恰绕过了LISTENER，对于LISTENER来说，这些新增的会话都是不可见的。因此，得到的结果当然和原来的一致。下面断开刚才连接的所有会话：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 .0 - Production on 星期四 3月 22 21:14:44 2007Copyright (c) 1982, 2005, Oracle. All Rights Reserved.连接到: Oracle Database 10g Enterprise Edition Release .0 - 64bit ProductionWith the Partitioning, Real Application Clusters, OLAP and Data Mining optionsSQL host$ sqlplus ndmain/ndmaintestrac2SQL*Plus: Release .0 - Production on 星期四 3月 22 21:14:52 2007Copyright (c) 1982, 2005, Oracle. All Rights Reserved.连接到: Oracle Database 10g Enterprise Edition Release .0 - 64bit ProductionWith the Partitioning, Real Application Clusters, OLAP and Data Mining optionsSQL host$ sqlplus ndmain/ndmaintestrac2SQL*Plus: Release .0 - Production on 星期四 3月 22 21:14:55 2007Copyright (c) 1982, 2005, Oracle. All Rights Reserved.连接到: Oracle Database 10g Enterprise Edition Release .0 - 64bit ProductionWith the Partitioning, Real Application Clusters, OLAP and Data Mining optionsSQL host$ sqlplus ndmain/ndmaintestrac2SQL*Plus: Release .0 - Production on 星期四 3月 22 21:14:59 2007Copyright (c) 1982, 2005, Oracle. All Rights Reserved.连接到: Oracle Database 10g Enterprise Edition Release .0 - 64bit ProductionWith the Partitioning, Real Application Clusters, OLAP and Data Mining optionsSQL host$ sqlplus ndmain/ndmaintestrac2SQL*Plus: Release .0 - Production on 星期四 3月 22 21:15:04 2007Copyright (c) 1982, 2005, Oracle. All Rights Reserved.连接到: Oracle Database 10g Enterprise Edition Release .0 - 64bit ProductionWith the Partitioning, Real Application Clusters, OLAP and Data Mining optionsSQL host$ sqlplus ndmain/ndmaintestrac2SQL*Plus: Release .0 - Production on 星期四 3月 22 21:15:14 2007Copyright (c) 1982, 2005, Oracle. All Rights Reserved.连接到: Oracle Database 10g Enterprise Edition Release .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 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 CONN NDMAIN/NDMAINTESTRAC已连接。SQL SELECT INSTANCE_NAME FROM V$INSTANCE;INSTANCE_NAME-testrac1SQL DISC从 Oracle Database 10g Enterprise Edition Release .0 - 64bit ProductionWith the Partitioning, Real Application Clusters, OLAP and Data Mining options 断开可以看到，连接时Oracle都会将会话连接到TESTRAC1上。下面看看连续启动多个会话，Oracle如何在实例间分配会话：E:SQLPLUS NDMAIN/NDMAINTESTRACSQL*Plus: Release .0 - Production on 星期四 3月 22 21:19:41 2007Copyright (c) 1982, 2005, Oracle. All rights reserved.连接到:Oracle Database 10g Enterprise Edition Release .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(C) 版权所有 1985-2000 Microsoft Corp.E:SQLPLUS NDMAIN/NDMAINTESTRACSQL*Plus: Release .0 - Production on 星期四 3月 22 21:19:59 2007Copyright (c) 1982, 2005, Oracle. All rights reserved.连接到:Oracle Database 10g Enterprise Edition Release .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(C) 版权所有 1985-2000 Microsoft Corp.E:SQLPLUS NDMAIN/NDMAINTESTRACSQL*Plus: Release .0 - Production on 星期四 3月 22 21:20:12 2007Copyright (c) 1982, 2005, Oracle. All rights reserved.连接到:Oracle Database 10g Enterprise Edition Release .0 - 64bit ProductionWith the Partitioning, Real 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的负载均衡功能。RAC负载均衡的简单测试（一）：/post/468/279433RAC负载均衡的简单测试（二）：/post/468/279754这篇文章讨论负载均衡与实例的忙闲是否有关。;nqy*y0 通过上一篇文档的讨论，RAC的负载均衡通过LISTENER建立的连接数有关。那个是否负载均衡只与这个因素有关，RAC的负载均衡和实例的忙闲状态是否有关呢？在TESTRAC2实例上启动两个做循环操作的SQL，模拟系统资源被大量占用的情况：SQL DECLAREITPUB个人空间.h glNS2 V_JOB NUMBER;ITPUB个人空间$V2FK!Dg M3 BEGIN)Ozt%Y fu:U.P0 4 DBMS_JOB.SUBMIT(V_JOB, ITPUB个人空间8b4x-#F b(i!S(b:h:y5 BEGINITPUB个人空间g,R.Ae3wgac*f&6 FOR I IN 1.1000 LOOPmJ/ rM38aO0 7 FOR J IN 1.1000000 LOOPITPUB个人空间1i9J)u3b0XS8 NULL;EAdrJek0 9 END LOOP;?F)MyAp j0 10 END LOOP;ITPUB个人空间xt0r(P1Upt11 END;, za9H#V0 12 TO_DATE(2007-3-22 22:00:00, YYYY-MM-DD HH24:MI:SS), ITPUB个人空间.w.MLl-Tn O$Q13 INSTANCE = 2);ITPUB个人空间_5B:Ejp.T(fU14 COMMIT;ITPUB个人空间,rp xhCyu$i15 END;$K G/W t4Dc0 16 /PL/SQL 过程已成功完成。已用时间: 00: 00: 00.53U#oe(y&i P)D1X0SQL DECLAREKbj F%zqWi0 2 V_JOB NUMBER;ITPUB个人空间F J+T8Vu8z)JY3 BEGINSI P9tP0 4 DBMS_JOB.SUBMIT(V_JOB, 0b;Cd k40 5 BEGINITPUB个人空间 DvV?$o6 FOR I IN 1.1000 LOOPITPUB个人空间 md A.G9G Or7 FOR J IN 1.1000000 LOOP44 K5X*:Hq+BJr-i$n0 8 NULL;ITPUB个人空间#|y)JR-P9 END LOOP;ITPUB个人空间S S$_(A.Ytv10 END LOOP;ITPUB个人空间 Iv-Nt:Vm0X zxFW11 END;, 8a*H(a h?gEDiZ0 12 TO_DATE(2007-3-22 22:00:00, YYYY-MM-DD HH24:MI:SS), /gO%Yo*9T6I6JJ)U0 13 INSTANCE = 2);F,R|Ot0 14 COMMIT;ITPUB个人空间/K Dg+c$S*t;J%fi?%k d15 END;%zGvIt0 16 /PL/SQL 过程已成功完成。等到两个JOB启动之后，检查操作系统的运行情况：$ sar 1 10SunOS racnode2 5.8 Generic_117350-46 sun4u 03/22/0722:00:29 %usr %sys %wio %idleITPUB个人空间.A1t5P8k N-nf:dk22:00:30 94 6 0 0?+w X.A%Db022:00:31 94 6 0 07C/aZZd-xW3pS022:00:32 95 5 0 0ITPUB个人空间#Q w oQ+W E1_22:00:33 91 9 0 0ITPUB个人空间 h:Z3V22:00:34 93 7 0 0c h ew|-b aV022:00:35 91 9 0 0)i4_K4kggi022:00:36 92 8 0 0&f)FY ZEG022:00:37 95 5 0 09pmk:dr022:00:38 94 6 0 0m.N*p1U2P 022:00:39 96 4 0 0Average 94 6 0 0NjP:htw0$ 然后进行连接实例的测试：SQL CONN NDMAIN/NDMAINTESTRAC已连接。nwh.E6SQ0SQL SELECT INSTANCE_NAME FROM V$INSTANCE;INSTANCE_NAMEITPUB个人空间J$j+a J-1X)gG$n;bd C0testrac1SQL CONN NDMAIN/NDMAINTESTRAC已连接。vnzx+z2v0SQL SELECT INSTANCE_NAME FROM V$INSTANCE;INSTANCE_NAMEITPUB个人空间/)w-XW)jJ xN-KY80Q&ns-BZ0testrac2SQL CONN NDMAIN/NDMAINTESTRAC已连接。PA#gB&Y5O%D0SQL SELECT INSTANCE_NAME FROM V$INSTANCE;INSTANCE_NAMEITPUB个人空间!h|Du*js_0M-ITPUB个人空间W t2v8P ,sVz.Ptestrac1SQL CONN NDMAIN/NDMAINTESTRAC已连接。e Gl%o m j!x*H:i+ot0SQL SELECT INSTANCE_NAME FROM V$INSTANCE;INSTANCE_NAMEITPUB个人空间$)jA;q1E9-ITPUB个人空间 E7pg 3stestrac2SQL DISC从 Oracle Database 10g Enterprise Edition Release .0 - 64bit ProductionITPUB个人空间?I-BspWith the Partitioning, Real Application Clusters, OLAP and Data Mining options 断开Hps9krM i0SQL 多个连接的测试：E:SQLPLUS NDMAIN/NDMAINTESTRACSQL*Plus: Relea