Frederick Tang Weblog

Recently, I took an interest in studying how various load balancing features work in a 10g RAC. I thought Jim has written a very good article on this, in a language most people can understand. In my opinion, there are 3 types of load balancing on a 10g RAC. I say in my opinion because Run Time Load Balancing relies on Server Side Load Balancing configurations, but I have separated it out anyways.

1. Client Side Load Balancing
2. Server Side Load Balancing
3. Run Time Load Balancing (using Implicit Connection Cache)

In this post, I wanted to test for evidence of Client Side Load Balancing, using Client Side Oracle Net Tracing.

Definition

Client Side Load Balancing is by definition “evenly spreads new connection requests across all listeners”, and “Oracle Database randomly selects an address in the address list and connects to that node’s listener. This provides a balancing of client connections across the available listeners in the cluster.” [source]. Another definition, “whereby if more than one listener services a single database, a client can randomly choose between the listeners for its connect requests. This randomization enables all listeners to share the burden of servicing incoming connect requests.” [source].

Client Side Load Balancing is configured by adding LOAD_BALANCE=ON in tnsnames.ora file. The Net Services Reference tells us how and were to embed this parameter – “embed this parameter under either the DESCRIPTION_LIST parameter, the DESCRIPTION parameter, or the ADDRESS_LIST parameter.” [source]. Note however, whether you place LOAD_BALANCE under ADDRESS_LIST or outside, will make a different in terms of load balancing ADDRESS_LISTs or load balancing ADDRESSes.

Configuration

I have a 2-node 10g RAC cluster, and for load balancing, I have the following entry in my tnsnames.ora file.

MODDB=
 (description=
  (load_balance=on)
   (address=(protocol=tcp)(host=moddb1-vip)(port=2521))
   (address=(protocol=tcp)(host=moddb2-vip)(port=2521))
  (connect_data=
      (service_name=MODDB)
  )
 )

I want to trace the Client Side Load Balancing behaviour from the client side, so I enable Oracle net tracing by adding the following lines in sqlnet.ora [source], and I should fine trace files for each physical connection made under $ORACLE_HOME/network/trace.

TRACE_LEVEL_CLIENT = USER
TRACE_FILE_CLIENT = SQLTRC

Testing a connection

We can first test one connection…

$ sqlplus -s tester/tester@MODDB

exit

Now take a look at the trace file produced, under $ORACLE_HOME/network/trace. An inspection of of the trace file shows two entries that are of interest (it is possible to observe server-side load balancing as well, but that’s a topic for another post):

niotns: Calling address: (description=(load_balance=on)(failover=on)(address=(protocol=tcp)(host=moddb1-vip)(port=2521))(address=(protocol=tcp)(host=moddb2-vip)(port=2521))(connect_data=(service_name=MODDB)(CID=(PROGRAM=e:\oracle\product\10.2.0\client_1\bin\sqlplus.exe)(HOST=LADDO)(USER=tester))))
nsc2addr: (DESCRIPTION=(load_balance=on)(failover=on)(address=(protocol=tcp)(host=moddb2-vip)(port=2521))(connect_data=(service_name=MODDB)(CID=(PROGRAM=e:\oracle\product\10.2.0\client_1\bin\sqlplus.exe)(HOST=LADDO)(USER=tester))))

Unfortunately, I couldn’t find much documentation on how to read a trace file, I can only guess…

* niotns entry shows a lookup of tns information based on the service name I supplied “MODDB”.
* nsc2addr entry shows Oracle Net connects to listener on moddb2-vip.

It’s difficult to observe load balancing with just one connection, so I wrote a script to run 1000 connections, using Bash Shell scripting:

#!/bin/bash

for a in {1..1000}
do
echo $a
sqlplus -s tester/tester@MODDB<<EOF
EOF
done

exit 0

Examining the traces

Extending the idea above, we can use a combination of grep, and wc to see the result of load balancing. For some reason, each connection produced 2 trace files, so we have 2000 trace files for 1000 connections.

$ ls -l *.trc |wc -l
2000

$ grep nsc2addr *.trc | grep load_balance |grep moddb1-vip |wc -l
498

$ grep nsc2addr *.trc | grep load_balance |grep moddb2-vip |wc -l
502

We can see over 1000 connections, 498 were made to moddb1-vip listener, whilst 502 were made to moddb2-vip listener. It is a fairly even distribution. But just how random is it? I have used the output of the trace files, and plot the first 100 connections on a scattered chart. The first note one can observe is that, these connections are not in a round-robin fashion. (1 = moddb1-vip; 2 = moddb2-vip)

Conclusion

As demonstrated above, it is quite simple to setup Oracle Net tracing on the client, to test and show whether Client Side Load Balancing is working properly. It is worth to note (again), that this type of load balancing has nothing to do with balancing server side load. It is to do with balancing load across the listeners.