Subject Re: Database Page size = WAS RE: [firebird-support] What UDF - Lib to use ?
Author Ann W. Harrison
At 09:45 AM 7/20/2004, Jonathan Neve wrote:

>Is there ever a case for using a higher value [than 1024 for page
>size]? What would be the effect?

The most important effect of a larger page size is reduced index
depth. Firebird indexes have a single page at the top and fan out from
there. The larger the page size, the wider the fan - i.e. one 4096 byte
page points to nearly four times as many pages at the next level down than
a 1024 byte page. The deeper the index, the slower retrievals become. To
a record through an index, the engine must read the top page of the index
and one page at each level until it gets to the bottom. Then it reads the
page with the record. The fewer levels, the fewer reads.

This "fan out" benefit is also seen in large blobs. A smallish blob fits on
a single page. Obviously, the larger the page size, the larger a simple,
one part blob can be. A blob that is larger than a page has two parts, an
array of page numbers and the pages that contain the data. The larger the
page, the fewer pages are required to hold the blob, therefor the shorter
the array, or the more data that can be stored in a blob before the array
itself exceeds a page. A huge blob has three parts, an array of page
numbers that identify pages of arrays of page numbers that identify pages
containing data.

A larger page size also reduces the number of pages used for internal
structures - pointer pages, transaction inventory pages, and page inventory
pages. The same number of bits are used, of course, but fewer pages, thus
fewer page reads.