Load Balancing and Failover with Oracle 10gR2 RAC
Barb Lundhild Oracle Corporation RAC Product Management
The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle.
Page 1
1
Confused?
a cin la n g
i ss Se on ad lo
Introduction
Key Items to Load Balancing with Oracle • • • • Oracle Net Services – Connection Load Balancing Automatic Workload Management Connection Pools Parallel Execution
Page 2
2
Li
d bala ion loa ct Conne
st
en er
ncing
lo
ad
ba la
Runtime connection load balancing
nc in g
S
r-si rve e
d
db lo a e
g in nc la ba
Client load b alanci ng
Single Instance
What do you do when there is more than one?
Real Real Application Application Clusters Clusters
Page 3
3
Oracle Net Services – Connection Load Balancing • Two types of load balancing
• Client Side Load Balancing • Server Side Load Balancing
• Best Practice is to set up both in a Real Application Clusters environment
Client-Side Connection Load Balancing
sales.us.acme.com= (DESCRIPTION= (ADDRESS_LIST= (LOAD_BALANCE=on) (ADDRESS= (PROTOCOL=tcp) (HOST=sales1-vip) (PORT=1521)) (ADDRESS= (PROTOCOL=tcp) (HOST=sales2-vip) (PORT=1521))) (CONNECT_DATA= (SERVICE_NAME= sales.us.acme.com)))
Listeners
Clients
Page 4
4
Server Side Connection Load Balancing
• Listener will load balance incoming connections across all nodes actively providing a service • Directs connection to the least loaded node • Use parameters LOCAL_LISTENER, REMOTE_LISTENER to ensure instances register services with all listeners
Server Side Connection Load Balancing
LISTENER
Service OLTP?
PMDB1
Application Server Network
on N1 on N2 on N3
RAC Database Network
PMDB2 PMDB3
Page 5
5
Connection Load Balancing
LISTENER
Service OLTP? PMDB1 on N1
tw Ne ork
Listeners RAC Database
Clients
What if there are Multiple Applications?
Page 6
6
Automatic Workload Management
• Application workloads can be defined as Services
• • • • • • Individually managed and controlled Assigned to instances during normal startup On instance failure, automatic re-assignment Service performance individually tracked Finer grained control with Resource Manager Integrated with other Oracle tools / facilities (E.G. Scheduler, Streams)
Use EM to Define Services
Page 7
7
Automatic Workload Management
Order Entry
Spare
Supply Chain
Normal Server Allocation
Page 8
8
Automatic Workload Management
Order Entry
Spare
Supply Chain
Server Failure
Automatic Workload Management
Order Entry
Supply Chain
Re-allocate spare to Order Entry
Page 9
9
Automatic Workload Management
Supply Chain Order Entry
Application Resource Requirements Grow
Many Services, one DB
Node-1 Node-2 Node-3 Node-4 Node-5 Node-6
OLTP 1 OLTP 2 OLTP 3 OLTP 4
Repor ting Batch
DW
Page 10
10
Distributed Transaction Processing with RAC
• • All branches of a tightly coupled distributed transaction must run on same instance Better handling of distributed transactions (XA, MS DTC) through DTP services
dbms_service.modify_service (service_name=>‘my.dtp.enabled.service’, DTP=>TRUE)
• •
Service has 1 “preferred instance” and guarantee only one active instance Create more services than RAC instances, assign groups of transactions to each service
Improved XA Support with RAC
• Tightly coupled Global txns can span RAC instances • TRUE by default • Allows the units of work performed across these Oracle RAC instances to share resources and act as a single transaction I.E. tightly coupled • 2PC requests can be sent to any node in the cluster
CLUSTERWIDE_DISTRIBUTED_TRANSACTIONS,
which is
Page 11
11
Services Best Practices
• Always connect with a service • If you do a srvctl stop database, you must do a srvctl start service after the srvctl start database
• Use server-side callout to enforce startup
• XA Applications should use DTP services • Service configuration can be changed dynamically
Connection Pools
c c c c c c cc c c c c
Application Connection Pool Real Application Clusters
Page 12
12
Load Balancing Advisory
• Load Balancing Advisory is an advisory for balancing work across RAC instances. • Load balancing advice • Is available to ALL applications that send work. • 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.
Load Balancing Advisory in Nut Shell
• Uses DBMS_SERVICE.GOAL
• Service time – weighted moving average of elapsed time • Throughput – weighted moving average of throughput
• AWR
• • • • Calculates goodness locally, forwards to master mmon Master mmon builds advisory for distribution of work Records advice to SYS$SERVICE_METRICS Posts load balancing advice via FAN event to AQ, PMON, ONS
• Load Balancing Advisory Users
• Use percentages and flags to send work, gravitate work.
Page 13
13
GV$SERVICEMETRIC
Service Time CPU_TIME DB_TIME -------- -------4525.497 22980.72 6111.93 124837.4 0 0 1750 1750 0 0
Service --------------SRV1 SRV1 SSKYDB SSKYDB SYS$BACKGROUND
Instance ---------SSKY1 SSKY2 SSKY1 SSKY2 SSKY1
mSec/Call -----------22981 124837 0 1750 0
THROUGHPUT ---------202.5948 141.3127 0 1.158301 0
set pages 60 space 2 lines 132 num 8 verify off feedback off column user_data heading "AQ Service Metrics" format A60 wrap break on SERVICE_NAME skip 1 select to_char(ENQ_TIME, 'HH:MI:SS') Enq_time, user_data from SYS.SYS$SERVICE_METRICS_TAB order by 1;
Load Balancing Advisory
Page 14
14
Fast Application Notification (FAN)
• RAC notification mechanism which let applications know about service & node events (UP or DOWN events) and Load Balancing Advisory events • Events published using Oracle Notification Service (JDBC) and Advanced Queues (OCI, ODP.NET)
Runtime Connection Load Balancing
• Solves the Connection Pool problem! • Easiest way to take advantage of Load Balancing Advisory • No application changes required • No extra charge software to buy • Enabled by parameter on datasource definition • Supported by JDBC and ODP.NET
Page 15
15
Runtime Connection Load Balancing
• Client connection pool is integrated with RAC load balancing advisory • When application does “getConnection”, the connection given is the one that will provide the best service. • Policy defined by setting GOAL on Service • Need to have Connection Load Balancing
Load Balancing Advisory Goals
• THROUGHPUT – Work requests are directed based on throughput. • SERVICE_TIME – Work requests are directed based on response time. • None – Default setting, turn off advisory
execute dbms_service.modify_service (service_name => 'test' , aq_ha_notifications => true , clb_goal => dbms_service.clb_goal_short ,goal=>dbms_service.goal_service_time);
Page 16
16
Step 1. Enable Cache and Fast Connection Failover, Remote ONS
• Set data source properties
OracleDataSource ods = new OracleDataSource() ... ods.setUser(scott); ods.setPassword(tiger);
ods.setConnectionCachingEnabled(True); ods.setFastConnectionFailoverEnabled(True); Ods.setONSConfiguration("nodes=host1:6200,host2:6200");
ods.setConnectionCacheName(MyCache); ods.setConnectionCacheProperties(cp); ods.setURL("jdbc:oracle:thin:@(DESCRIPTION= (LOAD_BALANCE=on) (ADDRESS=(PROTOCOL=TCP)(HOST=VIP1)(PORT=1521)) (ADDRESS=(PROTOCOL=TCP)(HOST=VIP2)(PORT=1521)) (CONNECT_DATA=(SERVICE_NAME=MYSERVICE)))");
•
or set system properties
-D oracle.jdbc.FastConnectionFailover=true
Step 2. Verify ONS on RAC nodes
• $ORACLE_HOME/opmn/conf/ons.config
localport=6100 remoteport=6200 loglevel=3 useocr=on # port ONS is writing to # port ONS is listening on
Page 17
17
Step 3. When starting the application..
• Specify system property
-Doracle.ons.oraclehome=
• Ensure ons.jar file is on the CLASSPATH.
SEE RAC SAMPLE CODE for FAST START GUIDE
Runtime Connection Load Balancing with OCI Session Pools
• The application must have been linked with the threads library. • The OCI environment must be created in OCI_EVENTS and OCI_THREADED mode. • Configure the RAC service with a GOAL, CLB_GOAL, and AQ_NOTIFICATIONS to TRUE.
dbms_service.modify_service(service_name=> ‘crm’, aq_ha_notifications=>true, goal=>service_time, clb_goal=>short);
Page 18
18
Runtime Connection Load Balancing
with JDBC, ODP.NET
CRM requests connection ? connection cache
“CRM is bored”
60%
10%
30%
“CRM is very busy”
“CRM is busy”
Instance 1
Instance 2
Instance 3
Heavy batch starts one node
- Average user calls/second
3500
3000
2500
2000
1500 No Load Balancing 1000 Service Time Goal Throughput Goal
500
0 1 7 13 19 25 31 37 43 49 55 61 67 73 79 85 91 97 103 109 115 121
Page 19
19
Goal = NONE (off)
Service 01 - elapsed time per call
40.0
35.0
30.0
Node 1 Node 2
elapercall (ms)
25.0
20.0
15.0
10.0
5.0
0.0 21:09:22
21:14:02
21:18:43
21:23:35 time
21:28:22
21:33:04
21:37:45
Goal = SERVICE_TIME
Service S01 - elapsed time per call
40.0
35.0
elapsed node 1
30.0
elapsed node 2
25.0 e p rc ll (m ) la e a s
20.0
15.0
10.0
5.0
0.0 18:43:24
18:48:05
18:52:48
18:57:31 time
19:02:18
19:07:03
19:11:48
Page 20
20
Fast Connection Failover
• • • • • Supports multiple connection caches Datasource for each cache mapped to a service Keeps track of service and instance for each connection Cleans up connections when failures occur Distributes new work requests across available instances
Mid-Tier
CACHES SERVICE 1 SERVICE 2 SERVICE 3
Database Tier
INST X INST Y INST Z
JDBC, ODP.NET, OCI
Failure Notification (FaN)
JDBC Fast Connection Failover Processing
• When DOWN signal received from Oracle Database 10g RAC
• Routes new requests to surviving instances • Throws exception if application was in midst of transaction
• When UP signal received from Oracle Database 10g RAC
• Creates new connections to new instances • Distributes new work requests evenly to all available instances
Page 21
21
Extended FAN/FCF Client Integration
• Oracle Call Interface
• standalone,TAF, connection pool, and session pool connections are automatically broken and cleaned up by OCI and the application callback is invoked within seconds of the failure event.
• ODP.NET
• FAN notifies ODP.NET which allows it to clean up resources from severed connections or establish new connections • HA Events=true; on connection string
• Service must have aq_ha_notifications=>true
Summary
• For best workload balancing with Real Application Clusters:
• Use Connection Load Balancing • Use Automatic Workload Management (Services) • When using Connection Pools, use Fast Connection Failover and Runtime Connection Load Balancing
Page 22
22
For More Information
• TWP : Workload Management with Oracle Database 10g
• Sample Code for RAC
http://www.oracle.com/technology/sample_code/products/rac/index.html • Oracle Clusterware and RAC Admin and Deployment Guide (Chapt 6)
http://download-west.oracle.com/docs/cd/B19306_01/rac.102/b14197/hafeats.htm#sthref374
http://www.oracle.com/technology/products/database/clustering/pdf/twpracwkldmgmt.pdf
Q & A
Page 23
QUESTIONS ANSWERS
23
For More Information
http://search.oracle.com
REAL APPLICATION CLUSTERS
or otn.oracle.com/rac
Page 24
24
Page 25
25