Hi,

I read a lot about DB2 INSERT performance here. I have a nice story as
well.

The thing is, I work on 2 installations of DB2 (on completely different
locations) which run on different (but same generation) hardware.
Benchmarking the disk throughput and CPU basically amounts to the same
figures (+/- 10%).

However, the INSERT performance on one server is 20x slower than on the
other ('slowest' server = fastest DB2 performance). And for the life of
me I can't understand why.

Both servers run 'Redhat ES 3 update 4' and 'DB2 8.1 FP9'. On both
servers I've done a clean install of DB2, followed by standard DB
creation, followed by my INSERT benchmark. Result: One server takes
9ms. for every insert, and the other one less than 0.5ms

Now I know there is a lot to tinker to improve performance, but first
I'd like to get the performance of a basic installation roughly the
same (furthermore, a lot of tinkering ammounts to no speed
improvement).

Can someone explain to me how it is possible that performance figures
on two basically equal DB2 installations (from DB and DBM, down to the
sysconf kernel parameters) can show such a difference? And can someone
point me in a direction to look for a solution? I'm sort of at my wits
end.

To make things worse I took an old crappy server from our scrapyard and
installed CentOS 3.3 and 'DB2 8.1 FP9' on it, and it outprerforms the
new server by 9-to-1.

While benchmarking the INSERTS I can see that the IOWAIT figures on
linux' 'top' are very high on the slowest performing server compared to
the fastest performing server. However, when running the disk benchmark
'bonnie' I get roughly the same figures on both servers.

My common sense tells me there are 2 possibilities: Disk hardware or
DB2. But DB2 is not that system-low-level right?

Help?

<sacha.prins@gmail.com> wrote in message
news:1120564704.433757.312630@g49g2000cwa.googlegr oups.com...
> Hi,
>
> I read a lot about DB2 INSERT performance here. I have a nice story as
> well.
>
> The thing is, I work on 2 installations of DB2 (on completely different
> locations) which run on different (but same generation) hardware.
> Benchmarking the disk throughput and CPU basically amounts to the same
> figures (+/- 10%).
>
> However, the INSERT performance on one server is 20x slower than on the
> other ('slowest' server = fastest DB2 performance). And for the life of
> me I can't understand why.
>
> Both servers run 'Redhat ES 3 update 4' and 'DB2 8.1 FP9'. On both
> servers I've done a clean install of DB2, followed by standard DB
> creation, followed by my INSERT benchmark. Result: One server takes
> 9ms. for every insert, and the other one less than 0.5ms
>
> Now I know there is a lot to tinker to improve performance, but first
> I'd like to get the performance of a basic installation roughly the
> same (furthermore, a lot of tinkering ammounts to no speed
> improvement).
>
> Can someone explain to me how it is possible that performance figures
> on two basically equal DB2 installations (from DB and DBM, down to the
> sysconf kernel parameters) can show such a difference? And can someone
> point me in a direction to look for a solution? I'm sort of at my wits
> end.
>
> To make things worse I took an old crappy server from our scrapyard and
> installed CentOS 3.3 and 'DB2 8.1 FP9' on it, and it outprerforms the
> new server by 9-to-1.
>
> While benchmarking the INSERTS I can see that the IOWAIT figures on
> linux' 'top' are very high on the slowest performing server compared to
> the fastest performing server. However, when running the disk benchmark
> 'bonnie' I get roughly the same figures on both servers.
>
> My common sense tells me there are 2 possibilities: Disk hardware or
> DB2. But DB2 is not that system-low-level right?
>
> Help?
>
So, what is the difference in the disk hardware?

sacha.prins@gmail.com wrote:
> Hi,
>
> I read a lot about DB2 INSERT performance here. I have a nice story as
> well.
>
> The thing is, I work on 2 installations of DB2 (on completely different
> locations) which run on different (but same generation) hardware.
> Benchmarking the disk throughput and CPU basically amounts to the same
> figures (+/- 10%).
>
> However, the INSERT performance on one server is 20x slower than on the
> other ('slowest' server = fastest DB2 performance). And for the life of
> me I can't understand why.
>
> Both servers run 'Redhat ES 3 update 4' and 'DB2 8.1 FP9'. On both
> servers I've done a clean install of DB2, followed by standard DB
> creation, followed by my INSERT benchmark. Result: One server takes
> 9ms. for every insert, and the other one less than 0.5ms
>
> Now I know there is a lot to tinker to improve performance, but first
> I'd like to get the performance of a basic installation roughly the
> same (furthermore, a lot of tinkering ammounts to no speed
> improvement).
>
> Can someone explain to me how it is possible that performance figures
> on two basically equal DB2 installations (from DB and DBM, down to the
> sysconf kernel parameters) can show such a difference? And can someone
> point me in a direction to look for a solution? I'm sort of at my wits
> end.
>
> To make things worse I took an old crappy server from our scrapyard and
> installed CentOS 3.3 and 'DB2 8.1 FP9' on it, and it outprerforms the
> new server by 9-to-1.
>
> While benchmarking the INSERTS I can see that the IOWAIT figures on
> linux' 'top' are very high on the slowest performing server compared to
> the fastest performing server. However, when running the disk benchmark
> 'bonnie' I get roughly the same figures on both servers.
>
> My common sense tells me there are 2 possibilities: Disk hardware or
> DB2. But DB2 is not that system-low-level right?
>
> Help?
>
Disc controller? Check the batteries of the write memory.
We had that with a benchmark a while ago. Suddenly write performance
dropped because the drive I/O would be fully synchronized (smart card
though - instead of crapping out it did the best it could with what it
got).

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

