Subject Re: [firebird-support] Our firebird database performance has come to a halt
Author Helen Borrie
At 03:52 AM 15/12/2005 +0000, you wrote:
>I work for a company that uses Firebird for its main database. On
>Tuesday morning the performance just came to a halt. Queries that were
>taking 10-15 ms are now taking 10-15 seconds. We have been trying to
>find anything glaring by going through logs and haven't been able to
>find anything.
>The firebird server process is constantly taking up 100% of the
>processor. Before this slow down it would peak to 100%, but rarely. We
>have tried restarting the server, restarting the firebird service,
>reinstalling firebird server, backup & restore the database,
>increasing page size, doing sweeps and optimizing indexes. Nothing has
>made a dent.
>The database is relatively large (around 3.1gb) and around five tables
>have over a million rows (one has up to 8.5 million). So we are going
>to archive some of that data and try the backup & restore again.
>However, that process takes about 9 hours total, when it used to take
>about 15 minutes to back it up (not sure how long restore used to take).
>One thing we did notice was that as soon as we turned off the database
>server the processor went down to 1 - 2% like you would expect, but as
>soon as you turned the database server back on, it would jump directly
>to 100%. I know we don't have that many users that should make it do
>that, especially late at night.
>So, if anyone can shed some light on our situation we would greatly
>appreciate it. We are almost out of ideas. Thanks again.

Well, something is wrong. :-)

How about starting this triage by telling us
1. Which server version
2. Which model (superserver/classic)
3. Which OS and hardware platform (includling CPU setup)
4. Whether hyperthreading is active on the server host
5. What you are using for the application environment
6. Which platform the apps are running on
7. Whether the client library versions match the server version

And show us the output of gstat -h.taken when the performance seems to slow

Also say whether you are using the -g switch for your backup and whether
you are restoring with gbak -r or gbak -c.