Subject Re: [firebird-support] Re: Update: CPU Pegged on Firebird v2
Author Ann W. Harrison
Let me clarify the garbage collection issue slightly.

Every transaction that touches a record causes it to be
checked for garbage collection unless the transaction was
started with the option that turns off garbage collection.
That option is not the default and is normally used only
by gbak and only when the -g switch is applied.

In classic in all versions, the transaction that reads
the record performs the garbage collection immediately.
In some cases that is "unfair" because a transaction that
does no write activities itself can be slowed down by
writes it must do as part of garbage collection. Since
old versions have to be removed from data pages and indexes,
the cost can be considerable.

In SuperServer starting with InterBase 6, when a transaction
finds a record with garbage - either unnecessary back versions,
a change that rolled back, or a deleted record - it puts that
record on a list. A separate thread monitors that list and
removes the unneeded versions and their index entries.

Sounds great. Unfortunately, under load, the garbage collect
thread gets behind. The further behind it gets, the more
expensive garbage collection becomes. When the record and
its back versions are on the same page, removing the back
versions is easy. When the page fills up and back versions
have to be put on different pages, both creating and removing
back versions requires writing two pages. Eventually, the
garbage collect thread appears to get so far behind that it
cannot do useful work in the time slice allotted to it.

In V2, by default, cheap garbage collection - garbage
collection when all record versions are on the same page -
is done immediately by the current transaction. Only the
expensive, multi-page case is deferred to the garbage
collect thread. Using the configuration file, you can
specify either the previous behavior - where all garbage
collection is done by a different thread - or the classic
behavior where all garbage collection is done by the
current thread.

Turning off sweep does not turn off the SuperServer garbage
collect thread. Sweep does two things. It reads every record
in the database, causing a full garbage collect pass, and it
can increase the value of the "oldest interesting transaction".

The oldest interesting transaction is the oldest transaction
that is active, in limbo, or rolled back. In the first two
cases, it can't be changed. However, a rolled back transaction
can be changed to committed once all its changes are
removed from the databases.