The faster server has 2 mirrored disks on an on board SCSI controller
(Dell motherboard). The slower server uses a disk cabinet with raid 1+0
and its own SCSI controller to talk to the mother-board SCSI
controller.

Disk IO benchmark bonnie (FWIW) shows similar performance with a slight
advantage to the disk cabinet.

<sacha.prins@gmail.com> wrote in message
news:1120566324.260572.192750@f14g2000cwb.googlegr oups.com...
> The faster server has 2 mirrored disks on an on board SCSI controller
> (Dell motherboard). The slower server uses a disk cabinet with raid 1+0
> and its own SCSI controller to talk to the mother-board SCSI
> controller.
>
> Disk IO benchmark bonnie (FWIW) shows similar performance with a slight
> advantage to the disk cabinet.
>
Also check the registry variables. I think it is db2set -all (but not sure).

Well, the HW disk cache on the fastest server is off (default). The
crappy server I used to test locally (which is still 9x faster) also
off.

Does the linux-cache know about the HW cache of a SCSI controller?

The only thing that db2set -all returns (on all servers) is:

DB2COMM=tcpip

DB2 insert logic (greatly simplified) generally follows:
1. Update data in buffer pool
2. Write log record
3. Return to application with SQLCODE=0. Application continues at 1.
4. Write data from data pool asychronously, in bulk

If you are not tuned to support using delayed write with page cleaners
then each data block must be physically written out to disk before
returning to the application. This can easily show up as a 20:1 decrease
in performance. Check for tuning parameter differences between the systems.

Try running the job on both systems taking a database snapshot before
and after the job to see what is happening with I/O. Look carefully at
counts and average durations for synchronous and asynchronous I/O. You
will hopefully see differences that will head you down the right path to
correcting this.

Phil Sherman



sacha.prins@gmail.com wrote:
> Hi,
>
> I read a lot about DB2 INSERT performance here. I have a nice story as
> well.
>
> The thing is, I work on 2 installations of DB2 (on completely different
> locations) which run on different (but same generation) hardware.
> Benchmarking the disk throughput and CPU basically amounts to the same
> figures (+/- 10%).
>
> However, the INSERT performance on one server is 20x slower than on the
> other ('slowest' server = fastest DB2 performance). And for the life of
> me I can't understand why.
>
> Both servers run 'Redhat ES 3 update 4' and 'DB2 8.1 FP9'. On both
> servers I've done a clean install of DB2, followed by standard DB
> creation, followed by my INSERT benchmark. Result: One server takes
> 9ms. for every insert, and the other one less than 0.5ms
>
> Now I know there is a lot to tinker to improve performance, but first
> I'd like to get the performance of a basic installation roughly the
> same (furthermore, a lot of tinkering ammounts to no speed
> improvement).
>
> Can someone explain to me how it is possible that performance figures
> on two basically equal DB2 installations (from DB and DBM, down to the
> sysconf kernel parameters) can show such a difference? And can someone
> point me in a direction to look for a solution? I'm sort of at my wits
> end.
>
> To make things worse I took an old crappy server from our scrapyard and
> installed CentOS 3.3 and 'DB2 8.1 FP9' on it, and it outprerforms the
> new server by 9-to-1.
>
> While benchmarking the INSERTS I can see that the IOWAIT figures on
> linux' 'top' are very high on the slowest performing server compared to
> the fastest performing server. However, when running the disk benchmark
> 'bonnie' I get roughly the same figures on both servers.
>
> My common sense tells me there are 2 possibilities: Disk hardware or
> DB2. But DB2 is not that system-low-level right?
>
> Help?
>

Sounds to me like its potentially the number of spindles, depending on
how many you have in the disk cabinet. Especially since you only have
2xdisks@RAID 1 on your newest server. Thats not alot of throughput,
especially if the OS sits on this too. How many drives have you got in
the disk cabinet and what are they? Does this server have seperate
areas for the OS and DB2?

The raid 1+0 has 4 disks (mirrored and striped). The OS and everything
else sits on a different disk. We designed the disk cabinet to have a
single physical raid 1+0 set for the database only. Optionally the logs
are written to their own physical raid set. This helps a little, but
still 20x slower than the other server, and 9x slower than the crappy
server.

We thought we had the setup rather well thought out...