Hi Neil,

> We are explicitly starting and committing our own transactions rather than
> relying on any auto-commit behaviour.
>
> We are running Classic (sorry I should have told you that already).
>
> I'll get our devs to checkout the MON$ tables and see what additional info
> can be gleaned from them.
>
> A current gstat -h output is this below, we experienced the slowdown a
> little while ago tonight.
>
> Database header page information:
> Flags 0
> Checksum 12345
> Generation 2240486
> Page size 8192
> ODS version 11.2
> Oldest transaction 1616642
> Oldest active 2143185
> Oldest snapshot 2143185
> Next transaction 2168081
> Bumped transaction 1
> Sequence number 0
> Next attachment ID 72398
> Implementation ID 26
> Shadow count 0
> Page buffers 0
> Next header page 0
> Database dialect 3
> Creation date Mar 26, 2016 10:35:08
> Attributes force write
>
> Variable header data:
> Sweep interval: 0
> *END*

The oldest transaction (limbo or a larger transaction got rolled back)
is way behind compared to oldest active and oldest snapshot. This can
cause performance issues, because upon starting a new transaction, the
transaction holds a private copy of the so-called TIP (transaction
inventory page), which gets bigger and bigger with an increasing gap of
the transaction counters and out-dated record versions won't get garbage
collected as well.

Your sweep task in the night does not seem to be able to do its job,
because otherwise the oldest transaction should move forward. Ideally
you schedule the sweep in a maintenance window ideally with zero
connections or at least low load.

Another reason why the oldest transaction can get stuck is a (failed)
distributed transaction (aka 2PC). This needs manual intervention
through gfix to resolve such a transaction either as committed or rolled
back. Any chance to you are using something which spans a single
transaction across several databases? E.g. replication comes to my mind.



--
With regards,
Thomas Steinmaurer
http://www.upscene.com/

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.