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.

Index / Cache Management ASA 7.0.4

3 posts in General Discussion Last posting was on 2004-01-18 18:21:05.0Z
gdurniak Posted on 2004-01-18 03:51:57.0Z
Sender: 7640.4009ff2b.1804289383@sybase.com
From: gdurniak@aol.com
Newsgroups: ianywhere.public.general
Subject: Index / Cache Management ASA 7.0.4
X-Mailer: WebNews to Mail Gateway v1.1s
Message-ID: <400a02dd.7644.846930886@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 17 Jan 2004 19:51:57 -0800
X-Trace: forums-1-dub 1074397917 10.22.241.41 (17 Jan 2004 19:51:57 -0800)
X-Original-Trace: 17 Jan 2004 19:51:57 -0800, 10.22.241.41
Lines: 22
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2335
Article PK: 4540

Sybase Adaptive Server Anywhere
Database Engine Version 7.0.4.2788
4K page size
table width 271 bytes

Our 500M database with 500M cache had amazing performance

Now it has grown to 1.5G, with 500M of index pages, and
performance has dropped (of course)

my question(s):
1. Does ASA give priority to the index pages, and fill the
cache?
2. Is there always some percentage of cache for data?
3. Should I delete any no longer used indexes, or are only
active indexes cached?
4. Is it "normal" to have index pages make up ~1/3 of a
database ?
5. Is there a whitepaper on this?

thanks
greg durniak


Glenn Paulley Posted on 2004-01-18 17:58:48.0Z
From: Glenn Paulley <paulley@ianywhere.com>
Reply-To: paulley@ianywhere.com
Organization: iAnywhere Solutions
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.2.1) Gecko/20021130
X-Accept-Language: en-us, en
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: Re: Index / Cache Management ASA 7.0.4
References: <400a02dd.7644.846930886@sybase.com>
In-Reply-To: <400a02dd.7644.846930886@sybase.com>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vpn-dub-020.sybase.com
X-Original-NNTP-Posting-Host: vpn-dub-020.sybase.com
Message-ID: <400ac958@forums-1-dub>
Date: 18 Jan 2004 09:58:48 -0800
X-Trace: forums-1-dub 1074448728 10.22.120.20 (18 Jan 2004 09:58:48 -0800)
X-Original-Trace: 18 Jan 2004 09:58:48 -0800, vpn-dub-020.sybase.com
Lines: 104
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2337
Article PK: 4542

Answers to specific questions are inlined.

I have two general points to offer. One, if you're dissatisfied with
your application's performance then I'd recommend doing some thorough
performance analysis of your more problematic queries, paying particular
attention to the specifics of each index. In 7.x and below, index keys
significantly larger than 10 bytes can be quite inefficient compared to
the indexing technology offered in 8.0.2 or 9.0. Also, in the lab we
have seen disk fragmentation negatively affect performance by a factor
of 3 - it may be time to:

1) backup your database
2) defragment your disk
3) unload/reload the database, using ALTER DBSPACE to allocate enough
space for the data in as few disk extents as possible
4) run some "priming" queries to cause the engine to capture data
statistics

My 2nd point is that by upgrading to 9.0 you gain a number of features
that offer considerable potential for performance improvements. These
include:

table page bitmap support
clustered index support
improved buffer pool management algorithms and better concurrency
much improved multi-CPU performance
advanced query processing algorithms, particularly for joins
significantly improved query optimizer
persistent column histograms to capture data distributions
customizable optimization parameters
customizable disk cost model
and so on.....

Glenn

gdurniak@aol.com wrote:
> Sybase Adaptive Server Anywhere
> Database Engine Version 7.0.4.2788
> 4K page size
> table width 271 bytes
>
> Our 500M database with 500M cache had amazing performance
>
> Now it has grown to 1.5G, with 500M of index pages, and
> performance has dropped (of course)
>
> my question(s):
> 1. Does ASA give priority to the index pages, and fill the
> cache?

The buffer pool management scheme in 7.0.4 does not expressly favour
index pages over other pages. However, on index scans index pages are
"hinted" so that once the pages are needed in cache they have already
been retrieved from disk.

> 2. Is there always some percentage of cache for data?

The server does not explicitly reserve portions of the cache for any
type of page. However, because table pages must always be retrieved even
when index scans are being performed, some proportion of the cache is
always populated with table pages.

> 3. Should I delete any no longer used indexes, or are only
> active indexes cached?

Only indexes that are being used in query processing, or being
maintained during table updates, are read into cache. Superfluous
indexes do not affect query performance, but will (of course) slow
database updates.

> 4. Is it "normal" to have index pages make up ~1/3 of a
> database ?

Obviously the answer depends on your schema and queries; however, 1/3 is
not an unreasonable value in a 7.0.4 database. With more recent
versions of ASA there is less reliance on indexed retrieval,
particularly for complex queries, so one can typically get away with
fewer indexes.

> 5. Is there a whitepaper on this?
>

