ASE 15 pagesize

[unknown]

Is there an official recomendation from Sybase for pagesize
specifically for ASE 15? ASE 15 @Windows has a dropdown
which, defaults to 4K (if i remember it right), not on
solaris(i guess its still 2K).

Any documented preferred pagesize recomendation from Sybase?

Your thoughts on -> if not 2k, 4k or 8k? any benchmark for
4k/8k to look at?

[Rob V [ Sybase ]]

I don't think there's any official recommendation, because in practice it
all depends on your actual storage configuration as well as the workload
characteristics in your system.

Some factors that play a role here are:
- disk I/O bandwith: bigger pages in principle mean higher effective I/O
speeds but this really depends on your storage configuration
- transaction logging patterns: larger server page size could lead to better
performance, but also to worse (due to log pages being re-written more
times).
- concurrency: when a page is larger, more rows will fit on it, which also
implies that a page lock will lock more rows. This can be a problem in
concurrent environments where blocking locks occur
- when accessing only few rows for every page read from disk, your cache hit
ratio will decrease which, in principle, is not good for performance

You get the idea -- it is not necessarily obvious to predict the effect of
using larger pages. You should test to see what works best for you.
One important factor here is also whether existing database dumps can be
loaded: this is only possible if both servers have the same page size. If
this is needed than you may need to stick to the server page size in yor
current servers.

If you're completely free to choose then 4KB seems a reasonable choice in
general for OLTP systems, whereas 8KB or 16KB may be better for DSS systems
(but again, how homogeneous is your workload?)

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:***@YOUR.SPAM.sypron.nl.NOT.FOR.ME
http://www.sypron.nl
Sypron B.V., Amersfoort, The Netherlands
Chamber of Commerce 27138666
-----------------------------------------------------------------

Post by unknown
Is there an official recomendation from Sybase for pagesize
which, defaults to 4K (if i remember it right), not on
solaris(i guess its still 2K).
Any documented preferred pagesize recomendation from Sybase?
Your thoughts on -> if not 2k, 4k or 8k? any benchmark for
4k/8k to look at?

[Eugene]

See very good book "Practical Oracle 8i" by J.Lewis (pp.192-195)
(I think for Sybase it is the same) the recommendation is
that it should be = the OS pagesize (for Solaris = 8k),
this is by the way shows that old Sybase recommendation
about raw device better than fs is not completely correct, cause
Sybase was recommending 2K page when fs size = 8K and raw devices
just little bit better than fs (12.05 sec vs 15.61 sec 1000 writes 8k)
comparing with 1000 writes of 2k pagesize = 26.63 sec.

So that 2K pagesize fs block 2 times slower than 8K pagesize fs
(but for raw devices it is almost the same ~ 12.sec)

HTH,
Eugene

Post by unknown
Is there an official recomendation from Sybase for pagesize
which, defaults to 4K (if i remember it right), not on
solaris(i guess its still 2K).
Any documented preferred pagesize recomendation from Sybase?
Your thoughts on -> if not 2k, 4k or 8k? any benchmark for
4k/8k to look at?