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

> 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