| Subject | Re: [firebird-support] FireBird .FDB file size won't go down even after deleting a large number of rows |
|---|---|
| Author | Alexandre Benson Smith |
| Post date | 2005-11-29T21:23:44Z |
Hari Kolasani wrote:
I will do a brief answer, this question are FAQ, please do search in the
message history and you will find a lot of very detailed information.
FB aloocates pages, each page can hold only one kind of data at a time,
lets take an example of a data page.
Data page hold the record information, when you need a new page FB looks
in the already allocated pages to sse if there is any empty page, if
not, then asks the OS for a new page and uses that page.
When a page is empty is added toa list of free pages, any time that FB
needs a new page it first looks to see if there is free pages available.
Reusing already allocated disk space is far more cheap than ask to the
OS and release the empty pages back.
So your database file will grow, grow grow and never shrink. But take
care to note that it could grow fast in the initial data load processes
and after that saturaton period, the grow will be a function of the new
data inserted on the database, if you delete and insert data about the
same rate, the DB size tends to stall.
The only way to reduce a database size is doing a backup/restore cycle.
Please do read about garbage collector, and the multi generational
architecture that FB uses to understand how important is to keep the
transaction livetime as short as possible. This will answer a lot of
questions like:
I have a million record table, I do a count(*), why it so slow
I deleted all the million records, and I do a select count(*) on the
same table and it is still slow.
Come back when you get new problems and doubts :-)
Welcome aboard !
see you !
--
Alexandre Benson Smith
Development
THOR Software e Comercial Ltda
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br
>I have been using a FireBird database in my app and the databaseHi Hari,
>grew to several thousand rows and the .FDB file size went up to 600
>MB over a period of a few weeks.
>
>First of all I noticed that the application runs very slow (SQL
>queries) when the file size is big.
>
>So, I deleted all the rows in the database using an SQL DELETE
>statement via ODBC, the file size still remains at 600 MB even when
>when the database is empty.
>
>1. Is there anyway to bring the .FDB file size down?
>2. Is there anyway to tune the performance when the FDB file size is
>large?
>
>
>- HK
>
>
I will do a brief answer, this question are FAQ, please do search in the
message history and you will find a lot of very detailed information.
FB aloocates pages, each page can hold only one kind of data at a time,
lets take an example of a data page.
Data page hold the record information, when you need a new page FB looks
in the already allocated pages to sse if there is any empty page, if
not, then asks the OS for a new page and uses that page.
When a page is empty is added toa list of free pages, any time that FB
needs a new page it first looks to see if there is free pages available.
Reusing already allocated disk space is far more cheap than ask to the
OS and release the empty pages back.
So your database file will grow, grow grow and never shrink. But take
care to note that it could grow fast in the initial data load processes
and after that saturaton period, the grow will be a function of the new
data inserted on the database, if you delete and insert data about the
same rate, the DB size tends to stall.
The only way to reduce a database size is doing a backup/restore cycle.
Please do read about garbage collector, and the multi generational
architecture that FB uses to understand how important is to keep the
transaction livetime as short as possible. This will answer a lot of
questions like:
I have a million record table, I do a count(*), why it so slow
I deleted all the million records, and I do a select count(*) on the
same table and it is still slow.
Come back when you get new problems and doubts :-)
Welcome aboard !
see you !
--
Alexandre Benson Smith
Development
THOR Software e Comercial Ltda
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br