Subject Re: [firebird-support] FB 1.5 RC6 linear slowdown
Author Ann Harrison
Rajesh Punjabi wrote:

> We have tested this on Win2k and XP and the degradation is similar on
>both. I havent had the bandwidth to test on Linux yet.
What do your performance monitors show as the cause of the problem?
Memory? Page faults? CPU? I/O?

A few things cause Firebird to slow down progressively.

One is deleting rows in a table that has an index with a large number of
duplicates. You mention foreign keys. If the parent table is very
small and the child table very large, Firebird creates an index on the
child table that has one entry for each child row and one distinct value
for each parent row. Gstat -a will (eventually) report the number of
entries and the number of duplicates for each index. If you have
indexes with > 30K duplicates, dropping the constraints that create
those indexes may help.

Another, paradoxically, is that you've allocated too much cache - too
many page buffers. Unless you've specifically adjusted page buffers,
you're OK. If you have adjusted page buffers either with gfix or the
configuration file, cut back on the number of buffers to 5K or less.

Another is garbage collection. Garbage collection becomes a problem in
two ways. The first and most common is that a long-running transaction
blocks garbage collection, causing pages to overflow with old versions
of rows and eventually damaging the locality of references to data.
That's not your problem. The second is that under load, in SuperServer,
the garbage collect thread can trail significantly behind the working
threads, with a similar result. Firebird 1.5 RC6 has less of a problem
in this area than other released versions of Firebird, but has some
problem in that area. Before you abandon Firebird, you might see if a
different way of handling the garbage collect thread helps.

Finally, someone else has already asked, but what are you measuring when
you measure locks? Our internal lock manager? Something else?