Performance problem

[djessi]

Hi,
I have a performance problem on an AIX system V5.2 running Oracle V.9, the problem is more serious when an sql is executed (swapping increase and nobody cannot connect via telnet till the end of the request).
I permanently have at least the following results, please help to interpret and find solutions.
Thanks
Djessi

---------------------------------------------

Topas Monitor for host: Sdwh001 EVENTS/QUEUES FILE/TTY
Fri Jan 26 09:42:23 2007 Interval: 2 Cswitch 3376 Readch 34.1M
Syscall 16191 Writech 1172.4K
Kernel 11.5 |### | Reads 2624 Rawin 0
User 88.5 |######################### | Writes 1284 Ttyout 0
Wait 0.0 | | Forks 6 Igets 1
Idle 0.0 | | Execs 7 Namei 701
Runqueue 3.5 Dirblk 361
Network KBPS I-Pack O-Pack KB-In KB-Out Waitqueue 1.0
en1 513.0 783 741 116.0 910.0
lo0 0.0 0 0 0.0 0.0 PAGING MEMORY
en0 0.0 0 0 0.0 0.0 Faults 2469 Real,MB 8191
Steals 90 % Comp 35.0
Disk Busy% KBPS TPS KB-Read KB-Writ PgspIn 0 % Noncomp 61.1
hdisk1 7.0 68.0 17 0.0 136.0 PgspOut 1 % Client 27.6
hdisk0 5.5 66.0 16 0.0 132.0 PageIn 66
skpower1 2.5 446.0 33 20.0 872.0 PageOut 208 PAGING SPACE
hdisk23 2.0 214.0 17 12.0 416.0 Sios 178 Size,MB 4096
hdisk19 1.0 232.0 16 8.0 456.0 % Used 69.6
hdisk2 0.0 100.0 12 0.0 200.0 NFS (calls/sec) % Free 30.3
hdisk24 0.0 96.0 13 0.0 192.0 ServerV2 0
skpower4 0.0 48.0 6 0.0 96.0 ClientV2 0 Press:
hdisk31 0.0 24.0 3 0.0 48.0 ServerV3 0 "h" for help
hdisk30 0.0 168.0 9 256.0 80.0 ClientV3 0 "q" to quit
hdisk41 0.0 24.0 3 0.0 48.0
hdisk40 0.0 172.0 9 256.0 88.0
hdisk26 0.0 0.0 0 0.0 0.0
skpower2 0.0 0.0 0 0.0 0.0
skpower3 0.0 340.0 18 512.0 168.0
skpower0 0.0 196.0 25 0.0 392.0
hdisk42 0.0 0.0 0 0.0 0.0
hdisk44 0.0 0.0 0 0.0 0.0
hdisk18 0.0 0.0 0 0.0 0.0
hdisk45 0.0 0.0 0 0.0 0.0

Name PID CPU% PgSp Owner
oracle 40978 37.0 2.2 bdm
oracle 51458 19.2 2.3 bdm
oracle 35300 9.0 2.4 oracle
oracle 49576 1.7 2.1 bdm
aioserver 15146 1.7 0.0 root
topas 45166 0.7 0.9 root
ksh 56594 0.7 0.6 bdm
sqlplus 54882 0.5 0.9 bdm
aioserver 13702 0.2 0.0 root
aioserver 15870 0.2 0.0 root
aioserver 20374 0.2 0.0 root
aioserver 19134 0.2 0.0 root
aixmibd 23812 0.0 0.6 root
aioserver 37670 0.0 0.0 root
naviagent 25708 0.0 3.0 root
uxioserv 14036 0.0 0.7 ocm513a
aioserver 18290 0.0 0.0 root
uxioserv 24776 0.0 3.2 ocm513a
uxcdjsrv 19438 0.0 1.7 ocm513a
tina_daem 28244 0.0 2.2 root



--------------------------------------------------------------------------------------------------



