AIX Configuration & Tuning For Oracle DB

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
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
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
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
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
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
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
Do not overcommit real memory!

8/29/2008
CPU Considerations
Use SMT with AIX 5.3

environments
#smtctl -m on -w now
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.
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
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)
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
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
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
-orUse dio mount option
Use cio mount option
-orUse cio mount option
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
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
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"
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
8/29/2008

AIX Configuration & Tuning For Oracle DB

Uploaded by

Copyright:

Available Formats

AIX Configuration & Tuning For Oracle DB

Uploaded by

Document Information

Original Description:

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

AIX Configuration & Tuning For Oracle DB

Uploaded by

Copyright:

Available Formats

AIX Configuration & Tuning for Oracle DB

IBM Advanced Technical Support

2006 IBM Corporation

2006 IBM Corporation

AIX Configuration for Oracle starting points

2006 IBM Corporation

Preparing the runtime environment

Maximum number of PROCESSES allowed per user (smit chgsys)

2006 IBM Corporation

Memory Tools - Virtual Memory Manager (VMM)

The -a option displays current parameter settings

The -o option is used to change parameter values

The -p option is used to make changes persist

VMM File System Cache Tuning (vmo)

minperm%=5 (default 20)

maxperm%=20 ( default 80)

maxclient%=20 (the default 80)

strict_maxclient=1 (the default) (AIX 5.2 ML04 or later)

VMM Page Stealing Thresholds

vmo o minfree=1440 o maxfree=1536 -p

2006 IBM Corporation

AIX Paging Space

Monitor paging activity:

Resolve paging issues:

SGA may be pinned on AIX, but not generally recommended

Do not overcommit real memory!

Use SMT with AIX 5.3

2006 IBM Corporation

DLPAR & Oracle

Data Layout for Optimal I/O Performance

Data Layout contd

Use AIX Logical Volume 0 offset (9i Release 2 or later)

AIX File Systems

No longer being enhanced

Enhanced JFS (JFS2)

Concurrent I/O (CIO) mount/open option

DIO, with write serialization

General Parallel File System (GPFS)

2006 IBM Corporation

Cached vs. non-Cached (Direct) I/O

Use cio mount option

2006 IBM Corporation

Oracle Database File Access

Redo Log/Control Files

Archive Log Files

Network Options (no) Parameters

Also confirm these are set properly at network interface

2006 IBM Corporation

Additional Network (no) Parameters for RAC:

Set udp_recvspace = 4 * udp_sendspace

Must be < sb_max

Oracle Information Sources - External

You might also like