Subject RE: [firebird-support] Re: Problem with application that creates hundreds of transactions per minute
Author Alan McDonald

I can reproduce this 'stalling' behavior.  At the moment, the application is importing records without delay.  CPU consumption remains very low.

To stall FB, I will now run a query that will scan several thousands of records that have just been INSERTed and UPDATEd.  Before I run this query, here are my stats:

Database header page information:
        Flags                   0
        Checksum                12345
        Generation              65205
        Page size               4096
        ODS version             11.2
        Oldest transaction      65091
        Oldest active           65092
        Oldest snapshot         65092
        Next transaction        65193
        Bumped transaction      1
        Sequence number         0
        Next attachment ID      64
        Implementation ID       26
        Shadow count            0
        Page buffers            0
        Next header page        0
        Database dialect        3
        Creation date           Dec 19, 2015 14:27:42
        Attributes              force write

    Variable header data:
        Sweep interval:         0
        *END*

The query normally takes less a few  seconds and is now stalled (7:18PM).  My application's import process has also stalled and the web application is not serving pages.  FB CPU consumption is at approx 1-2% and Read and Write I/O is very active, there is plenty of disk activity going on.

I was using FB 2.5.4 and upgraded to 2.5.5 Superserver but the problem is the same.  Running on Windows 2012.

The FB Server is definitely doing something, 35 minutes later, here are the stats:

Database header page information:
        Flags                   0
        Checksum                12345
        Generation              66143
        Page size               4096
        ODS version             11.2
        Oldest transaction      65091
        Oldest active           65092
        Oldest snapshot         65092
        Next transaction        66131
        Bumped transaction      1
        Sequence number         0
        Next attachment ID      84
        Implementation ID       26
        Shadow count            0
        Page buffers            0
        Next header page        0
        Database dialect        3
        Creation date           Dec 19, 2015 14:27:42
        Attributes              force write

    Variable header data:
        Sweep interval:         0
        *END*

Unfortunately, this is going painfully slow and I will shut down FB 2.5.5, backup and restore the database, and try with SuperClassic instead of SuperServer.

 

Sounds more like a transaction management issue to me.

You need to tell us what component set you are using for connection and the transaction settings they use.

Your insert transactions should be committing, not commit-retaining or whatever your component set describes this as.

You should do the inset in batches and hard commit more frequently.

Alan