473,320 Members | 2,107 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Very slow INSERTs - URGENT

DB2 V8.2 ICE Linux
An application is loading around 6 million rows in a table in database A
(Development environment).

The same application is taking ages to load the same table in database B
(UAT environment).
Database B is supposed to be faster since it is partitioned.
It has Referential integrity (RI) and indexes defined on it.
Does this have a major impact on INSERTs?

Is there anything else that needs to be looked at?

Thanks.

Cheers,
San.

Sep 16 '06 #1
10 3827
Here is a white paper that may help:

http://www-128.ibm.com/developerwork...m-0403wilkins/

Also, just because a database has been partitioned with DPF doesn't mean
it will automatically be faster. You have to ensure that it has the hw
resources that it needs. The hw configuration is very important.

Larry Edelstein

shsandeep wrote:
DB2 V8.2 ICE Linux
An application is loading around 6 million rows in a table in database A
(Development environment).

The same application is taking ages to load the same table in database B
(UAT environment).
Database B is supposed to be faster since it is partitioned.
It has Referential integrity (RI) and indexes defined on it.
Does this have a major impact on INSERTs?

Is there anything else that needs to be looked at?

Thanks.

Cheers,
San.
Sep 16 '06 #2
Thanks Larry!
This is exactly what I was looking for...

Cheers,
San.

Sep 16 '06 #3
San,

If you have a DPF production environment you need a DPF development
environment. Doesn't have to be the same number of nodes. But DPF it
ought to be.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Sep 16 '06 #4
Serge,
I totally agree with you.... In fact, you had mentioned this earlier as
well...
But whenever I put this point in front of the management, they ignore it
saying that we are not testing performance in the development
environment...
Are there any other benefits of partitioning the DEV database which I can
put forward to them??

Thanks.

Cheers,
San.

Sep 17 '06 #5
shsandeep wrote:
Serge,
I totally agree with you.... In fact, you had mentioned this earlier as
well...
But whenever I put this point in front of the management, they ignore it
saying that we are not testing performance in the development
environment...
Are there any other benefits of partitioning the DEV database which I can
put forward to them??
Money talks. Collect the amount of damage to the business because of
your current problem and any previous ones you may have encountered.
How are you supposed to track this problem, collect data, experiment if
your only system is production?
All you need are two logical nodes for development/test.
Sure you don't need DPF on the laptops of your app developers. But your
DBA's need access to a scaled down version of the original.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Sep 17 '06 #6
Serge is exactly right. My customers have suffered when they don't have
a test system (call it a QA system) that is representative of
production. It doesn't have to be an exact replica, but it should be
representative in some proportion.

Now ... let me tell you why you SHOULD be testing performance in a QA
system. It is not unheard of for maintenance (be it OS-related,
DB2-related, or even ISV-related) to affect performance. You want to
apply any changes ... any changes at all to an environment where you can
not only test functionality, but where you can test performance also. In
fact, one of my customers actually has a "benchmark" set of queries/txns
that they established a baseline for and then they run for EVERY SINGLE
change that is made to QA. If a change is made where the difference from
the baseline is significant and unexplained, research and analysis is
conducted to find out why. It could be as simple as an environment
variable that someone forgot to set ... or it could be a bug. You don't
want to find out about this in production ... you want to find out about
these kinds of things before any change every hits production. It
enhances quality of service for your end-users and minimizes outages ...
it is simply good systems management practice and reduces costs (in the
long run) to the business.

Such a QA system should be configured EXACTLY like production in terms
of sw and hw technology, but in terms of size, should be constucted in
some proportion. So for example, if production is a 16-way AIX 5.3
p-series POWER5 server with 8 logical partitions of DB2 DPF V8.2 @ fp 12
on it, perhaps QA should be a 4-way p-series server with the exact same
hw technology (if possible) with AIX 5.3 @ fp12 on it with maybe 2
logical partitions. The data in those 2 logical partitions should be
representative also to be about 1/4 of the total data in production.
That way, you can extrapolate performance results in QA.

Just my 2 cents.

Larry Edelstein
Serge Rielau wrote:
shsandeep wrote:
>Serge,
I totally agree with you.... In fact, you had mentioned this earlier as
well...
But whenever I put this point in front of the management, they ignore it
saying that we are not testing performance in the development
environment...
Are there any other benefits of partitioning the DEV database which I can
put forward to them??

Money talks. Collect the amount of damage to the business because of
your current problem and any previous ones you may have encountered.
How are you supposed to track this problem, collect data, experiment if
your only system is production?
All you need are two logical nodes for development/test.
Sure you don't need DPF on the laptops of your app developers. But your
DBA's need access to a scaled down version of the original.

Cheers
Serge
Sep 17 '06 #7
I went thru the same argument a few years back and between the
developers, QA, and the DBAs we finally managed to get ar least a
partial replication - similar to Larry's description - based on those
arguments. About 6 months after we got both the QA and Development
environments commissioned, we had a rash of one-of-a-kind hardware
failures in the production servers that took them off-line at a peak
demand cycle during a new campaign startup. The ability to rotate the
extra servers, even in limited capacities, made follow-on upgrade
requests a no brainer because the value of an additional partial
backup facility was immediately apparent to management (and us dumb
developers <g>).

