Kevin,

You might want to read this...
https://www.ibphoenix.com/resources/documents/search/doc_67

Regards
Paul


-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com]
Sent: 03 April 2019 06:37
To: Firebird Support
Subject: [firebird-support] Transactions



Greetings,

I’m sure this has been asked before - is there a knowledge base somewhere?

I’m trying to get a handle on transactions from gstat and I just want to make sure I’m reading this correctly. One of my installs is having some issues, Firebird 1.56, Classic Server. I have been reading Helen’s book, Chapter 25 - Overview of Transactions.

My app (a large one) is written in Delphi, using Devart’s IBDAC components.

Also, my end users tend to End Task on my app if a report doesn’t pop up immediately. I’ve tried to educate them. So with Classic (or Superserver I’m guessing) when the user kills a client app, I’m assuming it leaves not only a connection to the database active (as seen in Task Manager on the server) but also potentially one or more transactions active.

It appears from the book that I should be concerned with the gap between the OIT and the OAT and making sure they both move ahead. So from the gstat figures below, my app needs a bunch of improvements in transaction handling which I’m diving into as we speak.

4/1/2019 - before backup / restore
Oldest transaction 1388 (OIT)
Oldest active 812976 (OAT)
Oldest snapshot 811580
Next transaction 905141
Note: Performance was horrible on this day and usually is the first day of the month.

4/1/2109 - after backup / restore:
Oldest transaction 1294
Oldest active 1295
Oldest snapshot 1295
Next transaction 1298
Note: This looks perfect to me.

4/2/2019 - around mid-day:
Oldest transaction 1297
Oldest active 1298
Oldest snapshot 1295
Next transaction 56071
Note: Not good. OIT and OAT not moving forward.

4/2/2019 - around end of day:
Oldest transaction 1297
Oldest active 1298
Oldest snapshot 1295
Next transaction 91583
Note: Even worse. OIT and OAT not moving forward.

4/2/2019 - after backup and sweep:
Oldest transaction 1626
Oldest active 91623
Oldest snapshot 91623
Next transaction 91631
Question: I would’ve thought performing a sweep would’ve not only done garbage collection but also dealt with rolled-back transactions and remove “stumps” of deleted records (from the book) increasing the OIT.

4/2/2019 - after a 2nd sweep:
Oldest transaction 91631
Oldest active 91623
Oldest snapshot 91623
Next transaction 91632
Note: Now this is interesting. This looks pretty perfect to me.
Questions: Why didn’t the first sweep give me this kind of result? And should I schedule two sweeps every night?

Thanks in advance for any help here!

Best Regards,
Kevin

Some more background:

Batch file that does backup / sweep every night:

del "e:\fb databases\lumber\lumber_bu.log"
gbak -b -user SYSDBA -password masterkey "127.0.0.1:e:\fb databases\lumber\lumber.fdb" "e:\fb databases\lumber\lumber.fbk" -v -y "e:\fb databases\lumber\lumber_bu.log"
gfix -sweep -user sysdba -password masterkey "127.0.0.1:e:\fb databases\lumber\lumber.fdb"

Sweep batch file I run interactively:
e:
cd "e:\program files (x86)\firebird15\bin\"
gfix -sweep -user sysdba -password masterkey "127.0.0.1:e:\fb databases\lumber\lumber.fdb"
pause

[Non-text portions of this message have been removed]