RAC: Client & Server connection Load balancing in Oracle

Connection Workload management is one of the key aspects when you have RAC instances as you want to distribute the connections to specific nodes/instance or those have less load.

Oracle from 10g onwards (9i as well) has two features to provide the connection load balancing

Client Side load balancing (also called as connect time load balancing)

Server side load balancing (also called as Listener connection load balancing)

Client Side load balancing:- Oracle client side load balancing feature enables clients to randomize the connection requests among all the available listeners based on their load.

An tns entry that contains all nodes entries and use load_balance=on (default its on) will use the connect time load balancing or client side load balancing.

Sample Client Side TNS Entry:-

finance =
(DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(HOST = myrac2-vip)(PORT = 2042))
     (ADDRESS = (PROTOCOL = TCP)(HOST = myrac1-vip)(PORT = 2042))
     (ADDRESS = (PROTOCOL = TCP)(HOST = myrac3-vip)(PORT = 2042))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
     (SERVER = DEDICATED)
     (SERVICE_NAME = FINANCE) (FAILOVER=ON)
(FAILOVER_MODE =  (TYPE = SELECT) (METHOD = BASIC) (RETRIES = 180) (DELAY = 5))
)
)

Server side load balancing:- This improves the connection performance by balancing the number of active connections among multiple instances and dispatchers. In a single instance environment (shared servers), the listener selects the least dispatcher to handle the incoming client requests. In a rac environments, PMON is aware of all instances load and dispatchers , and depending on the load information PMON redirects the connection to the least loaded node.

In a shared server configuration, a listener selects a dispatcher in the following order:-

• 1. Least Loaded node
• 2. Least loaded instance
• 3. Least loaded dispatcher for that instance

In a dedicated mode, a listener select an instance in the following order:-

• 1. Least loaded node
• 2. Least loaded instance

In a RAC environment, *.remote_listener parameter which is a tns entry containing all nodes addresses need to set to enable the load balance advisory updates to PMON.

Sample Tns entry should be in an instances of RAC cluster,

local_listener=LISTENER_MYRAC1
remote_listener = LISTENERS_MYRACDB

TNSNAMES.ORA entries: Below entries should be there on all nodes tnsnames.ora file.

LISTENERS_MYRACDB =
(ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = myrac1-vip)(PORT = 2042))
     (ADDRESS = (PROTOCOL = TCP)(HOST = myrac3-vip)(PORT = 2042))
     (ADDRESS = (PROTOCOL = TCP)(HOST = myrac2-vip)(PORT = 2042))
)

LISTENER_MYRAC3 =
(ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = myrac3-vip)(PORT = 2042))
)

LISTENER_MYRAC2 =
(ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = myrac2-vip)(PORT = 2042))
)

LISTENER_MYRAC1 =
(ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = myrac1-vip)(PORT = 2042))
)

Listener route the incoming connections to least loaded nodes based on the load profile statistics that are dynamically updated by PMON. The more updates the busier the node, Updates can occur from 3 secs to 10 mins frequency and can be visible in listener log as follows:-

16-SEP-2012 01:22:58 * service_update * DEVPRD1 * 0

Further you can trace the pmon for load balancing updates from the listener

Place 10257 level 16 event and restart the instance and check pmon trace file.

Trace contents

kmmgdnu: MYRACDB goodness 27
kmmgdnu: MYRACDB
goodness=27, delta=1,
flags=0x4:unblocked/not overloaded, update=0x6:G/D/-
kmmlrl: 65 processes
kmmlrl: node load 687
kmmlrl: instance load 35
kmmlrl: nsgr update returned 0
flags=0x4:unblocked/not overloaded, update=0x6:G/D/-
kmmgdnu: MYRACDBXDB
goodness=0, delta=1,
flags=0x5:unblocked/not overloaded, update=0x6:G/D/-
kmmgdnu: MYRACDB
goodness=27, delta=1,
flags=0x4:unblocked/not overloaded, update=0x6:G/D/-
kmmlrl: 66 processes
kmmlrl: node load 775
kmmlrl: nsgr update returned 0
kmmgdnu: MYRACDB
goodness=28, delta=1,
flags=0x4:unblocked/not overloaded, update=0x6:G/D/-
kmmlrl: 70 processes
kmmlrl: node load 847
kmmlrl: nsgr update returned 0
*** 2012-09-16 03:47:26.891
err=-319 tbtime=0 etime=319 srvs=1 nreqs=0 sreq

As you see above the node load increases from 687 to 847 and the the process from 65 to 70

'Node load' gives an indication of how busy the machine is. By default, 'node load' is the primary factor for determining where new connections will go.

Hope its clear about two Connection load balancing features in Oracle,

In this post you have understood,

1) Client & server side load balancing with normal database tns connection strings

2) How pmon get updated the load information

Next Post :

What is load balance advisory and how does it work. How pmon knows above goodness and servicetime etc.

-Thanks

Geek DBA