Details of Memory usage of a LPAR & Oracle 9i performance is slow. 1

[RiazAhamed]

Hi,

We have a production IBM p690 Regatta server running AIX 5L version 5.2 in LPAR mode. In one of the LPAR, performance of Oracle 9i is slow.

# lsps -a
Page Space Physical Volume Volume Group Size %Used Active Auto Type
hd6 hdisk0 rootvg 3520MB 2 yes yes lv

# vmstat -s -v | pg
1349384679 total address trans. faults
731306679 page ins
290215386 page outs
59700 paging space page ins
208652 paging space page outs
0 total reclaims
783758578 zero filled pages faults
862553 executable filled pages faults
1620185788 pages examined by clock
200 revolutions of the clock hand
672521399 pages freed by the clock
12511446 backtracks
0 lock misses
886636 free frame waits
0 extend XPT waits
76459706 pending I/O waits
1018655296 start I/Os
197148814 iodones
4145916536 cpu context switches
4962899911 device interrupts
0 software interrupts
0 traps
8402090711 syscalls
8388608 memory pages
7880777 lruable pages
445305 free pages
1 memory pools
758650 pinned pages
80.1 maxpin percentage
20.0 minperm percentage
80.0 maxperm percentage
79.8 numperm percentage
6293929 file pages
0.0 compressed percentage
0 compressed pages
79.9 numclient percentage
80.0 maxclient percentage
6304495 client pages
0 remote pageouts scheduled
0 pending disk I/Os blocked with no pbuf
92548 paging space I/Os blocked with no psbuf
2996 filesystem I/Os blocked with no fsbuf
0 client filesystem I/Os blocked with no fsbuf
17575766 external pager filesystem I/Os blocked with no fsbuf

Found that the numclient utilisation is 79.9 percentage and
maxclient utilisation is 80.0 percentage. Is there any problem?

Can some one tell me more on the memory usage details?
Let me know if you need more details.

Thanks.
Riaz Ahamed

 

[mrn]

Are you using JFS2 File System?

Mike

"A foolproof method for sculpting an elephant: first, get a huge block of marble, then you chip away everything that doesn't look like an elephant.

 

[RiazAhamed]

Hi Mike,

Yes, we are using JFS2 filesystems mostly, on this LPAR of the p690.

 

[RiazAhamed]

Hi,

this is the output of nmon64 from the 64bit AIX server.
Please advise, if there are anty problem in the memory usage.

nmon64 v9a Hostname=ax-dev-a02 Refresh=2.0secs 09:43.33
CPU Utilisation +-------------------------------------------------+
CPU User% Sys% Wait% Idle|0 |25 |50 |75 100|
0 2.0 0.5 0.0 97.5| > |
+-------------------------------------------------+
Memory Use Physical Virtual Paging pages/sec In Out VM parameters
% Used 99.9% 7.0% to Paging Space 0.0 0.0 numperm 34.3%
% Free 0.1% 93.0% to File System 0.0 28.5 minperm 18.7%
MB Used 4091.0MB 613.1MB Page Scans 0.0 maxperm 74.7%
MB Free 4.9MB 8090.9MB Page Cycles 0.0 minfree 120
Total(MB) 4096.0MB 8704.0MB Page Reclaim 0.0 maxfree 128
Disk I/O all data is Kbytes per second
DiskName Busy Read Write |0 |25 |50 |75 100|
hdisk1 0% 0.0 0.0KB|> |
hdisk0 0% 0.0 0.0KB| > |
hdisk2 0% 0.0 0.0KB|> |
hdisk3 0% 0.0 2.0KB|> |
hdisk4 0% 0.0 26.0KB|> |
hdisk5 0% 0.0 4.0KB|> |
hdisk6 0% 0.0 0.0KB|> |
hdisk7 0% 0.0 12.0KB|> |
hdisk8 0% 0.0 0.0KB|> |
hdisk9 0% 0.0 8.0KB|> |
hdisk10 0% 0.0 4.0KB|> |
hdisk11 0% 0.0 0.0KB|> |
hdisk12 0% 0.0 0.0KB|> |
hdisk13 0% 0.0 0.0KB|> |
hdisk14 0% 0.0 0.0KB|> |
hdisk15 0% 0.0 0.0KB|> |
hdisk16 0% 0.0 0.0KB|> |
hdisk17 0% 0.0 0.0KB|> |
hdisk18 0% 0.0 0.0KB|> |
hdisk19 0% 0.0 0.0KB|> |
hdisk20 0% 0.0 0.0KB|> |
hdisk21 0% 0.0 0.0KB|> |
hdisk22 0% 0.0 0.0KB|> |
hdisk23 0% 0.0 14.0KB|> |
hdisk24 0% 0.0 0.0KB|> |
hdisk25 0% 0.0 8.0KB|> |
hdisk26 0% 0.0 0.0KB|>

 

[RiazAhamed]

