Oracle Performance Issue on VMS 7.3-2 and Oracle 9.205

[Paul Benford]

we are testing a new verison of our corporate application and get very poor performance.

We run tests using a test engine to simulate user load with our application.

Oracle is used as the back end and is RAC enabled. It is running on a 3 node ES45 cluster using memory channel and 2 CPUs' per ndoe and 3 Gbytes memory per node.

We have 70 % memory utilization during the test.

Once we get the number of users up to about 80 the CPUs' are saturated and running at 100 %. The compute queue is at 16 process.

Oracle is configured to use dispatchers and shared servers. The CPU consumption seems to be on the shared servers and the dispathers are heavy users of buffered I/O.

There is very little direct I/O.

I have attached a Performance Analysis summary.

We have tried several hardware configurations so the report I am attaching is a 2 node cluster with 4 cpus each and 6 Gbyte of RAM each.

as part of trouble shooting we removed RAC added CPUs etc to see what we would get. all the resource usage looks the same except when you add CPUs' but the problem occurs further into the test.

[Thomas Ritter]

Paul, post a CPU usage summary. Use "$ monitor modes" and look for items like EXEC mode. Have a corresponding "$ monitor system".

It will be interesting to study the EXEC mode usage. Like type repeated SQLs with unoptimised access strategies can perform badly.

[Hein van den Heuvel]

Paul,

The attachment failed. Please re-reply with new attachment.

Please add an STATSPACK result?


Did it ever work?
How good/bad did the old version work?
I like the reconfig test to understant 3*2CPU vs 2*4CPU and so on.
Have you also tested non-RAC as reference data.

Test engines, simulating users, and too easily run 'full blast' with no think time. I assume that when you mentoin 80 users ou don't mean 80 streams going max-sped, but a situation where the driver generates about as much load as 80 users, with think time and UI time, would generate when working hard.

hth,
Hein.

[DICTU OpenVMS]

You probably know this one, but just to make sure : take a look at metlink Note:282965.1

Just in case...

[Paul Benford]

Thanks for all the repsonses. I did not have much time yesterday when I started this thread so I will add more detail in this note.

First of all - a bit of back ground here

The original legacy application was created using powerhouse and interbase. It is a host based application. The target architecture is a 3 tier web based application. XP clients, WIN2003 middle tiers (started with win2000) and Oracle on OpenVMS as the back end. (the back end clusters are what the legacy application is currently using in production)

We started to rewrote the code to be a web based application. Since then we have been trying to improve the code. Over time hardware in the intel world has improved and now with Opteron based middle tier systems we can test up to 240 users ( limited to licensing .. will be fixed soon). Also fixes to the middle tier code has helped things along as well. The end result is that the middle tier systems are no longer a bottle neck. We have to test up to 600 users. That is the forecast number of users per region. (we have 6 regions).

Our testing tool is very robust and think times are built into it. It is the Rational product.

Testing methodology

