--- In firebird-support@yahoogroups.com, Walter Ogston <ogstoncw@c...>
wrote:

>
> Dear Friends,
>
> Firebird 1.5.2.4731 superserver on Windows 2000 5.00.2195 Service

Pack 4

> Intel x86 ~2,400 MHz
>
> I have just heard from a customer that their server seems to be running
> slowly - my application is generating a big report at about 30 sec per
> page. The client machine does not seem to be overworked, so I have

been

> looking at the server.
>
> The client box, on which the report generation software is running,

has a

> couple of percent cpu activity spiking to about 50%, does not appear

to be

> overoaded.
> The server cpu is running steadily at about 30%, memory usage at around
> 740M, total physical memory 1GB holding steady.
>
> I just now ran gstat -h and got back:
>
> C:\Firebird_1_5\bin>gstat -h <path>\scmain.fdb -user SYSDBA

-password ******

>
> Database "<path>\scmain.fdb"
>
> Database header page information:
> Flags 0
> Checksum 12345
> Generation 746030
> Page size 8192
> ODS version 10.1
> Oldest transaction 729287
> Oldest active 746023
> Oldest snapshot 746023
> Next transaction 746024
> Bumped transaction 1
> Sequence number 0
> Next attachment ID 0
> Implementation ID 16
> Shadow count 0
> Page buffers 0
> Next header page 0
> Database dialect 3
> Creation date Apr 18, 2005 17:00:05
> Attributes force write
>
> Variable header data:
> Sweep interval: 20000
> *END*
>
> The Oldest Transaction value looks suspicious to me. I have been

logging

> gstat -h output at daily backup, and I see from this that "Oldest
> transaction 729287" first appeared after
> Mon 12/19/2005 21:30:00.09, when gstat reported
> Oldest transaction 729285
> Oldest active 729286
> Oldest snapshot 729286
> Next transaction 729291
>
> I am a newbie at running a server like this, so I may be asking a

faq. Is

> this an instance of a "stuck" transaction that I have read about on

this

> list?

The gap is about 15 000, probably not hugely dangerous but it is on
the high side.

http://www.ibphoenix.com/main.nfs?a=ibphoenix&l=;KNOWLEDGEBASE;ID='128'

Firebird is a MGA database. That means every transaction gets a lovely
isolated view of the database. The downside is that the database must
keep track of old record versions after they have been deleted or
updated etc. If let loose, this uses excessive disk space and clogs
the garbage collection to the point where performance is compromised.

Transactions are initiated by client programs. As long as a client
program is running the transaction, then Firebird must maintain the
back versions, so the problem is probably going to be a client program
that has gone AWOL.

The usual culprit is the use of Commit Retaining. Particularly the
simple data access interfaces use commit retaining a lot in autocommit
type operations. Commit Retaining (aka soft commit) does not free its
requirement of the old back versions, so a normal Commit still must be
used from time to time to let Firebird know that you are no longer
interested in the back versions and it can do the required cleanup.

Or is something else going on - do I need to provide more information?

> How does such a thing come about, and what can I do to fix the

problem now,

> and to prevent it happening in the future?
>

Of course this is just your guess at the problem, it could be a
missing index is causing table scans which is crippling performance.
There is unfortunately no built in performance analysis tools to find
the culprit, but you should check that all queries have supporting
indices if they are selecting from large tables (or tables that will
be large one day).

If possible, you could restart the service which will end all
connections (notify everyone first obviously). Then run a sweep to do
some cleanup and see whether this fixes your problem. If so, you will
need to identify the AWOL client and resolve it or it will just happen
again when the garbage builds up.

Adam