RAC负载均衡的简单测试（二）
Rac环境安装完成之后，打算简单测试一下Oracle RAC的负载均衡功能。
这篇继续讨论负载均衡的特性。
RAC负载均衡的简单测试（一）：http://yangtingkun.itpub.net/post/468/279433
上一篇的测试中可以看到，当两个实例的负载相当的时候，新连接会平均分配到两个实例上去。
下面看看如果其中一个实例的连接数明显大于另一个实例的情况。增加连接TESTRAC2实例的连接数，这里使用的方法是在racnode2数据库上使用直接连接的方式：
$ sqlplus ndmain
SQL*Plus: Release 10.2.0.2.0 - Production on 星期四 3月 22 19:24:23 2007
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
输入口令:
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
SQL> host
$ sqlplus ndmain
SQL*Plus: Release 10.2.0.2.0 - Production on 星期四 3月 22 19:24:28 2007
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
输入口令:
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
SQL> host
$ sqlplus ndmain
SQL*Plus: Release 10.2.0.2.0 - Production on 星期四 3月 22 19:24:33 2007
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
输入口令:
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
SQL> host
$ sqlplus ndmain
SQL*Plus: Release 10.2.0.2.0 - Production on 星期四 3月 22 19:24:37 2007
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
输入口令:
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
SQL> host
$ sqlplus ndmain
SQL*Plus: Release 10.2.0.2.0 - Production on 星期四 3月 22 19:24:44 2007
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
输入口令:
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
SQL> host
$ sqlplus ndmain
SQL*Plus: Release 10.2.0.2.0 - Production on 星期四 3月 22 19:24:48 2007
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
输入口令:
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
SQL> host
$ sqlplus ndmain
SQL*Plus: Release 10.2.0.2.0 - Production on 星期四 3月 22 19:24:54 2007
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
输入口令:
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
SQL> host
$ sqlplus ndmain
SQL*Plus: Release 10.2.0.2.0 - Production on 星期四 3月 22 19:25:00 2007
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
输入口令:
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
SQL>
查询目前数据库的连接情况：
SQL> SELECT INST_ID, COUNT(*) FROM GV$SESSION GROUP BY INST_ID;
INST_ID COUNT(*)
---------- ----------
1 31
2 38
下面尝试多次连接，并检查连接的实例：
SQL> CONN NDMAIN/NDMAIN@TESTRAC已连接。
SQL> SELECT INSTANCE_NAME FROM V$INSTANCE;
INSTANCE_NAME
----------------
testrac2
SQL> CONN NDMAIN/NDMAIN@TESTRAC已连接。
SQL> SELECT INSTANCE_NAME FROM V$INSTANCE;
INSTANCE_NAME
----------------
testrac1
SQL> CONN NDMAIN/NDMAIN@TESTRAC已连接。
SQL> SELECT INSTANCE_NAME FROM V$INSTANCE;
INSTANCE_NAME
----------------
testrac2
SQL> CONN NDMAIN/NDMAIN@TESTRAC已连接。
SQL> SELECT INSTANCE_NAME FROM V$INSTANCE;
INSTANCE_NAME
----------------
testrac1
SQL> CONN NDMAIN/NDMAIN@TESTRAC已连接。
SQL> SELECT INSTANCE_NAME FROM V$INSTANCE;
INSTANCE_NAME
----------------
testrac2
发现连接到两个实例的概率仍然是一样的。看看连续启动多个连接的情况：
E:>SQLPLUS NDMAIN/NDMAIN@TESTRAC
SQL*Plus: Release 10.2.0.1.0 - Production on 星期四 3月 22 19:27:05 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
SQL> SELECT INSTANCE_NAME FROM V$INSTANCE;
INSTANCE_NAME
----------------
testrac1
SQL> HOST
Microsoft Windows 2000 [Version 5.00.2195]
(C) 版权所有 1985-2000 Microsoft Corp.
E:>SQLPLUS NDMAIN/NDMAIN@TESTRAC
SQL*Plus: Release 10.2.0.1.0 - Production on 星期四 3月 22 19:27:18 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
SQL> SELECT INSTANCE_NAME FROM V$INSTANCE;
INSTANCE_NAME
----------------
testrac2
SQL> HOST
Microsoft Windows 2000 [Version 5.00.2195]
(C) 版权所有 1985-2000 Microsoft Corp.
E:>SQLPLUS NDMAIN/NDMAIN@TESTRAC
SQL*Plus: Release 10.2.0.1.0 - Production on 星期四 3月 22 19:27:31 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
SQL> SELECT INSTANCE_NAME FROM V$INSTANCE;
INSTANCE_NAME
----------------
testrac1
SQL> HOST
Microsoft Windows 2000 [Version 5.00.2195]
(C) 版权所有 1985-2000 Microsoft Corp.
E:>SQLPLUS NDMAIN/NDMAIN@TESTRAC
SQL*Plus: Release 10.2.0.1.0 - Production on 星期四 3月 22 19:27:40 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
SQL> 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 31
2 30
然后以TESTRAC2为服务名，连接到RAC的TESTRAC2实例上：
$ sqlplus ndmain/ndmain@testrac2
SQL*Plus: Release 10.2.0.2.0 - Production on 星期四 3月 22 21:14:44 2007
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
SQL> host
$ sqlplus ndmain/ndmain@testrac2
SQL*Plus: Release 10.2.0.2.0 - Production on 星期四 3月 22 21:14:52 2007
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
SQL> host
$ sqlplus ndmain/ndmain@testrac2
SQL*Plus: Release 10.2.0.2.0 - Production on 星期四 3月 22 21:14:55 2007
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
SQL> host
$ sqlplus ndmain/ndmain@testrac2
SQL*Plus: Release 10.2.0.2.0 - Production on 星期四 3月 22 21:14:59 2007
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
SQL> host
$ sqlplus ndmain/ndmain@testrac2
SQL*Plus: Release 10.2.0.2.0 - Production on 星期四 3月 22 21:15:04 2007
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
SQL> host
$ sqlplus ndmain/ndmain@testrac2
SQL*Plus: Release 10.2.0.2.0 - Production on 星期四 3月 22 21:15:14 2007
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
SQL>
再次测试连接实例的情况：
SQL> SELECT INST_ID, COUNT(*) FROM GV$SESSION GROUP BY INST_ID;
INST_ID COUNT(*)
---------- ----------
1 31
2 36
SQL> CONN NDMAIN/NDMAIN@TESTRAC已连接。
SQL> SELECT INSTANCE_NAME FROM V$INSTANCE;
INSTANCE_NAME
----------------
testrac1
SQL> CONN NDMAIN/NDMAIN@TESTRAC已连接。
SQL> SELECT INSTANCE_NAME FROM V$INSTANCE;
INSTANCE_NAME
----------------
testrac1
SQL> CONN NDMAIN/NDMAIN@TESTRAC已连接。
SQL> SELECT INSTANCE_NAME FROM V$INSTANCE;
INSTANCE_NAME
----------------
testrac1
SQL> DISC从 Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options 断开
可以看到，连接时Oracle都会将会话连接到TESTRAC1上。下面看看连续启动多个会话，Oracle如何在实例间分配会话：
E:>SQLPLUS NDMAIN/NDMAIN@TESTRAC
SQL*Plus: Release 10.2.0.1.0 - Production on 星期四 3月 22 21:19:41 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
SQL> SELECT INSTANCE_NAME FROM V$INSTANCE;
INSTANCE_NAME
----------------
testrac1
SQL> HOST
Microsoft Windows 2000 [Version 5.00.2195]
(C) 版权所有 1985-2000 Microsoft Corp.
E:>SQLPLUS NDMAIN/NDMAIN@TESTRAC
SQL*Plus: Release 10.2.0.1.0 - Production on 星期四 3月 22 21:19:59 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
SQL> SELECT INSTANCE_NAME FROM V$INSTANCE;
INSTANCE_NAME
----------------
testrac1
SQL> HOST
Microsoft Windows 2000 [Version 5.00.2195]
(C) 版权所有 1985-2000 Microsoft Corp.
E:>SQLPLUS NDMAIN/NDMAIN@TESTRAC
SQL*Plus: Release 10.2.0.1.0 - Production on 星期四 3月 22 21:20:12 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
SQL> SELECT INSTANCE_NAME FROM V$INSTANCE;
INSTANCE_NAME
----------------
testrac1
SQL>
根据上面的测试可以看出，Oracle的负载均衡和实例通过LISTENER建立的连接数有关。
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/4227/viewspace-69237/，如需转载，请注明出处，否则将追究法律责任。
