Firebird SuperServer running at 100% CPU?

This problem usually shows up when you have an UPDATE intensive database and the sweep process has to kick in periodically to clean up the old versions of records. This is also known as the garbage collection. When it starts running on the SuperServer, it takes most of the server's resources until it completes (This is due to be fixed in Firebird 3). The default sweep interval setting is 20000, meaning that it would kick in when the difference between the Oldest Interesting Transaction (OIT) and Oldest Active Transaction (OAT) becomes larger than that. Each transaction checks this gap when it starts and starts the sweep if interval is breached. Sweep interval is set on database basis, i.e. you can have a different setting for each database.

Cure to this problem is to disable the automatic sweep (by setting the interval to zero) and running the sweep in the off-line hours. This works if you don't have a 24/7 application. To set the sweep interval, you can use your administration tool (Database properties window in FlameRobin) or gfix:

gfix -sweep -user SYSDBA -password ***** myserver:/path/to/db.fdb

Sweep is automatically done by GBAK while backing up the database, unless you use -g option which disables it. If you run regular nightly backup, there is no need for a special sweep job.


Beside this, there is a known problem when you set the database cache to a very high value (high compared to total RAM available to the machine). When this happens, swap space starts to get used and the whole system slows down. See the related FAQ #333 for more details.