Subject Re: [firebird-support] Correct Page Size for the database.
Author ian
Leyne, Sean wrote:
> Martin,
>> Currently using 4096 and wondering if we should be using something
> else
>> to improve performance.
> Performance is only affected by database page size in exceptional
> situations.
> I would suggest that you review your application and the SELECT
> statements which it is executing. This is where I think would find the
> source of any performance problems.
> As to your question, there are 3 factors which I believe need to be
> considered in setting the page size value:

Also read Helen's book, it is mentioned in there.
> 1 - the Depth value from GStat (db statistics) for indexes. A value of
> 3 or more suggests that a larger page size is required.
This only works with data in the database, so does not help at the set
up stage. Firebird, however, does allow for changes in page size with a
backup and restore.

> 2 - the size of the data rows and/or the size of BLOBs to be stored. If
> you are going to be storing large BLOB; the bigger the page, the better,
> since this will reduce the number of fetchs the engine will need to
> perform.
Data rows, excluding blob type fields, should fit on one page (they have
to in SQLServer). Usually there will be many records on a page. Blobs
can be significant if loaded frequently, but ignore them if you only
load a blob field once a week.
> 3 - To a lesser extent, the size of the disk cluster (or is it sector
> size?) for the drive you are using. In an ideal world, I would format
> the drive so that the db page size and the cluster size would be the
> same.

OK if you have that much control and it does not adversely affect
anything else and you do not want to change the page size in future.

Having a db page size which is larger than a disk page could mean
> that the disk would need to perform multiple disk reads in order to read
> a single db page.

This will be the normal case for Firebird installs.

The growing prevalence of cache disk controller and
> SAN based disks, is reducing the significance of drive characteristics
> as factor to selection of database page size.
Yes, although users going for Firebird are likely to be the last in line.