Added Oracle Replication to rp5470 and lost performance

[Andy Beal]

I'm asking this here as well as with Oracle because I know there are several Oracle/HP-UX guys in the forums and maybe someone has seen this before.

We have 2 rp5470 boxes, one for production, and one for test/development. Both are running Oracle 9i database. Last week we added 2 other systems that are using Oracle replication in async multimaster form, to replicate back and forth with the production box. The Test/development system did not change, and is not part of the replication environment. No one is currently using the other replication nodes, they are just sitting on the lan getting updates from the main system.

Performance on the rp5470 degraded horribly, much more than expected, compared to the identical test/dev box it's only about 33% of the speed. A particular long running process on our development system (with almost identical data), will finish in about 30 minutes, it takes almost 2 hours with the production system.

So using this particular process as a focus, using conventional wisdom I decided that replication should only affect inserts/updates/deletes, not select statements. So I took any table that is written to and removed them from the replication mix. I then make all my dbms replication jobs Broken, hence thinking I should have removed most of the overhead caused by the replication processes. Apparently this is not the case as that configuration resulted in virtually no change in performance. I can look at statspacks from the two systems and nothing stands out as a hinderance to the production system, it just looks slower.

Using Glance on both systems while doing this operation, it appears that the oracle process handling the session is consuming around 80% of one processor on the production side, and about 50% of a processor in the test/dev system. Looking at process resources and wait states, it looks like they are doing about the same, again nothing stands out as a difference, except it's taking more processor and seemingly doing less work. While testing I was the only user accessing each system.

I didn't expect replication to add any overhead to select statements, however it seems to have handicapped this fine system in a grave way. I know that I theres a good chance I could ditch the replicated sites and return to normal, but that's not really solving the problem. The other systems in the replication setup seem to be running fine, although they are 32bit Linux, they are not as fast as the test system but I don't expect them to be, they are much faster than my main production system.

Any help or insight (or another direction to look), is welcomed. I've included a statspack, fast.txt to this message this is from my test/dev box. I will followup with a slow.txt that is from the production box, while the replication queues were stopped. Remember that the tables being updated are no longer members of any replication groups.

Thanks,
Andy Beal

[Andy Beal]

Here is slow.txt from the production box. Thanks for giving me a look over guys!

[Steven E. Protter]

There is probably a need for you to make kernel modifications for performance to accommodate Oracle replication. It has obviously increased needs for resources.

Here is a link.

http://www2.itrc.hp.com/service/cki/search.do?category=c0&docType=Security&docType=Patch&docType=EngineerNotes&docType=BugReports&docType=Hardware&docType=ReferenceMaterials&docType=ThirdParty&searchString=UPERFKBAN00000726&search.y=8&search.x=28&mode=id&admit=-1335382922+1059402028723+28353475&searchCrit=allwords

Performance Tuning 101. Written by the guy that took my last Oracle tuning software case.

I'm attaching the latest version of my performance data collection suite. It can identifiy all kinds of bottlenecks and help you correct.

I'm sure there are adjustments you can make on the oracle side as well, but don't forget about the OS.

SEP

[Volker Borowski]

Hmmm,

one Box is 9.2.0.1 on is 9.2.0.2 !

If one Box had been patched, it might be that the corresponding catrep.sql (which is for replication) has not been executed as described in the patch note ?
If this patch has been applied recently, you might still have overhead from stored procedures being re-compiled. There is a script that recompiles the stored procedures (utlrp.sql or likewise, it is mentioned in the postactivities as well.)

The test-box has a significat smaller logbuffer which shows an almost double amount of waits for logwriter activities.
From this I'd assume the testbox being slower :-)

Looking at the buffer_gets in the first statement section, I doubt that the data is "allmost identical", sind the production database doee a whole lot more of GETS. Esp. the "logical reads per transaction" makes me think the production is doing 3 times more than the test. It bears a double amount of sessions although they are a little bit less active. The production executes the first statement 648 times, while the test-box does 1318 executions.
All the time both boxes do nearly no IO.

I'd start by adjusting the logbuffer on test and look into the patch-version issues, recompile stored procedures on both and re-snap the application run.

Hope this helps
Volker

[Andy Beal]

Steven, that link doesn't give me anything. I've looked at that sort of stuff in the script and not been able to come up with anything specific, now I have a lot of other daily traffic going on that complicates/contaminates taking those stats and comparing them to the other system.

Volker: You are correct, one box has been patched to 9.2.0.2, the production box, and it is slower. The test box was put into place in a hurry and hasn't been Oracle patched yet, it is the one that's behaving. They are Identical boxes, with the exception of no replication on the test system. Unfortunately some of the descrepancies show up because the statspack times are not quite the same. Hindsight is 20/20 and I won't have the system to myself again until next weekend.

I am looking into your suggestions, Thanks for the information.

[Tim Adamson_1]

Hi Andy,

Not sure if this will help but are the 2 systems patched identically? I have seen the case where an ARPA patch caused CPU load to increase quite substantially. Might be worth checking.

Hope it helps!!