Christian Kaufmann wrote:
I always thought, that the fill ratio is corrected to about 80% when doing a restore. But after my restore, I get the following values for the biggest table:
RANKINGITEM (154)
Primary pointer page: 186, Index root page: 187
Data pages: 25095, data page slots: 25095, average fill: 59%
Fill distribution:
0 - 19% = 0
20 - 39% = 1
40 - 59% = 25094
60 - 79% = 0
80 - 99% = 0
The table contains about 7'000'000 records. Most of these will remain unchanged, but new records will be added in the future. The data fields of my record are two smallint and two integer fields.
- should I do a restore with -use_all_space ?
- should I just leave it like this ?
- other suggestion for changing it ?
Ann W. Harrison answers:
It is well - and incorrectly - known that the fill ratio is 80% if the database is set up to retain space for new versions. I've read it often from sources who should know. But I've also read the code and was around when it was designed. When space is reserved, the system leaves space for one fragmented record header for every primary record version stored on a page.
A primary record version is the newest version of a record - back versions, blobs, and fragments don't count.
A fragmented record is one that is split across pages, either because it was longer than a page or because it was forced to be on a particular page and didn't fit completely there.
A fragmented record header is a normal record header plus a pointer to the page and line of the fragmented part of the data. Counting on my fingers, I think it's 22 bytes, of which six plus three fill bytes occur only in fragmented records.
So, if your records compress to less than 9 bytes, you'll have more than 50% reserved space.
In this case, I would use that switch. The reserved space is used only for modified and deleted records. If there is no space on page when a modification or delete is done, Firebird will move the back version to a different page. That's expensive, but not nearly so expensive as having all pages half filled.
Christian Kaufmann continues:
OK. But if I understand correctly, even an update of a single record, where I change only the field SWIMRESULTID forces Firebird to add a new copy of it and then, a new page will be necessary?
Ann W. Harrison answers:
Data is stored on data pages and index entries are stored on b-tree pages, so the number and types of indexes don't affect data storage.
If you use the -use_all_space switch, the database will put as many records as can possibly fit on each page. When you modify a record, Firebird first checks to see if the old version will fit on the same page with the new version. If not, then the old version will be put on a previously allocated data page for that table that does have space. First choice goes to dirty pages in the cache, second to other data pages in the cache, third to pages on disk.
If there is no previously allocated data page for that table with space, Firebird will take a free page in the file and allocate it to the table. If there are no free pages in the file, Firebird will extend the file and allocate the new page to the table.
When the back version is stored on a page, that page is available for new records or back versions. Eventually, the back version will become unnecessary and it will be removed, leaving space on that page for a back version of one of the newer records.
Until InterBase 3, we didn't reserve space for modifications. The first set of updates and deletes after the data was loaded tended to create a lot of I/O as back versions required new pages but eventually removing old versions and deleted records would create space and things got faster. The reserved space was added to reduce that "settling in" cost, but given the size of your records, the algorithm produces too much free space and you do excessive I/O because pages are only 60% full.