There is a data storage whitepaper available on the iAnywhere developer
community web site.

> thanks
> greg durniak

--
Glenn Paulley
Research and Development Manager, Query Processing
iAnywhere Solutions Engineering

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://casexpress.sybase.com/cx/cx.stm

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Whitepapers, TechDocs, and bug fixes are all available through the iAnywhere
Developer Community at www.ianywhere.com/developer


Breck Carter [TeamSybase] Posted on 2004-01-18 18:21:05.0Z
From: "Breck Carter [TeamSybase]" <NOSPAM__bcarter@risingroad.com>
Newsgroups: ianywhere.public.general
Subject: Re: Index / Cache Management ASA 7.0.4
Organization: RisingRoad Professional Services
Reply-To: NOSPAM__bcarter@risingroad.com
Message-ID: <ocil001mj8hpsikr7va49ecg7s7e8qq6r2@4ax.com>
References: <400a02dd.7644.846930886@sybase.com> <400ac958@forums-1-dub>
X-Newsreader: Forte Agent 1.8/32.548
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: bcarter.sentex.ca
X-Original-NNTP-Posting-Host: bcarter.sentex.ca
Date: 18 Jan 2004 10:21:05 -0800
X-Trace: forums-1-dub 1074450065 64.7.134.118 (18 Jan 2004 10:21:05 -0800)
X-Original-Trace: 18 Jan 2004 10:21:05 -0800, bcarter.sentex.ca
Lines: 103
X-Authenticated-User: TeamPS
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2338
Article PK: 4543

FWIW the Windows 2K disk defragmenter should be run *after* a large
file has been allocated; e.g., after ALTER DBSPACE and/or the reload
has been run to expand the .DB file. This disk defragmenter only seems
to defragment files, not free space, so if you create a large file
immediately after defragmenting, the new file may be fragmented.

Breck

On 18 Jan 2004 09:58:48 -0800, Glenn Paulley <paulley@ianywhere.com>

wrote:

>Answers to specific questions are inlined.
>
>I have two general points to offer. One, if you're dissatisfied with
>your application's performance then I'd recommend doing some thorough
>performance analysis of your more problematic queries, paying particular
>attention to the specifics of each index. In 7.x and below, index keys
>significantly larger than 10 bytes can be quite inefficient compared to
>the indexing technology offered in 8.0.2 or 9.0. Also, in the lab we
>have seen disk fragmentation negatively affect performance by a factor
>of 3 - it may be time to:
>
>1) backup your database
>2) defragment your disk
>3) unload/reload the database, using ALTER DBSPACE to allocate enough
>space for the data in as few disk extents as possible
>4) run some "priming" queries to cause the engine to capture data
>statistics
>
>My 2nd point is that by upgrading to 9.0 you gain a number of features
>that offer considerable potential for performance improvements. These
>include:
>
>table page bitmap support
>clustered index support
>improved buffer pool management algorithms and better concurrency
>much improved multi-CPU performance
>advanced query processing algorithms, particularly for joins
>significantly improved query optimizer
>persistent column histograms to capture data distributions
>customizable optimization parameters
>customizable disk cost model
>and so on.....
>
>Glenn
>
>gdurniak@aol.com wrote:
>> Sybase Adaptive Server Anywhere
>> Database Engine Version 7.0.4.2788
>> 4K page size
>> table width 271 bytes
>>
>> Our 500M database with 500M cache had amazing performance
>>
>> Now it has grown to 1.5G, with 500M of index pages, and
>> performance has dropped (of course)
>>
>> my question(s):
>> 1. Does ASA give priority to the index pages, and fill the
>> cache?
>
>The buffer pool management scheme in 7.0.4 does not expressly favour
>index pages over other pages. However, on index scans index pages are
>"hinted" so that once the pages are needed in cache they have already
>been retrieved from disk.
>
>> 2. Is there always some percentage of cache for data?
>
>The server does not explicitly reserve portions of the cache for any
>type of page. However, because table pages must always be retrieved even
>when index scans are being performed, some proportion of the cache is
>always populated with table pages.
>
>> 3. Should I delete any no longer used indexes, or are only
>> active indexes cached?
>
>Only indexes that are being used in query processing, or being
>maintained during table updates, are read into cache. Superfluous
>indexes do not affect query performance, but will (of course) slow
>database updates.
>
>> 4. Is it "normal" to have index pages make up ~1/3 of a
>> database ?
>
>Obviously the answer depends on your schema and queries; however, 1/3 is
> not an unreasonable value in a 7.0.4 database. With more recent
>versions of ASA there is less reliance on indexed retrieval,
>particularly for complex queries, so one can typically get away with
>fewer indexes.
>
>> 5. Is there a whitepaper on this?
>>
>
>There is a data storage whitepaper available on the iAnywhere developer
>community web site.
>
>> thanks
>> greg durniak

--
bcarter@risingroad.com
Mobile and Distributed Enterprise Database Applications
www.risingroad.com