Client Side Load Balancing for Oracle RAC

Client side load balancing balances the connection requests across the RAC instances. Set via LOAD_BALANCE parameter in the the net service name within a TNSNAMES.ORA file, it is just a random selection of the addresses in the address_list section of the net service name.

When clients connect using SCAN, Oracle Net automatically load balances client connection requests across the three IP addresses you defined for the SCAN, unless you are using EZConnect.

The tests are held under the environment as below, we use both 11gR2 client and 10gR2 client for testing the behaviours of client side load balancing by using SCAN and non-SCAN hostname.

                  GI: 12.1.0.2.0
4 nodes RAC:  11.2.0.4
Oracle Client: 11.2.0.4 and 10.2.0.5

This shell script is used to create multiple concurrent sessions to simulate database connections from clients.

$ cat create_multi_sessions.sh
#!/bin/bash
for ((i=1; i <= 40 ; i++))
do
nohup sqlplus -S jamesh/”Welcome123#”@RACTEST << EOT &
begin
dbms_lock.sleep(20);
end;
/
EOT
done

The service name is RAC_TEST running on node1 and node4, while available nodes are node2 and node3.

$ srvctl status service -s RAC_TEST -d TESTDB
Service RAC_TEST is running on instance(s) TESTDB1,TESTDB4

  • By using VIPs without “LOAD_BALANCE=ON”,  get same results from both 11gR2 and 10gR2 client. All connections go to first node where service RAC_TEST is running on.
 RACTEST_VIP =
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST =node1-vip)(PORT = 1521))
 (ADDRESS = (PROTOCOL = TCP)(HOST =node2-vip)(PORT = 1521))
 (ADDRESS = (PROTOCOL = TCP)(HOST =node3-vip)(PORT = 1521))
 (ADDRESS = (PROTOCOL = TCP)(HOST =node4-vip)(PORT = 1521))
 (CONNECT_DATA =
 (SERVICE_NAME = RAC_TEST)
 )
 )
SQL>select inst_id, username, count(*) from gv$session where username='JAMESH' group by inst_id, username;

INST_ID USERNAME COUNT(*)
---------- ------------------------------ ----------
1 JAMESH 40
  • By using VIPs adding “LOAD_BALANCE=ON”,  get same results from both 11gR2 and 10gR2 client. All connections are balanced on two nodes where service RAC_TEST are running on.
 RACTEST_VIP =
 (DESCRIPTION = ( LOAD_BALANE = ON )
 (ADDRESS = (PROTOCOL = TCP)(HOST =node1-vip)(PORT = 1521))
 (ADDRESS = (PROTOCOL = TCP)(HOST =node2-vip)(PORT = 1521))
 (ADDRESS = (PROTOCOL = TCP)(HOST =node3-vip)(PORT = 1521))
 (ADDRESS = (PROTOCOL = TCP)(HOST =node4-vip)(PORT = 1521))
 (CONNECT_DATA =
 (SERVICE_NAME = RAC_TEST)
 )
 )

SQL>select inst_id, username, count(*) from gv$session where username='JAMESH' group by inst_id, username;

INST_ID USERNAME COUNT(*)
---------- ------------------------------ ----------
1 JAMESH 20
4 JAMESH 20
  • By using SCAN or SCAN IPs with or without “LOAD_BALANCE=ON”, get same results from both 11gR2 and 10gR2 client. All connections are balanced on two nodes where service RAC_TEST are running on.
RACTEST_SCAN =
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST =clu-scan)(PORT = 1521))
 (CONNECT_DATA =
 (SERVICE_NAME = RAC_TEST)
 )
 )

RACTEST_SCAN_IP =
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST =10.10.10.80)(PORT = 1521))
 (ADDRESS = (PROTOCOL = TCP)(HOST =10.10.10.81)(PORT = 1521))
 (ADDRESS = (PROTOCOL = TCP)(HOST =10.10.10.82)(PORT = 1521))
 (CONNECT_DATA =
 (SERVICE_NAME = RAC_TEST)
 )
 )


 INST_ID USERNAME COUNT(*)
 ---------- ------------------------------ ----------
 1 JAMESH 20
 4 JAMESH 20

From the test, we can see the connections are always balanced on instances when SCAN or SCAN IPS are used in TNS entries. Below is the explanation from Oracle Doc.

Client Load Balancing using SCAN

When a SCAN Listener receives a connection request, the SCAN Listener will check for the least loaded instance providing the requested service. It will then re-direct the connection request to the  local listener on the node where the least loaded instance is running. Subsequently, the client will be given the address of the local listener. The local listener will finally create the connection to the database instance.

Advertisements
This entry was posted in GI and RAC and tagged , , . Bookmark the permalink.

One Response to Client Side Load Balancing for Oracle RAC

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s