CPU and LOAD Imbalance in RAC load balancing
jcoppiMay 21 2012 — edited Jun 1 2012Env :
Oracle RAC 11gR2
Version : 11.2.0.2
OS : RHEL 5.5
One database on a 3 node RAC cluster
node 1 : 8 CPU , 64 GB RAM
node 2 : 8 CPU , 64 GB RAM
node 3 : 16 CPU , 64 GB RAM
Application uses connection pool - min setting at 50
During perf tests, we are not crossing min settings, it is sufficient to satisfy new requests. Constant .7 Logons/sec on each node is probably a good indicator.
However, perf test results show imbalance in number of executes and trasactions per sec, resulting in more resource usage in one node. the node differs in each test.
In the load profile shown below, NODE2 has double the number of TX/SEC and EXECS/SEC.
Service Configuration ( this is the service that the app uses ) :
=====================================
Service name: CRST
Service is enabled
Server pool: OO217_CRST
Cardinality: 3
Disconnect: false
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: false
Failover type: SELECT
Failover method: BASIC
TAF failover retries: 0
TAF failover delay: 0
Connection Load Balancing Goal: LONG
Runtime Load Balancing Goal: NONE
TAF policy specification: BASIC
Preferred instances: OO217N1,OO217N2,OO217N3
Available instances:
Service is enabled on instances: OO217N3
-----
NODE 1
Per Second Per Transaction Per Exec Per Call
DB Time(s): 3.1 0.2 0.01 0
DB CPU(s): 2.5 0.2 0.01 0
Redo size: 73,944.50 4,919.40
Logical reads: 184,305.30 12,261.60
Block changes: 381.5 25.4
Physical reads: 0 0
Physical writes: 16.2 1.1
User calls: 1,035.30 68.9
Parses: 197.5 13.1
Hard parses: 0 0
W/A MB processed: 17.4 1.2
Logons: 0.7 0.1
Executes: 451.8 30.1
Rollbacks: 0 0
Transactions: 15
-----
NODE 2
Per Second Per Transaction Per Exec Per Call
DB Time(s): 9.6 0.3 0.01 0
DB CPU(s): 5.3 0.2 0.01 0
Redo size: 130,588.80 4,686.90
Logical reads: 449,322.70 16,126.30
Block changes: 690.8 24.8
Physical reads: 0.3 0
Physical writes: 30 1.1
User calls: 1,938.20 69.6
Parses: 346.7 12.4
Hard parses: 0 0
W/A MB processed: 39.1 1.4
Logons: 0.7 0
Executes: 825.2 29.6
Rollbacks: 0.1 0
Transactions: 27.9
-----
NODE 3
Per Second Per Transaction Per Exec Per Call
DB Time(s): 4 0.2 0.01 0
DB CPU(s): 3.2 0.2 0.01 0
Redo size: 105,331.20 4,966.80
Logical reads: 314,385.80 14,824.70
Block changes: 553.4 26.1
Physical reads: 1,168.40 55.1
Physical writes: 22.6 1.1
User calls: 1,443.70 68.1
Parses: 277.9 13.1
Hard parses: 0.1 0
W/A MB processed: 27.2 1.3
Logons: 0.8 0
Executes: 633.8 29.9
Rollbacks: 0 0
Transactions: 21.2
-----
How can we achieve the same load on node1 and node2, node3 as it turns out has 16 CPU and so will always be least loaded from a resource consumption perspective. But the number of executes and tx/sec should ideally be spread out evenly. There is a think time of 60-180 secs for a 3000 user test spread over 1 hr with a ramp up time of 10 min. And as mentioned, the app uses java connection pooling, with min connections set to 50.
Thanks,
Jhooma