Subject Re: [firebird-support] file size and transactions
Author Helen Borrie
Hello shg_sistemas@...,
Thursday, September 14, 2017, 2:10:23 AM, you wrote:

> Hello! I'm noticing a small increase of my database size. Now it
> has 566,992 KB. After a backup/restore the size goes to 533,640 KB.
> I don't have long running read/write transactions (well... I think)

There is nothing wrong with your transaction statistics.
The reason why the database "grows" is this:

A Firebird database is stored in one file. This file is made up of
"pages" - chunks of disk that the Fb engine keeps account of
individually. In your case, each page is 4Kb.

The engine is constantly performing maintenance on these pages - this
is called garbage collection and sweeping. GC and sweeping are
related to each other but they are not exactly the same. Between
them, they identify pages that no longer contain record versions that
are needed for anything, so they release these pages for re-use.
Thus, changing or deleting records does NOT reduce the size of the
database file.

A newly restored database has no spare pages waiting for re-use. That
is why the file has its "minimum" size immediately after a restore.
It has exactly the number of pages needed to store everything that was
backed up. Garbage pages are not backed up at all....which is why a
badly managed database can really only be properly cleaned up by a
full backup and restore.

At the same time as GC and sweeping are going on, the engine is
acquiring new pages (two at a time - in your case, 8 KB)) from the operating
system whenever it has insufficient empty pages available to store a
new record or record version from an insert or update. Old record
versions, from updates and deletes, stay on their pages until the
GC/sweep process ultimately frees them. When transactions are
not well managed, the housekeeping gets "stuck" on pages that the bad
management keeps in an "interesting" state. If the programmer has not
taken good care of committing transactions that are finished, the
engine has no choice but to keep those pages in an "interesting"
state. That is when you would see an increasingly large gap between
the oldest transaction (oldest interesting, or OIT) and the oldest
snapshot (OST). Then, you can query MON$TRANSACTIONS to try to
discover where the problems are. But your stats look fine.

Know, too, that the engine needs pages for other things besides data -
it maintains index pages, blob pages, etc. Once a page has been used
as one type of page (data or index or blob) it cannot be reused for
another type of storage. So some of your operations could be
consuming quite a lot of new pages and this is not an unhealthy sign,
either! ;-)