Subject Re: Problem with application that creates hundreds of transactions per minute
Author Caroline Beltran
Post date 2015-12-19T22:51:31Z
Here are some more details.  I used GBAK to backup and then restored the database.  Here are the gstat details:

Database header page information:
        Flags                   0
        Checksum                12345
        Generation              30
        Page size               4096
        ODS version             11.2
        Oldest transaction      1
        Oldest active           2
        Oldest snapshot         2
        Next transaction        22
        Bumped transaction      1
        Sequence number         0
        Next attachment ID      2
        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*

I ran the import process again for several minutes and here are the revised stats.  It had stalled again and I let it continue doing whatever it does.  I am not sure if I took these stats during or after the stall completed:

Database header page information:
        Flags                   0
        Checksum                12345
        Generation              16527
        Page size               4096
        ODS version             11.2
        Oldest transaction      16387
        Oldest active           16388
        Oldest snapshot         16388
        Next transaction        16515
        Bumped transaction      1
        Sequence number         0
        Next attachment ID      37
        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*

Is there something obviously wrong with the transactions?  Thank you.


On Sat, Dec 19, 2015 at 4:43 PM, Caroline Beltran <caroline.d.beltran@...> wrote:
I have happily used Firebird 2.5 for a web application that run 24x7 for at least 2 years.  The sweep interval is set to the default 20,000 and it works very well.  There is at least 10 inserts per minute but not much more than that.  I would guess that there are at least 20 reads per second and I've been completely happy with this trouble free application.

Now for the other problematic application...

This is also an online web application which runs wonderfully except during our data import process.  This process will INSERT on average about 200 records per minute.  But approximately 10% of those INSERTS require a large number of SELECTs and several UPDATEs to other records.  This is not a simple case of INSERTing new records.

Everything will run very well for a short period of time but then everything just stalls for several minutes.  CPU consumption is always low, even when the stall happens.  But the I/O Read and Write is quite high, even when stalled.  This tells me that FB is busy doing something.

For this particular database, I decided to set the Sweep Interval to 0 but this stalling still happens.

Just as soon as I notice that FB stalls, I stop the data import process and let FB do whatever it needs to do until it finishes.  I can then resume data importation as normal until it stalls again in a few hours.

Is it possible that sweeping is still being triggered even though I have set the interval to 0?

Thank you