Subject Firebird 2.5: first insert into table takes ages to complete
Author Dominik Psenner

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 60000 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.


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

Enzenbergweg 24/A - Via Enzenberg 24/A
I- 39018 Terlan - Terlano (BZ)<>

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]