DB2 8.1.4 I/O Performance

[joerg]

Hello,
we have quite a lot of troubles with our new installation of DB2 8.1.4
on a Linux-System. The server is a Dual-Xeon with 2.4 GHz and 2GB Ram,
with installed Raid 1 mirrored 75 GB harddisks.
The system itself is performing very fast but in combination with DB2
we are facing an I/O-problem.
Our DB-scenaio is as follows:

Loading a flat table over JDBC and splitting it into a more detailed
datastructure. We are commiting after every successful transformation
(no autocommit). What we now see is that the DB is doing almost
nothing and the server itself is also more or less idle.

We check everything we thought of and we found the following things:

up to 4 blocked processes (see VMSTAT result)
very low buffer-out rate (see VMSTAT result)
terribly high time of asynchronous write (see BUFFERPOOL
snapshot)

Has anbody an idea how to solve this problem ?

Thanks

Joerg

Result of VMSTAT

procs -----------memory---------- ---swap-- -----io---- --system--
----cpu----
r b swpd free buff cache si so bi bo in cs us
sy id wa
0 2 29156 75196 217096 2063308 0 0 17 33 17 9 1
1 98 0
0 1 29156 75196 217104 2063656 0 0 0 930 803 945 3
3 94 0
0 3 29156 73700 217104 2063816 0 0 0 895 770 910 3
1 96 0
0 4 29156 73700 217104 2064144 0 0 2 926 783 946 2
1 97 0

Result of BUFFERPOOL-snapshot

Buffer pool data logical reads = 1368786
Buffer pool data physical reads = 40172
Buffer pool temporary data logical reads = 0
Buffer pool temporary data physical reads = 0
Buffer pool data writes = 18557
Buffer pool index logical reads = 21874494
Buffer pool index physical reads = 686
Buffer pool temporary index logical reads = 0
Buffer pool temporary index physical reads = 0
Total buffer pool read time (ms) = 13395
Total buffer pool write time (ms) = 1792986
Asynchronous pool data page reads = 27476
Asynchronous pool data page writes = 15658
Buffer pool index writes = 19106
Asynchronous pool index page reads = 0
Asynchronous pool index page writes = 11751
Total elapsed asynchronous read time = 3329
Total elapsed asynchronous write time = 1308907
Asynchronous data read requests = 1718
Asynchronous index read requests = 0
No victim buffers available = 18560
Direct reads = 0
Direct writes = 0
Direct read requests = 0
Direct write requests = 0
Direct reads elapsed time (ms) = 0
Direct write elapsed time (ms) = 0
Database files closed = 3
Data pages copied to extended storage = 0
Index pages copied to extended storage = 0
Data pages copied from extended storage = 0
Index pages copied from extended storage = 0
Unread prefetch pages = 0
Vectored IOs = 1718
Pages from vectored IOs = 27476
Block IOs = 0
Pages from block IOs = 0
Physical page maps = 0

[Mark Yudkin]

Sounds like your disk controller. Try adding battery-backed memory to the
SCSI RAID controller, or updating the RAID controller card to one that can
manage better write performance.

(I've assume you have SCSI RAID as you said Dual Xeon and RAID-1. If you
don't, that's probably your problem).

