468,110 Members | 1,920 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,110 developers. It's quick & easy.

CPU usage in DPF on Windows during massive parallel single rowinserts

Hello, all.

DB2 v9.5.1 ESE, Windows 2003 Server 64-bit, 4 CPU (8 cores) each, DPF
environment.
There are 2 DPF instances on the same set of 5 computers.
One computer holds coordinator partitions and other 4 hold partitioned
tables (2 logical nodes per computer).

Application:
DataStage v8 running some (say 32) parallel massive single row inserts
(actually we use compound inserts: insert into table values
(?,..,?), ..., (?,...,?) ).
All inserts go through the coordinator partition and only into 1
database.
We don't use some advanced technique for inserts like local bypass or
INSERT BUF bind option.
intra_parallel parameter is off.

And we see that on each computer that holds partitioned tables 2 cores
are loaded almost on 100% and other 6 cores are almost idle.
It seems that such CPU usage is bottleneck of this workload.

My questions are:
Is the way to make db2 use all cores during such workload?
Has somebody encountered such problem?

Thanks in advance,
Mark B.
Jun 27 '08 #1
2 2218
Ian
4.****@mail.ru wrote:
>
My questions are:
Is the way to make db2 use all cores during such workload?
Has somebody encountered such problem?
I suspect that what you're seeing is a single agent for each database
partition doing the inserts for that partition. With a sufficiently
high level of inserts, you'll see the db2agent thread "pegging" a
single core.

The other EDUs (log writer, page cleaner, etc) are likely touching
the other cores, but relative to the agent there is very little work.
Jun 27 '08 #2
On Jun 18, 7:41*pm, Ian <ianb...@mobileaudio.comwrote:
>
I suspect that what you're seeing is a single agent for each database
partition doing the inserts for that partition. *With a sufficiently
high level of inserts, you'll see the db2agent thread "pegging" a
single core.

The other EDUs (log writer, page cleaner, etc) are likely touching
the other cores, but relative to the agent there is very little work.
Single agent for 32 parallel connections???
I think it's impossible since we are not playing with connection
concentrator.

May be DB2PROCESSORS registry variable adjusted for each node can
help?
Something like:
-- for the 1-st computer's nodes
db2set DB2PROCESSORS=0,1,2,3 -i my_inst 1
db2set DB2PROCESSORS=4,5,6,7 -i my_inst 2
...
-- for the 4-th computer's nodes
db2set DB2PROCESSORS=0,1,2,3 -i my_inst 7
db2set DB2PROCESSORS=4,5,6,7 -i my_inst 8
?

Unfortunately, I can't try this right now...
Jun 27 '08 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by Philip | last post: by
2 posts views Thread by Ryan Lowe | last post: by
2 posts views Thread by Jarvis | last post: by
reply views Thread by Norm | last post: by
10 posts views Thread by Niall | last post: by
15 posts views Thread by Dirk Reske | last post: by
2 posts views Thread by 6tc1 | last post: by
1 post views Thread by Damien | last post: by
1 post views Thread by Solo | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.