Yartax wrote:
What's the best page size from a peculiar database? it can be calculated from tables structure, like max record size from a table?
Eduardo A. Salgado answers:
I say use 4k as the basis and then forget about the page size until the depth of indexes becomes greater than 3 (whatever that means). When that happens, do a backup and restore your database with an 8k page size. Repeat the forget about it. The largest page size is 16k. You can get Helen's book on Firebird for more info but, if I recall correctly, this is one area where you need not spend much time wondering about how to opitimize it.
Ann W. Harrison answers:
That's good advice. Get your data loaded using a 4Kb page then run gstat on it with the -a and -r switches, sending the output to a file.
For each table, check the average record length. If you have any records that average more than 2Kb, you should probably increase the page size because one record per page isn't very efficient.
Check the indexes looking for any that have more than three levels. Firebird indexes are shaped like triangles with a broad level at the bottom containing key values and pointer to records, a much narrower level above containing key values and pointers to the bottom pages, and (one hopes) a single block at the top, containing key values and pointers to pages of pointers to pages of pointers to records. After four levels, you're spending more time getting through the pages of pointers than is efficient. Doubling the page size quadruples the fan-out...