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
