Robert martin schrieb am 21.03.2012 um 15:18 (+1300):

> We have noticed performance issues on a machine running a web service
> connected to a FB 2.5 database. On a brand new machine (to rule out
> computer problems) everything worked fine for the first few hours,
> however after deleting a large number of records our performance
> issues surfaced. Instead of a process starting for each connection,
> running for a few seconds, then completing (closing) the transactions
> open and sit idle for a long period of time before processing.
>
> Alter doing a backup and restore the database changed from 1.9GB to
> 560MB. Restarting the web server and everything is working great
> again.

Just a guess, but you could read these replies from the archives and see
if that might be the problem you're facing, long-running transactions
preventing garbage from being collected, slowing down operations in the
process.


FB performance - Helen Borrie 06.01.06
http://groups.yahoo.com/group/firebird-support/message/70793

Then look closely at how transactions are being committed. The IBX
default for AutoCommit is to use CommitRetaining. This is handy for
Delphi client apps and disastrous for the server. It causes garbage
to build up steadily. You are seeing better performance on a clean
database and degrading performance as garbage builds up and doesn't
get cleared. Check the database header statistics over this period
of degradation (gstat -h). If you see a widening gap between the
Oldest Transaction and the Oldest Active then you'll know that poor
database hygiene is a significant part of your problem.


Gbak never end... - Svein Erling Tysvær 12.02.09
http://tech.groups.yahoo.com/group/firebird-support/message/100241

One very important part of Firebird is transactions. They should
never be running for a long time (well, 'never' is a strong word -
read-only, read-committed transactions may be kept running, as can
all transactions on read-only databases, however, your transactions
probably do not fall in these two categories when you have the kind
of symptoms you describe).

There may of course be other reasons for your problem, but it is
typical for us Delphi developers (particularly if we have desktop
database background) to not take proper care of our transactions and
observe that our programs become slower and slower until we finally
decide to stop the database. Then everything works fine for a while
again until a new slow-down cycle starts. If this fits your
observations, then you need to take a look at the transactions. If
your problem is not gradually worsening, but that the program seems
to work perfectly until suddenly coming to a halt and that there is
no large gap in the statistics, I'd say there is probably some other
reason for your problem.


fb 2.1 on Windows: how to use more RAM? - Svein Erling Tysvær 28.08.10
http://tech.groups.yahoo.com/group/firebird-support/message/109741

Databases that respond quick when they're started, and then gradually
slow down until almost coming to a halt, are a typical sign of poor
transaction handling in one or more applications. The gap between
oldest transaction and next transaction is often the 'proof'.


Multi-Version Consistent Read Question - Ann Harrison 13.12.11
http://tech.groups.yahoo.com/group/firebird-support/message/116063

Firebird keeps information about each running transaction, including
the oldest transaction that was running when the transaction started.
When choosing record versions to remove, Firebird compares the
transaction identifier in the record version header with the "oldest
of the oldest" - i.e. the oldest transaction that was running when the
oldest transaction now running started - and keeps one version older
than that.

If the oldest transaction running is 200 and the oldest transaction
running when it started was 175 and the chain of record versions goes
199, 176, 175, 174, 173, 140, 123, Firebird can remove the versions
created by 173, 140, and 123. The newer versions will all stay until
transaction 200 exits and the next "oldest of the oldest" is higher
than 175.


If that's not your problem, then I'd say it's a nice collection of
useful quotes nonetheless. :)

Michael

> My suspicion is that this is related to garbage collection. Does this
> sound likely?
>
> We are using the FB 2.5, superclasic with default garbage collection
> settings. What changes would people recommend?
>
> I am considering turning off garbage collection and scheduling it to be
> manually run late at night. This would work fine for this system but
> may not suit other users.
>
> Machine is Win7-64 Quad core I7 with 8GB RAM running Apache.