Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

max parallel degree and max scan parallel degree

3 posts in General Discussion Last posting was on 2009-08-11 18:29:01.0Z
vtpcnk Posted on 2009-08-04 12:07:14.0Z
Sender: 2f94.4a770c03.1804289383@sybase.com
From: vtpcnk
Newsgroups: sybase.public.ase.general
Subject: max parallel degree and max scan parallel degree
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4a782472.df5.1681692777@sybase.com>
NNTP-Posting-Host: forums-3-dub.sybase.com
X-Original-NNTP-Posting-Host: forums-3-dub.sybase.com
Date: 4 Aug 2009 05:07:14 -0700
X-Trace: forums-3-dub.sybase.com 1249387634 10.22.241.188 (4 Aug 2009 05:07:14 -0700)
X-Original-Trace: 4 Aug 2009 05:07:14 -0700, forums-3-dub.sybase.com
Lines: 22
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28094
Article PK: 77340

is "max parallel degree" only relevant with partitioned
tables?

for "max scan parallel degree" the documentation says that
it is also used for parallel index scans for partitioned
tables.

does that mean that "max parallel degree" cannot do that?

and i am not sure i understand this : "To prevent multiple
disk accesses from becoming a problem, use this parameter
(max scan parallel degree) to reduce the maximum number of
processes that can access the table in parallel."

there is also a note in the manual that max scan parallel
degree cannot be higher than max parallel degree. why and
what is the connection between them?

also when running update stats on non-partitioned tables,
will max scan parallel degree help?

appreciate the feedback.


vtpcnk Posted on 2009-08-04 12:13:26.0Z
Sender: 2f94.4a770c03.1804289383@sybase.com
From: vtpcnk
Newsgroups: sybase.public.ase.general
Subject: Re: max parallel degree and max scan parallel degree
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4a7825e6.f33.1681692777@sybase.com>
References: <4a782472.df5.1681692777@sybase.com>
NNTP-Posting-Host: forums-3-dub.sybase.com
X-Original-NNTP-Posting-Host: forums-3-dub.sybase.com
Date: 4 Aug 2009 05:13:26 -0700
X-Trace: forums-3-dub.sybase.com 1249388006 10.22.241.188 (4 Aug 2009 05:13:26 -0700)
X-Original-Trace: 4 Aug 2009 05:13:26 -0700, forums-3-dub.sybase.com
Lines: 5
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28096
Article PK: 77338

or is max scan parallel degree about parallelism amongst
multiple tables in a join?

while max parallel degree is about parallelism in a single
partitioned table?


Jeff Tallman [Sybase] Posted on 2009-08-11 18:29:01.0Z
From: "Jeff Tallman [Sybase]" <jeff.tallman@sybase.com>
User-Agent: Thunderbird 2.0.0.22 (Windows/20090605)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: max parallel degree and max scan parallel degree
References: <4a782472.df5.1681692777@sybase.com>
In-Reply-To: <4a782472.df5.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a81b86d$3@forums-3-dub.sybase.com>
Date: 11 Aug 2009 11:29:01 -0700
X-Trace: forums-3-dub.sybase.com 1250015341 10.22.241.152 (11 Aug 2009 11:29:01 -0700)
X-Original-Trace: 11 Aug 2009 11:29:01 -0700, vip152.sybase.com
Lines: 70
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28130
Article PK: 77375

max parallel degree refers to the maximum number of worker processes
that ASE will use on a table. By default, the implication is for
partitioned tables. The reason why is that the maximum number of worker
threads ASE will use for an unpartitioned table is controlled explicitly
by "max scan parallel degree" - which uses a leap frog technique to
allocate which rows are scanned by which parallel thread. As noted,

max scan parallel degree <= max parallel degree

So, setting mspd to 5 won't work if mpd is 3 as 5 is higher than the
overall maximum parallelism allowed as controlled by mpd. The way to
think of it is that mpd is the overall limit and mspd is a governor for
unpartitioned tables as a special condition within that higher limit.

Really, there are two cases. Partitioned tables and unpartitioned
tables. For partitioned tables, the docs assume that the partitions are
on separate segments/devices although this is not always the case. For
unpartitioned tables, it assumes that a single page chain on a single
device is used. Consequently, for an unpartitioned table, ASE attempts
to have parallel threads read different pages/rows. Obviously, due to
next page pointers, extent maps, etc, the number of parallel threads
that are effective at this are quite small - e.g. 3-4. If you try a
parallel scan using 10 threads, it likely will not have substantially
better performance than 3-4 threads and may have less depending on how
the result merge needs to be handled. Another reason why the result
could be worse is that a parallel scan likely will use physical IO's -
and with 10 processes all reading from the same device, it likely
exceeds the device capacity for IOPS. For example, a tier 1 storage
device can do about 200 IOPS. 5 such devices in RAID 0 can then do
about 1000 IOPS (assuming 100% scalability for the moment). So, with 10
threads all requesting IOs at same time, each one would only be able to
perform ~100 IOPS as 10x100=1000. In that, each IOP would effectively
have an implicit service time of 10ms - which is slow. By contrast, 5
threads could do 200IOPS each and 3 threads could do 333IOPS each. For
reads one expects 2ms or better service times - so you can start seeing
how that increasing the number of concurrent threads on a single device
actually flattens performance. Now, this assumes physical IO. If the
server has a large enough memory region and you can force it all into
data cache, there could be a difference.


Jeff Tallman
Enterprise Data Management Products Technical Evangelism
jeff.tallman@sybase.com
http://blogs.sybase.com/database

vtpcnk wrote:
> is "max parallel degree" only relevant with partitioned
> tables?
>
> for "max scan parallel degree" the documentation says that
> it is also used for parallel index scans for partitioned
> tables.
>
> does that mean that "max parallel degree" cannot do that?
>
> and i am not sure i understand this : "To prevent multiple
> disk accesses from becoming a problem, use this parameter
> (max scan parallel degree) to reduce the maximum number of
> processes that can access the table in parallel."
>
> there is also a note in the manual that max scan parallel
> degree cannot be higher than max parallel degree. why and
> what is the connection between them?
>
> also when running update stats on non-partitioned tables,
> will max scan parallel degree help?
>
> appreciate the feedback.