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]
