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]

Re: Intra-parallelism Parameters

"Evan Smith" <esmith2112@hot mail.com> wrote in message
news:23658335.0 402090737.37b32 e7e@posting.goo gle.com...[color=blue]
> 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[/color]

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\)]

Re: Intra-parallelism Parameters

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\)]

Re: Intra-parallelism Parameters

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" <sescons@invali d.net> a écrit dans le message de
news:eEcWb.9688 6$km5.1678272@w agner.videotron .net...[color=blue]
> 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_degre e or dft_degree
> B) IS used and affects only and only tblspcs that are built on raid5[/color]
type[color=blue]
> devices and defined with a single container.
>
> What PM describes in his note applies to any or all tblspcs. defined[/color]
over[color=blue]
> 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=ex tentsize*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.[/color]

[Bill]

Re: Intra-parallelism Parameters

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" <sescons@invali d.net> wrote in message
news:eEcWb.9688 6$km5.1678272@w agner.videotron .net...[color=blue]
> 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_degre e or dft_degree
> B) IS used and affects only and only tblspcs that are built on raid5[/color]
type[color=blue]
> devices and defined with a single container.
>
> What PM describes in his note applies to any or all tblspcs. defined[/color]
over[color=blue]
> 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=ex tentsize*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)@sympati co.ca> a écrit dans le
> message de news:3rZVb.3977 $lK.281977@news 20.bellglobal.c om...[color=green]
> > DB2_PARALLEL_IO means that you big-block read (prefetch-size /[/color]
> extent-size)[color=green]
> > * 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[/color]
> may[color=green]
> > use parallel I/O for each table space value that you specify. The[/color][/color]
degree[color=blue]
> of[color=green]
> > parallelism is determined by the prefetch size and extent size for the
> > containers in the table space. For example, if the prefetch size is[/color][/color]
four[color=blue][color=green]
> > times the extent size, then there are four extent-sized prefetch[/color]
> requests.[color=green]
> > The number of containers in the table space does not affect the number[/color]
> of[color=green]
> > prefetchers. To enable parallel I/O for all table spaces, use the[/color]
> wildcard[color=green]
> > character, "*". To enable parallel I/O for a subset of all table[/color][/color]
spaces,[color=blue][color=green]
> > 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[/color]
> smaller[color=green]
> > requests executed in parallel based on the number of prefetchers.
> > When this variable is not enabled, the number of prefetcher requests[/color]
> created[color=green]
> > is based on the number of containers in the table space.
> >
> > <snip>
> >
> >
> >
> > PM
> >
> >[/color]
>[/color]

[Pierre Saint-Jacques]

Re: Intra-parallelism Parameters

To Bill and PM, ythanks for setting me strait.
I'll behave accordingly!!
Bonjour, Pierre.
"Bill" <wpellett@prodi gy.net> a écrit dans le message de
news:qysWb.2042 1$vh7.16739@new ssvr16.news.pro digy.com...[color=blue]
> 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[/color]
request.[color=blue]
>
> Bill
> "Pierre Saint-Jacques" <sescons@invali d.net> wrote in message
> news:eEcWb.9688 6$km5.1678272@w agner.videotron .net...[color=green]
> > 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_degre e or[/color][/color]
dft_degree[color=blue][color=green]
> > B) IS used and affects only and only tblspcs that are built on raid5[/color]
> type[color=green]
> > devices and defined with a single container.
> >
> > What PM describes in his note applies to any or all tblspcs. defined[/color]
> over[color=green]
> > 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=ex tentsize*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)@sympati co.ca> a écrit dans[/color][/color]
le[color=blue][color=green]
> > message de news:3rZVb.3977 $lK.281977@news 20.bellglobal.c om...[color=darkred]
> > > DB2_PARALLEL_IO means that you big-block read (prefetch-size /[/color]
> > extent-size)[color=darkred]
> > > * 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,[/color][/color][/color]
DB2[color=blue][color=green]
> > may[color=darkred]
> > > use parallel I/O for each table space value that you specify. The[/color][/color]
> degree[color=green]
> > of[color=darkred]
> > > parallelism is determined by the prefetch size and extent size for[/color][/color][/color]
the[color=blue][color=green][color=darkred]
> > > containers in the table space. For example, if the prefetch size is[/color][/color]
> four[color=green][color=darkred]
> > > times the extent size, then there are four extent-sized prefetch[/color]
> > requests.[color=darkred]
> > > The number of containers in the table space does not affect the[/color][/color][/color]
number[color=blue][color=green]
> > of[color=darkred]
> > > prefetchers. To enable parallel I/O for all table spaces, use the[/color]
> > wildcard[color=darkred]
> > > character, "*". To enable parallel I/O for a subset of all table[/color][/color]
> spaces,[color=green][color=darkred]
> > > 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[/color]
> > smaller[color=darkred]
> > > requests executed in parallel based on the number of prefetchers.
> > > When this variable is not enabled, the number of prefetcher requests[/color]
> > created[color=darkred]
> > > is based on the number of containers in the table space.
> > >
> > > <snip>
> > >
> > >
> > >
> > > PM
> > >
> > >[/color]
> >[/color]
>
>[/color]