RAC Stretch Clusters - a survey

There's been an interesting digression on the Oracle-L list today on the subject of RAC stretch clusters.

A stretch cluster is one in which nodes are separated - possibly by several miles - mainly as a precaution against a complete data-centre outage. I know of a couple of examples where separations in the region of 20-30 miles (30-50 km) are either in use or planned, and other posters on Oracle-L have mentioned "several" or "a handful" of implementations worldwide.

Obviously, the main performance issue for a stretch cluster is the latency and bandwidth of the interconnect. The bandwidth isn't affected by distance, but the latency certainly is.

I'd be very interested to hear from anyone who has implemented a stretch cluster (in test or production) with as much detail as you are free to pass on, particularly:

• what is the distance between sites
• how many nodes at each site
• is the workload evenly distributed (active/active homogeneous), partitioned (active/active, heterogeneous) or uneven (active/passive)
• some indication of database size and transaction rates (in whatever units are meaningful to you
• Any performance issues?

Either email me (nigel at preferisco dot com) or comment below. I would like to publish the results but let me know if any part(s) of your information is too sensitive to be broadcast, even anonymously.

Thanks in advance...

Avoiding application suicide by session pool

A recent post on Oracle-L mentioned:

We had an issue affecting one of our production DBs. The middle tier application for some reason, went crazy spawning processes chewing up the DBs process parameter. DB started throwing errors indicating max process exceeded.

He went on to ask how to wake up PMON to clean up these processes - he'd had to bounce the database to get things cleaned up.

My response was to ask about the root cause, rather than the symptom. The poster may have been solving the wrong problem (or rather, after putting out the fire, he needed to find the cause and stop it happening again).

"Mid tier went crazy spawning processes" is often a symptom of session pool
madness. In such an application, X number of users share a smaller Y number of Oracle sessions. Everything tootles along happily; Users (midtier threads if you like) loop around:

• get a session from the pool


• issue one or two SQL


• commit/rollback


• give the session back

As long as the users spend less time in Oracle than they do in the rest of the
application (and waiting for user input etc), no problem.

Then something goes wrong; maybe a session sits on a lock that everyone needs; maybe a sequence cache isn't big enough (or is ordeed) and/or you forgot that We Don't Use RAC; maybe you had an SGA problem like ORA-4031.

What happens next:

• all the Oracle sessions in the pool are busy


• next midtier thread asks for an Oracle session


• midtier pool manager says "no problem", launches a new Oracle session and adds it to the pool


• that session becomes busy


• and the next thread, and the next thread, and the next thread...

Soon instead of sharing say 100 Oracle sessions across 1000 processing threads,
your mid tier has responded to the blockage by adding 900 new sessions to the
load. That's probably made the problem worse, not better - kind of like slamming your foot on the accelerator when you see brakelights ahead in the fog.

I had exactly this problem performance last year, testing a J2EE app, using OC4J. We hit a 4031 problem (no bind variables in one part of the system) and then fairly immediately the application server did its lemming impersonation as described above.

Things to consider:
1) reduce the upper limit on the session pool size (definitely to below your Oracle processes level!)
2) if possible, slow down the rate of session starts (eg set a delay in the mid-tier session manager)
3) find out what caused the problem in the first case.

The good news is that if you dampen down this suicidal behaviour, you probably have a better chance of diagnosing the root cause next time.

"Micro-partitioning" pooled sessions in a RAC environment

One of the problems we've come across testing an OC4J based system against an Oracle 9.2.0.5 RAC database is that as a given application session borrows database sessions from the session pool, it finds itself skipping at high speed around all the nodes in the RAC. If you're not careful, it's possible to insert or update some piece of data (on one database node) and then find that it's not quite there when you take a look a millisecond later via a different node.


You can work around this by setting MAX_COMMIT_PROPAGATION_DELAY to zero. However that does impose extra load on the cluster.


The problem arises because your connect string (in the JDBC URL) specifies a RAC service, something like this:

url="jdbc:oracle:thin:@(description=(LOAD_BALANCE=on)(address=(protocol=tcp)(host=rac1)(port=1521))(address=(protocol=tcp)(host=rac2)(port=1521))(address=(protocol=tcp)(host=rac3)(port=1521))(connect_data=(service_name=RACDB)))"

This typically load-balances across all nodes in an annoyingly random and yet not entirely effective way - especially when there is a sudden increase in connections. Oracle’s default load balancing mechanism is particularly badly suited to a connection pool.


What you'd really like to do is to pin application sessions to database nodes - so that you can be (reasonably) sure that a given user, once logged on, will keep on using the same RAC node (node failure excepted, of course). Just have each app server connect to its 'own' database server.


Unfortunately, the JDBC URL configuration is clustered. Whatever you set it to has to work for all nodes. So we came up with a simple workaround:

• Turn load balancing off


• Add some new IP address mappings to each app server node's /etc/hosts file


• use the 'virtual' hostnames in the JDBC URL

Now you have this URL:

url="jdbc:oracle:thin:@(description=(LOAD_BALANCE=off)(address=(protocol=tcp)(host=db1)(port=1521))(address=(protocol=tcp)(host=db2)(port=1521))(address=(protocol=tcp)(host=db3)(port=1521))(connect_data=(service_name=RACDB)))"


and in each /etc/hosts you have something like:


10.20.30.1 rac1.prt.stl.int rac1 db1

10.20.30.2 rac2.prt.stl.int rac2 db2

10.20.30.3 rac3.prt.stl.int rac3 db3



On each app server, the mappings from racN to dbN are modified, so that each app server "prefers" its own RAC server, forming a stovepipe.
If the app server tier is on nodes app1, app2 and app3, the mapping is:

RAC node	app1 mapping	app2 mapping	app3 mapping
rac1	db1	db2	db3
rac2	db2	db3	db1
rac3	db3	db1	db2

So in normal operation, we have a stovepipe all the way down from web to app to database tiers. Ideally we would have the web load balancer intelligently cluster like users together (eg by organisation or region) to further reduce cluster overhead, but that's currently politically unacceptable. Another time, perhaps!


Cheers Nigel