|
|||||||||||||
|
|
In addition, the CLB_GOAL parameter provides yet another load balancing method: It tells the LBA how to perform connection load balancing. See Table 2 for a list of its acceptable enumerated constant values and the impact of setting either.
Depending on the settings for these two parameters, the LBA uses the metrics shown in Table 3 to make its determination of which instance should receive the next set of incoming connections:
Proof of Concept: LBA in Oracle 10gR2 RACTo enable my RAC database environment for LBA, I’ll first activate three different levels of load balancing using DBMS_SERVICE.MODIFY_SERVICE against the three new services I just created:
Listing 5 shows the code I’ve used to activate LBA management for these services, as well as a query against the DBA_SERVICES view plus the resulting output that verifies the proper settings for GOAL and CLB_GOAL for these services. Now that these three services are configured for LBA, I’ll use a combination of shell scripts to place a load on one of the two nodes for this RAC clustered database and then attempt to connect to both nodes in the cluster using the three RAC services. I’ll first execute the ConstantLoadGenerator.sh shell script to create 100 sessions that execute a SQL script that places a moderately heavy load on the database’s CPU, and then I’ll execute shell script RandomLoadGenerator.sh to simulate 40 additional user connections for one of the three services. How can I determine what the impact will be of running these tests? Oracle 10gR2’s GV$SERVICEMETRIC global view shows each service’s “goodness” on each instance in the RAC clustered database, the service’s “predicted” goodness (or delta), and summary statistics on how much resources the service consumed during the time period. Listing 6 shows the query I’ve constructed against that view; here’s the result of that query before I started my evaluations:
Current Service-Level Metrics
(From GV$SERVICEMETRIC)
Pred- CPU Elpsd # 0f
icted Time Time User
Good- Per Per Calls DBTime
Service Inst Good ness Call Call Per Per
Name Start Time End Time ID ness Incr Flags (mus) (mus) Second Second
-------- ---------- ---------- ----- ------- ------- ------- --------- --------- --------- ---------
Here’s the results of the first test against the ADHOC RAC service. While my tests executed, I noticed that Oracle 10gR2 continued to route connections to each node in the cluster regardless of the load on each instance, and the number of connections remained relatively constant on both nodes as new connections arrived. Here are the results of a query against GVSERVICE_METRIC for that test:
>>> Output after ADHOC unit testing:
Current Service-Level Metrics
(From GV$SERVICEMETRIC)
Pred- CPU Elpsd # 0f
icted Time Time User
Good- Per Per Calls DBTime
Service Inst Good ness Call Call Per Per
Name Start Time End Time ID ness Incr Flags (mus) (mus) Second Second
-------- ---------- ---------- ----- ------- ------- ------- --------- --------- --------- ---------
For the tests I ran against the DSS and OLTP RAC services, however, I noticed that Oracle 10gR2 definitely tended to route connections to the node that was least loaded, and this was reflected in the query results against the GVSERVICE_METRIC global view for these tests:
>>> Output after DSS unit testing:
Current Service-Level Metrics
(From GV$SERVICEMETRIC)
Pred- CPU Elpsd # 0f
icted Time Time User
Good- Per Per Calls DBTime
Service Inst Good ness Call Call Per Per
Name Start Time End Time ID ness Incr Flags (mus) (mus) Second Second
-------- ---------- ---------- ----- ------- ------- ------- --------- --------- --------- ---------
ADHOC 15:12:48 15:12:53 1 0 1 0 0 0 0 0
ADHOC 15:12:37 15:12:42 2 0 1 0 0 0 0 0
ADHOC 15:11:06 15:12:06 2 0 1 0 0 0 0 0
ADHOC 15:11:02 15:12:02 1 0 1 0 49585 322550 2 54
>>> Output after OLTP unit testing:
Current Service-Level Metrics
(From GV$SERVICEMETRIC)
Pred- CPU Elpsd # 0f
icted Time Time User
Good- Per Per Calls DBTime
Service Inst Good ness Call Call Per Per
Name Start Time End Time ID ness Incr Flags (mus) (mus) Second Second
-------- ---------- ---------- ----- ------- ------- ------- --------- --------- --------- ---------
ADHOC 15:14:48 15:14:53 1 0 1 0 0 0 0 0
ADHOC 15:14:36 15:14:42 2 0 1 0 0 0 0 0
ADHOC 15:13:06 15:14:06 2 0 1 0 0 0 0 0
ADHOC 15:13:02 15:14:02 1 0 1 0 0 0 0 0
DSS 15:14:48 15:14:53 1 100 100 4 0 0 0 0
DSS 15:14:36 15:14:42 2 100 100 4 0 0 0 0
DSS 15:13:06 15:14:06 2 100 100 4 0 0 0 0
DSS 15:13:02 15:14:02 1 100 100 4 59223 173148 0 3
ConclusionWith the advent of the Load Balancing Advisory in Oracle 10gR2, it’s now possible to insure that an unexpectedly dramatic increase in a RAC service’s workload does not overwhelm any single node of an Oracle 10gR2 RAC clustered database. Oracle 10gR2 leverages proven, existing RAC components like FAN and ONS event publishing to implement this enhancement. Finally, several new and enhanced data dictionary views provide excellent feedback to measure the efficiency of the Load Balancing Advisory. Download the SQL scripts and shell scripts for this article. References and Additional ReadingEven though I’ve hopefully provided enough technical information in this article to encourage you to explore with these features, I also strongly suggest that you first review the corresponding detailed Oracle documentation before proceeding with any experiments. Actual implementation of these features should commence only after a crystal-clear understanding exists. Please note that I’ve drawn upon the following Oracle 10gR2 documentation for the deeper technical details of this article: B14197-03 Oracle 10gR2 Clusterware and Real Application Clusters Administration and Deployment Guide B14203-08 Oracle 10gR2 Clusterware and Real Application Clusters Installation Guide for Linux B14212-02 Oracle 10gR2 Net Services Administrator’s Guide B14213-01 Oracle 10gR2 Net Services Reference B14237-02 Oracle 10gR2 Reference B14258-01 Oracle 10gR2 PL/SQL Packages and Types Reference B14210-02 Oracle 10gR2 High Availability Overview B25159-01 Oracle 10gR2 High Availability Best Practices And these MetaLink documents are also invaluable to obtaining a deeper understanding of how the Load Balancing Advisory has been implemented in Oracle 10gR2: 226880.1 Configuration of Load Balancing and Transparent Application Failover
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
 |
