Possible Performance Problem

[Charles McCary]

Group, hey we just turned up an Oracle Financials db on an N-class box with va7100 disk array.

1) We're seeing CPU usage sometimes around 100%, but averaging around 90% which is probably ok.

2) We're seeing Memory usage pegged at 98-100% at all times.

3) We're seeing some disk utilization at 100% and some small queue sizes on the array disks (1-3) and some large queue sizes on the internal disks (15-20 at times).

Any ideas? I will attach some sar, vmstat, and iostat outputs as well.

[Pete Randall]

My first indicator is always the users. How do they perceive response time? If they're happy then you need to note the stats you're seeing as a baseline for future reference. If they're not, then you start digging into what the cause(s) may be.

Pete

[T G Manikandan]

What is the configuration of the machine,
memory etc

Again come these parameters
dbc_max_pct
dbc_min_pct.

post your kernel parameters

USe top to find out which is using your processor.
Also do a

UNIX95= ps -e -o ruser,pid,vsz=Kbytes|more

to find the processes that are using more memory on the server.

Revert

Thanks

[Charles McCary]

sar -d for 10 minutes:
10:28:40 device %busy avque r+w/s blks/s avwait avserv
Average c1t6d0 15.19 0.59 23 380 5.29 8.75
Average c2t6d0 7.25 0.68 11 133 5.50 8.53
Average c3t0d2 77.65 0.83 211 4894 5.77 10.82
Average c3t0d4 57.01 0.50 110 1822 5.12 10.36
Average c4t0d4 33.62 0.65 55 965 9.39 17.22
Average c4t0d5 22.11 0.50 56 1017 5.02 6.47
Average c5t1d7 20.16 1.24 43 3000 10.10 19.19
Average c5t2d0 14.49 0.50 15 352 5.10 12.10
Average c3t2d2 2.79 0.50 0 2 3.88 251.60


sar -b
10:29:56 bread/s lread/s %rcache bwrit/s lwrit/s %wcache pread/s pwrit/s
10:29:58 571 5115 89 100 507 80 0 0
10:30:00 890 3760 76 342 407 16 0 0
10:30:02 726 6059 88 271 517 48 0 0
10:30:04 1682 5895 71 616 827 26 0 0
10:30:06 1428 6749 79 509 737 31 0 0
10:30:08 989 5087 81 540 845 36 0 0
10:30:10 1214 6436 81 523 726 28 0 0
10:30:12 1182 6875 83 425 804 47 0 0

vmstat:
memory page faults
avm free re at pi po fr de sr in sy cs
374266 8227 67 16 27 25 27 0 132 1698 17630 2180
CPU
cpu procs
us sy id r b w
69 8 23 16 4 0
73 6 21
74 5 21
73 6 21
383578 9042 41 0 54 397 30 0 1468 3081 31940 4560
79 14 7 16 2 0
85 10 5
83 11 6
86 8 6
421035 8704 20 0 48 182 12 0 734 2645 32518 4282
87 11 2 20 3 0
88 11 1
91 9 1
93 6 1
434308 8089 52 0 86 58 17 0 240 2673 33168 4660
88 9 2 21 2 0
86 10 4
84 13 3
86 12 1
409172 8033 25 0 96 18 6 0 77 2392 66365 9793
68 31 0 21 3 0
84 16 0
88 12 0
57 43 0
410867 8128 15 0 59 5 3 0 24 2459 84216 14093
79 18 3 17 4 0
69 27 3
76 21 3
63 33 4


I think we may need more memory, but would like some 2nd and 3rd opinions...

thanks,

Charli8e

[Charles McCary]

Sorry, here's the info:

4gb RAM
8gb swap

sysdef:
NAME VALUE BOOT MIN-MAX UNITS FLAGS
acctresume 4 - -100-100 -
acctsuspend 2 - -100-100 -
allocate_fs_swapmap 0 - - -
bufpages 104469 - 0- Pages -
create_fastlinks 0 - - -
dbc_max_pct 10 - - -
dbc_min_pct 5 - - -
default_disk_ir 0 - - -
dskless_node 0 - 0-1 -
eisa_io_estimate 768 - - -
eqmemsize 31 - - -
file_pad 10 - 0- -
fs_async 0 - 0-1 -
hpux_aes_override 0 - - -
maxdsiz 262144 - 0-655360 Pages -
maxdsiz_64bit 419430 - 256-1048576 Pages -
maxfiles 512 - 30-2048 -
maxfiles_lim 2048 - 30-2048 -
maxssiz 51200 - 0-655360 Pages -
maxssiz_64bit 51200 - 256-1048576 Pages -
maxswapchunks 4098 - 1-16384 -
maxtsiz 262144 - 0-655360 Pages -
maxtsiz_64bit 262144 - 256-1048576 Pages -
maxuprc 1500 - 3- -
maxvgs 10 - - -
msgmap 2555904 - 3- -
nbuf 81312 - 0- -
ncallout 8228 - 6- -
ncdnode 150 - -ndilbuffers 30 - 1- -
netisr_priority -1 - -1-127 -
netmemmax 0 - - -
nfile 15341 - 14- -
nflocks 400 - 2- -
ninode 9540 - 14- -
no_lvm_disks 0 - - -
nproc 8212 - 10- -
npty 128 - 1- -
nstrpty 60 - - -
nswapdev 10 - 1-25 - -

