Intra-parallelism Parameters

[Evan Smith]

We recently upgraded the box that hosts our main DB2 database to an
8-CPU machine with 8 GB of memory. With all the extra horsepower, we
were eager to test performance with intra-parallelism turned on. We
set the following parameters to the following values:

INTRA_PARALLEL = YES
MAX_QUERYDEGREE = ANY
DFT_DEGREE = ANY

After stopping and starting the instance, none of the parallelism
seemed to be active. The documentation indicated that it should be
with these settings. For grins, we set both MAX_QUERYDEGREE and
DFT_DEGREE to 8 (the number of CPUs) and all of a sudden we had lots
of parallelism going on.

My first question is, was this correct approach to take for making
intra-parallelism active?

My next question involves a related matter. The system ran with its
new settings, serving up data faster than ever. However, as the volume
increased we hit a limit. We ran out of FCM request blocks and all
work came to a halt. The documentation doesn't provide and rules of
thumb for determining what the correct number for this setting (and
its sister setting FCM_NUM_BUFFERS) should be. Can anyone provide me
with a ballpark estimate for an an 8 CPU machine with 8 GB of RAM, a
connection pool (used by applications) of about 100 connections in a
mostly OLTP environment? Platform is AIX 4.3, UDB 7.2 FP8.

Thanks,
Evan

[Mark A]

"Evan Smith" <es********@hotmail.com> wrote in message
news:23**************************@posting.google.c om...

We recently upgraded the box that hosts our main DB2 database to an
8-CPU machine with 8 GB of memory. With all the extra horsepower, we
were eager to test performance with intra-parallelism turned on. We
set the following parameters to the following values:

INTRA_PARALLEL = YES
MAX_QUERYDEGREE = ANY
DFT_DEGREE = ANY

After stopping and starting the instance, none of the parallelism
seemed to be active. The documentation indicated that it should be
with these settings. For grins, we set both MAX_QUERYDEGREE and
DFT_DEGREE to 8 (the number of CPUs) and all of a sudden we had lots
of parallelism going on.

My first question is, was this correct approach to take for making
intra-parallelism active?

My next question involves a related matter. The system ran with its
new settings, serving up data faster than ever. However, as the volume
increased we hit a limit. We ran out of FCM request blocks and all
work came to a halt. The documentation doesn't provide and rules of
thumb for determining what the correct number for this setting (and
its sister setting FCM_NUM_BUFFERS) should be. Can anyone provide me
with a ballpark estimate for an an 8 CPU machine with 8 GB of RAM, a
connection pool (used by applications) of about 100 connections in a
mostly OLTP environment? Platform is AIX 4.3, UDB 7.2 FP8.

Thanks,
Evan

I can't answer your questions right off hand, but I would cut back the
MAX_QUERYDEGREE and DFT_DEGREE to 6. This will allow some extra processors
for other DB2 related activities.

[PM \(pm3iinc-nospam\)]

For static sql, you may need to rebind with the right options.
For dynamic sql, the Current Degree special register is what's activating
the parallelism.

PM

[PM \(pm3iinc-nospam\)]

A : On the ground.
B : Probably swimming... ;-)

Never had raid-x but i've seen the parallel in action from io traces i've
made.

PM

"Pierre Saint-Jacques" <se*****@invalid.net> a écrit dans le message de
news:eE*********************@wagner.videotron.net. ..

Please help me out here, I feel I may have been going around with at
least one foot in the mouth?? DB2set parallel_io {all | * |tblspcid,
tblspcid,...}
A) IS not affected by intra_parrallel or max_query_degree or dft_degree
B) IS used and affects only and only tblspcs that are built on raid5 type devices and defined with a single container.

What PM describes in his note applies to any or all tblspcs. defined over JOBD (Just a bunch of disks) where there are mutiple containers for each
tblspc. and each icontainer of a tblspc. is defined over a separate
read/write mechanism. In that case, DB2 will do parallel io is
prefetchsize=extentsize*no. of containers. It will dos even with
parallel_io OFF.

Somebody tel me if I'm right, wrong, all at sea or what???
Thanks, Pierre.

[Bill]

Pierre,

Point A: You are correct - DB2_PARALLEL_IO has no dependency on the
intra_parallel parameters

Point B: DB2 V7 changed the behavior so that any table space can use the
DB2_PARALLEL_IO registry. By default DB2 can start a maximum number of
prefetch I/O's equal to the number of containers defined to a table space.
With the DB2_PARALLEL_IO registry set for a table space, the number of
prefetch I/O's is determined by calculating PREFETCH SIZE / EXTENT SIZE.
Of course, you still need enough IO servers to handle each prefetch request.

Bill
"Pierre Saint-Jacques" <se*****@invalid.net> wrote in message
news:eE*********************@wagner.videotron.net. ..

