At 08:24 PM 14/09/2005 +0000, you wrote:

>In message #64294, Ann W. Harrison wrote:
>
> >Gstat counts all records in the database, including those that are
> >marked as deleted but have not yet been removed. Records that are
> >marked as deleted are not actually removed until after the end of all
> >transactions that started before the delete was committed.
> >
> >Even when that condition is meet, the record is only garbage
> >collected when some transaction tries to read it, so gstat may be
> >counting records that are awaiting garbage collection.
>
>Can anyone help explain the "Even when this condition ..." part?
>Why would my application ever try to read a record it has deleted?

This is a multi-user database engine. Just because a transaction in one
application (or application instance) has finished with the deleted record,
it doesn't mean that all are. Another transaction might still have access
to the old record version; no old record version will be garbage collected
until all transactions that were interested in the objects have either
committed or rolled back.

When this condition is finally met, the garbage indications will be kicked
off next time a transaction selects from the table. This scenario is
sometimes referred to as "cooperative garbage collection".

>Because, I have a similar problem where the database file grows
>constantly. The application does a lot of record deletes. I'm
>using the native Firebird C interfaces in my application.
>
>I "fixed" the application by adding an SQL query after every 100
>deletions. A simple query "select * from mytable" seems to do the
>trick.

It does; and it's quite a good trick when you have an application that's
frequently doing bulk deletes on a table that doesn't get selected from
otherwise.

>Although I have a workaround, I'd like to understand this issue
>better, and I wonder if there is a better way?

Since I know that Ann is tied up right now, I'll try to explain for her
(but keep watching the thread next week as she will no doubt hop in then!)

The "cooperative garbage collection" that Ann described occurs on both
Classic and Superserver. However, Superserver also runs a separate garbage
collection ("GC") monitor thread in background. When the SS engine gets
the appropriate signal from this monitor thread, it starts a GC thread and
does (for all tables) what the next SELECT does for individual
tables. Classic also monitors the garbage situation but it doesn't have a
GC thread, since a Classic process isn't a thread of a parent server
process, but a self-contained process that doesn't spawn threads of an
overall server process.

>Here's gstat output after a test run where the database grew through
>out the test cycle. When I run the same test with the "fixed"
>application, the database file grows to a fixed size and stays
>there.
>
>Database header page information:
> Flags 0
> Checksum 12345
> Generation 50041
> Page size 4096
> ODS version 10.0
> Oldest transaction 50036
> Oldest active 50037
> Oldest snapshot 50037
> Next transaction 50038

That's a pretty healthy-looking set of statistics. There is no "gap"
between the Oldest Transaction ( = what we refer to as "the oldest
interesting transaction", or OIT) and the Oldest Active (or OAT). (All
active transactions are interesting; but not all interesting transactions
are active.)

Building up big gaps between these two causes performance to degrade. The
Oldest Snapshot tells you the number of the transaction where the next
garbage collection will start, if that transaction is not interesting.

A big and growing gap between the OAT and the OIT is usually a sign of a
system where applications are keeping read-write transactions running for
long periods. This is a problem and it is totally curable.

If there is a big gap between the OAT and the Next Transaction, it could be
a further sign that the applications are not being careful about ending
read-write transactions (or, indeed, read-only transaction that have been
set to run in concurrency isolation); so it's related to the other "gap"
problem.

As for growth in the database file size: the database file never gets
smaller. The engine does not return space to the filesystem. What it does
is to make the space available for re-use. If a whole page is left free
after GC, that page just goes into a general pool of usable
pages; otherwise, a page containing both data and free space is refilled
by future insert and update operations on the table or index that it
belongs to.

Space *does* get returned to the filesystem if you gbak the database,
recreate it from the gbak file and continue with the recreated database.

Hence, you're going to observe a lot more growth at the beginning of the
life of the database file - or upon recommencing operations on a restored
database - than later in the life of the file.

There are also ways to housekeep the database file through direct or
indirect human intervention. You can run an unconditional sweep (using the
gfix utility either from the command line or through the Services
API); and gbak -b also performs garbage collection unless you explicitly
tell it not to (by including the -g switch).

hth until Ann comes back...

./heLen