How much does the size of a database page affect its performance ?

[former_SQLA_member1694881]

Hi,

SAP SQL AnyWhere 17.0.10 x64

There is a database with a page size of 16 kb. There is a need to increase the size of the database page to the maximum, that is, up to 32 kb.

Will this affect the performance of the database server and the speed of executing SQL-queries ? Or will this only affect the size of the database file (.db and .log) ?

[VolkerBarth]

See the docs on limitations:

Table size is limited by maximum file size, and on current OSes and file systems (NTFS etc.), even for 8 KB pages you can go up to 2 TB (so with your current 16 KB, it might even be 4 TB), the 4 GB limit you mentioned only holds for FAT32 and older file systems...

[former_SQLA_member1694881]

I'm not talking about limiting the database file dependent on the file system, but about the limiting depending on the page size of the database.

[former_SQLA_member1694881]

Dbspace size = 2^28 x page size

[VolkerBarth]

...which for your current 16 KB page size would be (with my math):

2^28 * 16 * 2^10 = 2^42 = 4 TB

[MCMartin]

See Use an Appropriate Page Size

[BudDurland]

Following this out of curiosity. What is driving the need to use 32K pages? My understanding has always been that there's a little voodoo to tuning the page size, to balance storage efficiency vs. performance, and one of the key factors is record widths in the db. I.e., if the db is mostly little records, a larger page size may not provide much benefit.

[former_SQLA_member1694881]

In my database, in a separate table space, there is table with texts of documents of various formats (files with documents). Now the size of this table space is approximately 2 GB, taking into account a page of 16 KB, it can grow to 4 GB. I don’t want to store files with document texts outside the database (it’s not convenient).

I know about the algorithm (method) of how to programmatically split one table into several table spaces. But before putting it into operation, I first want to increase the size of the table space to its maximum size of 8 GB (in order to make full use of the maximum number of table space files in one database). And that’s why I need a 32 kb page.