Hernando Duque wrote:

> I've working around tuning up a database speed and found that gstat
> -h shows valuable information about transactions. Also that gfix
> -sweep removes garbage from the database.

Sweep (whether automatic or manual) removes garbage that is able to be
removed.

> I understood that when Next transaction grows too much from Oldest
> active transaction, "sweeping" the database makes this difference become smaller.

No, the 'Next' transaction is not relevant. It is simply the ID that
will be assigned to the next transaction that will be started. Of
course, gstat only provides you with a snapshot of the state of
transactions, as read from the transaction inventory page (TIP) when
gstat was run.

> But this is not the case with my database.

It would not be the case with any database that was being used, except
perhaps right after a restore from a gbak backup. In a newly restored
database, transaction id numbers start from 1.

> What am I doing wrong?

Let's look at your gstat -h results and decide whether you are
actually doing anything wrong. ;-)

> this is the gstat -h from the database:
> *************************************************

> Database "d:\med6\ibdata\sistmed6.fdb"
> Gstat execution time Mon Jul 01 10:39:55 2019

> Database header page information:
> Flags 0
> Checksum  ! ; 12345
> Generation 236628301
> Page size 4096
> ODS version 11.2
> Oldest transaction 236413964

This is the "oldest interesting transaction". a.k.a., OIT. An
"interesting transaction" is one that, although already committed or
rolled back, involves records that are still being actively worked on
by other transactions. As long as a transaction remains
"interesting", the records associated with it cannot be
garbage-collected.

> Oldest active 236413965

a.k.a. OAT, this is the oldest transaction that was still active when
gstat -h was run. An "active" transaction is one that has not yet
been committed or rolled back.

> Oldest snapshot 236413965

This is the value of the OAT the last time garbage collection (GC)
happened. On successive runs of gstat -h, the difference between it
and the OIT (known as "the gap") is an indicator of how effectively
the transactions are being managed by your applications. The bigger
the gap, the more likely it is that oneor more long-running
transactions are inhibiting GC.

> Next transaction 236627938
> Bumped transaction 1
> Sequence number 0
> Next attachment ID 31194
> ! Implementation ID 16
> Shadow count 0
> Page buffers 0
> Next header page 0
> Database dialect 3
> Creation date Mar 24, 2018 10:27:14
> Attributes force write

> Variable header data:
> Sweep interval: 20000

The sweep interval determines how big the "gap" should be in order for
the engine to register that an automatic sweep is needed. With this
(the default) configuration, the need for the auto-sweep will be
registered when the gap hits 20,000. Auto-sweeping is done by a
worker thread and will run when the engine detects a suitable lull in
operations. In a well-tempered database (as yours appears to be), an
autosweep will never occur because that gap simply never reaches that
size.

> *END*
> *************************************************

Your numbers, as shown here, look fine, especially considering you
have not done a restore for 15 months. ;-)

BTW, sweeping is not garbage collection. It is a process that marks
old record versions for GC, which will be carried out subsequently by
a GC worker thread (on Superserver) or by cooperative GC, or both,
depending on how your GCPolicy is set up in firebird.conf. GC happens
automatically, one way or another. A gbak backup also causes
cooperative GC, unless you run gbak with the -[no_]g[arbage_collect]
switch.

Helen


---
This email has been checked for viruses by AVG.
https://www.avg.com