WCS V5.1 Performance Tuning

Chapter 4. Database tuning 63

certain indexes always remain in memory, then that would be a valid reason to

move the indexes to their own table space and assign that table space its own

buffer pool. Make sure you have sufficient disk I/O bandwidth to cope with this

setup.

4.5 Adjusting database bufferpool size

The database bufferpool provides memory space for holding a portion of the

database in memory. It allows faster data access to the same data obtained from

disk because it does not require I/O to physical disks.

However, allocating a sizeable portion of memory to the database bufferpool has

to be weighed against the amount of memory available for both database and

other processes running on the server. If insufficient memory is available for

executing processes, the operating system has to compensate by swapping the

required pages to and from disk, with the I/O involved offsetting any performance

gain by the increased bufferpool size.

You can check whether the current bufferpool size is adequate by measuring the

‘bufferpool hit ratio’, which is the ratio of accesses to the bufferpool relative to

accesses to data on disks. For OLTP applications such as WebSphere

Commerce Suite, this percentage should be fairly high - in the region of 95 to 99

per cent.

How is the bufferpool hit ratio determined? The following steps describe the

procedure needed for extracting this value for a database system:

򐂰 Obtain snapshots of buffer pool activity as transactions are executed against

the database. Example 4-4 is a Korn shell script that could be used in testing

by modifying the highlighted entries as necessary (ensure that the db2

instance owner has permissions to execute this script):

Example 4-4 Shell Script for Capturing Bufferpool Snapshot Data

#!/bin/ksh

#--------------------------------------

# db2 snapshot monitor script - 04/10/01

#--------------------------------------

# Change to the name of your database - this example uses ‘mall’

database="mall”

# Change total monitoring time and snapshot intervals to

accommodate your test

# requirements -

#

Valid only for

AIX!

64 WCS V5.1 Performance Tuning

# This example tests every 5 secs for 15 minutes of \

transactionactivity

#15x60=900

typeset -i snapshot_count=0

typeset -i snapshot_interval=0

while (( $snapshot_interval < 900 ))

do

echo "Connecting to the ${database} database"

db2 connect to ${database}

# Setup the database monitor switches

echo "Creating the database monitor switches"

db2 "update monitor switches using bufferpool on"

# Now collect the database snapshot and put into timestamp \

file

echo "Collecting snaphot data"

echo

db2 "get snapshot for all on ${database}" > snapshot-‘date | \

awk ‘{print $4}’‘

# turn off database monitoring

echo "Switching off monitor switches"

echo

db2 "update monitor switches using bufferpool off"

# Disconnecting from database

echo "Disconnecting from the database"

echo

db2 disconnect ${database}

((snapshot_interval += 5))

((snapshot_count += 1))

echo "Number of database snapshots taken = ${snapshot_count}"

echo “Snaphot interval = ${snaphot_interval}"

# Wait for an interval of five seconds before collecting the next

snapshot

echo

echo "Sleeping for 5 seconds......"

sleep 5

done

echo

echo "Done....."

exit 0

Chapter 4. Database tuning 65

򐂰 If the above script is used, time-stamped data collection files are created for

each 15 second interval. The script should be invoked as the db2 instance

owner by the command ./dbsnapshot.sh

򐂰 From the list of snapshot files, determine the values for both buffer pool

logical and physical reads and writes by issuing the following command:

grep “Buffer pool” snap* | grep read > buffer_pool.out

with the generated output of the form shown in the example snapshot entry:

snapshot-Tue Apr 10 10:49:20 CDT 2001:Buffer pool data logical reads = .....

snapshot-Tue Apr 10 10:49:20 CDT 2001:Buffer pool data physical reads = ....

snapshot-Tue Apr 10 10:49:20 CDT 2001:Buffer pool index logical reads = ....

snapshot-Tue Apr 10 10:49:20 CDT 2001:Buffer pool index physical reads =

....

򐂰 From the listings in the buffer_pool.out file (see above highlighted entries),

calculate the bufferpool hit ratio for a snapshot interval using the formula:

1 - {(pool_data_physical_reads + pool_index_physical_reads)

/(pool_data_logical_reads + pool_index_logical_reads)} * 100%

If the bufferpool hit ratio is less than 95%, then calculate the current size of the

database using the following commands:

– db2 list tablespaces show detail

note the Total Pages for SYSCATSPACE, let us say, Ns

– db2 "select sum(npages) from syscat.tables" = Nt

– db2 "select sum(nleaf) from syscat.indexes" = Ni

and calculate the database size as:

Total Size = ((Nt + Ni) + Ns) x 4096 / (1024)**2 = ... MB (approx).

Based on the memory available on the server and the total size of the database,

the general rule of thumb is to allocate a

minimum buffer pool size equal to the

total database size.

Attention: The bufferpool allocation should allow for growth of the Commerce

database and should be revised if new custom tables and indexes are added

to the base schema.