| Subject | Re: [firebird-support] slow database ref/eDN8022297953 |
|---|---|
| Author | Svein Erling Tysvaer |
| Post date | 2008-06-13T20:42:03Z |
I'm sorry to say that I cannot give you an exact definition of the
difference between oldest transaction, oldest active and oldest snapshot
- I've heard it before, but would probably give a wrong answer if I
tried to answer, so I'll refrain from doing so.
The difference between these three and the next transaction means that
your database probably is filled by lots of garbage. The oldest active
transaction is a transaction that hasn't been committed yet, and the way
Firebird works, that means that all changes done since this transaction
was started has to be kept track of. So, if a record has been changed,
say 1000 times since transaction no. 1008, then you will have 1000
versions of this record in your database - each version may be visible
to different transactions. Likewise, if you delete a record, this record
still may be visible to transactions that started before the record was
deleted.
That is part of the beauty of Firebird - it makes it possible for you to
get a constant view of a database regardless of simultaneous changes.
However, it backfires if you do not take proper care of your
transactions - Firebird has to do lots of work to get the data you want
when it has to look through lots of record versions.
You have to change your programs so that no program keeps a transaction
open for a long time - well, read-only, read committed transactions may
be kept open since they do not prevent the oldest active transaction
from moving forward. When these counters gets (a lot) closer to next
transaction, you will have a lot less problems with a slow database, but
you may have quite some work to do before you get there.
Set
PS! Note that when I write 'oldest active transaction', I may confuse it
with 'oldest interesting transaction'. In general, if you keep both of
these close to 'next transaction', you will not run into too many problems.
dennis wrote:
difference between oldest transaction, oldest active and oldest snapshot
- I've heard it before, but would probably give a wrong answer if I
tried to answer, so I'll refrain from doing so.
The difference between these three and the next transaction means that
your database probably is filled by lots of garbage. The oldest active
transaction is a transaction that hasn't been committed yet, and the way
Firebird works, that means that all changes done since this transaction
was started has to be kept track of. So, if a record has been changed,
say 1000 times since transaction no. 1008, then you will have 1000
versions of this record in your database - each version may be visible
to different transactions. Likewise, if you delete a record, this record
still may be visible to transactions that started before the record was
deleted.
That is part of the beauty of Firebird - it makes it possible for you to
get a constant view of a database regardless of simultaneous changes.
However, it backfires if you do not take proper care of your
transactions - Firebird has to do lots of work to get the data you want
when it has to look through lots of record versions.
You have to change your programs so that no program keeps a transaction
open for a long time - well, read-only, read committed transactions may
be kept open since they do not prevent the oldest active transaction
from moving forward. When these counters gets (a lot) closer to next
transaction, you will have a lot less problems with a slow database, but
you may have quite some work to do before you get there.
Set
PS! Note that when I write 'oldest active transaction', I may confuse it
with 'oldest interesting transaction'. In general, if you keep both of
these close to 'next transaction', you will not run into too many problems.
dennis wrote:
> Also please explain this gstat's counters:
>
> Oldest transaction 1007
>
> Oldest active 1008
>
> Oldest snapshot 1008
>
> Next transaction 18504685
>
> What really means this extreme difference?
>
>
>
> Kind regards
>
> Dennis