473,546 Members | 2,171 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Same software, different hardware and abominable performance

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?

Nov 12 '05 #1
35 2803
<sa*********@gm ail.com> wrote in message
news:11******** **************@ g49g2000cwa.goo glegroups.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?
Nov 12 '05 #2
sa*********@gma il.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
Nov 12 '05 #3
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.

Nov 12 '05 #4
<sa*********@gm ail.com> wrote in message
news:11******** **************@ f14g2000cwb.goo glegroups.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).
Nov 12 '05 #5
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?

Nov 12 '05 #6
The only thing that db2set -all returns (on all servers) is:

DB2COMM=tcpip

Nov 12 '05 #7
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

sa*********@gma il.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?

Nov 12 '05 #8
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?

Nov 12 '05 #9
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...

Nov 12 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

16
2871
by: aurora | last post by:
Hello! Just gone though an article via Slashdot titled "The Free Lunch Is Over: A Fundamental Turn Toward Concurrency in Software" http://www.gotw.ca/publications/concurrency-ddj.htm]. It argues that the continous CPU performance gain we've seen is finally over. And that future gain would primary be in the area of software concurrency...
7
1840
by: sq1492 | last post by:
There are lots of talks about .Net going around. .Net is considered to be a RAD (rapid application development) framework so the question remains open whether it's useful for developing software when there are some requirements other than developments speed. What I mean is performance. I know there were already a lot of talk around about that,...
0
2487
by: Dana Morris | last post by:
Call for Participation OMG's First Annual Software-Based Communications (SBC) Workshop: From Mobile to Agile Communications http://www.omg.org/news/meetings/SBC2004/call.htm September 13-16, 2004 Washington, DC USA Introduction
5
2640
by: mr.iali | last post by:
Hi Everyone I would like to get into software developent using a programming language like c++, java or pl/sql for oracle. I have no idea where to start from. Which language is there more demand for. Looking at jobs there seems to be a better chance in getting a java job rather than a oracle or c++ job. Also is java and oracle a good...
2
3104
by: p175 | last post by:
Hi folks, I've tried reading just about every post I can on raid stiping / extent size etc and I'm just getting myself more confused than ever. Here's my situation. On a Windows Server 2000 standard edition system with only 1gb ram using UDB 8.2.3 Enterprise, I have an existing database that uses two raid arrays striped with 8kb....
54
5759
by: Matt | last post by:
How do we define systems programs? when we say systems programming, does it necessary mean that the programs we write need to interact with hardware directly? For example, OS, compiler, kernel, drivers, network protocols, etc...? Couple years ago, yes, I understand this is definitely true. However, as the software applications become more and...
9
1904
by: John | last post by:
Hi, I would like to protect my software from copying. What are best tools for it (hardware protection is not an option), do you have some reccomendation for protection or third party software that works? Thanks very much John
7
1221
by: Gilles Ganault | last post by:
Hello I was wondering: Are template engines like CakePHP and Smarty, or caches like APC etc. worth it, even if the contents of the SELECT from MySQL differs from user to user? Thank you.
0
7507
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
1
7461
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
6030
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5361
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
3492
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3472
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1922
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1046
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
747
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.