On Sun, 17 Sep 2006 16:59:05 UTC Larry <la***@nospam.netwrote:
Serge is exactly right. My customers have suffered when they don't have
a test system (call it a QA system) that is representative of
production. It doesn't have to be an exact replica, but it should be
representative in some proportion.

Now ... let me tell you why you SHOULD be testing performance in a QA
system. It is not unheard of for maintenance (be it OS-related,
DB2-related, or even ISV-related) to affect performance. You want to
apply any changes ... any changes at all to an environment where you can
not only test functionality, but where you can test performance also. In
fact, one of my customers actually has a "benchmark" set of queries/txns
that they established a baseline for and then they run for EVERY SINGLE
change that is made to QA. If a change is made where the difference from
the baseline is significant and unexplained, research and analysis is
conducted to find out why. It could be as simple as an environment
variable that someone forgot to set ... or it could be a bug. You don't
want to find out about this in production ... you want to find out about
these kinds of things before any change every hits production. It
enhances quality of service for your end-users and minimizes outages ...
it is simply good systems management practice and reduces costs (in the
long run) to the business.

Such a QA system should be configured EXACTLY like production in terms
of sw and hw technology, but in terms of size, should be constucted in
some proportion. So for example, if production is a 16-way AIX 5.3
p-series POWER5 server with 8 logical partitions of DB2 DPF V8.2 @ fp 12
on it, perhaps QA should be a 4-way p-series server with the exact same
hw technology (if possible) with AIX 5.3 @ fp12 on it with maybe 2
logical partitions. The data in those 2 logical partitions should be
representative also to be about 1/4 of the total data in production.
That way, you can extrapolate performance results in QA.

Just my 2 cents.

Larry Edelstein
Serge Rielau wrote:
shsandeep wrote:
Serge,
I totally agree with you.... In fact, you had mentioned this earlier as
well...
But whenever I put this point in front of the management, they ignore it
saying that we are not testing performance in the development
environment...
Are there any other benefits of partitioning the DEV database which I can
put forward to them??
Money talks. Collect the amount of damage to the business because of
your current problem and any previous ones you may have encountered.
How are you supposed to track this problem, collect data, experiment if
your only system is production?
All you need are two logical nodes for development/test.
Sure you don't need DPF on the laptops of your app developers. But your
DBA's need access to a scaled down version of the original.

Cheers
Serge

--
Will Honea
Sep 18 '06 #8
shsandeep wrote:
Are there any other benefits of partitioning the DEV database which I can
put forward to them??
There are some things to be aware of in DPF, which don't come into play in
non-DPF environments. Most notably, the fact that you have/need a
partitioning key on each column. Next, you may want to use replicated MQTs
in DPF when it makes sense - that question doesn't come up in non-DPF
either.

I would recommend that you use logical database partitions (all on the same
machine) in your dev environment. Then mgmt can't complain because (a)
they don't need to know it, and (b) you wouldn't need additional
hardware. ;-)

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Sep 18 '06 #9
Knut Stolze wrote:
I would recommend that you use logical database partitions (all on the same
machine) in your dev environment. Then mgmt can't complain because (a)
they don't need to know it, and (b) you wouldn't need additional
hardware. ;-)
a) Ehem, there is this small issue with licensing DPF.
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Sep 18 '06 #10
Serge Rielau wrote:
Knut Stolze wrote:
>I would recommend that you use logical database partitions (all on the
same
machine) in your dev environment. Then mgmt can't complain because (a)
they don't need to know it, and (b) you wouldn't need additional
hardware. ;-)
a) Ehem, there is this small issue with licensing DPF.
Oh yeah. I forgot about that... ;-)

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Sep 18 '06 #11

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

Similar topics

2
by: Robert Hooker | last post by:
Hi, I'm curious to know if I'm doing something wrong here, or if this is just mind-numbingly slow for a reason. In a simple WindowsFormsApplication: public Form1() { // Required for...
3
by: Mario Soto | last post by:
Hi. i hava a postresql 7.4.2 in a production server. tha machine is a Pentium IV 2,6 GHZ AND 1 GB IN RAM with lINUX RH 9.0. The postresql.conf say: ...
50
by: diffuser78 | last post by:
I have just started to learn python. Some said that its slow. Can somebody pin point the issue. Thans
5
by: PH | last post by:
Hi guys; I got a single processor computer, running an application that launches 2 threads. Each of these threads listens for incoming connections in a specific port, so there is a Loop ....
4
by: sommes | last post by:
It's only happen on .asp website, what's the problem? Thank you
3
by: cold80 | last post by:
I was just doing a performance test in order to see the benefit of using AJAX instead of doing a postback on the server. So I have a simple page that performs a request to the server and write a...
1
by: dumindaj | last post by:
I have problems running my Java Swing application remotely on a Linux machine. The application takes 20/30s to respond to events and painting takes considerable time. This is not a special case...
5
by: lazyvlad | last post by:
Hi, I'm writing here because this issue is becoming more annoying with each day it passes. So I have a form, a dataset with a few table adapters (3 to be precise) and a datagridview.The datagridview...
0
by: Mohs | last post by:
Hi All, I am very new to DB2, and in a project we are using db2, 8.2 version. My problem is that one of the procedures that previously used to complete in 10 minutes is not getting...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....

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.