Subject RE: [firebird-support] over-sweeping??
Author Alan McDonald
> I've encountered such deployments: the vendor has shipped the database
> with the sweep interval set to 0 (because of misinterpreting the docs, in
> many cases);

This was also the standard thing to do during the IB5.5 deployment phase.
Many people needed to rush back and set it to 0 before the 20,000 was reched
otherwise they would loose the lot!

and with applications that simply prevent any reasonable
> garbage collection; where the only thing available to the users to fix
> the outcome the (frequent) freezes and crashes is backup and restore (and

getting the application to not be so rude about it's transaction management
is another way to prevent this behaviour. But must say I have never
experienced frequent freezes and crashes cuased by the server.

> it is *scary* how often this is just "Backup" and "Restore", no
> intermediate gbak -c).
> With a DBMS that runs sweetly and doesn't need a SYSDBA on site, it's
> tempting for developers to think *they* can treat the live database as a
> black box and ignore the dynamic effects of changes in database
> content. The real trick is do the right stuff in development so that the
> users can treat it as a black box (more or long as they
> press the
> right buttons on or near the right day)
> /hb

It's also worth noting, though, that the sweep operation will do slightly
different things if it finds that it has been started in exclusive mode i.e.
it can do some nice things with the transaction inventory. If it finds that
it is not in exclusive mode, these operations are not carried out thereby
reducing the overall effectiveness of the operation, and also, that if there
are many roll-back transactions, the db size is not reduced at all (unlike
with backup/restore).

Finally - sweep was invented during a period when hard disk space was small
and expensive (which it isn't now) and was set with a default of 20,000.
This default value alone implies very clearly that the default sweep action
was never meant to be a frequent event. Indeed, under good to reasonable
application conditions (i.e. transaction management) it was never meant to
fire at all between normal backup/restore cycles. I can say for sure that
not one of my IB/FB applications would ever reach this default even if I
left it for 2-3 years untouched. What does that say about how important the
sweep operation as a whole was ever designed to be? As you suggest (and I
push a little further), the sweep function was provided IMO, as a failsafe
back for poor application conditions and very low DBA maintenance attention.

Bottom line - IB or FB is so robust that it can withstand some pretty cruel
treatment, you don't have to back and restore very frequently and you
certainly don't IMO need to sweep every 24 hours. You CAN do this but there
is no need nor any immediate or long-term benefits in performance. e.g. if
Sweep has only one other connection (i.e. not exclusive), then the
performance drop for that poor person currently connected PLUS the reduced
effectiveness of the sweep operation under non-exclusive mode, suddenly
removes any benefit at all from doing the sweep in the first place.