AIX Configuration & Tuning for Oracle DB
IBM Advanced Technical Support
8/29/2008
2006 IBM Corporation
Agenda
Basic AIX Configuration/Tuning for Oracle
Preparing the runtime environment
Memory
I/O
Network
2006 IBM Corporation
8/29/2008
AIX Configuration for Oracle starting points
The suggestions presented here are considered
to be basic configuration starting points for
general Oracle workloads
Customer workloads will vary
Ongoing performance monitoring and tuning is
recommended to ensure that the configuration is
optimal for the particular workload
characteristics
2006 IBM Corporation
8/29/2008
Preparing the runtime environment
User Limits (smit chuser)
Soft FILE size = -1 (Unlimited)
Soft CPU time = -1 (Unlimited)
Soft DATA segment = -1 (Unlimited)
Soft STACK size -1 (Unlimited)
/etc/security/limits
Maximum number of PROCESSES allowed per user (smit chgsys)
maxuproc >= 2048
2006 IBM Corporation
8/29/2008
Memory Tools - Virtual Memory Manager (VMM)
The AIX vmo command provides for the display and/or
update of several parameters which influence the way AIX
manages physical memory
The -a option displays current parameter settings
vmo a
The -o option is used to change parameter values
vmo o minfree=1440
The -p option is used to make changes persist
across a reboot
vmo p o minfree=1440
A number of the default vmo settings are not optimized for
database workloads and should be modified for Oracle environments
2006 IBM Corporation
8/29/2008
VMM File System Cache Tuning (vmo)
File System related parameter defaults are optimized for large file
system cache, not for RDBMS workloads
Typical vmo settings for Oracle:
lru_file_repage=0 (default=1) (AIX 5.2 ML04 or later)
Forces file pages to be repaged before computational pages
minperm%=5 (default 20)
Target for minimum % of physical memory to be used for file system
cache
maxperm%=20 ( default 80)
Target for maximum % of physical memory to be used for JFS file
system cache
strict_maxperm=0 (the default)
Enables/disables enforcement of maxperm as a hard limit
maxclient%=20 (the default 80)
maxclient=maxperm
Target for maximum % of physical memory to be used for JFS2 file
system cache
2006 IBM Corporation
8/29/2008
strict_maxclient=1 (the default) (AIX 5.2 ML04 or later)
VMM Page Stealing Thresholds
The following define thresholds for the VMM page stealing process (lrud):
minfree
Set minfree = 120 x # logical CPUs
maxfree
Set maxfree = minfree + (MAX(maxpgahead, j2_maxPageReadAhead) x
# logical CPUs)
Example:
For a 6-way LPAR with SMT enabled, maxpgahead=8 and
j2_maxPageReadAhead=8:
minfree = 1440 = 120 x 6 x 2
maxfree = 1536 = 1440 + (max(8,8) x 6 x 2)
vmo o minfree=1440 o maxfree=1536 -p
2006 IBM Corporation
8/29/2008
AIX Paging Space
Allocate Paging Space:
Configure Server/LPAR with enough physical memory to satisfy memory requirements
With AIX demand paging, paging space does not have to be large
Generally, keep within internal drive
Monitor paging activity:
vmstat -s
sar -r
Lsps a
Resolve paging issues:
Reduce file system cache size (LRU_FILE_REPAGE, MAXPERM, MAXCLIENT)
Reduce Oracle SGA or PGA (9i or later) size
Add physical memory
SGA may be pinned on AIX, but not generally recommended
vmo p o v_pinshm=1
LOCK_SGA=TRUE
2006 IBM Corporation
Do not overcommit real memory!
8/29/2008
CPU Considerations
Use SMT with AIX 5.3
environments
#smtctl -m on -w now
2006 IBM Corporation
8/29/2008
DLPAR & Oracle
CPU
Oracle 9i
Oracle CPU count does not recognize change in # cpus
AIX scheduler can still use the added CPUs
Oracle 10g
Oracle CPU count recognizes change in # cpus
Memory
Oracle 9i or 10g
SGA can be dynamically resized, but has an upper bound
by the parameter SGA_MAX_SIZE.
2006 IBM Corporation
8/29/2008
Data Layout for Optimal I/O Performance
Stripe and mirror everything (SAME) approach:
Goal is to balance I/O activity across all disks, loops, adapters, etc...
Avoid/Eliminate I/O hotspots
Manual file-by-file data placement is time consuming, resource intensive and
iterative
Use RAID-5 or RAID-10 to create striped LUNs (hdisks)
Create AIX Volume Group(s) (VG) w/ LUNs from multiple
arrays, striping on the front end as well for maximum
distribution
Physical Partition Spreading (mklv e x)
orLarge Grained LVM striping (>= 1MB stripe size)
http://www-1.ibm.com/support/techdocs/atsmastr.nsf/WebIndex/WP100319
2006 IBM Corporation
8/29/2008
Data Layout contd
Stripe using Logical Volume (LV) or Physical Partition (PP) striping
LV Striping
Valid LV Strip sizes:
AIX 5.2: 4k, 8k, 16k, 32k, 64k, 128k, 256k, 512k, 1 MB
AIX 5.3: AIX 5.2 Strip sizes + 2M, 4M, 16 MB, 32M, 64M, 128M
Use AIX Logical Volume 0 offset (9i Release 2 or later)
Use Scalable Volume Groups (VGs), or use mklv T O with Big
VGs
The -T O option specifies that the device subtype should be z, which
causes Oracle to use a zero offset when accessing this raw logical
volume
PP Striping
Use minimum Physical Partition (PP) size (mklv -t, -s parms)
Spread AIX Logical Volume (LV) PPs across multiple hdisks in VG
(mklv e x)
2006 IBM Corporation
8/29/2008
Asynchronous I/O
AIX parameters (smit aio) -- only applicable to file system based configs
minservers = 10 * # cpus
maxservers = (10 * # disks) / # cpus
maxreqs = a multiple of 4096 > 4 * #disks * queue_depth
enable at system restart
Typical settings: minservers=100, maxservers=100,
maxreqs=16384
Oracle parameters (init.ora)
disk_asynch_io = TRUE
filesystemio_options = {ASYNCH | SETALL}
Monitor usage:
Watch for Oracle alert log or trace file messages:
Warning lio_listo returned EAGAIN
AIX Monitoring
pstat a | grep aios
iostat A (AIX 5.3)
NMON -A and -t options
2006 IBM Corporation
8/29/2008
AIX File Systems
Journaled File System (JFS)
Buffer caching (default) provides Sequential Read-Ahead, cached writes, etc.
Direct I/O (DIO) mount/open option
no caching on reads
No longer being enhanced
Enhanced JFS (JFS2)
Buffer caching (default) provides Sequential Read-Ahead, cached writes,
etc.
Direct I/O (DIO) mount/open option
no caching on reads
Concurrent I/O (CIO) mount/open option
disabled
DIO, with write serialization
Use CIO for Oracle .dbf, control files and online redo logs only!!!
General Parallel File System (GPFS)
Non-cached, non-blocking I/Os (similar to JFS2 CIO) for all Oracle files
The
IBM with
clustered
system
for performance
RAC
GPFS, or
JFS2
CIO file
offer
similar
as Raw Devices
2006 IBM Corporation
8/29/2008
Cached vs. non-Cached (Direct) I/O
File System caching tends to benefit heavily sequential workloads with low
write content. To enable caching for JFS/JFS2:
Use default filesystem mount options
Set Oracle filesystemio_options=ASYNCH
DIO tends to benefit heavily random access workloads and CIO tends to
benefit heavy update workloads. To disable JFS, JFS2 caching, see the
following table:
JFS
JFS2
Oracle 9i
Oracle 10g
Set filesystemio_options=SETALL
-orUse dio mount option
Set filesystemio_options=SETALL
-orUse dio mount option
Use cio mount option
Set filesystemio_options=SETALL
-orUse cio mount option
2006 IBM Corporation
8/29/2008
Oracle Database File Access
Data Base Files (DBF)
I/O size is db_block_size or db_block_size * db_file_multiblock_read_count
Use CIO or no mount options for extremely sequential I/O
If block size is >=4096, use a filesystem block size of 4096, else use 2048
Redo Log/Control Files
I/O size is always a multiple of 512 bytes
Use CIO or DIO and set filesystem block size to 512
Archive Log Files
Do not use CIO or DIO
rbrw mount option can be advantageous
Oracle Binaries
Do not use CIO or DIO
2006 IBM Corporation
8/29/2008
Network Options (no) Parameters
Set sb_max >= 1 MB (1048576)
Set tcp_sendspace >= 262144
Set tcp_recvspace >= 262144
Set rfc1323=1
Also confirm these are set properly at network interface
level
2006 IBM Corporation
8/29/2008
Additional Network (no) Parameters for RAC:
Set udp_sendspace = db_block_size *
db_file_multiblock_read_count
(not less than 65536)
Set udp_recvspace = 4 * udp_sendspace
Must be < sb_max
Increase if buffer overflows occur
Use Jumbo Frames
Examples:
no -a |grep udp_sendspace
no o -p udp_sendspace=65536
netstat -s |grep "socket buffer overflows"
2006 IBM Corporation
8/29/2008
Oracle Information Sources - External
Oracle Product Certification information: http://otn.oracle.
com/support/metalink/index.html
Oracle Pricing Information:
http://store.oracle.com/
Oracle Technology Network
http://otn.oracle.com
IBM Redbooks:
http://www.ibm.com/redbooks
Techdocs Technical Sales Library
http://w3.ibm.com/support/techdocs
2006 IBM Corporation
8/29/2008
