Hi Neil, > We are explicitly starting and committing our own transactions rather than > relying on any auto-commit behaviour. > > We are running Classic (sorry I should have told you that already). > > I'll get our devs to checkout the MON$ tables and see what additional info > can be gleaned from them. > > A current gstat -h output is this below, we experienced the slowdown a > little while ago tonight. > > Database header page information: > Flags 0 > Checksum 12345 > Generation 2240486 > Page size 8192 > ODS version 11.2 > Oldest transaction 1616642 > Oldest active 2143185 > Oldest snapshot 2143185 > Next transaction 2168081 > Bumped transaction 1 > Sequence number 0 > Next attachment ID 72398 > Implementation ID 26 > Shadow count 0 > Page buffers 0 > Next header page 0 > Database dialect 3 > Creation date Mar 26, 2016 10:35:08 > Attributes force write > > Variable header data: > Sweep interval: 0 > *END*
The oldest transaction (limbo or a larger transaction got rolled back) is way behind compared to oldest active and oldest snapshot. This can cause performance issues, because upon starting a new transaction, the transaction holds a private copy of the so-called TIP (transaction inventory page), which gets bigger and bigger with an increasing gap of the transaction counters and out-dated record versions won't get garbage collected as well. Your sweep task in the night does not seem to be able to do its job, because otherwise the oldest transaction should move forward. Ideally you schedule the sweep in a maintenance window ideally with zero connections or at least low load. Another reason why the oldest transaction can get stuck is a (failed) distributed transaction (aka 2PC). This needs manual intervention through gfix to resolve such a transaction either as committed or rolled back. Any chance to you are using something which spans a single transaction across several databases? E.g. replication comes to my mind. -- 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: [email protected] [email protected] <*> To unsubscribe from this group, send an email to: [email protected] <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