Following is the output of nmon64 of the memory and processes running in the p690 LPAR.

nmon64 v9a Hostname=ax-dev-a02 Refresh=2.0secs 09:50.15
Memory Use Physical Virtual Paging pages/sec In Out VM parameters
% Used 99.9% 7.0% to Paging Space 0.0 0.0 numperm 34.4%
% Free 0.1% 93.0% to File System 0.0 26.9 minperm 18.7%
MB Used 4091.0MB 613.1MB Page Scans 0.0 maxperm 74.7%
MB Free 5.0MB 8090.9MB Page Cycles 0.0 minfree 120
Total(MB) 4096.0MB 8704.0MB Page Reclaim 0.0 maxfree 128
Top Processes Procs=320 mode=3 (1=Basic, 2=CPU 3=Perf 4=Size 5=I/O w=wait-procs)
PID %CPU Size Res Res Res Char RAM Paging Command
Used KB Set Text Data I/O Use io other repage
594068 0.5 53976 24200 19596 4604 0 1% 0 0 0 oracle
1060952 0.5 58244 28472 19596 8876 0 1% 0 0 0 oracle
1462494 0.5 2556 2596 128 2468 1130 0% 0 0 0 nmon64
139332 0.0 56 52 0 52 0 0% 0 0 59 reaper
143430 0.0 64 36 0 36 0 0% 0 0 0 lrud
147528 0.0 56 36 0 36 0 0% 0 0 0 xmdetd
151626 0.0 56 32 0 32 0 0% 0 0 0 vmptacrt
155724 0.0 80 56 0 56 0 0% 0 0 0 pilegc
159822 0.0 56 44 0 44 0 0% 0 0 0 xmgc
163920 0.0 56 44 0 44 0 0% 0 0 0 netm
168018 0.0 124 88 0 88 0 0% 0 0 0 gil = TCP/IP
172116 0.0 56 32 0 32 0 0% 0 0 0 wlmsched
180436 0.0 468 208 16 192 0 0% 0 0 0 inetd
188558 0.0 56 32 0 32 0 0% 0 0 0 vpath_deamon
196768 0.0 404 116 36 80 0 0% 0 0 0 syslogd
200778 0.0 796 236 220 16 0 0% 0 0 0 ksh
204914 0.0 68 32 0 32 0 0% 0 0 0 kbiod
209048 0.0 56 32 0 32 0 0% 0 0 0 rtcmd
213206 0.0 56 32 0 32 0 0% 0 0 0 lvmbb
217202 0.0 136 112 0 112 0 0% 0 0 0 j2pg
221368 0.0 548 16 0 16 0 0% 0 0 0 srcmstr
229560 0.0 644 276 4 272 0 0% 0 0 0 syncd
237568 0.0 57464 27688 19596 8092 0 1% 0 0 0 oracle
241790 0.0 260 16 0 16 0 0% 0 0 0 nfsd
245830 0.0 1780 628 164 464 0 0% 0 0 0 sendmail
250032 0.0 892 88 0 88 0 0% 0 0 0 errdemon

 

[letis]

hello,

hope I don't spoil mrn answer ) I would only comment your first nmon output.

but first on your vmstat output, the maxperm and maxclient have their default setting of 80%, which means that at a moment, 80% of your physical memory can be filled with files from jfs filesystems (maxperm) or jfs2 / cdrfs / nfs filesystems (maxclient). This is not a recommanded setting for a rdbms server, which would rather be anywhere between 15 and 40%, it depends.

numperm should be the current amount of RAM used to cache jfs files, and numclient should be the current amount of RAM used to cache jfs2 / Cdroms / nfs files.

These numbers are inconsistent in the vmstat output, or there's something subtle i don't understand, how can they be both around 79 % ??? it's possible that numclient is a subset of numperm but vmstat man page does not tell.


whatever, you perhaps have pagination due to these settings during peak hour, and therefore some slow down, but currently the system looks rather quiet, is this the case ?

regards,

 

[DSMARWAY]

hi ,

how big is your database ?
how big is your SGA in your database ?

Are users experiencing slow performance when running queries?

Have you run statistics against the database ?

is the problem only on 1 partition ?
what do you use fro yor disks ie.. EMC,SSA etc..

 

[sbix]

Try the Concurrent I/O mount option for the filesystems hosting datafiles.
It will reduce the overhead of copying data from the AIX cache to the Oracle LRU DB cache.
It has been designed to reduce exactly this problem and to emulate the behaviour of raw devices, without losing the benefits of using filesystems

 

[AIX5L]

Hi

Is Asynchron I/O configured ? min 10 max 500
How many numfsbufs are defined ? i prefer 500
Change maxperm to 20% and minperm to 10%
Where is your Paging Space ? lsps -a
How big is the SGA
How many mem consuming the User oracle ? svmon -U oracle

 

[RiazAhamed]