[Anil C. Sedha]

Charles,

You may try increasing your buffer space to 250 Mb and check the performance of the system.

I believe that would improve the performance.

Regards,
Anil

[Jeff Schussele]

Hi Charles,

I didn't see the timeslice kernel parm in that output.
Make sure it ISN'T set to 1 - it should be set to 10. If it's 1 that can cause high CPU values as it'll be context switching big-time.

Rgds,
Jeff

[Charles McCary]

Group,

Hi - timeslice is 10.

Just set dbc_max_pct to 10%, from 30, you think that may have something to do with disk util / wait queue?

thanks,

c

[Jeff Schussele]

Hi (again) Charles,

Yes, that will help disk usage. But the flipside is it will impact memory usage & can cause more pageouts. And you're already paging frequently
If you increase it, I'd going in smaller steps - maybe 15-20% to start?.
And then check the impact on paging./

Jeff

[Charles McCary]

Well, it's hard to tell which is having the impact on response time. Memory utilization or Disk utilization. I guess one way to fix this would be to buy more memory (that would increase the buffer and available memory). Any other suggestions?

[Volker Borowski]

Hi,

the init.ora with the database parameters would be of help.

How many CPUs do you have ?
How big is the database ?
How many users are working on it ?
How many oracle-Processes do you have when the users are working ?

The last one might be the most important, because if you have many users and rund dedicated server proceses, you'll get tons of useless processes, that have to be swapped in and out all the time. Change your listener configuration to MTS in this case. Search the forum there was a MTS/dedicated thread some months ago.

Quite a couple of points to start with on the database side
Good hunting
Volker

[Charles McCary]

4 cpus

DB is about 45gb

175-200 oracle users

206 procs were running just now with 189 being local sessions.


thanks,

Charlie

[John Poff]

Hi Charles,

We are running Oracle Financials here also. It can be quite a beast. To compare to your numbers, our DB is about 355 Gb, roughly the same number of users and processes. We run on a V2600 with 10 CPUs and 12 Gb of RAM. I just checked. Our memory usage is 78% and our CPU usage is around 50%. You can tweak the kernel parameters a bit, but my guess is that you will need more CPU and RAM.

JP

[Bill Hassell]

Generally speaking, there is not a lot you can do to HP-UX to fix what is essentially common Oracle behavior. Depending on what performance tools your DBA has for Oracle, some major improvements can be made with changes in the SGA options and size. Poor SQL statements and items that need indexes can kill performance.

The operating system is being asked to do inefficient things as fast as it can and there are a few things that a better distribution of disk volumes over multiple chanels will help, but the answer is to reduce the disk activity and possible add more RAM and processors. Make sure Oracle is running 64 bit mode.

[Johan Bergwitz]

If you tune up the buffercache as some people has mentioned I would suggest that you also mount your filesystems that hosts the Oracle db-files with the vxfs option 'convosync=direct'. That will ensure that Oracle data isn't buffered two times (once in Oracle, once in HP-UX).

Rgds
Johan

[Charles McCary]

Hi - also wanted to throw something else on the table about the va7100 we're using. Right now we are running on firmware version 11, with plans to upgrade to 16 very soon. Initially when we set up the luns for this array we were told that to use the alternate path would actually cause a performance decrease. So we set up all Luns on the primary path only to each array (we have 3 7100's) and didn't use striping due to the perf. hit. Now supposedly at f/w 16 performance is increased with the va7100 and I've still heard that using the alt. path could cause a perf. hit. Anyone have any info. or ideas on this?

thanks,

c

[harry d brown jr]

Charles,

Who told you using an alternate path would cause performance issues?

That's like saying if you use all four tires on your care you are going to have performance problems, so you should only use two, but unless you are Joey Chitwood you're not going to be able to accomplish that feat.

And yes, upgrade your FW to rev 16.

And I would add at least 2GB more of ram.


live free or die
harry

[Charles McCary]

Harry,

Backline disk engineer whom I've had previous experience with told me that. I actually trust him on this one. If you have a va7100, try doing some dd's over the prim. path vs. the alt path. The alt. path is significantly slower. That may change when we do the fw upgrade, but as of right now I've seen it with my own eyes.

c