memory performance problem

[Stanimir]

Hi all,
I have an performance problem with my system:
hpux11.23 + oracle10.2 + application.
The system is working slow,and what I see is:

1.Performance Monitor show me
average cpu usage 70% /normal/
average I/O - /normal, believe me :)/
average net usage: 120% more, than previous day,
average Mem usage: 80%, but "Virtual memory queue" is increased unnormally /2-3 times more, than previous day/.

2.Here an output of some commands:
#vmstat -n
VM
memory page faults
avm free re at pi po fr de sr in sy cs
6938172 5313567 2214 21 838 0 0 0 0 26871 380866 10274
CPU
cpu procs
us sy id r b w
24 10 66 40 16 0
24 10 66
24 9 66

So I see that I have "pi=838" and some blocked procs?


#swapinfo -tam
Mb Mb Mb PCT START/ Mb
TYPE AVAIL USED FREE USED LIMIT RESERVE PRI NAME
dev 16384 0 16384 0% 0 - 1 /dev/vg00/lvol2
dev 16384 0 16384 0% 0 - 1 /dev/vg01/lvol1
dev 16384 0 16384 0% 0 - 1 /dev/vg01/lvol2
dev 16384 0 16384 0% 0 - 1 /dev/vg01/lvol3
dev 16384 0 16384 0% 0 - 1 /dev/vg01/lvol4
dev 4460 0 4456 0% 0 - 2 /dev/vg01/lvol5
reserve - 86358 -86358
memory 130935 102845 28090 79%
total 217315 189203 28108 87% - 0 -




My questions:

1.What mean, that I have memory usage like normal, but "Virtual Memory Queue" so increased. What is an physical meaning of the
concept: "Virtual Memory Queue". Is that mean, that I have enough memory, but Oracle processes cannot use it effectivelly for some reason?

2."pi=838" - is there any connection with the above.

Thank you.

[Steven E. Protter · Accepted solution]

Shalom,

Virtual memory queue? Not an often used term.

http://64.233.183.104/search?q=cache:e4wcHHDCpGgJ:h50237.www5.hp.com/PartnerPortal/Forum/Messages/DownloadAttachment.aspx%3FAID%3De00be2a0-14ce-44c7-bef7-7d0a0c7854a3+%22Virtual+Memory+Queue%22+hpux&hl=iw&ct=clnk&cd=1&gl=il&client=firefox-a

I would in this situation take a look at shared memory settings.

shmmax
shmseg

It's possible that shared memory is all used up and that is causing the perofrmance problems.

Pages in is 838 but pages out is zero, which means you are not actually paging. Virtual memory is always reserved any time a process opens so there is a place to put it if needed.

If Oracle is really hungry then maxdsiz_64 and its 32 bit parter (whichever is relavent) is another potential problem area to look at.

You have good tool's but here are a few more:
http://www.hpux.ws/system.perf.sh

SEP

[A. Clay Stephenson]

The pi (page in) rate is nothing to worry about. Every new program that is executed is not in memory and must be read in from disk. That triggers a page fault and a page in satifies that fault. Pi's are completely normal and are nothing to worry about. Page outs are something quite different. This is really the only column in the vmstat display that you should be concerned about. This indicates that there is not enough room in physical memory to page in needed requests so some pages must be sent to swap to make room. Since your po rate is zero, you really have no problems. Note also that your swapinfo display shows that no actual swap is being used (consistant with the 0 po rate). The "memory" line in swapinfo indicates that pseudoswap is enabled (swapmem_on=1) but all that is is kernel arithmatic to increase process reservation space and is intended for those systems which have more physical memory than swap space.

[Stanimir]

Thank you for your responces,so my
K-parameters are :

shmmax 137304129536 /= avail memory 128G /
shmseg 300 default
maxdsiz 1073741824 Default maxdsiz_64bit 2147483648

swapmem_on=1, but I dont thing to change it

