Oracle and IBM AIX 5.2  swapping and memory issues   June 25th, 2006

 

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