Fwd: RAC application mix
- From: Adam Musch <ahmusch@xxxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <Oracle-L@xxxxxxxxxxxxx>
- Date: Mon, 1 Jun 2009 22:00:06 -0500
---------- Forwarded message ----------
From: Adam Musch <ahmusch@xxxxxxxxx>
Date: Mon, Jun 1, 2009 at 9:59 PM
Subject: Re: RAC application mix
To: mkline1@xxxxxxxxxxx
If I had to solve a problem with a RAC-based application where RAC was
required for availability and/or scalability that had a lot of
crosstalk/internode blocks of interest inhibiting response time and the
vendor can't/won't make a change, here's what I'd do:
1. Create/modify an after logon session trigger, which populates a package
global variable -- put the instance ID in it.
2. Change the heavily crosstalk write-only / log tables to be list
partitioned/subpartitioned on a new INSTANCE_ID column. Change all the
indexes to local.
3. Add a before-insert trigger to set the INSTANCE_ID using the global
package variable configured on login.
And beat the hell out of the vendor until they make the application
RAC-aware. I've been a vendor for COTS/consultingware, so I've been beaten
on by customers in this situation, and the customer's right.
This may sound extreme, but it would isolate all that write-only traffic
from internode to intranode, eliminating the crosstalk. There may be better
means than I'm aware of or have outlined here, but the same principles
apply. From my perspective, it seems simpler than trying to tune INITRANS,
because traffic on multiple nodes is still going to cause internode interest
for index pages. Partitioning appears to be the right tool for the problem.
On Mon, Jun 1, 2009 at 4:25 PM, Michael kline <mkline1@xxxxxxxxxxx> wrote:
> Was called back to look over a RAC configuration that has been around for
> a while.
>
>
>
> When it was put in, it was one application and was pretty much allowed to
> load balance over the two nodes.
>
>
>
> This last time, low and behold, the customer had added about 4 applications
> to the mix.
>
>
>
> They are still load balancing on the select, and I’m finding that 2-3 of
> the applications have EXTREEM HEAVY updates to a sort of log table, some
> have multiple log tables. In the one case it’s a couple of thousand users
> that are sort of registering various status phases like “login, in, query,
> review, query, exit, logged out” and take that times all those users and
> there is heavy cross traffic. Combine that with the other applications doing
> sort of the same thing, and then one of the others is mostly reporting with
> lots of full table scans.
>
>
>
> Where’s the best place to “start” when all that combination seems to
> indicate there’s going to be a lot of things to point to.
>
>
>
> The one vendor said they had no RAC customers running their product. It’s
> the one with all the logging. They advised an active/active or
> active/passive cluster. Would it simply be enough to try and get them to not
> use “load balancing” and simply go with “Pick a node and use it as primary.
> Put the other applications on the other node.”
>
>
>
> Anyone else been “gifted” with this opportunity? We can add indexes, for
> the other vendors to tune things, but don’t really know how to get around
> several thousand users updating the same table. How high on a two node RAC
> can INITTRANS be pushed and would it be practical? The other stuff is pretty
> typical.
>
>
>
> *Michael Kline*
>
> Principal Consultant
>
> Business to Business Solutions
>
> 13308 Thornridge Ct
>
> Midlothian, VA 23112
>
> O: 804.744.1545
>
> Fax: 804.763.0114
>
>
>
Other related posts:
- » Fwd: RAC application mix - Adam Musch