we are currently using the rational test tool to exercise about 6 screens. Login, upate screens etc. (there are 600 screens but for peformance testing we are testing the 6 forecast heavy usage screens. 1 test lasts about 1 hour and ramps the usage up from 20 to 240 users in increments of 10. There is think time built in.

Last week we tried to go to 240 users (as we got the new Opterons (HP DL385 dual CPU )and seen the response time drop off. I checked the back end found the symptoms as descibed in my first note. The cluster configuration at that time was 3 ES45s, 3Gbytes Ram each, 2 cpus each, memory channel interconnect and attached to a HSG80 san using a redundant configuration (2 controllers, 2 switches)

So this week we have done the following tests and we see the same symptoms in all the configurations. all that varies is the time at which the problems occur(100% CPU, 16 + processes in compute queue and huge buffered i/o (8k to 11K)). The T# is the test number used for reference,.

1 ES45 with 6 Gbytes of RAM, 4 cpus, No RAC and VAXcluster = 0, T1263
1 ES45 with 6 gbytes of RAM, 3 CPUS, No Rac, and vaxcluster=0, T1264
1 ES45 with 6 Gbytes of RAM, 2 CPUS, No RAC and Vax cluster=0, T1265

2 ES45s with 6 gbytes of RAM, 4 CPUS, RAC enabled and in a cluster of 2, T1266
2 ES45s with 6 gbytes of RAM, 3 CPUS, RAC enabled and in a cluster of 2, T1267
2 ES45s with 6 gbytes of RAM, 2 CPUS, RAC enabled and in a cluster of 2, T1268

I have zip file with performance reports from PSPA in ZIP files by test number and I zipped them into 1 zip file to allow me to attach them ( I hope it works !)

We could not do a 3 node cluster as we do not have enough CPUs' not in use :).

[Paul Benford]

Also on stats pak

We do not have a stats pak for these specific tests.

A DBA here came by and just told me he has gotten a patch from Oracle that addresses several shared server issues.

will let you all know what happens !

I will post a stats pak if the problem persists after applying the patch.
Thanks

[Thomas Ritter]

Pau, montior the usage realtime. Look at the output of monitor modes. It maybe very telling. If oracle is the problem, then exec mode be very high.

[Thomas Ritter]

Paul, I find running monitor in non-interactive mode very useful.

syntax is something like this

$monitor/begin="''f$time()'"/endin="''f$time()'+0 06:00" -
/display=dud.tmp -
/record=monraw.tmp process/topcpu, process/topdio,modes,system

If there is a specific time interval under study then you can replay at that time. Very quick and can be very telling.

[Paul Benford]

I do run monitor as a detached process as well as the data collector for PSPA.

But due to the reboots for the hardware reconfig the saved binary file is not getting closed properly and has nothing in it. I guess I have to check my procedure. It is basically the monitor procedure from sys$examples spruced up a bit .. but not enough I guess.

But for some of the tests I do have good monitor files.

But in the files I attached exec mode and interrupt mode CPU usage is in there and they are taking about 8% of the CPU.

I will create a monitor summary file for the test period of CPU modes and attach it.

[Paul Benford]

Here is the monitor summary report

modes, state and sys/cur of 5 minutes when the system is exhibitig the less then desirable performance.

There is very high Interrupt and Kernel percentage during that time. I guess the other tool I am using is averaging out those values over the test period

[Hein van den Heuvel]

>> Here is the monitor summary report
>> There is very high Interrupt and Kernel percentage during that time.

Just FWIW, without trying to solve your problem perse, IMHO the stats you shared show very reasonable, possible LOW kernel and interupt time.

It suggests that Oracle and friends are doing some 'real' work, like IOs and LOCKs while doing the user stuff. Not unreasonable IMHO.

From a different perspective... if you where to 'tune' the kernel and int stack time entirely away (not that you could), then I don't think your users would notice
the extra idle time much.

However, if you could somehow half the user time, then the box would have a whole cpu idle. So where do want to spend your tuning time and energy?

Dispatchers and shared servers where all the rage 10+ years ago. They nicely save on memory needs and time wasted resolving contention. They may however have outplived theyr use for you. Have you tried without? A direct client to oracle-slave link, or does that create a prohibitive amount of processes on the backend? I suppose that with a 2 CPU system the shared server may be more appropriate then with higher SMP counts.

Just thinking out loud...

Hein.

[John H. Reinhardt]

You say you are testing a new version of the application. I see later you mention re-writing it from using PowerHouse to being web-based. What version of VMS and Oracle is the current production app running on (I assume that verion is running satifactorily)? During the re-design were the SQL queries changed? If the version of Oracle changed did the re-design take into account any changes in Oracle? Have statistics been generated for the data you are testing on? The Rule-based optimiser in Oracle can behave quite badly if the statistics are not current for the data in the tables. It WILL make a difference.

It's been too long since I was in charge of a busy Oracle database on VMS but the Monitor stats for Processor Mode don't seem to far off for a 100 process load.

What are the figures for your SGA sizes? Have you done the calculations for the various hit counts? Data buffer hit percentage? SQL cursor re-use? Latch waits?

MTS has historically performed horribly on VMS since Oracle 7.3 but supposedly this has been fixed in 9iR2 (9.2.0.3 and up) so I doubt MTS is the problem. You really need to do a good test with STATPACK running and find out what Orable is doing. My guess is that it's the application which is out of tune rather than the VMS system, but until we know what Oracle is doing it's just a guess.

[Thomas Ritter · Accepted solution]

Paul, when we over work out systems, 4 node ES45 cluster with three cpus each and 24 GB of RAM, we typically have a COM queue around 40 and CPU maxed out at 300% percent. We had exec mode at about 90% which indicated something with oracle/rdb was not working as it should.
Your figures look good from a system perspective. The slow user experience looks application related.

[Paul Benford]

Okay

To catch everyone up to what we have done here and more configuration detail

We are running OpenVMS 7.3-2 with TCPIP 5.4 ECo5.

Oracle verison is 9205 with a patch from Oracle that addresses various items with shared server. VMS patches were also loaded. Neither made a difference.

Late on Dec 9 the DBA reduced the number of dispatchers to 2. That improved the response time by 20% !. The compute queue went way down.

attached is a monitor summary of the test with 2 dispatchers, statspak with 10 dispatchers (Nov) and a stats pak with 2 dispatchers as well as a listing of VMS patches loaded.


VMs patches

[Paul Benford]

Well we made some application changes (changing one routine that opened a connection to the database 34,000 times in 10 minutes to do it once) and increased the performance by 64 %.

Thanks to all who contributed. Merry Christmas and happy new year

[Paul Benford]

see previous entry