Neil, > We are investigating an issue with one of our systems where it gradually > slows down during the course of a day. > > We have our epos system that we write ourselves using Delphi XE2 and > the IBX components.
How does your transaction model/layer look like in your client application with IBX? If you are not starting an explicit transaction yourself, IBX is running in "auto commit" mode, wrapping each statement into it's own transaction context and commit the statement with a commit retaining, which basically preserves the physical transaction context and makes your transaction counters (OIT/OAT/OST) stuck. Any chance to run gstat -h when your experience such a response time drop? > We have a group of tables that are updated during the end of sale save > process. > > Typically this whole save process takes milliseconds, but on one > particular site we see it taking several seconds and occasionally taking > up to 35 seconds. > > We have done some digging into the behaviour of Firebird, we are > currently using v2.5, and it appears that Firebird needs to check the > entire chain of back versions when reading a record from the database. > Is this the cooperative Garbage Collection process running? If you are running Classic or SuperClassic architecture, then yes. In these architectures, each statement is also doing garbage collection in its execution context. > It seems like it would be logical for a lookup to only search back > records until it finds the record that is supposed to be visible to it, > but it appears to go all the way back. Visible from a transaction context. Beside the transaction ID, also the used isolation level needs to be taken into account. With using commit retaining behind the scenes, e.g. due to auto commit, you basically end up in accumulating back record versions. > This causes us a slowdown if there are a lot of back versions still > active, and flags up data as corrupted if something untoward has > happened to them, even if the current committed version of the record > seems to be valid. > > Can anyone confirm this behaviour and any way to work around it? To be sure, if the slowdown for a single statement is driven by cooperative garbage collection, you can use the Trace API and its detailed statistics output (something like "backouts", "purges", "expunges") per statement. The MON$RECORD_STATS monitoring table is another source for that information, if your statement is still available through MON$ tables. -- With regards, Thomas Steinmaurer http://www.upscene.com/ Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc. ------------------------------------ ------------------------------------ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ ------------------------------------ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
