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.

Pros and Cons of larger page size on ASE

6 posts in General Discussion Last posting was on 2010-08-25 12:47:04.0Z
John Everitt Posted on 2010-08-20 09:23:19.0Z
Sender: 191.4c6e46c4.1804289383@sybase.com
From: John Everitt
Newsgroups: sybase.public.ase.general
Subject: Pros and Cons of larger page size on ASE
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4c6e4987.435.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 20 Aug 2010 02:23:19 -0700
X-Trace: forums-1-dub 1282296199 10.22.241.41 (20 Aug 2010 02:23:19 -0700)
X-Original-Trace: 20 Aug 2010 02:23:19 -0700, 10.22.241.41
Lines: 30
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29478
Article PK: 78716

All,

ASE 15.0.2 ESD#5 64 bit on RHEL 5.4

I am considering migrating my production ASE servers from 2k
page size to an 8k page size. We are using HP DL380 G6
servers and it seems that the Raid controller on these boxes
is much happier using an 8k i/o block size rather than 2k.
In fact, during testing we are getting up to a 4x
improvement in performance (obviously the higher i/o load
tests produce the bigger differences in performance). I have
tried adding 8k i/o pools to the caches on the 2k server and
although I can get performance improvements, it's no where
as near as good as using an 8k page size.

The question is then, are there any other issues that I
should be aware of when moving to an 8k page size ? In the
past on 12.5 I have had my fingers burnt with 8k servers
where according to Sybase, the servers suffered alot from
procedure cache fragmentation, which is significantly
accentuated with a larger page size. Does anyone know of any
similar issues with later versions of ASE ?

Thanks in advance.

John Everitt
Database Manager
Metropolitan Health
Cape Town
South Africa


Rob V [ Sybase ] Posted on 2010-08-20 16:52:44.0Z
From: "Rob V [ Sybase ]" <robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
Reply-To: robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY
Organization: Sypron BV / TeamSybase / Sybase
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.8) Gecko/20100802 Thunderbird/3.1.2
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Pros and Cons of larger page size on ASE
References: <4c6e4987.435.1681692777@sybase.com>
In-Reply-To: <4c6e4987.435.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: <4c6eb2dc$1@forums-1-dub>
Date: 20 Aug 2010 09:52:44 -0700
X-Trace: forums-1-dub 1282323164 10.22.241.152 (20 Aug 2010 09:52:44 -0700)
X-Original-Trace: 20 Aug 2010 09:52:44 -0700, vip152.sybase.com
Lines: 63
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29483
Article PK: 78710


On 20-Aug-10 11:23, John Everitt wrote:
> All,
>
> ASE 15.0.2 ESD#5 64 bit on RHEL 5.4
>
> I am considering migrating my production ASE servers from 2k
> page size to an 8k page size. We are using HP DL380 G6
> servers and it seems that the Raid controller on these boxes
> is much happier using an 8k i/o block size rather than 2k.
> In fact, during testing we are getting up to a 4x
> improvement in performance (obviously the higher i/o load
> tests produce the bigger differences in performance). I have
> tried adding 8k i/o pools to the caches on the 2k server and
> although I can get performance improvements, it's no where
> as near as good as using an 8k page size.
>
> The question is then, are there any other issues that I
> should be aware of when moving to an 8k page size ? In the
> past on 12.5 I have had my fingers burnt with 8k servers
> where according to Sybase, the servers suffered alot from
> procedure cache fragmentation, which is significantly
> accentuated with a larger page size. Does anyone know of any
> similar issues with later versions of ASE ?
>
> Thanks in advance.
>
> John Everitt
> Database Manager
> Metropolitan Health
> Cape Town
> South Africa

Pro: larger page size provides higher disk I/O bandwith
Con: larger page size can lead to lower cache efficiency and therefore
higher physical I/O (when on average only a single row is read for every
page brought into cache)
Con: when using page locking, you lock more rows when the page size is
larger, thus increasing the chance of lock contention
Con: existing database dumps with a different page size cannot be loaded
into a server with a different page size

It all depends how you think this all works out for your system...

HTH,

Rob V.
-----------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 15.0/12.5/12.0/11.5/11.0
and Replication Server 15.0.1/12.5 // TeamSybase

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks& Recipes for Sybase ASE" (ASE 15 edition)
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"

mailto:rob@YOUR.SPAM.sypron.nl.NOT.FOR.ME
http://www.sypron.nl
Sypron B.V., Amersfoort, The Netherlands
Chamber of Commerce 27138666
-----------------------------------------------------------------


Sherlock, Kevin [TeamSybase] Posted on 2010-08-20 17:52:11.0Z
From: "Sherlock, Kevin [TeamSybase]" <kevin.sherlock@teamsybase.com>
Newsgroups: sybase.public.ase.general
References: <4c6e4987.435.1681692777@sybase.com>
Subject: Re: Pros and Cons of larger page size on ASE
Lines: 32
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5512
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4c6ec0cb$1@forums-1-dub>
Date: 20 Aug 2010 10:52:11 -0700
X-Trace: forums-1-dub 1282326731 10.22.241.152 (20 Aug 2010 10:52:11 -0700)
X-Original-Trace: 20 Aug 2010 10:52:11 -0700, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29484
Article PK: 78714

