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.

ASA Page Size

4 posts in General Discussion Last posting was on 2003-06-12 13:36:27.0Z
Doug Stone Posted on 2003-06-11 19:00:34.0Z
From: "Doug Stone" <nospamdstone@res-q.com>
Subject: ASA Page Size
Date: Wed, 11 Jun 2003 12:00:34 -0700
Lines: 18
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
Message-ID: <udEr5xEMDHA.218@forums-2-dub>
Newsgroups: ianywhere.public.general
NNTP-Posting-Host: user-112vkdk.biz.mindspring.com 66.47.209.180
Path: forums-1-dub!forums-master.sybase.com!forums-2-dub.sybase.com
Xref: forums-1-dub ianywhere.public.general:1197
Article PK: 3441

We've noticed that performance of our application at customers sites will
often improve dramatically after increasing db page size from 2048 to 4096.

However, if the customer's db is under a couple of hundred meg, the page
size increase usually does not improve performance.
I'm wondering, though, what about our customers whose DBs are increasing in
size rapidly? Those customers will probably see their db grow above a gig,
in a year or two. Should we suggest that those customers increase their
page size to 8192 or above?

What is the downside to a large page size?

Thanks,
Doug


"Richard Biffl" Posted on 2003-06-12 03:57:18.0Z
From: "Richard Biffl" <sybaseforums a t n-oo=s-p-aa-m=blacklettersoftwaredotcom>
References: <udEr5xEMDHA.218@forums-2-dub>
Subject: Re: ASA Page Size
Date: Wed, 11 Jun 2003 23:57:18 -0400
Lines: 45
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
Message-ID: <#rlBCfJMDHA.187@forums-2-dub>
Newsgroups: ianywhere.public.general
NNTP-Posting-Host: user-112ut0j.biz.mindspring.com 66.47.116.19
Path: forums-1-dub!forums-master.sybase.com!forums-2-dub.sybase.com
Xref: forums-1-dub ianywhere.public.general:1198
Article PK: 3442

Besides what you can find in the help index under Page Size, you might want
to look at the white paper on improving performance, at
http://www.sybase.com/detail/1,6904,1023801,00.html.

From my reading, it seems that the effect of page size on performance
depends more on your table-row and query characteristics than on the number
of rows in your tables, so I would not expect the optimum page size to
change much as the database grows. However, the help points out that page
size has an impact on index fan-out and therefore on the depth of an index
on a table with many rows. That section states a good rule of thumb: "Large
databases should have 4K pages."

The white paper cited above says pages are limited to 255 rows, so large
page sizes would just waste memory if the tables are narrow. I don't know
whether the 255-row limit applies to index pages as well as data pages. If
it does, then the benefits for indexes of larger pages sizes will diminish
too, because the help says a 4k page size allows about 200 index entries on
a page. Perhaps this is why the help file recommends 4k page size for large
databases.

I hope iAnywhere will change the default page size for new databases to 4k
someday soon. The 2k page size is probably the most frequently
recommended-against default value in the product.

Richard

"Doug Stone" <nospamdstone@res-q.com> wrote in message
news:udEr5xEMDHA.218@forums-2-dub...
>
> We've noticed that performance of our application at customers sites will
> often improve dramatically after increasing db page size from 2048 to
4096.
>
> However, if the customer's db is under a couple of hundred meg, the page
> size increase usually does not improve performance.
> I'm wondering, though, what about our customers whose DBs are increasing
in
> size rapidly? Those customers will probably see their db grow above a
gig,
> in a year or two. Should we suggest that those customers increase their
> page size to 8192 or above?
>
> What is the downside to a large page size?


Glenn Paulley Posted on 2003-06-12 13:36:27.0Z
Date: Thu, 12 Jun 2003 09:36:27 -0400
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
Subject: Re: ASA Page Size
References: <udEr5xEMDHA.218@forums-2-dub> <#rlBCfJMDHA.187@forums-2-dub>
In-Reply-To: <#rlBCfJMDHA.187@forums-2-dub>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
Message-ID: <#OUxsgOMDHA.221@forums-1-dub>
Newsgroups: ianywhere.public.general
NNTP-Posting-Host: paulley-xp.sybase.com 172.31.141.96
Lines: 82
Path: forums-1-dub!forums-master.sybase.com!forums-1-dub.sybase.com
Xref: forums-1-dub ianywhere.public.general:1200
Article PK: 3444

The 255-row limit applies only to table pages, not to indexes.

One other point: I do not recommend page sizes > 8K for virtually all
applications. A 16K or 32K page size usually causes too much buffer pool
pollution and page fragmentation to make their choice worthwhile. By
"buffer pool pollution" I mean that 16 or 32K of RAM will be necessary
to retrieve a single row in any table - and the rest will be wasted.
Unless the cache is made much larger to accomodate this phenomena,
overall performance is bound to suffer.

