Subject Re: [firebird-support] Re: use of -USE_ALL_SPACE
Author Ivan Přenosil
> as I now understand it, if I restore the database with -USE_ALL_SPACE &

> then do gfix -use reserve I will have a database which has all the static

> data on full pages and the data that does change will quickly go back to

> the 80/20 page usage. Something like 90% is static so I'm hoping this will

> give me some improved performance on queries against the static data.

It depends - do you want to update only new data, or those restored with

use_all_space too ? The problem is that UPDATE will never change

record's location, it will stay on "it's" page "forever".

So, in "normal" database, when you update the record, FB will store

the record and its older version(s) on the same page (if free space on that

is sufficient). Thus, update will touch only single page, garbage collection

will also touch single page (not counting indexes etc).

In "your database" :) when you update the record on 100% filled page,

FB will store the new data on original place/page, whereas backversion

on another page. Thus, update and garbage collection will both

touch at least two pages each.

I.e. you get worse performance.

And that is not all - when you update the record such that it requires more

space to store, the record still has to remain on the original page,

but since there is not enough room, it becomes fragmented - part is stored

on original page, part on new page.

Thus it will affect all subsequent oprations with such record -

whenever you read that record, FB will have to read 2 db pages !


[Non-text portions of this message have been removed]