--- In firebird-support@yahoogroups.com, "intellekta"
<uwe.cramer@i...> wrote:

>
> Hello,
> we have inserted and deleted ~ 30.000.000 records in a table.(buggy sp).

OK, so you have inserted 30 million records, committed, realised they
were wrong and so deleted them. Deleting records in Firebird does not
physically remove the record (at first anyway). It simply flags it as
being deleted. This is because older transactions may still be
interested in seeing the previous value(s) before the delete. The same
is true for updates. New transactions wont see the deleted records.
Eventually, your database has no more active transactions that could
possibly see the previous values of the deleted transaction. At this
time, garbage collection does its work and frees up the space for re-use.

> after that the client performance slows down to 1/10 and the memory
> usage in fbserver goes from ~200KB to ~800KB.

Firebird maintains an undo log (for a while anyway) so that if you
decide to rollback, less garbage is left lying around. The trade off
is that this log may grow significantly if your transaction lasts a
long time.

> sweep does not change this situation.

Possibly you still have an active transaction that is (or rather could
potentially be) interested in the previous value of the deleted
records. While this is the case, sweep can't do anything about it.

> the database filesize goes from ~14GB to ~22GB

This is normal. You added more data, Firebird needed more pages to
store the information on, the file size grows. File space is never
returned to the OS. Apart from being an enormous housekeeping job to
effectively defrag the pages, chances are you will need to request
disk space again, so why bother?

> the backup filessize after sweep is a little bit bigger as it was in
> the past.

That does not make sense. It should be the same size unless you are
backing up more information.

> backup takes ~5 times longer as before.

Try using the -g switch of gbak. This prevents the garbage collection
that occurs while the backup is running (a nice side effect of a
nightly backup). Restore to a new database and see if the restored
database still has these problems.

> any suggestions to resolve this, except complete restore from a backup?

Restarting the server process will abandon any active transactions,
which will allow garbage collection to work correctly.

> Thanks in advance
> Uwe Cramer
> additional conf infos:
> OS = W2K SP4
> HW = HP DL380 / 2GB / 2xXeon 3.0GHz
> FB = SS Mode
> firebird.conf without any change / all options with # comment
>

Looking at your specs, you may need to watch that the fbserver process
doesn't keep switching CPUs (which can happen with SS and dual CPU on
Windows machines). If this happens, set the CPUAffinity mask in the
firebird.conf to lock fbserver to a single CPU.

Adam