Please help me out here, I feel I may have been going around with at
least one foot in the mouth?? DB2set parallel_io {all | * |tblspcid,
tblspcid,...}
A) IS not affected by intra_parrallel or max_query_degree or dft_degree
B) IS used and affects only and only tblspcs that are built on raid5 type devices and defined with a single container.

What PM describes in his note applies to any or all tblspcs. defined over JOBD (Just a bunch of disks) where there are mutiple containers for each
tblspc. and each icontainer of a tblspc. is defined over a separate
read/write mechanism. In that case, DB2 will do parallel io is
prefetchsize=extentsize*no. of containers. It will dos even with
parallel_io OFF.

Somebody tel me if I'm right, wrong, all at sea or what???
Thanks, Pierre.
"PM (pm3iinc-nospam)" <PM(pm3iinc-nospam)@sympatico.ca> a écrit dans le
message de news:3r******************@news20.bellglobal.com...

> DB2_PARALLEL_IO means that you big-block read (prefetch-size / extent-size)

> * extent-size blocks of data.
>
> for
> page 4k
> extent 8
> prefetch 16
>
> read 2 X (8 X 4k) each time you read.
>
> <snip>
> While reading or writing data from and to table space containers, DB2

may

> use parallel I/O for each table space value that you specify. The degree of

> parallelism is determined by the prefetch size and extent size for the
> containers in the table space. For example, if the prefetch size is

four > times the extent size, then there are four extent-sized prefetch

requests.

> The number of containers in the table space does not affect the number

of

> prefetchers. To enable parallel I/O for all table spaces, use the

wildcard

> character, "*". To enable parallel I/O for a subset of all table spaces, > enter the list of table spaces. If there is more than one container,
> extent-size pieces of any full prefetch request are broken down into

smaller

> requests executed in parallel based on the number of prefetchers.
> When this variable is not enabled, the number of prefetcher requests

created

> is based on the number of containers in the table space.
>
> <snip>
>
>
>
> PM
>
>

[Pierre Saint-Jacques]

To Bill and PM, ythanks for setting me strait.
I'll behave accordingly!!
Bonjour, Pierre.
"Bill" <wp******@prodigy.net> a écrit dans le message de
news:qy*******************@newssvr16.news.prodigy. com...

Pierre,

Point A: You are correct - DB2_PARALLEL_IO has no dependency on the
intra_parallel parameters

Point B: DB2 V7 changed the behavior so that any table space can use the
DB2_PARALLEL_IO registry. By default DB2 can start a maximum number of
prefetch I/O's equal to the number of containers defined to a table space.
With the DB2_PARALLEL_IO registry set for a table space, the number of
prefetch I/O's is determined by calculating PREFETCH SIZE / EXTENT SIZE.
Of course, you still need enough IO servers to handle each prefetch request.
Bill
"Pierre Saint-Jacques" <se*****@invalid.net> wrote in message
news:eE*********************@wagner.videotron.net. ..

Please help me out here, I feel I may have been going around with at
least one foot in the mouth?? DB2set parallel_io {all | * |tblspcid,
tblspcid,...}
A) IS not affected by intra_parrallel or max_query_degree or dft_degree

B) IS used and affects only and only tblspcs that are built on raid5

type

devices and defined with a single container.

What PM describes in his note applies to any or all tblspcs. defined

over

JOBD (Just a bunch of disks) where there are mutiple containers for each
tblspc. and each icontainer of a tblspc. is defined over a separate
read/write mechanism. In that case, DB2 will do parallel io is
prefetchsize=extentsize*no. of containers. It will dos even with
parallel_io OFF.

Somebody tel me if I'm right, wrong, all at sea or what???
Thanks, Pierre.
"PM (pm3iinc-nospam)" <PM(pm3iinc-nospam)@sympatico.ca> a écrit dans le message de news:3r******************@news20.bellglobal.com...

> DB2_PARALLEL_IO means that you big-block read (prefetch-size /

extent-size)

> * extent-size blocks of data.
>
> for
> page 4k
> extent 8
> prefetch 16
>
> read 2 X (8 X 4k) each time you read.
>
> <snip>
> While reading or writing data from and to table space containers, DB2

may

> use parallel I/O for each table space value that you specify. The degree

of

> parallelism is determined by the prefetch size and extent size for

the > containers in the table space. For example, if the prefetch size is

four > times the extent size, then there are four extent-sized prefetch

requests.

> The number of containers in the table space does not affect the

number of

> prefetchers. To enable parallel I/O for all table spaces, use the

wildcard

> character, "*". To enable parallel I/O for a subset of all table

spaces, > enter the list of table spaces. If there is more than one container,
> extent-size pieces of any full prefetch request are broken down into

smaller

> requests executed in parallel based on the number of prefetchers.
> When this variable is not enabled, the number of prefetcher requests

created

> is based on the number of containers in the table space.
>
> <snip>
>
>
>
> PM
>
>