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

Intra-partition parallelism on AIX

P: n/a
JSC
Hello all,

We're running AIX 5.3 and DB2 v8.1 FP 9. Topas and lscfg confirm that
this is a 4-way SMP environment, however only 1 of the 4 processors
seems to be operative. That is, with INTRA_PARALLEL=YES,
MAX_QUERYDEGREE=ANY and DFT_DEGREE=ANY overall processor load when
running a CPU intensive query is 25% (when broken out - 3 procs are
idle and one is at 100%; monitoring tool shows 100% of time is User
CPU).

DB2 is not "bound" to any particular processor using WLM, as far as we
know. This is a logically partitioned box (at the AIX level, not DB2
DPF). Is it possible that the other processors are showing up in the
configuration but are not logically "activated" or "assigned" to this
partition? I'm Out of my depth OS wise, so not sure I'm even asking
the question correctly. What I'm interested in knowing is how to get
the query to run on all for processors. Footnote: processing at 100%
did seem to switch between two of the processors on the list. Thanks,
JSC

Aug 9 '06 #1
Share this Question
Share on Google+
8 Replies


P: n/a
JSC wrote:
Hello all,

We're running AIX 5.3 and DB2 v8.1 FP 9. Topas and lscfg confirm that
this is a 4-way SMP environment, however only 1 of the 4 processors
seems to be operative. That is, with INTRA_PARALLEL=YES,
MAX_QUERYDEGREE=ANY and DFT_DEGREE=ANY overall processor load when
running a CPU intensive query is 25% (when broken out - 3 procs are
idle and one is at 100%; monitoring tool shows 100% of time is User
CPU).

DB2 is not "bound" to any particular processor using WLM, as far as we
know. This is a logically partitioned box (at the AIX level, not DB2
DPF). Is it possible that the other processors are showing up in the
configuration but are not logically "activated" or "assigned" to this
partition? I'm Out of my depth OS wise, so not sure I'm even asking
the question correctly. What I'm interested in knowing is how to get
the query to run on all for processors.
What's your query and its plan? If DB2 does not use a parallel plan, it's
not much surprising that only one CPU is used.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Aug 10 '06 #2

P: n/a
JSC
That's my question I guess - aside from two instance level and one db
parameter, what are the necessary conditions for the optimizer to
consider intra-partition parallelism? At this point, the explain
output indicates that it is not being considered even though the
parameters are set as recommended in the docs. Wondering if it could
be a licensing issue or some other OS setting. Thanks, JSC
Knut Stolze wrote:
JSC wrote:
Hello all,

We're running AIX 5.3 and DB2 v8.1 FP 9. Topas and lscfg confirm that
this is a 4-way SMP environment, however only 1 of the 4 processors
seems to be operative. That is, with INTRA_PARALLEL=YES,
MAX_QUERYDEGREE=ANY and DFT_DEGREE=ANY overall processor load when
running a CPU intensive query is 25% (when broken out - 3 procs are
idle and one is at 100%; monitoring tool shows 100% of time is User
CPU).

DB2 is not "bound" to any particular processor using WLM, as far as we
know. This is a logically partitioned box (at the AIX level, not DB2
DPF). Is it possible that the other processors are showing up in the
configuration but are not logically "activated" or "assigned" to this
partition? I'm Out of my depth OS wise, so not sure I'm even asking
the question correctly. What I'm interested in knowing is how to get
the query to run on all for processors.

What's your query and its plan? If DB2 does not use a parallel plan, it's
not much surprising that only one CPU is used.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Aug 11 '06 #3

P: n/a
Another thing to keep in mind is if the number of cpus have changed
without restarting the instance. If you are running LPARs and
dynamically reallocate the cpus I believe DB2 will not automatically
discover the change.

Lew

Aug 11 '06 #4

P: n/a

se*****@yahoo.com wrote:
Another thing to keep in mind is if the number of cpus have changed
without restarting the instance. If you are running LPARs and
dynamically reallocate the cpus I believe DB2 will not automatically
discover the change.

Lew

You could also check db2diag.log for message like following:

