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.

Increasing Page Size

6 posts in Product Futures Discussion Last posting was on 2002-04-02 17:08:41.0Z
Nancy Dreisbach Posted on 2002-01-02 19:39:09.0Z
From: "Nancy Dreisbach" <nancy.dreisbach@starzencore.com>
Subject: Increasing Page Size
Date: Wed, 2 Jan 2002 12:39:09 -0700
Lines: 9
Organization: Starz Encore Group
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.50.4522.1200
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4522.1200
Message-ID: <CWAHPa8kBHA.234@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: 204.97.85.2
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:961
Article PK: 95201

We are looking at upgrading to ASE 12.5, and at the same time are
considering increasing the page size from 2K pages. However, after reading
through documentation, I am struggling with justifying this change. The
only benefit I can see is that the server accesses more data each time it
reads a page. Can't this also be achieved by creating named cache with an
increased page size? What is the benefit to increasing the data page size?
Is there an impact on text pages?


Kevin_MxGinn Posted on 2002-04-02 17:08:41.0Z
From: Kevin_MxGinn
Date: Tue, 2 Apr 2002 13:08:41 -0400
Newsgroups: sybase.public.ase.product_futures_discussion
Subject: Re: Increasing Page Size
Message-ID: <3643BA2B462DB945005E2DC785256B8F.001E636A85256B36@webforums>
References: <CWAHPa8kBHA.234@forums.sybase.com> <MPG.169d8b40c94599b198b983@forums.sybase.com>
Lines: 10
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:572
Article PK: 94100

The biggest drawback that I see with the increased page size is the level
of granularity of applying the increased page size. Applying the increased
page size at a finer granularity would make it's usage far more attractive.
I would like to see the page size settable at least at the database level.
I am aware of the fact that this introduces a wide array of issues and
problems in it's implementation, but this level of granularity is required
to make this feature usable.

Kevin McGinn
Entrada Software - DBA/Data Architect


Juerg_Wyttenbach Posted on 2002-01-25 16:16:37.0Z
From: Juerg_Wyttenbach
Date: Fri, 25 Jan 2002 11:16:37 -0500
Newsgroups: sybase.public.ase.product_futures_discussion
Subject: Re: Increasing Page Size
Message-ID: <9E5D80A58B7C9FEE0059697485256B4C.00700D1485256B35@webforums>
References: <CWAHPa8kBHA.234@forums.sybase.com>
Lines: 27
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:857
Article PK: 94393

In the last six weeks I run two test's with ASE 12.5 on Solaris 8 and HP/UX
both times 64bit. From the quality point of view of ASE core functionality
I would not recommend to plan a migration to a larger page size in the near
future if your ASE background is not seniour enough.

Regarding performance 4 (8k) pages are standard on all large systems since
more than 10 years. Harddrives mixed workload transfer rate is optimal for
block sizes between 8 & 16 k. thus don't expect any severe inpact to any
I/O System as long as you look at OLTP applications. You also rarly will
see any speedup for OLTP applications except in cases where the heigth of
an index decreased or range processing is made (bill positions). In the
tests all Batch like operation showed a tremendous speedup. Bcp run in the
GB/minute range without much tuning.
But be aware that many volume manager's have a limitted stripe size of 64k
and thus chunk size's of 8 or 16 per physical path are the limmiting factor
for large I/O. A 64K I/O finally is only completed if 4 (8) devices
finished their I/O which will significantlly increase the physical I/O
load. If the data comes from a large memory pool (EMC,HDS....cache) then
this is neglectable.
Last : Is is more than urgent that ASE uses row level locking for all
system tables. So this might hurt some of you. But remember that most of
what you see of system tables is a view to an in memory structure and there
locking may happen differently.

juerg.wyttenbach@sunrise.net
VLDB Consultant


Seth Posted on 2002-03-05 02:14:59.0Z
Message-ID: <3C842A23.487D6AE5@sybase.com>
Date: Mon, 04 Mar 2002 21:14:59 -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: Increasing Page Size
References: <CWAHPa8kBHA.234@forums.sybase.com> <9E5D80A58B7C9FEE0059697485256B4C.00700D1485256B35@webforums>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.ase.product_futures_discussion
Lines: 60
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:753
Article PK: 94282

I understand the row-locked catalog issue. It is a major upgrade for us
and customers and as such will be available tentatively in our next
major release.

Regarding the pagesize, the 4K pagesize is much more in alignment
to OS (most of the OS memory pagesize or 4K). We have done performance
benchmarks with 4K pagesize. If you need togo to 4K pagesize,
as Rob mentioned here, if you have narrow rows and all-paged locks
then you will have contention. Suggest using RLL for narrow row tables.
Our TPCC benchmarks was run with APL. One aspect with 4k pagesize
is that the index levels will automatically reduce (more index rows
in the index pages). This means traversing less number of index levels
and results in less number of i/o.

Sethu

