Subject Re: [firebird-support] Compacting the database from a App
Author Helen Borrie
At 05:59 PM 16/10/2010, Venus Software Operations wrote:
>On 16/10/2010 04:08, Helen Borrie wrote:
>> That part is true. The other part is that now you are working in a
>> transactional environment. Take very good care to complete
>> transactions in a timely way - otherwise you will build up a lot of
>> garbage that, in large quantities, will adversely affect impact
>> performance as time goes by.
>How can I detect this?

gstat -h will tell you some useful things about the state of garbage collection. If your client code is behaving correctly, you should not see enormous differences between the 'Oldest transaction', the 'Oldest Active transaction' and the 'Oldest snapshot'.

>I rely on a framework doing this, so I am not starting and stopping any transactions.

Not all frameworks are created equal with regard to completing transactions. What's more, if your framework provides an autocommit-style of transaction management, you should take steps to find out whether it uses COMMIT RETAINING instead of a hard COMMIT. Hard COMMIT = good, COMMIT RETAINING is a bad legacy from the old Borland world, that should be avoided.

Most driver frameworks for Firebird have their own forums or lists. That's where you should go for advice about what your framework does under the hood.

>If detected what would be the recommended action to get things back to normal.

Fix the code.

> If this is possible I would want to make it a part of regular housekeeping.

If you think you might be getting garbage buildup, keep regular statistics for a while. If you see the signs of too-large transaction gaps, then regularly putting the database into shutdown followed by a gbak -b or sweep in exclusive mode should bring it down temporarily.

It's not ideal to be having to shut out users, though. Nor is it great to be having users put up with regularly degrading performance. IMO it should never be regarded as a way to avoid improving the behaviour of applications.