IBM GLOBAL SERVICES

IBM DB2 Information Management

Technical ConferenceSept. 20-24, 2004

Las Vegas, NV

© IBM Corporation 2004

D17

Dwaine R Snow

DB2 UDB Best Practices

Introduction

• Discuss best practices for – Building your databases– Configuring DB2 and your databases– Monitoring– Tuning

• Based on experience with customers

Building Databases• Ensure enough physical disks

– Too few disks in the #1 cause of poor performance– General ROT, minimum 6-10 disks per CPU

• More is good

– Do not isolate table spaces to disks unless you have plenty of disks

• Normally better to spread table spaces across all available disks

• Do not create more than one TEMP table space per page size

• SMS is normally the best choice for TEMP

Building Databases

• If using SMS on AIX, use 3 or more containers per table space to prevent i-node contention

• Logging– Ensure logs are placed on separate disks

– Use mirror logging • Removes logging as a single point of failure

– When using mirror logging, ensure the logs are on different disks, arrays, disk adapters

Building Databases

• For SAN/NAS– Do not count on a large disk cache for

performance– Buffer pool normally absorbs the hits– Size for performance, NOT capacity

Partitioned Databases

• Rules of thumb for RAW data volumes– For Regatta class CPUs 50 - 200 GB per CPU– For Intel/AMD, HP, SUN 25 - 150 GB per CPU

• Rules of thumb for CPU to Partition Ratio– For Regatta 1 or 2 CPUs per partition both work well– For Intel/AMD, HP, SUN 2 CPUs per partition

typically work best

Configuring DB2• Use 64 bit if you can

– Allows more addressability• Bigger buffer pools

• Bigger sorts

– Less limitations for dynamic tuning

Configuring DB2• Disable intra-partition parallelism using

INTRA_PARALLEL– Leaving this enabled and setting MAX_DEGREE to 1 is worst

possible configuration

• Connection Concentrator should be used for workloads with: – Many connections– Issuing very quick SQL statements– Do not use connection concentrator for large / long running

queries

• Set the CPUSPEED to –1 so DB2 can calculate the appropriate value

Configuring DB2• Set DIAGLEVEL and NOTIFYLEVEL to 3• For large result sets set RQRIOBLK as big as

possible (64K)– Default OK for single row results

• Set SHEAPTHRES based on the average # of concurrently executing apps times the SORTHEAP– But not less than 10X SORTHEAP

• Make sure there are few people in the SYSADM group– Too many can lead to problems

Configuring your databases• Other than size, a single large buffer pool requires no

tuning and gives very good performance• Multiple buffer pools

– Can give better performance if sized correctly– Require constant monitoring

• Configure enough primary logs to handle workload– Creating secondary logs is expensive

• Setting LOGPRIMARY to -1 enables infinite logging– Do you really need/want this?– If using this, set MAX_LOG to limit recovery timeframe

Configuring your databases• Set NUM_IOCLEANERS based on # of CPUs• Set NUM_IOSERVERS based on # of physical disk

the database is using• Set DFT_EXTENT_SZ based on underlying disks

– Be aware of striping, SAN disk configuration

• Set DFT_PREFETCH_SZ based on underlying disks

• Set BLK_LOG_DSK_FUL so that DB2 waits if the log disk becomes full

Configuring your databases

• Type II indexes can improve concurrency– For databases migrated from V7, use reorg to

convert the indexes

• If you encounter an “Out of DBHEAP’ error– Double DBHEAP and continue– Since DB2 only allocates what is needed, no

need to spend a lot of time calculating exact value

Configuring your OLTP databases• For dynamic workloads define a large package cache• For dynamic workloads use query optimization 2 or 3

• Set the log buffer to at least 256 pages• Larger log file size improves performance

– But can impact recovery

• Set MAXLOCKS to 20-30%– UNIX default is too low– Also increase LOCKLIST from default

Configuring your OLTP databases

• Set MINCOMMIT to 1?– Maybe set to # trans per second / 10 (or # trans / 100)

• Set AVG_APPLS low, typically 1 is good– Not based on the # of connections

• Reduce CHNGPGS_THRES to 20-30 %– Default can cause system slowdowns

• Set SOFTMAX to an integer multiple of 100%• Do not set SORTHEAP too large as it makes sorts

more “attractive” to the optimizer• Use smaller page sizes

Configuring your DSS databases

• Use query optimization of 5 or higher – Required for some optimizations, i.e. hash joins

• Ensure AVG_APPLS is set based on real workload– Monitor the system and determine the average # of

concurrently executing applications– Not based on # of connections

• Large SORTHEAP can help to reduce overflowed sorts

• Since normally read only, increase DLCHKTIME• Use larger page sizes

Configuring your ODS

• Use QP to ensure query response times

• Run report is UR isolation level

• Use MQTs to improve report performance, separate access from updates

Monitoring

• Make sure you take DB2 and OS snapshots at the same time

• Only take the snapshot(s) you are interested in– Snapshot for all can add overhead

• SQL functions for snapshots allow you to easily insert the data into table(s) for analysis using SQL– Can analyze data for trends

– Predict and plan for growth

Things to Look for in Snapshots

• High number of sort overflows indicate larger SORTHEAP may help performance

• Examine ratio of rows read to rows selected– High ratio indicates likely table scans

• Rows written/inserted for a query only workload indicates sorts

• Application snapshots are for the length of the connection, not individual statements – To examine statements look at statement snapshot

Tuning

• Use AUTOCONFIGURE to get an initial configuration

• When tuning, change one parameter at a time, and re-test

• Make sure your statistics are current• If you add an index, make sure you run RUNSTATS• Before retesting a poorly performing stmt

– Flush the package cache• Otherwise the old plan will be reused

• When you create an index, the key order does matter– Put column with highest cardinality as the first key

Troubleshooting• When there is a performance problem, use vmstat to

see if the bottleneck is CPU, I/O or memory• High I/O wait typically indicates overflowed sorts or

table scans– Use iostat to isolate the disk, then correlate to the database

object

• High CPU usage may indicate sorting or lock waits are occurring– Examine database snapshots to determine which one is

occurring

• Memory issues usually indicated by paging

Troubleshooting• Statement snapshot (or QP) can help identify

poorly performing queries– Look for queries with high execution times

• Especially if their cost is low

– Look for queries with a number of sorts• More important for queries that are run many times

Summary

• Hopefully these tips can save you time and/headaches

• Make sure you examine the tips and think about how they relate to your environment

• When making config changes, make one change at a time and re-test