Juerg_Wyttenbach wrote:
>
> In the last six weeks I run two test's with ASE 12.5 on Solaris 8 and HP/UX
> both times 64bit. From the quality point of view of ASE core functionality
> I would not recommend to plan a migration to a larger page size in the near
> future if your ASE background is not seniour enough.
>
> Regarding performance 4 (8k) pages are standard on all large systems since
> more than 10 years. Harddrives mixed workload transfer rate is optimal for
> block sizes between 8 & 16 k. thus don't expect any severe inpact to any
> I/O System as long as you look at OLTP applications. You also rarly will
> see any speedup for OLTP applications except in cases where the heigth of
> an index decreased or range processing is made (bill positions). In the
> tests all Batch like operation showed a tremendous speedup. Bcp run in the
> GB/minute range without much tuning.
> But be aware that many volume manager's have a limitted stripe size of 64k
> and thus chunk size's of 8 or 16 per physical path are the limmiting factor
> for large I/O. A 64K I/O finally is only completed if 4 (8) devices
> finished their I/O which will significantlly increase the physical I/O
> load. If the data comes from a large memory pool (EMC,HDS....cache) then
> this is neglectable.
> Last : Is is more than urgent that ASE uses row level locking for all
> system tables. So this might hurt some of you. But remember that most of
> what you see of system tables is a view to an in memory structure and there
> locking may happen differently.
>
> juerg.wyttenbach@sunrise.net
> VLDB Consultant
>


Rob Verschoor Posted on 2002-01-02 22:05:19.0Z
Reply-To: "Rob Verschoor" <rob@sypron.nl>
From: "Rob Verschoor" <rob@sypron.nl>
References: <CWAHPa8kBHA.234@forums.sybase.com>
Subject: Re: Increasing Page Size
Date: Wed, 2 Jan 2002 23:05:19 +0100
Lines: 57
Organization: Sypron B.V.
MIME-Version: 1.0
Content-Type: text/plain; charset="Windows-1252"
Content-Transfer-Encoding: 7bit
X-Newsreader: Microsoft Outlook Express 5.00.2919.6600
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2919.6600
Message-ID: <jf8Vou9kBHA.99@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: p0332.spl.euronet.nl 194.134.112.76
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:960
Article PK: 95198


"Nancy Dreisbach" <nancy.dreisbach@starzencore.com> wrote in message
news:CWAHPa8kBHA.234@forums.sybase.com...
> We are looking at upgrading to ASE 12.5, and at the same time are
> considering increasing the page size from 2K pages. However, after
reading
> through documentation, I am struggling with justifying this change.
The
> only benefit I can see is that the server accesses more data each
time it
> reads a page. Can't this also be achieved by creating named cache
with an
> increased page size? What is the benefit to increasing the data
page size?
> Is there an impact on text pages?
>

The main advantage of having larger page sizes is that you have a
higher I/O capacity -- as much as 128Kb I/Os when using 16Kb server
pages (or 8 times bigger than possible with 2Kb pages). As always, the
question to answer is whether this is solving a problem your currently
having.
Anyway, more I/O capacity is probably most of an advantage when doing
DSS-type queries; when doing OLTP, it would most likely result in a
more inefficient use of the data cache.

There are some other issues to be borne in mind when using large
server pages: when using page lock schemes, the risk of lock
contention will increase as well (but you can use row locking for user
tables) -- however, this also applies to system tables and their
indexes which have the same large page size and which usually have
allpages locking.
Also, an empty table will occupy one extent (or 128 Kb for 16Kb
pages), which may lead to more space left unused in the database
(especially for small tables). And you'll also need more space in
tempdb.

I think I wouldn't go to a large server page size if you don't have a
large data cache, and if there's any risk of concurrency. Otherwise,
give it a try -- and let us know how you liked it !

HTH,

Rob
----------------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 12.0/11.5/11.0

Author of "The Complete Sybase ASE Quick Reference Guide"
Online orders accepted at http://www.sypron.nl/qr

email mailto:rob@sypron.nl.*No*Spam*Please*
WWW http://www.sypron.nl
snail Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands
----------------------------------------------------------------------


Pablo Sanchez Posted on 2002-01-12 22:31:52.0Z
From: "Pablo Sanchez" <pablo@dev.null>
References: <CWAHPa8kBHA.234@forums.sybase.com> <jf8Vou9kBHA.99@forums.sybase.com>
Subject: Re: Increasing Page Size
Date: Sat, 12 Jan 2002 15:31:52 -0700
Lines: 43
Organization: High-Performance Database Engineering
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
Message-ID: <rx$bhm7mBHA.317@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: 207.225.105.222
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:943
Article PK: 95183


"Rob Verschoor" <rob@sypron.nl> wrote in message
news:jf8Vou9kBHA.99@forums.sybase.com...
> "Nancy Dreisbach" <nancy.dreisbach@starzencore.com> wrote in message
> news:CWAHPa8kBHA.234@forums.sybase.com...
> > We are looking at upgrading to ASE 12.5, and at the same time are
> > considering increasing the page size from 2K pages. However,
after
> reading
> > through documentation, I am struggling with justifying this
change.
> The
> > only benefit I can see is that the server accesses more data each
> time it
> > reads a page. Can't this also be achieved by creating named cache
> with an
> > increased page size? What is the benefit to increasing the data
> page size?
> > Is there an impact on text pages?
> >
>
> The main advantage of having larger page sizes is that you have a
> higher I/O capacity -- as much as 128Kb I/Os when using 16Kb server
> pages (or 8 times bigger than possible with 2Kb pages). As always,
the
> question to answer is whether this is solving a problem your
currently
> having.

Adding to what Rob mentioned, page sizes map directly to system I/O
calls: read/write If you have a DSS application, you'll stress the
system less doing less, larger I/O's, than more, smaller I/O's:

16K = four 2K I/O's
- or -
one 16K I/O
--
Pablo Sanchez, High-Performance Database Engineering
www.hpdbe.com
Independent Contractor, available for short-term and long-term
contracts