Subject Re: [firebird-support] fbserver process goes to 100%
Author Helen Borrie
At 04:00 PM 15/11/2004 +1100, you wrote:

>I have a number of shops using firebird for the local transaction
>database. The shop machines are running Windows XP Pro with SP2. At a
>number of times during the day the fbserver process goes to 100% CPU and
>the machine slows to a crawl. This is happening at different times on a
>number of machines and continues for 10 minutes or more.

The most likely reason for this is likely to be one of two things (or even
both of them):

1) Garbage collection
2) Sweeping

>Restarting the fbserver process fixes the problem for a while.

If it is GC or sweeping that is causing these slowdowns, you're going to
make the problem worse by interrupting GC or sweep.

>Has anyone else experienced this?

Well, GC should happen as a background thread in the normal course of
events. A particular user would notice maybe a half-second delay in
response at some point. If you are getting GC operations that are slowing
everything to a crawl, that suggests you have some problems in your
application code to do with long-running transactions and/or large bulk
updates and deletes that don't get hard-committed. GC will get to a point
where it can't keep up with the levels of garbage that are accumulating -
it's just taking a long time to find garbage that it is actually able to
collect, because so much is hanging around in the system, stuck because of
these long-running or improperly completed transactions.

If in fact it is sweep kicking in because the sweep interval threshold is
being reached frequently, then the root cause would be similar. Sweep does
more stuff than GC does. A 10-minute sweep wouldn't be too surprising in a
garbage-prone system.

>Is there a DB maintenance regime that I should be employing?

On the face of it, I'd want to be eyeballing the transaction stats
regularly and I'd be scheduling a daily manual sweep as part of the
housekeeping. I'll also be asking the application programmer to find and
fix the problem that's causing this.

>Are there auto jobs that run that could do this?

You can run a manual sweep from a daemon script (batch file on Windows) -
typically, have the script perform a sweep and then do the daily backup
using the -g switch (for "no garbage collection", as GC isn't needed if you
do sweep first...) Obviously, schedule it for a quiet time.