SQL Anywhere Feedback - Option to specify page size for dbspace

Author

Option to specify page size for dbspace

Andrei Iliev

2005-06-09, 1:23 pm

Currently (ASA 9.0.2), all dbspaces in the database have page size
specified under db creation (dbinit -p). Option to specify page size
separately for dbspace can improve performance in cases where some tables
are fat, some narrow. Example:
1) table t1(id,attribute1,att
ribute2) - narrow table - could be created in
dbspace with page size say 2-4k
2) table t2(id,blobdata) - very fat table contains blob data (images, long
text etc) - could be created in dbspace with page size say 8-32K

Andrei Iliev

Breck Carter [TeamSybase]

2005-06-09, 8:23 pm

I support the possibility of tuning space usage for different parts of
the same database, as well as allowing the file-to-dbspace
relationship to be something other than 1-to-1.

However...

Only the first 255 bytes of a blob column is stored in a table data
page, the rest goes in extension pages.

IMO the need for 8K is rare, and more often is helpful for tables with
large numbers of rows and many indexes rather than fat rows.

Also, 16K and 32K should never be used IMO.

Breck

On 9 Jun 2005 10:15:12 -0700, "Andrei Iliev" <xzxz@mail.ru> wrote:

>Currently (ASA 9.0.2), all dbspaces in the database have page size
>specified under db creation (dbinit -p). Option to specify page size
>separately for dbspace can improve performance in cases where some tables
>are fat, some narrow. Example:
>1) table t1(id,attribute1,att
ribute2) - narrow table - could be created in
>dbspace with page size say 2-4k
>2) table t2(id,blobdata) - very fat table contains blob data (images, long
>text etc) - could be created in dbspace with page size say 8-32K
>
>Andrei Iliev
>

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

Andrei Iliev

2005-06-10, 7:23 am

> Only the first 255 bytes of a blob column is stored in a table data
> page, the rest goes in extension pages.
If you have larger page size you would get smaller number of extension pages
and hence less fragmentation and faster access? Besides, less number of
extension pages leads to less wasting of disk space in case where PCTFREE
!= 0
Am I right?

"Breck Carter [TeamSybase]" < NOSPAM__bcarter@risi
ngroad.com> wrote in
message news:tlcha11pi4pp8f8
2b6283voqmpvvhvkaf7@
4ax.com...
> I support the possibility of tuning space usage for different parts of
> the same database, as well as allowing the file-to-dbspace
> relationship to be something other than 1-to-1.
>
> However...
>
> Only the first 255 bytes of a blob column is stored in a table data
> page, the rest goes in extension pages.
>
> IMO the need for 8K is rare, and more often is helpful for tables with
> large numbers of rows and many indexes rather than fat rows.
>
> Also, 16K and 32K should never be used IMO.
>
> Breck
>
>
> On 9 Jun 2005 10:15:12 -0700, "Andrei Iliev" <xzxz@mail.ru> wrote:
>
in[color=darkred]
long[color=darkred]
>
> --
> SQL Anywhere Studio 9 Developer's Guide
> Buy the book:
http://www.amazon.com/exec/obidos/A...7/risingroad-20
> bcarter@risingroad.com
> RisingRoad SQL Anywhere and MobiLink Professional Services
> www.risingroad.com

John Smirnios

2005-06-10, 11:23 am

Just as when you run two databases with different page size, cache
memory would be wasted for every image cached from dbspaces with the
smaller page size. The cache manager divides the cache images into
pieces of equal size and doing otherwise would be complex & expensive.

-john.
--
John Smirnios
Senior Software Developer
iAnywhere Solutions Engineering

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

Andrei Iliev wrote:
>
> If you have larger page size you would get smaller number of extension pages
> and hence less fragmentation and faster access? Besides, less number of
> extension pages leads to less wasting of disk space in case where PCTFREE
> != 0
> Am I right?
>
> "Breck Carter [TeamSybase]" < NOSPAM__bcarter@risi
ngroad.com> wrote in
> message news:tlcha11pi4pp8f8
2b6283voqmpvvhvkaf7@
4ax.com...
>
>
> in
>
>
> long
>
>
> http://www.amazon.com/exec/obidos/A...7/risingroad-20
>
>
>
>

Breck Carter [TeamSybase]

2005-06-10, 11:23 am

Yes, with a larger page size a blob value will fit in fewer extension
pages.

I'm not sure "larger page size" always leads to "smaller number of
pages"... if there are a large number of very narrow rows it might
lead to wasted space because there is also a limit on the number of
rows stored in a table page: 255.

I'm not sure "smaller number of pages" always implies "less
fragmentation"... row splits are a performance killer, and two
different databases could have vastly different numbers of row splits
depending on application update behavior, even if the numbers of rows
and pages were the same.

I'm not sure "larger page size" always implies "fewer I/O operations"
because the engine reads data in 64K blocks.

I don't think PCTFREE applies to extension pages.

A large page size may have a dramatic effect on the performance of
large indexes. However, the rule of thumb remains valid in most cases:
use 4K unless performance tests indicate 8K will help.

Breck

On 10 Jun 2005 02:43:19 -0700, "Andrei Iliev" <xzxz@mail.ru> wrote:

>If you have larger page size you would get smaller number of extension pages
>and hence less fragmentation and faster access? Besides, less number of
>extension pages leads to less wasting of disk space in case where PCTFREE
>!= 0
>Am I right?
>
>"Breck Carter [TeamSybase]" < NOSPAM__bcarter@risi
ngroad.com> wrote in
>message news:tlcha11pi4pp8f8
2b6283voqmpvvhvkaf7@
4ax.com...
>in
>long
>http://www.amazon.com/exec/obidos/A...7/risingroad-20
>

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

Sponsored Links