Subject Re: Problem - growing DB size
Author Svein Erling Tysvær
25000 or 75000 is not a 'massive' amount. I normally work with
databases containing between 1 and 15 million records, and that isn't
even enough to be a big database in Firebird terms.

There is no way to turn off Firebirds multi generational architecture
(keeping several versions of each record), it is a fundamental part of
the engine. What you should always try to do, is to not have
long-running transactions (i.e. don't start a transaction and wait
several hours before committing). Your oldest active transaction is
528 and your next is 703, that means that inserts/updates/deletes from
about 170 transactions has to be kept. Now, there is no reason to
worry (unless every transaction updates lots of records, and you
update considerably less than 10000 records per transaction on
average), if you committed or rolled back the oldest active
transaction so that the number increased to e.g. 628, Firebird can
reuse the space used by record versions from the 100 transactions that
are now obsolete.

That you have 25000 records and 75000 old versions, does not mean that
the number of old versions neccessarily will be higher when you have
2500000 records in your database (though you must take care of your
transactions). Your indexes also seems fine.


--- In, "mysha_cz" wrote:
> > > Please can you help me?
> > > I have this problem. My DB (running on FB 1.5, Superserver) is
> > > growing too fast.
> > > The problem is in massive update in records, which is necessary
> > > to do. When I look at table statistics, I see a lot of old
> > > versions of records. For example records are 25000, old versions
> > > 75000. I don't know why there are still records of all
> > > generations. I would like to know: exist some parameter of
> > > server, or exist some transaction setting which reserve only one
> > > old generation of records?
> Oldest transaction 527
> Oldest active 528
> Oldest snapshot 528
> Next transaction 703
> 2) table part
> Index INDEXRIDES (0)
> Depth: 2, leaf buckets: 47, nodes: 27386
> Average data length: 0.00, total dup: 23223, max dup: 305
> Index RDB$PRIMARY2 (1)
> Depth: 3, leaf buckets: 313, nodes: 27386
> Average data length: 39.00, total dup: 0, max dup: 0