Lately, Jordan bank upgraded their ICBS banking software to Oracle 9i and IDS9i. The Bank also moved from a decentralized to a centralized system.
We were contracted to monitor and diagnose performance issues during the launching phase of the new system.
The IBM server was equipped with 32 GB of RAM, The Oracle SGA was around 8GB of RAM. Around 2000 sessions were running on the system.
every now and then, high swapping activity was noticed, and the performance tended to degrade the longer the system is running.
The cause of this phenomenon is attributed to the AIX Virtual Memory Manager. AIX keeps steeling memory pages to enhance the buffer of File I/O. Since Oracle SGA is buffer file I/O already, such Operating System feature is not suitable for Relational Database Management Systems. Not only that, but SGA blocks end up being paged from physical memory into paging, causing undesirable performance impact.
The solution for this memory management problem configure memory management in way that would decrease AIX physical memory acquisition for buffering file IO.
The parameters that affect the described behaviour are shown in bold below. Note that the shown values are the default values and are not appropriate for RDBMS
memory_frames = 1572864
pinnable_frames = 1431781
maxfree = 1088
minfree = 960
minperm% = 20
minperm = 294356
maxperm% = 80
maxperm = 1177427
strict_maxperm = 0
maxpin% = 80
maxpin = 1258292
maxclient% = 80
lrubucket = 131072
It is recommended to modify the minperm% to 10-15%, maxperm% to 30% and maxclient% to value equal to maxpers% (30% in this case)
The following will enable you to find out how much memory is used by the AIX buffer system
# svmon -G
size inuse free pin virtual
memory 131072 128122 1800 10200 48990
pg space 262144 98220
work pers clnt lpage
pin 10203 0 0 0
in use 44030 84326 6244 0
add the two number highlighted in yellow (90570) which gives you the total memory stolen for buffering file I/O.
The number highlighted in RED in the total physical memory (131072)
Therefore, the percentage of memory used for buffering to the total memory is
90570/131072 or 69%. You can keep adjusting minperm and maxperm until this percentage goes down to a reasonable level and paging of SGA stops.
On AIX, there is an init.ora parameter called SGA_LOCK, which helps you to lock the SGA into physical memory.
a new AIX parameter lru_file_repage is supposed to have an effect similar to SGA_LOCK