As far as I can remember, logical page size had no relationship to how
procedure cache was managed. I believe in an early ESD for 15.0 , there
were some changes to the memory manager around procedure cache to reduce the
likelyhood of 701 errors (due to fragmentation, or otherwise). In general,
pages in procedure cache are allocated in 2k chunks at first (regardless of
logical page size of the server), and then grows by double until the calling
task has it's memory. I think there is an exception for proc plans though
(these get 16k chunks from the begining) since they are usually going to
consume more than 2k. This was a change to procedure cache managment which
used to always just allocate the smallest blocks possible which lead to 701
errors when a large contiguous block was needed.

Here is a CR listed in 15.0 ESD2 cover letter:

dataserver 407021 Procedure cache management is enhanced to perform
auto-tuned large allocation. Procedure cache memory request start with
chunks of 2k. Large chunks, upto 16k, are allocated based on the past
allocation behavior for the query. The enhancement reduces external
fragmentation in the procedure cache.

<John Everitt> wrote in message news:4c6e4987.435.1681692777@sybase.com...
..<clip>
> The question is then, are there any other issues that I
> should be aware of when moving to an 8k page size ? In the
> past on 12.5 I have had my fingers burnt with 8k servers
> where according to Sybase, the servers suffered alot from
> procedure cache fragmentation, which is significantly
> accentuated with a larger page size. Does anyone know of any
> similar issues with later versions of ASE ?


Sherlock, Kevin [TeamSybase] Posted on 2010-08-20 17:57:12.0Z
From: "Sherlock, Kevin [TeamSybase]" <kevin.sherlock@teamsybase.com>
Newsgroups: sybase.public.ase.general
References: <4c6e4987.435.1681692777@sybase.com> <4c6ec0cb$1@forums-1-dub>
Subject: Re: Pros and Cons of larger page size on ASE
Lines: 47
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5512
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4c6ec1f8$1@forums-1-dub>
Date: 20 Aug 2010 10:57:12 -0700
X-Trace: forums-1-dub 1282327032 10.22.241.152 (20 Aug 2010 10:57:12 -0700)
X-Original-Trace: 20 Aug 2010 10:57:12 -0700, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29485
Article PK: 78713

Maybe some day, procedure cache will actually be a "cache" where compiled
objects are managed, and all of this "scratch pad" usage by the other
"modules" (see monProcedureCacheModuleUsage, and
monProcedureCacheMemoryUsage ) can be set aside somewhere else (perhaps
configurable to the dba?).

BTW - when upgrading from 12.5 to 15.x, I would suggest recreating ALL of
your stored procedures to take advantage of a small efficiency in a change
to sysprocedures table.

"Sherlock, Kevin [TeamSybase]" <kevin.sherlock@teamsybase.com> wrote in
message news:4c6ec0cb$1@forums-1-dub...
> As far as I can remember, logical page size had no relationship to how
> procedure cache was managed. I believe in an early ESD for 15.0 , there
> were some changes to the memory manager around procedure cache to reduce
> the likelyhood of 701 errors (due to fragmentation, or otherwise). In
> general, pages in procedure cache are allocated in 2k chunks at first
> (regardless of logical page size of the server), and then grows by double
> until the calling task has it's memory. I think there is an exception for
> proc plans though (these get 16k chunks from the begining) since they are
> usually going to consume more than 2k. This was a change to procedure
> cache managment which used to always just allocate the smallest blocks
> possible which lead to 701 errors when a large contiguous block was
> needed.
>
> Here is a CR listed in 15.0 ESD2 cover letter:
>
> dataserver 407021 Procedure cache management is enhanced to perform
> auto-tuned large allocation. Procedure cache memory request start with
> chunks of 2k. Large chunks, upto 16k, are allocated based on the past
> allocation behavior for the query. The enhancement reduces external
> fragmentation in the procedure cache.
>
>
> <John Everitt> wrote in message news:4c6e4987.435.1681692777@sybase.com...
> ..<clip>
>> The question is then, are there any other issues that I
>> should be aware of when moving to an 8k page size ? In the
>> past on 12.5 I have had my fingers burnt with 8k servers
>> where according to Sybase, the servers suffered alot from
>> procedure cache fragmentation, which is significantly
>> accentuated with a larger page size. Does anyone know of any
>> similar issues with later versions of ASE ?
>
>


