Subject Re: [firebird-support] Question about transactions
Author Helen Borrie
At 09:00 PM 3/12/2008, you wrote:

>i�ve a problem with out database.

I don't think you have a problem with your database....yet.

>The size of the file increase and increase. Here are the status:
>flags: 0
>checksum: 12345
>generation: 3311
>page size: 4096
>ods version: 11.1
>oldest transaction: 86
>oldest active: 87
>oldest snapshot: 87
>next transaction: 3303
>bumped transaction: 1
>sequence number: 0
>next attachement ID: 9
>implementation ID: 16
>shadow count: 0
>page puffers: 0
>next header page: 0
>database dialect: 3
>creation date: dec. 3, 2008, 10: 30:42
>attributes: force write
>variable header data:
>sweep interval: 20000
>The firebird.log containt the follow messages:
>DBSRV (Server) Wed Dec 03 10:24:46 2008
> INET/inet_error: read errno = 10054

That is just a network message..but it pre-dates the creation date of your database, anyway.

>I�m using firebird 2.1 superserver
>Some Clients has a connection to firebird (java netbeans). One Procedure
>does update one table 10 times in a second (state-table) Is this a problem?


>Please, can you help me, how can I make the database more stable? How can
>the database protect itself?

What instability do you observe? You have not described any.

>This problem I have on more firebird database (upper size in a few days)

A new (or newly restored) database will grow quickly until its size becomes "big enough" to accommodate both your committed data and the garbage that you keep there. When the DB engine needs more space for new records, or to write new versions of existing records, it will try to find enough space on existing pages. If it cannot find enough free space, it will ask the operating system for more pages. Each time it gets a new page your database file grows by 4 KB.

The database will settle into a steady growth pattern, once it has enough pages and free space to keep pace with the actual increase of data and your pattern of garbage collection. Of course, if you are writing applications that inhibit garbage collection, the database has no choice other than to keep growing. Space occupied by "interesting" garbage remains trapped and unavailable for re-use.

"Interesting" garbage is garbage from an older transaction that is still interesting to one or more newer transactions. It remains interesting until those newer transactions are committed.

We can see from your header statistics that you are building up a stack of active transactions, perhaps 2-3000 in an hour; but that is not a problem, since this is a very new database and these statistics were probably reported while several users were busy.

Keep an eye on the changes in the statistics: if the gap between the Oldest Active and the Oldest Snapshot does not change much over a long period, or if the gap between the Oldest and the Oldest Snapshot is going into many thousands, then you probably do have a garbage problem. The suggested monitoring tools would help to find the offending uncommitted transactions and could indicate places in your application code, where insufficient care is being take to commit work in a prompt and timely way.