Subject Re: GDB file grows
Author tmiravet
--- In, Helen Borrie <helebor@t...>
> At 12:12 PM 24/09/2004 +0200, you wrote:
> >Hi all,
> >
> >We have a firebird database 1.0.3 Version WI-V6.2.972 installed
since some
> >months. Yesterday we detected that the .gdb file was huge -18Gb-.
> >doing a backup of such huge database with gbak, the .gbk file was
about 20
> >Mb -normal size-. And restoring the .gbk file we got a normal
size .gdb
> >file -about 22Mb-.
> >
> >So, we delete the huge file and start working with the restore
one. After
> >a while the size was 30Mb, after another while 50Mb, ... it seems
> >something makes the .gdb file to grow, and it is not because such
> >of data is stored in the database.
> >
> >When we did again the gbak to the, at that point, 150M gdb file,
we got
> >again a normal size .gbk file and the restored one had also normal
> >
> >Has anyone experience something like this? Any help is really
> My guess is that you have this combination of conditions:
> read-write transactions running that never finish, the sweep
interval set
> to 0 (auto-sweeping is disabled) and you never do manual sweeps.
The first
> is a problem with your application code; the other two are caused
> administrative ignorance and neglect, but the three are intertwined.
> You can switch off auto-sweeping if your applications manage
> well (since auto-sweeping is unlikely to be triggered off where the
> application code takes proper care of transactions). If you switch
> sweeping, you must do manual sweeps. With good transaction
management, the
> need to sweep will be perhaps monthly, quarterly or even yearly.
How often
> it's needed in a well-behaving database would depend on how much
> you do. Apart from restores, sweeping is the only way to free up
> that was occupied by deleted records.
> With poor transaction management (which your symptoms suggest) you
> need to sweep several times a week. But you really need to hunt
out those
> perpetual transactions and fix them so that you avoid getting the
> into this state.
> Another contributor to your troubles may be a page size that is too
> small. If you inherited a database that was created with the IB 5
or 6
> defaults, or an early beta of Firebird 1, the default page size of
1 Kb is
> pretty unrealistic and will cause to server to waste space and to
> blocks of page storage too often.
> You can find out quite a lot about the state of the database by
running a
> gstat -h on it. Pipe the output to a file and paste it here so we
can help
> to work out what's going on it.
> ./heLen

Here is the result of gstat. I did it this morning. The size of .gdb
file was 2GB, last friday was only 20M


Database "grader.gdb"

Database header page information:
Flags 0
Checksum 12345
Generation 1151
Page size 4096
ODS version 10.0
Oldest transaction 1079
Oldest active 1080
Oldest snapshot 1042
Next transaction 1145
Bumped transaction 1
Sequence number 0
Next attachment ID 0
Implementation ID 16
Shadow count 0
Page buffers 0
Next header page 0
Database dialect 3
Creation date Sep 25, 2004 8:26:23

Variable header data:
Sweep interval: 20000


In your mail you mentioned the need of sweep to free up space
occupied by deleted record. Well, some days before the problem was
detected, there was a deletion of around 67000 record in a table.
Usually no deletions are done in the database, they are done only to
clean historical logs. I think, that was the cause of the problem.
The system is running about 1 year ago and never detected something
similar before. So, this morning I did a manually sweep, it took
around 4 minutes -in a test database was only a few seconds- and
after that, it seemed that the database was not growing.

Do you think I should change the sweep interval, or just keep that in
mind and do manually sweeps from time to time?

Thanks for your help,