>>> it's a disaster .... i disable the gfix -sweep every night and launch it >>> only when the difference between oldest transaction and next transaction >>> is> 20 000 but this become true only after 5 hours :( so at the end >>> everyday the difference between oldest transaction and next transaction >>> grow by more than 20 000 :( >>> >>> i can not run gfix everyday, it's took hours to finish :( >>> >>> what i can do ? >> >> Find the reason why you produce such a transaction gap within 5 hours. >> Is it just the OIT which is behind or OAT as well? What's the output of >> gstat -h? >> > > ok, i think i found ... few hours ago the difference > between OIT and next transaction was 50 000 but > now i try again gstat -h and the difference if only 5 ... > > Database header page information: > Flags 0 > Checksum 12345 > Generation 1003772708 > Page size 8192 > ODS version 11.2 > Oldest transaction 1003732753 > Oldest active 1003732754 > Oldest snapshot 1003732754 > Next transaction 1003732757 > Bumped transaction 1 > Sequence number 0 > Next attachment ID 39942 > Implementation ID 26 > Shadow count 0 > Page buffers 0 > Next header page 0 > Database dialect 3 > Creation date Oct 30, 2011 2:23:04 > Attributes force write, no reserve > > Variable header data: > Sweep interval: 0 > *END* > > i don't do any long transaction on the server but this is hard > to know if one select or update was not long to return as lot of > application are > connected to the database, but all application use the same procedure > to select or update the data : > > procedure doSQL > begin > StartTransaction > Try > select or update data > committransaction > except > rollbacktransaction > end; > end; > > for now the only explanation i see is that we do very lot of transaction > (average 225 / Secondes is it lot ?)
That's quite a number, when running in 24x7 mode! Although due to the example above, it seems you do explicit transaction management. So, either you still use automcommit somewhere, which results in a new transaction for every piece of work, our you have a lot of connections and work to be done by explicit transactions. I really think that you are using autocommit as well. The least expensive transactions are READ COMMIT READ ONLY, so basically make use of at least read-only transactions, when you are selecting stuff from the database and don't write. I misunderstanding of how to work with transactions efficiently is probably the number one reason for server performance degradation and performance problems in general. > on the server and this why 20000 sean to be too little ... with around > 19 millions transactions by days > i thing i must use much more than 20000 (1 million?) > > How much transaction by second can firebird can handle ? It seems to "work" in your case, but at least there is a limit of 2^31 transactions before you urgently have to run a backup/restore cycle, which ain't be fun with a 120GB database and more or less 24x7 requirements. So, get used to transactions. Use them wise and efficiently. Separate read-ony and read/write transactions. Reaching the 1 billion limit in ~52 days is quite a number! -- With regards, Thomas Steinmaurer (^TS^) Firebird Technology Evangelist http://www.upscene.com/ http://www.firebirdsql.org/en/firebird-foundation/