So what is your recomendation for these settings? Any other related K-parameters to the sittuation?

[Stanimir]

And also:

shmmni 512

[Steven E. Protter]

Shalom,

Wow that box has a lot of ram.

If its a heavy oracle server, I'd crank shmmax to 25% of ram. Maybe 25% of memory, which HP defines as ram plus swap.

Further, if you have a lot of open oracle processes shmseg at 512 may not be enough. I generally get it up high to not impede Oracle from creating new shared memory segments.

If you want to check usage, you'll need to check with glance.

This seems like a kernel tuning issue, you definitely have enough physical memory.

SEP

[Stanimir]

Hi Steven,
Thank you for your advice, I have already
changed these params /they are dynam /:

shmmni 650
shmseg 400

Is this enough? I have fear for increasing params so much. I'll look for any change of performance next day.

Anyway I'm afraid,that there is another reason, an mechanizm,which constrain Ora-procs to use available memory.But this is only my suppose. If you have any suggesstion - will be welcome!

Thank you again, the points are comming :)

[Steven E. Protter]

Shalom,

shmmni 650
shmseg 400

Is this enough? I have fear for increasing params so much. I'll look for any change of performance next day.

Very hard question to answer.

I'd want to know how many shared memory segments I was using. This is however a parameter that can be cranked up higher with little chance of negative impact.

Couple of links might help here:
http://docs.hp.com/en/B3921-90010/shmseg.5.html

Tuning guidelines.

http://www.docs.hp.com/en/J2716-90017/ch06s05.html

SEP

[TwoProc]

Have you checked how your memory is cut up for your Oracle Shared memory?

Run ipcs -am and have a look at the number of spots taken by the user that owns and runs the database (often it's "oracle").

Assuming that you have only database instance running, you should have your shared memory in no more than 6 cuts. If it is cut up in hundreds of little pieces, then it becomes very inefficient because, from what I've been told (caveat emptor), Oracle will only handle 6 or 7 (can't remember the number for sure) of the sliced memory segments actively. Above that number and the memory requests start becoming something approaching a fully queued state. This is information I got from the joint HP/Oracle tuning team for Oracle 9i - don't know if it's relevant to 10G. Anyway, try keeping the number of shared memory areas down to 6 or less, by increasing the size of shmmax for both 64 bit and 32 bit kernel params.

Overall though, I can't see how you were doing fine before, and suddenly it's running slow. The above discussion shouldn't really address your issue, if all was well before.

Did you perhaps, have an unclean shutdown leaving old shared memory segments for Oracle out there in the memory space? Once again, you'd see that with the "ipcs -am" command, and you'd look for initialization times older than your database has been running, and/or low to no NATTCH processes to the segments - depending on whether or not your presumed unclean shutdown pulled ALL of the Oracle processes down, or left some pieces of it out there still trying to run.


Another scenario, do you have any statistics on key/active tables out of whack?

Look at your longest running *active* connections in Oracle, are many of them all running/waiting on the same statements to run? Pick an active one with longest time since "last call" (this is easiest to find using Toad with either session browser or DBA/Kill/Trace Session if you have it). Look at the statements, if they seem to be similar either in statement or in tables/functions being called; pick a sid of one of them and run the following:

select *
from v$session vs, dba_objects do
where vs.row_wait_obj#=do.object_id
and vs.sid = &sid;

Look for the object_name and object_type columns returned.

If the answer is a table or an index, and you get many of the same/similar answers for many of the active processes running since their
last call time (I often sort on this in Toad), then you may have need to refresh some stats on those tables or indexes involved. If the tables are HUGE try using a low sampling percentage so that it doesn't take too long - like 10% or so, if the table is relatively small - go ahead and sample bigger, 30 or 40 percent. Keep in mind that while statistics are being gathered on monstrous tables, performance will be even further degraded until its complete. If the pain threshold is too high, you break the gather without hurting anything further, you'll just be back where you were before.