Glenn

Richard Biffl wrote:
> Besides what you can find in the help index under Page Size, you might want
> to look at the white paper on improving performance, at
> http://www.sybase.com/detail/1,6904,1023801,00.html.
>
> From my reading, it seems that the effect of page size on performance
> depends more on your table-row and query characteristics than on the number
> of rows in your tables, so I would not expect the optimum page size to
> change much as the database grows. However, the help points out that page
> size has an impact on index fan-out and therefore on the depth of an index
> on a table with many rows. That section states a good rule of thumb: "Large
> databases should have 4K pages."
>
> The white paper cited above says pages are limited to 255 rows, so large
> page sizes would just waste memory if the tables are narrow. I don't know
> whether the 255-row limit applies to index pages as well as data pages. If
> it does, then the benefits for indexes of larger pages sizes will diminish
> too, because the help says a 4k page size allows about 200 index entries on
> a page. Perhaps this is why the help file recommends 4k page size for large
> databases.
>
> I hope iAnywhere will change the default page size for new databases to 4k
> someday soon. The 2k page size is probably the most frequently
> recommended-against default value in the product.
>
> Richard
>
> "Doug Stone" <nospamdstone@res-q.com> wrote in message
> news:udEr5xEMDHA.218@forums-2-dub...
>
>>We've noticed that performance of our application at customers sites will
>>often improve dramatically after increasing db page size from 2048 to
>
> 4096.
>
>>However, if the customer's db is under a couple of hundred meg, the page
>>size increase usually does not improve performance.
>>I'm wondering, though, what about our customers whose DBs are increasing
>
> in
>
>>size rapidly? Those customers will probably see their db grow above a
>
> gig,
>
>>in a year or two. Should we suggest that those customers increase their
>>page size to 8192 or above?
>>
>>What is the downside to a large page size?
>
>
>

--
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


Reg Domaratzki Posted on 2003-06-12 13:20:52.0Z
From: "Reg Domaratzki" <rdomarat__AT__ianywhere__DOT__com>
References: <udEr5xEMDHA.218@forums-2-dub>
Subject: Re: ASA Page Size
Date: Thu, 12 Jun 2003 09:20:52 -0400
Lines: 54
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
Message-ID: <OQ68$XOMDHA.345@forums-1-dub>
Newsgroups: ianywhere.public.general
NNTP-Posting-Host: rdomarat-pc.sybase.com 172.31.143.163
Path: forums-1-dub!forums-master.sybase.com!forums-1-dub.sybase.com
Xref: forums-1-dub ianywhere.public.general:1199
Article PK: 3443

One other thing to verify is that it's really the page size that improves
performance, and not the just the fact of rebuilding the database. It's
possible you're seeing the the increase in performance on larger databases
because they've been in use longer, and there is more page fragmentation of
your rows in the database, and the physical database file is more fragmented
on the hard drive as well. A rebuild of the database will help both those
issues, and it's possible that it's the rebuild that helps, and not
necessaarily the page size.

The answer to choosing the right page size is always "it depends" (sorry).
The document that Richard pointed you to in his reply (which is
http://my.sybase.com/detail?id=1023801 for those of you who don't feel
looking back in the thread) has a good section on choosing the right page
size, as well as a plethora of other great tips. The bigest drawback to
larger page sizes are increased memory and disk space usage. Disk space
usage isn't as big a deal as it was 5 years ago, but memory usage is
certainly something to consider. I wouldn't suggest moving from a 4kb to an
8kb page size just because your database file is growing.

--
Reg Domaratzki, Sybase iAnywhere Solutions
Sybase Certified Professional - Sybase ASA Developer Version 8
Please reply only to the newsgroup

iAnywhere Developer Community : http://www.ianywhere.com/developer
ASA Patches and EBFs : http://downloads.sybase.com/swx/sdmain.stm
-> Choose SQL Anywhere Studio
-> Set "Platform Preview" and "Time Frame" to ALL

"Doug Stone" <nospamdstone@res-q.com> wrote in message
news:udEr5xEMDHA.218@forums-2-dub...
>
> We've noticed that performance of our application at customers sites will
> often improve dramatically after increasing db page size from 2048 to
4096.
>
> However, if the customer's db is under a couple of hundred meg, the page
> size increase usually does not improve performance.
> I'm wondering, though, what about our customers whose DBs are increasing
in
> size rapidly? Those customers will probably see their db grow above a
gig,
> in a year or two. Should we suggest that those customers increase their
> page size to 8192 or above?
>
> What is the downside to a large page size?
>
> Thanks,
> Doug
>
>
>