Commented by: Piter (sirpiter)

Database file producing the problem

Commented by: @dyemanov

Statistics for the database:

Database header page information:
Page size 8192
ODS version 11.2
Oldest transaction 79141
Oldest active 87008
Oldest snapshot 87008
Next transaction 406482
Sweep interval: 20000

When you connect to a database, sweep starts running, because of the stuck transaction counters. It performs a garbage colleciton pass through the whole database. The biggest table is this one:

DZIENNIK_ZDARZEN (145)
Primary pointer page: 220, Index root page: 221
Average record length: 49.26, total records: 9057
Average version length: 9.08, total versions: 311711, max versions: 311632
Data pages: 1253, data page slots: 1253, average fill: 91%

Index DZIENNIK\_ZDARZEN\_IDX1 \(1\) 
    Depth: 2, leaf buckets: 269, nodes: 176797 
    Average data length: 7\.20, total dup: 92, max dup: 8 

and it has a lot of garbage, so the GC activity takes a noticeable time deleting unnecessary record versions and index keys (which is even more expensive).

So the first thing that is wrong is your application which has sub-optimal transaction management. That said, Firebird could also behave better performance wise, but it doesn't hang for sure, it works more or less "as designed".

Commented by: @dyemanov

The sweep performance "problem" is caused by 311632 versions of a single record. It looks like your application frequently updates the same record and at the same time the garbage collection is blocked by some long running transaction.

Commented by: Piter (sirpiter)

Thanks for your answer.

Suprisingly (for me), when I break the sweeping process killing fbserver.exe in Task Manager and running it again - the problem stops. During next opening database runs normally.

Ok, that's the tip for me - to check transactions. In Delphi components transactions are often "automatic", so I need to check them.

Commented by: @dyemanov

The engine quickly removes the garbage record versions and starts spending CPU cycles on removing corresponding index keys. After killing the server, the next connection again triggers the sweep but it finds almost no record versions to clean up (the most problematic one is already removed) and thus it skips the index GC pass. As a result, everything looks like working normal, besides the fact that indices still contain garbage keys and they cannot be cleaned up properly (only by index recreation, AFAIU).

Commented by: Piter (sirpiter)

I've changed the type and priority of this issue.
Looks like it works ok, but - as you've said - Firebird could also behave better performance wise.