AW: [firebird-support] Firebird 2.5: first insert into table takes ages to complete

[Dominik Psenner dominik.psen...@topcontrol.it [firebird-support]]

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.psen...@topcontrol.it
 
​ 

​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 transaction4314
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: 2

> 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]

Re: [firebird-support] Firebird 2.5: first insert into table takes ages to complete

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Dominik Psenner wrote:

> we are observing an interesting issue and hope for interesting
> insights what could cause the symptoms. First of all a few numbers
> about the database and the firebird instance in question:


> · Database contains several hundred tables

> · Few tables contain up to 10.000.000 records

> · Database size on disk is roughly 3GB

> · Firebird is 2.5 with SuperServer flavour

Which sub-release?

> · Page size is 16384

> · Page buffers is configured to 2048

> · Forced writes is enabled

> We noticed that the very first insert after a large migration that
> changed the table layout (new columns and such) takes a large amount
> of time to complete. About 50 seconds on a laptop machine with an
> i7, 8gb memory and an SSD. This first insert reads more than 6
> pages from disk to cache and writes almost the same amount of pages
> from cache to disk. Following insert commands, even after a restart
> or rollback of the previous insert, read only about 70 pages and
> write only 3 to 5 pages from cache to disk and take 5ms to 40ms to
> complete. Following inserts behave, regardless of whether the first
> insert command transaction was rolled back or the firebird process
> was restarted. Doing a backup of the database and restoring the
> database changes the observed behaviour of the first insert
> statement to behave like the previously mentioned subsequent insert 
> statements.

> Observations:


> · This issue does not appear related to disk page caching
> or firebird page caches because it survives computer reboots and firebird 
> server restarts.

> · This issue appears to be related to the persistent state
> of the firebird database because after a backup to gbk and restore to fdb the 
> symptom disappears.

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.

> Is it known and considered "normal" that some insert statements may
> cause the firebird server to read and write several thousand pages
> and taking ages (50 seconds vs 40ms)? Are there any known causes for this 
> symptom?

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.

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.

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?

HB

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.



---
This email has been checked for viruses by AVG.
https://www.avg.com

[firebird-support] Firebird 2.5: first insert into table takes ages to complete

[Dominik Psenner dominik.psen...@topcontrol.it [firebird-support]]

Hi,

we are observing an interesting issue and hope for interesting insights what 
could cause the symptoms. First of all a few numbers about the database and the 
firebird instance in question:


· Database contains several hundred tables

· Few tables contain up to 10.000.000 records

· Database size on disk is roughly 3GB

· Firebird is 2.5 with SuperServer flavour

· Page size is 16384

· Page buffers is configured to 2048

· Forced writes is enabled

We noticed that the very first insert after a large migration that changed the 
table layout (new columns and such) takes a large amount of time to complete. 
About 50 seconds on a laptop machine with an i7, 8gb memory and an SSD. This 
first insert reads more than 6 pages from disk to cache and writes almost 
the same amount of pages from cache to disk. Following insert commands, even 
after a restart or rollback of the previous insert, read only about 70 pages 
and write only 3 to 5 pages from cache to disk and take 5ms to 40ms to 
complete. Following inserts behave, regardless of whether the first insert 
command transaction was rolled back or the firebird process was restarted. 
Doing a backup of the database and restoring the database changes the observed 
behaviour of the first insert statement to behave like the previously mentioned 
subsequent insert statements.

Observations:


· This issue does not appear related to disk page caching or firebird 
page caches because it survives computer reboots and firebird server restarts.

· This issue appears to be related to the persistent state of the 
firebird database because after a backup to gbk and restore to fdb the symptom 
disappears.

Is it known and considered "normal" that some insert statements may cause the 
firebird server to read and write several thousand pages and taking ages (50 
seconds vs 40ms)? Are there any known causes for this symptom?

Mit freundlichen Grüßen - Distinti saluti - Best regards

Dominik Psenner
Software Developer

Tel:+39 0471 319 999
Fax:   +39 0471 319 990
Email: dominik.psen...@topcontrol.it

[cid:image001.jpg@01D46095.2E8E5360]
Enzenbergweg 24/A - Via Enzenberg 24/A
I- 39018 Terlan - Terlano (BZ)
www...topcontrol.it






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!


[Non-text portions of this message have been removed]