Sdwh001 />vmstat 1 10
kthr mémoire page erreurs cpu
----- ----------- ------------------------ ------------ -----------
r b avm fre re pi po fr sr cy in sy cs us sy id wa
3 3 869753 100841 0 30 11 971 2615 0 963 12068 2479 69 11 11 8
4 0 869777 100688 0 0 0 0 0 0 1282 15269 2548 92 8 0 0
4 0 869777 100424 0 0 0 0 0 0 1613 20115 3725 92 8 0 0
4 0 869777 99995 0 0 0 1 3 0 1779 21668 3861 88 12 0 0
4 0 869782 99885 0 0 1 152 383 0 1697 21641 3311 94 6 0 0
3 0 869782 100008 0 0 0 424 1051 0 1721 22097 4221 91 9 0 0
4 0 869782 99887 0 0 0 327 453 0 1705 21472 4376 90 10 0 0
3 0 869797 99875 0 0 0 267 409 0 1565 27273 3054 90 10 0 0
4 0 869797 99635 0 0 0 16 20 0 1708 34193 3355 84 16 0 0
4 0 869797 99884 0 0 0 615 731 0 1766 30808 3541 88 12 0 0
Sdwh001 />


------------------------------------------------------------------------------------------------------

Sdwh001 />vmo -a
memory_frames = 2097152
pinnable_frames = 1973999
maxfree = 100008
minfree = 100000
minperm% = 20
minperm = 397034
maxperm% = 80
maxperm = 1588138
strict_maxperm = 0
maxpin% = 80
maxpin = 1677722
maxclient% = 80
lrubucket = 131072
defps = 1
nokilluid = 0
numpsblks = 1048576
npskill = 8192
npswarn = 32768
v_pinshm = 0
pta_balance_threshold = 50
pagecoloring = 0
framesets = 2
mempools = 1
lgpg_size = 0
lgpg_regions = 0
num_spec_dataseg = n/a
spec_dataseg_int = n/a
memory_affinity = 1
htabscale = -1
force_relalias_lite = 0
relalias_percentage = 0
Sdwh001 />


-----------------------------------------------------------------------------------------------------

Sdwh001 />lsps -a
Espace de pagination Volume physique Groupe de volumes Taille %Utilisé Active Auto Type
paging00 hdisk1 rootvg 2048Mo 70 oui oui lv
hd6 hdisk0 rootvg 2048Mo 70 oui oui lv
Sdwh001 />


----------------------------------------------------------------------------------------------------

 

[RMGBELGIUM]

okay :

minfree pct = 10
maxfree pct = 20
maxclient pct = 20

start with that.
your bdm app is taking a lot of cpu,and the run queue is constantly at 3-4.
This baby needs an upgrade Add a cpu , and some memory to prevent excessive swapping.

rgds,

R.

 

[RodKnowlton]

Your total work set is using roughly 3.3GB (avm * 4 / 1024 = work set memory in megabytes) and you've averaged 41 paging space accesses per second since boot (first line of vmstat).

You need to allocate more of memory to computational pages, which you do by reducing the allocation for non-computational pages (perm pages, of which client pages are a subset). 3.3GB is a little over 41% of your physical memory. If we round that up to 55% for a safety buffer, we get:

minperm pct = 10
maxperm pct = 45
maxclient pct = 45


I believe RMGBELGIUM meant to use "perm" instead of "free" in their suggestions also. Tightening down to maxperm=20 would unnecessarily reduce AIX's file caching memory. Any memory that Oracle is using for caching should already be represented in the avm figure, if that vmstat was at the peak of memory usage (the avm figure in the first line is not the average but the current, unlike the rest of the line). Since the avm is climbing throughout the posted vmstat, it's probably not the peak, so you should track it through a typical day and find the highest value, then calculate as explained above.

- Rod


IBM Certified Advanced Technical Expert pSeries and AIX 5L
CompTIA Linux+
CompTIA Security+

Wish you could view posts with a fixed font? Got Firefox & Greasemonkey? Give yourself the option.

 

[jprabaker]

You need to ensure that lru_file_repage=0 in your vmo settings. You've got 8G of memory and only using 3G so setting lru_file_repage should stop the paging - you don't need to set minperm%, maxperm% or maxclient% - the defaults should be fine with your 8G. If the parameter isn't there then upgrade your ML level to (i think) at least ML4. 5.2 below ML4 is known to be particularly bad with paging.

And buy a CPU!

 

[mrn]

I think I'd be looking at a re-write of the sql that's causing the problem first.

Mike

"Whenever I dwell for any length of time on my own shortcomings, they gradually begin to seem mild, harmless, rather engaging little things, not at all like the staring defects in other people's characters."