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.

Get WCS V5.1 Performance Tuning now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.