Thanks ALL for your fast response.

For letis;
I changed the maxclient value & maxperm value to 40% for the LPAR and rebooted the same. This new values are in effect now, but the memroy usage is still 98% average.

nmon64 v9a Hostname=ax-dev-a02 Refresh=2.0secs 09:42.50
CPU Utilisation +-------------------------------------------------+
CPU User% Sys% Wait% Idle|0 |25 |50 |75 100|
0 0.5 0.5 0.0 99.0| > |
+-------------------------------------------------+
Memory Use Physical Virtual Paging pages/sec In Out VM parameters
% Used 99.8% 1.1% to Paging Space 0.0 0.0 numperm 31.9%
% Free 0.2% 98.9% to File System 0.0 7.0 minperm 18.7%
MB Used 4089.6MB 98.7MB Page Scans 0.0 maxperm 37.4%
MB Free 6.4MB 8605.3MB Page Cycles 0.0 minfree 120
Total(MB) 4096.0MB 8704.0MB Page Reclaim 0.0 maxfree 128


For DSMARWAY;
I don't know how big the SGA in the database.
I didnot run statistics against the database, since I though the DBA's would've done that. Otherwise, how can I run the statistics against the database?
This problem exists in a couple of LPAR's of an IBM p690 system.
I am trying to fix the AIX server performance problem on a test LPAR and once I am sucessfull, I'll do the same on a production LPAR of the p690.
We use IBM Shark for disk storage in our environment.

For AIX5L;
I need to check whether AIO is configured or not.
I'll change the maxperm, minperm and numfsbufs to the values, as you specified..

# /> lsps -a
Page Space Physical Volume Volume Group Size %Used Active Auto Type
paging03 vpath18 oraclevg 1024MB 2 yes yes lv
paging02 vpath19 oraclevg 1024MB 2 yes yes lv
paging01 vpath20 oraclevg 1024MB 2 yes yes lv
paging00 vpath0 oraclevg 1024MB 2 yes yes lv
hd6 hdisk0 rootvg 4608MB 1 yes yes lv
# />

I don't know how big is te SGA. How can I check?

I can't paste the entire output of the command "svmon -U oracle" here, since it's large. I can email you as a text file, if you want to analyse.

I am only pasting a portion of this command, as follows;

# /> svmon -U oracle | pg

===============================================================================
User Inuse Pin Pgsp Virtual LPageCap
oracle 621652 7088 5360 514184 -

...............................................................................
SYSTEM segments Inuse Pin Pgsp Virtual
5966 3886 526 6452

Vsid Esid Type Description LPage Inuse Pin Pgsp Virtual
0 0 work kernel segment - 5921 3873 526 6407
d18ad - work - 45 13 0 45

...............................................................................
EXCLUSIVE segments Inuse Pin Pgsp Virtual
611291 3202 4747 502660

.
.
.
.
.
.
191419 - clnt /dev/fslvoracle:202190 - 0 0 - -
141734 - clnt /dev/fslvora5:22 - 0 0 - -
191579 - clnt /dev/fslvora11:42 - 0 0 - -

...............................................................................
SHARED segments Inuse Pin Pgsp Virtual
4395 0 87 5072

Vsid Esid Type Description LPage Inuse Pin Pgsp Virtual
d09ad 90000000 work loader segment - 2167 0 16 2347
a09aa d work loader segment - 2043 0 21 2566
161156 90020014 work shared library text - 109 0 47 148
1810d8 1 clnt code,/dev/hd2:147 - 55 0 - -
171157 9fffffff clnt shared library text, - 10 0 - -
/dev/hd2:2566
1009f0 9ffffffe work other kernel segments - 9 0 3 11
310c3 - clnt /dev/hd2:26074 - 2 0 - -
# />

 

[sbix]

SGA is determined on parameter given inte initYOUR_ORACLE_SID.ora file, ask to you DBA for info or run from the ora_user account the following
sqlplus '/ as sysdba'
select sum(value)/1024 from v$parameter where name in ('db_cache_size', 'shared_pool_size');
exit

you will an approximated value for you SGA in KB

I still suggest to use the concurrent I/O mount option which has been developed exactly to reduce memory problems

 

[nixchic]

Hi,
I experienced the same issue on one of our LPARS. AIX 5.2 running Oracle 9i. I noticed paging on the system. I adjusted the following settings, and noticed a marked improvment.

default set
maxperm 80% 20%
minperm 20% 10%
maxclient 80% 19%
maxfree 256 1152
minfree 240 1080
maxpgahead 8 64




IBM Certified Specialist pSeries AIX system administration

 

[RiazAhamed]

Thanks for all your help.

 

[AIX5L]

Hi

If you increase the maxpgahead only jfs FS are affected.
Do you know your Applications I/O
If you increase the maxpgahead only io-streams and big Files are affected. If you have Applications on the same Server with small Files it can result in poor I/O for this application.