2006-08-10-01.08.47.635000-240 E237975H804 LEVEL: Event
PID : 900 TID : 2596 PROC : db2syscs.exe
INSTANCE: DB2 NODE : 000
FUNCTION: DB2 UDB, base sys utilities, DB2StartMain, probe:911
MESSAGE : ADM7513W Database manager has started.
START : DB2 DBM
DATA #1 : Build Level, 124 bytes
Instance "DB2" uses "32" bits and DB2 code release "SQL08022"
with level identifier "03030106".
Informational tokens are "DB2 v8.1.9.700", "s050422", "WR21350", FixPak
"9".
DATA #2 : System Info, 1304 bytes
System: WIN32_NT CR696678-A Service Pack 1 5.1 x86 Family 6, model 9,
stepping 5
CPU: total:1 online:1
Physical Memory(MB): total:1023 free:566 available:566
Virtual Memory(MB): total:2461 free:2626
Swap Memory(MB): total:1438 free:2060
************************************************** **************
This message is dumped every time db2 is started so you want to look
for the message from latest restart. This will show you definitively
how many cpu's db2 instance found on startup.

Regards, Yasir.

Aug 14 '06 #5

P: n/a
se*****@yahoo.com wrote:
Another thing to keep in mind is if the number of cpus have changed
without restarting the instance. If you are running LPARs and
dynamically reallocate the cpus I believe DB2 will not automatically
discover the change.
But you have to remember that DB2 does not influence the scheduling of
processes/threads to CPUs. That's what the operating system is doing.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Aug 14 '06 #6

P: n/a
JSC
Thanks all for the feedback. We have restarted the instance, same
result. I'm wondering if there is any way of testing the availability
of all 4 CPU's independent of DB2. Again, I'm outside my depth on the
AIX side, and the admin staff is on vacation. Is there an AIX utility
I could run that would utilize all available CPU so I could possibly
isolate this to the DB2 side. Thanks again. JSC
Knut Stolze wrote:
se*****@yahoo.com wrote:
Another thing to keep in mind is if the number of cpus have changed
without restarting the instance. If you are running LPARs and
dynamically reallocate the cpus I believe DB2 will not automatically
discover the change.

But you have to remember that DB2 does not influence the scheduling of
processes/threads to CPUs. That's what the operating system is doing.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Aug 15 '06 #7

P: n/a
JSC wrote:
Thanks all for the feedback. We have restarted the instance, same
result. I'm wondering if there is any way of testing the availability
of all 4 CPU's independent of DB2. Again, I'm outside my depth on the
AIX side, and the admin staff is on vacation. Is there an AIX utility
I could run that would utilize all available CPU so I could possibly
isolate this to the DB2 side. Thanks again. JSC
Try running "topas". This tool shows a lot of the AIX system information,
including CPUs.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Aug 15 '06 #8

P: n/a
Outside of DB2, you can try the following -
1. lscfg | grep Processor
This will get the processor information and their state

2. /usr/sbin/prtconf -c
Get CPU count and info from another AIX command

3. To "stress test" your system to see if all the CPUs can be active, I
would do the following -
- Identify a large file - sized between 1-2 GB (say a binary or even a
DB2 container file)
- make several copies of it
- run gzip/compress/sum in parallel on all those files (you can write a
loop in shell)

Within DB2, I would also suggest checking the following
1. Database parameters -
NUM_IOCLEANERS = should be 1 (say, equal to # of CPUs)
NUM_IOSERVERS = should be 1 (say, equal to # of CPUs or even more)
MAXAPPLS = should be large enough for your workload

2. Instance parameters -
MAX_QUERYDEGREE = ANY (I believe you have confirmed this)
INTRA_PARALLEL = YES (I believe you have confirmed this)
MAXCAGENTS = MAX_COORDAGENTS (this is to ensure that you allow several
concurrent co-ord agents)

3. Do an explain plan on a large table (or even SYSCAT.COLUMNS) and you
should see something similar where the Query Degree should be 1 (I
have 8 CPUs on my machine) -

Access Plan:
-----------
Total Cost: 459.186
Query Degree: 8

Rows
RETURN
( 1)
Cost
I/O
|
1036
LTQ
( 2)
459.186
74
|
1036
TBSCAN
( 3)
458.575
74
|
1036
TABLE: SYSIBM
SYSTABLES
Knut Stolze wrote:
JSC wrote:
Thanks all for the feedback. We have restarted the instance, same
result. I'm wondering if there is any way of testing the availability
of all 4 CPU's independent of DB2. Again, I'm outside my depth on the
AIX side, and the admin staff is on vacation. Is there an AIX utility
I could run that would utilize all available CPU so I could possibly
isolate this to the DB2 side. Thanks again. JSC

Try running "topas". This tool shows a lot of the AIX system information,
including CPUs.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Aug 16 '06 #9

This discussion thread is closed

Replies have been disabled for this discussion.