DMS, Linux, and iostat?

[Jean-David Beyer]

I am running DB2 V8.1.5 on a RHEL 3 ES system. 4096 Megabytes RAM. The
main parts of a database are SMS, page size 4096 (default). I keep the
indices on a hard drive used for nothing else except swap (that almost
never happens).

Now iostat says:

avgrq-sz
The average size (in sectors) of the requests that
were issued to the device.

My database defines the index thus:

-- CREATE buffer pool for STOCK_INDICES: 819,200,000 bytes.
-- Large because we hope to get entire index into memory sometimes.
CREATE BUFFERPOOL BP_STOCK_INDICES
SIZE 200000;

-- CREATE DMS space for STOCK_INDICES.
-- STOCK_INDICES are on one drive, so we try one extent of prefetch.
CREATE TABLESPACE STOCK_INDICES
MANAGED BY DATABASE
USING (DEVICE '/dev/raw/raw4' 3976087
)
EXTENTSIZE 64 PREFETCHSIZE 64
BUFFERPOOL BP_STOCK_INDICES
OVERHEAD 7.5 TRANSFERRATE 0.12;

so I would expect the IO for the index to be in chunks of 512 sectors at a
time (4096 * 64 / 512); i.e., one extent at a time.

The data are on sd[a-c]1 and have extent size of 32, so similar
considerations apply.

But when loading the database with INSERT statements, iostat says:

Time: 13:21:24
avg-cpu: %user %nice %sys %idle
17.10 75.15 7.65 0.10

Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s rkB/s wkB/s
avgrq-sz avgqu-sz await svctm %util
/dev/sda1 0.00 0.00 0.00 5.16 0.00 41.25 0.00 20.63
8.00 0.01 0.20 0.20 0.10
/dev/sdb1 0.00 0.00 0.00 6.47 0.00 51.77 0.00 25.88
8.00 0.02 0.31 0.31 0.20
/dev/sdc1 0.00 0.00 0.00 6.98 0.00 55.81 0.00 27.91
8.00 0.01 0.14 0.14 0.10
/dev/sdd2 0.00 0.00 0.00 369.26 0.00 2953.29 0.00 1476.64
8.00 47.72 13.02 2.29 84.63

Does DB2 ignore extent size when doing IO (not allocation) to raw DMS
partitions and use one page at a time? Is iostat incorrect? Am I
misunderstanding something? I would have thought the average request size
for sd[a-c]1 would be 256 sectors and the average request size for sdd2
would be 512 sectors. Yet they are all listed as 8.00.

--
.~. Jean-David Beyer Registered Linux User 85642.
/V\ Registered Machine 241939.
/( )\ Shrewsbury, New Jersey http://counter.li.org
^^-^^ 13:10:00 up 1 day, 2:41, 6 users, load average: 8.29, 6.55, 5.32