BTW, how large are your buffer pools? (If they're too small that can also
produce problems, but probably isn't your problem here).

"joerg" <jo**********@publica.de> wrote in message
news:47**************************@posting.google.c om...

Hello,
we have quite a lot of troubles with our new installation of DB2 8.1.4
on a Linux-System. The server is a Dual-Xeon with 2.4 GHz and 2GB Ram,
with installed Raid 1 mirrored 75 GB harddisks.
The system itself is performing very fast but in combination with DB2
we are facing an I/O-problem.
Our DB-scenaio is as follows:

Loading a flat table over JDBC and splitting it into a more detailed
datastructure. We are commiting after every successful transformation
(no autocommit). What we now see is that the DB is doing almost
nothing and the server itself is also more or less idle.

We check everything we thought of and we found the following things:

up to 4 blocked processes (see VMSTAT result)
very low buffer-out rate (see VMSTAT result)
terribly high time of asynchronous write (see BUFFERPOOL
snapshot)

Has anbody an idea how to solve this problem ?

Thanks

Joerg

Result of VMSTAT

procs -----------memory---------- ---swap-- -----io---- --system--
----cpu----
r b swpd free buff cache si so bi bo in cs us
sy id wa
0 2 29156 75196 217096 2063308 0 0 17 33 17 9 1
1 98 0
0 1 29156 75196 217104 2063656 0 0 0 930 803 945 3
3 94 0
0 3 29156 73700 217104 2063816 0 0 0 895 770 910 3
1 96 0
0 4 29156 73700 217104 2064144 0 0 2 926 783 946 2
1 97 0

Result of BUFFERPOOL-snapshot

Buffer pool data logical reads = 1368786
Buffer pool data physical reads = 40172
Buffer pool temporary data logical reads = 0
Buffer pool temporary data physical reads = 0
Buffer pool data writes = 18557
Buffer pool index logical reads = 21874494
Buffer pool index physical reads = 686
Buffer pool temporary index logical reads = 0
Buffer pool temporary index physical reads = 0
Total buffer pool read time (ms) = 13395
Total buffer pool write time (ms) = 1792986
Asynchronous pool data page reads = 27476
Asynchronous pool data page writes = 15658
Buffer pool index writes = 19106
Asynchronous pool index page reads = 0
Asynchronous pool index page writes = 11751
Total elapsed asynchronous read time = 3329
Total elapsed asynchronous write time = 1308907
Asynchronous data read requests = 1718
Asynchronous index read requests = 0
No victim buffers available = 18560
Direct reads = 0
Direct writes = 0
Direct read requests = 0
Direct write requests = 0
Direct reads elapsed time (ms) = 0
Direct write elapsed time (ms) = 0
Database files closed = 3
Data pages copied to extended storage = 0
Index pages copied to extended storage = 0
Data pages copied from extended storage = 0
Index pages copied from extended storage = 0
Unread prefetch pages = 0
Vectored IOs = 1718
Pages from vectored IOs = 27476
Block IOs = 0
Pages from block IOs = 0
Physical page maps = 0

[PM \(pm3iinc-nospam\)]

NUM_IOCLEANERS = ?

Command :
get db cfg for dbname

You can also get a few snapshots to investigate
*log*
Dirty page steal cleaner triggers
Agents stolen
Database files closed
Number of files closed
etc.
PM

"joerg" <jo**********@publica.de> a écrit dans le message de
news:47**************************@posting.google.c om...

Hello,
we have quite a lot of troubles with our new installation of DB2 8.1.4
on a Linux-System. The server is a Dual-Xeon with 2.4 GHz and 2GB Ram,
with installed Raid 1 mirrored 75 GB harddisks.
The system itself is performing very fast but in combination with DB2
we are facing an I/O-problem.
Our DB-scenaio is as follows:

Loading a flat table over JDBC and splitting it into a more detailed
datastructure. We are commiting after every successful transformation
(no autocommit). What we now see is that the DB is doing almost
nothing and the server itself is also more or less idle.

We check everything we thought of and we found the following things:

up to 4 blocked processes (see VMSTAT result)
very low buffer-out rate (see VMSTAT result)
terribly high time of asynchronous write (see BUFFERPOOL
snapshot)

Has anbody an idea how to solve this problem ?

Thanks

Joerg

Result of VMSTAT

procs -----------memory---------- ---swap-- -----io---- --system--
----cpu----
r b swpd free buff cache si so bi bo in cs us
sy id wa
0 2 29156 75196 217096 2063308 0 0 17 33 17 9 1
1 98 0
0 1 29156 75196 217104 2063656 0 0 0 930 803 945 3
3 94 0
0 3 29156 73700 217104 2063816 0 0 0 895 770 910 3
1 96 0
0 4 29156 73700 217104 2064144 0 0 2 926 783 946 2
1 97 0

Result of BUFFERPOOL-snapshot

Buffer pool data logical reads = 1368786
Buffer pool data physical reads = 40172
Buffer pool temporary data logical reads = 0
Buffer pool temporary data physical reads = 0
Buffer pool data writes = 18557
Buffer pool index logical reads = 21874494
Buffer pool index physical reads = 686
Buffer pool temporary index logical reads = 0
Buffer pool temporary index physical reads = 0
Total buffer pool read time (ms) = 13395
Total buffer pool write time (ms) = 1792986
Asynchronous pool data page reads = 27476
Asynchronous pool data page writes = 15658
Buffer pool index writes = 19106
Asynchronous pool index page reads = 0
Asynchronous pool index page writes = 11751
Total elapsed asynchronous read time = 3329
Total elapsed asynchronous write time = 1308907
Asynchronous data read requests = 1718
Asynchronous index read requests = 0
No victim buffers available = 18560
Direct reads = 0
Direct writes = 0
Direct read requests = 0
Direct write requests = 0
Direct reads elapsed time (ms) = 0
Direct write elapsed time (ms) = 0
Database files closed = 3
Data pages copied to extended storage = 0
Index pages copied to extended storage = 0
Data pages copied from extended storage = 0
Index pages copied from extended storage = 0
Unread prefetch pages = 0
Vectored IOs = 1718
Pages from vectored IOs = 27476
Block IOs = 0
Pages from block IOs = 0
Physical page maps = 0