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.

Creating different indexes on different table / same table simultaneously.

3 posts in Product Futures Discussion Last posting was on 2002-03-14 16:38:48.0Z
j Posted on 2002-02-15 09:47:42.0Z
From: J
Date: Fri, 15 Feb 2002 04:47:42 -0500
Newsgroups: sybase.public.ase.product_futures_discussion
Subject: Creating different indexes on different table / same table simultaneously.
Message-ID: <6524EE9BE803882D0035CDF185256B61.0035CE3885256B61@webforums>
Lines: 44
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com!webforums.sybase.com!news
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:765
Article PK: 94301

We've been investigating our options to speed up index builds using
multiple indexes.
Our thinking were roughly surrounding a number of factors.

1.
Get the best performance using sort-buffers & large I/O

2.
See what improvements the use of parallel processing brings to the table

3.
Play with the sorted_data & fillfactors.

4.
Test the parallel (i.e. 3,4 or 6 different sessions) creation of NC indexes
should not be faster than the recreation of NC indexes one-after-the-other.

5.
Test the parallel (i.e. 3,4 or 6 different sessions) rebuilding indexes for
different tables.

Dicounting #3 (which isn'r REAL index creation anyway.
Our testing shows that #2 performs best, which wasn't really a surprise.
The effect diminishes predictably as you increase the parallel degree.
Point being that this scales well to a few engines, but not beyond. (Do
note that the clustered-partitioned performance is actually quite
impressive.)

However, #5 and #4 performs worst !! This seems to be related to sort
buffers.
#5 doesn't surprise that much, due to limited cache.
#4 seems to be related to the allocation of sort buffers. (i.e. one session
runs very fast, & the rest are dead-slow.)

Not wanting to knock too much on this one, I was wondering if Sybase could
introduce a configuration parameter "engine_allocated sort buffers",
which default to 1 (same as current setup), but which will allow the
administrator to reserve sort-buffers for each engine, which would allow
option 4 to be viable,
and also would allow much better scalability for indexing.
I do NOT see this as a brilliant suggestion, but as a cheapo-way to improve
the utilisation of engines when rebuilding indexes.

Any thoughts ?

J


Seth Posted on 2002-03-05 02:02:13.0Z
Message-ID: <3C842725.90568780@sybase.com>
Date: Mon, 04 Mar 2002 21:02:13 -0500
From: Seth <user@sybase.com>
Organization: Sybase, Inc.
X-Mailer: Mozilla 4.79 [en] (Windows NT 5.0; U)
X-Accept-Language: en
MIME-Version: 1.0
Subject: Re: Creating different indexes on different table / same table simultaneously.
References: <6524EE9BE803882D0035CDF185256B61.0035CE3885256B61@webforums>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.ase.product_futures_discussion
Lines: 59
NNTP-Posting-Host: 10.22.91.118
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:754
Article PK: 94283

Hi,

I did the parallel sort manager in 11.5.

The sort buffers is for each index creation session. I think the
main reason for #4 being slow may be due to that limited cache
that you said you have. BTW, what was the size of the table
and caches ?

The sort buffers should definitely improve performance with
large I/O and doing it in parallel (both with consumers option
and parallel create index) it should speed. Can you get
sp_sysmon output?

Sethu

J wrote:
>
> We've been investigating our options to speed up index builds using
> multiple indexes.
> Our thinking were roughly surrounding a number of factors.
>
> 1.
> Get the best performance using sort-buffers & large I/O
>
> 2.
> See what improvements the use of parallel processing brings to the table
>
> 3.
> Play with the sorted_data & fillfactors.
>
> 4.
> Test the parallel (i.e. 3,4 or 6 different sessions) creation of NC indexes
> should not be faster than the recreation of NC indexes one-after-the-other.
>
> 5.
> Test the parallel (i.e. 3,4 or 6 different sessions) rebuilding indexes for
> different tables.
>
> Dicounting #3 (which isn'r REAL index creation anyway.
> Our testing shows that #2 performs best, which wasn't really a surprise.
> The effect diminishes predictably as you increase the parallel degree.
> Point being that this scales well to a few engines, but not beyond. (Do
> note that the clustered-partitioned performance is actually quite
> impressive.)
>
> However, #5 and #4 performs worst !! This seems to be related to sort
> buffers.
> #5 doesn't surprise that much, due to limited cache.
> #4 seems to be related to the allocation of sort buffers. (i.e. one session
> runs very fast, & the rest are dead-slow.)
>
> Not wanting to knock too much on this one, I was wondering if Sybase could
> introduce a configuration parameter "engine_allocated sort buffers",
> which default to 1 (same as current setup), but which will allow the
> administrator to reserve sort-buffers for each engine, which would allow
> option 4 to be viable,
> and also would allow much better scalability for indexing.
> I do NOT see this as a brilliant suggestion, but as a cheapo-way to improve
> the utilisation of engines when rebuilding indexes.
>
> Any thoughts ?
>
> J


j Posted on 2002-03-14 16:38:48.0Z
From: J
Date: Thu, 14 Mar 2002 11:38:48 -0500
Newsgroups: sybase.public.ase.product_futures_discussion
Subject: Re: Creating different indexes on different table / same table simultaneously.
Message-ID: <8A47EF4E3DE2F57A005B715385256B7C.0021D47F85256B73@webforums>
References: <6524EE9BE803882D0035CDF185256B61.0035CE3885256B61@webforums> <3C842725.90568780@sybase.com>
Lines: 48
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com!webforums.sybase.com!news
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:666
Article PK: 94196


> Hi,
>
> I did the parallel sort manager in 11.5.
>
> The sort buffers is for each index creation session.
> I think the main reason for #4 being slow may be due
> to that limited cache that you said you have.
> BTW, what was the size of the table and caches ?
>
> The sort buffers should definitely improve performance with
> large I/O and doing it in parallel (both with consumers option
> and parallel create index) it should speed.
> Can you get sp_sysmon output?
>
> Sethu

I was held up a bit with other work....

I do not agree with your statement regarding #4 performing badly, as this
is on the SAME table. The way I thought it through is that each of the
create nc index (on the same table) would do a table scan.
This table is guaranteed not to be in cache when I kick this off. So the
first thread should do most of the I/O, and the rest should read the data
while in cache. No issue, I thought.

However only the first thread performs really well. The other all seem to
falter.
What I would like to know is why ?
I suspect sort-buffers because I do not think the i/o would be a serious
issue, as explained.

I could increase the cache size, but to expect all tables to fit into cache
is pie in the sky.
The reason we're looking into this is due to the REALLY large tables, which
does NOT fit into cache, and which is a pain on indexing.

Please let me know what you think.

Thanks

Johan
> 4. Test the parallel (i.e. 3,4 or 6 different sessions) creation of NC
indexes
> should not be faster than the recreation of NC indexes
one-after-the-other.
>
> 5. Test the parallel (i.e. 3,4 or 6 different sessions) rebuilding
indexes
> for different tables.