I’m responding inline.. see below.
Mit freundlichen Grüßen - Distinti saluti - Best regards Dominik Psenner Software Developer Tel: Fax: Email: +39 0471 319 999 +39 0471 319 990 [email protected] Enzenbergweg 24/A - Via Enzenberg 24/A I-39018 Terlan - Terlano (BZ) www.topcontrol.it Driving efficiency in food processing Die unbefugte Verwendung dieser Mitteilung ist verboten und könnte strafrechtlich verfolgt werden. Wer diese Mitteilung irrtümlicherweise erhält wird gebeten uns umgehend zu informieren und anschließend die Mitteilung zu vernichten. Vielen Dank. Il presente messaggio è diretto unicamente al destinatario sopra indicato. L'utilizzo non autorizzato del presente messaggio è vietato e potrebbe costituire reato. Chiunque altro riceva questa comunicazione per errore è invitato ad informarci immediatamente ed è tenuto a istruggere quanto ricevuto. Grazie per la collaborazione. Caution: The unauthorized use of this message is prohibited and may be prosecuted by law. Anyone who receives this communication in error is requested to inform us immediately and then delete the message. Thank you very much for your collaboration! Von: [email protected] [mailto:[email protected]] Gesendet: 10. Okt 2018 22:48 An: [email protected] Betreff: Re: [firebird-support] Firebird 2.5: first insert into table takes ages to complete Dominik Psenner wrote: > Which sub-release? The latest, i.e. 2.5.8.27089 > Yes. The symptoms suggest you have a large number of record versions > that are waiting for garbage collection. New records cannot be > written to existing pages until after that garbage has been cleared. This makes sense. We have applied migrations that add columns to large tables. This certainly caused every record in that table to be rewritten at least once for each new column that was added. Since the added columns are also not null, a subsequent update rewrites all records again. I would however assume that garbage collection is done at that point in time when records are updated. This is most probably not the case, allowing garbage to accumulate and create a large spike of garbage collection work that finally is done at one point in time. In our case that one point in time is an insert statement that then takes a minute to complete. > See above. After a backup and restore, there is no garbage. However, > the first operation on a dirty table will cause a garbage collection - > hence the long time taken for this first insert. So the behavior stems from the design decision that garbage is not collected at the end of a statement or transaction. It appears to me that this is something that we have to live with for the time being, is it not? > Run gstat -h on the database when you start to notice these delays. > Check the values of the various ' ... Transaction' reports and copy > them back here. There’s nothing unusual there. Oldest transaction 3270 Oldest active 4313 Oldest snapshot 4313 Next transaction 4314 Bumped transaction 1 As you can see there are no stuck transactions and only very few transactions that are still missing a hard commit. The total transactions ran on the database are also very few – caused by a recent backup restore as already mentioned before. Sweep has not run yet because oldest snapshot - oldest transaction < sweep interval: Sweep interval: 20000 > As to the cause, it is totally due to inadequate management of > transactions. This style of poor management commonly comes from > applications that keep read-write transaction open for long periods > and never committing them. Are your apps written in Delphi? Yes, there are some. However those applications have not connected to the database in question since the last backup/restore. The other applications are written in C# and use the firebird dnet provider. These applications are a rewrite from scratch with sane transaction management built-in and enforced by a restful interface and sensible units of work. I am rather sure that those applications are sane from that point of view. > p.s. Would you please strip out your company's footer details when you > post to the lists. The warnings have absolutely no point in a > mailserve list and they take up a lot of space on subscribers' disks. Wearing the hat of the company I work for, I apologize for the noise. [Non-text portions of this message have been removed]
