The Process of Tuning DB2 for AIX - IBM

|L.L. Bean Inc. |[pic] |
|DB2 Performance Recommendations | |
|Version 0.1, April 13, 2009 | |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
|Shelton Reese |
|Database Consultant |
| | |
Table of Contents
Table of Contents 2
Introduction 2
The Process of Tuning LL Bean's DB2 for AIX 5.5 3
Operating system settings 4
Performance fundamentals 4
Updating catalog statistics 7
Monitoring and tuning database configuration parameters 9
Buffer pool size 9
Log buffer size (LOGBUFSZ) 12
Application heap size (APPLHEAPSZ) 13
Sort heap size (SORTHEAP) and sort heap threshold (SHEAPTHRES) 13
Number of agents (MAXAGENTS, NUM_POOLAGENTS and NUM_INITAGENTS) 15
Locks (LOCKLIST, MAXLOCKS and LOCKTIMEOUT) 17
Maximum number of active applications (MAXAPPLS) 19
Number of asynchronous page cleaners (NUM_IOCLEANERS) 20
Number of I/O servers (NUM_IOSERVERS) 22
Number of commits to group (MINCOMMIT) 22
AIX Disk Layout Considerations 24
Conclusion 25
Appendix: DB2_STATUS (KORN SHELL Script to Perform the Configuration
Update) 25
Introduction L.L.Bean, Inc. is a privately held mail-order and retail company based in
Freeport, Maine, specializing in clothing and outdoor recreation equipment.
Its annual sales were USD $1.47 billion in 2005. LL Bean's Internet
interface requires use of multiple technology layers. Keeping these
applications performing optimally is an intense challenge because of the
scope and technological diversity of the application. Understanding the DB2 UDB architecture and all its implications is quite
challenging. Moreover, IBM provides over eighty parameters that impact the
tuning of the database manager or the underlying databases. Because the
parameters interact in complex ways, it is easy to adjust a parameter to
address a particular performance issue, only to have unforeseen detrimental
results in another area of the database. At LL Bean the key problem is
Database Configuration problems causing the overall performance to be
negatively affected. All numbers used in this document were collected from
the ECOMP DB2 instance. All software has been delivered via the support
directory on the production server. IBM was brought in to resolve the performance issues that LL Bean was
experiencing. As part of the effort to resolve the performance problems, I
installed and executed seven pieces of software on the production "ecomp"
server at LL Bean, as follows. . db2_status.ksh -d --> this script waits for the sampling
time and then gathers all the DB2 snapshot information into the IBM
snapshot tables. The script provides the following data for reviw:
. The hit ratio for the data and index pages of every bufferpool.
. The hit ratio for the catalog cache.
. The hit ratio for the package cache.
. The number of sorts serviced and number of sorts rejected.
. The average elapsed time for each sort.
. The update command to update the configuration to proper settings
. aix_check.ksh . This script reads the AIX configuration and
reports on any problem that might be found with possible suggestions. The Process of Tuning LL Bean's DB2 for AIX 5.5 This feedback is organized in three sections of this document, as shown
below. Within these sections, I will provide the approach I used to
identify problems and the actions I suggest for solving them.
Fundamentals to improve LL Bean's database performance based on operating
system settings, which provides essential recommendations based on the
analysis of documents and snapshots taken 11/11/07 and 11/14/07. All
documentation is stored on the LL Bean ~/support/snapout directory. Based
on runtime timestamp for reference.
Updating catalog statistics, which emphasizes the importance of collecting
and maintaining up-to-date database statistics, the lack of which is often
the source of many of the performance problems.
Monitoring and tuning LL Bean's database configuration parameters, which
describes a list of database manager parameters and database parameters in
the order of importance. Normally it's not necessary to go through the
whole list to achieve the performance goals. I only recommend that several
of them be adjusted to gain performance improvement.
With these procedures, IBM can get LL Bean's Internet application up and
running with verifiable and reasonably good performance.
Operating system settings
Recommendation
The following settings are recommended to allow maximum resource usage of
AIX by DB2. CPU, Memory & Kernel ulimit
'ulimit' defines file descriptors limits. It specifies the number of open
files permitted. If this value is set too low, a memory allocation error
will occur on AIX and a too many files open will be logged to the to the
stderr log file. Set this value higher than the default system value. For
large multi-processor machines, set to unlimited.
How to change the parameter
# ulimit -a // to query
# ulimit -n // to set to a new value
ulimit -n -1 // to set it to unlimited
Maximum Number of Processes Per User
'maxuproc' defines the max number of processes each AIX user can
'fork'. If it is set too low, you may not be able to create high
number of database agents necessary to handle connections from your
application. So it is recommended to set this to at least 4096.
How to change the parameter
Set the AIX maxuproc (maximum number of processes per user) device
attribute to 4096 by entering the command:
chdev -l sys0 -a maxuproc='4096'
Investigative Steps
The value set by AIX (1024) has been found to be too low for some
large scale database environments, which causes DB2 to generate an SQL
error message "SQL1402N - Unable to authenticate user due to
unexpected system error."
To check the value run:
lsattr -l sys0 -E | grep maxuproc
Shared Memory Segments
The 'EXTSHM' environment variable defines the max number of memory segments
shared by all user-mode applications. Some database servers, such as IBM
DB2, relies on this environment variable to support large workloads. To
set it, add the following lines to a user's profile:
EXTSHM=ON
export EXTHSM Network
In high volume environment, TCP layer on AIX should be tuned to the similar
values listed below:
Investigative Steps
To display a list of current tunable values, use:
/usr/sbin/no -a
To make TCP changes permanent, add the following lines into the /etc/rc.net
file:
/usr/sbin/no -o sb_max=6192000
/usr/sbin/no -o tcp_sendspace=4096000
/usr/sbin/no -o tcp_recvspace=4096000
/usr/sbin/no -o udp_sendspace=65536
/usr/sbin/no -o udp_recvspace=655360
/usr/sbin/no -o rfc1323=1
/usr/sbin/no -o ipqmaxlen=150
/usr/sbin/no -o clean_partial_conns=true Ethernet Device Driver Properties
To display a list of current ethernet adapter device driver settings such
as MTU, IPs, etc., use:
lsattr -E -l en2
where en2 is the adapter name - chances are you have several, en0, en1,
etc.
To change the adapter network settings, one way is to run the command line
tool chdev:
# ifconfig en0 detach
chdev -l ent0 -a tx_que_size=8192
# ifconfig en0 hostname up
where tx_que_size is 8192 Disk I/O
Tune FILE SYSTEM Performance
The following file system mount option settings allow you to take advantage
of caching, throughput optimization, and performance of UNIX attached
extern