Re: Oracle connections and memory usage
From: Lamar Saxon (Lamar.Saxon_at_AMERICREDIT.COM)
Date: 11/10/05
- Previous message: Vincent D'Antonio: "Re: Oracle connections and memory usage"
- Maybe in reply to: Vincent D'Antonio: "Oracle connections and memory usage"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
Date: Thu, 10 Nov 2005 12:44:17 -0600 To: aix-l@Princeton.EDU
Actually the 2x CPUs is a function of SMT on AIX 5.3. ( Symmetric Multi
Threading ).
Lamar
_____
From: IBM AIX Discussion List [mailto:aix-l@Princeton.EDU] On Behalf Of
Vincent D'Antonio
Sent: Thursday, November 10, 2005 12:36 PM
To: aix-l@Princeton.EDU
Subject: Re: Oracle connections and memory usage
Mark,
Thanks for the info I will tey this and see what happens. Also
something with the P5 servers it is an 8 way system but lparstat -i
shows me with 8 virtual CPU's also. May be something to do with the
dual core design of the CPU's?
Thanks
Vince
On 11/10/05, Hunter, Mark <Mark.Hunter@anheuser-busch.com> wrote:
I notice you have 16 cpus, not 8. Shouldn't make a difference
though.
Without any users, you have 75% of the boxes RAM in use. That
leaves less than 8GB for your 10.5GB worth of connections. Yes, you are
going to swap.
Tune minperm% to 3, maxperm% to 5, maxclient% to 5. With 32GB,
and especially with Oracle, you don't need a large disk cache. 5% is
still 1.5GB , so you should be fine.
Have the oracle team look at decreasing the SGA from 16GB to
12GB. See what it does to the cache hit %.
This should give you enough room to run 1500 connections.
Mark Hunter
_____
From: IBM AIX Discussion List [mailto:aix-l@Princeton.EDU ] On
Behalf Of Vincent D'Antonio
Sent: Thursday, November 10, 2005 10:22 AM
To: aix-l@Princeton.EDU
Subject: Re: Oracle connections and memory usage
Mark,
We are using JFS2. and right now there is no load and no
connections.
Here is the vmstat 5 5 output:
System configuration: lcpu=16 mem=31744MB
kthr memory page faults cpu
----- ----------- ------------------------ ------------
-----------
r b avm fre re pi po fr sr cy in sy cs us sy id
wa
0 0 4646776 2721286 0 0 0 0 0 0 48 9853 388 1
0 93 6
1 0 4645713 2722347 0 0 0 0 0 0 158 99367 2190 7
4 83 6
0 0 4645713 2722369 0 0 0 0 0 0 47 499 351 0
0 93 6
0 0 4645716 2722365 0 0 0 0 0 0 45 2809 529 1
0 93 6
0 0 4645716 2722363 0 0 0 0 0 0 49 347 351 0
0 93 6
and topas:
Topas Monitor for host: sdp1 EVENTS/QUEUES
FILE/TTY
Thu Nov 10 11:21:49 2005 Interval: 2 Cswitch 333
Readch 5431
Syscall 743
Writech 19161
Kernel 0.2 |# | Reads 12
Rawin 0
User 0.1 |# | Writes 6
Ttyout 269
Wait 6.0 |## | Forks 0
Igets 0
Idle 93.7 |########################### | Execs 0
Namei 22
Runqueue 0.0
Dirblk 0
Network KBPS I-Pack O-Pack KB-In KB-Out Waitqueue 0.0
en7 2.5 24.5 2.5 1.4 1.1
en6 0.3 1.5 1.0 0.2 0.1 PAGING
MEMORY
lo0 0.1 1.0 1.0 0.0 0.0 Faults 257
Real,MB 31743
en8 0.0 0.0 0.0 0.0 0.0 Steals 0
% Comp 57.5
PgspIn 0
% Noncomp 9.8
Disk Busy% KBPS TPS KB-Read KB-Writ PgspOut 0
% Client 10.0
hdisk4 0.0 0.0 0.0 0.0 0.0 PageIn 2
hdisk7 0.0 0.0 0.0 0.0 0.0 PageOut 4
PAGING SPACE
hdisk28 0.0 0.0 0.0 0.0 0.0 Sios 6
Size,MB 32768
hdisk30 0.0 0.0 0.0 0.0 0.0
% Used 1.0
hdisk29 0.0 0.0 0.0 0.0 0.0 NFS (calls/sec)
% Free 98.9
hdisk6 0.0 0.0 0.0 0.0 0.0 ServerV2 0
hdisk11 0.0 0.0 0.0 0.0 0.0 ClientV2 0
Press:
hdisk13 0.0 8.0 0.5 0.0 8.0 ServerV3 0
"h" for help
ClientV3 0
"q" to quit
Name PID CPU% PgSp Owner
topas 1110202 0.1 1.1 root
hats_nim 135766 0.0 1.7 root
oracle 237782 0.0 7.7 oracle8
oracle 123506 0.0 7.7 oracle8
oracle 180690 0.0 7.6 oracle8
hatsd 119642 0.0 8.2 root
gil 25154 0.0 0.1 root
oracle 193420 0.0 10.0 oracle8
hats_nim 140212 0.0 1.7 root
On 11/10/05, Hunter, Mark <Mark.Hunter@anheuser-busch.com >
wrote:
Hmmm....
Looks like you have minperm%=5, maxperm%=10, and
maxclient%=10. Probably should be ok.
Are you using jfs or jfs2? jfs2 hard limits by default
but jfs does not. (strict_maxclient).
Oracle is using 22.5GB approx. How many connections is
this? I doubt you can get it, but what is the svmon for oracle just
after the database comes up before any connections (minimum usage)? Can
you get an svmon during the 800 connections?
Try
svmon -U | awk '$1~"User"{print;getline;print}'
also
vmstat 5 5
Also, if you can grab a screenprint of a topas session,
that might help.
_____
From: IBM AIX Discussion List
[mailto:aix-l@Princeton.EDU ] On Behalf Of Vincent D'Antonio
Sent: Thursday, November 10, 2005 9:08 AM
To: aix-l@Princeton.EDU
Subject: Re: Oracle connections and memory usage
Here is the requested info:
minperm = 388872
maxperm = 777747
maxclient% = 10
SGA is set to 16GB, there are no other appl. running on
this system just oracle.
out put of svmon -U is hugh but here is the beginning of
the output:
========================================================================
=======
User Inuse Pin
Pgsp Virtual LPageCap
oracle8 5695786 89342
68764 5751820 -
........................................................................
.......
SYSTEM segments Inuse Pin
Pgsp Virtual
65554 65541
1 65555
Vsid Esid Type Description LPage
Inuse Pin Pgsp Virtual
0 0 work kernel segment (lgpg_vsid=0) Y
65536 65536 0 65536
12b8a4 - work -
11 3 1 12
2739cf - work -
7 2 0 7
........................................................................
.......
EXCLUSIVE segments Inuse Pin
Pgsp Virtual
5621614 22340
68628 5677608
Numbers don't lie and I agree with you that I should be
able to get my 1500 connections no problem but the system seems to
disagree with me on this, maybe I am missing a process that is using
more memory than being reported but I couldn't find anything.
Thanks again for you suggestions,
Vince
On 11/10/05, Hunter, Mark
<Mark.Hunter@anheuser-busch.com > wrote:
More information please.
What are your vmo minperm%, maxperm%, and
maxclient%? Hopefully you have tuned these already.
How big is the oracle SGA? Most oracle DBA like
to set these really large.
What other application run on the box?
What does svmon -U using your RAM?
Based upon your numbers, 7MB * 1500 connection =
10.5GB of RAM, so you should have plenty.
Mark Hunter
Anheuser-Busch Cos.
MIS Consultant, ES&SO Server Planning and
Integration
*Office: (314) 632-6663
*Fax: (314) 632-6901
?Pager: (314) 841-4026
* Email: Mark.Hunter@Anheuser-Busch.com
The information transmitted (including
attachments) is covered by the Electronic Communications Privacy Act, 18
U.S.C. 2510-2521, is intended only for the person(s) or entity/entities
to which it is addressed and may contain confidential and/or privileged
material. Any review, retransmission, dissemination or other use of, or
taking of any action in reliance upon, this information by persons or
entities other than the intended recipient(s) is prohibited. If you
received this in error, please contact the sender and delete the
material from any computer.
_____
From: IBM AIX Discussion List
[mailto:aix-l@Princeton.EDU] On Behalf Of Vincent D'Antonio
Sent: Thursday, November 10, 2005 8:13 AM
To: aix-l@Princeton.EDU
Subject: Oracle connections and memory usage
AIXer's,
I have a P570 8way with 32GB of memory running
oracle 10G. Per my DBA each connection to the database is sucking up
7MB of memory which after a while we start to swap, this is about 800
connections and need we to support a lot more than this (1500).
Is there anything that I can tune to improve
memory usage or is there anything within oracle that can be tuned? I
have AIX 5.3 ML2 no lpars just a full system.
Any info to help this would be great.
Thanks in advance for your comments and
suggestions,
Vince
Privileged and Confidential. This e-mail, and any attachments there to, is intended only for use by the addressee(s) named herein and may contain privileged or confidential information. If you have received this e-mail in error, please notify me immediately by a return e-mail and delete this e-mail. You are hereby notified that any dissemination, distribution or copying of this e-mail and/or any attachments thereto, is strictly prohibited.
- Previous message: Vincent D'Antonio: "Re: Oracle connections and memory usage"
- Maybe in reply to: Vincent D'Antonio: "Oracle connections and memory usage"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
Relevant Pages
|
|