Randall Jordan Posted on 2010-08-24 10:41:49.0Z
From: Randall Jordan <randall.jordan@intrade.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.8) Gecko/20100802 Thunderbird/3.1.2
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Pros and Cons of larger page size on ASE
References: <4c6e4987.435.1681692777@sybase.com>
In-Reply-To: <4c6e4987.435.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: <4c73a1ed$1@forums-1-dub>
Date: 24 Aug 2010 03:41:49 -0700
X-Trace: forums-1-dub 1282646509 10.22.241.152 (24 Aug 2010 03:41:49 -0700)
X-Original-Trace: 24 Aug 2010 03:41:49 -0700, vip152.sybase.com
Lines: 51
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29494
Article PK: 78725

I'd be more worried about the number of rows per page, if you have not
calculated that, you should. It has been pointed out that when sybase is
page locking, you will lock up all the rows on that page, however if you
are in rowlock mode, the only problem will not be locking, but I/O as to
read a new row, you will have to read the entire block of data from the
disk into cache which will be an increase of 4x in I/O. BTW are you
doing transactional, or large Scale data moving?

Further note, that the Disk and the controller are not the only elements
affecting the performance. The type of buss, the amount of ram, the size
of the caches in the database are all impacted, and probably to a
greater extent that the disk and controller.

Are you using Raw, or filesystem for the database devices? If raw, I can
see your I/O issues, but if you are on files the system buffers will
mask any performance issues with the blocking size. Files will be faster.

On 20/08/2010 10:23, John Everitt wrote:
> All,
>
> ASE 15.0.2 ESD#5 64 bit on RHEL 5.4
>
> I am considering migrating my production ASE servers from 2k
> page size to an 8k page size. We are using HP DL380 G6
> servers and it seems that the Raid controller on these boxes
> is much happier using an 8k i/o block size rather than 2k.
> In fact, during testing we are getting up to a 4x
> improvement in performance (obviously the higher i/o load
> tests produce the bigger differences in performance). I have
> tried adding 8k i/o pools to the caches on the 2k server and
> although I can get performance improvements, it's no where
> as near as good as using an 8k page size.
>
> The question is then, are there any other issues that I
> should be aware of when moving to an 8k page size ? In the
> past on 12.5 I have had my fingers burnt with 8k servers
> where according to Sybase, the servers suffered alot from
> procedure cache fragmentation, which is significantly
> accentuated with a larger page size. Does anyone know of any
> similar issues with later versions of ASE ?
>
> Thanks in advance.
>
> John Everitt
> Database Manager
> Metropolitan Health
> Cape Town
> South Africa


John Everitt Posted on 2010-08-25 12:47:04.0Z
Sender: 66fa.4c750c54.1804289383@sybase.com
From: John Everitt
Newsgroups: sybase.public.ase.general
Subject: Re: Pros and Cons of larger page size on ASE
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4c7510c7.6744.1681692777@sybase.com>
References: <4c73a1ed$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 25 Aug 2010 05:47:04 -0700
X-Trace: forums-1-dub 1282740424 10.22.241.41 (25 Aug 2010 05:47:04 -0700)
X-Original-Trace: 25 Aug 2010 05:47:04 -0700, 10.22.241.41
Lines: 56
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29495
Article PK: 78729

Randell,

Thanks for the feedback.

We use DOL tables almost exclusively so I'm not too worried
about lock contention.

We are also using directio on linux which should in theory
bypass the file system drivers. We did some testing on our
servers (HP DL380 G7 with P400i Raid Controller using
internal SAS drives) and the reason we investigated 8k page
size is that this server appeared to be much happier using
8k i/o block sizes while 2k resulted in a relatively poor
throughput (we used iometer to test i/o throughput).
Application testing also suggested a near 4x improvement in
performance for high i/o operations (i.e. when the caches
were empty). I believe this is due to the server using 8k
blocks on the physical disks so whether you read 8k or 2k
blocks, the work done to retrieve the info is pretty much
the same.

Incidentally, the application is generally oltp although
there are one or two "expensive" queries that can return a
few thousand wide (500 bytes approx) records. This where we
get the majority of the performance gain unsuprisingly.

So, I get the impression that I shouldn't be too concerned
about using 8k pages. How I wish though that Sybase had some
sort of tool (other than bcp) that could assist with page
size changes ...

Thanks again.

John

> I'd be more worried about the number of rows per page, if
> you have not calculated that, you should. It has been
> pointed out that when sybase is page locking, you will
> lock up all the rows on that page, however if you are in
> rowlock mode, the only problem will not be locking, but
> I/O as to read a new row, you will have to read the
> entire block of data from the disk into cache which will
> be an increase of 4x in I/O. BTW are you doing
> transactional, or large Scale data moving?
>
> Further note, that the Disk and the controller are not the
> only elements affecting the performance. The type of buss
> , the amount of ram, the size of the caches in the
> database are all impacted, and probably to a greater
> extent that the disk and controller.
>
> Are you using Raw, or filesystem for the database devices?
> If raw, I can see your I/O issues, but if you are on
> files the system buffers will mask any performance issues
> with the blocking size. Files will be faster.
>