| Subject | Re: [firebird-support] database growing too much after bulk import |
|---|---|
| Author | Helen Borrie |
| Post date | 2008-11-05T20:37:26Z |
At 05:56 AM 6/11/2008, you wrote:
[ ... answered by Ann...]
Note that, if you drop the table and then recreate it, before doing this one-time import, there will be no garbage and that space will be available for re-use immediately.
However, if your first action after restoring is to delete all the records in this table, then you will have garbage on the pages where the data was restored. , When you start inserting new records, there will be no spare space to re-use, so the engine requests and allocates new pages as required. Provided you are committing work (deletes and inserts in the case of the task you described) in a timely manner, the space occupied by the deleted record version stubs will become available after a subsequent garbage collection or sweep.
In other words, such a jump in growth is likely to be observed only under conditions where space available for re-use is too little to accommodate the new records or record versions...
To reiterate: deleting records or dropping tables does NOT reduce the size of the database file.
./heLen
[ ... answered by Ann...]
>And the DatabaseGrowthIncrement part in the config file is the default. It'sYes; and it is not a wise idea to alter it if you are not the person watching over the database on a day-to-day basis. It protects the database from getting corrupted if disk space runs out during a write.
>commented out.
>#DatabaseGrowthIncrement = 134217728Deleting records does not make a database smaller. Later, when your application's behaviour allows it, the space previously used by those deleted records ("garbage") will be released for re-use.
>
>
>So, is this a normal behaviour of the database? I thought it could be an error
>in the design on the STOCK table. The "import" section of my app erases all the records before importing from the DBF file. This task will run just one time
>when the databse starts to work in production, and no more. Is that OK?
Note that, if you drop the table and then recreate it, before doing this one-time import, there will be no garbage and that space will be available for re-use immediately.
>Anyway, this growing was observed after doing a backup/restore, so there are no old versions of records. Am I right?A restore restores all of the data that was visible to the gbak transaction as "latest committed version" at the time of backup. So a freshly restored database has no back versions and also it has no "spare" space on pages.
However, if your first action after restoring is to delete all the records in this table, then you will have garbage on the pages where the data was restored. , When you start inserting new records, there will be no spare space to re-use, so the engine requests and allocates new pages as required. Provided you are committing work (deletes and inserts in the case of the task you described) in a timely manner, the space occupied by the deleted record version stubs will become available after a subsequent garbage collection or sweep.
In other words, such a jump in growth is likely to be observed only under conditions where space available for re-use is too little to accommodate the new records or record versions...
To reiterate: deleting records or dropping tables does NOT reduce the size of the database file.
./heLen