| Subject | RE: [firebird-support] Database file size |
|---|---|
| Author | mahendra.nepali@iflexsolutions.com |
| Post date | 2006-04-25T15:42:31Z |
Hi Alexandre,
Thanks for the immediate reply. But there is one more thing I want to
ask. As said that Firebird will utilize the deleted (unused) space for
storing other data. But please correct me, is this table specific or is
it database specific. I mean does Firebird utilize the deleted disk
space of one table, for inserts or updates into another table. To
elaborate this consider the following process
1) Delete from table A -> this deletes say 40 MB of data. Hence I have
40MB of unused space.
2) Some Inserts into Table B -> this inserts 20 MB of data but into
Table B. Will this utilize 20MB of the unused 40 MB?
Regards,
Mahendra Nepali
Desk: +91-22-67184586
Mobile: +919820191887
Email: mahendra.nepali@...
-----Original Message-----
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Alexandre Benson
Smith
Sent: Tuesday, April 25, 2006 8:15 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Database file size
Importance: Low
mahendra.nepali@... wrote:
This is a FAQ, and has been answered a lot of times in this list, please
check the archives for a full coverage of the topic. I will be brief in
my explanation.
The database size is never reduced when one deletes records, the pages
that hold those records are marked as "available" so when new space is
needed there is no need to ask the OS for more space, it just uses the
already allocated and available space.
So if you insert 40MB of data in temp table, then transfer it to the
"official" table, and delete the records from the temp table, your
database you grow around 80 MB (40 MB for the temp, 40MB for the
official) then you delete all the records from the temp table your
database will not shrink in 40MB, but next time you insert 40MB in your
temp table again, the unused space will be used, so your database will
grow "nothing", and when you insert on the official table it will grow
around 40MB.
In the practice the numbers will vary of course, but rougly the only
increase in space will be the size of the new uploaded records not twice
as that.
see you !
--
Alexandre Benson Smith
Development
THOR Software e Comercial Ltda
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Visit http://firebird.sourceforge.net and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !
Also search the knowledgebases at http://www.ibphoenix.com
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Yahoo! Groups Links
DISCLAIMER:
This message contains privileged and confidential information and is intended only for the individual named.If you are not the intended recipient you should not disseminate,distribute,store,print, copy or deliver this message.Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system.E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted,corrupted,lost,destroyed,arrive late or incomplete or contain viruses.The sender therefore does not accept liability for any errors or omissions in the contents of this message which arise as a result of e-mail transmission. If verification is required please request a hard-copy version.
Thanks for the immediate reply. But there is one more thing I want to
ask. As said that Firebird will utilize the deleted (unused) space for
storing other data. But please correct me, is this table specific or is
it database specific. I mean does Firebird utilize the deleted disk
space of one table, for inserts or updates into another table. To
elaborate this consider the following process
1) Delete from table A -> this deletes say 40 MB of data. Hence I have
40MB of unused space.
2) Some Inserts into Table B -> this inserts 20 MB of data but into
Table B. Will this utilize 20MB of the unused 40 MB?
Regards,
Mahendra Nepali
Desk: +91-22-67184586
Mobile: +919820191887
Email: mahendra.nepali@...
-----Original Message-----
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Alexandre Benson
Smith
Sent: Tuesday, April 25, 2006 8:15 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Database file size
Importance: Low
mahendra.nepali@... wrote:
> Hi,application.
>
> I am facing an issue with the size of the firebird database file.
>
> I run a data upload process that loads data from series of XML files
> (around 70 GB+ data) into Firebird 1.5 Database via a .net
>size.
> The process takes atleast 10+days to complete but when I check the
> database file size, it is 5 times greater than the total xml file
>receive
> To speed up the process I load lots of data into temp tables and then
> delete the records from them for each xml file I load. However I think
> the delete doesn't shrink the database size.
>
> Then I tried backup and restore, where the database file size got
> reduced drastically.
>
>
>
> Can someone please let me know how I can reduce the database size when
> the delete is fired? This is because my application is going to
> daily data updates of minimum 40 MB and the same process is going todown
> run. But that could cause concerns as the database size may exceed the
> harddisk size and every time a backup and restore will have a large
> time on an online application.Hi Mahendra,
>
>
>
> Regards,
>
> Mahendra Nepali
>
This is a FAQ, and has been answered a lot of times in this list, please
check the archives for a full coverage of the topic. I will be brief in
my explanation.
The database size is never reduced when one deletes records, the pages
that hold those records are marked as "available" so when new space is
needed there is no need to ask the OS for more space, it just uses the
already allocated and available space.
So if you insert 40MB of data in temp table, then transfer it to the
"official" table, and delete the records from the temp table, your
database you grow around 80 MB (40 MB for the temp, 40MB for the
official) then you delete all the records from the temp table your
database will not shrink in 40MB, but next time you insert 40MB in your
temp table again, the unused space will be used, so your database will
grow "nothing", and when you insert on the official table it will grow
around 40MB.
In the practice the numbers will vary of course, but rougly the only
increase in space will be the size of the new uploaded records not twice
as that.
see you !
--
Alexandre Benson Smith
Development
THOR Software e Comercial Ltda
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Visit http://firebird.sourceforge.net and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !
Also search the knowledgebases at http://www.ibphoenix.com
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Yahoo! Groups Links
DISCLAIMER:
This message contains privileged and confidential information and is intended only for the individual named.If you are not the intended recipient you should not disseminate,distribute,store,print, copy or deliver this message.Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system.E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted,corrupted,lost,destroyed,arrive late or incomplete or contain viruses.The sender therefore does not accept liability for any errors or omissions in the contents of this message which arise as a result of e-mail transmission. If verification is required please request a hard-copy version.