CPU usage in DPF on Windows during massive parallel single rowinserts

[4.spam@mail.ru]

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.

[Ian]

Re: CPU usage in DPF on Windows during massive parallel single rowinserts

4.spam@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.

[4.spam@mail.ru]

Re: CPU usage in DPF on Windows during massive parallel single rowinserts

On Jun 18, 7:41 pm, Ian <ianb...@mobile audio.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...