By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,237 Members | 1,234 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,237 IT Pros & Developers. It's quick & easy.

Very slow INSERTs - URGENT

P: n/a
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
Share this Question
Share on Google+
10 Replies


P: n/a
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

P: n/a
Thanks Larry!
This is exactly what I was looking for...

Cheers,
San.

Sep 16 '06 #3

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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 discussion thread is closed

Replies have been disabled for this discussion.