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
dominik.psenner@...

​

​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: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com]
Gesendet: 10. Okt 2018 22:48
An: firebird-support@yahoogroups.com
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]