will discuss about various types of load balancing techniques that can be configured in 11g R2 RAC for workload management.
In RAC we have multiple instances of a database running on multiple servers.
– Failover : If connection to an instance fails, client should automatically connect to another instance
– Load balancing : Workload should spread across various instances to obtain maximum performance/throughput
. On client side
. On server side
TO configure failover/load balancing, tnsnames.ora should contain multiple listener addresses to connect to multiple instances e.g.
Multiple Listener addresses within a description: i.e. User is trying to connect to a service which is supported by multiple instances.
In case SCAN is used, SCAN name is used in the address which resolves to 3 SCAN listeners.
e.g.
LOAD BALANCING
————–
Load balancing in RAC implies distributing the workload over multiple instances accessing the same physical database. Two kinds load balancing can be configured
– Connect time load balancing (CTLB)
. On client side
. On server side
– On server side – Run time load balancing (RTLB)
Connect time load balancing (CTLB) : This enables user to connect to one of the instances supporting the service. The connection stays with the same instance until the user disconnects orthe session is killed. It can be configured on the client side and/or server side.
- Connect time load balancing on client side: When a user session attempts to connect to the database, Oracle Net chooses an address specified in tnsnames.ora to connect to in a random order rather than sequential order. This has the effect of clients connecting through addresses which are picked up at random and no one address is overloaded. Its configuration is quite simple. You just need to set the parameter LOAD_BALANCE=ON in the client connection definition in tnsnames.ora. For example :
RAC=
(DESCRIPTION=
(ADDRESS_LIST=
(LOAD_BALANCE=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)
)
LOAD_BALANCE parameter is set to ON by default. When this parameter is set to ON, Oracle Net Services progresses through the list of listener addresses in a random sequence, balancing the load on several listeners. When it is set to OFF, the addresses are tried out sequentially until one succeeds.
When using SCAN in the connection definition, Oracle database randomly connects to one of the available SCAN listeners in a round robin fashion and balances the connections on the three scan listeners. Here is a sample tnsnames.ora for a RAC database using SCAN.
RAC=
(DESCRIPTION=
(LOAD_BALANCE=ON)
(ADDRESS= (PROTOCOL=TCP) (HOST=SCAN-HOSTNAME) (PORT=1521))
)
(CONNECT_DATA= (SERVICE_NAME= RAC)
)
Limitation of connect time load balancing : The connection stays with the same instance for the life of a session. If connection lasts a long time, it might be possible that load of current instance increases and some other less loaded instance might be preferable. In that case we would like the connection to switch the other more appropriate instance. This can be achieved by using Run time Load Balancing (RTLB).
- Connect time load balancing on server side: After a listener receives the connection request, it can forward the request to another instance based on the connect time load balancing goal (CLB_GOAL)specified for the service. CLB_GOAL can be :
LONG(Default) - used for application connections that are connected for a long period such as third party connection pools and SQL*Forms applications. In this case,
. the listener will load balance on number of sessions
. Run time load balancing goal will not be used in this case
SHORT - used for application connections that are short in duration. This should be used with connection pools integrated with the load balancing advisory. In this case, listener uses Load Balancing Advisory (LBA) to make the connection based on CPU utilization on the node.
Limitation of Connect time load balancing on client side : The listener has no idea if the session has been assigned to an endpoint whose corresponding database server is already overloaded. Hence, timeouts can occur if the node is heavily loaded and unable to respond quickly.Hence, to overcome this problem, server side connect time load balancing needs to be configured. It is useful to spread initial connection load among all listeners inthe cluster. Client may then be redirected based on server side load balancing.
Run Time (Server side) load Balancing (RTLB): In this case, the listener routes incoming client connections according to policies and based on the current service level provided by the database instances. The listener determines the connection distribution depending upon profile statistics that are dynamically updated by PMON. The higher the load on the node, the more frequently PMON updates the load profile.Thus connections may be switched depending upon changes in cluster configuration, application wrokload overworked nodes or hangs.
The core of server side laod balancing id Dynamic service registration so that a services are registered with all the listeners. Since PMON on each node sends load profile to all the listeners with which the service is registered, all the listeners come to know about load profile of all the instances and hence the connection is forwarded to the most appropriate listener depending upon the goal of the run time load balancing.
Run time load balancing is achieved using connection pools. Work requests are automatically balanced across the pool of connections.The connection allocation is based on the current performance level provided by the database instances as indicated by the LBA FAN events. This provides load balancing at the transaction level instead of load balancing at the time of initial connection.
With server-side load balancing, the listener directs a connection request to the best instance currently providing the service by using the load balancing advisory.
Load Balancing Advisory
- is an advisory for balancing work across RAC instances
- Monitors workload activity for a service across all instances in the cluster
- Analyzes the service level for each instance based on defined metric goal
Metric: service time (GOAL_SERVICE_TIME)
Metric: throughput (GOAL_THROUGHPUT)
- Publishes FAN events recommending amount of work to be sent to each instance and data quality flag
- Default is Off.
- Directs work to where services are executing well and resources are available
- Adjusts distribution for different power nodes, different priority and shape workloads, changing demand
- Stops sending work to slow, hung, failed nodes early
How to configure server side load balancing:
1. set parameters LOCAL_LISTENER and REMOTE_LISTENER
2. set CLB_GOAL = SHORT for the service
3. set RTLB_GOAL for the service
1. set parameters LOCAL_LISTENER and REMOTE_LISTENER
LOCAL_LISTENER parameter should be set to name of the listener defined in the same node
REMOTE_LISTENER should be set to names of the listeners running on other nodes
For example in a 3 node setup
Host01 running instance orcl1
Host02 running instance orcl2
Host03 running instance orcl3
For host01,
local_listener – (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP) HOST=host01-vip)(PORT=1521))))
remote_listener – (DESCRIPTION=(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP) HOST=host02-vip)(PORT=1521)
(ADDRESS=(PROTOCOL=TCP) HOST=host03-vip)(PORT=1521))))
For host02,
local_listener – (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP) HOST=host02-vip)(PORT=1521))))
remote_listener – (DESCRIPTION=(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP) HOST=host01-vip)(PORT=1521)
(ADDRESS=(PROTOCOL=TCP) HOST=host03-vip)(PORT=1521))))
For host03,
local_listener – (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP) HOST=host03-vip)(PORT=1521))))
remote_listener – (DESCRIPTION=(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP) HOST=host02-vip)(PORT=1521)
(ADDRESS=(PROTOCOL=TCP) HOST=host01-vip)(PORT=1521))))
When we start the three instances the corresponding PMON processes get dynamically registered with all the listeners and start feeding listeners with load profile information. Now all the listeners come to know about load profile of all the instances and hence the connection is forwarded to the listener of the least loaded node.
When SCAN is used, remote_listener parameter should be set to SCAN name on all the nodes i.e.
remote_listener – myrac-cluster-scan:1521
So that PMON process of each instance registers the database services with the default listener on the local node and with each SCAN listener, which is specified by the REMOTE_LISTENER database parameter.
2. set CLB_GOAL = SHORT for the service
EXECUTE DBMS_SERVICE.MODIFY_SERVICE
(service_name => ‘sjob’ -
, clb_goal => DBMS_SERVICE.CLB_GOAL_SHORT);
OR
srvctl modify service
-s orcl_serv database -d orcl
-j SHORT // connection load balancing goal {long|short}
3. set RTLB_GOAL for the service
A request for a connection is serviced by selecting a connection based on the service goal as determined by the Load Balancing Advisory. The service goal determines whether the connection provides best service quality, that is, how efficiently a single transaction completes, or best throughput, that is, how efficiently an entire job or long-running query completes.
For RTLB, we can define service level goal which will be used only if CLB_GOAL= SHORT
There are 3 options available
NONE – Default setting, you are not taking advantage of this feature
THROUGHPUT – Work requests are directed based on throughput. THROUGHPUT should be used when the work in a service completes at homogenous rates. An example is a trading system where work requests are similar lengths. Attempts to direct work requests according to throughput. The load balancing advisory analyzes the service level for each instance based on the service time and is based on the rate that work is completed in the service plus available bandwidth to the service. For example for the use of THROUGHPUT is for workloads such as batch processes,trading system work requests have similar lengths and next job starts when the last job completes:
EXECUTE DBMS_SERVICE.MODIFY_SERVICE
(service_name => ‘sjob’ -
, goal => DBMS_SERVICE.GOAL_THROUGHPUT );
OR
srvctl modify service
-s orcl_serv database -d orcl
-B throughput // runtime connection load balancing goal { service_time|throughput | none}
SERVICE_TIME – Work requests are directed based on response time. SERVICE_TIME should be used when the work in a service completes at various rates. In this case, Load balancing advisory data is based on elapsed time for work done in the service plus available bandwidth to the service. An example for the use of SERVICE_TIME is for workloads such as internet shopping where the rate of demand changes and work requests are of differing various lengths.:
EXECUTE DBMS_SERVICE.MODIFY_SERVICE
(service_name => ‘OE’ -
, goal => DBMS_SERVICE.GOAL_SERVICE_TIME -
);
OR
srvctl modify service
-s orcl_serv database -d orcl
-B service_time // runtime connection load balancing goal { service_time|throughput | none}
You can see the goal settings for a service in the data dictionary and in the DBA_SERVICES, V$SERVICES, and V$ACTIVE_SERVICES views.
SUMMARY:
Workload management involves :
– Failover : If connection to an instance fails, client should automatically connect to another instance
– Load balancing : Workload should spread across various instances to obtain maximum performance/throughput
Load balancing can be :
– Connect time load balancing (CTLB)
. On client side
. On server side
– Run time load balancing (RTLB)
TO configure failover/load balancing, tnsnames.ora should contain multiple listener addresses to connect to multiple instances
Connect time load balancing (CTLB) : This enables user to connect to one of the instances supporting the service. The connection stays with the same instance until the user disconnects orthe session is killed. It can be configured on the client side and/or server side.
- Connect time load balancing on client side: When a user session attempts to connect to the database, Oracle Net chooses an address specified in tnsnames.ora to connect to in a random order rather than sequential order. This has the effect of clients connecting through addresses which are picked up at random and no one address is overloaded. Its configuration is quite simple. You just need to set the parameter LOAD_BALANCE=ON in the client connection definition in tnsnames.ora.
Limitation of Connect time load balancing (CTLB) on client side : The listener has no idea if the session has been assigned to an endpoint whose corresponding database server is already overloaded.
- Connect time load balancing (CTLB) on server side: After a listener receives the connection request, it can forward the request to another instance based on the connect time load balancing goal (CLB_GOAL)specified for the service. CLB_GOAL can be : LONG or SHORT
LONG(Default) – In this case,
. the listener will load balance on number of sessions
. Run time load balancing goal will not be used in this case
SHORT - used for application connections that are short in duration. This should be used with connection pools integrated with the load balancing advisory. In this case, listener uses Load Balancing Advisory (LBA) to make the connection based on CPU utilization on the node.
Limitation of connect time load balancing : The connection stays with the same instance for the life of a session. If connection lasts a long time, it might be possible that load of current instance increases and some other less loaded instance might be preferable.
Run Time (Server side) load Balancing (RTLB): In this case, the listener routes incoming client connections according to policies and based on the current service level provided by the database instances. With server-side load balancing, the listener directs a connection request to the best instance currently providing the service by using the load balancing advisory.
To configure server side load balancing:
1. set parameters LOCAL_LISTENER and REMOTE_LISTENER
2. set CLB_GOAL = SHORT for the service
3. set RTLB_GOAL for the service
